Common mistakes in financial modeling? Are you making them?

By

8 mins read

TL;DR. Financial modeling wellness check. Common mistakes in financial models. Do not use cash or equity as a balancing item to balance your balance sheet. Do not project cash or equity as a percentage of a top line figure when projecting your financial statements. Calculate them independently. Use a three-line format – opening, activity, closing for key items. Put in automated cross checks for reconciling changes in key items across all three financial statements. Build a model for Dr. Doom.

For anyone with consulting, accounting, or banking backgrounds, financial modeling is a skill taken for granted.

Common mistakes financial models
Building Financial Models. Common mistakes in financial models

It is like running, breathing, or sleeping. After years of building models in Excel it become second nature. Nothing really fazes us in Excel anymore. Yet, there are 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.  

What is this common mistake?

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.

  1. You have grown or purchased assets (balance sheet) but have not paid for them (income statement) through or via cash (balance sheet).
  2. 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.
  3. You have amortized something, but the impact has flown through all the statements (see (b) above).
  4. 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 you have made.

If you have not set up your model correctly, you are about to go down a very frustrating rabbit hole in Excel.

Common mistakes in financial modeling? Are you making them?

What is the right way?

One. Model cash and equity separately. Ideally over three lines and never as a balancing item.

What is this about three lines?

Modeling cash on the balance sheet - automated error checks and reconciliation.
Modeling cash on the balance sheet – automated error checks and reconciliation.

Two. Use a three line break down (Open, Activity, Close) format for cash, equity, depreciation, and fixed assets.  

Three. A related issue to the same challenge is forecasting or predicting cash balances as a percentage of revenues or current assets. That is also a 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.

Four. Link your statements with each other. Balance Sheet. Income Statement. Statement of cash flows.

Linking Financial Statements. How are the balance sheet, the income statement and the statement of cash flows linked.

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.

Common mistakes in financial modeling? Are you making them?

Five. Setup automated cross checks that reconcile linked items.

  1. Change in retained earnings or equity between balance sheet and income statement on account of income transferred from your P&L.  
  2. Cash between balance sheet and statement of cash flows.  
  3. Assets purchased and sold between Balance sheet and statement of cash flows. Liabilities added or reduced between Balance sheet and statement of cash flows.

A quick summary

Financial modeling wellness check. Do not use cash or equity as a balancing item to balance your balance sheet. Do not project cash or equity as a percentage of a top line figure when projecting your financial statements. Calculate them independently. Use a three-line format – opening, activity, closing for key items. Put in automated cross checks for reconciling changes in key items across all three financial statements.

Beyond the financial modeling wellness check there is one more shift in modeling perspective that can help you stand out from other model builders.

Building Financial Models for Dr. Doom.

Building financial models for Dr. Doom
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.

Shortfall probabilities for downside risk
Shortfall probabilities for downside risk

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.

  1. Estimate the threshold orders need to fall by for your free cash flow to turn red.
  2. 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.

Financial Modeling. June 2021. Workshop
Financial Modeling. Wellness and form checks.

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.

Financial Modeling Training Workshop June 2021
Financial Modeling Training Workshop June 2021

Register now for the Financial Modeling with Jawwad Farid workshop scheduled for the first two weeks of June. 5 half days. Three case studies. Live instructor led online sessions. Limited seats. Take advantage of the special pre-order pricing which expires end of April.

https://financetrainingcourse.com/education/financial-modeling-build-better-models-faster/