Financial models you can create with Excel

For all the expensive subscriptions and analysis programs, much of the work done by Wall Street analysts and managers is done on the Excel software you have on your own computer. With just a little effort, you can also create a variety of financial and analytical models, and investing the extra time and energy in learning more about macros can give you even more options.

Company Financial Models

The core of what every sell-side analyst (and many buy-side analysts) does is their collection of a company’s financial models. They are simply spreadsheets that contain (and help form) the analyst’s opinions on the likely financial results of the company in question. They can be incredibly detailed and complex, or relatively simplistic, but the model will never be better than the quality of work that goes into forming the estimates. In other words, an elaborate guess is still just a guess.

Financial models are typically constructed with the x-axis serving as time (full quarters and years) and the y-axis breaking down results by line item (i.e. revenue, cost of goods sold, etc. .). all rare to have a separate sheet generating the revenue estimate; whether it’s a per-segment basis for a large conglomerate like United Technologies (UTX) or General Electric (GE) or a simpler selling price of units sold and estimated for a smaller, simpler company .

For these models, the model builder must enter estimates for certain elements (i.e. revenue, COGS/gross margin, SG&A/sales) and then ensure that the mathematical formulas are correct. From this base, it is also possible to build sophisticated and interconnected models for the income statement, the balance sheet and the cash flow statement, as well as macros allowing investors to create “bullish/bearish/basic” scenarios. ” changeable with a click or two.

Although most would deny it, surprisingly few buy-side analysts build their own business models from scratch, in my experience. Instead, they will essentially copy models built by sell-side analysts and “test” them to see how the numbers react to various circumstances.

Assessment models

Even if you don’t build your own business models, you should seriously consider building your own valuation models. Some investors are content to use simple metrics like price-earnings, price-earnings-growth, or EV/EBITDA, and if that works for you, there’s no reason to change. Investors who want a more disciplined approach, however, should consider a discounted cash flow model.

Discounted cash flow (DCF)

DCF modeling is pretty much the gold standard for valuation and many books have been written on how free cash flow (operating cash flow minus capital expenditures at its simplest level) is the best indicator of the company’s financial performance. One row will be used to contain year-by-year cash flow estimates, while rows/columns below can contain growth estimates, discount rate, shares outstanding, and cash/debt balance.

There must be a starting estimate for “year 1” and this can come from your own company’s financial model or from sell-side analyst models. You can then estimate growth rates by creating individual estimates year by year or by using “global estimates” that apply the same growth rate for years 2-5, 6-10, 10-15, etc. You then need to enter a discount rate (a number you can calculate with CAPM or another method) in a separate cell, along with shares outstanding and net cash/debt (all in cells separated).

Once done, use the NPV (net present value) function in your spreadsheet to process your cash flow estimates and discount rate into an estimated NPV, to which you can add/subtract net cash/debt , then divide by the outstanding shares. As part of this process, remember to calculate and include a terminal value (most analysts calculate explicit cash flows over 10 or 15 years and then apply a terminal value).

The essential

Investors should remember that detailed or sophisticated modeling is no substitute for judgment and discretion. Too often, analysts rely too heavily on their models and forget to do the occasional “reality check” on their basic assumptions.

Nonetheless, building your own models can tell you a lot about what a particular business needs to do to grow, what that growth is worth, and what the street already expects of a particular business. Therefore, the relatively modest time it takes to build these models can often pay for itself many times over by leading you to make better investment decisions.

Robert D. Coleman