Excel Online Activity: Aggregate Planning - Level 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 contin 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 apolled? The 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 5. What are the costs associated with level demand production plan Round your answers to the nearest dollar Production Inventory Lost Sales Month Cost Cost Totals 4. What is the total cost? Round your answer to the nearest dollar Cost Overtinie Cost Undertime Cost Rate Change Cost 3 5 Check My Work Rete Problem Xfx A Aggregate Planning: Level Production Strategy D E F G Production cost (5/unit) Inventory holding cost (S/unit) Lost sales cost (S/unit) Overtime cost (S/unit) Undertime cost (S/unit) Rate change cost (5/unit) Normal production rale (units) Ending Inventory (previous Dec) $74.00 $1.10 $93.00 $6.30 $2.70 $5 20 2,000 800 Cumulative Cumulative Product Ending Demand Production Availability Inventory Lost Sales Month January February March April May June Demand 1,300 900 1.900 2,700 2,800 3,100 3,300 3,000 2,100 1,200 2.200 2500 July August September October November December Average 5 Maximum Production Cost Inventory Cost Lost Sales Cost Overtime Undertime Rate Change Cost Cost Cost 2 8 9 20 11 32 33 34 35 36 37 38 39 40 Month January February March April May June July August September October November December Totals 3 44 45 46 Total cost 19884 51 M N Formulas Lost Sales Month January February March April May June July August September October November December Average Demand 1300 900 1.900 2.700 2.800 3,100 3,300 3,000 2.100 1,200 2,200 2,500 #N/A Cumulative Demand #N/A #NIA #N/A #N/A #N/A #NIA NNIA #N/A N/A #N/A IINIA #N/A Cumulative Product Production Availability #N/A #N/A WNIA #N/A #N/A F #NIA #N/A F #N/A #N/A #N/A N/A F #N/A #N/A F #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A WNIA WNIA #N/A Maximum Ending Inventory #N/A #N/A #N/A #NIA WNIA #N/A WNIA #N/A #N/A #NIA #N/A #N/A #N/A WNIA #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A WNIA Month January February March April May June July August September October November December Totals Production Cost #N/A WNIA ANIA #N/A SNIA #NIA ANIA WNIA WNIA WNIA WNIA ANIA Inventory Cost WNIA ANIA INIA F WNIA UNIA F WNIA F WNIA F UNIA F WNIA WNIA ONIA WNIA #N/A Lost Sales Cost IN/A #N/A #N/A #N/A F ONIA AINA #N/A WNIA ONIA #N/A #N/A N/A N/A Overtime Cost WNIA WNIA WNIA WNIA N/A WNIA WNIA WNIA N/A WNIA WNIA WNIA IN/A Undertime Cost #N/A #NIA WNIA WNIA #N/A INIA WNIA ANA F. WNIA IN/A F WNIA F WNIA WNIA Rate Change Cost #N/A #N/A WN/A WNIA WN/A WNIA WNIA WNIA WNIA NN/A ONIA N/A WNIA Total cost WNIA Excel Online Activity: Aggregate Planning - Level 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 contin 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 apolled? The 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 5. What are the costs associated with level demand production plan Round your answers to the nearest dollar Production Inventory Lost Sales Month Cost Cost Totals 4. What is the total cost? Round your answer to the nearest dollar Cost Overtinie Cost Undertime Cost Rate Change Cost 3 5 Check My Work Rete Problem Xfx A Aggregate Planning: Level Production Strategy D E F G Production cost (5/unit) Inventory holding cost (S/unit) Lost sales cost (S/unit) Overtime cost (S/unit) Undertime cost (S/unit) Rate change cost (5/unit) Normal production rale (units) Ending Inventory (previous Dec) $74.00 $1.10 $93.00 $6.30 $2.70 $5 20 2,000 800 Cumulative Cumulative Product Ending Demand Production Availability Inventory Lost Sales Month January February March April May June Demand 1,300 900 1.900 2,700 2,800 3,100 3,300 3,000 2,100 1,200 2.200 2500 July August September October November December Average 5 Maximum Production Cost Inventory Cost Lost Sales Cost Overtime Undertime Rate Change Cost Cost Cost 2 8 9 20 11 32 33 34 35 36 37 38 39 40 Month January February March April May June July August September October November December Totals 3 44 45 46 Total cost 19884 51 M N Formulas Lost Sales Month January February March April May June July August September October November December Average Demand 1300 900 1.900 2.700 2.800 3,100 3,300 3,000 2.100 1,200 2,200 2,500 #N/A Cumulative Demand #N/A #NIA #N/A #N/A #N/A #NIA NNIA #N/A N/A #N/A IINIA #N/A Cumulative Product Production Availability #N/A #N/A WNIA #N/A #N/A F #NIA #N/A F #N/A #N/A #N/A N/A F #N/A #N/A F #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A WNIA WNIA #N/A Maximum Ending Inventory #N/A #N/A #N/A #NIA WNIA #N/A WNIA #N/A #N/A #NIA #N/A #N/A #N/A WNIA #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A WNIA Month January February March April May June July August September October November December Totals Production Cost #N/A WNIA ANIA #N/A SNIA #NIA ANIA WNIA WNIA WNIA WNIA ANIA Inventory Cost WNIA ANIA INIA F WNIA UNIA F WNIA F WNIA F UNIA F WNIA WNIA ONIA WNIA #N/A Lost Sales Cost IN/A #N/A #N/A #N/A F ONIA AINA #N/A WNIA ONIA #N/A #N/A N/A N/A Overtime Cost WNIA WNIA WNIA WNIA N/A WNIA WNIA WNIA N/A WNIA WNIA WNIA IN/A Undertime Cost #N/A #NIA WNIA WNIA #N/A INIA WNIA ANA F. WNIA IN/A F WNIA F WNIA WNIA Rate Change Cost #N/A #N/A WN/A WNIA WN/A WNIA WNIA WNIA WNIA NN/A ONIA N/A WNIA Total cost WNIA