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. 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 C14 H 1 1 M N D Loet Sales Demand 1,600 1.000 2,000 F 2.800 Month January February March Apri May June July August September October November December Average 2.900 2.200 2.300 3,200 2.200 1,100 D E F G 1 Aggregate Planning: Chase Demand Strategy 2 3 Production cost ($unt) $74.00 4 Inventory holding Oost ($unt) $140 5 Lost sales cost (unti $10.00 6 Overtime cost (unit) $6.40 7 Undertimecast Aunt) $2.90 Rate change oast ($unn) $4.70 9 Normal production rate (units) 2,100 10 Ending inventory previous Dec.) 800 11 Cumulative 12 Cumulative Product Ending Loet 13 Month Demand Demand Production Availability Inventory Sales 141 January 1.600 15 February 1,000 16 March 2,000 17 Apri 2,800 18 May 2,900 19 June 2.200 20 July 2.300 21 August 3.200 September 2,200 23 October 1.100 24 November 1,900 25 December 2,200 26 Average Maximum 27 28 Production Inventory Lost Sales Overtime Undertime Rate Change 29 Month Cost Cost Cost Casi Cast Cast 30 January 31 February 32 March 33 Apri 34 May 35 June 36 July 37 August 38 September 39 Octaber 40 November 41 December 42 Tola's 43 44 Total cast 45 46 47 48 Curnulative Dan #NA UNA F F #NA #NA F #NA #NA F #NA #NA #NA F F #NA WNA F WNIA Cumulative Product Production Availability #NA ANA UNA ANIA #NA ANA F #N/A ANA F F #NA #NA #NA #N/A F F #NA ANIA #NA #NIA #NA F #NIA WNA ANA WNA ANGA F WNA F NINA Maximum Ending Inventory ANIA ANIA ANIA F #N/A ANIA # #N/A #NIA #N/A #NIA F ANIA ANIA F NNIA F ANIA ANIA ANIA ANA ANA #NA #N/A #NIA #NIA #NIA ANA ANA NNIA 1900 2.200 ANIA Month January February March April May June July August September October November December Totals Production Coat ANA ANA ANA F ANA #NIA ANA F ANA F ANTA ANA ANA ANA ANA ANIA F Inventory Cost #NA F #NA #NA F #NA #NA #NA #NA F #NA F #NA #NA F #NA F #NIA #NA F Last Sales Cost #NA #NA #NA #NA F #NA #NA #NA #NA #N/A #NA #NA #NA #NA Overtime Cost #NIA #NIA #NIA F ANA F ANIA F in F ANA ANIA F F ANA ANA F #NA #N/A F #NIA F #N/A Undertime Ceat #NIA #NIA #NIA F ANIA ANIA F ANIA ANIA F ANIA ANIA F ANIA ANIA #NIA F #NIA Rate Change Cost #NIA #NA #NA #NA ANIA ANIA ANIA ANA ANA ANA #NA #N/A #NIA Total sust ANIA 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. 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 C14 H 1 1 M N D Loet Sales Demand 1,600 1.000 2,000 F 2.800 Month January February March Apri May June July August September October November December Average 2.900 2.200 2.300 3,200 2.200 1,100 D E F G 1 Aggregate Planning: Chase Demand Strategy 2 3 Production cost ($unt) $74.00 4 Inventory holding Oost ($unt) $140 5 Lost sales cost (unti $10.00 6 Overtime cost (unit) $6.40 7 Undertimecast Aunt) $2.90 Rate change oast ($unn) $4.70 9 Normal production rate (units) 2,100 10 Ending inventory previous Dec.) 800 11 Cumulative 12 Cumulative Product Ending Loet 13 Month Demand Demand Production Availability Inventory Sales 141 January 1.600 15 February 1,000 16 March 2,000 17 Apri 2,800 18 May 2,900 19 June 2.200 20 July 2.300 21 August 3.200 September 2,200 23 October 1.100 24 November 1,900 25 December 2,200 26 Average Maximum 27 28 Production Inventory Lost Sales Overtime Undertime Rate Change 29 Month Cost Cost Cost Casi Cast Cast 30 January 31 February 32 March 33 Apri 34 May 35 June 36 July 37 August 38 September 39 Octaber 40 November 41 December 42 Tola's 43 44 Total cast 45 46 47 48 Curnulative Dan #NA UNA F F #NA #NA F #NA #NA F #NA #NA #NA F F #NA WNA F WNIA Cumulative Product Production Availability #NA ANA UNA ANIA #NA ANA F #N/A ANA F F #NA #NA #NA #N/A F F #NA ANIA #NA #NIA #NA F #NIA WNA ANA WNA ANGA F WNA F NINA Maximum Ending Inventory ANIA ANIA ANIA F #N/A ANIA # #N/A #NIA #N/A #NIA F ANIA ANIA F NNIA F ANIA ANIA ANIA ANA ANA #NA #N/A #NIA #NIA #NIA ANA ANA NNIA 1900 2.200 ANIA Month January February March April May June July August September October November December Totals Production Coat ANA ANA ANA F ANA #NIA ANA F ANA F ANTA ANA ANA ANA ANA ANIA F Inventory Cost #NA F #NA #NA F #NA #NA #NA #NA F #NA F #NA #NA F #NA F #NIA #NA F Last Sales Cost #NA #NA #NA #NA F #NA #NA #NA #NA #N/A #NA #NA #NA #NA Overtime Cost #NIA #NIA #NIA F ANA F ANIA F in F ANA ANIA F F ANA ANA F #NA #N/A F #NIA F #N/A Undertime Ceat #NIA #NIA #NIA F ANIA ANIA F ANIA ANIA F ANIA ANIA F ANIA ANIA #NIA F #NIA Rate Change Cost #NIA #NA #NA #NA ANIA ANIA ANIA ANA ANA ANA #NA #N/A #NIA Total sust ANIA