Excel Online Activity: Aggregate Planning - Chase 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 chase 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. X Open spreadsheet Questions 1. What is the average monthly demand? Round your answer to two decimal places. 2400 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 chase demand production plan? Round your answers to the nearest cent. Production Inventory Lost Sales Overtime Undertime Rate Change Month Cost Cost Cost Cost Cost Cost Totals $ $ $ 0 $ $ 4. What is the total cost? Round your answer to the nearest cent. $ File Home Insert Formulas Data Review View Help Editing Comments Mv Arial 10 B av Av Merge General $ .00 >.0 v v v Ev 28 v Ov ... .00 B27 fx F G H I J K L M N Ending Inventory Lost Sales Ending Inventory 2,100 A B D E 1 Aggregate Planning: Chase Demand Strategy 2 3 Production cost ($/unit) $68.00 4 Inventory holding cost ($/unit) $1.10 5 Lost sales cost ($/unit) $85.00 6 Overtime cost ($/unit) $6.30 7 Undertime cost ($/unit) $2.90 8 Rate change cost ($/unit) $5.40 9 Normal production rate (units) 2,300 10 Ending inventory (previous Dec.) 1,200 Cumulative 11 12 Cumulative Product 13 Month Demand Demand Production Availability 14 January 1,500 15 February 1,100 16 March 2,100 17 April 2,700 18 May 2,900 19 June 3,200 20 July 3,400 21 August 3.200 22 September 2,300 23 October 1,500 24 November 2,300 25 December 2.600 26 Average 2,400.00 Maximum 27 28 Production Inventory Lost Sales Overtime 29 Month Cost Cost Cost Cost 30 January 31 February 32 March 33 April = A Sheet1 + 2,700 Month Demand January 1,500 February 1,100 March April May 2,900 June 3,200 July August 3,200 September October 1,500 November 2,300 December 2,600 Average =AVERAGE(B14:B25) Cumulative Demand #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 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 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 Maximum 3,400 #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 2,300 Undertime Rate Change Cost Cost Overtime Cost Month January February March April Production Cost #N/A #N/A #N/A #N/A Inventory Cost #N/A #N/A #N/A #N/A Lost Sales Cost #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Undertime Cost #N/A #N/A #N/A #N/A Calculation Mode: Automatic Workbook Statistics Give Feedback to Microsoft 100% +