The EGAD Bottling Company has decided to introduce a new line of premium bottled water that will include several "designer" flavors. Marketing manager Georgianna Mercer is predicting an upturn in demand based on the new offer and the increased public awareness of the health benefits of drinking more water. She has prepared aggregate forecasts for the next six months, as shown in the following table (quantities are in tankloads): Month May Forecast 50 June 60 July 70 Aug 80 Sep 90 Oct 70 Production manager Mark Mercer (no relation to Georgianna Mercer) has developed the following information. (costs are in thousands of dollars) Regular production cost $1 per tankload Regular production capacity 60 tankloads Overtime production cost $1.6 per tankload Subcontracting cost $1.8 per tankload Holding cost $2 per unit per tankload per month Back ordering cost $5 per month per tankload Beginning inventory 0 Among the strategies being considered are the following: 1. Level production supplemented by up to 10 tankloads a month from overtime. 2. A combination of overtime, inventory, and subcontracting. 3. Using overtime for up to 15 tankloads a month, along with inventory to handle variations. The objective is to choose the plan that has the lowest cost. Which plan would you recommend?

How is this problem solved on the spreadsheet?

The EGAD Bottling Company has decided to introduce a new line of premium bottled water that will include several "designer" flavors. Marketing manager Georgianna Mercer is predicting an upturn in demand based on the new offer and the increased public awareness of the health benefits of drinking more water. She has prepared aggregate forecasts for the next six months, as shown in the following table (quantities are in tankloads): [ Production manager Mark Mercer (no relation to Georgianna Mercer) has developed the following information. (costs are in thousands of dollars) Regular production cost \$1 per tankload Regular production capacity 60 tankloads Overtime production cost $1.6 per tankload Subcontracting cost $1.8 per tankload Holding cost $2 per unit per tankload per month Back ordering cost $5 per month per tankload Beginning inventory 0 Among the strategies being considered are the following: 1. Level production supplemented by up to 10 tankloads a month from overtime. 2. A combination of overtime, inventory, and subcontracting. 3. Using overtime for up to 15 tankloads a month, along with inventory to handle variations. The objective is to choose the plan that has the lowest cost. Which plan would you recommend