How To Build Financial Models
Building a financial model from scratch can seem daunting at first, but if you take your time and do things right the first time around, it will definitely save you from a headache down the road. Try laying things out in an easy step-by-step manner so it’s easier to spot and fix mistakes early on. Below is a list of tips that will help you create a model that will not only give you an accurate view into the future, but also will impress your investors.
Financial Model Building Guide
1. Make it Clear What Your Assumptions Are
Investors like to look at the results from various sets of assumptions. To make it easier for someone to change around your assumptions, it’s always best to keep all of your inputs in one place, either at the top of your worksheet, or even on a separate tab if you are making a lot of assumptions. You should also have all of your hard coded numbers (e.g. numbers inputted directly into a cell, not a formula) in blue font, and all formulas in black (and if you want to get fancy, cells that link to other tabs can be green). Anyone reviewing your model will likely know this methodology and this makes it easier for him or her to play with your assumptions. You should also never have a hard code in the same cell as a formula – link your formula to another cell with the hard code. The more you link things, the more dynamic your model will become, making it more informative for the viewer.
2. Make Your Formulas Easy to Follow
Someone reviewing your model should be able to clearly follow your formulas and understand your methodology, so avoid putting multiple functions within a single cell. If you have a complicated equation, break out each step of the equation in a different row. If you are linking to an assumption on a separate tab, create a row where you link to that assumption. Then put the first part of the equation in the row below, and so on and so forth. The short-cut to follow a formula is “CTRL + [“ or you can see which cells on the current tab are involved in a formula by pressing F2.
3. Use Formulas to Check your Work
In order to make sure everything is adding up, try using equations. For example, add a row titled “check” at the bottom of your balance sheet, subtracting your total assets from your total liabilities and equity. If that row does not show 0’s all across, then you need to do some detective work to figure out why your balance sheet isn’t balancing. It also helps to see the exact amount you are off by.
4. Save, Save, Save
It goes without saying that you should constantly save your work, but it’s helpful if you save each version with the date of that version in the title so you can go back if you make changes that you want to undo further down the line. I also like to add a descriptor if it’s a special version of the model such as “XYZ Operating Model 10.07.14 – Board Meeting”.
5. Include Option for Various Cases/Scenarios
From the start, create a way to adjust your revenue numbers by a given percentage. Once you have completed your model, it’s easy to make adjustments and see when you would run out of cash if you were to cut expected revenues by say 50 percent.
6. Keep It Pretty
Formatting will go a very long way towards convincing investors that this is a serious model. Keep all fonts and sizes consistent (except for header rows), make sure dollar amounts have a dollar sign, get rid of digits after the decimals, use italics for any percentages, shade and border important rows, and format the print area so things print nicely on the page. These may seem trivial, but it shows attention to detail and a high standard of work, which are positive things to any potential investor.
7. Limit your Tabs
A model looks unwieldy when it has too many tabs. In the earliest stages, keep your P&L, Balance Sheet and Cash Flow Statement all on the same tab. When things get more complicated, you might want to give each statement its own tab; however, keep the same time period in the same column on each tab. For example, if January 2014 is in column C on your P&L, it should also be in column C on your balance sheet. This is with the exception of a summary tab, where you might want to show a more basic version of your P&L by year, and maybe add a few KPI’s as well.
8. Don’t Ignore Your Balance Sheet
You will put the most thought into your P&L, but your Balance Sheet is critical in providing an accurate view of your cash flows. One particularly impactful item is your Accounts Receivable balance. If it takes your customers three months to pay you, then your cash flows will be shifted by three months and you need to account for that by making the accounts receivable balance equal to the current month’s revenue, plus the two prior month’s revenue.
9. Don’t Hide Cells or Tabs
When someone is reviewing your model, it’s annoying if you’re linking to something that is hidden (which can ultimately be easily found anyway). If there is a row or column you don’t want to show on your sheet, then group it instead of hiding. You can highlight a row by hitting “SHIFT + SPACE” or a column by hitting “CTRL + SPACE” and then group by hitting “ALT + D, G, G” (this means holding ALT while hitting D then G, and then another G).
10. Use Cell Comments
This is a really helpful way to explain your assumptions without adding unnecessary text to your model. You can insert these comments quickly by using the shortcut “ALT + I,M”. Now that you know what your model is supposed to look like, the best piece of advice I can offer is to learn some keyboard shortcuts and brush up on your Excel functions. Cutting down on how often you have to reach for your mouse will significantly reduce the time it takes to build your model. Print out a list of Excel shortcuts and tape it up next to your computer. This will save time on the backend as you start building the ultimate financial model.