Modular Spreadsheet Development – A Thought Revolution

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:

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:

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

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:

• Modular Spreadsheet Development – Basic Example.xlsb
• Revenue Module (Amount & Growth Rates).xlsb
• Expenses Module (Amount & Growth Rates).xlsb
• Income Statement Module.xlsb
• Dashboard Summary Module.xlsb

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:

• Best Practice Modeling – 5 changes to implement today
• Project Finance Modeling using Excel – Part 1 & Part 2
• Financial Modeling using Excel – Part 1, Part 2, Part 3, Part 4, Part 5 & Part 6
• Spreadsheet Risk Management Part 1, Part 2, Part 3 & Part 4