How to Find the lower total cost and what quantity we need to order? – holding cost | order cost | total cost
How to Find the lower total cost and what quantity we need to order?
When you calculate an economic order quantity, you identify how many items you should order to minimize order costs. However, you can’t always order that exact amount. Suppliers might require you to order their minimum quantity 30 and your job is to find the lowest cost quantity that you can order while meeting their policy. In this video I’ll show you how to do that
You can find my sample file in this video file collection. In my workbook, I have two worksheets. The first one is called constrained and that’s where we’ll work.
In it, I have calculated the economic order quantity for items with the parameters that you see in cells B3 through B7. And you can see that the EOQ or Q star is 219.09. However, it turns out that we can’t order exactly that amount. The supplier requires us to order in increments of 30 and you can see the table that I’ve created over on the right in columns D through G to calculate the order cost, holding costs and total costs for each of the quantities that I have listed in the quantity column. When we’re done we’ll find the lowest total cost and order that amount if we can.
My first task is to calculate my total order cost if I were only ordering 30 items at a time. So I can see with a flow rate of 1800 that I’ll have to order 60 times throughout the year. And if my order costs is 1200, then the order costs should be, doing a little math in my head, 7,200. So let’s see if that works out. So in cell E4 I’ll type equal and I will have the flow rate, which is in cell B4 and I don’t want this reference to change so I’ll press F4. I will divide that by the quantity which is in cell D4 because I’m at a table, Excel identifies it as add quantity. In other words, the quantity column in the current table. And we’ll multiply that by the order cost. So multiply by the value in B3 and again, I want that reference to stay absolute so I press F4 and enter and I get my order costs as expected. If I order 30 at a time at 7,200, if I go 10 times that amount, it’s 720. So it looks like my calculation is correct.
Next, I need to calculate my holding cost. And because I’m always ordering the same amount, I assume that I have half of that inventory on hand at any one time. Now, of course on day one, I’ll have the full compliment. So for example, 30 items, but at the end of the period I’ll have zero and 30 plus zero is 30 and you divide that in half and your average inventory on hand is 15. So to calculate the holding cost based on average inventory click in cell F4, type an equal sign and 0.5 to indicate that we have half of a particular quantity on hand at a time. Multiply that by the value in the quantity column. So I’ll click cell D4 again, get the same indications before and multiply that by the holding cost which is in cell B7. And I don’t want that reference to change so I’ll press F4 and enter, and there we go, we get our holding costs and the ratio of the value of 135 at quantity 30 is 10 times that when I have 10 times the quantity, so everything is good there.
And the total cost is simply the sum of those two values. So in G4, type an equal sign and we’ll just do E4 plus F4 and enter. And there we get our total costs. And as we scroll down, I can see that the lowest total cost is at 210. So $1,973 and 57 cents. However, do note that if we order in increments of 240 which is above our economic order quantity then it’s only about $6 and 50 cents more expensive. So the question is whether it’s worthwhile to take on a little bit of extra inventory or save $6 and 43 cents a year. If you’d like to see this data laid out visually in the chart, you can switch to the total cost chart worksheet, and there you see how the quantity per order affects your total cost. And after you start ordering about 150 you see that the cost doesn’t change all that much.