EVERY ANSWER IS INCORRECT.

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. Questions 1. What is the average monthly demand? Round your answer to two decimal places. 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 level demand production plan? Round your answers to the nearest dollar. Production Cost Inventory Cost Lost Sales Cost Overtime Cost Undertime Cost Month Totals $ $ $ $ 4. What is the total cost? Round your answer to the nearest dollar. $ Rate Change Cost B Aggregate Planning: Level Production Strategy 3 Production cost ($/unit) $66.00 $1.50 4 Inventory holding cost ($/unit) 5 $98.00 6 Lost sales cost ($/unit) Overtime cost ($/unit) Undertime cost ($/unit) $6.40 7 $3.30 8 Rate change cost ($/unit) $4.70 9 Normal production rate (units) 2,200 10 Ending inventory (previous Dec.) 800 11 12 13 Demand Month January 14 1,800 15 February 1.400 16 March 2,300 17 April 2,900 18 May 3,100 19 June 3,300 20 July 3,400 21 August 3,100 22 September 2,200 23 October 1,000 24 November 1,800 25 December 2,300 26 Average 27 28 Production Inventory Lost Sales Cost Cost 29 Month Cost 30 January 31 February 32 March 33 April 34 May 35 June 36 July 37 August 38 39 September October November 40 41 December Totals 42 43 44 Total cost 45 46 D 1 2 E Cumulative Cumulative Product Demand Production Availability Ending Inventory Maximum Overtime Undertime Rate Change Cost Cost Cost F G Lost Sales H I Formulas Month January February March April May June July August September October November December Average Month January February March April May June July August September October November December Totals Total cost J K L Cumulative Demand Demand 1,800 1,400 #N/A #N/A #N/A #N/A 2,300 2,900 3,100 #N/A 3,300 #N/A V Production #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 3,400 #N/A 3,100 #N/A 2,200 #N/A 1,000 #N/A 1,800 #N/A 2,300 #N/A #N/A Inventory Cost #N/A #N/A #N/A V Lost Sales Cost #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Production Cost V #N/A V #N/A V #N/A #N/A #N/A #N/A #N/A #N/A #N/A V #N/A #N/A #N/A #N/A #N/A #N/A #N/A V #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A V #N/A #N/A Maximum M Cumulative Product Availability #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Overtime Cost #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A P N #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Ending Inventory Undertime Cost #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 0 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Lost Sales Rate Change Cost #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A P 123456MOOIEMSASSTOO -----: 39 40 4125 A te Planning: Level Production Strategy OOOOOOO O I O .....OOOO Lost Sales November 20 Totals: -SUM(B30:841) -SUM(C30 C41) -SUM(D30:041) Total cost: -SUM(B42:G42) Average monthly demand = 2383 Maximum monthly ending inventory = 2250 3) Costs Overtime Production Inventory Lost Sales Cost Cost Cost Cost 20725 192733 8960 Month Totals: 1834800 Undertime Rate Change Cost 0 Cost 548