Forecasting, Profitability and Financial Modelling

Key Points

  • Create more maintable financial models and dispense with manual data integration, VLOOKUPS, and nested Excel calculations
  • Use the latest Excel modelling Add-Ins or develop models outside of Excel using an OLAP database
  • CMBI can develop the model in partnership with your analysts, accountants, or statisticians
  • CMBI use the latest inexpensive BI technologies for the most cost effective solution

CMBI develop solutions for Profitability, Forecasting, and other more complex financial spreadsheet models using the latest self-service BI tools that go beyond the capability of traditional Excel modelling without the costs associated with Enterprise BI and Reporting projects.

About CMBI

Are your spreadsheet models starting to creak?

One of the most common dilemas for companies today is how to avoid too much reliance on spreadsheet models that are large, slow and difficult to understand and maintain. The ongoing manual maintenance and data integration consume precious resources, whilst the potential for mistakes adds to your company's operational risk.

Beyond the spreadsheet with CMBI

CMBI have a number of pragmatic alternatives to traditional spreadsheet development. We have the knowledge of VBA, Excel formulas, and data analysis to understand and untangle your existing models and requirements, but with skills in a new generation of self-service BI technologies to create larger, more extensible models, whilst ensuring the solution remains maintainable. Technology solutions range from Excel Add-Ins like Power Pivot and Power Query through to OLAP databases like Analysis Services.

Extending Excel

With the help of these inexpensive technologies your models can consume vastly more data, and provide a broader range of functionality including, What-If analysis, scenario modelling, data mining, time intelligence and more, all within the familar Excel environment .

Working with you

CMBI work with your analysts, accountants, and statisticians to build your models so that you continue to own, understand, and maintain the solution. CMBI have experience working with complex domains ranging from pharamaceuticals, banking, financial accounting, and advanced sales performance reporting and benchmarking.

Contact CMBI for a discussion of your modelling requirements and read on to understand more about Power Pivot which is one of the best and most cost effective alternatives to traditional spreadsheet modelling.

Sydney Property Trends model sample

CMBI's Colin McGowan has developed this NSW Residential Sales Property Growth Power Pivot model using Excel 2010 with the Power Pivot 2012 SP1 Add In which is FREE from Microsoft. For your convenience you can download a pre-converted Excel 2013 model too.

Introduction to NSW Residental Sales Property Growth Power Pivot Model

The NSW Residential Sales Property Growth Power Pivot model demonstrates some key features of Power Pivot modelling, DAX calculations, and Excel visualisation using data that is engaging yet relatively straightforward to understand. The workbook contains a number of worksheets which are briefly described below.

Overview sheet

The Overview sheet allows you to analyse sales property growth for houses, units, or all property in NSW for any period between 1991 and Q1 2015. The data is grouped by Local Government Area (LGA) and can be filtered by Rings which are defined by their proximity to the CBD. The Overview sheet uses the following features

  • Excel Pivot Table linked to Power Pivot model
  • Conditional formatting linked to Pivot Table measure in Power Pivot model
  • DAX time intelligence and advanced DAX calculations using CALCULATE statement for measures and calculated columns
  • Slicers linked to the Pivot Table which act as parameters for viewing different data cuts


NSW Property Trends in Excel Pivot Table with conditional formatting

Price Trend sheet

The Price Trend sheet allows you to visualise growth for one of more LGAs. The Excel Chart is linked to the Power Pivot model via a Pivot Table in a hidden sheet. Interact with the Slicers to add or remove one or more LGAs


NSW Property Trends in Excel Pivot Chart

Growth sheet

The Growth sheet uses Excel conditional formatting to provide a Heat Map effect for visualising growth for NSW Residential Property. The user can adjust the time period or see more LGAs in the table on the right of the sheet by interacting with the Slicers


NSW Property Growth statistics in Excel Pivot Table with heat-map style conditional formatting

Time Intelligence sheet

Time Intelligence is a key feature of Power Pivot. DAX Time Intelligence functions are used in many of the calculations in this model. This Time Intelligence sheet makes it easier for users to see the behaviour of different combinations of DAX Time Intelligence functions against a single value


NSW Property Growth statistics using DAX Time Intelligence functions

Technical details about the sample Power Pivot model

The workbook is unprotected and you are free to add your own visualisations and calculations to the model. If you have any feedback about the model or notice any errors or points for improvement please contact colin@cmbi.com.au

Power Pivot model data

The model uses data from the NSW FCS Government Department website. You can obtain the original data here. CMBI have manipulated this raw data using Power Query to create the final tables seen in the model. These tables are:

  • Area Region - which is a list of geographic regions at various levels within NSW
  • Median Sales Price Residential Property - this table contains all the median sale price data for each quarter in the period and for each Area Region
  • Date - a distinct list of quarter end dates used for the median sale price data

The median price data in the model is non-aggregatable. It is not valid to add two median values together. Instead the calculations focus on comparing these median values over time. Although we cannot aggregate the median prices, the Area Region table contains geographic regions at various levels and the median prices for these aggregate region-levels are contained in the data set as pre-calculated values.


NSW Property Growth Excel Pivot Pivot Model Diagram view

Power Pivot model calculations

The model defines a number of calculations using DAX. DAX is similar to Excel formulas but more powerful for advanced modelling scenarios. You can inspect the formula behind any of the values in the Pivot Tables by opening the Power Pivot window and selecting the Median Sales Price Residental Property sheet in the Data View from the Home tab. The bottom panel of this screen shows all the calculations and clicking on one will show the DAX formula in the formula bar


Power Pivot window showing a DAX calculation in the formula bar

DAX formulas are very powerful and can be used to solve most modelling and analytics requirements. Some features include

  • Time Intelligence which can be used to calculated YTD, parallel periods, growth, and opening and closing balances
  • CALCULATE statement that can manipulate the context of a calculation for complex set based analysis or advanced comparisons and correlations
  • Text manipulation to add groupings, concatenate columns, or create additional descriptive attributes
  • Most Excel formulas can be translated into DAX formulas. DAX also has capability beyond standard Excel formulas.


Power Pivot formula bar showing DAX calculation using the CALCULATE statement

Want to learn more about alternatives to traditional Excel modelling?

CMBI's Colin McGowan provides onsite training for all levels of Power Pivot users and developers. Please contact colin@cmbi.com.au to arrange a training sesssion or learn more about training and mentoring options with Power Pivot and other BI tools

About CMBI

CMBI designs and develops business intelligence and analytics solutions for clients in Sydney and regional NSW. CMBI technology expertise includes Power BI, Power Pivot, Excel, SQL Server, Analysis Services, SharePoint BI, and Tableau

See Also