A Skills Approach: Excel 2016
Chapter 7: Exploring Advanced Charts
and Graphics
Challenge Yourself 7.4
In this project, you will analyze housing trends using charting tools.
Skills needed to complete this project:
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Changing the Sparkline Type
Adding Markers to Sparklines
Applying Quick Styles and Other Formatting to Sparklines
Formatting Other Chart Elements
Emphasizing a Data Point in a Pie Chart
Formatting a Data Point or a Data Series
Inserting a Shape
Applying Quick Styles and Other Formatting to Shapes
Adding and Removing Trendlines
Changing Trendline Options
Inserting a Picture
Applying Quick Styles and Other Formatting to Pictures
Resizing and Moving Pictures
Adding a Picture to a Header
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
Important: Download the resource file(s) needed for this project from the Resources link. Make sure to
extract the file(s) after downloading the resources zipped folder. Visit the SIMnet instant help for step-bystep instructions.
1. Open the start file EX2016-ChallengeYourself-7-4. 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. On the Summary by Type worksheet, modify the Sparklines.
a. Change the Sparklines to columns.
b. Apply the Quick Style Green, Sparkline Style Accent 6 (no dark or light) (the first style at the
right in the third row of the gallery).
4. Add a marker to the Sparklines.
a. Add a marker for the high point.
b. Change the high point marker color to Blue (the third color from the right in the row of standard
colors).
1 | Page
Challenge Yourself 7.4
Last Updated 3/2/18
A Skills Approach: Excel 2016
Chapter 7: Exploring Advanced Charts
and Graphics
5. In the pie chart, change the colors for the individual data labels to match their respective data points
and then apply a bevel shape effect to all the data labels. Selecting a single data label is similar to
selecting a single data point.
a. Apply the Blue, Accent 1, Lighter 80% fill color (the fifth color from the left in the second row
of theme colors) to the data label for the Modern data point.
b. Apply the Orange, Accent 2, Lighter 80% fill color (the fifth color from the right in the second
row of theme colors) to the data label for the Condo data point.
c. Apply the White, Background 1, Darker 5% fill color (the first color at the left in the second
row of theme colors) to the data label for the Ranch data point.
d. Apply the Gold, Accent 4, Lighter 80% fill color (the third color from the right in the second
row of theme colors) to the data label for the Mediterranean data point.
e. Select all the data labels and apply the Round or Circle bevel shape effect (the first effect in the
Bevel section).
6. Emphasize the Ranch data point.
a. Rotate the pie chart 240° until the Ranch data point is at the right side.
b. Explode the Ranch data point away from the pie by 35%.
c. Apply the Round or Circle bevel shape effect (the first effect in the Bevel section) to the Ranch
data point. Hint: Do not use the Format Data Point task pane to apply the shape effect. Use the
option off the Ribbon instead.
7. In the column chart, change the color of each data point to match the corresponding color in the pie
chart. You do not need to change the color of the Modern data point.
a. In the column chart, apply the Orange, Accent 2 fill color (the fifth color from the right in the
top row of theme colors) to the Condo data point.
b. In the column chart, apply the White, Background 1, Darker 35% fill color (the first color at
the left in the fifth row of theme colors) to the Ranch data point.
c. In the column chart, apply the Gold, Accent 4 fill color (the third color from the right in the first
row of theme colors) to the Mediterranean data point.
8. Add a callout to the column chart to emphasize the Ranch data point.
a. Add a Callout: Line shape (the fifth option in the Callouts section of the Gallery) to the chart.
b. Add the text: 32% of total
c. Resize and position the callout so it is pointing to the Ranch column as shown in Figure EX 7.66.
2 | Page
Challenge Yourself 7.4
Last Updated 3/2/18
A Skills Approach: Excel 2016
Chapter 7: Exploring Advanced Charts
and Graphics
FIGURE EX 7.66
d. Change the shape style Subtle Effect – Gray, Accent 3 (the fourth style from the left in the
fourth row of theme styles).
9. Apply the Fill: Blue, Accent color 1; Shadow WordArt style (the second WordArt style) to the
column chart title.
Apply the Fill – Blue, Accent 1, Shadow WordArt style (the second WordArt style)
to the column chart title.
10. Apply the Dash dashes line option (the fourth option in the list) to the column chart gridlines.
11. Add a trendline to the 1 and 2 Bedroom Condo Sales chart on the Condo Sales worksheet.
a. Switch to the Condo Sales worksheet.
b. Add a trendline.
c. Change the trendline type to a two period moving average trendline.
Download
Resources
12. Add the SOLD picture from your local drive, network, or external media storage device, and then
apply a style, resize and move the picture.
a. Insert the SOLD picture downloaded from the Resources link.
b. Apply the Metal Frame picture Quick Style (the third style from the left in the top row of the
style gallery) to the picture.
c. Resize the picture so it is 1.5 inches wide. Do not change the aspect ratio of the picture.
d. Reposition the picture so it is located near the bottom right corner of the chart as shown in
Figure EX 7.67.
Download
Resources
13. Add a picture from your local drive, network, or external media storage device to the worksheet
header.
a. Add the RealtyLogo picture (downloaded from the Resources link) to the left section of the
worksheet header. Hint: Because this worksheet is a chart sheet, you cannot switch to Page
Layout view. You will need to use the Page Setup dialog instead. If you need help with this
method, refer to the another method box in the Adding a Picture to a Header skill.
3 | Page
Challenge Yourself 7.4
Last Updated 3/2/18
A Skills Approach: Excel 2016
Chapter 7: Exploring Advanced Charts
and Graphics
b. Preview how the worksheet will look when printed to verify that you inserted the correct picture
into the header. It should look similar to Figure EX 7.67.
FIGURE EX 7.67
Step 2
Upload &
Save
Step 3
Grade my
Project
4 | Page
14. Save and close the workbook.
15. Upload and save your project file
16. Submit project for grading.
Challenge Yourself 7.4
Last Updated 3/2/18
A Skills Approach: Excel 2016
Chapter 5: Adding Charts and
Analyzing Data
Fix It 5.6
The staff members of a private college preparatory school have been trying to analyze student data and create
what-if scenarios regarding investment decisions. Unfortunately, they’ve made a mess of this workbook. Can you
help them fix it?
Skills needed to complete this project:
Step 1
Download
start file
•
•
•
•
•
•
•
•
Sorting Data
Converting Data into Tables
Filtering Table Data with Slicers
Removing Duplicate Rows from Tables
Adding Total Rows to Tables
Creating PivotTables Using Recommended PivotTables
Inserting a Pie Chart
Changing the Chart Type
•
Analyzing Data with Data Tables
1. Open the start file EX2016-FixIt-5-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. The data in the Student Test Data worksheet has a couple of problems to fix:
a.
Format the data so the school faculty can use slicers to filter data. Use any style you want.
b. The data should be organized alphabetically by student.
c. Display slicers so data can be filtered by month, by subject, or by student or by any combination of the
three. Go ahead and display the slicers, but you don’t need to apply any filtering.
d. Find and delete the duplicate records. There should be 90 unique records.
e. The data should include a Total row to calculate the average score.
4. The PivotTable on the Student Pivot sheet should summarize the average scores for each subject for each
student by month. However, the Score field data have been added to the wrong section of the PivotTable.
a.
Move the Score field to the correct section of the PivotTable.
b. Modify the calculation to display the average score instead of the total score.
5. The PivotChart on the Faculty Pivot sheet should show the size of each department as a proportional part of
a whole. A line chart was the wrong choice.
a.
Change the chart type to a better choice.
b. Display Data Labels using the Data Callout format.
1 | Page
Fix It 5.6
Last Updated 11/20/17
A Skills Approach: Excel 2016
Chapter 5: Adding Charts and
Analyzing Data
6. The school finance committee has determined that a $28,000 facilities upgrade will be needed in 12 years.
An alumnus has donated $20,000. If invested, will it grow to be enough? That depends on the rate of
return. On the Data Table worksheet, an attempt has been made to use Excel’s data table feature to
determine the value of the investment after ten years at different rates of return. The formula in cell D6 is
correct, but something went wrong with the data table. Delete the zeros in cells D7:D30 and try again.
Even if you’re not familiar with the FV formula used in cell D6, you should be able to figure this out.
7. Save and close the workbook
Step 3
Grade my
Project
Step 2
8. Upload and save your project file.
9. Submit project for grading.
Upload &
Save
2 | Page
Fix It 5.6
Last Updated 11/20/17
Healthcare Financial Management and Economics
Week 7 Assignment – Chester Medical Center
Assignment: Developing Financial Statements
All organizations, including those in the healthcare industry, need to make money to be
profitable and survive. Financial statements, such as balance sheets, profit and loss
statements, and cash flow statements, summarize this profitability and an organization’s
overall financial status. For this Assignment, you practice developing three key financial
statements using data from the Chester Medical Center.
Chester Medical Center
Chester Medical Center is a large mid-western acute care and ambulatory facility
serving an urban city population.
Givens (in ‘000s):
Provision for bad debt expense
Cash
Patient revenues (net of contractuals)
Net accounts receivable
Ending balance, temporarily restricted net
assets
Wages payable
Inventory
Long-term debt
Supply expense
Gross plant, property, and equipment
Net assets released from temporary restriction
Depreciation expense
General expense
Transfer to parent corporation
Beginning balance, unrestricted net assets
Accounts payable
Beginning balance, temporarily restricted net
assets
Interest expense
Labor expense
Accumulated depreciation
Long-term investments, restricted
Ending balance, unrestricted net assets
$10,200
$12,600
$188,000
$15,300
$13,700
$7,800
$4,400
$39,000
$21,000
$175,000
$3,300
$10,000
$36,000
($3,300)
$160,600
$12,000
$17,000
$1,800
$105,000
$35,000
$64,800
$164,600
To prepare for this Assignment:
Examine the data from Chester Medical Center. Reflect on how you will use this data to
develop a Balance Sheet, Profit and Loss Statement, and Cash Flow Statement. Refer
to the course text for additional guidance.
The Assignment:
Complete a Balance Sheet, Profit and Loss Statement (Statement of Operations), and
Cash Flow Statement (Statement of Changes in Net Assets) using the Week 7 Financial
Statement Excel Template located in the Learning Resources.
Your Assignment is due by Day 7 of Week 7.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
