Performance implications of adding a large number of categories

Hi Nick,

We've amended the topic of this thread, as this is actually not a Modano issue but an Excel performance issue.

Adding rows is an extremely memory-intensive task in Excel, and one that takes more time than anything else when inserting modules and using automation like categories and time series automation. This is because every time a row is added Microsoft Excel has to look at the surrounding workbook (e.g. formulas and other objects referencing data around the inserted rows) and re-establish relationships following the row insertion. And this can take a lot of time in some particular cases.

For this reason our engineers have done thousands of hours work analyzing the performance impacts of adding rows (e.g. when adding categories). And as a general finding we found that row insertion starts materially lagging once worksheets contain more than 1,000 rows. The lag is significant and sometimes quite sudden - e.g. we found that inserting a row into a worksheet containing 800 rows of data might take 0.1 seconds, whereas inserting a row into a worksheet containing 1,200 rows might take 0.8 seconds. So when you're adding a large number of rows - e.g. 50 categories - the process can take a long time. And the performance impact is exponential as worksheets contain more rows, not linear.

Something to note is that, while not particularly intuitive, it's faster to add 1,000 categories at once than 20 lots of 50 categories. So while it may appear like Excel is hanging when adding 1,000 categories at once, it's a lot quicker than 20 separate processes of adding 50 categories, even though being involved in running a process 20 times makes you feel like more is happening.

Something else to consider is the flow-on effects of adding categories. Even in a simple 3-way model, adding a category of revenue with prices and volumes will add 5-10 rows per category, taking into account multiple category blocks in the revenue module itself and the addition of corresponding flow-on categories into dependent modules such as debtors, GST/Sales Tax/VAT, the income statement and the cash flow statement. So adding 1,000 categories often results in over 5,000 rows being inserted throughout you model, in different places.

So, as a general rule for avoiding performance issues when adding categories:

1. Limit how many categories you need to add by controlling model scope.
2. Consider using a freeform workbook instead of a modular workbook if you're building simple database revenue and expense projections with thousands of rows of data.
3. Try to limit the number of rows on any worksheet to 1,000 rows, even if this means splitting categories across multiple modules with their components located on separate sheets.
4. Consider flow-on effects of the categories you're adding.
5. If all else fails, try get a faster computer.
6. Go back to step 1 and repeat!

As a final point I will note one of Modano's core objectives: You should never be worse off from using Modano, so we always look at whether using Modano has slowed the process compared to doing it manually. And there are few cases in which Modano actually costs you time, and we're always working hard to mitigate the ones that do...

If you're happy to upload the file (resetting all assumptions if they're confidential) to this thread, or email it to support@modano.com, we will run some tests in it for you.

M.