There is a cost to carrying stock that extends beyond an item's cost. Purchase too much of an item, it just sits on shelves taking up space in the shop, making both a target for shoplifters and using up your overdraft often for prolonged periods of time. Conversely underbuying is also a costly mistake, as not having the item can cost sales. What EOQ attempt to find the best balance between these two problems.
The formula is (Number of times to order) =sqrt( 2xDxk/h)
where
D is the ordering quantity of the item
k is the cost of ordering
h= holding costs.
Now our system is not designed to handle this, but it does have an adhoc reporting system that can be used to do such a calculation. I think as an exercise many of you would find it interesting to do such a calculation both for its results and as a means of learning the tremendous power of the system you have. Once you master this adhoc reporting, you will discover what a great power you do have in your point-of-sale system.
Note if there is significant interest in this, we will do an online training course on this and/or incorporate this into our software.
In retail profitability and inventory management are intertwined. One of the earliest and most popular scientific methods of doing this is Economic Ordering Quantity (EOQ) which I spoke about early here.
http://www.possolutions.com.au/blogs/newsagency/economic-order-quantity
I explained how I got my stock turns there and its 10 for the stationery department, hold that figure.
Go to register reports and select the top stock report as marked with the red arrow here
Now select your criteria, I used the stationery department because it's a simple department that most people could relate to. Furthermore, note marked in red, I put 99 million in for the number of items just to get everything. I also picked a whole year as small periods can have fluctuations that can muck up the long-term trends.
Now I got this report
Although I could use SQL, I decided to import the data into excel as you can see here with the red arrow.
This then produced for me an excel spreadsheet of my report.
That is the standard report but now, I can change this to almost anything I want - adhoc. This is the report I changed it too, click on it for more details and I will now explain how I did it.
You will see in my excel spreadsheet, that I have put all my variables on the right; this is so I can play with them later as you will see.
The first variable I need is D which is the ordering quantity of the item, I have that here as units so that problem is solved all I need to do is Units and divide it by 52 weeks a year, so a column M2 is =G2/52 and I just copy it down.
The cost of ordering is something I have to determine, I might say it takes for the department about two hours for someone to do an order and another hour to process the order when the goods come. Say 3 hours plus some extras say about $150. Since I have 247 items in this department that works out to.
k = $150/247 = 0.60728745
Now what we need is the holding cost h which is the holding cost.
I would say for a year we are looking the interest of money plus shoplifting of about 1.5% so say about 14% so per week that works out to 14%/52 weeks a year about .2% plus there is a rental on the building. This is something you need to determine. I suggested before that you go to your last year's profit and loss to determine this. What is your cost of the space that you require to sell an item? Many when they do this report to me how stunned, they were just how high that figure actually is, this figure I called Fixed h. In this case as I do not know it, I am setting it to zero.
Now I need the stock on hand, I could use the figure that is there, but it does suffer from the short term fluctuations that I spoke about above so I prefer to use the yearly figure so I need to subtract the profit from the sales and divide by the stock turns.
So what I do is make a formula (Sale$-Profit$)/stock turn in this excel sheet, which here is =(J2-H2)/$R$1 which is my SOH figure.
My h is now (SOH) x h + Fixed h, and you can see the column there marked N.
Now my EOQ is easy to determine as its =SQRT(2*M2*$S$2/N2) and copy that down.
Since I am working in weeks, what I did was that 52 weeks and divide by my EOQ.
The formula here suggests that I should look at it every two or three months, which make sense as in this store, stationery is a small department with sales of about $3000 a year. There are certainly more important departments that the shop should be looking into it.
Now what I would do is change the figure, increase or decrease the stock turns, the holding costs etc to see what happens.
Having said this, ultimately good inventory management comes down to make sure you're not sacrificing profitability due to poor procedures.
