I got the answers in the green check correct, it is the one in x I am having trouble with.

Question

Consider the situation faced by Golden Beverages, a producer of two major products — Old Fashioned and Foamy Delite root beers. Golden Beverages operates as a continuous flow factory and must plan future production for a demand forecast that fluctuates quite a bit over the year, with seasonal peaks in the summer and winter holiday season. How should Golden Beverages plan its overall production for the next 12 months in the face of such fluctuating demand if the level demand strategy is applied? The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below.

 

Answer 1

Hi Joseph, you posed this question a couple of months ago, so I don't know if you're still looking for an answer, but here's a response anyway. This particular Excel exercise has been posted and answered on other forums, but my response and rationale won't necessarily agree with other posts. Here are my thoughts:

1. To find the average monthly demand, you'll first need to sum the total units of demand. After you find the total demand, divide it by the number of months to determine the average. The answer is higher than the number shown on Question 1 in the spreadsheet.
2. The next three metrics follow the "unit x rate" formula. So, the total production cost is the number of units multiplied by the per-unit cost. In this case, the number of units is the total demand for the year. The per-unit production amount is provided in the spreadsheet. The answer is lower than the number shown on Question 3 in the spreadsheet.
3. Similarly, the total inventory cost is total ending inventories (maximum ending inventory x 12 months) multiplied by the inventory holding cost. The answer is lower than the number shown in the spreadsheet.
4. Also, the total lost-sales cost is number of units times the per-unit cost. The number of units would be the unmet demand; in other words, the total demand minus the normal production rate over 12 months. After you determine the number of units in demand that weren't produced, multiply this by the per-unit amount. The answer is higher than the number shown in the spreadsheet.
5. The total cost is the sum of all cost categories. The answer is lower than the number shown in the spreadsheet.

Hope this helps!