Hey guys - a few posts ago there was someone who used the attached model case study for critique. I did this relatively quickly during my down time - so I'm sure there are errors to be caught. However, I feel that I have done enough modeling and underwriting to have a decent grasp of how to complete the exercise, and wanted to share for others. Only reason I used this PGIM copy was that it has been shared on this forum before, and appears readily available to the public.
Note that I used a macro for the goal seek question. To replicate this macro, you can use the excel goal seek took...the inputs are built out in cells B40 and B41. ALSO - I use 2016 excel...looks like the attachments are only for excel 97-03 (.xls)??? Anyway, this might all blow up for someone who doesn't have the compatibility. I tried to save as .xls, but it gave me a bunch of compatibility errors. Not sure why as they are all just basic formulas, but formatting might get jacked up...
TO BE CLEAR - this template is currently used by PGIM for potential analysts during their interview process. I have a few contacts within Pru, and they have seen this model. Not that anyone would, but to prevent anyone from taking this exact template/example and submitting it for potential job offer, I wanted to mention this and prevent any potential issues.
I have a few other models/case study examples...I will submit them as well when I have time.
Thanks !!
| Attachment | Size |
|---|---|
| MF case study from PGIM 262 KB | 262 KB |
Great job - any reason the sensitivity tables are hardcoded? Seems to be easier just to use the table feature to put those together.
this may be something with the compatibility check. I'm bad at data tables...but from the little I've used them, I always have an issue making them dynamic. Seems that to have the baseline value being an input, you need to have it linked to another data table or the formula becomes broken. sure there's a better way, I just always have issues so play it safe with the only way I know to make it work...
PS just checked the data table issue again. For some reason it switched to hard code when I saved from 2016 to .xls. Normally its live.
Obviously disregard these values if you were to change the inputs on the cash flow portion of the model...
Really well laid out and easy to read/interpret model - well done. Just out of interest - how long did this take you? I assume they would give this to you to do overnight rather than expect this to be done in 2/3 hours?
Also, is there any particular reason you didn't include Y10 NOI when calculating Unlevered IRR (cell R30) - I would have thought that cell value should be $68,008,500?
noticed the same
Do you have a dynamic version of this model?
This is on the easier side of modeling tests, right?
I have a modeling test coming up. Just out of curiosity, did PGIM provide you with the template that you just filled in or did you have to create all of that from scratch?
They give you a PDF of all of the assumptions. It's up to you to build out the rest.
Not in RE, but enjoy browsing other industries models. Thanks for the upload.
Hey guys I know this is an old post, but I have an important question. I tried recreating the model from scratch and came up with similar numbers except terminal value. I believe the OP used the formula =R20(1+D35)/D26-D35 = 65,810,554. I used =(R20(1+D35))/(D26-D35) = 144,783,221. Please correct me if I'm wrong but the OP divided by D26 before subtracting D35. I'd appreciate your response monkeys. Thanks for all your help throughout the years.
I'm not seeing the instructions for Section 2....am I missing something?
TIA
Hello,
Could someone send me the calculations with the formulas included?
Thank you very much in advance!
+ Sb’d
Bump to post updated model with formulas.
Hi guys,
Will face a living/multifamily test soon. I'm having a go now at the CF tab (updating it with formulas). If someone needs it, please let me know! (Btw looking for any test related to Oaktree RE / Victoria AM) thanks!
Would love this. Have an exam coming up shortly.
Had a go at the Cash Flow formulas (really easy, took 15 min, I recommend re-doing this model from scratch) and just done a goal seek for the Q4 (no macro). Could be more dynamic with more IFs... Haven't touched the sensitivities but 2 sec on google and you'll do it easily. Don't hesitate to send questions. Can't manage to drop the file here... Happy to be taught how otherwise drop me an email address in DM and I'll send you the file.
Also: Terminal value I took y10 NOI
Would love to see your newer version!
Hey if anyone is still here lol - where are the instructions for Section 2?
Hi, did anyone manage to get the updated model ?
Thanks
Voluptas rerum sunt ratione incidunt ea suscipit odit. Dicta maxime consectetur pariatur accusantium ea aliquam. Possimus quidem est est reiciendis sint ducimus praesentium.
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...
Sint quam nobis dolores ut. Iusto adipisci earum omnis reprehenderit. Laborum inventore rerum laborum non possimus facere.