Most common modeling mistakes?
What are some of the most common mistakes when modeling? What are some good checks to go through when your models don't balance?
The Wall Street Oasis Financial Modeling Course is currently 15% off to new members.
Common Modeling Mistakes
When troubleshooting an incorrect model a lot of it depends on what type it is but user @Acceptthetermsandconditions offers a few starting points:
- Revolver not done correctly if using a non-cash sweep model
- Linking cash to the wrong cash item on the balance sheet when computing interest income
- Using the wrong cash number from the cash flow statement in your balance sheet
- Matrix errors
- Wrong links to other Worksheets
- Use the divide by rule to find other mistakes: i.e divide the number that your off by in half, then look for that number on the cash flow.
- The difference between your equity numbers should be +NI - D. If not, you screwed up
- Wrong calculations of pensions/leases
- Adding all CA plus subtotal for CA instead of just using the subtotal in calc for Total Assets
- Same for liabilities and equity
Learn More About Financial Models On WSO
- Scared Shitless: How Did You Actually Learn How To Competently Build Financial Models
- Managing Your Money - Building A Personal Financial Model
- Need Some Tips To Learn Financial Modeling
Looking to become a modeling master?
Sign up for our financial modeling training course to learn all this and more. Check out our 15% off discount to Wall Street Prep's Financial Modeling Courses.
too vague a topic. what kind of model (operating, dcf, lbo, ipo, acc/dil, merger etc)
Operating: cash flow: Non-negative dividends cash flow: Non-negative capex cash flow: Changes in working capital error IS/BS: Summing wrong items into subtotals or totals Adding depreciation in the calculation of net ppe
other just number mistakes such as not scrubbing financials correctly. unjustifiable assumptions.
Disconnect between revenue forecast and cost forecasts. I.e. revenue growing without cost growing, or vice versa. Have seen models where revenue grows 10% a year for five years, yet costs remained relatively flat over same period, always raises alarm bells.
Let's say you're building a bare bones LBO model during an interview. When you realize that your BS isn't balancing or it is but the numbers don't look right, what are some of the most common mistakes to look for or any good tricks to check things through?
Would it be a good idea to first build out the model with a generic set of numbers (10000 revenue 5000 EBIT...) and have things tie out before inputting actual numbers?
I'm pretty sure the things I listed are common examples of why a balance sheet wouldn't balance.
I guess my first post never posted...
Here it goes:
Revolver not done correctly if using a non-cash sweep model Linking cash to the wrong cash item on the balance sheet when computing interest income Using the wrong cash number from the cash flow statement in your balance sheet Matrix errors Wrong links to other Worksheets Use the divide by rule to find other mistakes: i.e divide the number that your off by in half, then look for that number on the cash flow.
The difference between your equity numbers should be +NI - D. If not, you screwed up Wrong calculations of pensions/leases Adding all CA plus subtotal for CA instead of just using the subtotal in calc for Total Assets Same for liabilities and equity
What are matrix errors?
Modeling mishaps? (Originally Posted: 01/05/2015)
I'm putting together a presentation on modeling and wanted to put together some real life examples of careless modeling errors that caused big material mistakes. To hit home the importance of conventions and best practices.
Any famous errors or any personal stories?
Other than checking that the BS balances, what are some other good checks to make sure your models isn't busted
Hi, I've been doing several model audits now and most of the modeling errors I've experienced were the ff: scattered assumptions (should be the at least one assumptions page only), hardcoding of values, inconsistent formulas, improper or lacking of label/heading. I think these are some of "famous errors" being committed
Hi, I've been doing several model audits now and most of the modeling errors I've experienced were the ff: scattered assumptions (should be the at least one assumptions page only), hardcoding of values, inconsistent formulas, improper or lacking of label/heading. I think these are some of "famous errors" being committed (including myself)
There was a post on here a month or two ago talking about an article which said that an analyst at Goldman screwed up the diluted shares outstanding and had the buyer either under/overpay by $100mm, can't remember. Google it, it was recent enough.
Here it is: http://dealbook.nytimes.com/2014/10/17/an-oops-for-goldman-sachs-in-its…
Enron
it's not an error if it's intentional
It's not uncommon to see excel errors in equity research table outputs - lesson is to run an error check before you finalize something. It only takes a few minutes and is a good way to make sure that there isn't anything glaring.
I'm sure what you mean by "error check" but simple errors like hardcoding a number, not subtracting an cost/expense, linking to incorrect cell or not properly formatting a formula are errors that are not easily caught and can have quite an impact.
I've run into research reports that cut a lot of corners. Often times they will group and un-group accounts like D&A, short term investments, and shareholders equity, making it much harder to understand the "percentages" or ratios that reference those same accounts. It can be a pain, and it definitely produces modeling errors on their part.
Have never made one so unfortunately cannot contribute to this thread
Why don't you sit the next couple of plays out buddy
I once saw a model trip in the swimsuit competition. Brutal. She lost even though she had huge bewbs.
Inserting rows/columns. I was working with a new-hire at an old job on a transaction and they inserted a row into a sources/uses table and basically didn't pick up one of the transaction fees that was = about 2% of total transaction size... Didn't catch it till we finalized the term sheet. Had to go back to the underwriter and revise the schedules.
Vlookup and hlookup using a fixed #.. Same thing it isn't flexible or robust
This is basically what I am talking about.
I've been guilty of and seen some mislinks/hard coded numbers that were embedded into formulas that were incorrect/ not properly formatted formulas - (a misplacement of parenthesis) etc.
I didn't know if there were some famous spreadsheet errors that threw valuations completely off.
Yeah, best way to fix this is to never use VLookup or HLookup. (SUMIFS and/or SUMPRODUCT are much more powerful functions that you can use to do the same thing).
An incredibly basic one that i've seen multiple times is just confusing signs, especially below the line, e.g. accidentally adding interest, amort, etc to EBITDA to get to pretax income. This will happen when someone has those as negative items but then subtracts all the "expenses" to get to pretax income
Using alt + = for the sum function and either picking up the header (which is often a number if there's a date) or not summing the full range (often happens if the data isn't contiguous)
Illo et delectus voluptas exercitationem. Ipsam rerum expedita eum sapiente. Voluptatum sint aut qui aliquam omnis ut velit.
Non animi ab odit temporibus non. Nam fugiat nam maiores explicabo. Eius et qui et dolor.
Tempore sit cumque quis expedita perferendis repellendus sed. Eligendi consequuntur beatae voluptatem nihil.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...
Repellat neque et eos illo culpa dolorem. Vel rerum repudiandae fuga rerum optio quis.
Officiis culpa voluptatem iusto dicta cupiditate placeat et. Quo numquam et ut atque.
Aliquam ratione est magni et harum. Ea quas sunt sed consequatur distinctio est voluptatum. Similique quaerat aut repellendus odio dicta. Dolorem fugit quo necessitatibus harum ullam minus.