In my last blog post, I covered the what, how, and why of a financial model. In that post, I focused on mostly the theoretical side of why you should use a financial model or forecast to help achieve a business goal. But while this theoretical side might be the most important reason behind a financial model, sometimes building the damn thing can be the toughest part!
I’d first like to disclaimer that there is no right or wrong way to build a financial model. Everyone digests data differently, so what might work for me might not work best for you. That being said, I want to share a few of my favorite tips that I’ve learned over the past few years as my models and forecasts have evolved.
- Link and automate at all times. If there is one thing I can promise you, it is that you will change your assumptions at some point with your financial model. The worst thing you can do is have to make a change with a key assumption…and then make that same change in twelve other cells to keep your model working. Use formulas even for the basic math, as you want consistency with how your model works as your data changes. Referencing or linking cells in your formulas will also make your model more dynamic and reduce errors related to data quality.
- Use color coding to help organize the data. This may seem minor, but color coding your data (Links vs. Inputs vs. Formulas) will help you digest everything in the model a lot quicker. The standard formatting is blue font for inputs/assumptions, green font for links to other tabs or worksheets, and black font for all formula or output data (i.e. anything else you don’t have to manually calculate). I don’t use the green font that often, but I’m a HUGE fan of keeping my inputs blue and everything else normal black font. Pro Tip: see how few input cells you can use to drive the entire model. Keep the number low and you are a true modeling wizard!
- Spend the time on what’s important. Go into more detail or use more layers of assumptions on the key metric(s) in your model. I’ll go ahead and jump to conclusions and say this metric will most likely be revenue. Don’t spend a lot of time trying to figure out how much money you will spend on office supplies. I’d recommend taking the previous three to six months’ average amount for most expenses, do a quick sanity quick to see if that looks reasonable, and then move on. You are generally in control of expenses, unlike revenue, which requires a bit more thought. Avoid a simple __% growth assumption; instead make your revenue assumptions multi-layered.
- Example: a Sales Development Rep’s quota is 16 demos set per month —> 50% of all demos go to trial —> once in trial, 75% become new customers —> average deal size is $1,000 —> I expect $6,000 of new revenue generated assuming one Sales Development Rep.
Financial models can be very powerful tools in your business. Whether you use it frequently or infrequently, a model can be a huge help in setting and achieving goals, testing theories, and evaluating actual against predicted results.
If nothing above really stuck, feel free to make a copy of this template of a financial model that I’ve used for a bit (Disclaimer: this has been used mainly for a SaaS companies, so you might have to modify if you are not a subscription based software company). Good luck, and feel free to reach out if you have any questions about my crazy model!