Announcement
Contact us today to get your free copy of our Top Ten Excel Power User Tips for Accountants and Financial Analysts. Our Top Ten list is 60+ pages of useful tips gained from years of experience using Excel in a variety of business environments. Just send an email today to info@rpscgi.com, with “Top Ten Tips” in the subject. Please be sure to include your name, company and business title.
Background
Though Excel provides built-in functionality to consolidate worksheets, it is a bit overly complex for most situations. The solution that we frequently utilize does consolidate worksheets, without using Excel’s consolidation function, while also giving users more flexibility to change what is being consolidated. Typically, we will create a series of financial statements that need to be consolidated into a consolidated enterprise. [Note: Because we typically are consolidating financial statements, most of this tip will be referring to the consolidation of financial statements.] The supporting financial statements may represent lines of business or divisions or some other entity type. By using our 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. The template looks as follows:
The above worksheet is the template for our consolidated financials as well as for the four LOBs with year 1 starting in column F, and the total market for each LOB starting at row 9. Each of the LOBs and consolidation worksheets need to be structured the same, for all cells to be consolidated. So if the 2011 revenue forecast for LOB1 starts at LOB1!F13, with the 2012 forecast at LOB1!G13, then it will be the same for all the other LOB worksheets, as in the following:
Worksheet 2011 2012 2013
Revenue LOB1 LOB1!F13 LOB1!G13 LOB1!H13
Revenue LOB2 LOB2!F13 LOB2!G13 LOB2!H13
Revenue LOB3 LOB3!F13 LOB3!G13 LOB3!H13
Revenue LOB4 LOB4!F13 LOB4!G13 LOB4!H13
Given the above format, you could easily consolidate the 2011 forecast with the following formula on the consolidation worksheet:
2011 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. Our approach to flexible consolidation eliminates the need to individually reference each worksheet cell as was done above. 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”. The names can be whatever you like, but should be descriptive. Once the “Start” and “End” worksheets have been created, we insert the individual LOB worksheets between them. When you have created the five worksheets (Summary, LOB1, LOB2, LOB3 and LOB4), and two bookends (Start & End), the ordering of the worksheets needs to appear as follows:
The “Summary” worksheet is where all the worksheets will be consolidated. In order to SUM the four LOBs, you simply need a formula that can SUM across all the worksheets from “Start” through to “End”. With the 2011 revenue projections for each LOB at F13 on each of the four worksheets, the 2011 revenue can be consolidated on the Summary worksheet at Summary!F13 with the following formula:
What is being consolidated: Year 1 revenue for all LOBs
At Cell Address: Summary!F13
Formula: =SUM(Start:End!F13)
Our new formula–SUM(Start:End!F13)–is equivalent to =Start!F13 + LOB1!F13 + LOB2!F13 + LOB3!F13 + LOB4!F13 + End!F13. But the new formula is all we need to summarize all four LOB worksheets onto the Summary worksheet. Now, if we are asked to display the consolidation without LOB4, we simply drag the LOB4 worksheet beyond the “Start” and “End” bookend range. Once we move the LOB4 worksheet beyond the “End” worksheet, the worksheet tabs now appear as follows:
With LOB4 outside the SUM formula range, the values at Summary!F13 will only include LOB1, LOB2 and LOB3. And voila, LOB4 is now excluded from the consolidation, without having to adjust a single formula. 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.
Our example is rather simplistic. This approach can be used in larger consolidation. Assume that we have to restructure a sales force with 400 Territories. Each Territory tiers into a Region, of which there are 20. Each Region tiers into a District, of which there are four. The four Districts tier into the total company. Though this is rather large, if each territory is a worksheet, our approach would permit the required flexibility needed to restructure the entire sales force. The worksheets might be organized as follows:
Region1Start
Terrritory001
Territory005
…
Territory150
Region1End
Region2Start
Territory250
Territory305
…
Territor395
Region2Emd
Through all 20 regions
District1Start
Region1Summary (This summarizes Region1Start:Region1End)
Region2Summary (This summarizes Region2Start:Region2End)
…
Region7Summary (This summarizes Region7Start:Region7End)
District1End
District2Start
Region9Summary (This summarizes Region9Start:Region9End)
Region10Summary (This summarizes Region10Start:Region2End)
…
Region15Summary (This summarizes Region15Start:Region15End)
District2End
Through all four districts
TotalCompanyStart
District1Summary (This summarizes District1Start:District1End)
District2Summary (This summarizes District2Start:District2End)
District3Summary (This summarizes District3Start:District3End)
District4Summary (This summarizes District4Start:District4End)
TotalCompanyEnd
TotalCompany Summary (This summarize TotalCompanyStart:TotalCompanyEnd)
With this structure, without having to manipulate formulas, we could change the composition of our regions and districts very efficiently with only our mouse.
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. Hopefully you have found this useful. Send us a message or check out our website at www.rpscgi.com.
RESOURCE PLANNING SOLUTIONS
Email: info@rpscgi.com
Web: www.rpscgi.com
Check out the video for this blog below:
http://www.youtube.com/watch?v=5hb5fFN2chk
