Author: Ryan Spear

Barely utilised, but incredibly powerful, the Power Query functionality in Excel has been available for over a decade, however financial professionals can often overlook the huge value it can add to their business. The prime example being the role it plays in your property cash flow models.  

Whether you’re modelling a new acquisition, preparing for a disposal, or assessing your ongoing portfolio cash flows. Power Query will not only save you hours of work, but it also provides insights that you potentially didn’t know were available.  

 Power Query will automatically transform and aggregate data from as many excel workbooks as you like. Read on to find out how you can leverage it in your business.  

Financial Model Aggregation 

Say you have ten different workbooks each containing its own model, cash flow, or property information. It could be an asset for purchase, a monthly report, a quarterly valuation or annual budget for each division.  

Whilst comparing metrics and numbers, you end up with multiple workbooks open at the same time, whilst desperately trying to copy and paste values or link up workbooks with external references to another workbook, Excel crashes on you!

To prevent this from happening, you can follow these steps:  

Your folder should contain all your workbooks that you want to aggregate data from, Power Query will allow you to manipulate the data no matter the format, generate reports, group, sum, count, average - anything you need! 

An example of Power Query in use 

You can use Power Query to extract property information from over a hundred property models whilst aggregating a portfolio of income and outgoings automatically.  

At Forbury, we used Power Query for a client, to extract property information from over 100 property models. Automatically, we were able to aggregate a portfolio of income and outgoings. This automatically built a model that could identify the clients biggest liabilities, highlighting future months that would impact our overall portfolio performance instantly.   

A great advantage is that once you’ve formatted your query once, you can run Power Query again automatically. This serves as an instant automation, extracting everything you need from a collation of workbooks in seconds. Giving you the ability to update any of your properties within your Excel model as part of your day-to-day, and all you need to do is refresh your workbook and you’ll instantly, have a summarised report of over 100 workbooks. 

There is, however, a large pre-requisite to ensuring this works flawlessly and that’s the use of keeping standardised workbooks.  

Standardised Workbooks 

Keeping standardised workbooks can be a challenge, especially when your workbooks are in different formats, on various sheets - making extracting data from them difficult. At Forbury we look after standardisation for you by providing model templates that scale with changes. We ensure that your models are always on the same version of the template, no matter when you created them. That means that deals worked on three years ago can be in the exact same format as deals modelled up today.  

Conclusion

Your company likely has plenty of models available that aren’t being fully utilised. These provide huge value and insight which are waiting to be discovered. By using Power Query, you’re able to pull out formatted, consistent data at the click of a button.  

If you’re keen to learn more about Forbury, you can book a demo  here, or reach out to our UK Sales Manager  ed.ohanrahan@forbury.com  for more information.