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 chose 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 answego 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 Questions 1. What is the average monthly demand? Round your answer to two decimal place barrels 2. What is the maximum monthly ending inventory Round your answer to the nearest whole number barrels 5. What are the costs associated with chose demand production plan? Round your answers to the nearest cent. Production Inventory Lost Sales Overtime Month Cost Cost Totals Undertime Rate Change Cost Cost Cost Cost $ 5 5 $ 4. What is the total cost? Round your answer to the nearest cent F G H A D E 1 Aggregate Planning: Chase Demand Strategy 2 Production cost (5/unit) 568 00 4 Inventory holding cost (Slunit) $150 5 Lost sales cost (Slunit) $9200 Overtime cos! (S/unit) $6.50 7 Undertime cost (S/unit) $3.20 8 Rate change cost (unit) $4.70 9 Normal production rate (units) 2,400 10 Ending Inventory (previous Dec) 800 11 Cumulative 12 Cumulative Product 13 Month Demand Demand Production Availability 14 January 1.500 15 February 900 16 March 1,900 17 April 2.800 18 May 3,000 19 Jung 3,300 20 July 3.400 21 August 3200 Cu Ending Inventory Lost Sales Month January February March April May June July August Demand 1,500 900 1.900 2.800 3.000 3.300 3.400 3.200 #N #N #N #N AN UN WN EN 1 M Lost Sales Month January February March April May June July August September October November December Average Demand 1,500 900 1.900 2,800 3,000 3,300 3,400 3,200 2.300 1,400 2.300 2.800 #N/A Cumulative Demand WNIA #N/A WNIA WNIA #N/A WNIA F #NIA #N/A F WNIA #N/A #N/A F #N/A Production WNIA #N/A WNIA #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 #NIA #N/A #N/A #N/A #N/A #NIA #N/A #N/A SN/A Maximum Ending Inventory WNIA #NIA WNIA WNIA WNIA #N/A #N/A #NA #N/A #NIA #N/A #N/A #N/A #N/A #NIA WNIA #N/A WNIA WNIA #N/A #NIA #N/A ONIA ANIA ONIA Production Cost #N/A NIA Month January Fohren Inventory Cost =NIA NIA Lost Sales Cost #N/A NA Overtime Cost #N/A Undertime Cost N/A NIA Rate Change Cost #N/A JA ARHIVEE Production Cost Inventory Cost Lost Sales Cost Overtime Cost Undertime Rate Change Cost Cost ho Month January February March April May June July August ptember October ovember ecember Totals Month January February March April May June July August September October November December Totals Production Cost #N/A #NIA #NIA #N/A #N/A #N/A #N/A #N/A #NIA #N/A #N/A #NIA #N/A Total cost #N/A tal cost Rate Change Com Month January February March April May June July August September October November December Totals Production Cost WNIA WNIA #N/A #N/A F #NIA #N/A F #N/A #N/A #N/A #N/A #N/A #N/A #N/A Inventory Cost WNIA WNIA WNIA #NIA #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Lost Sales Cost WNIA WN/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #NIA #N/A #N/A Overtime Cost MNIA WNIA #N/A NNIA WNIA EN/A #NIA #N/A ANIA UNIA F #N/A #NIA Undertime Cosi NNIA ANIA ANIA UNA WNIA WNIA NIA NA #N/A #NIA INA F NIA F #N/A ANIA BNIA ANIA NA ANIA UNIA ANIA NIA ANIA #NIA UNIA INA #NIA Total cost #N/A 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 chose 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 answego 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 Questions 1. What is the average monthly demand? Round your answer to two decimal place barrels 2. What is the maximum monthly ending inventory Round your answer to the nearest whole number barrels 5. What are the costs associated with chose demand production plan? Round your answers to the nearest cent. Production Inventory Lost Sales Overtime Month Cost Cost Totals Undertime Rate Change Cost Cost Cost Cost $ 5 5 $ 4. What is the total cost? Round your answer to the nearest cent F G H A D E 1 Aggregate Planning: Chase Demand Strategy 2 Production cost (5/unit) 568 00 4 Inventory holding cost (Slunit) $150 5 Lost sales cost (Slunit) $9200 Overtime cos! (S/unit) $6.50 7 Undertime cost (S/unit) $3.20 8 Rate change cost (unit) $4.70 9 Normal production rate (units) 2,400 10 Ending Inventory (previous Dec) 800 11 Cumulative 12 Cumulative Product 13 Month Demand Demand Production Availability 14 January 1.500 15 February 900 16 March 1,900 17 April 2.800 18 May 3,000 19 Jung 3,300 20 July 3.400 21 August 3200 Cu Ending Inventory Lost Sales Month January February March April May June July August Demand 1,500 900 1.900 2.800 3.000 3.300 3.400 3.200 #N #N #N #N AN UN WN EN 1 M Lost Sales Month January February March April May June July August September October November December Average Demand 1,500 900 1.900 2,800 3,000 3,300 3,400 3,200 2.300 1,400 2.300 2.800 #N/A Cumulative Demand WNIA #N/A WNIA WNIA #N/A WNIA F #NIA #N/A F WNIA #N/A #N/A F #N/A Production WNIA #N/A WNIA #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 #NIA #N/A #N/A #N/A #N/A #NIA #N/A #N/A SN/A Maximum Ending Inventory WNIA #NIA WNIA WNIA WNIA #N/A #N/A #NA #N/A #NIA #N/A #N/A #N/A #N/A #NIA WNIA #N/A WNIA WNIA #N/A #NIA #N/A ONIA ANIA ONIA Production Cost #N/A NIA Month January Fohren Inventory Cost =NIA NIA Lost Sales Cost #N/A NA Overtime Cost #N/A Undertime Cost N/A NIA Rate Change Cost #N/A JA ARHIVEE Production Cost Inventory Cost Lost Sales Cost Overtime Cost Undertime Rate Change Cost Cost ho Month January February March April May June July August ptember October ovember ecember Totals Month January February March April May June July August September October November December Totals Production Cost #N/A #NIA #NIA #N/A #N/A #N/A #N/A #N/A #NIA #N/A #N/A #NIA #N/A Total cost #N/A tal cost Rate Change Com Month January February March April May June July August September October November December Totals Production Cost WNIA WNIA #N/A #N/A F #NIA #N/A F #N/A #N/A #N/A #N/A #N/A #N/A #N/A Inventory Cost WNIA WNIA WNIA #NIA #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Lost Sales Cost WNIA WN/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #NIA #N/A #N/A Overtime Cost MNIA WNIA #N/A NNIA WNIA EN/A #NIA #N/A ANIA UNIA F #N/A #NIA Undertime Cosi NNIA ANIA ANIA UNA WNIA WNIA NIA NA #N/A #NIA INA F NIA F #N/A ANIA BNIA ANIA NA ANIA UNIA ANIA NIA ANIA #NIA UNIA INA #NIA Total cost #N/A