A reader emailed me to say "there is a lot to learn about financial modeling". This got me thinking about why people find it so hard to build financial models. I am going to do a series of posts on how to build models more easily. Probably two major points per post. The first thoughts follow:
- Build a Re-usable Template Every financial model has an income statement, balance sheet, cash flow statement and a summary tab. If you use generic titling for each part of the model, it easily becomes reusable. (See Startup Excel Model to see an example of the generic titles to use on the income statement, balance sheet and cash flow statement.) By re-using the template, you save time for the important part of the model. If you have six classes of long term debt to model, build a separate debt schedule and link the total back to the long term debt cells on the template balance sheet. Do not change the balance sheet template to include six rows-one for each class of debt-because then you have to also change the cash flow and summary tabs in six places. When you build the template build it to encompass most situations. For example, in long term liabilities have a separate row each for senior debt, mezzanine debt and due to shareholders. (Remember, putting in a zero value also provides information.) If you have to build models for very different industries, such as banks and manufacturers, you may need to build two generic templates. In summary, use schedules to detail information and link back to the generic template.
- Build One Period Models Models should be built with the idea that the first period is representative of the whole time period modeled. To do this every cell in the first period (except totals) links back to the assumptions page and every subsequent period is just a copy>paste of the previous period. For example, if you have seasonal revenues, build a seasonality index on the assumptions page and then in the revenue detail schedule add a row for the seasonality factor. Use Vlookup or Hlookup to properly link to the seasonality on the assumption page. If you only pay taxes quarterly, then use an indicator (0,0,1,0,0,1 etc.) to indicate when the payments are made. To create the indicator, use the MOD command and count the columns [=IF(MOD(COLUMN(),3)=0,1,0)]. (This command counts the columns and puts in a 1 every third column.) There are many other tricks to creating one period models, but it is really just a matter of logic. "This one period logic" also applies if you have multiple economic units--say stores. Each unit would have its own tab for P&L and maybe balance sheet to show individual unit economic contribution. Now if you build the first unit's first period model correctly using the assumptions tab, you can copy>paste the first period model not only across multiple time periods but also to multiple economic units. To clarify, what I just said was you could build a 100 store, 120 period model by just building the model for the first store, first period correctly. (Worst case you might have to enter the store name or number on each of the hundred tabs.)
More tips to follow. Let me know if this is helping you aspiring Excel modelers.