How to review Excel Models for errors?

Hey everyone,

I did two banking internships and then moved to big tech. I cover revenue reporting, but it's 99% SQL & Python. I have a new manager that did 5 years at a top IB, and has no clue about SQL/Python but is very strong in Excel. As a result, all of the current infrastructure needs to have an Excel version to check outputs. Senior leadership loves this (as they also don't know tech skills beyond Excel), so politically I can't push back against this. These Excel models are relatively swanky, and cover things like sales target setting.

My problem is that I am not that great at reviewing work in Excel. I've created or updated models now twice where she would find a few formulae that don't tie or go down all the way. This is obviously no bueno. Does anyone have a structure or approach to review new models, or updated models for errors? I need to get up to speed very rapidly.

Comments (12)

Most Helpful
Nov 7, 2022 - 11:54am
Mastery 7 IB Shitter, what's your opinion? Comment below:

I have to review a bunch of god-forsaken, unwieldly 50-tab models from client companies relatively often. A few tips:

  1. Start at the output: If the final output is an income statement, I'll start there and trace back until I reach hardcodes. That tells me the basic logic of the model. From there you can look across the rows and columns to figure out any discrepancies in formulas.
  2. Pay attention to the little green corners on some cells: These denote inconsistencies in formulas, so always audit these to see if there are issues.
  3. Ctrl+F for ".xls": This helps you find external links that might be broken or incorrectly linking to another excel file because someone haphazardly copy and pasted a sheet from another excel file.
  4. Do manual re-calculations to check common line items: If a line for "revenue" appears across multiple tabs, make check rows comparing this across tabs and investigate discrepancies.
  5. Read any in-cell comments (the red corners of cells): May have helpful notes from prior users.
  6. Compare outputs in powerpoint: If it is a model frequently updated, output the old version on a powerpoint. Then, output the new version on another slide. Quickly flip back and forth to see changes and investigate.
hardstuck in IB
  • 14
Nov 7, 2022 - 12:49pm
Destressed Debt, what's your opinion? Comment below:

Very good methods mentioned above.

I would just add to #6 that another method is to have the old values copy and pasted as values in cells nearby and set up to calculate the delta between the new and old as well, assuming you have some white space in the tab, so you can visually see what the old, new, and the delta between the two are.

Get Jiggy With It

  • 1
Nov 8, 2022 - 6:00am
DatesExcelModels, what's your opinion? Comment below:

Awesome tip, thanks! Applied it directly today.

Nov 8, 2022 - 6:00am
DatesExcelModels, what's your opinion? Comment below:

This is exactly what I was hoping for, and the reason why I still come back to this site every so often. Would SB twice if I could.

Nov 9, 2022 - 3:54pm
grantmeaname, what's your opinion? Comment below:

Came here to say this.

Ctrl+`

And can it ever be?

Nov 8, 2022 - 11:33am
anonguytoibd, what's your opinion? Comment below:

So the Excel models are adding value and need outputs from your work? I'm not sure I completely follow but I'm inferring that the figures to the main outputs are also needed. I think there is a middle ground here so you don't have to replicate a lot of your code based process into Excel unless this manager is asking for a complete audit of sorts. Can you get her comfortable with at least half of your code based figures leading to the main outputs? It could save you time, and I'd argue your process is more structurally sound if built right than a more Excel based one.
 

If I inferred your situation correctly, it is a frustrating one and would be enough to make me look for another job at a company that values my skillset. 

  • Associate 1 in IB - Cov
Nov 8, 2022 - 11:20pm

As a banker (hopefully getting out soon) who is proficient in SQL and somewhat proficient in Python, I think the following applies:

If you are creating financial models, then Excel is more powerful because Excel is made to build financial models, including revenue build-up.

If you are dealing with data, any kind of data, Python + SQL is simply better than Excel. Most IB people are reluctant to learn new skills. 

Nov 24, 2022 - 4:42am
DatesExcelModels, what's your opinion? Comment below:

+1, and a lot of the regular updates where data is pulled from somewhere else should at least use BigQuery import/SQL something to get the data in a decent format, and then the QUERY function to structure stuff. There is so much to automate.

If you're passionate about this, give me a DM!

  • 1
  • Analyst 1 in IB-M&A
Nov 24, 2022 - 1:36pm

Explicabo rem repellat consequatur accusantium. Eum molestiae iusto nihil occaecati. Soluta placeat aut quis doloremque quo aliquid facere. Iure facere harum eos nostrum. Qui earum iste omnis animi ad ipsam et.

Consectetur necessitatibus ut dicta et. Corporis dignissimos et a reiciendis. Quia et tenetur amet et nam. Accusantium ut quia ea dolores aut omnis saepe.

Exercitationem iste nobis dignissimos quo. Enim molestiae ut nulla at non repellendus cumque. Nihil odit rerum mollitia beatae ducimus odit. Ut dolores excepturi iste. Provident voluptas placeat consequatur placeat voluptatum tenetur. Ratione praesentium in odio.

Expedita omnis est quis culpa laboriosam minima. Eum qui vel in eaque. Qui aut vitae libero quia quisquam.

Start Discussion

Career Advancement Opportunities

November 2022 Investment Banking

  • Jefferies & Company (▲08) 99.6%
  • Lincoln International (= =) 99.3%
  • Financial Technology Partners (+ +) 98.9%
  • Evercore (▽01) 98.5%
  • Bank of America Merrill Lynch (▲01) 98.1%

Overall Employee Satisfaction

November 2022 Investment Banking

  • PJT Partners (= =) 99.6%
  • Evercore (▲02) 99.3%
  • Greenhill (▲06) 98.9%
  • Canaccord Genuity (▲15) 98.5%
  • William Blair (= =) 98.1%

Professional Growth Opportunities

November 2022 Investment Banking

  • PwC Corporate Finance (▲14) 99.6%
  • Lincoln International (▲03) 99.3%
  • Jefferies & Company (▲04) 98.9%
  • William Blair (▽02) 98.5%
  • Evercore (▽01) 98.2%

Total Avg Compensation

November 2022 Investment Banking

  • Director/MD (10) $613
  • Vice President (38) $392
  • Associates (219) $256
  • 2nd Year Analyst (139) $163
  • 3rd+ Year Analyst (19) $160
  • 1st Year Analyst (463) $153
  • Intern/Summer Associate (88) $151
  • Intern/Summer Analyst (336) $92