Steve's Financial Modeling Tutorial

My Blog

Home
Who
Basics
Model Basics
Overview
Excel
Income I
Income II
Cashflow
Balance Sheets
Depreciation
Corporate Tricks I
Corporate Tricks II
Enron
Downloads
Model Basics

Financial models are used to PREDICT financial performance.  Therefore, they are closer to necromancy than accounting (which is more like necrophilia than golf).  Predicting the future is the goal, which tells you why it's so important to use some rational techniques.  As Yoda said, "Always in motion is the future."

Models are designed to be read left to right.  Time periods are on the x-axis moving from current (left) to future (right).  Rows of financial data travel downwards, each cell adding to the information of the row above it and to the left, resulting in summary information at the bottom. Like so:

TIP:  Blue cells.  This is a trick I got from AGWAMBA1.  Some cells are the result of calculations, some cells are real numbers that you have typed into the cell.  Cells with real numbers are called inputs and I make all inputs blue.  You can use whatever color you want.  If you do this, you'll have a much easier time reading your model, like at midnight when your investment presentation is due the next day at 8am.

TIP:  Set up your rows so that there is a constant column and an initial input. Cells for later months are simply equal to the earlier months plus your constant function.  Your constant could be a percentage increase (as in the example above). This is a useful way to set up your cells.  This way, if you want to type a specific number into a cell, such as Year 3 revenue above, all the following cells take on that new value. So you can type in a specific change and it's automatically carried through the future months or years.  Remember to make that input cell blue or you won't be able to find it later.

COOL TIP: to hide a cell without affecting its contents, set the color of the font to white.

How Far Out Should My Model Go?

Your model can be for a single quarter (3 months) or for 50 years.  I worked for many years in the timber investment business and 30 year models were standard since trees take so long to grow.

However, the value of the model's predictions beyond, say, 5 years, is very low.  People can't predict how much money they'll have in their own personal checking accounts week to week.  Do you think you can predict the financial state of a company 10 years from now?  Not bloody likely.

Should My Model Be Based On Months? Weeks? Quarters? Years?

This is related to how far out your model will extend.  It is physically difficult to read a model with hundreds of columns. So don't make a model with weekly predictions out 10 years.

Generally, don't make a weekly model for more than a year.  Don't make a monthly model for more than three years. Don't make a quarterly model for more than 5 years.

I generally think weekly models are not very valuable. There's too much error in a weekly prediction.  If you are modeling a start-up company, it might have some value, but monthly is probably your best bet.

I generally make a one- or two-year, month-to-month model for start-ups. Most analyst reports include a quarter-to-quarter projection for two years.  I don't like quarters because I have trouble thinking in "quarters" though the financial community relies heavily on quarter-to-quarter comparisons.

 

---------------------------------------------------------------------------------------------

1A guy with an MBA.
My Blog on Tumblr


 

Copyright and other other rights reserved by Steven Saltman. © 2002 and all future years until the end of time. Yes, I answer mail sent to me via ssaltman@gmail.com