10 Commandments of Financial Modeling

From Nick Colas, Chief Strategist at Convergex, comes the 10 Commandments of Financial Modeling:

Commandment #1: Know your purpose.

The lesson: The reason to do a financial model – one that forecasts future income statements, balance sheets and cash flows – is not just to figure out if the company in question will beat or miss the next quarter. Rather, it is to understand a company’s entire business model. Where does it really make its money? How much does it take to keep the business stable? How much to grow it? And how does all this compare to other companies in the industry?

Commandment #2: Do your own work.

The lesson: It is easy enough to just pull together other analysts’ models these days; you can get revenues and earnings estimates off of Yahoo! and other sources. Look if you must, but do that AFTER you’ve done your own work. You’ll be in a better position to assess how realistic those “Street” numbers are after you finish your own model.

Commandment #3: Focus on revenues.

The lesson: The smartest client I ever had once told me, “Doing a good model all comes down to units, mix, and price.” Those are the three components of revenues, so that’s where you spend 50% of your time. DO NOT just add 5-15% to last year’s revenues and call it a day. If the company reports units, work out a revenue-per-unit. If they list types of products, do the same thing. If they give geographic breakdowns for sales, include that. Scrape every piece of revenue data you can, and include it in the model.

Once you have a historical perspective on units, mix and price, you can begin to forecast future results. Know when new products come to market, and include them with set assumptions for adoption rates, price and market share. Long range forecasting (past 1 year) is an exercise in educated guesswork, but the more you systematize your guesses the more quickly you’ll know when you are wrong (which you will be… that’s ok).

Commandment #4: Nothing happens in a vacuum.

The lesson: Every public company has competition. Even when modeling one company, keep a weather eye for what your projections imply about industry-wide market share and growth. Special note for high-growth tech and health care models: how large is the addressable market for new offerings? And how are you forecasting price/unit?

Commandment #5: Tear apart the cost/margin structure.

The lesson: Costs are stickier than revenues, so they should “Just” take 25% of your time to dissect. The point you are after: what are marginal profits for the last “Unit” of sales? How much of the cost structure is fixed, versus variable? Which products have better profit margins? (Hint: usually the ones that the company advertises more heavily and features in their annual report/analyst days.)

Remember: fixed cost structures don’t change much quarter-to-quarter, even if revenues do. Model accordingly.

Commandment #6: Yes, you have to do a balance sheet and cash flow model every time.

The lesson: While earnings per share may get the limelight, remember Commandment #1. We’re doing all this work to understand business models. Every commercial enterprise uses various kinds of capital: working capital, fixed capital, acquired capital, intellectual capital etc. To the degree to which cash flow and earnings diverge, it will only become apparent if you model all three financial statements. That’s the oldest red flag and the book, and you’ll miss it if you just model earnings. This is the final 25% of your time.

And yes, you have to tie together the three financial statements so they change as you alter your inputs. You want to know what happens to capital, cash flow and earnings when you change a revenue assumption.

Commandment #7: Always model two calendar years by quarter…

The lesson: This is a pain but it is critical. Why? As with so much of this whole exercise, very few people do it anymore. But here’s a little secret: four quarters make a year, and when you have 2 calendar years of quarters in the model it is easier to see when you have to adjust estimates for annual earnings. Earnings reports are information, and the more granular your forecast (in the form of quarterly rather than just annual results) the more data you can glean and use.

Commandment #8: … And 5 years ahead.

The lesson: No, your 2020 earnings estimate isn’t worth much. The chances the company hits it are basically zero. But working through a long run financial model forces you to consider important factors like “Will this company need outside capital to grow”, “how long can it hold its current competitive advantage, and why”, and “what is the return on new capital invested, and why?”

Commandment #9: Now, make it simple.

The lesson: As hard as the first eight commandments might sound, the actual heavy lifting starts now. Summarize what the financial model tells you in one paragraph. Where does the company actually make money, and is that income stream growing or shrinking? How much risk is there to your revenue model, either from competition or cyclicality? If you had to own the company for 5 years (out to the end of your financial model), how confident would you be that your estimates would prove correct?

Commandment #10: Do as much as you can.

The lesson: If this all seems like too much work, that’s sort of the point. By developing a detailed financial model, you are doing a lot of the heavy lifting necessary to learn a company in a systematic way. The model actually helps you streamline that process while making sure you don’t miss anything too obvious

~~~

Convergex is an agency-focused global brokerage and trading related services provider with one overarching mission: Solve the financial industry’s toughest challenges for our institutional investor clients.

Nick Colas, Chief Strategist
Convergex
ncolas -at-convergex.com
212-448-6095