This video will outline a macro-free approach to execute effective consolidation of data, which in this example will sum capital expenditure across three business units for strategic planning purposes.

Strategize Financial Modelling – Excel Data Consolidate.xlsb

 

Excel Consolidate function

The Consolidate function enables users to summarise and report results from separate worksheets, by consolidating data from each individual worksheet into an aggregate worksheet.

After selecting Data > Consolidate, you will arrive at the following window:

 

Curiously, Excel Consolidate allows users to aggregate data via a number of Excel function – not just the SUM function.

 

Consolidating Financial Information

We can now aggregate forecast capex from three separate business unit worksheets into our consolidated financial model.

Firstly, the individual business worksheets must be open in the same application of Excel – in order to correctly execute the Excel Consolidate function.

We now have the worksheets for the business units, Gamma, Alpha, across the entire forecast year by month, and by line item.

And finally the Delta business unit. Note, Excel Consolidate will aggregate these line items, even if the individual worksheets have different time-series data.

 

Excel Consolidate Parameters

Before we start consolidating our capex data by business unit, there are some key points to know about Excel Consolidate:

• Consolidate allows us to summarise data from multiple sources into one output.
• Worksheets can be consolidated from the same workbook or independent workbooks.
• Excel will enable this consolidation to be carried out based on category, formula or position
  

Category means by line item in the row or column. Formula applies cell references to the source worksheets to achieve aggregation. Position can be applied if all worksheets are the same in order and position.

This capex example will be based on category, because we are using the same row and column labels, in order to match the data into the output worksheet.

 

Executing Excel Consolidate

After selecting Excel Consolidate, under the Reference section, we need to load the respective reference to each source worksheet range.

We select Alpha worksheet first, because it contains the earliest column of data – January 2016. Remember to return to the Consolidate window, and clicking Add to guarantee the successful uploading of this range.

We then clear Alpha’s range in the Reference window, and move onto the other business worksheets.

It is preferable to check the boxes for Top row, Left column, and Create links to source data.

As soon as we complete the uploading on these source worksheet ranges, we can simply click OK and Excel will almost instantly generate the consolidate view.

 

The Consolidate View

We have the forecast capex line items down the worksheet. We have the time-series correctly presented across the worksheet. The clear value of the Excel Consolidate tool to aggregate capex line items across months – by business unit file is evident.

Given the link to source option was checked earlier, this schedule is presenting the source cell address reference for each line item. Finally, we will use the AGGREGATE function to sum the high-level total amounts by line item. Making sure to ignore hidden rows.

Excel Consolidate permits users to drill into the granular detail by line item, or hide such detail and merely analyse the high-level view by these line items.