How to build a solid financial model for your early stage startup

As a venture capital investor, I work with a lot of smart founders starting great companies. None of them actually start by building a financial model, but sooner or later they all need one. This article is directed at those in charge of quantifying the business idea in the form of a financial model. It might help you understand how to build a financial model and guide you to build something that helps you manage your business and communicate with your investors and partners more clearly.

Why you need a financial model

I think we all agree that creating a financial model is not the most important step to begin with. However, once you get serious about setting up your venture you should build a solid financial model for various reasons:

  • Simulate your business model
    The most relevant reason for building a financial model early on is that it forces you to quantify your business idea in the unemotional world of Excel or other number crunching tools.
  • Make sure you understand your business mechanics
    Building your financial model will force you to understand the driving forces behind your business model. Once you try to put your idea into a financial model you will quickly need to answer a lot of questions to yourself (and prepare you to answer them to investors) and decipher financial modeling practice problems. This is one of the most significant steps in building a financial model.
  • Clarify what your basic assumptions are
    Building your model in a structured way will enable you to do a sort of sensitivity analysis and find out what the most sensitive assumptions of your business model are. Doing so is great because you then can check whether those assumptions are realistic (or rework them).
  • Quantify your financing need
    This step is critical in the financial modeling process. Playing around with your major assumptions will give you a feel for how sensitively your model reacts to changes in those inputs and see the impact it has on the financing needs of your business. This gives you a solid base from which to define how much money to raise.
  • Check whether or not you are on track with your business
    Last but not least your financial model should serve you as the basis for an honest reality check. Comparing real world KPIs to the assumptions you based your model on will help you see whether you are on track with your business plan. Adapting the model by feeding it with actual data generated by your business will give you a clear view on where your company is heading, what needs to change or how much money you need to raise in order to accomplish your mission.

Structuring your financial model

Learning how to build a financial model is not a tough nut to crack as long as you have the right tricks up your sleeves. Everyone who has tried to build a financial model in Excel knows that things can become very complex very fast using Excel. That´s why it is incredibly important to build your Excel model in a way that enables you to maintain an overview and understand your model also three months after you build it. Here are some hints for keeping things structured and clear:

  • Follow are clear Input – Calculation – Output structure
    and distribute the different sections over multiple sheets:
    • Input
      • One or more tabs listing all the assumed values and variables you are using within your model
      • Ideally color-coded to mark the cells as “changeable”
      • For bigger models and depending on your business model consider splitting your inputs over several tabs, e.g. one for your P&L, one for your Balance Sheet etc.
    • Calculation
      • Any calculations you are performing with data from your input section
      • This is your engine room where you model your business logic and implement dependencies and algorithms
    • Output
      • P&L, Balance Sheet and Cashflow and KPIs as a result of your calculations
      • Except for sums or subtotals no heavy calculations are done here

I would also recommend to have a separate Admin Tab for summarizing all side calculations, e.g. the timeline you use on any of the sheets or list boxes you might use for your input section.

  • Summary / Cockpit always highly appreciated
    For yourself as well as for anyone else looking at your model, you should create a summary or cockpit that displays the most important outcomes and KPIs. Ideally it would also enable you to play around with key assumptions and check for sensitivities. As always, charts and graph help viewers grasp your model and performance quickly.
  • Build an integrated model with P&L, Cashflow (and Balance Sheet)
    Make sure you have a basic understanding of the interaction of profit and loss accounting, cashflow and your balance sheet. Your financial model should definitely be an integrated model which means that your P&L is interlinked with your modeled cashflows and your balance sheet. As a simple example think of a software startup selling a monthly subscription to clients, who pay for one year in advance. While revenues are split across the twelve months of the year, cashflow will happen in the first month.

    Especially early stage startups normally do not need to focus on balance sheet planning. However, if your business is heavily focused on balance sheet accounts or you need to raise a debt facility for working capital financing you need to think about modeling a balance sheet as well.
  • Do monthly calculations and then summarize (or even smaller fractions)Even if you end up showing quarterly or yearly business results, I would urge you to do your calculations on the smallest time interval, which in in most cases is a monthly basis. Doing so will give you all the data you need to aggregate into higher timeframes or to calculate meaningful KPIs.
  • No hardcoded values
    Try to eliminate all hard-coded values from your calculations and move them to your Input section. E.g. don´t use fixed numbers for VAT, social security and other calculations, which may be hard to find in case you want to change them later on. Compile them all in one tab so there is one input to change, not many lines of formulas. No hard coding includes being careful using pivot tables and other non-dynamic elements that can be tempting to use in quick rough-and-ready calculations to get fast results.

Structure your calculations

Don´t put too many calculation steps in one formula. The calculation tabs are the engine room of your financial model. Here you really put your business logic into formulas and calculate your business results, which then build your output. Depending on the complexity of your business and the number of calculations things can get very messy and tracking errors can become a nightmare if things are not built in a clean and transparent manner. Here are my top 10 tips to keep things orderly:

  1. Building blocks
    Build small blocks for each relevant calculation and consider making use of the group function to keep an overview by collapsing sections you are not working on.
  2. Calling up input data
    For each calculation you are doing, call up the assumptions from your Input sheets and color-code them, so that you can see that those are the inputs going into your calculation. If you want to understand the calculation later on you don´t want to do jump between tabs all the time.

  3. Split calculations
    Split a more complex calculation into several sub calculations instead of putting everything into one formula. It will make things a lot easier to follow for you and everyone who tries to understand your model. As a side effect you will get intermediate results that you could use within other calculations.
  4. Recycle calculations
    Whenever you do calculations try to reuse calculation steps you already have performed elsewhere in your financial model. Call up these intermediate results for the next block of your calculations to avoid scrolling up and down the sheet.
  5. One formula per line
    Use one formula per line in Excel. If you do monthly calculations for a time period of three years you will end of with 36 columns of excel. Always use one formula from column 1 to column 36 to ensure that you understand what is going on. Don´t change the formula in column 15 for the rest of the line. In more complex models you will spend hours finding minor mistakes.
  6. Use account logic
    Wherever meaningful try to use an account logic when modeling flow sizes. For example, think of modelling the number of customers that use your service. You could try and put everything into one formula and have one line for the number of customers. Instead you could also use an account logic showing you the numbers for customers at the beginning of the period, new customers, lost customers and customers at the end of the period. By doing so you not only have a much more transparent and comprehensible calculation but also a lot of more information to work with in the future. Try to keep that logic for all relevant accounts like user numbers and all sorts of balance sheet accounts.

  7. Avoid circular references (advanced)
    First of all, disable the iteration function in Excel to make sure you become aware of circular references within you model.There are several occasions predestined for circular references, e.g. interest calculations. If circular references are unavoidable break them by using VBA.

  8. Build sensitivity into your model (advanced)
    Try to figure out early on, which inputs might be very sensitive. You could easily build in the mechanics for playing around with your inputs, without changing the input data itself or changing your calculations. As you can see from the picture above you could easily include a sensitivity factor into your calculations and put that assumption into your cockpit for fast and easy changing your model.
    These tips are sure to help you learn how to build a financial model without any hassle.

    With Sensitivity

    Without Sensitivity

  9. Check, Check and Check again
    Your model can very quickly become very complex. Therefore, you should build as many check calculations as possible. Display a master-check on your sheets to become aware of any serious errors in your model as soon as they occur. It will save you hours of work tracing back to the step at which the error came into the model. Some basic checks you should include are e.g. whether the two sides of your balance sheet are the same or if your yearly P&L equals the some of the twelve months. You should also incorporate checks that ensure the integrity of your inputs, e.g. if you split your lead generation in % over different channels you cannot distribute more than 100%.

  10. Save different versions of you model regularly
    Nothing is more frustrating than losing hours of work because Excel crashes or you made something overly complicated and want to go back to an earlier point in time. I highly recommend saving the different versions of your model regularly, e.g. counting up the version-number.
  11. Now that you are aware of the concept regarding how to build a financial model for a startup, let’s delve into other considerations.

What else needs to be considered

  • Create output-tabs for different time horizons
    It’s always better to have a bit more on hand than might actually be needed. E.g. create a monthly, quarterly, and yearly Profit & Loss Statement, Cashflow, and Balance sheet. Here it pays out to have calculated everything on a monthly basis, so you have what you need to aggregate to higher timeframes.
  • Enable your model to incorporate actual data
    For continuous use in the future, you need to build your model in a way that allows you to incorporate actual data from your accounting firm or tax consultant the future. For sure this adds a lot of complexity to your model, but I think it’s worth the effort. One of the goals of having a financial model in place is actually to build a realistic and attainable business plan based on concrete assumptions. Putting real-world data into your model as a basis for your further planning will make your model much more reliable and trustworthy to investors.

    You should build your model considering periods that are filled with actual data and build the planned periods (in the picture above marked as “budget”) on top of that. As time goes by you just roll over and a new month becomes “actual”.
  • Close to reality
    By definition every model simplifes reality. This is also true for any financial model. At the same time, the usefulness of your model depends on its accuracy. Thus try your best to model key components of your business as close to reality as possible. Some examples are:
    • Consider cashflow timing from customers as well as suppliers (e.g. do customers pay in advance or only 30 days after invoicing them)
    • Model your costumers as cohorts (e.g. to assign different retention rates to them)
    • If you have a manageable number of customers, find a way to incorporate the existing contracts into your model (e.g. to plan revenue based on concrete data)
    • Now that you are familiar with how to build a financial model, let us delve into ways to use it during fundraising.

How to use your model during fundraising

First of all, you build your model in order to plan, analyze and manage your business. Of course, once you go into fundraising your (potential) investors will ask for a financial plan or your financial model. A word of warning here: Don´t just send over your financial model and let the investor explore the file on its own. Make sure you have a chance to lead the analyst, associate, partner or whomever you speak with through your model and explain what you have built and why. Most investors will not build scenarios on their own, even if more and more are starting to do so.

In a first step you should make sure that your financial model is consistent with the amount of money you want to raise and that you can defend why, when and how much you want to raise, and what you can deliver.

Far too many founders see a financial plan as a necessary thing that needs to be submitted to the investor without making sure it is also a useful planning tool. I have seen a lot of cases where the financial model was totally off from what the founders pitched to me. This is a showstopper. Even if your startup is still very young and taking into account that no one can predict the future, it is your task to build a business plan that you can deliver on. Oldie but goldie: underpromise and overdeliver.

Presenting your (well-structured) financial model to interested investors will:

  • Assure the investor that you are managing your business in a structured and thoughtful way. Your model is used to assess the way you think and work.
  • A clear model with well-researched assumptions (or even assumptions proved by real world data) will help you defend your arguments on the case.
  • A reliable model might also open up room for negotiation regarding major funding terms including valuation because it lets you relate your discussion to numbers, multiples and sensitivities.

Make use of the financial model in reporting

Building your financial model should not be a one-time exercise, but it should be an important tool to assess and manage your business finances over time. You should set up your model in a way that allows you to feed actual numbers into it as time progresses and your planned numbers become actuals. Here are some hints and aspects to consider when using your model for reporting:

  • Accounting vs. financial model structure
    The biggest challenge for incorporating actual figures is the different structure of accounts between your financial model and the normal figures you usually get from your accounting firm or tax lawyer. E.g. the normal structure of a German BWA is quite different from what you have modelled in Excel. Here are some work-around tips:
    • Make sure you get the full picture from your accounting firm and let them send you the totals and balances list (as an Excel export) so that you can see all the booked accounts. You could easily use that list and match it with the lines of the P&L in your financial model.
    • When building your model try to anticipate whether you will be able to track the relevant items separately in your accounting. Things you have planned in separate lines in your P&L should also be booked on different accounts in your accounting.
  • KPIs vs. financials
    When you start building a financial model you will do your calculations based on a number of non-financial KPIs like conversion rates, impressions or leads generated at events. Those will not show up in your accounting. I would highly recommend tracking those systematically with other tools and to find a way to feed them into your model as well, since they are critical assumptions for accurate planning in the future. Do your best to track all your relevant KPIs from the beginning and try to build up an infrastructure to extract those data with the least manual effort possible.

Final thoughts

Modeling your business and financials in Excel might not be fun for all of you. There are several tools you could use instead and which will also give you good results. However, the exercise of building your model itself will help you to understand your business and your key assumptions better and more holistically. No model will ever be error-free or precisely model reality. But you get close enough to have a solid foundation for managing and planning your business.

We hope that this article helped you to a great extent in learning how to build a financial model. Make sure you have the right strategies up your sleeves.

Well, good luck and in case you need help, feel free to reach out to me via LinkedIn.

Leave a Reply

Your email address will not be published. Required fields are marked *