fbpx
Search
Close this search box.

Modular Spreadsheet Development – A Thought Revolution

Financial Modeling - 24 comments

This article is written by Michael Hutchens from Best Practice Modelling.

This article provides a high level overview of Modular Spreadsheet Development principles. In next part the implementation of these concepts will be discussed.

Modular Spreadsheet Development – An awesome concept

I want to share a concept with you so awesome that once you understand it you may never use Excel the same way again.

This concept, called Modular Spreadsheet Development, makes it possible to build spreadsheets exponentially faster while reducing the risk of errors and making spreadsheets much easier to understand.

This concept is not completely new, but I’m writing this article because the spreadsheet modelling world would be a much better place if it was more commonly adopted.

An idea born through necessity

It took me three months to build my first model after I became a professional financial modeller in 2002. It was a model of a large energy company, and it’s safe to say I didn’t get a lot of sleep during that three month period.

The second model I built was of a water company, and I realized that although it was a different business, large parts of the model required the exact same logic as my energy company model. So instead of starting from scratch, I created a copy of my energy model, deleted the parts that didn’t apply to my water company, and instantly had over half my water company model built within a day.

The third model I built was of a toll road company, and once again I realized that the majority of the model required identical logic to parts of my energy and water models. This process was more complicated, because I had to copy, cut and paste pieces out of both of my prior models, but compared to building from scratch it was relatively easily. And I got a lot of sleep.

Never repeat yourself

Within one year of starting my financial modelling business I had already developed an obsession with efficiency. It just seemed insane to spend days and nights repeating tasks that I’d done the last time I’d built a model. Two things had become clear:

  1. Templates don’t work; and
  2. Large parts of many models are identical to parts of other models that you or someone else has built previously.

So my obsession turned to finding a way to re-use and share parts of models, so that I could build more models quickly and easily instead of re-inventing the wheel each time.

Re-using and sharing ‘modules’

The answer fell into my lap one day in 2005 thanks to a hero-worthy depreciation formula written by one of my colleagues. The formula was much shorter than any depreciation calculation I’d ever seen before, and I immediately wanted to use it in every financial model I built from that day on.

I asked my colleague to strip everything out of his model except for the depreciation parts, leaving only two sheets containing depreciation assumptions and outputs respectively. He obliged, and soon-after sent me an email with his fixed assets module attached. In diagrammatic form, it looked something like this:

fixed_assets_module - Modular Spreadsheet Development

This was a revolution for everyone in our firm. We never needed to model depreciation from scratch again. And we knew the calculations were correct so there was minimal risk of errors each time we re-used the module.

We immediately recognized that through compartmentalizing complexity into modules, we could re-use and share content when building models like Legopieces. We started building models in a fraction of our prior model development times.

Modules, modules, modules

It’s safe to say that I’ve become obsessed with building, re-using and sharing modules. Whether it’s a valuation module, a dashboard module or an income statement module, each one feels like an app I’ve developed for an App Store, and I always take great pride in sharing my most impressive modules with anyone who’s interested in using them.

My colleagues have also become obsessed with modules, and between us we’ve developed over 3,000 of them over the past 9 years, allowing for different time frames and levels of sophistication. Here’s an example of just a few of the revenue modules we’ve built and use all the time:

  • Revenue (Amounts)
  • Revenue (Amount & Growth Rates)
  • Revenue (Amounts & Growth Rates)
  • Revenue (Prices × Volumes)
  • Revenue (Real & Nominal Outputs)

It’s a whole new mentality, because each time we build a module we’re aware that it will most likely be re-used indefinitely, so we obsess about making sure it is as perfect as possible.

Infinite potential

One of the awesome things about Modular Spreadsheet Development is that it creates seemingly infinite opportunities to improve the way spreadsheets are built, used, shared and communicated.

The modules within a spreadsheet can be grouped into module areas, such that diagrams can be used to communicate the content of a model, as show below for a basic profitability model:

profitability_model_modules - Modular Spreadsheet Development

Relationships between modules can be shown using module links diagrams, as shown below for a basic income statement module:

Basic Income Statement Module - Modular Spreadsheet Development

Furthermore, every element of the modelling process can be systematized based on a modular approach. Our firm now scopes and builds all models on a module-by-module approach, often working in teams by allocating different modules to different team members.

We also train and assess staff making sure they understand each module so that they can then use different combinations of modules on different projects without ever re-inventing the wheel.

Basic example

The following Excel workbooks provide a basic example of the implementation of Modular Spreadsheet Development, in this case to forecast earnings over a five year period. The first workbook contains revenue, expenses, income statement and dashboard summary modules sourced from the subsequent four workbooks respectively, which each contain a single module:

Each of these modules could be exchanged with other modules to change the model functionality. For example, the Revenue (Amount & Growth Rates) module might be exchanged with a Revenue (Drivers) module to base the revenue forecasts on prices and volumes rather than growth rates. And this could be done without affecting the integrity of the other modules in the workbook.

This process will be discussed in more detail in the second part of this series.

More information

I truly believe that the spreadsheet modelling world should be creating and sharing modules like free apps in an App Store. We’d all save time, build better models, reduce spreadsheet errors, and help maintain and improve Microsoft Excel’s reputation as the world’s leading analytical tool.

I have created an 83 second movie explaining Modular Spreadsheet Development, which you can access via the following link:

http://www.bestpracticemodelling.com/chandoo/msd

You can also download the following PDF document:

Modular Spreadsheet Development – Fundamentals

Added by Chandoo

Modular development is not a new concept. In software industry, modular development is one of the first things you learn. Even those of you who are using Excel alone, have relied on modules all the time, in the disguise of VBA functions, add-ins, classes etc.

But this is the first time, I am seeing modular development applied to spreadsheet models. I am excited to try these concepts when developing dashboards or models.

What about you? Have you heard about MSD? Did you come across any models developed with these concepts? Please share your thoughts and concerns using comments.

More on Modeling Best Practices:

Chandoo

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

24 Responses to “Modular Spreadsheet Development – A Thought Revolution”

  1. Robert Clark says:

    Example links aren't working 🙁

  2. shah says:

    same issue... download link not working.

  3. Rudra says:

    Yeah Chandoo, I have heard about MSD...Mahendra Singh Dhoni...

  4. karthiganesh says:

    Hi Chandoo,

    Now I realize, I started using this modular approach in LOTUS 123, later QuatroPro. Till reading this article, I do not realize that I used very long back.

    In Lotus 123, I prepared a 'Production Plan' for our Yarn factory for one set of raw material combination (which took 7 days). When they changed the raw material combination, I reworked but in less time (which took 5 days). Later I designed worksheet, where entering raw material combination will generate 'Production Plan'.

    Was it Modular Spreadsheet Development ??

  5. Rob says:

    There is a software package in the UK called Brixx that uses this principle. The problem I tend to find with 'MSD' is that business plans start to sprawl as they become more complex. Also, calculating something in isolation then consolidating with other modules always worries me...(I'm a pessimist)

    • Hi Rob. I just checked out Brixx. It's definitely a similar concept, although it's quite a leap from good old Microsoft Excel. I will be explaining the practical implementation of MSD in Excel in my follow-up article, so perhaps I can meter some of that pessimism!

  6. Robjowens says:

    There is a software package in the UK called Brixx that uses this principle. The problem I tend to find with 'MSD' is that business plans start to sprawl as they become more complex. Also, calculating something in isolation then consolidating with other modules always worries me...(I'm a pessimist)

    • Hi Rob

      I too was pessimistic, but the efficiency gains our organisation have achieved since adopting MSD have been quite staggering.

      The key to MSD is very clearly understanding how a model can best be broken up into re-usable and exchangeable pieces (modules) before diving into the build process.

      We found that, within a particular field (which for us is financial modelling and business planning) there is a great deal of module re-use, with obvious examples being assets depreciation, working capital analysis, financial statements and DCF valuations.

      In fact, we've uploaded over 1,500 of these modules to our website for free download (see http://www.bestpracticemodelling.com/downloads/modules) if you want to check them out.

      I will discuss more details about the implementation of MSD in my follow-up article, which Chandoo should be posting over the next few days.

  7. Ryan Shoemaker says:

    If you are not familiar with BPM Toolbox, this is the Excel add-in being used to build these example models above.

    I have been a user for a couple years now. This product have saved countless hours in developing financial valuation, capital project and other models.

    Highly recommend.

    • Thanks Ryan. The Standards and bpmToolbox played a massive role in creating the consistency that made MSD so obvious.

      I think one of the reasons why MSD is not yet widely used is because standardization isn't respected enough for MSD to become more prominent.

      My primary objectives are efficiency improvements and risk mitigation, and all of these systems fundamentally assist in achieving these objectives.

      It's always great when others 'get it', and I think it's only a matter of time before all of this becomes an expectation in organizations wanting to better use spreadsheets.

  8. Shireesha says:

    Fantastic approach, to tidy up messy messy world of spreadsheet models. Very well documented and explained.

    Awesome work! Thanks.

  9. duncan says:

    xlsb links still dead

  10. […] recently posted about Modular Spreadsheet Development (MDS). Like it was for him, this is a new concept for me but it made […]

  11. Ashraf A. says:

    That awful moment when an undetected error somewhere in your model generates misleading output for your client?

    ...that awful moment when it takes hours to trace back the source of that error?

    ...that awful moment when you have missed the deadline and cannot still see the end in sight?

    These are 3 classic challenges that people in modelling face but MSD is definately a concept and mindset that can signifiantly cure the above issues.

    Thanks for sharing @Michael.

  12. dany says:

    Good concept.
    Instead of one big template...there is a split of it to modules or pillars..which are eventually templates by themselfs...and you choose which to use...
    Might work....for something which is regural and rutine...but for new projects probablly building new template might be a better solution.
    Another point..these days..business partners dont have time and want to see excells asap...directly and even to change some numbers..not sure if too many sheets will help with this challenge..

  13. Hamish says:

    How do you make the hyperlink buttons?

  14. Hi Hamish.

    The 'hyperlink buttons' are actually shapes (inserted via the Insert tab, Shapes menu) with their Fill property set to 'No fill' and a hyperlink added to them by right-clicking on them and selecting the Hyperlink menu item from the displayed shortcut menu.

    Let me know if this doesn't make sense and I will provide more detailed instructions.

  15. […] Instead of starting from scratch every time you build a new workbook, you can save time with modular spreadsheet development. […]

  16. Fernando says:

    Hi Michael,

    I understand how valuable an MSD approach can be when designing financial and risk models. Nonetheless, I believe a big part of Excel users do a lot of ad hoc reports, work with very different data basis and do adhoc reports that then become standard reports. The way I've found to become more efficient has been with macros and pivot tables.

    I would like to get your thoughts on MSD beyond designing financial and risk models.

    Thanks Michael!

    • Hi Fernando

      The applicability of MSD, and the efficiency gains flowing from its implementation, are often directly related to the level of standardization present within the underlying subject matter.

      Financial modeling is obviously ripe for MSD because it is heavily governed by international accounting standards which create a foundation for the consistent creation of modules - e.g. financial statements, etc.

      For more ad hoc reports, I still see great benefits in spreadsheet standardization, but the applicability of MSD really depends on (1) whether it is likely that you or someone else has undertaken a similar analysis before and (2) whether the analysis can be divided up into smaller pieces (i.e. modules) rather than simply using templates.

      All things aside, MSD is certainly not appropriate for all spreadsheet modelling projects, but it certainly can be used for a lot of them. Particularly if you are a regular user of Excel undertaking similar types of analysis in different spreadsheets.

      I provide more practical depth on MSD in my follow-up article so please stay tuned.

Leave a Reply


« »