0 Excel Online Activity: Aggregate Planning 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 aggregate planning 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. X Open spreadsheet Questions 1. What is the average monthly demand? Round your answer to two decimal places. 2175 barrels 2. What is the maximum monthly ending Inventory? Round your answer to the nearest whole number, barrels 1. What is the average monthly demand? Round your answer to two decimal places. 2175 barrels 2. What is the maximum monthly ending inventory? Round your answer to the nearest whole number. barrels 3. What are the costs associated with aggregate production plan? Round your answers to the nearest cent. Production Overtime Undertime Inventory Cost Lost Sales Cost Rate Change Cost Month Cost Cost Cost $ Totals 0 0 0 4. What is the total cost? Round your answer to the nearest cent. $ Check My Work Reset Problem fx B D E F G H Aggregate Planning Production cost 1$/unit) Inventory holding cost ($/unit) Lost sales cost ($/unit) Overtime cost ($/unit) Undertime cost (Slunit) Rate change cost (S/unit) Normal production rate (units) Ending inventory (previous Dec.) $68.00 $1.10 $91.00 $6.70 $2.70 $4.90 2,300 1,200 Ending Inventory Lost Sales Month January February March April May 5 6 .7 18 Demand 1.200 800 1,800 2.700 2,900 Cumulative Product Production Availability 975 2,175 2,175 2,175 2,175 Cumulative Demand 1.200 2,000 3,800 6,500 9,400 Month January February March April May Number B A (prevus Des.) D E G 1.200 H Ending Inventory Lost Sales Month January February March April May June July August September October November December Average Demand 1,200 800 1,800 2,700 2.900 3,300 3,400 3,100 1,900 900 1,800 2,300 Cumulative Demand 1,200 2,000 3,800 6,500 9,400 12,700 16.100 19.200 21,100 22,000 23,800 26,100 Cumulative Product Production Availability 975 2,175 2,175 2,175 2,175 2.175 2,175 2,175 2,175 2,175 2,175 2,175 Maximum Month January February March April May June July August September October November December Average Demand 1,200 800 1,800 2.700 2.900 3,300 3,400 3.100 1,900 900 1.800 2.300 WNA Partin Production Inventory Ina Rales Overtime Intertime Rata Chama Sheet1 + Ghe fx A B D E F G H Production Cost Inventory Cost Lost Sales Cost Overtime Undertime Rate Change Cost Cost Cost Month January February March April May June July August September October November December Totals Month January February March April May June July August September October November December Totals WE Total cost UN Total cost