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
PivotChart.
The data underlying the PivotTable have been updated since it was created. Update the
PivotTable.
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