How I Build Models — The Year Was...
 I didn't look as handsome at 2am. |
It was early 2010. I was a green Consultant in the wake of the Financial Crisis, and staffed on another high-profile bankruptcy case.
I was recently put in charge of "the model before the model" (my model was a data source for someone else's model, it's how we rolled back then), because the person who built it changed jobs.
There was one problem though — the model completely sucked just wasn't working the way it was supposed to... outputs were inconsistent, data sources weren't matching up, and the update process was slower than a day at the DMV.
After some deliberation amongst the team, we decided the model wasn't reliable and had to be rebuilt... TONIGHT (we had a client deliverable tomorrow).
Then, everyone looked at me: lowest man on the food chain. No "real" responsibilities. Young and full of spunk. A perfect match.
And the 33-hour work day began...
More Holes Than a Colander
Now, I wasn't the best financial modeler back then, but I was good enough to get by.
Nonetheless, as I flipped through the tabs, something become readily apparent — there were errors everywhere.
Outputs from one tab didn't link to another. Various hard codes sitting in formulas. No global "check system" anywhere.
And (thankfully) a lot of dormant tabs that could be deleted from the get go.
The Rebuild
Instead of trying to fix the existing model, I chose to do a complete rebuild, and use the existing model as a guide.
I just felt more confident knowing every keystroke came from me and was something I could explain.
The the most impactful thing I did? Implement an error-checking system.
All these years later, it still amazes me how many people don't have error checking in their models, and guess what? Their models have mistakes.
Here's the approach I use (one tab called... "Error Checking"):
 The Error Checking System I use in all Financial Models. |
Error Check 1: Model Issues
These are "must know" because it means something is wrong with the model itself.
Some of the most common ones:
- Balance Sheet doesn't balance
- Statement of Cash Flows doesn't match the Balance Sheet
- Net Income from accounting system doesn't match the model
- Detailed underlying model doesn't match output on the Summary page
Error Check 2: Business Issues
These are "good to know" because it means there's a strategic item that needs review, but the model is working fine.
Most common ones:
- Revolver exceeds capacity
- Leverage Ratio not in compliance
- Fixed Charge Coverage Ratio not in compliance
Master Error Check Cell
Each of the potential errors above links to a place in the model that should always equal zero (i.e., Assets – Liabilities + Equity = $0).
So, when something does not equal zero, I am notified on the error page.
The "Master Error Check Cell" is the sum of all the individual error checks and tells me, "hey, there's a mistake in here somewhere."
The Overnight Grind
Turns out, the original model wasn't as terrible as people thought, and I was able to leverage a lot of the original logic, albeit in a more streamlined way.
I deleted a bunch of dead tabs, simplified the lookup formulas and incremental steps from data to summary, cleaned things up with proper formatting, and made sure the output was an easy copy/paste for "the big model" (the model managed by another co-worker, for which "my model" was the data source).
Still, it took me all night, and I didn't go home until 5pm the next day (33 hours starting from 8am the previous day).
I'll never forget drinking coffee at 3am and watching people come into the office around 7am saying "you're still here?!"
Was I stressed? Sure was.
Could I do it faster today? Definitely.
But, it served as a lifelong lesson for my financial modeling career, so I wouldn't change it.
Early Lesson. Lifelong Impact.
A "great model" becomes a bad one really quickly if you don't build a double-check system for your work.
The model I rebuilt was actually decent in hindsight, but when missing key structural items it caused our team to panic (and gave me an all-nighter).
Even the best modelers make mistakes. I make them. You'll make them.
It's an inherent risk that comes with the open-ended functionality of working with a spreadsheet.
So, try to learn from my story, save yourself an all-nighter.
Implement an error-checking system in your financial model as soon as you can.
That's it for today. See you next time.
—Chris
p.s., if you enjoyed this post, then please consider checking out my Financial Modeling Courses. As featured by Wharton Online, Wall Street Prep, and LinkedIn Learning, you'll learn to build the exact models I use with Private Equity and FP&A teams around the world. 👉 Click here to learn more.