H I ] K M Formulas B D E F G 1 Aggregate Planning: Level Production Strategy 2 3 Production cost (S/unit) $75.00 4 Inventory holding cost (S/unit) $1.10 5 Lost sales cost (S/unit) $89.00 6 Overtime cost (S/unit) $6.50 7 Undertime cost (S/unit) $2.60 Rale change cost (S/unit) $4.90 9 Normal production rate (units) 2.200 10 Ending inventory (previous Dec.) 900 11 Cumulative 12 Cumulative Product Ending Lost 13 Month Demand Demand Production Availability Inventory Sales 14 January 1,800 15 February 1.200 16 March 2,400 17 April 3,000 18 May 3,300 19 June 3,500 20 July 3,700 21 August 3,400 22 September 2.500 23 October 1,500 24 November 2.400 25 December 2,900 26 Average Maximum 27 28 Production Inventory Lost Sales Overtime Undertime Rate Change 29 Month Cost Cost Cost Cost Cost Cost 30 January Month January February March April May June July August September October November December Average Demand 1.800 1.200 2,400 3,000 3,300 3,500 3,700 3,400 2.500 1,500 2,400 2,900 #NIA Cumulative Demand #NIA #NIA #NIA #NIA #NIA #NIA F #NIA #NIA F #NIA F #NIA #NIA F #NIA 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 #NVA #N/A #N/A #N/A #N/A Maximum Production Cost #N/A Month January Inventory Cost #NIA Lost Sales Cost UNA Overtime Cost #N/A 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. 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 Overtime Undertime Inventory Cost Lost Sales Cost Rate Change Cost Month Cost Cost Cost Totals $ $ $ $ $ 4. What is the total cost? Round your answer to the nearest dollar. $