Calculate production order quantities - Microsoft Excel Tutorial

- [Instructor] So far in this chapter, we've taken on the role of a retailer that orders goods from suppliers. In this movie, I'll assume we're a manufacturer with our own production facility. Given a certain level of demand and other information, what quantity of products should we ask our production facility to make every day? To demonstrate this calculation, I will use the sample file 0107 production order quantity that you can find in the chapter one folder of the exercise files collection. The calculation that you use to find the Q-Star production given the certain inputs is very similar to the EOQ or economic order quantity calculation that you do when you're buying. You can see that we have an annual demand that's in cell B3. That's the number of items that your customers buy from you. Next, we have the setup costs. That's what it costs you to initiate a production run. We have the holding costs of each item and this is an annual cost. Next, we have the daily production rate. That's how many items your factory or facility can turn out in a day. And then we have the daily demand rate of 10. So how did I get that number? Well, I assume that we have 250 working days in a year. And if I divide the annual demand of 2,500, by 250, we get the daily demand rate of 10. So we don't need to be working all day every day because our daily demand rate is below the daily production rate. But the question is, how can we best, that is most cost-effectively create order runs. And how many should we produce each time. To do that, we will create a formula that implements the equation you see on the right. So it's two times the setup cost and the flow rate which is similar to what we had before and in the denominator we'll be multiplying the holding cost by one minus the daily demand over the daily production rate. And what that term does is increase the EOQ as our daily demand comes close to our daily production rate. If daily demand and daily production rate are the same. All right, let's go ahead and implement that equation. I'll click in cell B9, type an equal sign. We're taking the square root of the equation on the right. So that will be two multiplied by the setup cost which is in B4. I'll go ahead and go in the same order as I have in the equation, multiplied by the flow rate, which is annual demand. That's in cell B3. And we're going to divide that by the holding cost, which is in cell B5. And I do have this in parenthesis. So I typed the left parentheses after the forward slash for division multiplied by, and I'll do another set of parentheses, one minus the daily demand which is in B7 divided by the daily production rate which is in B6 and I'll type one, two, three parentheses to close out the equation and enter. And I get a Q-Star of production of 353 and a half units. So 350 for rounding up. So based on these calculations and the cost that we have entered, given our annual demand and daily production rate and daily demand rate, our Q-star production is 354 items. So every time we started production, we make 354, and it looks like we'll do about seven of those a year or a little bit more. So if your company manufacturers items, you can use the technique I showed you in this movie to determine how many to make at one time.