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. :(
| Attachment | Size |
|---|---|
| Term Sheet 780.17 KB | 780.17 KB |
| prudential_model_test_ver0.3.xls 90 KB | 90 KB |
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
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!
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.
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!
Yeah, i didn't read the instructions.
But that said, it is good practice to make one. All my models have them and it is a good way to check your work in the future.
this is too funny - a while back I had to complete the same analyst test. PS - did you pull your model from adventures in CRE??? the formats look familiar. Cant attach models in comments I guess or else Id share my version.
Hey,
Yea I used a template from AdventureCRE.
If i PM you my e-mail, could you send me your model?
Thanks
David, you left your name on the model.
is that a bad thing...???
Nope, just thought I would be helpful and let you know
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.
Hey,
After posting the model here I found the mistakes you mentioned above as well!
but thank you for taking your time and giving me a feedback!
Good catch, I assumed he used year 11 NOI because he included a year 11 cash flow.
for all the years besides year 10, you have the sale price calculating as NOI times cap instead of divided
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...
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.
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.
Obviously didn't know this was the exact Pru model. That changes things a little.
Couldn't agree with this part more.
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.
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...
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.
also for reference....you can absolutely copy and paste ACRE model, change the inputs, and expect it to work...
Either way - I dont want to detract from the purpose of this thread...so ill sign off from the topic..
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!
ctrl+f "modle"
google GFYS.
Hey david
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!!
When I drafted the version which is posted above, I missed the expenses line so I used the historical trail.
you should use $7250. :)
Et ullam sit aliquam totam voluptatibus. Quam explicabo rerum et distinctio qui amet. Aut tempore ea rem quos. Placeat nobis temporibus totam voluptatem in qui sed.
A quis laboriosam velit ullam nesciunt vitae. Ut nobis earum corrupti magnam quis et laudantium animi. Inventore repudiandae doloremque et est at dolores. Rerum nihil accusamus dolorum voluptatum eaque ut. Et iste animi est harum dolorem. Deserunt dolorem tempora vitae minima sit voluptatum consequatur. Enim dolores numquam reprehenderit ut. Est sint velit qui cum nam.
Quo nihil sed recusandae repudiandae id. Laudantium quia aspernatur sed maxime. Culpa sit animi et consequuntur minus ad qui.
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...
Dolore numquam suscipit sequi molestias vel velit quae harum. Nesciunt ab architecto reiciendis qui. Aut aspernatur tenetur quo debitis minima deleniti explicabo. Tempora eum libero eos et. Eum nulla consequuntur corporis id sed exercitationem. Enim fugit debitis et a non quis. Magnam blanditiis doloremque et et et debitis reiciendis.