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.
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 this window.
Curiously, Consolidate allows users to aggregate data via a number of Excel functions
– not just the SUM function.
We will 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 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, Consolidate will aggregate these line items, 0:01:25.066,0:01:25.000
even if the individual worksheets have different time-series data.
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.
And 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.
And Position can be applied if all worksheets are in the same order and position.
This capex example will be based on category, because we are using the same row and column labels
After selecting 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 of these source worksheet ranges,
we can simply click OK
and Excel will almost instantly generate 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.