Getting Numbers to Tell the Story

In today’s high-pressure business environment, how you deliver your message is as important as the message itself. Financial professionals are called on to not only simplify complex data for management but also artfully integrate both financial and non-financial information. Such exercises can be vital to an organization’s survival because helping managers understand and remember complicated data can give your business a competitive edge.

One of the most powerful ways to present numerical data is with graphs and charts—formats that instantly translate complex collections of numbers into concise, eye-appealing statements. Fortunately, with today’s speadsheet software you don’t have to convert numbers into graphics manually. Most of the work is done with a few mouse clicks. For some charts, you may need to add a little formatting and a few formulas. The resulting graphics then can be printed or electronically transmitted.

This article teaches techniques for using four tools that transform common business data into engaging graphics using Microsoft’s Excel to demonstrate them.

HORIZONTAL BAR GRAPH

The first example is a horizontal bar graph, produced using formulas. The formulas in columns E and G in exhibit 1, below, graphically depict monthly budget variances by displaying one of the series of characters in the Wingdings font family. The number of characters displayed is determined by an “if” function.

To create this chart, enter the data shown in columns A through D; then enter the following formulas:

E4=If(D4<0,rept(“n”,-round(D4*100,0)),””)
F4=A4
G4=If(D4>0,rept(“n”,-round(D4*-100,0)),””)

Assign the Wingdings font to cells E4 and G4; then copy the formulas down the columns to accommodate all the data. Center the text in column F, and adjust any other formatting you like. Depending on the numerical range of your data, you may need to change the scale; experiment by replacing the 100 value in the formulas. You can, of course, substitute any Wingdings character you like for the n in the formulas to produce a different image in the chart.

GANTT CHART

Also known as a time and activity chart, the Gantt chart displays a timeline for each task and is an effective way to keep a project on schedule.

Excel doesn’t offer a Gantt as a chart option, but you can produce one anyway—as we have in exhibit 2, below—by using a stacked bar chart. In this example, the project comprises 12 tasks. Each task has a start date, a duration and an end date. The chart shows you at a glance the timing of critical steps that lead to successful completion of the project.

Follow these steps to create this chart:

• Start with a new workbook and enter the task data shown in exhibit 2. Column A contains the task descriptions; column B, the start date for each task; and column C, the number of days to complete the task. Column D contains formulas that determine the completion date for each task. For example, the formula in cell D4 is =B4+C4-1. Column D isn’t essential, but including it will tell you exactly when a particular task is to end.
• Create a stacked horizontal bar chart from the data in range A4:C15. The Chart Wizard probably will guess these series incorrectly, so you’ll need to set the category axis labels and data series manually. The category (x-axis) labels should be range A4:A15; the series 1 data, B4:B15; and the series 2 data, C4:C15.
• Remove the chart’s legend and adjust the chart’s height (or change to a smaller font) so that all x-axis labels are visible.
• In the Format Axis dialog box, select the following Scale options for the x-axis: Categories in reverse order and Value (y) axis crosses at maximum category. This displays the tasks in order from top to bottom.
• Access the Format Axis dialog box for the y-axis. Set the minimum and maximum values to correspond to the earliest and latest dates in your project. Note that you can enter actual dates into this dialog box. To display weekly intervals, set minimum to a Monday, maximum to a Sunday and Major Unit to 7.
• Select the data series that corresponds to the data in column B and go to the Format Data Series dialog box. Set Border to None and Area to None. This hides the first data series—the start dates—making the chart resemble a Gantt chart.
• Apply other formatting as desired. For example, you can add grid lines and a title. Exhibit 2 shows the completed chart after some touch-up work.

If you adjust your project schedule, the chart will be updated automatically. However, if you use dates outside the original date range, you’ll need to change the scaling for the y-axis.

THERMOMETER CHART

Most people are familiar with the chart named for its resemblance to a thermometer; it measures progress toward a goal—usually in percentages. In exhibit 3, below, the sample worksheet tracks daily progress toward the goal of acquiring 1,000 new customers in a 15-day period. The worksheet shows that as of day six the company has 626 new customers; the chart vividly depicts that number as 63% of the goal.

Here’s how to create the chart:

• Assign cell B20 as the goal value. Assign the formula =SUM(B4:B18) to cell B21 and to cell B23 =B21/B20, a formula for calculating the percentage of the goal attained. As you enter new data in column B, the formulas display the current results.

• To create the chart, enter the formulas listed above, along with the figure’s sample data; then select cell B23, and click the Chart Wizard button. Notice the blank row preceding cell B23. If you fail to include this blank row, Excel will use the entire data block—not just the single cell—to construct the chart. Since B23 is isolated from the other data, the Chart Wizard uses only the single cell. In step 1 of the Chart Wizard dialog, specify a Column chart and a Cluster Column subtype (the first choice). Click Next twice, and then in step 2 make additional adjustments: Add a Chart Title (Title tab), dump the Category (x) axis (Axes tab), delete the legend (Legend tab) and specify Show value (Data Labels tab). Click Finish to view the chart.

The chart needs further customization. To display the Format Data Series dialog, double-click the column. Click the Options tab, and set the Gap width to 0 (this setting instructs the column to occupy the entire width of the plot area). To change the pattern used in the column, click the Patterns tab and make your selection. The example shown here uses a gradient fill effect. Next, double-click the vertical axis to bring up the Format Axis dialog. In the Scale tab of the Format Axis dialog, set Minimum to 0 and Maximum to 1.

BUBBLE GRAPH

A bubble graph is a variation of a point or line graph in which the data points (dots) have been replaced by circles of various sizes (bubbles). The bubble graph compares sets of three values: one value is represented by the bubble’s location on the x-axis, one by its location on the y-axis and the third by the size of the bubble—proportional to its value.

Obviously, bubble graphs have an advantage over point or line graphs because they display one more variable in the same space. See exhibit 4, below.

There are several ways to enable the viewer to decode the bubbles’ information: provide a legend; show values in or near the bubbles; or include a sample bubble for size reference (as in exhibit 4). In the example, each bubble’s location represents a company’s sales/revenue information and number of products sold. The sizes of the bubbles indicate the values of the third variable: the company’s market share measured in percentages. The chart in this example shows that company G has the most products and the greatest market share. However, it does not have the highest sales.

To create this chart

• Enter the data shown in columns A through D; then click the Chart Wizard button. Select Bubble as the chart type and click Next. Set the data range as cells B4:D10, with the series set to columns.
• Click Next to go to step 3 in the Chart Wizard dialog, and you will be prompted to set the chart options. Label the chart title, x-axis and y-axis as shown. Under the Legend tab, remove the check mark for Show legend.
• Click Next again to go to step 4, and select the chart location to be an object in your current spreadsheet. Click Finish to view the chart. Then, using the mouse, resize and position the bubble graph at the appropriate location.

To complete the formatting, double-click on the y-axis, and in the Scale tab of the Format Axis dialog set the minimum to $0, maximum to $80,000 and Major Unit to $20,000. Set number format to currency, decimal places to 0 and symbol to $. Double-click on the x-axis, and in the Scale tab set minimum to 5. Apply other formatting as desired.

For example, you can label each bubble with a company name. Go to Chart Options and activate Show labels under the Data Labels tab. Then change the labels by clicking on each data label and typing in the company name. I added a sample size bubble in the upper right corner of exhibit 4 by clicking on Insert, Picture, AutoShapes; selecting an oval; then drawing a circle of the same diameter as the circle for company G with a 35% market share. I then labeled the sample size bubble for reference.

A THOUSAND POINTS OF DATA

Lack of data is rarely a problem for an accountant. Rather, the challenge is to distill those data and present them coherently. A poor presentation contains too much information or information that is not arranged in a useful way. A rich presentation often combines words, numbers and graphics. Graphics add punch; words and numbers clarify. Every second counts, and saving time is one of the best corporate contributions and career investments an accountant can make.

KEITH HERRMANN is director of financial planning at Midway Airlines, a regional airline with its hub at the Raleigh–Durham International Airport. He is on the board of the Institute of Management Accountants. His e-mail address is [email protected].

©2004 AICPA.