I've been building financial models for clients regularly since 2016. This includes small businesses and businesses with 100s of millions in revenue. What makes a good template depends on several factors and nearly every case is a little bit different. I'll go over all the factors I consider when building something new.
You can browse my financial model template library here (I've got over 150 builds ready to go) and 1,000s of hours of QA, specific industry knowledge, and logic structuring have gone into them.
Also, see two more helpful videos at the bottom of the page that go through validation of business economics and population of another example case.
Who / what is the financial model template for?
First and foremost, when I am trying to build a template, it depends on the user. If I'm building something for everyone (general use) then I've got to try and balance specific features with broader usability. Also, is it an operational model with a profit / loss format? Is it a fund model or some other cash flow waterfall model? Is it some ad hoc unique model with not much precedence in structure or logic? There are all kinds for all different uses.
If I'm building something for a specific client, then I want to know what their specific needs are, such as:
- Does the client need a full three statement integration? (Meaning financial statements that are connected to all the assumptions dynamically). Financial statements would be the Income Statement, Balance Sheet, and Statement of Cash Flows. Typically, integrating a balance sheet can be time consuming if there is advanced accounting logic that is necessary to accurately project the financial forecast of the business. Mainly, accruals, inventory, payables, and receivables can take time. You've also got depreciation, accumulated depreciation, and fixed asset values.
- Interestingly, in the first 2-3 years of building financial models for clients, 90% of them just needed a general profit and loss statement and a final 'other cash flow' section and cash flow calculation. This allowed for IRR and DCF Analysis. Typically, the reason why formal financial statement integrations are needed is if you are presenting something to investors. If not, and the template is for internal use, then the user may not care about formalities and rather just see revenues, expenses, capex, debt amortization, and cash flow per period.
- What time frame is being modeled (3-year, 5 year, 10 year, 20 year)? Generally, I make templates that allow the user to select when to stop a forecast and shut off all data past a certain point. For riskier startups, a 5-year model is often used but for more steady cash flow businesses (real estate / oil and gas / franchising or licensing) you would have a 10 to 15 year model to accurately understand the impact of all aspects of the investment and operations.
- What kind of time periods need to be displayed? The most common is monthly that rolls up into annual. There could also be quarterly, but in general a template is going to have monthly periods as the lowest level view, and everything will feed off the monthly pro forma detail. This is my best practice anyway.
Once I know the high-level scope of what the main template purpose is and who is using it (timing assumptions and required financial summary outputs) then what I like to think about is the revenue assumptions.
Revenue Assumptions
- How is revenue earned exactly? When building a template, you want to figure out what the sources of revenue are and how granular the assumptions need to be in order to show exactly what factors drive revenue over time. More granular is considered bottom-up and less granular is considered top down. Depending on the specific business use case, one could be better than the other.
- For example, starting a fund that invests in franchising of laundromats, where you have possibly 10s or 100s of locations, doesn't need bottom up assumptions for every single one. You would have some high-level assumptions about location types and scale from there.
- However, if you wanted a template that was reusable for a single location, then maybe you have a schedule of all the different types of machines that are purchased, what their capacity is over time and how much of that capacity is utilized by customers over time. You would also need inputs for the price customers pay to use the machines as well as if there are other income sources besides washing / drying.
- In general, you want to figure out all the ways that revenue can be made and then design a simple input section that the user can configure which accurately represents the possible revenue over time given the assumptions. Every single financial model template I've done has a unique revenue assumption layout that is designed specifically for that business and the way it operates.
After you know exactly how revenue is earned and you have a good idea of what to build, it is time to think about expenses.
Expense Assumptions
- What are the variable costs (if any)? This means expense items that often sit in the cost of goods sold or cost of revenue section of a pro forma. When you reduce them from revenue, you get gross profit per period. If I'm building a template, I need to know how to configure these costs so they scale directly with revenues in a way that makes logical sense. For example, if I am running a SaaS company and I know that to service my users, it cost me $4 per user for up to 1,000 users and then $3 per user from 1,000 to 5,000 users, then I need to define a lookup table with that data and each month simply lookup the total users, plug that into a VLOOKUP formula, and pull the resulting cost per user for that month and multiply it by the total users in that month.
- You could also have something like repairs and maintenance of fixed assets that are being rented out. The more items you rent out, the higher your repairs and maintenance expenses are going to be.
- Anything that directly scales up and down with the level of revenue is considered a variable cost and you are likely going to need to build specific logic in the model to account these things whatever they may be.
- It is possible there are no variable costs and that is fine. It just depends on the business.
- What are the fixed costs? This is often the easiest part to build. Generally, you just define a fixed monthly cost, the start month of that cost, and then make it adjustable each year or dynamically on the actual monthly pro forma detail. The user can enter this easily and it will be reduced from gross profit to get to your Net Operating Income or EBITDA. I usually make 30-50 slots for this section, but it depends on the exact requested requirements.
Other Cash Flow Items
- How is the business financed and what are the primary capital expenditures or initial investments required? Here I would build logic for general startup costs to be entered, any cash flow offsets from cost of goods sold vs. inventory purchases, fixed asset purchases, buildings, equipment, and anything that is depreciable. Once you know the total startup costs, usually it makes sense to define how much of those costs are financed with debt vs. equity. WACC will drive off of these inputs.
- What kind of debt is used? This is one of the most complicated and cumbersome things to integrate into a financial model. It is important to understand if the financing is going to include a single loan at the beginning of the forecasted period or multiple loans throughout the life of the financial forecast. Also, what are the terms of the loan(s)? such as being interest only, balloon payments, regular amortization, or regular amortization with a balloon at some future month. Do you need to account for the potential for one ore multiple refinance events? (More common in real estate modeling templates).
- What is the timing of investor cash requirements? This is a big one if the template is being used to analyze and summarize expectations for investors. There are a couple ways to structure this part. You could simply show the final cash flow per period and any negative cash flows represent required investments while any positive cash flows represent available distributions or savings for working capital. Or you could do a cumulative cash flow position where you sum up the total of all the positive and negative cash flows each month. The absolute minimum negative cash flow position in that case is the minimum equity investment required. Doing it that way will give the user a required cash investment that keeps the business afloat after accounting for all positive and negative cash flows and the timing of them. Also, if you do it that way you are assuming any positive cash flows are potentially used to offset any investments required for negative cash flow periods if you are in the negative cumulative cash flow period still.
- Do you include a terminal value? After you have all the operating assumptions built in and all the financing / investment assumptions, it is time to figure out how what to do at the end of the forecasted period. There are two options, 1) end all activity or 2) show the sale of the business and net exit proceeds. The first option is simple to model into any template, you just have all the formulas that pull from the assumptions zero out if the month is past the defined exit month. The second option is more difficult. My approach is usually to define an exit multiple or an exit cap rate (if it is for real estate cap rate is the common approach). You want to have an input for the user to enter the multiple the business is sold for, and I always just use the trailing 12-month revenue or EBITDA. When you have this exit event, make sure that any remaining debt is automatically paid back so you have a true net exit proceed amount.
General Formatting
Being as congruent as possible across every template is important and professional. The common practice I do for formatting is as follows:
- Light yellow cells with blue text are all editable.
- White cells (no color fill) with black text are all formula cells that should not be edited.
- All gridlines removed on all tabs.
- Use light gridline formatting for areas where I want to manually have cells outlined.
- Have an index page that explains what is on each tab briefly (you want to ease users into the model)
- For number formatting, I like the Accounting format or do a number with no symbol that has a comma for 1,000s place and simply put the currency symbol at the top of each summary page / relevant area.
- I like to use white and light greys.
- Font is at your discretion, I like Calibri. It is just important to make the font the same across the entire model (size and type).
- Freeze rows and column. This is not necessary in every single tab but whenever you have rows going far down off what is visible on one screen or columns going far to the right, freeze the row and/or column headers so the model is more easily understandable.
When building financial model templates, just know that you should have a general structure for what to account for and often clients will have specific logic that they want to implement. These points above serve as a good starting point for the primary things to consider.
More In-depth Tutorials of Using These Financial Model Templates:
Tuning a Financial Model
Entering Assumptions from Scratch
If you want a jumpstart in your financial modeling, check out The Super Smart Bundle.
