• Technology
  • CFO.com | US

Spreadsheet “Worst Practices”

Here's how finance executives abuse the most-useful of computer programs — and how to do better.

The best ways to compare and review the results from different combinations of variables are (a) to copy the original data sets and calculations into a separate spreadsheet tab, and (b) to build a comparison spreadsheet tab, which presents and contrasts the original, and at least one alternative result, built with a different data set.

6) The Presentation Readiness Problem

The inability to have spreadsheet results ready for a presentation is perhaps the final common abuse committed in Figure 1. At some point in this scenario, it is fair to assume that presenting the revised quarterly revenue forecasts will be required. With this spreadsheet, though, a presentation would require reviewing each cell — a common thread from the poor practices previously listed — and reentering the data and results. A sound, but often unused best-practice is to anticipate presentation readiness. This means, in addition to segregating data from algorithms, placing meaningful column and row labels in each portion of the spreadsheet. Thus, each significant portion of the spreadsheet can be copied and placed in a presentation without retyping and reviewing for errors.

“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:

Discuss

Your email address will not be published. Required fields are marked *