EX2016 Guided Project 8 3 instructions PDF

Preview

Summary

Excel Assignment...

Description

USING MICROSOFT EXCEL 2016

Guided Project 8-3

Guided Project 8-3 Courtyard Medical Plaza has new worksheets for weight loss workshops. You use Solver with sample data and add scenarios and data tables to complete a sample set. You also create PivotTables to analyze dental insurance data.

Skills Covered in This Project • • • •

Create and manage scenarios. Use Solver in a worksheet to find a solution. Build a one-variable data table. Build a two-variable data table.

• • • •

Create and customize a PivotTable. Insert a slicer in a PivotTable. Insert a PivotChart. Generate Descriptive Statistics for a set of data.

This image appears when a project instruction has changed to accommodate an update to Microsoft Office 365. If the instruction does not match your version of Office, try using the alternate instruction instead.

Step 1: Download start file

1. Open the CourtyardMedical-08 workbook and click the Enable Editing button. The file will be renamed automatically to include your name. 2. Install Solver and the Analysis ToolPak. a. Select the Options command [File tab]. b. Click Add-Ins in the left pane. c. Click Go near the bottom of the window. d. Select the Solver Add-in box. e. Select the Analysis ToolPak box. f. Click OK. 3. Click the Workout Plan worksheet tab and select cell E10. Five activities are included in this plan to burn calories for weight loss. This cell includes a SUM formula. 4. Add scenarios in a worksheet. a. Click the What-if Analysis button [Data tab, Forecast group] and select Scenario Manager. b. Click Add. c. Type Basic Plan as the name. d. Click the Changing cells box, select cells D5:D9, and click OK. e. Do not edit the Scenario Values and click OK. f. Click Add to add another scenario. g. Type Double as the name, keep the Changing cells as is, and click OK. h. Change the values to 2, 2, 4, 2, 2, doubling each current value, in the Scenario Values dialog box and click OK (Figure 8-96). i. Click Close. 5. Use Solver to find a target calorie burn. a. Click the Solver button [Data tab, Analyze group]. b. Select cell E10, the cell with a SUM formula, for the Set Objective box. c. Click the Value Of radio button and type 3500 in the entry box. d. Click the By Changing Variable Cells box and select cells D5:D7. Solver finds how many times each activity should be performed to burn 3,500 calories subject to the constraints.

Excel 2016 Chapter 8 Exploring Data Analysis and Business Intelligence

Last Updated: 4/20/18 Page 1

USING MICROSOFT EXCEL 2016

Guided Project 8-3

6. Add constraints to a Solver problem. a. Click Add to the right of the Subject to the Constraints box. b. Select cell D5 for the Cell Reference box. c. Choose >= as the operator. d. Click the Constraint box and type 2. The constraint requires that the exercise be done at least twice a week. e. Click Add to add each of the five remaining constraints shown here: D5...