Most complex model you've ever built

I've been delving into some fairly complex modeling work since I've started work, and I was curious to hear some stories on other people's work with high end models and to hear about just how complex a model can get.

So, what's the most hardcore model you've ever built and what made it so complex?

Complex Modeling Examples

While WSO users aren’t going to post their most complex models to the site (pretty sure that would violate some part of their employment agreements), they shared their input on which roles and situations often require complex modeling:

  • Private Equity models often get very complex
  • With PE models it’s about the projections, ex: you can have models that have triggers for dozens of scenarios, such as contract renewals or patent extensions or drug approvals. Some firms even assign probability values to these scenarios and use expected values.
  • Trading models, even basic option pricing models that any salesperson, trader, or structurer can build in their sleep are more complex than most banking models I've seen

Certified Private Equity Professional @sk8247365" provided a little more detail on a model he worked on:

Looking at a hospitality-sorta (think vrbo but with a portfolio) company, built a property by property occupancy model that you could use their data dump and it would tell you, on a daily, monthly, and yearly view:

  • Everything booked
  • How much was paid
  • Percentage discount to market
  • % discount to their pricing
  • Holiday occupancy
  • Holds by employees
  • Holds by members
  • 24 hour holds
  • Interest (web clicks) per property

All of this rolled up revs to be matched with expenses on a YE, rolling 12 months, and next FY view. Finally, it created a daily calendar that highlight trouble occupancy areas, and matched current revs and potential future revs with business model projections.

The end result was to answer pretty much any revenue and performance related question. It was about 25 megs. Maybe not insanely difficult but i thought it was cool to just dump the data, change the named range, and watch the thing unfold and dissect.

Recommended Reading

Want to improve your financial modeling skills?

Check out WSO’s side-by-side comparison of financial modeling courses and get a WSO discount.

Financial Modeling Course

 
Banking on Investments:
Model = Complex is an oxymoron.

Model = Busy work is a universal truth.

^he speaks the truth. Models aren't complex, they're just so freaking hard to follow sometimes.

Are you even in banking?

Yes, models can get complex. Work on a complex financing transaction and tel me it's all simple. Not saying it's rocket science, but they can get complex.

 
Not saying it's rocket science, but they can get complex.

It is relative to what you consider complex. With enough time and a basic intuitive sense of what's going on, it is easy to pound out even the most "complex" model.

That said, buy-side firms' own models will always be more complex than a banks own rendition. The model itself has very little do with the success of a pitch. PE firms always create their own more accurate models - they know to never trust a bank's model. If the bank didn't fuck it up first, they also often have incentive to be purposefully inaccurate.

I can only speak to bank models. PE models...out of my league. Someone in PE could probably describe some very complex models.

 

in PE models is in the projections. So for example, you can have models that have triggers for dozens of scenarios, such as contract renewals or patent extensions or drug approvals. Some firms even assign probability values to these scenarios and use expected values. Their motivation is to get a very realistic sense of returns, whereas banks are just worrying about coverage, so they don't have the incentive to be precise. The hardest sell side models, I've heard, are bank merger models.

 
Best Response
 If you want to see complex models go talk to someone on a <span class="keyword_link"><a href="//www.wallstreetoasis.com/finance-dictionary/trading-overview">trading</a></span> floor. While I agree that many models are basic once you get used to them, even basic option pricing models that any salesperson, trader, or structurer can build in there sleep are more complex than most banking models I've seen. 
 Just as an example, one of the keys to these models is the ability to bring in live data feeds, for things like discount rates, volatility curves, FX rates, underlying prices, etc. Then the ability to test scenarios by applying various values, forecasts, etc. to a number of variables simultaneously.
I learned more about modelling during my first few months on the <span class="keyword_link"><a href="//www.wallstreetoasis.com/finance-dictionary/trading-overview">trading</a></span> floor than most of my banker friend learned in years....  
 
skins1:
If you want to see complex models go talk to someone on a trading floor. While I agree that many models are basic once you get used to them, even basic option pricing models that any salesperson, trader, or structurer can build in there sleep are more complex than most banking models I've seen. Just as an example, one of the keys to these models is the ability to bring in live data feeds, for things like discount rates, volatility curves, FX rates, underlying prices, etc. Then the ability to test scenarios by applying various values, forecasts, etc. to a number of variables simultaneously. I learned more about modelling during my first few months on the trading floor than most of my banker friend learned in years....

I completely agree with you, skins. Traders are absolute madmen when it comes to financial analysis. I played around with a Multi-factor DCF model with 10 different cases for interest rate risk, stock market risk, inflationary risk, time horizon risk, etc. and it was so fucking confusing that I convinced I wanted to be in IB. It's even harder than rocket science.

 

YEAH TDCTKA THAT'S WHAT I'M TALKING ABOUT!!!

********"Babies don't cost money, they MAKE money." - Jerri Blank********

********"Babies don't cost money, they MAKE money." - Jerri Blank********
 

I think people often put too much weight into what a model says. Particularly for more senior folks, models seem to get treated as if they were some sort of black-box / magic 8-ball for predicting the future. While I could use 200 variables in a model instead of just 5, as long as my inputs for those variables are garbage so too will be the output.

 

Looking at a hospitality-sorta (think vrbo but with a portfolio) company, built a property by property occupancy model that you could use their data dump and it would tell you everything booked, how much was paid, percentage discount to market, % discount to their pricing, holiday occupancy, holds by employees, holds by members, 24 hour holds, interest (web clicks) per property - on a daily, monthly, and yearly view. All of this rolled up revs to be matched with expenses on a YE, rolling 12 months, and next FY view. Finally, it created a daily calender that highlight trouble occupancy areas, and matched current revs and potential future revs with business model projections. Basically created a analysis application using lookups and excel.

The end result was to answer pretty much any revenue and performance related question. It was about 25 megs. Maybe not insanely difficult but i thought it was cool to just dump the data, change the named range, and watch the thing unfold and dissect. Also a nice change from plug and chug dcfs.

 
sk8247365:
Do a full NAV drillout... much more complex than a DCF.

I've done that too. But it is still a DCF at the end of the day. Discounting cash flows from oil and gas reserves based on X prices. No not an EBITDA DCF, but still discounting cash flows.

Now try to do a 20+ asset model each with its own fiscal/tax regime for large borrowing base deals. Then get it audited so it can be referenced in the docs.

In project finance you constantly have to deal with operator-level models. The real skill lies in good design and setup to make a clear and efficient model- and avoiding unnecessary complexity.

 
sk8247365:
Do a full NAV drillout... much more complex than a DCF.

I've done that too. But it is still a DCF at the end of the day. Discounting cash flows from oil and gas reserves based on X prices. No not an EBITDA DCF, but still discounting cash flows.

Calling it merely a DCF is an gross oversimplification. Having to factor in gas shrink, differentials, phasing in NOLs, sensitizing well costs year to year, having triggers lifting or imposing moratoria on drilling in various plays, coupled with the fact that you're doing this for 20-25 different development regions... yeah you're right, that's the same as a DCF. Not even factoring in the stat and risking that goes into every region on a case by case basis.

 
rufiolove:
sk8247365:
Do a full NAV drillout... much more complex than a DCF.

I've done that too. But it is still a DCF at the end of the day. Discounting cash flows from oil and gas reserves based on X prices. No not an EBITDA DCF, but still discounting cash flows.

Calling it merely a DCF is an gross oversimplification. Having to factor in gas shrink, differentials, phasing in NOLs, sensitizing well costs year to year, having triggers lifting or imposing moratoria on drilling in various plays, coupled with the fact that you're doing this for 20-25 different development regions... yeah you're right, that's the same as a DCF. Not even factoring in the stat and risking that goes into every region on a case by case basis.

You fail to see what I am saying. I am not saying that your inputs are not complex and important to getting to your dcf. But it is a DCF. PV-10? Present value @10%. Am i wrong to call a pv-10 a dcf? I am not saying you can't make it a huge complex file with 100's of triggers and inputs, but even those are not too magical:

Gas shrink: Usually quick analyst estimate based on market/experience and presentations. Can get tricky though on new plays. Differentials: spit out from a quick bloomberg download NOL: This can be tricky Well Costs: Analyst estimates/presentations/call Triggers: Data validation --> Drop down box --> if statement

I am just saying, regardless of how complex you can make it sound and what goes into it, the basic principle at the end of the day is to use alllll of that to get what? Cash flows. Then what? Discount them. AKA - DCF.

I found the most impressive model in O&G was to actually build and get to work a hyperbolic decline curve. Not the differential inputs or other pricings, but the actual formula is sweet when you run that goal seek and make a perfect chart.

 

The hardest I've done was a smaller acquisition in which the company being acquired only "kinda" kept track of the financials...although not having the data made it more 'hard' than 'complex' now that I think about it. Needless to say, it didn't close.

Regards

"The trouble with our liberal friends is not that they're ignorant, it's just that they know so much that isn't so." - Ronald Reagan
 

I don't think they are so complicated. I think the legal stuff is way more complicated. However, they will probably have many models for various products, borrowers, etc.

Do what you want not what you can!
 

Does anyone have any advice on how I could break into an analyst position within the real estate sector.

BG: Grad from a non target last year, been working in Big 4 ever since. Tried to switch over to their securitization practice, but was put on hold due to the effects of the subprime market.

I realize that a bb isn't in the near future, but I was hoping to get some input as to where I should look.

 

If you want to break into RE as an analyst, I would consider looking for a top notch REIT (assuming that "a bb isn't in the near future"). I recently graduated this past December and took a FA position (with a REIT) that one would consider very comparable to a career in IB. Every firm and every position is different; I think if you send out your resume and get some interviews, you can see if the position fits your career plan.

Also, like Runner said, RE PE is definitely another area to look into. Your Big 4 background will certainly have some pull in a multitude of areas. I don't know if this helps at all, but just putting some thoughts out there.

 

I appreciate you taking the time to get back to me.

I have seen a few analyst postings on selectleaders, one was for ING Clarion and the other for Rockwood Capital. Should I be looking at smaller shops?

I am mainly looking to get into valuations/acquisitions but I fear that my resume may get lost if I apply online.

I would really appreciate any advice!

 

Et placeat corporis hic molestiae aspernatur reprehenderit. Ut nihil maxime illo non accusamus dolor. Deserunt saepe aut ut vel nesciunt ab fugiat. Ipsum dolore expedita qui vero maxime. Rerum aut reiciendis enim ipsa est consequatur.

Sit explicabo asperiores ipsa. Qui omnis voluptatem sed consequatur et aut. Unde provident corporis perspiciatis.

 

Error dolores aliquam qui perspiciatis vel soluta unde quidem. Voluptatem eos molestias deserunt aut sit quia aut saepe. Vero dolorem in nihil consequatur animi. Incidunt voluptatibus sit iusto minus quia ab iste.

Distinctio nemo libero nisi natus accusantium. Sint cupiditate at qui enim consequatur eius. Sint quia cumque reiciendis eaque et temporibus vitae. Enim explicabo veritatis rem ut nulla est. Et nisi eligendi autem repellendus necessitatibus. Enim occaecati ad iusto animi culpa.

Cumque dolores temporibus consequatur nobis deserunt libero officiis veritatis. Corrupti enim nihil qui officiis eligendi ut et.

Cumque nisi ab magni quo. Dolores harum qui doloremque nihil commodi. Nemo recusandae tempore non in eos quasi voluptatem. Et aut fugiat optio rerum ut esse incidunt qui.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
BankonBanking's picture
BankonBanking
99.0
4
Betsy Massar's picture
Betsy Massar
99.0
5
kanon's picture
kanon
98.9
6
dosk17's picture
dosk17
98.9
7
GameTheory's picture
GameTheory
98.9
8
CompBanker's picture
CompBanker
98.9
9
DrApeman's picture
DrApeman
98.9
10
Jamoldo's picture
Jamoldo
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”