# Excel Exam Quiz 8

## Unlock all answers in this set

question
Use Solver to find the combination of procedures that will result in the maximum possible profit per week in cell B7. Use the range name Optimal as the variable cells. Add these constraints in order: values in the name range Optimal are less than or equal to the values in the named range Max; values in the named range Optimal are whole numbers; values in the named range Procedures are less than or equal to the values in the named range MaxAllowed. Rune Solver and accept the solution.
In the Data Ribbon Tab in the Analyze Ribbon Group, you clicked the Solver button. Inside the Solver Parameters dialog, you typed B7 in the Set Objective input, typed Optimal in the By Changing Variable Cells input, and clicked the Add button. Inside the Add Constraint dialog, you typed Optimal in the Cell Reference input, typed Max in the Constraint input, clicked the Add button, and typed Optimal in the Cell Reference input. Inside the Add Constraint dialog in the Operator drop-down, you selected int. Inside the Add Constraint dialog, you clicked the Add button, typed Procedures in the Cell Reference input, typed MaxAllowed in the Constraint input, and clicked the OK button. Inside the Solver Parameters dialog, you clicked the Solve button. Inside the Solver Constraints dialog, you clicked the OK button.
Explanation: The goal of this problem is to find the combination of procedures that will result in the maximum possible profit per week. To do this, we will use Solver to find the values in the named range Optimal that will maximize the value in cell B7. We will add the following constraints to Solver: values in the named range Optimal are less than or equal to the values in the named range Max; values in the named range Optimal are whole numbers; values in the named range Procedures are less than or equal to the values in the named range MaxAllowed. We will then run Solver and accept the solution.
question
Complete the two-variable data table in cells A7:E12. The formula has been entered for you in cell A7. The substitute values in cells B7:E7 reference the original cost of goods percentage in cell B3, and the substitute values in cells A8:A12 reference the original owner withdrawal percentage in cell B4.
In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-If Analysis button. In the What-If Analysis menu, you clicked the Data Table... menu item. Inside the Data Table dialog, you typed B3 in the Row input cell input, typed B4 in the Column input cell input, and clicked the OK button.
Explanation: The two-variable data table in cells A7:E12 is used to determine the optimal mix of two assets, in this case, cost of goods and owner withdrawals, to generate the highest return. The original cost of goods percentage and owner withdrawal percentage are entered in cells B3 and B4, respectively. The substitute values in cells B7:E7 and A8:A12 are used to generate the data table. The formula in cell A7 is used to calculate the return for each combination of cost of goods and owner withdrawals. The highest return is generated when the cost of goods is at its original value and the owner withdrawal percentage is at its substitute value of 5%.
question
Add a calculated field to this PivotTable to calculate the total pay for each employee (=Salary+Bonus). Name the new field Total Pay.
In the Analyze Ribbon Tab in the Calculations Ribbon Group you clicked the Fields, Items, & Sets button. In the Fields, Items, & Sets menu, you clicked the Calculated Field... menu item. Inside the Insert Calculated Field dialog, you typed Total Pay in the Name input, typed =Salary+Bonus in the Formula input, and clicked the OK button.
Explanation: In order to add a calculated field to a PivotTable, first select the PivotTable. Then, click on the Fields, Items, & Sets" drop-down menu in the "Analyze" tab. From there, select "Calculated Field." A new window will pop up. In the "Name" field, type in the name of the new field, such as "Total Pay." In the "Formula" field, type in the formula for the field. In this case, the formula would be "=Salary+Bonus." Finally, click "OK" to add the new field to the PivotTable."
question
You opened the backstage view, clicked the Options navigation button, clicked the Add-Ins tab, and clicked Cell 9x0. Inside the Excel Options dialog, you clicked the Go... button. Inside the Add-ins dialog, you checked the Solver Add-in check box, clicked the OK button.
Explanation: The Solver add-in is a Microsoft Office Excel add-in that is available when you install Microsoft Office or Excel. The Solver add-in is an optimization tool that can be used to find optimal solutions to problems with a variety of objective functions and constraints. The Solver add-in can be used to find solutions to problems such as:â€¢ Linear programming problemsâ€¢ Quadratic programming problemsâ€¢ Nonlinear programming problemsThe Solver add-in is a powerful tool that can be used to solve a wide variety of optimization problems. If you are not familiar with the Solver add-in, it is recommended that you consult with a qualified optimization specialist before using the Solver add-in to solve a complex optimization problem.
question
Create a new scenario to reflect a change in cell B9 to value of 0.01 Name the scenario Low COLA.
In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-if Analysis button. In the What-if Analysis menu, you clicked the Scenario Manager... menu item. Inside the Scenario Manager dialog, you clicked the Add... button. Inside the Add Scenario dialog,m you typed Low COLA in the Scenario name: input, clicked the OK button. Inside the Scenario Values dialog, you typed 0.01 in the text box input, clicked the OK button.
Explanation: If the value in cell B9 changes to 0.01, this would represent a low cost-of-living adjustment (COLA). In this scenario, employees would receive a smaller raise than they might under other circumstances. This could have a negative impact on morale, as employees may feel that their efforts are not being adequately rewarded. To offset this, management may need to provide additional incentives, such as bonuses or additional paid time off.
question
Create a scenario summary report. Accept the recommended results cells.
In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-if Analysis button. In the What-if Analysis menu, you clicked the Scenario Manager... menu item. Inside the Scenario Manager dialog, you clicked the Summary... button. Inside the Scenario Summary dialog, you clicked the OK button.
Explanation: A scenario summary report is a report that provides an overview of a particular scenario. It typically includes a description of the scenario, the main actors involved, the main events that occurred, and the results of the scenario.
question
Use Goal Seek to find the value for cell C@ that will result in a value of \$550,000 for cell B15. Accept the solution.
In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-if Analysis button. In the What-if Analysis menu, you clicked the Goal Seek... menu item. Inside the Goal Seek dialog, you typed 550,000 in the To value: input, typed C2 in the By changing cell: input, and clicked the OK button. Inside the Goal Seek Status dialog, you clicked the OK button.
Explanation: Goal seek is a function in Excel that allows you to find the value of a cell that will result in a desired value for another cell. In this case, you would use goal seek to find the value for cell C@ that will result in a value of \$550,000 for cell B15. To do this, you would select the cell that you want to find the value for (C@), enter the desired value for the other cell (B15), and then click the Goal Seek" button. Excel will then calculate the value for C@ that will result in a value of \$550,000 for B15."
question
Complete the one-variable data table in cells, E3:F8 to calculate the breakeven sales point for varying owner withdrawal amounts. The formula has been entered for you in cell E3. It references the original owner withdrawal value in cell B12. The substitute values have been entered for you in cells E4:E8.
In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the Data Table.... menu item. Inside the Data Table dialog, you typed B12 in the Column input cell input, you clicked the OK button.
Explanation: The goal is to find the break-even point for varying owner withdrawal amounts. The formula in cell E3 is set up to reference the original owner withdrawal value in cell B12. The substitute values have been entered in cells E4:E8. By substituting these values into the formula, we can calculate the break-even point for each owner withdrawal amount.
question
Using the Scenario Manager, show the Low COLA scenario. Close the Scenario Manager when you are through.
In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-if Analysis button. In the What-if Analysis menu, you clicked the Scenario Manager... menu item. Inside the Scenario Manager dialog, you clicked the Scenarios: list. Inside the Scenario Manager dialog from the Scenarios: list, you selected Low COLA . Inside the Scenario Manager dialog, you clicked the Show button, clicked the Close button.
Explanation: The Scenario Manager is a tool that allows you to create and manage multiple scenarios in Excel. To access the Scenario Manager, click the Data tab, then click the What-If Analysis button. Click Scenario Manager in the drop-down menu.In the Scenario Manager dialog box, click the Add button. In the Add Scenario dialog box, give the scenario a name (e.g., Low COLA").In the Changing cells box, click the cell that contains the value you want to change for the scenario (e.g., B4).In the Scenario Values box, type the new value you want for the cell in the Changing cells box (e.g., 2%).Click OK to close the Add Scenario dialog box.To see the results of the Low COLA scenario, click the Show button.To close the Scenario Manager, click the Close button."
question
Create a Forecast Sheet based on the selected data. Use a line chart and forecast values through 2018.