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
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
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
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.
Kool-Aid TrapAllowing 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
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.
Enter supporting content here