Bombed my first modeling test. How did yours go? How did you underwrite your assumptions?

Officially took my very first modeling test for an interview yesterday. I think I bombed it. How much of a modeling test is more about the approach to actually nailing all of the inputs/calculations?

I thought I understood how to do simple MF modeling (in reality I do). Don't get me wrong, I know the formulas, how to set it up, make it dynamic, format it, make it pretty, all that. Excel is not the issue.

TLDR: However, what I don't have experience/repetitions doing is creating my own assumptions. They were not give. How do you underwrite your assumptions?

Even if I completely F'd up the assumptions, I feel like I can walk through the model, how I got to everything, why I included certain things/left others out.

The full picture:
I was given a bunch of historical information spanning about 30 Excel and PDF documents that I had to parse through and organize into files. They included: rent rolls, occupancy data, operating statements, property information (location, floor plans) vendor service agreements, vacancy reports, only loan documents to figure out the debt financing, etc. The test was to put together the historical information and then produce a 1 year pro forma.

Once I figured out what was what, I laid out my model with the following tabs: 1) Projection 2) Historicals 3) Rent Roll Summary 4) Rent Roll Analysis 5) Amort. Schedule. I did all the back end calculations in tabs 2-5 and rolled everything into tab 1 with a property summary at the top that had the year 1 pro forma with the typical line items from income all the way through NOI, debt financing, cash flow after debt, and I inserted a risk section for the loan balance, DSCR etc, Debt Yield, etc.

How I created the assumptions:
Since I was given historical data (2011- 9/2014) I took the average increases/decreases of the historical data and used that as the average as the % increase for the next year. Was I close at all?

Comments (8)

Jul 30, 2018 - 11:38am

I know its cliche but having helped hire, and going through the process myself - I think they just want to make sure you aren't a complete beginner at excel and want to know how you think.

I'm assuming you aren't an experienced professional (based on what you said above) so you wouldn't be the final person drawing the assumptions anyways. If you had a general idea of what you were doing I think that's probably all they were looking for. A lot of people BS their level of skill in certain programs so its a weed out test basically.

Most Helpful
Jul 30, 2018 - 12:26pm

jpacai making career pivot from more third part AM/PM consulting. So, don't have the experience with a ton of underwriting vs. more verification of facts

Time given: few hours. Sent via email with a deadline to return it. Half the battle at the beginning was going through all the documents to figure out the necessary info to start building the model and started to get way too bogged down trying to make everything perfect.

I know I nailed the debt part, which makes me feel better.

Jul 30, 2018 - 2:28pm

Bombed mine... it was given in the office, on a small notepad laptop, limit of 1hr 25min... took me literally one minute to find the bloody " key and kept misspelling the formulas given the small keyboard... and forget efficient keyless navigation...

Most frustrating thing was one part of a five section test involved a debt waterfall, and they outright said they did not expect me to know how to do it... but gave it to me anyhow. Just could not code it then and there. Had I seen a previous template: different story.

Start Discussion

Popular Content See all

+130IBby Intern in Corporate Finance">Intern in CorpFin
You Did it Citi
+120OFFby 2nd Year Analyst in Investment Banking - Industry/Coverage">Analyst 2 in IB - Ind
HELP: Sticky Situation with Boss
+105OFFby 2nd Year Analyst in Investment Banking - Mergers and Acquisitions">Analyst 2 in IB-M&A
Idgaf anymore
+29IBby 1st Year Analyst in Investment Banking - Industry/Coverage">Analyst 1 in IB - Ind
Anyone else just want out of this shit?
+28IBby 2nd Year Analyst in Investment Banking - Generalist">Analyst 2 in IB - Gen
Evercore Target Schools?
+26BSCHby Prospective Monkey in Investment Banking - Mergers and Acquisitions">Prospect in IB-M&A
To Snitch or not to Snitch?
+18OFFby 2nd Year Analyst in Investment Banking - Industry/Coverage">Analyst 2 in IB - Ind

Total Avg Compensation

March 2021 Investment Banking

  • Director/MD (9) $911
  • Vice President (31) $349
  • Associates (162) $231
  • 2nd Year Analyst (97) $151
  • Intern/Summer Associate (92) $144
  • 3rd+ Year Analyst (23) $145
  • 1st Year Analyst (370) $131
  • Intern/Summer Analyst (306) $82

Leaderboard See all

LonLonMilk's picture
Jamoldo's picture
Secyh62's picture
CompBanker's picture
redever's picture
frgna's picture
Addinator's picture
Edifice's picture
NuckFuts's picture
bolo up's picture
bolo up