Looking for some advice from others who have developed using the Multi-Business Unit Model. I have created a simple 5yr, monthly model for a client of a mutli-site gym who is undertaking a merger with another similar business, creating a 12 site model, currently in London, but potential to expand (as a combined entiity outside of London). So I had anticipated another half dozen sites to be added. My COA is not overly complex with 21 P&L accounts, and I am using just two BU Classes(Company and Region). I started noticing some longer time delays in Modano adding my 13th and 14th sites, but was not overly concerned.
BUT now I face the prosect of a third target merger in the same sector and with it another dozen sites, and now potential for a combined entity to expand nationally, so 40, 50, 60+ sites. I am seeing ~10 mins for a new Category BU to be added (mirrored), from current 29 BUs and fear that this will continue to increase as I push this, so something has to give because this is not practical.
I have considered starting over with an Annual model, or maybe quarterly - OR just cutting the COA down from 21 to half with some hard pruning on the Opex side. Both will impact my deliverable to client, so not overly keen, but functionality is key ultimately.
So my question to this audience is: has anyone else hit this issue and have you addressed either with a better Time Period re-build or does the COA size make a big difference?
Thanks for reading - appreciate any input on the subject.
Kevin
Hi Kevin,
Without seeing the model it's obviously hard to pinpoint the major cause of the extra time it takes to add a business unit. There are often many factors including but not limited to:
However, the thing I would check first are the number of rows on each sheet. When an Excel sheet hits about 1000 rows the speed of row insertion and calculation often falls off a cliff and it is a sudden change from being completely manageable to completely unmanageable. I think for you the sheet that will pose the most problems is the financials. The first thing I'd do is put the P+L, Balance Sheet and Cash Flow Statement on separate sheets. Also if you have put all the business units on the same sheet then I'd look to split these so that you don't go over 1000 rows.
Hope that helps.
Thanks
Michael
Michael,
I was not aware of the 1,000 row milestone as being one of Excel's "cliff edges" - I can certainly play around with the model and see how it behaves with this in mind.
However, I can see 1,000 as being something I will hit if I take this further and double the BU list. As such I think a major change in COA depth or time periods is needed.
There is very little functionality added to the MBU template, so I don't think those functions will cause an issue.
Let me go away and experiment. I may fire you a support ticket and buy some time if this gets too much to fathom.
thanks
Kevin
Hi Kevin,
As per Michael Lees' comments above, this is an area we've spent thousands of hours analyzing due to the fact that it's a key factor determining what can practically be done using Modano.
As Michael notes, we've found that in models with a large number of rows and columns (e.g. monthly rolling models), there is an exponential increase in the time taken to insert/duplicate/mirror modules once the number of rows containing formulas on sheets surpasses 1,000 rows. But this statement overly-simplifies a very complex area, as it's actually a product of a wide range of things that impact Excel's formula updating algorithm, with obvious ones being the number of looking functions (INDEX, MATCH, SUMPRODUCT, SUMIF, etc.) and the number of rows/columns containing these formulas on each sheet.
Whilst unfortunately we have not found any specific rules which can be optimize any model, we have found that limiting sheet depth and the number of lookup functions is usually the key to preventing module insertion/duplication/mirroring delays. Also, breaking up large workbooks into multiple linked workbooks can really help in some cases.
We've also added some (currently undocumented) advanced module optimization features to Modano which in some cases really help by allowing you to tell Modano to strip out all formulas (or only numerical formulas) from sheets containing components to be duplicated before duplicating/mirroring modules and re-instating them afterwards, which can hugely decrease the duplicate/mirror time, as the primary speed hit is a result of updating formula relationships when rows are inserted/deleted. In some cases we've seen this reduce module duplication/mirror time from 20 minutes to 1 minute.
You can find these module optimization options via the Project Manager (Build tab, Project Manager), via the Edit button within the module properties panel, via the Customizations button at the bottom of the displayed Module Properties dialog, as shown below:
This is complex and fiddly trial-and-error stuff, and as you can see from these somewhat random tools we've developed it's an area we've spent frustrating amounts of time trying to understand and conquer.
Having said that, we almost always win these battles on a case-by-case basis, but each one is different. So if you're really battling get support from our team.
On a positive note, usually the models that have these issues are the ones providing the most benefit to organizations, often replacing much more expensive and inflexible ERP systems. So it's usually worth persevering.
Godspeed. M.