The Most Common Mistake in Forecasting an Uncertain Future

Spreadsheets are the most powerful and most used forecasting tool; however, 90% of all spreadsheets contain errors.

Proofread your spreadsheets. Time and again, project after project, we see this common mistake when we do third-party reviews. And it invalidates the fancy theories and hard data collection work. The most common mistake in forecasting is not adequately proofreading the spreadsheet.

Here are the three basic items for a proofreading checklist to help you do a better job.

Develop an influence diagram. An influence diagrams is a visual display of a problem that depicts objectives, key elements, and dependencies. Influences among the different aspects are shown by connecting arrows. Yes, there are other preliminaries like problem statement, boundary conditions, data verification, choosing the right tool. Let the influence diagram keep it simple, concise, and visual.

Establish activities to validate the three levels of quality. More specifically, the model calculates results without crashing; the model calculations do what is intended (logic errors); and, the model is well constructed and easily transferable to someone else. There are many ways to address each of these categories. The most important issue is that the review activity includes at least one activity in each category.

Specify good practices. The third aspect of the proofreading checklist is to include 5 to 10 items that an organization or analytical team agree are most important. Eight potential candidates for good practices include:

1. Include an input tab and a results tab

2. Color code cells differently for inputs, outputs, and calculations

3. In equations, use input names rather than cell references (Battleship language)

4. Input names should be one or two words

5. Lowercase letters should be used in all equations

6. Use a working model, and label the working model different from the master version

7. Specify code review tools and approaches that are used for quality assurance

8. Identify if (and where) legacy code was imported and utilized

---

JD Solomon, Inc provides services at the nexus of facilities, infrastructure, and the environment. Contact us for more information on forecasting funding demands for the future renewal & replacement of assets.

Send Me a Monthly Update from JD Solomon, Inc