Please help solve

17 2)Determine the week 21 product mix that will maximize the profit, using the the forecast demand for product A in week 21, and the information provided in the Product Mix Model' worksheet. Report, in Blackboard, the resulting total profit of your 20 optimal product mix, to two decimals ("If you did not get a result in 1c that you are comfortable with, then use a value of 500 for 21 Month 21 demand for A. Also, your production amounts do not have to be integers, decimals are OK] 22 23 3) Make a copy of the worksheet Product Mix Models, from 02. Add a constraint that the combined production quantities of 24 product lines c D must be at least 1,000 (to satisfy an imporant customer), and then solve agin to find the optimal product mix given 25 the new constraint. Report, in Blackboard, the resulting total profit of this solution, to two decimals 26 27 full marks require that your model has formulas that reference cells, so that if the Instructor changes a number it will change other 28 affected cells, and the worksheet must be formatted for easy navigation 29 30 31 32 33 34 35 36 37 38 39 40 41 42 C D F G H Demand in Month 21 Prot/Unt $ 540 4.245 Product une D G 720 635 495 320 895 420 4.00 4.07 $3.34 $ 5.99 $ 2.17 S 4.72S 375 7.60 OE G 12 11 Number of unies of each resource required to product one unit of each product G D 3 10 6 2 8 10 7 4 3 7 11 6 11 8 3 11 7 2 1 2. 6 11 10 10 1 11 10 11 9 4 3 2 2 3 3 1 3 0 2 4 3 4 Resources 0.24 0.22 0.22 0.09 0 Resource 10 2 2 1 6 4 6 1 4 H 3 6 8 4 12 4 0 0 0.22 5 Resource capacities for Month 21 Resources 25,000 Resource2 39,000 Resources 50,000 Resource 25,000 Resources 30,000 Resource 11,000 Resource 4,000 Resources 10,000 Resource 500 Resource10 15,000 NNON 3 0 3 0.17 3 GO Product unes D G Month 21 Production 5 26 8 30 A B D G M The XYZ production company are making a production plan for Month 21, for each of their eight product lines (A, B, C, D, E, F, G, and H). They are using demand forecasts, production data, and profitability information in a Product Mix model in order to determine the production quantities that they will produce. Your job is to help them by doing the following: 1) Compute a forecast of demand for product line A in month 21, using the worksheet titled 'Demand for A': a) use an exponential smoothing model; Initialize by making the forecast in month two equal to the demand in month one (F, = D.) and an alpha of 0.5 (a=0.5), and then compute exponential smoothing forecasts for all months up to and including month 21. Please record the month 21 forecast value, rounded to the nearest whole number in the corresponding answer box in Blackboard. ("Note - this is asking for month 21, which is in row 23). b) compute the Mean Squared Error (MSE), using all of months 5-20 (note - be sure to use the months 5-20, which are in rows 7-22 of the spreadsheet). Please record the MSE, rounded to two decimals, in the corresponding answer box in Blackboard. [You may want to make a copy of your 'Demand for A' worksheet before the next step). c) Use Solver to calibrate the value for alpha such that the MSE is minimized, and report (in Blackboard) a new forecast for month 21, rounded to the nearest whole number. This forecast will be used in part two (you can enter the value in cell D4 of the Product Mix Model' worksheet) 2) Determine the week 21 product mix that will maximize the profit, using the the forecast demand for product A in week 21, and the information provided in the 'Product Mix Model' worksheet. Report, in Blackboard, the resulting total profit of your optimal product mix, to two decimals (*If you did not get a result in 1c that you are comfortable with, then use a value of 500 for Month 21 demand for A. Also, your production amounts do not have to be integers, decimals are OK). 3) Make a copy of the worksheet Product Mix Model', from 02. Add a constraint that the combined production quantities of product lines C + D must be at least 1,000 (to satisfy an imporant customer), and then solve agin to find the optimal product mix given the new constraint. Report, in Blackboard, the resulting total profit of this solution to two decimals full marks require that your model has formulas that reference cells, so that if the Instructor changes a number it will change other affected cells, and the worksheet must be formatted for easy navigation Paste e Merge & Center $ % Cond Form E15 A H L M Monthly Demand for Product Line A 550 56 530 10 11 12 B D E Historical monthly demand for product line A Month Demand 1 S30 2 526 3 535 4 527 S 535 6 522 7 532 527 9 543 10 536 11 540 12 531 13 528 14 541 15 534 16 546 17 545 18 534 19 542 20 543 21 BB & B / 30 2 3 4 5 6 7 10 11 12 15 15 16 17 18 20 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 34 35 36 37 38 39 17 2)Determine the week 21 product mix that will maximize the profit, using the the forecast demand for product A in week 21, and the information provided in the Product Mix Model' worksheet. Report, in Blackboard, the resulting total profit of your 20 optimal product mix, to two decimals ("If you did not get a result in 1c that you are comfortable with, then use a value of 500 for 21 Month 21 demand for A. Also, your production amounts do not have to be integers, decimals are OK] 22 23 3) Make a copy of the worksheet Product Mix Models, from 02. Add a constraint that the combined production quantities of 24 product lines c D must be at least 1,000 (to satisfy an imporant customer), and then solve agin to find the optimal product mix given 25 the new constraint. Report, in Blackboard, the resulting total profit of this solution, to two decimals 26 27 full marks require that your model has formulas that reference cells, so that if the Instructor changes a number it will change other 28 affected cells, and the worksheet must be formatted for easy navigation 29 30 31 32 33 34 35 36 37 38 39 40 41 42 C D F G H Demand in Month 21 Prot/Unt $ 540 4.245 Product une D G 720 635 495 320 895 420 4.00 4.07 $3.34 $ 5.99 $ 2.17 S 4.72S 375 7.60 OE G 12 11 Number of unies of each resource required to product one unit of each product G D 3 10 6 2 8 10 7 4 3 7 11 6 11 8 3 11 7 2 1 2. 6 11 10 10 1 11 10 11 9 4 3 2 2 3 3 1 3 0 2 4 3 4 Resources 0.24 0.22 0.22 0.09 0 Resource 10 2 2 1 6 4 6 1 4 H 3 6 8 4 12 4 0 0 0.22 5 Resource capacities for Month 21 Resources 25,000 Resource2 39,000 Resources 50,000 Resource 25,000 Resources 30,000 Resource 11,000 Resource 4,000 Resources 10,000 Resource 500 Resource10 15,000 NNON 3 0 3 0.17 3 GO Product unes D G Month 21 Production 5 26 8 30 A B D G M The XYZ production company are making a production plan for Month 21, for each of their eight product lines (A, B, C, D, E, F, G, and H). They are using demand forecasts, production data, and profitability information in a Product Mix model in order to determine the production quantities that they will produce. Your job is to help them by doing the following: 1) Compute a forecast of demand for product line A in month 21, using the worksheet titled 'Demand for A': a) use an exponential smoothing model; Initialize by making the forecast in month two equal to the demand in month one (F, = D.) and an alpha of 0.5 (a=0.5), and then compute exponential smoothing forecasts for all months up to and including month 21. Please record the month 21 forecast value, rounded to the nearest whole number in the corresponding answer box in Blackboard. ("Note - this is asking for month 21, which is in row 23). b) compute the Mean Squared Error (MSE), using all of months 5-20 (note - be sure to use the months 5-20, which are in rows 7-22 of the spreadsheet). Please record the MSE, rounded to two decimals, in the corresponding answer box in Blackboard. [You may want to make a copy of your 'Demand for A' worksheet before the next step). c) Use Solver to calibrate the value for alpha such that the MSE is minimized, and report (in Blackboard) a new forecast for month 21, rounded to the nearest whole number. This forecast will be used in part two (you can enter the value in cell D4 of the Product Mix Model' worksheet) 2) Determine the week 21 product mix that will maximize the profit, using the the forecast demand for product A in week 21, and the information provided in the 'Product Mix Model' worksheet. Report, in Blackboard, the resulting total profit of your optimal product mix, to two decimals (*If you did not get a result in 1c that you are comfortable with, then use a value of 500 for Month 21 demand for A. Also, your production amounts do not have to be integers, decimals are OK). 3) Make a copy of the worksheet Product Mix Model', from 02. Add a constraint that the combined production quantities of product lines C + D must be at least 1,000 (to satisfy an imporant customer), and then solve agin to find the optimal product mix given the new constraint. Report, in Blackboard, the resulting total profit of this solution to two decimals full marks require that your model has formulas that reference cells, so that if the Instructor changes a number it will change other affected cells, and the worksheet must be formatted for easy navigation Paste e Merge & Center $ % Cond Form E15 A H L M Monthly Demand for Product Line A 550 56 530 10 11 12 B D E Historical monthly demand for product line A Month Demand 1 S30 2 526 3 535 4 527 S 535 6 522 7 532 527 9 543 10 536 11 540 12 531 13 528 14 541 15 534 16 546 17 545 18 534 19 542 20 543 21 BB & B / 30 2 3 4 5 6 7 10 11 12 15 15 16 17 18 20 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 34 35 36 37 38 39