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
- Financial Valuation Modeling Boot Camp
- Looking for Complex Waterfall Models Have Lots to Trade Share
- Looking for Models Used in the Industry
Want to improve your financial modeling skills?
Check out WSO’s side-by-side comparison of financial modeling courses and get a WSO discount.
hmm I would love to talk about it, but that would be giving away my company's secrets:P
why didnt you start off?
Kelly LeBrock.
********"Babies don't cost money, they MAKE money." - Jerri Blank********
Model = Complex is an oxymoron.
Model = Busy work is a universal truth.
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.
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.
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.
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.
That's when shit hits the fan.
the guys in weird science came up with a pretty good model.
Zinger!
(Four year bump, cause that's how I roll).
YEAH TDCTKA THAT'S WHAT I'M TALKING ABOUT!!!
********"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.
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.
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
The hardest model I've done required using the RTD function of excel (it was a live model it was awesome)
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.
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.
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
How large/complex are financial models for really complicated deals? (Originally Posted: 03/09/2011)
For marquee transactions like Lehman restructuring or BofA-ML merger, I'd imagine the models to be ridiculous. Anyone have an idea of how complex they get?
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.
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.
i built this model that was so complex it just turned into the Apple iOS 5
Real Estate - Are the valuations/models complicated? (Originally Posted: 06/15/2007)
In terms of Real Estate investment, are the valuations/models complicated? Is it more or less taxing than M&A might be?
Usually less taxing if you're dealing with REITs.
that wasn't a pun, was it?
what deal is made between the originator and the SPE?
In between the originator and the SPE is the conduit.
It was...
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.
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.
Where do you live?
I know a couple of top-tier RE PE shops that take people from Big-4.
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.
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.
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...
Omnis inventore sed voluptas quia molestiae. Minima nihil deleniti et commodi rerum. Blanditiis officia porro est suscipit ea eius. Aliquam qui tempore eveniet iure quod eaque. Sit enim eius voluptatem aut placeat voluptas non. Optio magnam nobis temporibus et aut veniam veniam. Enim quibusdam explicabo qui nihil voluptatem.
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.