Modeling Test Solution is driving my crazy

Hi guys,

I received feedbacks for a modeling test I took for an Analyst Position and I'm currently stuck. 
It was a 7-hour modeling test from home (yup weird... A previous analyst from the fund sent me his exam from 2018: an extremely easy 3-hour acquisition model that you can do in 25minutes...)


1st question: 

"Combine the London1 asset and London 2 asset cashflows on a calendar quarter basis. The residual values can be totalled to the nearest calendar quarter, i.e. an April residual value can be treated as a March value."

(I'm going to try to be clear as possible)

In the solution if we take the sheet where he grouped the 2 assets, for the Potential Base rent we have a result that is equal to London 1 Potential Base Rent Q1  + London 2 Potential Base Rent Q1 but the formula is really different... 
(In some blank cell he wrote "2/3")

Formula wrote to find the combined Potential Base Rent:

London2 Potential Base Rent Q1+(London1 Potentiel Base Rent Q2(2/3))+(London1 Potential Base Rent Q1(1-(2/3))

Just why ? Must be something obvious but I'm really stuck to work on the solution and learn from my mistakes... He said that they will contact me for follow-up calls but I'm still waiting...


2nd question:

Basically the test was grouping the 2 assets, model debt assumptions (2 scenarii), then find IRR, Waterfall, Refinancing loan...

"a) You have received terms from a lender who is willing to provide total debt that would result in a 2.5x annual NOI ICR once stabilised (NOI for the next 12 months over interest cost for the next 12 months). The interest rate on the debt will be 4% pa, comprised of a 0.5% base rate + 3.5% margin, and the total cost of financing fees are estimated to be 2.25% (including the arrangement fee / legals etc.). 
The debt will be issued Day 1 at acquisition in December 2018. On the same tab as the combined cashflows, model the financing fees, as well as the total debt and interest cost each quarter. 

b) The lender has also requested that 12 months post stabilisation of the portfolio, there is amortisation of the debt for a period of 1 year at a rate of 1% pa."
 

I would be curious to know what you would have done to model Debt Assumptions from "2.5x annual NOI ICR" and for the "b)"...

Is it me or it was a really difficult modeling test?


Thanks a lot in advance for your returns! 

 

Vel itaque et sed impedit libero. Minima quidem sint quam ut iusto voluptatum. Officiis dolorem ut eaque et qui.

Consequatur voluptatem unde amet voluptatem omnis. Aut voluptatem maiores molestiae sit corporis. Aliquid deleniti error modi autem et ipsum voluptas. Provident quo et molestiae minima ipsum blanditiis cupiditate maxime.

Career Advancement Opportunities

May 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Lazard Freres No 98.9%
  • Harris Williams & Co. 25 98.3%
  • Goldman Sachs 17 97.7%
  • JPMorgan Chase 04 97.1%

Overall Employee Satisfaction

May 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.9%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 04 97.1%

Professional Growth Opportunities

May 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.9%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

May 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (21) $373
  • Associates (91) $259
  • 3rd+ Year Analyst (14) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (68) $168
  • 1st Year Analyst (205) $159
  • Intern/Summer Analyst (146) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
CompBanker's picture
CompBanker
98.9
6
kanon's picture
kanon
98.9
7
dosk17's picture
dosk17
98.9
8
GameTheory's picture
GameTheory
98.9
9
bolo up's picture
bolo up
98.8
10
DrApeman's picture
DrApeman
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”