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

Comments (48)

Nov 11, 2007

hmm I would love to talk about it, but that would be giving away my company's secrets:P

why didnt you start off?

Nov 11, 2007

Kelly LeBrock.

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

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

Nov 11, 2007

Model = Complex is an oxymoron.

Model = Busy work is a universal truth.

Nov 11, 2007

Models are usually very basic. e.x. stolen from sell-side research.
When I was a summer, I worked with an associate on a ridiculously complex model. The company was a complete mess (multiple divisions, leasing, capex, currency, etc.) and it was a restructuring play for a PE firm so it had to be decent.

Nov 11, 2007

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.

Learn More

7,548 questions across 469 investment banks. The WSO Investment Banking Interview Prep Course has everything you'll ever need to start your career on Wall Street. Technical, Behavioral and Networking Courses + 2 Bonus Modules. Learn more.

Nov 11, 2007
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.

    • 1
Nov 11, 2007

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.

Nov 12, 2007

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.

Nov 12, 2007

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...">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...">trading</a></span> floor than most of my banker friend learned in years....

Nov 12, 2007
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.

Nov 16, 2011
albatrosslee:

It's even harder than rocket science.

That's when shit hits the fan.

Nov 12, 2007

the guys in weird science came up with a pretty good model.

Nov 16, 2011
tdctka:

the guys in weird science came up with a pretty good model.

Zinger!

(Four year bump, cause that's how I roll).

Learn More

7,548 questions across 469 investment banks. The WSO Investment Banking Interview Prep Course has everything you'll ever need to start your career on Wall Street. Technical, Behavioral and Networking Courses + 2 Bonus Modules. Learn more.

Nov 11, 2007

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********

Nov 12, 2007

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.

Nov 16, 2011

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.

Nov 16, 2011

Not to say that the hyperbolic decline curve in O&G were not complex, but at the end of the day it was another DCF.

Nov 16, 2011
sk8247365:

Not to say that the hyperbolic decline curve in O&G were not complex, but at the end of the day it was another DCF.

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

Nov 16, 2011

2 part model - Unrated credit rating model used as the basis for a CDO model. Spent months building this right before sub-prime hit and then essentially threw it all out... fml

    • 1
Nov 16, 2011

The hardest model I've done required using the RTD function of excel (it was a live model it was awesome)

Nov 16, 2011

http://www.imdb.com/title/tt0108988/
edit: Just saw the Weird Science comment. SB to you!
edit #2: That guy hasn't posted in three years... crap.

Nov 16, 2011

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.

Nov 16, 2011
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.

Nov 16, 2011
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.

Nov 16, 2011
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.

Nov 17, 2011

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

Nov 18, 2011

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!

Nov 18, 2011

Yeah. I'd be interested to see some insanely complex models. Most of the really confusing ones that I've seen really didn't need to be, and should have been more simplistic.

Nov 17, 2011

i built this model that was so complex it just turned into the Apple iOS 5

Nov 18, 2011

Usually less taxing if you're dealing with REITs.

Nov 18, 2011
monkey_scribe:

Usually less taxing if you're dealing with REITs.

that wasn't a pun, was it?

    • 1
Nov 18, 2011

what deal is made between the originator and the SPE?

Terence

Nov 18, 2011

In between the originator and the SPE is the conduit.

Nov 18, 2011

It was...

Nov 18, 2011

The biggest pain in regards to working with RE is all the legal document review rather than anything qualitative, assuming you're working in acquisitions and you have to review cryptic title docs and whatnot.

Nov 18, 2011

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.

Nov 18, 2011

Where do you live?

I know a couple of top-tier RE PE shops that take people from Big-4.

Nov 18, 2011

Thanks for the feedback.

I am working in NYC. Would the PE shops you know of happened to be located there?

I would appreciate any advice that you have to offer.

Nov 18, 2011

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.

Nov 18, 2011

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!

Nov 18, 2011
Comment
Nov 18, 2011
Nov 16, 2011
Comment