Charts Tell the Real Story

Add pizzazz—and clarity—to a financial report.
BY TERRYANN GLANDON

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 .

SPONSORED REPORT

Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.

QUIZ

News quiz: IRS warning on cyberattacks and a change in pension rules

Once again, the IRS sounds the alarm about a threat from cyberthieves. See how much you know about this and other recent news with this short quiz.

CHECKLIST

Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.