Solve the following problem. Please use excel and show work including formulas. Use the excel template down below. Please answer the problem by showing formulas and work. Don't just put the answers

Excel template. Please use this template and show formulas please.

Period				May	Jun	Jul	Aug	Sept	Oct	Total
Forecast				50	60	70	90	80	70	420
Output
	Regular
	Part Time
	Overtime
	Subcontract
Output - Forecast
Inventory
	Beginning
	Ending
	Average
Backlog
Costs:
	Regular	@	1
	Part Time	@
	Overtime	@	1.6
	Subcontract	@	1.8
	Hire/Layoff
	Inventory	@	2
	Back orders	@
Total

CASE EIGHT GLASSES A DAY (EGAD) 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 offerings 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 Jun Jul Aug Sep Oct Total Forecast 50 60 70 90 80 70 420 Production manager Mark Mercer (no relation to Georgianna) 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 tankload per month Backordering cost Backlogs are not allowed Beginning inventory o tankloads Among the strategies being considered are the following: Level production supplemented by up to 10 tankloads a month from overtime. A combination of overtime, inventory, and subcontracting. Regular production should be the same each month. Using overtime for up to 15 tankloads a month, along with inventory to handle variations. Regular production should be the same each month. Question 1. The objective is to choose the plan that has the lowest cost. Which plan would you recommend? 2. Presumably, information about the new line has been shared with supply chain partners. Explain what information should be shared with various partners, and why sharing that information is important. E31 Xv fx B C E F G . K May 50 Jun 60 Jul 70 Aug 90 Sept 80 Oct 70 Total 420 1 Period 2 Forecast 3 Output 4 Regular 5 Part Time 6 Overtime 7 Subcontract 8 Output - Forecast 9 Inventory 10 Beginning 11 Ending 12 Average 13 Backlog 14 Costs: 15 Regular 16 Part Time 17 Overtime 18 Subcontract 19 Hire/Layoff 20 Inventory 21 Back orders 22 Total 23 @ 1 @ @ 1.6 1.8 @ @ 2 @