The Economic Order Quantity. In this screen cast, I will show you how to calculate the Economic Order Quantity using Google Sheets.

0:11

Here, we have the setup.

0:14

We are going to use this example, Product Demand equals 5,000 units. Let’s assume it’s per year. The product value is $100. Each time we place an order, it costs us $100 to submit the order and receive it and we pay 25% of the item’s value in carrying the inventory for a year. Our goal will be to calculate the order quantity and the total cost resulting from that order quantity. Now, let’s take a look how we can do this.

0:54

So here, we have the total cost formula and let me start with that first. So, the total cost is made up of the order receiving cost and the inventory holding cost.

1:07

This first part here is the order receiving cost and this part here is the order holding cost. So, it’s a very simple formula and we’re going to implement it in three pieces. I’ll first do the order receiving cost, which equals the order cost multiplied.

1:33

By the demand divided by the order quantity. Now in this case, what I did was I just put in a couple values and then we’re going to play around a little bit to see how it varies. Later on, we’re going to get to the economic order quantity. But let me start with this first, so it becomes a little bit more clear. So this is the order receiving cost and then the order holding cost is the value of the product multiplied by the carrying cost percentage multiplied the quantity that we are ordering each time, and that is divided by 2 and then the total cost is just the sum of the 2 prior calculated values. So, there you have it. The total for an order quantity of 50 is $10,625. So that’s the yearly cost of placing the orders, receiving them and holding the inventory. Now 50 under each time if we sell 5,000 per year is a very small order, so we will have very frequent order placement. Now, let’s see how the total cost varies by placing different size orders. So, I just moved these values across here.

3:05

And then we can do the same thing and that’s the beauty of spreadsheets. Now, we calculate very quickly a range of different values. So we see the total costs start at 10,625, which we calculated previously. I just picked these numbers randomly, by the way and it goes down. It seems to reach a minimum of 5,000 and then it goes back up.

3:32

Now if we place these values on a larger stream, here is what happens. And I just grew the graph to see it a little bit better, but we see that the total cost, which is this orange line starts up high, goes down and it seems to reach the minimum by 200 units per order and then it goes back up. Now we see that the inventory carrying cost, which is the red line steadily increases. Because the larger the orders, the more inventory will hold at the given time. The higher average inventory yes, in other words. And then this blue line, which is placing and receiving the orders that goes down, because we place larger and larger orders that means we place them less frequently, because each order lasts us much longer.

4:34

Now, let’s take a look at the economic order quantity. So, what the EOQ does is it sets equal the holding cost with the order in cost. And when those two are equal as you can see, let me go back to the graph for a second. As you can see in this graph, when they cross, in other words, when they’re equal, that’s when we seem to have hit the minimum. It’s a little bit hard to tell in this graph. But mathematically, that’s what the EOQ formula does. So, it’s a fairly simple formula and I will show you how to do it in a spreadsheet. So, it’s the square root of 2 times the order cost times the demand divided by recurring costs multiplied by the product value. You actually recognized that you enter each value just once and here’s the formula on the right-hand side, and on the left-hand side right here where I just worked on is how this formula would look in a spreadsheet. Hit Return and there you go. The result is 200 and that results in an equal receiving cost to an equal holding cost. Our total cost is 5,000. We already knew that was the lowest value overall and there you have it. That’s our overall calculation of the economic order quantity.

6:15

And by the way, for those of you who like Excel, it works exactly the same way.

# 6. Economic Order Quantity

Bài học Progress

0% Complete