Need feedback on my model(Acquisition)

Hi, everyone.

I drafted an acquisition model to practice with based on a term sheet I found from this forums Model DB(Google Drive).
I would love any kind of feedback from any of you monkeys have time to review this model or if some of you who drafted the same model using the term sheet, I would love to exchange our models as well!

Much Appreciated!!

*edited I found out that I applied inflation rate wrong! a new model version is uploaded also it seems like WSO doesn't let me upload excel with macros. :(

AttachmentSize
Term Sheet 780.17 KB780.17 KB
prudential_model_test_ver0.3.xls 90 KB90 KB

Real Estate Modeling Course

  • Real-life RE Modeling Tests from actual Interviews
  • Various asset classes including multi-family, commercial and more
  • Huge discount - until more tests and cases added

Comments (30)

Apr 28, 2017 - 5:00pm

1) calculate the other income growth rate off of the correct inflationary rate (you have it linked to the rent growth rate
2) I don't see any financing terms provided, may want to building an amortization schedule for the perm

Apr 29, 2017 - 1:09am

Hey, thank you for the feedback.

I realized the inflation rate and fixed it.

Financing term is listed as Debt inputs in the bottom of Input column, I also didn't build amort. schedule since term sheet stated that it will pay interest only for the full duration of the load term and make the full balloon payment at the end of loan term.

Again, I appreciate you taking your time and giving me a feedback!

Apr 28, 2017 - 8:00pm

First, I'm glad WSO does not let you upload a file with macros. Second, you have like 1-2 macros. I'm not saying 1-2 macros is a bad thing, most of my models don't have any.

You have everything laid out in a confusing way, at least for me.

Did you really need to have two entire sets of cash flows, 1 levered and 1 unlevered? Seems like a waste of effort and space in your layout.

Make sure all your inputs are blue font, ALL. sometimes i like to do a light blue shade and blue font.

Make an amort table.

Add a yearly dscr and DY, and especially an outgoing DY you want to make sure this can get refinanced out although more than likely it will just be on the books.

Apr 29, 2017 - 1:20am

Hi,

First, thank you for the feedback!

I made two CF(Levered/Unleverd) for sake of practice.
It's was my first time building a model in English. Since I worked in Asia. So in order for me memorize and get familiarized with English terminology I did some redundancy.

I will mark all the input blue font in the future!

I am curious, you and Link_REDev suggested that I make an amort. Table. But as I mentioned in reply to Link_REDev, with the instructions from the terms sheet why would you need an amort. table??

Thanks!

Learn More

300+ video lessons across 6 modeling courses taught by elite practitioners at the top investment banks and private equity funds -- Excel Modeling -- Financial Statement Modeling -- M&A Modeling -- LBO Modeling -- DCF and Valuation Modeling -- ALL INCLUDED + 2 Huge Bonuses.

Learn more
May 3, 2017 - 7:50pm

Couple of things here:
1) The test says to put capital reserves above NOI, so not as capex, but as a regular expense, just switch it up there. So you're NOI in this case will equal your CF before debt service.
2) When you're capping your NOI for the resale, you normally cap next years NOI (year 11), so it would be year 11 NOI/5.50%, not year 10. Which is normally why you see 11-year cash flow runs for 10 year holds.
3) The Sales Fee should be negative number, these are selling costs incurred by Pru in this case to pay a brokerage firm to market the asset for sale. So it would be : Sales price * (1-2%) = Net Residual Value

Hope this was helpful, good luck man.

May 3, 2017 - 10:26pm
<span>nymetrorealesate</span>:

When you're capping your NOI for the resale, you normally cap next years NOI (year 11), so it would be year 11 NOI/5.50%, not year 10. Which is normally why you see 11-year cash flow runs for 10 year holds.

Good catch, I assumed he used year 11 NOI because he included a year 11 cash flow.

May 4, 2017 - 1:09am

for all the years besides year 10, you have the sale price calculating as NOI times cap instead of divided

May 5, 2017 - 9:56am

Not sure I would copy and paste Adventures in CRE model and call it your own. Sorry not trying to be high and pious here, just wanted to throw that out there. At least reorganize the design or the formatting, so it doesn't render such a resemblance... Especially considering this was likely for an analyst test that you completed for a job opportunity at PGIM...What is the hiring manager recognized it was another person's model? Just a thought to be careful when it comes to models. I know the guys a ACRE post them for free basically, but still...

May 5, 2017 - 11:49am

gonna have to disagree with you a little, but I get your overall point.

OP, at one time or another all of us have started out using and replicating someone else's models. There are certain parts to an excel 3 statement model which are just going to be the same e.g. cash flows, expenses, amort tables, formulas, and formatting(everyone and their mother uses dark blue for fill). I find that it is in your tweaking your model to make it dynamic that you really get the differences. Now not sure you are there yet, but I don't think this will ding you with the company.

Best Response
May 5, 2017 - 12:43pm

I'm indifferent to an extent with this, but since we're talking...I'll write a novel about 3 things:

1.) Why risk it? I know everyone abides by the blue font = inputs cardinal rule, but it took me 2 seconds to look at this model and realize it was from ACRE based on the formatting alone. Check out the website, download the model, and you'll see what I mean.. If I was able to do that this quickly, chances are another person will too. Is it worth not getting a potentially great job because you weren't willing to at least change the formatting? I mean at least change the background from gray to white, move and rearrange the assumptions box, etc...

2) An analyst needs to know how to build a simple CF/DCF model. A full blown acquisitions/U/W model, fine two days is way too little time and is tough for any analyst to genuinely build out. But a simple DCF/CF model is the starting point that everyone should know. I know people at Pru who use these tests, and all of them say this is just a way to make sure the candidate has the bare essentials for the job. Simply, if you can not open a blank excel sheet, and create a CF statement and DCF model for a basic MF building...you should not be working at a place like Pru. Not being mean, just honest. They won't have the time to walk you through and correct your errors, on something that you should be able to teach yourself. All I'm saying is the concepts are very simple...download ACRE model, STUDY it, LEARN it, and then build your own. Oftentimes, company's have you complete these models ONSITE...if not, you'll usually have to walk the manager through your work and explain your answers. PRU is one of the few top shops that doesn't do this. Again, they only view it as a baseline to make sure the candidate meets the minimum requirements (just feedback from a couple hiring level people that work for Pru, so maybe every office is different).

3.) I happen to KNOW that this is the exact analyst test Prudential puts out for candidates during the interview process. How do we not know he's asking people to critique "his" model, make the changes, and submit it for a potential job? I'm sorry, but I don't think that's the right way to do it. Let's say he updates the model with everyone input, takes the template off ACRE, submits it to Pru, and gets the job. Then what? Two weeks later the manager says okay new analyst, we're looking at deal XYZ, model out these assumptions, etc., etc. What happens when the analyst can't do the work? Submit another post on WSO and hope someone gives an answer? Talking yourself into a job does you and the company no good. Anyone who would disagree with this, ask yourself...if you're an MD, responsible for your team and results, and you find out you hired an analyst who submitted a model he got off the internet and doesn't really know how to construct the model he submitted - a model that is a key foundation to the work you'll be doing on a daily basis...would you hire that candidate knowing that information???

Of course, perhaps this was an innocent post where the OP was just trying to sharpen his modeling skills and craft. But to me there's too many question marks, and it just didn't look right.

to the OP, I'm not Einstein. I spent hours and hours on weekend studying every model I could get my hands on. Eventually I got to the point where I could build out these models from scratch. I soon realized that was just the baseline. you NEED to know the concepts and mechanics behind the numbers...otherwise a model is pointless. Please don't take this as an insult to your integrity or harsh criticism - I'm just recommending you go about it a different way, and I think you'll find its more productive to your understanding of modeling and your RE career.

May 5, 2017 - 1:23pm

Obviously didn't know this was the exact Pru model. That changes things a little.

<span>Post hoc ergo propter hoc</span>:

to the OP, I'm not Einstein. I spent hours and hours on weekend studying every model I could get my hands on. Eventually I got to the point where I could build out these models from scratch. I soon realized that was just the baseline. you NEED to know the concepts and mechanics behind the numbers...otherwise a model is pointless. Please don't take this as an insult to your integrity or harsh criticism - I'm just recommending you go about it a different way, and I think you'll find its more productive to your understanding of modeling and your RE career.

Couldn't agree with this part more.

May 5, 2017 - 1:57pm

Ok let me just make things clear since it seems like you didn't read any of my previous comment in the beginning of this thread..

I am not what so ever currently participating or applying to Prudential analyst position.. as i said in the first comment, i found this term sheet from this forum when i searched "models". It was shared by one of the community member on a google drive. I believe people started to share these information for people like me, whom are trying to study and learn RE modle.

Even if you didn't know this information. If you opened up the term sheet. It clearly states that is was for a analyst test on 2015-2016. So i am very offended that you accuse me of trying to cheat my way thorugh a interview process with Prudential.

Secondly, i openly admitted that i incorporated AdventureCRE's template and frankly, i don't see why you are all upset about it. I am sure you know You can't just copy past some else modle and expect it to work out. I only used their formatting(coloring inputs with blues, terminology etc). I did it since i practiced and learned to modle in a different language and when i looked at AdvantureCRE's modle layout, it was completely different from what i was familiar with.

May 5, 2017 - 2:13pm

well, Prudential is currently using the exact same analyst test for positions that are being filled as we speak...so there's no way to have known.

As I said at the end, my goal wasn't to offend you. I know the ACRE model, and this one is very very similar. I just think you should study models which you obviously know how to find because you found the ACRE one....learn the concepts, and practice truly building you own.

Think you would have been better off, and avoided all this confusion, if you simply opened a new worksheet titled "Book1", and started from scratch...hell even using the assumptions outlined in the Pru term sheet if you dont feel like making up your own. But at least any critiques would be based off your genuine approach to a problem, and not someone else's that you just tweaked.

Either way, even if it was cheating on an application to Pru, I really have no skin in this game - just offering advice, which is what was asked in the post...

May 5, 2017 - 2:30pm

I didn't copy paste the excel book.. i started from "book1" as i said before I only beautified my modle using their TEMPLATE..

And does it really matter if i ordered my inputs in same way as someone else's modle? Or colored the boarder the same thickness of someone else's modle?

If Prudential is actually using the same term sheet over and over again to hire new analyst every year. I frankly think they deserve whats comming for them.

May 10, 2017 - 2:31pm

Hi - just someone seeking to really further my RE career ... and I'm extremely green in modeling. Ive been snooping around posts - and I was just wondering if AdventureCRE is adventuresincre .com? Haven't seen that site before and now I'm snooping over there and it looks great! Your model looks great... I wish I could speak more about the calculations. I can tell you what the values mean but I flounder when it comes to the actual building DCF models. Kudos to you! You seem light years ahead of me!

Aug 26, 2017 - 10:50am

Hi, old thread, but I am also working on this model as I am just sharpening my skills. I have a Q on this model. So, on page 3, it says expenses per unit per year is $7250, so for 166 units it is $1, 203,500. Now, shouldnt you deduct this amount from your EGI to get NOI? take EGI for year 1 for example- it is $3,938,308, should you not deduct $1,203,500 to get year 1 NOI? Why did you use the historical expenses trailing 90 days instead? Would really appreciate your help, thank you!!

Aug 27, 2017 - 11:20am

Velit occaecati aspernatur non totam ipsum. Perspiciatis accusantium quod expedita deleniti. Dolores qui est fuga odit.

Corporis quidem inventore ullam quia. Et est aut accusantium natus ea eveniet. Laborum minus omnis expedita delectus ullam doloremque. Et debitis voluptates corrupti tempora qui dolore. Eum architecto laborum quos earum ut. Et et sed neque. Voluptate et excepturi sit vitae.

Eius ut facere earum impedit. Voluptates ipsa omnis fugiat dolores repudiandae.

Aug 29, 2017 - 11:34pm

Veritatis nulla sint mollitia quae. Aut omnis ut quas eos id. Sed quae distinctio quae aliquam et omnis. Vero placeat aspernatur expedita sunt.

Blanditiis voluptas accusantium expedita accusantium eaque. Ratione id soluta enim distinctio dicta.

Velit ut nobis voluptatem sed dolores nihil quo. Est dicta tempore necessitatibus eum molestiae repudiandae. Dicta itaque repudiandae enim aliquam. Ut vel ut deleniti voluptas.

Eligendi beatae qui placeat quos. Cum et vitae quos occaecati molestiae sint accusamus.

Start Discussion

Total Avg Compensation

October 2021 Investment Banking

  • Director/MD (10) $853
  • Vice President (39) $363
  • Associates (229) $233
  • 2nd Year Analyst (139) $155
  • 3rd+ Year Analyst (32) $149
  • Intern/Summer Associate (105) $142
  • 1st Year Analyst (504) $135
  • Intern/Summer Analyst (387) $83