Excel 2016 Skills Approach – Ch 8 Fix It 8.6

A Skills Approach: Excel 2016 Chapter 8: Exploring Advanced Data Analysis

1 | Page Fix It 8.6 (Mac 2016) Last Updated 4/4/18

Fix It 8.6 (Mac 2016 Version) In this project, you will fix errors in financial data for a restaurant chain to find which locations are the lowest

performers and correct the Solver parameters to set target sales goals for next year using reasonable constraints.

You will create the missing scenario summary report.

Skills needed to complete this project: • Managing Conditional Formatting Rules

• Filtering and Sorting Using Cell Attributes

• Sorting Data on Multiple Criteria

• Using Advanced Filter

• Creating a Custom Filter

• Refreshing Data in a PivotTable

• Creating What‐If Analysis Scenarios

• Activating the Solver Add‐In

• Using Solver

• Creating Scenario Summary Reports

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.

1. Open the start file EX2016-FixIt-8-6. The file will be renamed automatically to include your name.

Change the project file name if directed to do so by your instructor, and save it.

2. If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the

workbook so you can modify the workbook.

3. There are multiple conditional formatting rules applied to cells in the Financial Data worksheet. Make

changes to the conditional formatting rules on this worksheet only.

a. There should be three conditional formatting rules: a Top 2 rule, a Bottom 2 rule, and an Icon Set

rule. Delete all the extra rules.

b. Correct the cell range for the remaining rules. All the rules should be applied to cells D5:G20.

c. Fix the remaining conditional formatting rules so if the value of the cell is in the top 2 values or the

bottom 2 values, the icon rule is not applied.

4. Now that the conditional formatting rules have been fixed, sort the data alphabetically by the value in the

Location column and then by icon in the Quarter 1 column so rows with the up arrow are at the top and

rows with the bottom arrow are at the bottom.

a. If necessary, be sure to click a cell in the data set to de-select the D5:G20 cell range or the sort will be

applied to only the selected cells and your data will be mixed up.

Step 1

Download start file



A Skills Approach: Excel 2016 Chapter 8: Exploring Advanced Data Analysis

2 | Page Fix It 8.6 (Mac 2016) Last Updated 4/4/18


b. You will need to use the Sort dialog as this requires a multi‐level sort.

c. If the sort if performed correctly, the data will be sorted by location, and then within each location, by

the icon in the Quarter 1 column.

5. An advanced filter should be performed to find any locations with a profit/loss for any quarter less than

or equal to 10,000. The results should be copied to cell I4.

a. The criteria range beginning in cell A23 is set up incorrectly. The criteria for this filter should include

values <10,000 in Quarter 1 or <10,000 in Quarter 2 or <10,000 in Quarter 3 or <10,000 in Quarter 4.

b. The previous attempt at using Advanced Filter used the wrong Criteria range.

c. The previous attempt at using Advanced Filter resulted in an incorrect Copy to range. Be sure to start

the new Copy to range in cell I4.

d. AutoFit column K.

6. Apply a custom number filter to the main data set to show only stores with a profit greater than $55,000 in

Quarter 4. You will need to select the data range A4:G20 before enabling Filter.

7. The data underlying the PivotTable have been updated since it was created. Update the PivotTable and


The data underlying the PivotTable have been updated since it was created. Update the


8. Conditional formatting rules have been applied to the data on the Financial Targets worksheet to highlight

the two lowest values for each quarter. However, the values for Quarter 4 are not highlighted. Use the

Conditional Formatting Rules Manager to fix the problem.

9. The Financial Targets worksheet has two scenarios for possible sales targets for next year. Show the 20%

Increase for All Locations scenario.

10. The Financial Targets worksheet has been set up with Solver parameters to find reasonable target sales goals

for the two worst locations for each quarter. Some locations had shown very uneven profit/loss results

from quarter‐to‐quarter. The Solver parameters include constraints to limit the new sales target for each

cell to less than or equal to the average quarterly sales for that location.

Fix the Solver parameters to find the maximum possible value for the overall average quarter income (cell G24) by

changing the values in cells C10, C15, D15, D19, E15, E19, F10, and F19 within the following constraints. It may

be easier to delete all of the existing constraints and start over.

a. For each quarter, the value of the two variable cells for that quarter must be equal to each another.

This requires a total of four constraints.



A Skills Approach: Excel 2016 Chapter 8: Exploring Advanced Data Analysis

3 | Page Fix It 8.6 (Mac 2016) Last Updated 4/4/18

b. None of the changing cells can have a value greater than half the average for that location (the location

average is calculated in column G). This requires a total of eight constraints — one for each variable cell

where the value is less than or equal to the value in the average cell for that location.

c. Fix the Solver parameters and then run Solver.

d. Keep the Solver solution and create a new scenario named Solver Results.

11. Create a scenario summary report to show the changing results for cell G24 only

12. Save and close the workbook

13. Upload and save your project file.

14. Submit project for grading. Step 2

Upload & Save

Step 3

Grade my Project

"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"