Break up large model into multiple linked workbooks

5 posts / 0 new
Last post
Tarjei Kirkesaether A+ 58
TK
Break up large model into multiple linked workbooks

Does anyone have any experience in breaking up large workbooks into smaller, linked workbooks (i.e. creating a Project)?

I have a very large model (45 MB and growing, using 1.5 GB memory on 64 bit Office) and I estimate that approx 50% of the size is due to very detailed debt and PPE calculations. These calculations are housed in multiple mirrored modules (I.e, all debt modules are mirrored and all PPE modules are mirrored). I would like to break these two sections out of the model but I am unsure what the best way of doing this.

If I were to do this, my plan would be to add two linked workbooks, one named Debt and one named PPE, insert one module from the original model into each linked models and then mirror the inserted modules as required. I would then create export modules from each of the new workbooks and export the data required (mainly summaries feeding financial statements) from the new workbooks into the original model, then replace any internal links in the original model with import module links. Next step would be to delete the existing modules in the original model.

This seems like a very big job and is probably not possible in the time I currently have available, hence I'm looking for some understand if this is the correct way or if there is a smarter way of doing it.

I have reviewed the guidelines and looked through the forum but can't seem to find anything outlining this, apologies if I missed something. If this is too big a request for the forum I will consider purchasing Support Credits to get a better understanding.

T

Michael Hutchens A+ 189

Hi Tarjei,

Given that we consider you to be a power user of Modano, I don't think an issue of this complexity is likely to be resolved via this forum, not least because I'm assuming that you're unable to share the workbook for confidentiality reasons, meaning anyone wanting to help you will struggle based only on what you've said in your post.

We help a lot of our clients manage large workbook challenges, and dis-aggregating a bloated workbook into smaller more manageable workbooks is commonly part of this. As a general approach, we interrogate the workbook to determine the drivers of both file size and performance issues - which are often not the same as Excel can open extremely large workbooks and perform very well as long as the workbook performance hasn't been heavily impacted by things like thousands of SUMIF, INDEX, MATCH and SUMPRODUCT (i.e. lookup) functions.

In your case, we would look at how much of the size and performance issues are being caused by your Debt and PPE modules (etc.), and as a first step see if there is some way to optimise the size and performance of these modules. Sometimes this can be done easily, other times it's not possible.

Assuming that your workbook is an XLSB, 45MB is enormous if most of the content is calculations. I'm guessing it's a monthly model, with quite a few years, which would help explain this size...

Assuming there's no way to streamline your existing modules, the approach you've suggested is broadly what we undertake, but for the fact that we sometimes use independent modular workbooks instead of a multiple-linked workbook project if we're able to do so - i.e. if there's no category linkages between the models which need to be maintained. We then write some fairly basic VBA using the Modano API to ensure the alignment of their period titles during time series rolling and extension, etc.

As per my initial comments, I'm really just guessing and generalising here, as we do not believe there is one 'best practice' way to do this stuff, as it all depends on what's actually causing your issues, which differs for each large model we look at.

Our services guys would be more than happy to review your model and provide you with some guidance via our support system.

M.

Tarjei Kirkesaether A+ 58
TK

Hi Michael,

Thank you for your reply, I suspected this could not be solved by a forum post and I will get in touch with your service team if I decide to go down this path. For anyone who haven't used the Modano support I can highly recommend it. Regardless of your level of Modano knowledge I have learned so much for using the support credits our company bought back in 2016. 

The model is very large indeed (12 years, monthly, it's 58 MB if not saved at XLSB) but most of my time lately has been spent trying to streamline it. It has however become the be all end all answer machine to all forecasting questions at our company so I seem to to be making it bigger, not smaller!

One point I'm curious about is what you mentioned about using independent workbooks rather than linked workbooks if you're able to do so. When you do this can you still use the import/export modules to link between the models or do you link/paste values into assumptions cells? Are the only disadvantages of this approach that time series and category blocks don't automatically update?

Thanks again

T

Michael Hutchens A+ 189

Hi Tarjei,

Sorry for the belated reply. When I mentioned using independent workbooks rather than linked workbooks if you're able to do so, we don't use modular import/export sheets when doing this and instead go back to old-school Excel rules in which import/export sheets must be manually aligned and ideally not changed much at all. So it can be painful with categories, so we tend to only use this approach when the data moving between workbooks is total-only data, and we usually hide the export sheets so they aren't inadvertently edited breaking the their alignment with their corresponding import sheets.

The automatic alignment of categories across linked workbooks is extremely powerful but we do try to limit (or even avoid) inter-workbook category-based links whenever possible when working with a large number of related workbooks. I know this isn't always practical, so we scope the most suitable solution based on the practicalities of each project.

I've also just added a complex post on how we automatically align time series assumptions using VBA and the Modano API in these related, but unlinked, modular workbooks:

www.modano.com/forum/how_automatically_align_time_series_independent_modular_workbooks

We've found this functionality really handy when working with clients with 10+ related modular workbooks that aren't in the same project.

M.

Tarjei Kirkesaether A+ 58
TK

Thanks Michael - I'll definitely take a look at that guide!