—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.