Excel Exam Quiz 8
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.
Add a timeline to the PivotTable to filter the data by values in the Date field. Use the timeline to filter the PivotTable to show only dates in April.
In the Analyze Ribbon Tab in the Filters Ribbon Group you clicked the Insert Timeline button. Inside the Insert Timelines dialog, you checked the Date check box, you clicked the OK button. You clicked the April segment.
Add a slicer to the PivotTable for the Employee field and use the slicer to show only data where the value is Sue Chur.
In the Analyze Ribbon Tab in the Filters Ribbon Group, you clicked the Insert Slicer button. Inside the Insert Slicer dialog, you checked the Employee check box clicked the OK button. You clicked the Sue Chur button.
Install the Solver add-in
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.
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.
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.
Create a Forecast Sheet based on the selected data. Use a line chart and forecast values through 2018.
In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the Forecast Sheet button. Inside the create Forecast Worksheet dialog, you clicked the Create button.
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.
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.
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.
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.
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.
