Spreadsheet "Worst Practices"

"Better" Practices Review (Part I)
Figure 1 clearly falls far short of the ideal for developing a spreadsheet. But what would a best-practice spreadsheet look like?

Typical spreadsheet improvements are represented in Figure 2a and Figure 2b. This type of spreadsheet design resolves many of the issues and poor practices we encountered in Figure 1. Monthly data by product are separately listed; interim calculations and assumptions are added. However, this all-encompassing approach still has two weaknesses. It is difficult to update if other products need to be added. And it includes too much detail; copying for presentation purposes is still difficult. One would have to hide the monthly columns first, to present a cleaner set of analyses.

"Better" Practices Review (Part II)
A far better approach is pictured in Figures 3a, 3b, 3c and 3d. Here, even for this "simple" request to forecast revised quarterly product revenues, we have developed a spreadsheet with four separate tabs. Tab 1, (Figure 3a) and Tab 2 (Figure 3b) would hold basic volume and price data, data source references, and assumptions. Tab 3 (Figure 3c), would hold key interim calculations and analyses. Tab 4, (Figure 3d) would summarize the key data and calculations in a presentation-ready format. This approach promotes a sound financial analysis and outcome by:

—Encouraging the separation of data, and documenting their reference sources. Keeping data separate makes it far easier to change or increment, as interim calculations produce incorrect or unexpected results. Using separate tabs for data and some interim calculations also makes efficient the timely identification of poor or inaccurate data or assumptions, and makes changing or adding data to subsequent analyses much easier. Often one is attempting to solve for a specific outcome and making it easier to modify the data makes finding the solution much quicker. Also, later use by yourself or others is not adversely affected by memory or even personal involvement.

—Making robust and useful the key interim calculations and analyses for which the data and your assumptions are being used. The spreadsheet developer would use this tab to observe and identify profound and or unusual trends, poor assumptions, etc. The developer would continue to iterate through observation, review, and analysis using tabs 1, 2, and 3 until a sound analysis has been prepared.

—Encouraging a summary design for communication and presentation. By developing a presentation-ready tab, the analyst has the discipline to synthesize the results of all key assumptions and analytical results, concurrent with the ongoing analysis, so that only key findings and results will be presented or communicated. All too often, a reply using a spreadsheet design similar to Figure 2b is offered. The audience then is forced to either "analyze" the detailed spreadsheet to determine the key findings and results, or to ask the analyst to summarize once more. Designing a summary tab at the outset encourages sound analytical skills.

"Better" Practices Summary
This scenario may seem simple, but the preparatory steps to provide the answer are clearly anything but simple. Treat the development of a spreadsheet — any spreadsheet — more like writing a term paper with footnotes and a bibliography. It is a difficult discipline to acquire and perfect. But the incredible functionality and ease-of-use of an electronic spreadsheet can lull users into a false sense of accomplishment that they will pay for down the road. Understanding that pitfall — along with the others — should provide incentives for trying to improve spreadsheet discipline, and getting better results.

Contributors Shahid Ansari and Richard Block are in the Management Accounting department at Babson College, Babson Park, Mass. Dr. Ansari is a professor of management accounting, having taught, consulted, and conducted scholarly research for many years. Professor Block, an adjunct professor of management accounting, is also a CFO Leadership partner at the executive services firm Tatum LLC.

To download an Excel file containing the spreadsheets we reference in this article, click here.

• « previous
• 1
• 2
• 3