(answered) – Can someone please guide me through this project? I’m stuck in

Posted on : by : admin

$ 21.00

(answered) – Can someone please guide me through this project? I'm stuck inDescriptionSolution downloadThe QuestionCan someone please guide me through this project? I'm stuck in step #5USING MICROSOFT EXCEL 2013Independent Project 8-4 (Mac 2016)Independent Project 8-4 (Mac 2016 Version)Boyd Air is requesting management to use solver for a manufacturing analysis decision between small and large planes.They would like to know how many of each to produce in order to maximize profit. Please include all three reports, aPivotTable, and a PivotChart to help explain your results.Skills Covered in This ProjectInstall the Solver add-in.Create SUMPRODUCT formulas.Set the Solver parameters.Create Answer, Sensitivity, and Limits reports.Save as Solver scenario.Create a scenario.Display a scenario.Create a scenario summary report.Create a scenario PivotTable report.Create a PivotTable.Edit value field settings.Create a Chart.Insert a slicer.Format a PivotTable.Format a Chart.Format a slicer.Step 11.Open the Planes-08 start file. Click the Enable Editing button.2.The file will be renamed automatically to include your name. Change the project file name if directed to doso by your instructor, and save it.3.4.Downloadstart fileInstall the Solver add-in if necessary. Add-Ins are located in the Tools menu.Add the SUMPRODUCT formulas.a.b.Enter the following formula in D10: =SUMPRODUCT(B10:C10,B13:C13)c.5.Enter the following formula in D9: =SUMPRODUCT(B9:C9,B13:C13)Enter the following formula in F13: =SUMPRODUCT(B6:C6,B13:C13)Set Solver parameters to maximize profit by changing the number of planes produced without using moreresources than are available. The number of planes produced must be a whole number.a.In the Set Objective box, type F13.b.c.Use the cells B13:C13 for the Variable Cells.Set D9:D10 <= F9:F10 for the firstconstraint and B13:C13 = int for thesecond constraint. Int is a selectionused to force the values to be integersor whole numbers. Hint: choose intfrom the operator drop-down arrowbutton.Choose Simplex LP for the Select aSolving Method and check the MakeUnconstrained Variables NonNegative box.Click Solve. A solution of 10 smallplanes, 12 large planes, and $48,421 intotal profit displays (Figure 8-81).d.e.6.Save the Solver scenario. Click the SaveScenario button and Type OriginalResources as the scenario name.Excel 2013 Chapter 8 Using Decision-Making Tools and XMLLast Updated: 11/16/15 Page 1USING MICROSOFT EXCEL 2013Independent Project 8-4 (Mac 2016)7.Create Answer, Sensitivity, and Limits reports for the Solver data (Figure 8-82).8.Create a new Solver scenario called Max Resources by changing F9 to 7500 and F10 to 2000 onthe Solver sheet.a. Don?t forget to re-solve the problem before saving the scenario.b. The results change to 15 small planes, 13 large planes, and $62,829 for profit.9.Display the Original Resources scenario.10. Create an airplane production scenario summary report using D9, D10, and F13 from the Solver sheet for theresult cells (Figure 8-83).11. Create an airplane productionscenario PivotTable report usingD9, D10, and F13 from theSolver sheet for the result cells.12. Edit Value Field Settings of B3 inthe Scenario PivotTable sheetto display Materials, C3 todisplay Labor Hours, and D3 todisplay Total Profit. Adjust theformat of column D to displayCurrency format for values.8-83 Excel 8-4 Scenario Summary worksheet completed (Mac 2016)Excel 2013 Chapter 8 Using Decision-Making Tools and XMLLast Updated: 11/16/15 Page 2USING MICROSOFT EXCEL 2013Independent Project 8-4 (Mac 2016)13. Create a Clustered Bar C