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)),””) 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:
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:
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
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.
| |||||||||||||||
n 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 nonfinancial 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. 
