✓ Custom $€ .0 .on File Home Insert Share Page Layout Formulas Data Review View Help Draw Arial 10 B B14 ✓ X✓ fx 1400 > 甘く曲 > 冠 > Comments Editing ✓ . . . P Q R S T 3 A Production cost ($/unit) B с D E F G H J K L M N $74.00 4 Inventory holding cost ($/unit) $1.50 5 Lost sales cost ($/unit) $82.00 6 Overtime cost ($/unit) $6.80 7 Undertime cost ($/unit) $3.20 8 Rate change cost ($/unit) $5.00 9 Normal production rate (units) 2,000 10 Ending inventory (previous Dec.) 800 11 Cumulative 12 13 Month Demand Cumulative Demand Product Production Availability Ending Inventory Lost Cumulative Cumulative Product Sales 14 January 1,400 1,475 15 FUERANZ222222223323333BRUINE 14 February 1,000 2,275 Month January February Demand Demand Production Availability Ending Inventory Lost Sales 1,400 #N/A 1,475 #N/A #N/A #N/A 1,000 #N/A 2,275 #N/A #N/A #N/A 16 March 1,800 2,275 March 1,800 #N/A 2,275 #N/A #N/A #N/A 17 April 2,700 2,275 April 2,700 #N/A 2,275 #N/A #N/A #N/A 18 May 3,000 2,275 May 3,000 #N/A 2,275 #N/A #N/A #N/A 19 June 3,400 2,275 June 3,400 #N/A 2,275 #N/A #N/A #N/A 20 July 3,600 2,275 July 3,600 #N/A 2,275 #N/A #N/A #N/A 21 August 3,300 2,275 August 3,300 #N/A 2,275 #N/A #N/A #N/A September 2,200 2,275 October 1,000 2,275 24 November 1,700 2,275 September October November 2,200 #N/A 2,275 #N/A #N/A #N/A 1,000 #N/A 2,275 #N/A #N/A #N/A 1,700 #N/A 2,275 #N/A #N/A #N/A 25 December 2,200 2,275 26 Average Maximum December Average 2,200 #N/A 2,275 #N/A #N/A #N/A #N/A Maximum #N/A 27 28 Production 29 Month Cost Inventory Cost Lost Sales Cost Overtime Undertime Rate Change Cost Cost Production Inventory Lost Sales Cost 30 January Month January Cost Cost Cost Overtime Cost Undertime Rate Change Cost Cost #N/A #N/A #N/A #N/A #N/A #N/A 31 February February #N/A #N/A #N/A #N/A #N/A #N/A March March #N/A #N/A #N/A #N/A #N/A #N/A April April #N/A #N/A #N/A #N/A #N/A #N/A 34 May May #N/A #N/A #N/A #N/A #N/A #N/A 35 June June #N/A #N/A #N/A #N/A #N/A #N/A 36 July July #N/A #N/A #N/A #N/A #N/A #N/A 37 38 39 August September October August #N/A #N/A #N/A #N/A #N/A #N/A September #N/A #N/A #N/A #N/A #N/A #N/A October #N/A #N/A #N/A #N/A #N/A #N/A 40 November November #N/A #N/A #N/A #N/A #N/A #N/A 41 December December #N/A #N/A #N/A #N/A #N/A #N/A 42 Totals Totals #N/A #N/A #N/A #N/A #N/A #N/A 43 44 Total cost Total cost #N/A 45 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. 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. Month Totals Production Cost $ Inventory Cost $ 4. What is the total cost? Round your answer to the nearest cent. $ Check My Work Reset Problem Lost Sales Cost $ Overtime Cost Undertime Cost Rate Change Cost $ $ $