Getting Numbers to Tell the Story
2000; American Institute of Certified Public Accountants; Volume: 190; Issue: 1 Linguagem: Inglês
ISSN
0021-8448
Autores Tópico(s)Spreadsheets and End-User Computing
ResumoAdd eye-catching graphics to presentations. EXECUTIVE SUMMARY * ONE OF THE MOST POWERFUL ways to present numerical data is with graphs and charts because they can translate an enormous collection of numbers instantly into a concise, eye-appealing statement. * THIS ARTICLE PROVIDES four tools--the horizontal bar graph, the Gantt chart, the thermometer chart and the bubble graph--that can be added to spreadsheets by formatting the data in the right way and by writing a few formulas. * USING THOSE TOOLS will help you increase the impact of your reports and meet the challenge of making numbers 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 nonfinancial information. Such exercises can be vital to an organizations 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. [Exhibit 1 ILLUSTRATION OMITTED] To create this chart, enter the data shown in columns A through D; then enter the following formulas: E4=If(D4 [is less than] 0, rept(n,-round(D4* 100,0)),) F4=A4 G4=If(D4 [is greater than] 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 E 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. [Exhibit 2 ILLUSTRATION OMITTED] 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. …
Referência(s)