TL;DR. Financial modeling wellness check. Common mistakes in financial modeling. 1) Do not use cash or equity as a balancing item to balance your balance sheet. 2) Do not project cash or equity as a percentage of a top line figure when projecting your financial statements. Calculate them independently. 3) Don’t forget to use a three-line format – opening, activity, closing for key items. 4) Link your models. 5) Don’t forget to reconcile. Put in automated cross checks for reconciling changes in key items across all three financial statements. 6) Check your growth assumptions 7) Build a model for Dr. Doom.
A financial model is only as good as the thinking and design behind it. Like other forms of modeling, financial modeling also suffers from a number of common design and thinking mistakes. These modeling mistakes can make or break a model, becoming the difference between models that impress right out of the gate and models that trip and fall flat. Here is a list of 7 recommendations, a checklist that can help you improve and scale your modeling game in less than 10 minutes.
It is like running, breathing, or sleeping, form follows function. After years of building models in Excel it become second nature. Yet, common mistakes even experienced bankers make when building models for their teams or clients. Mistakes that I used to make till instructors and coaches pointed out what was wrong with the design I was using.
Specifically, these mistakes apply to projecting financial statements for future years. If you build balance sheet, income statement and cash flows, there is a reasonable chance you have faced these issues before. The most common manifestation is your model balance sheet not balancing, which can be easily fixed. How you fix it, is where we make mistakes.
How would you know if you are making the same mistake? Add a few simple cross checks to your model and see if it passes or fails the test.
1) Using equity or cash as a balancing item.
You have built your balance sheet and income statement, but your balance sheet does not balance. No worries, you use the easiest possible solution, you have been using it for years. Pick either cash or equity as a balancing item. The difference between assets and liabilities is parked in either of these two items. Problem solved? No.
Why is that a bad idea? One would think this is a classical solution to an old problem. But it is not. From a flow point of view all three financial statements are linked to each other. Balance Sheet. Income Statement and Statement of cash flows.
While you may be able to balance one, you will quickly run into issues with the other two. More importantly you would not have any idea where the issues originated from. To trace the cause for the missing balance, you will have to find where the difference came from. There are only a handful of reasons why one ends up with these challenges that result in an unbalanced balance sheet.
- You have grown or purchased assets (balance sheet) but have not paid for them (income statement) through or via cash (balance sheet).
- You have reduced assets with depreciation for the year but have not expensed the depreciation correctly. Or you have expensed it but forgotten to take the impact in your balance sheet.
- You have amortized something, but the impact has not flown through all the statements (see (b) above).
- You have switched the year over year change calculation signs for current assets or liabilities. Rather than adding you have subtracted or vice versa.
If you have set up your model correctly, tracing such errors is not a problem. It is not always easy, but it is doable. A simple hack is to calculate the difference in the missing balance year on year. The change in that amount generally ties in with the mistake or the sum of mistakes you have made.
If you have not set up your model correctly, if your model is a complex, multi tab adventure, you are about to go down a very frustrating rabbit hole in Excel.
What is the right way?
One. Model cash and equity separately. Not as a single line item but ideally over three lines and never as a balancing item.
What is this about three lines?
2) Use opening, activity, closing for the four trouble spots on your statements
Use a three line break down (Open, Activity, Close) format for cash, equity, depreciation, and fixed assets. These are four of the most common line items where we run into trouble. Setting up an opening, activity, closing format will make it easy for you to debug what went wrong, when something does go wrong.
3) Model, don’t project as a percentage.
A related issue, same challenge, is forecasting or predicting cash balances as a percentage of revenues or current assets. That is also an equally bad idea.
Why? Because cash balance, or changes in cash balances should flow from the statement of cash flows. You should be able to reconcile the change in cash balances on the balance sheet with the change between opening and closing cash from the statement of cash flows.
The general rule of thumb is to model any line item on your balance sheet and income statement. Static items, carried over items from discontinued businesses, items that are not relevant or significant in terms of exposure can be represented by static or absolute values. But the rest need to be modeled. With out modeling, changes are likely to be missed and unlikely to flow through accurately leading to issues with balancing.
4) Link statements with each other. Balance Sheet. Income Statement. Statement of cash flows.
How are they linked? Cash from opening Balance sheet flows into beginning cash in the statement of cash flows. Ending cash from the same statement flows back into closing balance sheet. Sounds simple?
Does your model do that? Mine did not.
How about retained earning?
Net Income flows from your Profit and Loss statement into retained earnings and the closing balance for equity. And then flows back into the balance sheet. I used to model equity as a balancing item.
Other than Cash and Equity, changes in assets and liabilities also need to flow through all three statements. We often model them as a percentage. But we forget to flow the changes through the other two statements.
5) Reconcile.
Setup automated cross checks that reconcile linked items.
- Change in retained earnings or equity between balance sheet and income statement on account of income transferred from your P&L.
- Change in cash between balance sheet and statement of cash flows.
- Assets purchased and sold between Balance sheet and statement of cash flows. Liabilities added or reduced between Balance sheet and statement of cash flows. Depreciation charged on the balance sheet and adjusted in statement of cash flows.
6) Calibrate assumptions with the real world.
This sounds easy but is the hardest of all steps. There are two specific assumptions that you need to watch out for. The first is your assumed growth rate in general. As you grow, you are likely to hit step functions in expenses and capacity. Your expenses at a certain level can only support a certain volume of business. As you grow beyond that volume you need to reinvest in infrastructure, resources, space and capacity. We often overlook this point and project growth without any associated adjustments. Your expenses need to grow with your sales. Scale efficiencies may lead to a difference in growth rates, but you are unlikely to see sales growth without an associated shift in expenses in the real world.
Think of this as the investment required for you to scale. Your call center team can only handle a certain volume of calls, your servers a certain volume of sessions. If you grow beyond these levels you need to add more seats and more servers.
The second is terminal growth. Terminal growth is perpetual growth. A growth rate that you will hit when your business hits stability and starts growing at the general economic growth rate. Perpetual growth is unlikely to be twice or thrice as large as the general economic growth for your region, market or economy. So if your country is likely to grow at 3% for the next few decades, it is bad form to project terminal growth at 9% or 12% in perpetuity.
You may need to model out for an additional 5 or 10 years to hit terminal growth. If that is what it takes do that. A good cross check is to check the proportion of your enterprise value linked with what we call terminal value. If more than 80% of your value is associated with cashflow associated with terminal value, you may need to model a few more years to reduce that percentage. If that 80% is based on a 2x or 3x terminal growth rate compared to general economic growth you may need to revisit your assumptions.
A quick summary
Financial modeling wellness check. 1) Do not use cash or equity as a balancing item to balance your balance sheet. 2) Do not project cash or equity as a percentage of a top line figure when projecting your financial statements. Calculate them independently. 3) Use a three-line format – opening, activity, closing for key items. 4) Link your statements. 5) Put in automated cross checks for reconciling changes in key items across all three financial statements. 6) Check your growth assumptions.
Beyond the financial modeling wellness check there is one more change in modeling perspective that can help you stand out from other model builders.
7) Bonus lesson. Building Financial Models for Dr. Doom.
As founders we often build models that glow green. We build them with rose tinted glasses. Optimism. Growth, disrupting incumbents, joys of scaling a good thing and the world as it should be. Neat, tidy, clean, and green.
Optimism is good and is a required condition for surviving as a founder. But beyond product market fit once you start to grow the business an alternate perspective is required. What metrics do you want to track and watch? How would you define them?
Side by side with optimism we also need to look at downside risk. If things do not work according to our plan, what would our plan b look like? Are there are metrics (see above) that we need to track to avoid getting on the path to plan b?
Have you ever built a financial model for Dr. Doom?
If your business world went to hell in a hand basket, how would you turn that ship around?
Where would you start? What would you do? What would you focus on? While it is just as important to model growth, its equally important to ask two very different questions.
a) What would break this business?
b) What would we do when that happens?
We call this the banker’s model. Because it came out of our credit modeling courses for relationship managers at one of the most well-known banks in the Middle East.
Bankers do not care about growth. They care about being paid back. They want two or more paths to principal repayment. It is a different mindset. Rather than looking at scale, we look at downshifting gears.
When business slows down or is not doing as well as we expected, how would we fare?
The banker’s model is an alternate model that we put together side by side with our primary mainstream financial model. You can add it as a separate tab or save it as a different version.
Just take your mainstream model and identify everything that drives growth within your topline. Then reverse the trend. Break everything that can break. Growth, expenses, refunds, bad debts, supply chain cost over runs, business disruption – anything that can blind side you and your team. And then ask a simple question.
Can we find two paths to sanity out of this? If we cannot, should we proceed on this path? It is ok to not find two paths out of every crisis you work through. The important thing is to think through your plans and models before the crisis arrive.
Founders and bankers. Who would have thought our world would meet this way?
Building the Dr. Doom model.
One simple technique is shortfall analysis. By what level do order volumes need to fall for our profitability to fall in the red zone? What is the probability of that event happening?
It is a powerful technique that flips the growth question. We first estimate what would it take to break the business (order volume reduction) and follow that through with the probability of that event happening.
It is a two-step process.
- Estimate the threshold orders need to fall by for your free cash flow to turn red.
- Estimate the probability of that fall. You need a bit of statistics and the good old normal distribution for it.
Not rocket science, not accurate to two decimal places and reliant on assumptions and approximations but a good tool to play with.
Why bother? Why build a banker’s model as a founder? Why do a wellness check?
The best time to plan for a crisis, to find a path to sanity is when you are not drowning in panic. A banker’s model forces you to ask the other question. What if? When the worst case scenario happens, will we be able to bear the load? If you have a good board, they have already asked you this. If they have not, they will in future.
Where did these lessons come from?
I have been teaching financial modeling for 11 years. Prior to and side by side with teaching modeling, I had been building models for three decades. We reviewed financial modeling as part of actuarial exams as well as part of my work as a consulting professional. One would think that I would have picked up these tools and techniques over time in the field. I did not.
We often use off the shelf models at work that only require us to plug inputs and the results are ready for our review. We use software for calculating credit spreads and ratings and it is rare that we are asked to build models from scratch as bankers, consultants, or professionals.
But sometimes building simplified models from zero is the best way of understanding a business. Numbers can be used to tell powerful stories. We need that background when presenting our businesses in front of investors, lenders, and partners. Before we do that, we need to become one with our models.
In 11 years of teaching the subject I have only seen four students with their modeling ethos down pat. These are the ones who knew what they were doing. But even then, for two of them, their balance sheet did not balance.
It is not a reflection on my students. It is just a question of practice. Accountants have an edge because they understand the links between financial statements. For everyone else, these lessons take time to learn. If it wasn’t for Professor Michael Kirschenheiter and his valuation and financial reporting course at Columbia Business School, I would still be building models the same way.
That was my first wellness check, my first form correction, where I learnt to add the cross checks. Equity and cash balancing item lessons I learnt while building models as part of my consulting practice. The three-line items approach was a gift from an accounting client. We were trying to reconcile treasury profits for treasury products using daily revaluations. Treasury reconciliations can make anyone see the light. The opening, activity, closing discipline came from that exercise. The inspiration for downside modeling is owed to building credit models. We used shortfall models as part of our work in estimating probability of default for credit counterparties. The cross over to building better models for founders was a natural next step.
Valuation and Financial Modeling for Founders and Startups – Lecture playlist
Checkout the multipart free lecture series on financial modeling on our YouTube channel. 5 short episodes. 5 valuable lessons. We add a new episode every month. Take a look.
