Please use excel and show formulas and setup, 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 (.e., regular, overtime, and subcontracting). (15) Table 9: Transportation Method Template B. Include the total cost for the production plan for the year in the last row of Table 8. (5) Quarter DEMAND FOR Unused Total Quarter Quarter Quarter Capacity Capacity 2 3 4 Available 1 Beginning Inventory From Regular Overtime Note: Include at a minimum a copy of the completed transportation method table and your total cost Supply calculation in your test document as a means of showing your work (see template provided - Table 9). Quarter 1 Table 7. Demand and capacity data Quarter Demand Regular Overtime Subcontracting Capacity Capacity Capacity 1 690 1050 92 400 2 920 1050 92 400 Quarter 2 3 3 1840 IOSO 115 400 4 1050 115 400 Subcontracting Regular Overtime 2185 Subcontracting Quarter 3 Regular Regular production cost per unit: Overtime production cost per unit: Subcontracting cost per unit: Inventory holding cost per unit per quarter. Beginning Inventory: $78 S104 $117 $1 150 Overtime Subcontracting Quarter 4 Regular Overtime Subcontracting Table 8: Production plan Production Units Produced Period Regular Overtime Subcontracting Quarter Quarter 2 Total Demand 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 (.e., regular, overtime, and subcontracting). (15) Table 9: Transportation Method Template B. Include the total cost for the production plan for the year in the last row of Table 8. (5) Quarter DEMAND FOR Unused Total Quarter Quarter Quarter Capacity Capacity 2 3 4 Available 1 Beginning Inventory From Regular Overtime Note: Include at a minimum a copy of the completed transportation method table and your total cost Supply calculation in your test document as a means of showing your work (see template provided - Table 9). Quarter 1 Table 7. Demand and capacity data Quarter Demand Regular Overtime Subcontracting Capacity Capacity Capacity 1 690 1050 92 400 2 920 1050 92 400 Quarter 2 3 3 1840 IOSO 115 400 4 1050 115 400 Subcontracting Regular Overtime 2185 Subcontracting Quarter 3 Regular Regular production cost per unit: Overtime production cost per unit: Subcontracting cost per unit: Inventory holding cost per unit per quarter. Beginning Inventory: $78 S104 $117 $1 150 Overtime Subcontracting Quarter 4 Regular Overtime Subcontracting Table 8: Production plan Production Units Produced Period Regular Overtime Subcontracting Quarter Quarter 2 Total Demand Unmet Demand Quarter 3 Quarter 4 Total Units Total Cost