Though Excel provides built-in functionality to consolidate worksheets, it is a bit overly complex for most situations. This solution consolidate worksheets, without using Excel’s consolidation function, and also gives users more flexibility to change what is being consolidated. By using this approach to consolidate worksheets, we can instantly modify the composition of our consolidation by either adding or removing worksheets from the consolidated total
The one limitation to this approach is that each worksheet needs to be identically structured relative to the items that are going to be consolidated. Generally this requirement is not particularly onerous given the nature of worksheet consolidation. In addition to the line of business-level worksheets, we will need one worksheet that sums all the lines of business as a consolidated financial statement
Application
To illustrate, assume that we have a business consisting of three lines of business (LOB) titled LOB1, LOB2 and LOB3. Each LOB's P&L and balance sheet is additive to the consolidated financial statement. In our example, the leadership team is considering adding a fourth LOB and wants to be able to easily consolidate or exclude the fourth LOB, to better analyze LOB4’s impact on the consolidated financial statements. This will require five worksheets, one for each of the four LOBs and one for the consolidated business. We start by creating a template for our financial statement with the years going across the columns. Each of the LOBs and consolidation worksheets need to be structured the same, for all cells to be consolidated.
You could have consolidated as follows
2015 Consolidated Revenue = LOB1!F13 + LOB2!F13 + LOB3!F13 + LOB4!F13
The issue that arises is when you want to remove one of the LOBs from the consolidation, which means that every formula referencing LOB4 needs to be edited.
In order to make use of the flexible consolidation approach you will need to add two extra worksheets that establish the range. We usually name the two worksheets “Start” and “End”. We insert the individual LOB worksheets between them. So there are 6 sheets now (one for each LOB and one start and one end). The consolidation worksheet will be outside the start and end sheets.
Now the new formula in consolidation worksheet is Formula: =SUM(Start:End!F13)
Now, if we are asked to display the consolidation without LOB4, we simply drag the LOB4 worksheet beyond the “Start” and “End” bookend range. This approach will allow us to create some very sophisticated reporting and analysis spread sheets, while permitting addition and deletion of worksheets within our consolidation, with much less formula editing
The one caveat to this approach is that the bookend worksheets (Start and End) are part of the SUM function, which means those worksheets must be blank; otherwise the SUM function will include any data on those two worksheets
Vamsidhar Ambatipudi
www.pacegurus.com
The one limitation to this approach is that each worksheet needs to be identically structured relative to the items that are going to be consolidated. Generally this requirement is not particularly onerous given the nature of worksheet consolidation. In addition to the line of business-level worksheets, we will need one worksheet that sums all the lines of business as a consolidated financial statement
Application
To illustrate, assume that we have a business consisting of three lines of business (LOB) titled LOB1, LOB2 and LOB3. Each LOB's P&L and balance sheet is additive to the consolidated financial statement. In our example, the leadership team is considering adding a fourth LOB and wants to be able to easily consolidate or exclude the fourth LOB, to better analyze LOB4’s impact on the consolidated financial statements. This will require five worksheets, one for each of the four LOBs and one for the consolidated business. We start by creating a template for our financial statement with the years going across the columns. Each of the LOBs and consolidation worksheets need to be structured the same, for all cells to be consolidated.
You could have consolidated as follows
2015 Consolidated Revenue = LOB1!F13 + LOB2!F13 + LOB3!F13 + LOB4!F13
The issue that arises is when you want to remove one of the LOBs from the consolidation, which means that every formula referencing LOB4 needs to be edited.
In order to make use of the flexible consolidation approach you will need to add two extra worksheets that establish the range. We usually name the two worksheets “Start” and “End”. We insert the individual LOB worksheets between them. So there are 6 sheets now (one for each LOB and one start and one end). The consolidation worksheet will be outside the start and end sheets.
Now the new formula in consolidation worksheet is Formula: =SUM(Start:End!F13)
Now, if we are asked to display the consolidation without LOB4, we simply drag the LOB4 worksheet beyond the “Start” and “End” bookend range. This approach will allow us to create some very sophisticated reporting and analysis spread sheets, while permitting addition and deletion of worksheets within our consolidation, with much less formula editing
The one caveat to this approach is that the bookend worksheets (Start and End) are part of the SUM function, which means those worksheets must be blank; otherwise the SUM function will include any data on those two worksheets
Vamsidhar Ambatipudi
www.pacegurus.com
No comments:
Post a Comment