Common Financial Modeling Mistakes

A working list of things that everyone assumes right, that is actually wrong.

Beta Lever / Unlever
Right out of the gate students are taught about the perils of leverage. M&M one - leverage irrelevance, M&M two - full leverage is optimal, M&M three -  optimal leverage rests on earnings volatility assumptions (ie bankruptcy risk). Let's focus on M&M three, as this is closest to reality.

Theory suggests the optimal capital structure should be found by taking the limit of WACC, where Ke changes with respect to D/A via beta, and that debt should get more expensive under higher leverage ratios. Bankrupcy costs are nonlinear, changing from 1% debt to 2% debt has no effect, going from 90% debt to 91% debt has material effect. The graph below outlines what theory might expect a typical optimal capital structure curve to look like.

Here my bankruptcy multiplier would be suitable for a pretty stable firm, perhaps a utility company. Riskier firms would have higher multiples, and the minimum would be found closer to the left axis.

Now here is the catch

This is what is approximated by the beta unlever, lever formula (that is based off of M&M two) where Beta_levered = Beta_Unlevered*(1+(D/E)(1-t)). Notice this is a linear relationship.

Now look at this.

This is what my finance professor suggests is found in practice (highly recommend taking Prof. Jarrell, his class is where the bulk of this post comes from). The main takeaway from above is that bankruptcy costs seem only to kick in at the end, and that firms actually have a wide region of leverage ratios in which to achieve their minimal WACC.

So what's the problem? Analysts are constantly using the lever unlever formula to adjust a firms beta and thus their WACC in comparable analysis. This crops up in all sorts of places, but nowhere is it worse than WACC estimation for private firms. Private firms looking to IPO will usually have very different capital structures than the firms in the industry they are about to enter. IB analysts don't have a cost of equity because the firm has no publicly traded stock, so the discount rate for the company comes nearly entirely from peers. Trouble is they adjust for the different leverage using the beta unlever, lever formula. Relatively debt light firms will be heavily penalized and relatively debt heavy firms will get a unwarranted premium. Analysts also do this in relative valuation when firms have different leverage ratios.

The simple rule should be, when it's close don't adjust, when it's not close, try not to use it. I am very interested to see if these horror stories pan out in reality when I enter the industry. My hope is that a PhD has taken the time to develop a more robust leverage adjustment ratio that accounts for bankruptcy costs, just like the CAPM now is commonly used with add-on's like size risk, liquidity risk, and country risk.


Terminal CapEx and Depreciation Assumptions
A firm must build faster than it falls apart right? It's funny how many DCF's assume this not to be the case with implied depreciation larger than CapEx in perpetuity.

This is what I take to be the best DCF framework, (again it is also what is taught by Jarrell). When you have granular information about specific revenues and expenses doing cash flows by division or product definitely has value, however the general spirit of the valuation should be as above.

I have also had some success with the CFO - Chng in Op Cash + CFI(usually negative) - Pref Divs + Pref issuance + Debt = Free Cash Flow to Equity. The major downside with this method is how much information is impounded into CFO and CFI, here the burden really lies on your economic intuition in your pro forma statements you are pulling from. The trouble with the terminal forecast is it often sticks out as unreasonable when put next to the year that proceeds it, because it needs to encompass all years after forecasting, not just the "steady state" year after the growth phase. The CFO model lends itself to be a function of projection, with a black box effect on many crucial assumptions. This is what the forecast period is for, the terminal period is altogether a different beast and needs to be delt with appropriately.

In the forecast period your margins will lie heavily on company guidance and the extension of historical trends. Your CapEx and Depreciation assumptions can be stripped out the footnotes and MD&A. Getting the forecast period right is not rocket science and most practitioners will end up with similar results.

The terminal period however sees some interesting solutions and some very wrong solutions.

Error 1
The real egregious error that can be made is to forecast NCF in the terminal period as simply the NCF of the last year in the forecast period times the perpetuity growth assumption. Here you imbed all of the forecast assumptions into the terminal period, including assuming all terminal years will have the exact CapEx/Depreciation relationship as the last year.

Error 2
Some will crawl up to NOPAT and multiply it by the perpetuity growth and then use the plowback method. The plowback method is derived from economics. A firm will need to spend it's steady state growth (real) divided by its return on investment (real also) on PPE, and the rest of its NOPAT income can be returned to shareholders. The exact derivation of the formula can be found on the internet, or in Jarrell's class notes. The reason why forecasting off of NOPAT is wrong is because you imbed the profitability and efficiency assumptions from the forecast period into the terminal period.

Solution 1
The above solution works by forecasting revenue to grow at perpetuity and then making purposeful assumptions all the way down to NCF. COGS and SG&A margin can be gleaned by looking at mature firms in the industry. Change in working capital is forecasted the same way it was in the forecast period; steady state ((A/R+Inv-A/P)/Rev) multiplied by the change in Revenue from year to year. Finally the plowback ratio is used to end at NCF and then you find perpetuity figure through the Gordon Growth Model using a mature discount assumption.

Solution 2
Not shown above, but often used is to simply forecast the terminal cash flow using multiples valuation. (NCF of the firm * AveNCF of industry/Average MVE = Terminal MVE). The trouble here is the quality of the peer multiple (quality = standard deviation within the peer group), and its propensity to change overtime (volatility or the standard deviation of the median over time). Of course as with most things in finance, it is usually a good idea to do them both, and do a sensitivity analysis on top of each method.

How do YOU DCF?

Many a young financier becomes enraptured with the power of the discounted cash flow model in their third year finance classes, and student investment funds are teeming with DCF models that do three way sensitivity, have 4 growth stages, and model 20 years into the future. However quickly students feel cheated by this tool that promises to value everything with a income statement.

At first it's the baseline figures; what should the beginning change in net working capital be, what if it's been on a negative growth path for the last 3 years. Back in 2009, baselines were really ugly; what should you do with deferred tax loss, a negative NOPAT, uncertain CapEx expenditures. Then it's growth rates, which are especially tricky for growth companies. Soon you end up with a value per share for Google at $103 and you wonder where you went wrong.

I'll outline how I approach some of the many DCF decisions and then I'll talk about where the DCF really shines - when it's used backwards.

Revenue
This is the best line item to spend the majority of your time modeling because it's the hardest to fake. Managers can push and pull on SG&A, depreciation, tax expense, and really everything else before net income, but there is very little you can do (legally) to move the top line around.


I like to use one of three approaches to model revenue growth, regressions, market share & growth, and historical. Regressions are by far my favorite, especially for resource companies. How much correlation do you think there is between Nobel Energy Corp's historical revenue and the yearly price of oil multiplied by the barrels of oil produced per year? 85%.

Yes it's a very short development period, but it tells a good story, and it makes sense (when oil price drops so does revenue). The biggest plus is with regressions you are able to use industry data to forecast the company. In this Nobel Energy example I used the EIA oil forecast (see my "must have links" post) and with this I can model out 30 years if I desire. If the company you are tackling has been pretty stable for the last 10 years (no M&A or spin offs) you'd be best to create your model in a development period and then test it on recent ex ante data to see how it performs. For example create the formula on data from 2000 to 2006 and then see how it performs from 07 to 08. Or develop through 08 and test 09 to the TTM. Most models might break through the crash period, so give your power tests some slack if you use this as your test period.

If you are valuing a consumer goods or manufacturing business you will be most interested in market share and market growth. Analysts produce a lot of research on which companies are likely to gain or lose market share, and how much the entire market is expected to be worth today and tomorrow. The gathering and aggregating of this data will be much messier than resource data, but if you are able to put it all together I'd bet your model power would be far stronger than just extrapolating on historical data.

Sometimes you need to use what you have in the company, and although it hurts there are ways to make the best of it. The big first step is to dive deep into the MD&A and scour past conference calls to get a good idea on managements expectations for growth. Any negative expectations should be taken very seriously, any very positive expectation should be taken with a grain of salt.

NWC, CapEx, Dep, EBIT
So after spending all that time on modeling revenue, must we do the same for each DCF component as well. I say we do not! Whittling to our free cash flow using the formula:

[Rev-Exp=GM-Dep-Tax=EBI+Dep+Chg_NWC-CapEx=FCF] 

We are just left to consider proportions and margins before the terminal cash flow. If management gives firm CapEx projections than you can hard code those in if you feel they will happen, otherwise look closely at the CapEx proportion to revenue over a historical period that represents the future 5/10 years. Same should be done with depreciation, NWC and the profit margin to EBIT to estimate expenses. Generally if the company is doing more business all of these items should increase roughly linearly. The exception of course is if the business is shifting from one business cycle to another. If you feel the company is losing its "core competency" or competitive edge then you should make some adjustments to the EBIT margin you assume in the future. This is tricky business and you might want to look at a historical example. For instance you might look at Microsoft's margins coming into 2000, to model Google's next 10 years.

Terminal Cash Flow
This whole calculation is a headache in itself, but extremely important as it usually makes up ~2/3 of the firm value. If the firm is steady state you could probably do the terminal cash flow anywhere from 5 to 10 years out. Theory and historical data suggests that the profit margin should squeeze that the firm will increase dividend payments (or start them) so your plowback is smaller. If the firm is growth, you might want to go through the exercise of smoothing to a mature stage over a 10 to 15 year horizon. Generally this is a messy business and it is hard not to feel like you are crudely making up growth factors when it's 2021.

Making the DCF do something useful
So after playing with your DCF and making it produce a price that fits your thesis, it's hard to think that the model is anything more than a elaborate way of making up a stock price. And honestly, without rich financial data, a professionally developed model, and a team of 7am to 11pm working analysts your not going to generate alpha. You can however make the model do something useful.

By accepting Semi Strong Form Efficiency instead of WFE we can say that the stock price is the correct price. This allows us to chose a different output variable, like equity risk premium for our DCF model (hope you soft coded it, so you can use solver). The beauty of it is, if the output variable is a 'common variable' (not firm specific) we can use several different firm DCF models to narrow down on a more accurate value. It's like triangulation in mountaineering, but in finance! For instance by modeling all of the big companies in the auto sector (and yes going through the work of regressions and margin estimates for each of them) we can turn all the models around and figure out what equity holders demand as a risk premium. Of course adjustments for outliers will be needed and you may want to weight your average to focus on 'pure' peer firms in the industry. Boil this down through all the sectors and you have completed a much more robust and market sensitive estimate of E[Rm], which we can feed back into either the CAPM or Famma French Model to make passive investment decisions. The power obviously would be found in narrow sub sectors where you are likely to be have some added information, but the method is actually a widely used practice.

What actually keyed me onto this method was a paper by Aswath Damodaran. You can find the paper here, and it nicely details some of the implications of the E[Rm] DCF solution method near the last third.

Framing an Investment Decision - A Top Down Approach

I'm no expert, but here is my take on the road map one could take to make an investment. As a preface to those financially educated, I have been educated in "chicago" style investing (efficent market), believe the market fails at Semi Strong Form Efficiency, and I follow Top Down methodology.

I'm going to start from the point that an asset manager would start at. The client service representative (or you, if your doing it alone) would have already determined your risk/riskfree split based on your risk aversion and investment horizon. The discussion from here on out will be an approach to investing in risky assets without horizon constraints and with a reasonable risk appetite constraint.

Wind at Your Back
The first point of research to be done is entirely macro, and then you get into looking at specific assets. You need to figure out where the general economy is going, where the future risks may lie, and what industries are likely to be favorable in the future, then which companies will capitalize on the environment. This involves a lot of reading, I would suggest checking out some of the research reports that are put out by banks and information agencies, there are a lot of links in my "must have links" post. The general process is:

• Where are we in the economic cycle? If interest rates are high moving investments over to low risk, debt could be very prudent. Recall that as interest rates fall bond prices rise, but you only will receive this benefit if you are in bonds before the market anticipates a shift to lower interest rates. Also bonds have two major risks, interest rate risk and default risk. Unless you plan on buying CDS insurance on your debt investments you will want to be invested in firms (or governments) that are both liquid and solvent, as interest rates usually only fall in market crashes.
• Obviously it feels as though we are at a bottom now, but we have already had a good rally. I would say that in a long term view there are still plenty bottom buying opportunities to be had, but there is a good chance we will find ourselves lower than we are today over the next 3 years.
• Where are industries in their business cycles? Oil and gas in North America both have a lot of inventory in waiting. Dot-com has been roaring ahead for a long time now and there is a lot of talk about another internet bubble forming. Paint this picture for each of the major industries by using economic data and research reports. Banks and government bodies often have very good free reports. The Economic Intelligence Unit (the data side of The Economist) is a good source as well. When you see a industry that you think is bottoming you are likely to find Value opportunities. When you see an industry that has bottomed and is growing strong you are likely to find Growth opportunities. Industries that are nearing their top or are falling will have short strategy opportunities (puts). This the first big area asset managers really add value, being able to spot a difference in the true business cycle and the one priced in by the market can make a lot of money for the long term investor. 
•  So now that you have your industries classified as value, growth, or short you can start looking at actual stocks OR you can make the appropriate plays with indexes. This could be as basic as buying the value and growth industries and not buying the short industries, or as exotic as finding ETF's that give you exposure to high Book Value / Market Value stocks in the value industry and low Book Value / Market Value stocks in growth industries. (MV/BV is a value/growth measure typically used in the Famma French Four Factor Model). 
• Finding value stocks is done many ways, and this is the second major area that asset managers make their pay check, finding the diamond in the rough. A very simple way of identifying these assets is to look at comparable multiples. A multiple is generally a market derived item divided by a financial statement item (or sometimes vice versa). Examples include: Price/Earnings Per Share, (P*Shares Outstanding)/EBIT, EV/EBIT, P/(E*G), MV/BV. Multiples are always compared to the median value of the firm's closest peers (companies generally do not form normal distributions when grouped by multiple and so means are skewed by outliers). How many you choose is a bit discretionary but usually more than 10 and less than 50 is common.  From a value standpoint you want to find companies that have low [market/book] multiples and investigate further. Sometimes these companies will have justifiably low ratios - they might be poor businesses. These can still be candidates because poorly run firms can be bought up by another firm, leaving shareholders with a sizable premium, but this can take a long time. If you find a stock that has a low multiple and it seems to be a solid business with low default risk and growth rates comparable to its peers then you might have found yourself a value stock to buy. An efficient market theory is that all firms will revert to the mean, your low multiple firm should converge to the median multiple over time, which means that either the numerator (price) will grow, or the denominator (assets/earnings) will shrink. Firms love to get bigger so usually it is the price that moves.
• Finding growth stocks is similar, look for high multiple candidates. This is less in line with EMH principles but there is research that suggests firms who have performed well in the past seem to enjoy persistence in their performance, and yield higher returns compared with their peers longer than theory would suggest. This is almost always because they hold a scarce resource their peers do not, (think Apple and Google). There is some evidence that these growth stocks although desirable, are not the true winners. The real high flying stocks will be "value with growth characteristics". Value stocks with the low multiples are usually your blue chip businesses, but everyonce in a while a low multiple company will take off as a growth stock and become a high multiple company. This transition is very profitable and finding these companies is the explicit goal of many Bottom Up investment approaches. 
• Valuation. You can do it easily with the multiple approach briefly described above by taking the firms denominator item in the multiple and multiplying it by the median multiple to find the expected Price or EV (enterprise value) if the company were to revert to the mean. The second major way is to create a discounted cashflow model of a company. I plan to discuss both of these valuation methods at length in a future post, but for now here is the coles notes of a DCF. A stock is a fractional ownership of a company. A company is made up of assets (buildings, machines ect) that make money. This money belongs to two entities, debt holders, and the companies owners. A firms worth can be considered the sum of all future cash flows it will make by doing its business discounted at the firms cost of borrowing the money to do business. This discount rate can be thought of an opportunity cost of the owners and the debt holders. Subtract debt from this sum and you are left with the value of the company that belongs to the owners; divided by the number of shares an you have a share price. DCF valuations require a lot of assumptions because they are computed off of future figures, so they are fairly useless in business that have a lot of uncertainty in their future cash flows, but can be very valuable in very stable businesses. If you price derived from valuation is higher than the market price, buy baby buy!
• Highly priced firms (highly priced relative to their firms using multiples, the actual stock price has absolutely nothing to do with it) which you suspect to fall in price are good targets for short strategies. You can identify these through multiple analysis (high multiple stocks), or just through economic intuition. The safest way to short is to buy puts on the security you think will fall. You will pay a premium up front for the opportunity to make money if the stock falls, or if volatility in the stock increases. If the stock rises in price you don't lose any more money (like you would if you actually shorted the stock). 
• The key point to all of these strategies is you have to have information that the seller of the security does not. If the stock was definitely going down, who would want to buy it? If it was definitely going up, who would sell? Most of the shares in the market are traded by institutional holders who put a lot of time into being on the right side of the trade. There is some "dumb money" in stocks, so your chances of playing someone for a fool are at least not 0, but the option market is almost exclusively written by institutions so when you are buying that put you are calling some quant jock at Goldman a fool. The good news is that historically the markets go up, so if you are net long your playing a game where everyone will win. 
• Now it's time to buy the stock. You need to put on your portfolio analysis hat now and think about how the security will affect your diversification. How much should you buy? Should you sell something that holds a similar market niche as this company to make room for it? How will it change the risk profile of your whole portfolio. You also might want to get your technical analysis hat on too. EMH theory, valuation, and economic analysis can find you good stocks to buy, but there is no denying that there are some optimal and less than optimal times to buy. Look for events in the stocks future like earnings announcements. Take a look at momentum indicators and make a play at the fools game of bottom picking. If you're even a little successful you could have just added 5% onto your yearly return. A way to avoid this task is to simply scale into the stock over a moderate period of time. The law of averages should make sure you aren't totally screwed on entry.
• Or don't buy the stock. Maybe it's not the best way for you to make money. If through your economic reading you've found a stock that you think has a great opportunity to make money over a short period of time, you could consider event play strategies. These involve calls and puts and include, bull spreads, bear spreads, straddles, strangles, and lots more. Each has their purpose and don't require that much background knowledge to understand. Pay attention to the break even points, and remember, you are trying to beat a bank, don't do it on a whim. 

If this sounds like a lot of work, maybe you should do what I plan on doing. Buy the market and become the benchmark that all these jokers are going to try to beat. Theory suggests they won't in the long run, although I'm not sure if the S&P is going to be getting up to that 8% annual return all the pension funds are predicting...