Chapter 14 Excel Activity: Aggregate Planning & Chase Production Question 1 0/25 Video Submit Excel Online Activity: Aggregate Planning - Chase Production Consider the situation faced by Golden Beverages, 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. 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 chase demand production plan? Round your answers to the nearest cent. Production Lost Sales Overtime Inventory Cost Undertime Cost Rate Change Cost Month Cost Cost Cost Totals $ $ $ $ $ $ 4. What is the total cost? Round your answer to the nearest cent. Check My Work Reset Problem v 10 A" A fx A B Aggregate Planning: Chase Demand Strategy D E F H 1 K L Production cost ($/unit) Inventory holding cost ($/unit) Lost sales cost ($/unit) Overtime cost ($/unit) Undertime cost ($/unit) Rate change cost ($/unit) Normal production rate (units) inding inventory (previous Dec.) $68.00 $1.20 $89.00 $6.60 $2.90 $5.00 2,300 1,200 Cumulative Cumulative Product Demand Production Availability Lost Ending Inventory Lost Sales Ending Inventory Month January Sales February .6 17 18 19 20 21 22 Demand 1,300 800 1,600 2.500 2,700 3,000 3,100 2,800 1,700 600 1.300 1,800 March April May June July August September October November December Average Month January February March April May June July August September October November December Average Demand 1,300 800 1,600 2.500 2.700 3,000 3,100 2,800 1,700 600 1,300 1,800 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 Cumulative Product Production 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 #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 #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 #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 #N/A Production Cost Inventory Cost Lost Sales Cost Overtime Undertime Rate Change Cost Cost Cost Overtime Cost 24 25 26 27 28 29 30 31 32 33 | 34 35 Month January February March April May June July August September October November Month January February March April May June July August September October November n.--- Production Cost #N/A #N/A #N/A #N/A F #N/A #N/A #N/A #N/A #N/A #N/A #N/A Inventory Cost #N/A #N/A #N/A #N/A #N/A #N/A #N/A #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 #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 #N/A Undertime Cost #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 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 ILIA eet1 + Wavleka-l ... Chapter 14 Excel Activity: Aggregate Planning & Chase Production Question 1 0/25 Video Submit Excel Online Activity: Aggregate Planning - Chase Production Consider the situation faced by Golden Beverages, 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. 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 chase demand production plan? Round your answers to the nearest cent. Production Lost Sales Overtime Inventory Cost Undertime Cost Rate Change Cost Month Cost Cost Cost Totals $ $ $ $ $ $ 4. What is the total cost? Round your answer to the nearest cent. Check My Work Reset Problem v 10 A" A fx A B Aggregate Planning: Chase Demand Strategy D E F H 1 K L Production cost ($/unit) Inventory holding cost ($/unit) Lost sales cost ($/unit) Overtime cost ($/unit) Undertime cost ($/unit) Rate change cost ($/unit) Normal production rate (units) inding inventory (previous Dec.) $68.00 $1.20 $89.00 $6.60 $2.90 $5.00 2,300 1,200 Cumulative Cumulative Product Demand Production Availability Lost Ending Inventory Lost Sales Ending Inventory Month January Sales February .6 17 18 19 20 21 22 Demand 1,300 800 1,600 2.500 2,700 3,000 3,100 2,800 1,700 600 1.300 1,800 March April May June July August September October November December Average Month January February March April May June July August September October November December Average Demand 1,300 800 1,600 2.500 2.700 3,000 3,100 2,800 1,700 600 1,300 1,800 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 Cumulative Product Production 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 #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 #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 #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 #N/A Production Cost Inventory Cost Lost Sales Cost Overtime Undertime Rate Change Cost Cost Cost Overtime Cost 24 25 26 27 28 29 30 31 32 33 | 34 35 Month January February March April May June July August September October November Month January February March April May June July August September October November n.--- Production Cost #N/A #N/A #N/A #N/A F #N/A #N/A #N/A #N/A #N/A #N/A #N/A Inventory Cost #N/A #N/A #N/A #N/A #N/A #N/A #N/A #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 #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 #N/A Undertime Cost #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 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 ILIA eet1 + Wavleka-l