Tao of the Zentropist

January 8, 2010

Common Financial Modeling Pitfalls

Financial modeling is part art and part science, and in the hands of those inexperienced or deliberately seeking to obfuscate reality, potentially an exercise in wishful thinking if not outright deception. The truth is, nobody has a crystal ball that can unerringly predict future revenues, cash flows, expenses and other pro-forma information given the countless shifting variables which can influence these numbers, but with some foresight, analysis and careful planning, one can create a credible, defensible model that illustrates performance that is not “black swan” in nature and has a reasonable chance of being realized given the stated parameters.

We must also separate the mechanics of programming an Excel Workbook with inter-linked datasheets necessary to create desired pro-formas (e.g. the Income Statement / Profit & Loss, Cash Flow and Balance Sheets) with the planning necessary for information to logically flow throughout the model. This requires a certain amount of sophistication and understanding of how these items all relate to each other. As with any other programming, the mantra “garbage in, garbage out” is one to remember.

Following are some of the key pitfalls that I have observed in financial models, aside from incorrect calculations and formulas…

BEWARE PREDICTABLE “HOCKEY STICK” REVENUE GROWTH

It’s interesting to note the tendency that regardless of the business vertical or product or service offerings, so many revenue projections tend to follow an extremely predictable trend line; the first two to three years often show modest growth, but by Year Four revenues suddenly rocket upwards, buoyed no doubt by massive public recognition of the value of the company’s offerings and resulting in what analysts call a “hockey stick” profile.

This “default” profile more often than not reflects the need of a company seeking investment to demonstrate an attractive return on investment (ROI) within a time-frame that is not too long-term for most investment sources. Unfortunately, it is often arrived at by manipulating, consciously or sub-consciously, data in order to arrive at a desired result, rather than compiling data without regard to the desired result, and then following it to its conclusion. In other words, rather than letting the facts speak for themselves in order to form a theory, one arrives at the theory first and then cherry picks “facts” in order to prove the theory true.

To be sure, there are certainly legitimate circumstances for the “hockey stick” profile to emerge, but you must be confident that this is not the product of wishful thinking or the confluence of “perfect storm” conditions.

ASSUMPTIONS ARE NOT GROUNDED IN REALITY

More sophisticated reviewers of financial models understand that these exercises in analysis are not a Magic Eight-Ball but rather an artificial construct that is highly dependent upon the quality of data entered, the programming which drives the resulting pro-formas, and of course, the ability of the company to execute on the various aspects of the business plan which allegedly support the conclusions show in the financial projections.

All too often, those preparing the model will fail to cite sources from which data is extracted or will base projections on overly-optimistic third-party research which sometimes has an agenda of its own. While it is not always possible or practical for a company to conduct extensive primary research or have access to historical operational data to help support future projections of market conditions, customer behavior and trends, etc., it is vital for the sake of credibility in the due diligence process that the company err on the side of caution and present conservative projections which under-estimate revenues and over-estimate costs to provide more margin for error and adjustment to “reality on the ground” rather than pie-in-the-sky wishful thinking.

MISTAKING TAM FOR THE MARKET NICHE YOU ARE TARGETING

Another very common problem is that companies sometimes overstate the actual market their product(s) or service(s) are addressing, and paint an unrealistic picture of the actual market size. In marketing lingo, “total addressable market” (TAM) is a conclusion as to the size of a given market assuming no competition exists and the company can distribute its product/service without constraint. Of course, this is something of an artificial benchmark, because competition (even indirect) always exists, even for a brand new product/service, and achieving complete distribution is virtually impossible no matter the medium.

However, in reality most companies are targeting very specific niches within larger markets and when assessing market potential, they must recognize and acknowledge this. For example, if you are manufacturing men’s technical outdoor apparel, your TAM would have to be based on the industry’s definition of “technical” clothing, intended for the outdoors, and only for men. It would be irrelevant and erroneous to base your market size projections on total revenues realized from sales of newly manufactured clothing, which includes men, women, children and all of the various types of clothing within the broader category.

COSTS TO CAPTURE MARKET SHARE GROSSLY UNDERESTIMATED

Another error to avoid when projecting market share is the tendency to take “short-cuts” and make arbitrary decisions such as, “our company can capture 2% of the market by Year 3” simply because that seems like a modest slice of the pie and that 2% number nicely dovetails into sufficient revenues and net profits to make your model look attractive to investors.

Unfortunately, if you wish to be taken seriously and have a credible defense to skeptics and those playing devil’s advocate, you must work more granularly and outline the process by which you arrive at the projected market share. Even more importantly, you must be realistic about the costs involved to capture such share, which will involve marketing initiatives that cost very real dollars, even in a digital age. All too often, companies grossly underestimate the costs of marketing channels, the duration of campaigns, or assume that “word of mouth” and other free or low-cost solutions will provide the customer awareness, brand equity and penetration that they need.

CASH FLOW RELIES ON OPTIMUM PAYMENT CYCLES FOR A/R AND A/P

Another easy to overlook detail in a financial model is the assumption that is made regarding payment cycles for accounts receivable (A/R) and accounts payable (A/P) especially during challenging economic environments. There is a tendency to assume 30-day cycles for both, without taking into consideration industry practices, likely customer behavior, and the effect of tightened credit as a result of the global recession which began in 2008. Many companies naturally will be aggressive in seeking to collect on A/R, yet will drag out their A/P as long as possible to conserve cash. In order to maintain sufficient operating capital, it is critical when preparing a model that you assume that you will not be paid as quickly as you might like for your products/services, and you will still have to pay your bills in a relatively timely manner or face other consequences which may constrain operations.

CLOSING THOUGHTS ON FINANCIAL MODELING

While preparing a financial model can be daunting and time-consuming, when done properly it should provide a business with a useful tool for internal planning as well as raising outside capital, if that is the company’s intention. In my experience, financial models that project beyond 5 years out are unnecessary and so incredibly speculative as to be fairly worthless, and are usually discounted by reviewers anyway. While some modeling advocates like to provide a monthly breakdown over a 5-Year / 60-month period, this too is often unnecessary; many reviewers are happy with breaking out only Year 1 (and sometimes Year 2) by month, and some will settle for quarterly breakdowns in lieu of this.

Finally, since it is easy to make honest mistakes in constructing a model, even if operating from a template, having another knowledgeable party review the calculations and underlying assumptions is a great way to error-check and provide friendly challenges so the model can withstand less forgiving scrutiny.

If your business requires assistance in developing a financial model, or for other Business Plan development, strategic planning, marketing or project management needs, please visit Black Rock Consulting online or email us for a confidential discussion of your needs. Initial consultations are FREE OF CHARGE and WITHOUT FURTHER OBLIGATION.

Advertisements

1 Comment »

  1. A very nice Topic. Thanks alot hope you go for the detail next time!

    Comment by Franz — January 12, 2010 @ 10:16 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: