This blog post is about what I call a Business Model renovation which I call a workbook where the client won’t allow any major changes but I have to fix or add extra features. I took on this major job about two and a half years ago to do major alterations to a crucial, complex, legacy spreadsheet for a large Brisbane based company with 16 entities around Australia and overseas.
It was a tricky, challenging job that had the potential to really go wrong, but thankfully it went very well because I think I established a good process in attacking the problem. Because I was quite nervous of how this task might go wrong, thankfully I spent quality time up front to plan & design the best way to approach this project.
So my client, a major business with multiple overseas business units had a business model of approximately 50 sheets that was used for obtaining loans from its banks. These banks were very comfortable with this workbook the way it was and was available to them from time to time as part of the loan agreements. So the client did not want the model to visually change much.
This Business Model (which truly was a whole of business model) was in Accounting financial statement format with months across the page in columns. I suppose give earlier posts on this topic, I should mention having the time dimension across the page in a model holding data is never ideal. Far better to having the time dimension as one column and so months would go down the page. But I wasn’t hired to do a re-write. In summary this workbook had:
- Profit & Consolidated group level Loss and Cash Flow forecast and actuals consolidated sheets
- 16 business unit entry sheets for actual and forecast data entered by each entity’s accountants
- 16 more sheets using this entered data for each entity to show a high level P & L and Cash Flow for that entity
- It also had group level consolidated snapshots on sheets for financial years in separate sheets and Bank Forecasts
- Elimination entry sheet. assumptions sheets for debt and working capital
Just to add some more spice to this mix, as any accountants might suspect, the rows had a mix of formulas that varied within that row for actuals in hard code and forecasts as the formulas. Sound familiar? This also is not ideal and not something I would not have if I were building this mode from scratch. If data is kept separate from output (reports) in financial modelling you can easily have the one formula all the way across the row which looks for an Actual vs Forecast flag to decide which data to show.
Anyhow, the task was to dovetail a balance sheet section (as provided by the client’s accountant) to this workbook for each entity as well as a consolidated balance sheet for the group and incorporate some movements of the balance sheet to the cash flow areas where appropriate. And what ever you do don’t change the look of the workbook. I am sorry I can’t show you better screenshots below for client confidentiality reasons which makes this post a harder read I know. But below is a Worksheet Map of the finished version that gives some understanding which I explain later on where I got this.
So this job was actually like a house renovation hence my title for this post. There were sections the house owner needed to leave intact, but he wanted new sections which caused under the skin alterations to existing sections. Most builders would agree, a new house is far easier to build than renovating an old house. But in the end this job went so well and I just thought it’s worth sharing the process I took to this very real world problem Excel Analysts or contractors often face.
The process I came up with was as follows:
- Base data – lock away copies of the original workbooks safely aside in a folder so I could be absolutely sure of the original business model and data. No brainer.
- Review of workbook – Gain a practical working understanding of the workbook and each sheets job – in particular I would need to determine the each business entity sheet’s differences if any. Check for constants, merged cells, hidden rows or columns. Are there any hidden sheets you need to be aware of – are there any Very Hidden sheets (which can only be made visible within the VBE module). Are formulas different within the rows and if so from where to where.
There are tools out there to help with this kind of’ review. The one I really like and use is the very inexpensive John Walkenbach PUP Tools which does a host of useful things but it in particular produces a Worksheet Report which is a great place to start getting that understanding of a messy, complex legacy spreadsheet. I did a simple diagram to help my understanding.
- Harmonize – each business entity sheet have to be adjusted so all were exactly the same in terms of dimensions, cell positions column and row headings formulas and format eg Interest had to be in row 32 in each sheet and July 2015 had to be column Z in each sheet. So no formulas were changed just moving rows and columns at this stage. If you deleted below and to the right of each business entity sheet then again, the PUP Tools Worksheet Report with it’s Used Range, Used Rows and Used Columns helps show consistency of the altered sheets.
- Test – after this harmonizing step stop and Test the workbook is delivering exactly the same results as the original before proceeding. To do this I use temporary helper rows and columns extensively. For instance a row of values being the addition of a P & L column including subtotals (although meaningless in itself) can be compared to the same addition in the changed P & L and it must be the same.
- Create a template – by this I mean a ‘template’ worksheet (nothing to do with Excel template workbooks) where you will do all formula changes and additional lines which will then be the model to be used to copy to all other sites. This template needed to be dynamic in the sense the formulas would adjust to the business unit it was copied to and then each business unit has exactly the same formulas and only the business entity name is different on each sheet. So these same formulas need to work for business entity A and for business entity B after being copied. The advantage being that you only have to maintain one set of formulas. I used a INDEX + INDIRECT construct and I am the first to be wary of using the volatile INDIRECT function which knocks around your spreadsheet performance. But at the time I couldn’t think of an alternative and it was only in a dozen or so lines for for about 16 sheets. It worked like a charm. Basically each cell that needed entry data had this kind of formula =INDEX(INDIRECT($G$2),$AQ9,Z$199)*Z$203/1000 so the right data is in from the Entity entry sheet. G2 was the data entry sheet name and “!” and range construct (=”‘”&A2&” F’!”&Settings!$AY$6) that INDEX would see as an array. AQ9 was from a helper column showing the row number from the entry sheet to be used for this line and Z199 was a helper row showing the column index number.
- Quick and dirty macros – needed for speed and consistency but not too fancy because these will be just used a few times to make the changes you want consistently to every sheet. Yes you could do the changes manually but to consistently do the same action across 16 or so sheets, well I would prefer to do it by macro. Plus chances are you are going to have changes or unforseens anyway so the macro will save you time each time that happens. Even if you are not strong with VBA a recorded macro with logical changes will suffice. I will do a further post on one I used.
- Test – after using the macros check, check and check the output to ensure past data hasn’t changed, new calcs look sensible and cross check where you can to the data entered.
I hope this post helps someone with a messy project like this. I really enjoyed this job and got immense satisfaction from pulling it off.