Please use excel and show formulas and setup. Table 9 is the Excel setup to use and Table 8 is where to input the answers.Thanks

A major manufacturer of mobile phones uses inventory, overtime, and subcontracting to accommodate periodic demand fluctuations. The company creates a production plan annually but updates it quarterly. Table 7 below provides the expected demand and available capacities for the company's next four quarters (values are in units). Use the transportation method to design a minimum cost production plan that will satisfy demand for the upcoming year. They have 150 units in inventory at the beginning of the planning period and backordering is not permitted. A. Fill in Table 8 below specifying how many units will be produced each period using the 3 types of capacity (1.e., regular, overtime, and subcontracting). (15) B. Include the total cost for the production plan for the year in the last row of Table 8. (5) Table 9: Transportation Method Template Quarter DEMAND FOR Unused Total Quarter Quarter Quarter Capacity Capacity Available Supply From Beginning Inventory Quarter 1 Regular Overtime Note: Include at a minimum a copy of the completed transportation method table and your total cost calculation in your test document as a means of showing your work (see template provided - Table 9). Table A Demand and capacity data Quarter Demand Regular Overtime Subcontracting | Capacity | Capacity Capacity 1 690 1050 92 400 2 920 1050 92 400 3 1840 1050 115 400 4 2185 1050 115 400 Subcontracting Quarter 2 Regular Overtime Subcontracting Quarter 3 Regular Regular production cost per unit: Overtime production cost per unit: Subcontracting cost per unit: Inventory holding cost per unit per quarters Beginning Inventory: $78 $104 $117 $1 150 Overtime Subcontracting Quarter 4 Regular Overtime Table 8: Production plan Production Period Regular Quarter 1 Units Produced Overtime Subcontracting Subcontracting Total Demand Quarter 2 Unmet Demand Quarter 3 Quarter 4 Total Units Total Cost A major manufacturer of mobile phones uses inventory, overtime, and subcontracting to accommodate periodic demand fluctuations. The company creates a production plan annually but updates it quarterly. Table 7 below provides the expected demand and available capacities for the company's next four quarters (values are in units). Use the transportation method to design a minimum cost production plan that will satisfy demand for the upcoming year. They have 150 units in inventory at the beginning of the planning period and backordering is not permitted. A. Fill in Table 8 below specifying how many units will be produced each period using the 3 types of capacity (1.e., regular, overtime, and subcontracting). (15) B. Include the total cost for the production plan for the year in the last row of Table 8. (5) Table 9: Transportation Method Template Quarter DEMAND FOR Unused Total Quarter Quarter Quarter Capacity Capacity Available Supply From Beginning Inventory Quarter 1 Regular Overtime Note: Include at a minimum a copy of the completed transportation method table and your total cost calculation in your test document as a means of showing your work (see template provided - Table 9). Table A Demand and capacity data Quarter Demand Regular Overtime Subcontracting | Capacity | Capacity Capacity 1 690 1050 92 400 2 920 1050 92 400 3 1840 1050 115 400 4 2185 1050 115 400 Subcontracting Quarter 2 Regular Overtime Subcontracting Quarter 3 Regular Regular production cost per unit: Overtime production cost per unit: Subcontracting cost per unit: Inventory holding cost per unit per quarters Beginning Inventory: $78 $104 $117 $1 150 Overtime Subcontracting Quarter 4 Regular Overtime Table 8: Production plan Production Period Regular Quarter 1 Units Produced Overtime Subcontracting Subcontracting Total Demand Quarter 2 Unmet Demand Quarter 3 Quarter 4 Total Units Total Cost