Key to
Instructions
To help
readers follow the instructions in this
article, we use two different typefaces.
Boldface type is used to
identify the names of icons, agendas and
URLs.
Sans serif type indicates
commands and instructions that users
should type into the computer and the
names of files.
|
 ne of the most critical tasks of
accountants is translating those dry columns of
financial numbers into information that management
can quickly comprehend. One effective tool is the
chart, which graphically demonstrates what the
numbers mean. But, as good as charts are, the
traditional ones have an inherent weakness. For
example, if you want to show how spiraling
marketing costs, spent to boost sales, will
deplete profits, then the traditional chart
probably lacks the punch you’re trying to achieve.
In other words, if the sales numbers are large and
growing fast while the profits are relatively
small and shrinking, a conventional chart of their
relationship will not illustrate your point.
The good news: That weakness is
correctable. With a few adjustments, you can
transform a lackluster chart into one that
commands attention. Follow along and see how.
| Consider the chart in exhibit 1
, at right, which compares soaring
Sales (the blue line)
with ebbing Operating income
(the pink line). While it’s clear
that sales are rising, that slightly
dipping pink line certainly isn’t making
the point that profits are collapsing.
Fortunately, Excel, which can easily
convert tables into charts, has special
ways to address such a situation. To
illustrate we’ll create a chart with the
underlying spreadsheet data shown in
exhibit 2 , below. You may want to
duplicate the chart and work along with
me. | |
Once you’ve created the worksheet, hold down
the Ctrl key while highlighting the header (row
4), Sales (row 5) and
Operating income (row 9). When
highlighted, they are shaded gray. Click
on Insert on the toolbar, then
Chart to bring up the
Chart Wizard ( exhibit 3
, below). Select the Custom Types
tab and scroll down to Lines on 2
Axes .
|
Click twice on Next
to get to Step 3
and then click on the
Titles tab. Enter
Snick’s Snack Sales as the Chart
title . Enter Sales in the
Value (Y) axis field
and Operating income in the
Second value (Y) axis
field ( exhibit 4 ,
below). The default placement of
the legend is to the right of the chart.
To move it to another location in the
chart, click on the Legend
tab, click on another selection
and wait a few seconds for the legend to
appear. You can test each placement
until you are satisfied with the results
( exhibit 5 , below right). Now
press Next . |
|
To place the finished chart on the same
worksheet as the data, simply click on
Finish . If you wish to place
it on a separate sheet, enter a new worksheet
name, such as 2003 Chart , and then click on
Finish ( exhibit 6 ,
below).
|
CHANGE LOCATION
If you want to change its location
after you have placed the chart on the
data sheet, right-click inside the chart
once and then click on Location
from the menu. This will bring
up the Chart Location
window. But if you originally
had placed the chart in its own
worksheet and now you want to move it,
you don’t have to click on the chart;
simply go to the menu bar at the top and
select Chart and then
Location to get the
Chart Location
window. | |
| To
change a line color, right-click on it and
choose Format Data Series
( exhibit 7 , above).
Click on the Patterns tab
and change the line color from
Automatic and the
Foreground marker color
to the desired colors. Increase the
thickness ( Weight ) of
the line by clicking on the down arrow and
by selecting a heavier line. Then click on
OK . You also can
add data points for emphasis along the
chart lines ( exhibit 8 , at
right). To do that right-click on the
Sales (blue) line and
choose Format Data Series
. Select the Data
Labels tab and check the
Value box. |
|
| | | If necessary, you can
reposition the data labels by
right-clicking on one of them. Then
select Format Data Labels
and click on the
Alignment tab and
change the label position.
| | |
| | To enhance the
emphasis, you also can change the scale on
one or both Y-axes. Compare the difference
between exhibit 8 and exhibit 9
, at right. To do that, right-click on
the primary (left vertical) Y-axis and
select Format Axis from
the shortcut menu. Choose the
Scale tab and change
Minimum from the
default of 0 to
20,000 . As you can
see, you can transform numbers into
effective, easy-to-understand pictures.
So get familiar with Excel’s chart
functions to send the message you want.
Resource
TECH Conference
May 2–5, 2004
Venetian, Las Vegas
| |
|
TERRYANN GLANDON, CPA, PhD, is an assistant
professor of accounting at the University of Texas
at El Paso. Her e-mail address is
tglandon@utep.edu . |
Get Clients Ready for Tax Season