Publications

How to Avoid a Financial Model's Hidden Traps

Can your management team describe vision and strategy in terms of dollars and cents? Can you assess whether plans are economically viable? Is your financial model aligned with your firm’s business strategy? Will your communication of expected results withstand challenge and scrutiny?

"By failing to prepare, you are preparing to fail," noted Benjamin Franklin, and rightfully so. Poor planning can cause shareholders and creditors to lose money, management to lose credibility, and the entity to fail.

Developing a long-term, prospective financial model requires business insight, focus and prioritization of competing initiatives, project organization and collaboration, effective communication, and excellent spreadsheet design. Even then, many models fail to impress. Here are several traps and how to avoid them.

The Speed Trap
Management frequently assumes that developing a financial model is a speedy process, requiring little more than entering a few numbers into a template. This is seldom the case. Developing a model is a high-level, software-development initiative, heavily dependent on meaningful and clearly articulated business assumptions; these take time. Figure 1 shows a healthy “waterfall process” for model development.

Your most pressing exercise is to develop key assumptions about how the business will operate, an effort that is often shortchanged. Although essential, this information is frequently lacking, especially in early-stage companies. Remember the mantra "garbage-in-garbage-out" and avoid wasting time designing a spreadsheet if you cannot first articulate important strategies, opportunities or exposures, revenue and cost drivers, resource needs, capital sources and costs, and applicable business processes.

Budget adequate time between the Program and Test/Verify processes as well. Realize several iterations will likely be needed before management’s thinking coalesces and the model is vetted. Assume you won’t get a second chance if you fall short the first time.

Finally, begin the project before you face an impending deadline. It is much better to dust off and update an “evergreen” model than to hastily create one from scratch while the clock ticks.

The Boo-Boo Trap
The August 19, 2008, edition of Business Finance magazine cites academic studies that show "...upwards of 86 percent of spreadsheets contain errors," a remarkably high level.

Financial models by nature are complex and fraught with potential for mistakes. Many errors are formulaic: calculations are logically incorrect, ranges of numbers are improperly defined, formulae are over-written by predetermined results and so forth. In other cases, the relationship between financial statements is poor or nonexistent. Common examples include cash per the balance sheet not matching the cash flow statement, or net income not tying to the cash flow or balance sheet. At an even more basic level, eyebrows will raise when the "balance sheet...doesn’t."

Good spreadsheet development skills are helpful, but do not rely on them as the sole control over logical and numerical accuracy. Complete a thorough self-review and have at least one “fresh set of eyes” check key aspects of the model. Inexpensive software is available to help check for inconsistencies and mistakes. Use these tools and techniques to avoid a boo-boo.

The Linkage Trap
Figures are meaningless without context. Two linkage sins are common in many models. The first is a sin of omission, in which key model assumptions are left out or are too cryptic to be of value. Second is a sin of inconsistency, in which financial model assumptions don’t align with the business plan. Either scenario will create confusion and damage your credibility.

Instead, make sure that your packet includes a stand-alone description of key assumptions that support the model, as well as important accounting policies and the basis of accounting used (GAAP, tax, IFRS or others). Then perform a thorough review for consistency between the financial model and the business plan to ensure a consistent linkage to supporting material.

The Kool-Aid Trap
Allowing a great idea to devolve into so much hot air is a major mistake, so don’t drink too much of your own Kool-Aid. Financial models must be based on achievable objectives. This axiom especially applies to the top line of the income statement. Revenue is usually the most difficult element to get right. Significant flaws can result – mistakes that can have a pervasive impact and call into question the reliability of gross margin, variable cost, cash flow, assets and net equity figures.

Plan to spend the most research and discussion time on developing revenue assumptions. At a minimum, management must understand and explain:

total available market (primary market segments and specific target customers)
competitive factors
pricing plan
buyer profile (who will sign your purchase order and why?)
sales process and structure
distribution/market channels
other resources needed to achieve targets

Keep in mind the six P's: "prior proper planning prevents poor performance." Your effort will pay off in an improved model and, more importantly, better decision-making and execution of top-line strategies.

The Fragment Trap
Most projections consist solely of an income or operating statement. But this is an incomplete picture of the expected financial state of the company. It can mislead both internal and external users by masking material issues such as cash needs and availability, ownership interests, borrowing and collateral base and so forth.

Instead of a single fragment, model a complete set of financial statements, inclusive of the balance sheet, and statements of operations, cash flow and retained earnings. This helps ensure that parties have the information they need to make an informed decision. And even if you distribute only a subset of that information, the exercise of preparing it enhances management’s understanding of the business.

The Volume Trap
Determining the right volume to present is an art, not a science. Each user digests information differently. At one end of the spectrum are highly summarized results that provide little comfort regarding how the numbers are derived. At the other end is a data dump that overwhelms the user with detail. Neither approach is useful.

Knowing your audience is essential. As a general rule, the model should include detail down to a defined threshold of materiality (i.e., the key financial results, positions or relationships will not be materially distorted if this item was missing from the model). That does not mean, however, that you will present all the prepared information. Organize detail into meaningful groupings. Examples include: 1] summarizing monthly or weekly results into quarterly or annual totals (columnar groupings), or 2] summarizing expenses into a total expense line (row groupings). One variant is a traditional “layer cake” approach in which you present a summary dashboard and back it by supporting detail. This summarized volume of information illustrates important points, while the underlying detail informs management and inspires confidence in your presentation.

The Communication Trap
Regrettably, many models bury key insights, important values and helpful numerical relationships amongst numerous worksheets. Forcing readers to search through pages of data for a few nuggets of information is sure to frustrate them.

Make it easy. Include a summary page containing valuable information that focuses on vital matters. Educated users want to see universal figures such as the predicted cash, assets, liabilities and equity balances, as well as expected results in revenue, gross margin, net income, change in cash and EBITDA. Industry, business environment, and the specific purpose of the model should also help you determine what additional information is useful. Examples might include: income by business segment, headcount, key operating or liquidity ratios, earnings per employee and a host of other possibilities.

***********************

Avoiding these perils while preparing and presenting your prospective financial model elevates your chance for success. The outcome will be an improved model and a better prepared management team. Plus, you will have increased your credibility. Good luck!

Click here to download a .PDF version of this document.