Excel Online Activity: Aggregate Planning - Level Production 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 Open spreadsheet Questions 1. What is the average monthly demand? Round your answer to two decimal places 2600 barrels 2. What is the maximum monthly ending Inventory? Round your answer to the nearest whole number 2700 barrels 3. What are the costs associated with level demand production plan Round your answers to the nearest dollar Production Cost Inventory Cost 5 Lost Sales Cost Month Totals Overtime Cost Undertime Cost Rate Change Cost $ 83 $ 1 3 4. What is the total cost Round your answer to the nearest dollar Aggregate Planning: Level Production Strategy Production cost ($/unit) Inventory holding cost (S/unit) Lost sales cost (S/unit) Overtime cost ($/unit) Undertime cost (5/unit) Rate change cost (S/unit) Normal production rate (units) Ending inventory (previous Dec.) $74.00 $1.30 $98.00 $6.60 $3.20 $4.90 2.300 1.000 5 7 3 Month January February March April May June July August September October November December Average Demand 1,800 1,200 2,200 3,000 3.100 3,500 3,600 3,500 2,500 1,500 2,500 2,800 2,600.00 Cumulative Cumulative Product Ending Demand Production Availability Inventory 1,800 2.300 3,300 1,500 3,000 2.300 5,600 2,600 5,200 2.300 7.900 2,700 8.200 2.300 10.200 2,000 11,300 2.300 12 500 1,200 14,800 2.300 14,800 0 18.400 2,300 17.100 0 21.900 2,300 19,400 0 24.400 2.300 21.700 0 25.900 2.300 24,000 0 28.400 2.300 26,300 31,200 2,300 28,600 Maximum 2.700 Lost Sales 0 0 0 0 0 0 1,300 2.500 2.700 1.900 2,100 2,600 1 2 3 4 5 6 Inventory Cost Lost Sales Cost Overtime Undertime Rate Change Cost Cost Cost Production Month Cost January $2,042,400.00 February $2,042,400.00 March $2,042,400.00 April $2,042,400.00 May $2,042,400.00 June $2,042,400.00 July $2,042,400.00 August $2,042,400.00 September $2,042,400.00 October $2,042,400.00 November $2,042,400.00 December $2,042,400.00 Totals $24,508,800.00 Total cost TO