Real Estate Portfolio Dashboard Model
!! Kindly use the Microsoft 365 Excel before purchasing the model, otherwise the dynamic dashboard will NOT work.!!
Real Estate Portfolio Dashboard model presents a series of dashboards that will allow the user to check the most important metrics of a real estate portfolio such as number of properties, square feet, rent amount, costs, net operating income, investment, valuation, vacancies, etc.
In case you are prompted by excel to enable content, kindly do so to enable the data model.
So, a quick overview of the model, in the contents tab you can see the structure of the model and by clicking on any of the headlines to be redirected to the relevant worksheet.
On the Inputs tab, there is a description of the color coding: Inputs are always depicted with a yellow fill and black letters, and calculations are depicted with white fill and black characters.
There is also a color coding for the various tabs of the model. Yellow tabs are mostly assumptions tabs, grey tabs are calculations tabs, blue tabs are outputs tabs (that is effectively results or graphs) and finally light blue tabs are admin tabs (for example: the cover page, contents, and checks).
The only tabs where the user input is needed are colored in yellow (those are Inputs, Characteristics, Rents and Costs). The user needs to fill the yellow cells in each of these tabs, all the remaining tabs are calculated automatically.
In the Inputs tab the user sets the model’s name, responsible, currency, property type items (to categorize the property).
Moving the Characteristics tab, the user sets for each ID the Property Description, Country, Property Type (through a list), State, Beds, Square Feet, Purchase Date and Amount, Refurbishments, Exit Market Cap Rate, Sold Date and Sold Net Amount. The remaining columns in blue and light blue are calculated.
In the Rents tab, the user fills in the yellow cells the monthly rent (or other income from the property). Grey cells indicate the property is not owned and is either sold or not purchased yet, so don't populate those cells. If the property is vacant for whatever reason set the rent to zero.
In the Costs tab, the user fills in the yellow cells the monthly costs related to the property. Grey cells indicate the property is not owned and is either sold or not purchased yet, so don't populate those cells. If the property is vacant, you can still set costs here related to repairs etc.
As soon as you have filled in the yellow cells, in the yellow tabs, go to Data and press Refresh All. This is done to update the dashboards with your new data (tabs All, Properties, Portfolio Worth, Exit Metrics, Occupancy).
Let's briefly go through the dashboard views. In tab All, the user can see an annual summary of the model: the number of properties, square feet, rent, costs, NOI, and cash flows. Additionally, the user can see the investment split as purchase cost and refurbishments, entry and exit cap rates, as well as valuation amounts, sold amount.
You can play with the slicers to see actuals and forecast data, annual summary year selection, and sold date filter.
In the tab Properties, you can select the ID and the Date, as well as the Actuals, and see summarized data for each property as well as trends for rent, net operating costs, cash flows. Finally various feasibility metrics are presented for properties sold.
In the Portfolio Worth tab, the user can see the unsold properties and various metrics such as square feet, property type, valuation, rents, net operating income and total investment. If desired the user can select specific IDs or States to see those metrics.
In the Exits Metrics tab, the user by selecting only the sold properties, can see various key performance indicators for the property sold.
Finally in the Occupancy tab, the user can select the date range, as well as the ID or State and see the vacancy and occupancy rates, the occupancy trend, the occupancy per State, the average rent, net operating income, the average beds, the average square feet, and the entry and exit cap rates.
Also please note that all data used are dummy values to populate the model.
Lastly, the checks tab where the most critical checks are aggregated. Whenever you see an error message in any page, you should consult this page to see where the error is coming from.
