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.Questions1. What is the average monthly demand? Round your answer to two decimal places.barrels2. What is the maximum monthly ending inventory? Round your answer to the nearest whole number.barrels3. What are the costs associated with aggregate production plan? Round your answers to the nearest cent.4. What is the total cost? Round your answer to the nearest cent.

A B C D E F G H Aggregate Planning K L M WN Production cost ($/unit) $75.00 Inventory holding cost ($/unit) $1.40 Lost sales cost ($/unit) $99.00 Overtime cost ($/unit) $6.40 Undertime cost ($/unit) $2.70 00 Rate change cost ($/unit) $5.00 9 Normal production rate (units) 2,100 Ending inventory (previous Dec.) 1,200 11 Cumulative Cumulative 12 Cumulative Product Ending Lost Cumulative Product 13 Month Demand Demand Production Availability Inventory Sales Month Demand Demand Production Availability 14 January 1,700 1,200 January 1,700 #N/A 1,200 #N/A 15 February 1,200 2,400 February 1,200 #N/A 2,400 #N/A 16 March 2, 100 2,400 March 2,100 #N/A 2,400 #N/A 17 April 2,900 2,400 April 2,900 #N/A 2,400 #N/A 18 May 3,100 2,400 May 3,100 #N/A 2,400 #N/A 19 June 3,400 2,400 June 3,400 #N/A 2,400 #N/A 20 July 3,500 2,400 July 3,500 #N/A 2,400 #N/A 21 August 3,200 2,400 August 3,200 #N/A 2,400 #N/A 22 September 2, 100 2,400 September 2,100 #N/A 2,400 #N/A 23 October 1, 100 2,400 October 1, 100 #N/A 2,400 #N/A 24 November 2,000 2,400 November 2,000 #N/A 2,400 #N/A 25 December 2,500 2,400 December 2,500 #N/A 2,400 #N/A 26 Average Maximum Average #N/A Maximum 27 28 Production Inventory Lost Sales Overtime Undertime Rate Change Production Inventory Lost Sales Overtime 29 Month Cost Cost Cost Cost Cost Cost Month Cost Cost Cost Cost 30 January January #N/A #N/A #NIA #NIA 31 February February #N/A #N/A #N/A #N/A 32 March March #N/A #N/A #NIA #NIA April April #N/A #N/A #N/A #N/A 33 May May #N/A #N/A #NIA #NIA 34 > = Sheet1 +