First, we need to understand what a financial modelling is so we can keep this in mind throughout the test and better accept why companies are asking us more and more to go through Excel tests in order to proceed to the next round of interviews.
Financial Modelling in real estate is used as a decision making tool for investment purposes. Companies use financial models to forecast the future of a real estate asset or portfolio with several assets, including partially owned assets such as the case of joint-ventures.
Nowadays Microsoft Excel is a spreadsheet tool which is used worldwide for constructing financial models as it gives more flexibility and transparency. With proper analysis of the model the decisions like investment risk-return analysis and capital requirement can be done.
The typical financial modelling test will consist of a case study whereby we will need to a) prepare a recommendation for this asset to buy, sell or hold; or b) answer a set of questions. The case study will normally present an income-producing (already let) property or one that is partially let. It is unusual that tests ask us to model a vacant property or prepare a development model with multiple scenarios for bare land, site demolition or complete refurbishment. Therefore, we should focus on income-producing assets which will generate rent from day one.
It is also very typical that the test will include debt, i.e. a geared investment. So, we should be prepared for this too. However, this depends on the type of company that we are applying for; if it’s a private equity or hedge fund, it’s a given; if it’s a family office or ungeared fund, not so much so.
Instructions to follow:
- Read the case study carefully. Think about the assumptions and how they relate to each other. Start to strategize about how to tackle the test.
- Read the questions and think about what it is being asked.
For instance, if the questions are:
What is the after-tax income in Year 3?
This gives us an indication that we will need to forecast tax and make assumptions for the tax rate if not included in the assumptions. Also ‘Year 3’ is probably an indication that we can use a yearly cash flow.
What is the geared IRR if the property is sold in year 5?
It looks like they want us to think about selling the property in different years, so we need to be prepared to create a sensitivity analysis on exit year. Therefore, when creating the cash flow, we need to make it flexible enough to adjust it to the holding period.
Prepare an investment recommendation for this asset.
In this case, the sky and time are the limit, and we will need to be very careful with how we allocate time to each of the tasks: building the rent roll function, calculating expenditures, forecasting debt schedule, finding the IRR, NPV, Worth at a minimum and creating a sensitivity table.
We should think carefully about the questions and not panic if we don’t know how to calculate all the figures being asked.
- Check how long you have to complete the test.
The most important success point in any test is how we allocate time for each task or question. Say, we have 3 hours and need to prepare a full recommendation report. We should then split the time as such: 15 minutes reading the questions and thinking about the best points we will make. 15 minutes creating the input, output and model layout. 1.5 hours creating the cash flow and calculating the output ratios. 15 minutes creating a sensitivity table and adjusting the model if necessary. 45 minutes writing up.
If there is a set of questions and 3 hours and it is a multiple choice test, make it 3 minutes for each question. If there are 20 questions, this will make one hour. In this case, we will have 2 hours left: 15 minutes reading the questions and thinking about which calculations will be necessary, 1.5 hours preparing the calculations, 1 hour answering the questions and 15 minutes’ leeway.
- Things we should know how to calculate beforehand:
- Purchase and sale prices excluding transaction costs
- Rents, lease expiries and second leases
- Operating expenses – void rates, letting fees
- Capital expenditures – when the first tenant leaves
- Net Operating Income
- Net Cash Flow
- Senior debt: Interest-only and constant amortisation (I doubt they will ask for constant payment)
- Mezzanine debt: using MIN and MAX function
- Geared Net Cash Flow
- IRR, NPV and Worth (Equity Multiple may be worth checking as well)
- Sensitivity table
- It is almost impossible to know exactly what the test will look like and it is a certainty that we won’t know everything. But we need to give a good go and preparation and time management are key to success.
- After we finish the test, two things can happen: either we feel we have accomplished a mission or we will have a feeling that we could have done more. It’s human nature. Whatever the outcome, we should be proud to have had the opportunity to go this far. Either passing or not, we know that we have learnt something in the process.
Real Estate Financial Modelling Sample Test
Real Estate Financial Modelling Sample Test
If you need to practise, fill out the form below so we can send you a sample real estate financial modelling test.