New Perspectives on Microsoft Excel 2016 Instructor’s Manual 1 of 8
Microsoft Excel 2016
Module 4: Analyzing and Charting Financial Data
A Guide to this Instructor’s Manual:
We have designed this Instructor’s Manual to supplement and enhance your teaching experience
through classroom activities and a cohesive module summary.
In addition to this Instructor’s Manual, our Instructor’s Resources also contains PowerPoint
Presentations, Test Banks, and other supplements to aid in your teaching experience.
Table of Contents
Module Objectives
1
Introduction to Financial Function
Getting Started with Excel Charts
2
2
Working with Chart Elements
2
Performing What-If Analysis with Charts
3
Creating a Column Chart
3
Creating a Line Chart
4
Creating a Combination Chart
Editing a Chart Data Source
End of Module Material
Module Objectives
Students will have mastered the material in this module when they can:
• Use the PMT function to calculate a
loan payment
• Add data labels to a pie chart
• Format a chart legend
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 2 of 8
Introduction to Financial Functions
LECTURE NOTES
• Review financial functions for loans and investments
CLASSROOM ACTIVITIES
Classroom Discussion: The cost of a loan to the borrower is largely based on what three factors?
(Answer: Principal, interest, and time required to repay the loan.) What does APR mean? (Answer: The
annual interest rate on a loan, generally expressed as a percentage rate.)
Quick Quiz:
1. The _______ function can be used to calculate the payment schedule required to completely
repay a mortgage or other type of loan. (Answer: PMT)
Getting Started with Excel Charts
LECTURE NOTES
• Demonstrate how to select a data source
TEACHER TIP
Charts, also referred to in Excel as graphs, provide visual representations of the workbook data. Review
Figure 4-6 to help students visualize the look of a chart and the kinds of data that might be summarized by a
chart.
CLASSROOM ACTIVITIES
Classroom Discussion: What is an embedded chart? What are the advantages and disadvantages of the
embedded chart? (Answer: By default, a chart is inserted as an embedded chart, which means the chart
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 3 of 8
disadvantage is that an embedded chart covers worksheet cells, which might contain data and
formulas.)
Quick Quiz:
1. Each chart must have a(n) _______. (Answer: data source)
Working with Chart Elements
LECTURE NOTES
• Explain how to choose a chart style
TEACHER TIP
Pie charts are very useful for comparing values to each other in a data series. Pie charts have five elements
that are common to most charts. You can select and format any chart element individually or you can apply
a built-in style or chart layout to format all of these elements at once.
CLASSROOM ACTIVITIES
Class Discussion: What is a chart style? (Answer: A chart style is similar to a cell style or a table style in
that it formats several chart elements at one time. You can change the color and appearance of the slices
by selecting a different chart style from the Chart Styles gallery on the Chart Tools Design tab.)
Quick Quiz:
1. A pie slice is an example of a _______. (Answer: C)
A. legend
2. True/False: The color of pie slices can be changed. (Answer: True)
LAB ACTIVITIES
Divide the class into groups of two. Now that they have created their first chart, have the groups repeat
the process, only have them create a different type of chart. The purpose of this activity is to allow the
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 4 of 8
chapter and repeat the steps for creating a chart but this time, they should explore the options and make
Performing What-If Analyses with Charts
LECTURE NOTES
• Explain how charts can be a powerful tool for what-if analysis
• Describe how data can be limited using the process of filtering
CLASSROOM ACTIVITIES
Quick Quiz:
Creating a Column Chart
LECTURE NOTES
• Demonstrate how to create a column chart
TEACHER TIP
Discuss with students when it is appropriate to use a bar or column chart. A column chart displays values in
different categories as columns; the height of each column is based on its value. Related to the column chart
is the bar chart, which is a column chart turned on its side, so each bar length is based on its value.
CLASSROOM ACTIVITIES
Class Discussion: If you decide the data in a column chart would be more effective presented as a line
chart, what would you do? (Answer: After creating a chart, you can easily switch the chart to a
different chart type without having to recreate the chart from scratch. You can change its chart type
rather than creating a new chart. Clicking the Change Chart Type button in the Type group on the
Creating a Line Chart
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 5 of 8
LECTURE NOTES
• Demonstrate how to create a line chart
TEACHER TIP
Discuss with students when it is appropriate to use a line chart. A line chart compares values from several
CLASSROOM ACTIVITIES
Class Discussion: What is a line chart? (Answer: A line chart compares values from several categories
with a sequential order, such as dates and times that occur at evenly spaced intervals. The values are
indicated by the height of the line.)
Working with Axes and Gridlines
LECTURE NOTES
• Demonstrate how to edit the scale of the vertical axis
CLASSROOM ACTIVITIES
Class Discussion: What are gridlines and what do they do? (Answer: Gridlines are the horizontal and
vertical lines that help you compare data and category values.)
Quick Quiz:
Formatting Data Markers
LECTURE NOTES
• Demonstrate how to format data markers
CLASSROOM ACTIVITIES
Class Discussion: What is the purpose of formatting data markers? (Answer: It can make them stand out
and make the chart easier to understand. It can provide a way to identify different values in a chart.
Formatting the Plot Area
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 6 of 8
LECTURE NOTES
• Review what the plot area includes and how to format it
CLASSROOM ACTIVITIES
Quick Quiz:
1. True/False: Changes to the plot area are often made in conjunction with the chart area.
Creating a Combination Chart
LECTURE NOTES
• Explain how to create a combination chart
• Describe how to work with primary and secondary axes
CLASSROOM ACTIVITIES
Class Discussion: What is the benefit of combination charts? (Answer: Combination charts enable you
1. A(n) _______ is descriptive text that appears next to the axis. (Answer: axis title)
2. True/False: You cannot remove an axis title. (Answer: False)
Editing a Chart Data Source
LECTURE NOTES
• Review how to edit a chart data source
CLASSROOM ACTIVITIES
Class Discussion: How do you add a data series to an existing chart? (Answer: Select the chart, click the
1. True/False: You can modify the data source to specify which ranges should be treated as
category values and which ranges should be treated as data values. (Answer: True)
2. True/False: You can edit the data source in the Edit Data Source dialog box. (Answer: False, the
Select Data Source dialog box)
Exploring Other Chart Types
LECTURE NOTES
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 7 of 8
CLASSROOM ACTIVITIES
Quick Quiz:
1. True/False: A sunburst chart conveys information through a series of concentric rings with the
Creating Sparklines
LECTURE NOTES
• Demonstrate how create a sparkline
CLASSROOM ACTIVITIES
Class Discussion: What are the three types of sparklines? Give an example of when each sparkline might
Quick Quiz:
1. A(n) _______ is a mini chart that is displayed within a worksheet cell. (Answer: sparkline)
2. To create a set of sparklines, you first select a(n) _______, and then you select a location range
LAB ACTIVITIES
Have students practice inserting and formatting sparklines.
Creating Data Bars
LECTURE NOTES
• Demonstrate how to create data bars
• Explain how to modify a data bar rule
CLASSROOM ACTIVITIES
Class Discussion: How are data bars and sparklines similar or different? (Answer: Data bars differ from
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 8 of 8
sparklines, data bars can be used to create compact graphs that can be easily integrated alongside the
End of Module Material
• Review Assignments: Review Assignments provide students with additional practice of the skills they
learned in the module using the same module case, with which they are already familiar. These
assignments are designed as straight practice and do not include anything of an exploratory nature.
• Apply. In this type of Case Problem, students apply the skills that they have learned in the module to
solve a new problem.
• Create. In a Create Case Problem, students are either shown the end result (such as a finished Word
document) and asked to create the document based on the figure provided, or, students are asked to
create something from scratch in a more free-form manner.
• Challenge. A Challenge Case Problem involves one or more Explore steps. These steps challenge