This webinar provided an introduction to Commercial Real Estate Financial Modelling using Microsoft Power BI. With financial reporting increasingly using visualization software like Tableu and Power BI, the time is ripe for real estate financial modellers to explore and adopt cutting-edge toolsets. This webinar focused on building intuitive, visual, and highly interactive Commercial Real Estate financial models. The webinar is presented by Bayfield CEO Sonia Martin-Gutierrez and Jay Anantharaman of ValQ.

Microsoft Power BI for Real Estate Financial Modelling

As Jay explains, Power BI has its origins in three different Excel add-ins: PowerPivot, PowerQuery and PowerView. Recently, Power BI has been heavily adopted by analysts, especially in finance across industries, and also for financial modelling. If you are already modelling in spreadsheets, it is a logical extension to try Microsoft Power BI.

Commercial Real Estate Scenario—Net Operating Income 

To display the usefulness of PowerBI, Jay uses a commercial real estate scenario in which he calculates net operating income for a commercial property. As Jay explains, “in the equation, we are going to have revenue and expense line items. Furthermore, we are going to pass on some of the values to the Power BI model.”

Jay begins by providing a traditional spread-sheet model. The model has net operating income, revenue and expense items, and it is driven by its assumptions that are projected for five years. Jay argues that the problem with traditional spread-sheet models is that when you make several changes to your assumptions, it is not easy to understand exactly how they impact the outcome. Moreover, it is hard to compare two or three different scenarios simultaneously. Consequently, Jay believes Microsoft Power BI better handles such financial modelling scenarios.

Live Demo

As Jay explains, Microsoft Power BI desktop software is free. In addition, you can download a resource pack that provides a step-by-step tutorial. The step-by-step tutorial offers an in-depth explanation of building a model similar to the one provided by Jay.

In his live demo, Jay highlighted how intuitively Microsoft Power BI could build a financial model. Specifically, Power BI breaks down cost and revenue items in a tree diagram-like fashion. Moreover, when changing assumptions, you can drag a slider that simulates all affected values (Figure 1). Thus, you can do simulations at the click of a button.

Figure 1

Power BI is a highly effective tool for scenario testing. In Power BI you can combine many of the formula techniques with your data models and see how future results could be impacted across various dimensions and assumptions. You can create numerous scenarios with different assumptions and toggle between all these scenarios at the click of a button to see how it impacts the final results. Finally, you can graphically compute the scenarios and compare them side-by-side (Figure 2).

Figure 2

Once you are inside Microsoft Power BI, you will have many tabs, similar to Excel. The first thing you need to do is to bring your Excel data into Power BI. To do that, go under the Home tab and click on input data from Microsoft Excel spreadsheet. Once you bring it in, you will see the data under the fields pane within Power BI.

If you want to take a look at the imported data, go to the Data tab. Once you have the data, the second step is to import the value visual. Once you are done importing, you will be able to see ValQ (Figure 3). The next thing is to click on the visual to drop on the canvas and then expand to full screen. Upon completing this process, you need to start assigning data to your visual. Make sure that ValQ is selected, then assign your values to the value field. You will realize that the moment you start adding values, the value visual will be activated.

 

Figure 3

 

Next, Jay explained how to create an advanced model, right. Upon clicking on ‘create an advanced model’ it is going to create a blank spreadsheet. Next, you will have to assign data that will allow you to create a blank tree diagram-like structure. The way to do this is first to double click KPI and type in net operating income. Once you are done, click on finish, and net operating income will be at the top. Net operating income needs to have two branches, revenue, and expenses. Adding these is the first step to building a skeleton.

Furthermore, you need to configure the time period. By default, the model handles periods of monthly granularity. After setting your desired time period, you can start assigning data.

Next, Jay provided a demo of how to update real estate taxes. Specifically, click on the model tab, then double click on real estate taxes (Figure 4). Instead of seeing it as data source, go to the drop-down and select multiply. The result is a simplistic scenario analysis. Click on finish and then it will show the value for real estate taxes. Finally, Jay went through a tutorial on how to complete a configuration panel. Specifically, this is the process of assigning KPIs and key inputs. The difference between the two is that key inputs are the variables to play around with, including the vacancy rate, tax rate, common area, and maintenance.

Conversely, KPIs are indicators that you would like to observe, such as your net operating income and effective gross revenue. Once you have decided on your key inputs and KPIs, you can start with your simulations. Once you do this, you would have completed the commercial real estate financial model in Microsoft Power BI, without writing a single line of code.

 

Figure 4