Imagine you are standing on your ideal plot of land, getting ready to build the house of your dreams. Naturally, you are excited and motivated to get quickly to the process of building. However, would you start pouring concrete and putting up walls without a blueprint? How would you know how much to spend on the materials or labor? How much do you need to complete the project? Will you run out of money this year?
When people say they don’t prioritize or have a financial model, I think of the person above, building a house without a blueprint. A financial model is a strategic plan that substantiates and justifies business decisions and provides clarity on the past and future of your business.
I’ve been building models for years as both an internal and external resource. My goal here is to explain five of the biggest modeling shortfalls I’ve seen, both as an operator and a consultant. Many of these points are lessons I’ve learned from past personal mistakes, humiliations, and generous mentors.
Bottoms-Up Bulk
Businesses are complicated, but that doesn’t mean your model needs to be right out of the gate. It can be overwhelming to model out your business for the first time. Many people get trapped in the weeds of minutiae and end up including unnecessary bulk in their models in order to justify the time they spent wading through everything.
My advice is to model from the top-down. If you have historical financials, use that as a starting point and recreate the actuals using the KPIs you can actualize. When in doubt, keep the model simple. My rule of thumb: If you cannot validate (i.e. actualize) a financial driver, do not model it.
Rabbit-Hole Calculations
One of my biggest pet peeves is clicking in a cell and seeing a web of logic leading me to various sheets and (gasp) external excel workbooks. Remember that the model is not just for you, so the logic needs to be user friendly. Tease out the assumptions rather than hiding the math in one cell. This advice at first glance may appear to contradict my earlier advice, but I want to stress that consolidating multiple factors in a single cell will make the model prone to errors and will cast doubt on the logic itself.
Black Text, Black Hole
Excel offers way more colors than one should ever use. However, mild color coding can help you and the model’s users navigate and easily digest a sheet. My preferred color usage is as follows:
- Gray fill/white text: headers for dates, column descriptors
- Royal blue text: hard coded assumptions/actuals
- Purple text: data sourced from an external sheet
- Black text: internal calculations within a sheet
- Red: reserved for errors or warning signs (negative cash)
Keep the colors consistent. The result should allow a reader to understand the basic dynamics of a worksheet in a single glance.
Embrace Laziness
Updating a model with actuals (“actualizing”) is one of the biggest pain points for my clients. I can understand why it is a dreaded task, guaranteed to be filled with new GL account landmines, projection misjudgments and the dreaded mystery variance of $1. Sadly, there is no avoiding these snags. However, the following tips make the process as easy as possible:
- Actuals: Export a monthly P&L and balance sheet report for at least one year each month. Before you update, check to make sure there are no prior period adjustments; this is usually why the balance sheet doesn’t balance. Make the cash flow report dynamic to the balance sheet and net income rather than pasting the report in from your system. In Quickbooks, the cashflow report can get a little wonky and not align with the balance sheet.
- Avoid direct links: Accountants be damned, they will always sneak in new GL accounts without you knowing. Save yourself heartache by using lookups/sumifs calcs. Data validation drop downs are lifesavers if you want to save time and map pesky new accounts to established buckets. If you do need to add the new line, have them source your model income statement. Use moderation. These calcs can easily slow down the model if overused, so I typically use it for GL accounts and not dates for actuals.
Version Control
I’m still traumatized from not being able to find the version of a model that supported a board-approved presentation. When you have completed a plan and completed a board presentation, do yourself a favor and send yourself an email containing the model and the presentation. I have yet to find a more effective way to lock down a version. For scenarios, try to use descriptive file names that give you an idea of what to expect. Dates and editors are fields that your filing system will save, so I don’t consider them necessary to use. Here are two examples:
- AB Inc_V1 April.19.2020_LS (Weak Title)
- AB Inc_Accelerated Scenario_$5M Raise_V1
Your financial model is the blueprint of your business. It is not just something on your potential investors’ checklist. It’s a map that will help direct you to whatever successful outcome you are aiming for. Build it, maintain it and rebuild it often.
