I have figured out the formulas needed for each cell. Could you please show me what to put into solver to find the lowest total cost please.

Assuming there is no constraint on the ending inventory, use Solver to find the optimal plan using the Level Capacity strategy. There is no need to round the results, i.e., you can keep the production quantities as decimal numbers if it is the result of Sovler. Assuming the inventory at the end of the planning period (i.e., end of Dec. 2020) should be as low as possible, complete the planning table above (yellow cells).

Q2. SOP (19 points) - Read the scenario descriptions and complete parts (a), (b) and (c) below. The ABC Company has the following demand data (highlighted in green) for the last 2 years of sales for all models of their popular Toy Pop product (in units): - The company currently has five employees on the ToyPop line, each capable of producing approximately two ToyPops per day (assume 25 days per month). - Hiring and layoff are not considered for Year 2020. - The employees each earn $20 per hour for the standard 8-hour day, with $10 extra per hour premium for each hour of overtime. - They can subcontract the production of the Toy Pop, but to do so costs them $42 per unit above the standard cost. - They can use inventory, but inventory holding costs are $25 per month per unit, based on the number of units in inventory at the end of the month. They have room for only 200 units in inventory, after which they n facility, which adds another $15 per month to the inventory holding cost. - Backorder cost is $150 per month per unit. - They currently have (as of the end of Decemer 2019) 29 units in inventory. Number of employees Production rate/employee/day Number of days/month Regular production cost/hour Number of regular hours/day Overtime premium/hour Overtime capacity/month/employee Subcontracting premium/unit Holding cost/month/unit Inventory storage capacity Public storage holding cost premium Beginning inventory Backorder cost/unit 5 employees 2 units 25 days $20 8 hours $10 5 days $42 $25 200 units $15 29 units $150 (b)-1 Number of hours needed/unit (b)-2 Regular production cost/unit (b)-3 Maximum overtime capacity/month (b)-4 Overtime cost/unit (b)-5 Subcontracting cost/unit 4 hours $80 50 units $120 $122 Costs Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total 232 301 422 355 296 288 233 194 274 244 221 247 254 325 398 369 324 298 255 242 256 266 235 249 43 313 410 362 310 293 244 218 265 255 228 248 3389 Total Cost 2 2018 Demand 2019 Demand (a) 2020 Demand Regular production Overtime production Subcontracting production 29 Ending Inventory Backorder Quantity - Inventory (internal) - Inventory (public storage) 0 214 0 0 0 527 0 0 Total Production Quantity o 0 0 0 0 0 0 0 0 0 29 937 129916091902 2146 2364 2629 2884 3112 3360 22983 0 0 0 0 0 0 0 0 0 29 0 0 0 0 0 0 0 0 0 0 0 $3,447,450 $725.00 29 Total Costs $3,448,175 Requirements (a) Use the 2018 and 2019 demand data to develop a forecast for 2020 annual demand (month by month) (fill in cells G6 to S6). 1 pt (b) Use your 2020 forecast data to develop an aggregate plan (SOP) using the Level Capacity strategy. Start the planning from calculating the following parameters and fill in the corresponding yellow cells in Column C. 1. What is the number of hours needed for producing one unit of Toypop? (fill in cell C18) 2. What is the regular production cost per unit? (fill in C19) 0.5 pt 3. What is the maximum overtime capacity per month? (fill in C20) 0.5 pt 4. What is the overtime cost per unit? (fill in C21) 0.5 pt 5. What is the subcontracting cost per unit? (fill in C22) 0.5 pt Assuming the inventory at the end of the planning period (i.e., end of Dec. 2020) should be as low as possible, complete the planning table above (yellow cells in G7 to T15). You need to fill formulas into Inventory/Backorder-related cells and also Total Quantity & Costs cells. 9 pts (c) After completing parts (a) and (b) above, please create a copy of this worksheet, and name the new worksheet as "Q2 Level Solver". Assuming there is no constraint on the ending inventory, use Solver to find the optimal plan using the Level Capacity strategy. There is no need to round the results, i.e., you can keep the production quantities as decimal numbers if it is the result of Sovler. 7 pts Q2. SOP (19 points) - Read the scenario descriptions and complete parts (a), (b) and (c) below. The ABC Company has the following demand data (highlighted in green) for the last 2 years of sales for all models of their popular Toy Pop product (in units): - The company currently has five employees on the ToyPop line, each capable of producing approximately two ToyPops per day (assume 25 days per month). - Hiring and layoff are not considered for Year 2020. - The employees each earn $20 per hour for the standard 8-hour day, with $10 extra per hour premium for each hour of overtime. - They can subcontract the production of the Toy Pop, but to do so costs them $42 per unit above the standard cost. - They can use inventory, but inventory holding costs are $25 per month per unit, based on the number of units in inventory at the end of the month. They have room for only 200 units in inventory, after which they n facility, which adds another $15 per month to the inventory holding cost. - Backorder cost is $150 per month per unit. - They currently have (as of the end of Decemer 2019) 29 units in inventory. Number of employees Production rate/employee/day Number of days/month Regular production cost/hour Number of regular hours/day Overtime premium/hour Overtime capacity/month/employee Subcontracting premium/unit Holding cost/month/unit Inventory storage capacity Public storage holding cost premium Beginning inventory Backorder cost/unit 5 employees 2 units 25 days $20 8 hours $10 5 days $42 $25 200 units $15 29 units $150 (b)-1 Number of hours needed/unit (b)-2 Regular production cost/unit (b)-3 Maximum overtime capacity/month (b)-4 Overtime cost/unit (b)-5 Subcontracting cost/unit 4 hours $80 50 units $120 $122 Costs Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total 232 301 422 355 296 288 233 194 274 244 221 247 254 325 398 369 324 298 255 242 256 266 235 249 43 313 410 362 310 293 244 218 265 255 228 248 3389 Total Cost 2 2018 Demand 2019 Demand (a) 2020 Demand Regular production Overtime production Subcontracting production 29 Ending Inventory Backorder Quantity - Inventory (internal) - Inventory (public storage) 0 214 0 0 0 527 0 0 Total Production Quantity o 0 0 0 0 0 0 0 0 0 29 937 129916091902 2146 2364 2629 2884 3112 3360 22983 0 0 0 0 0 0 0 0 0 29 0 0 0 0 0 0 0 0 0 0 0 $3,447,450 $725.00 29 Total Costs $3,448,175 Requirements (a) Use the 2018 and 2019 demand data to develop a forecast for 2020 annual demand (month by month) (fill in cells G6 to S6). 1 pt (b) Use your 2020 forecast data to develop an aggregate plan (SOP) using the Level Capacity strategy. Start the planning from calculating the following parameters and fill in the corresponding yellow cells in Column C. 1. What is the number of hours needed for producing one unit of Toypop? (fill in cell C18) 2. What is the regular production cost per unit? (fill in C19) 0.5 pt 3. What is the maximum overtime capacity per month? (fill in C20) 0.5 pt 4. What is the overtime cost per unit? (fill in C21) 0.5 pt 5. What is the subcontracting cost per unit? (fill in C22) 0.5 pt Assuming the inventory at the end of the planning period (i.e., end of Dec. 2020) should be as low as possible, complete the planning table above (yellow cells in G7 to T15). You need to fill formulas into Inventory/Backorder-related cells and also Total Quantity & Costs cells. 9 pts (c) After completing parts (a) and (b) above, please create a copy of this worksheet, and name the new worksheet as "Q2 Level Solver". Assuming there is no constraint on the ending inventory, use Solver to find the optimal plan using the Level Capacity strategy. There is no need to round the results, i.e., you can keep the production quantities as decimal numbers if it is the result of Sovler. 7 pts