Chandoo recently posted about Modular Spreadsheet Development (MDS). Like it was for him, this is a new concept for me but it made sense.
The idea of Modular Spreadsheet Development is efficiency. If you’re developing certain spreadsheet elements over and over again, time and expense could be cut down by building modules instead of starting from scratch over and over again.
Currently, I’m working on 3 projects that involve recycling chunks of previous projects. This is beneficial for the client in money, time and experience.
This next part is something that friends have warned against: “You’re giving too much away!” Well … if I end up as a hobo for talking too much, it’ll be my own fault.
FORMS, DASHBOARDS & GUTS
One type of spreadsheet that I’ve built over and over again has included:
- An input form
- A data storage sheet
- A dashboard
There have been variations. Some require 2 input forms, some don’t require a dashboard. Some forms have no required fields. But these are all minor variations on a larger theme:
let me load data in >> store it somewhere >> gimme summaries
More formal representation
It took a while to see the common pieces but after doing this for so many clients, the pieces were clear. The image below shows just 5 projects where Input, Storage & Output were needed. The top cells show the project/purpose. The bottom cells list examples of the types of measurements.
Examples:
Insurance Policies & Commissions
EFFICIENCY ISN’T THE ONLY VICTORY HERE
Let’s look at 3 points:
FIRST You get better with each module you build
Chandoo makes the point:
[Modular Spreadsheet Development] is 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.
I agree. Each time I make one of these Form-Storage-Output models I see new ways to make it better. With the first few, I wasn’t aware of the user’s need to add new types of data so, the models weren’t easy to expand. Also, the data input controls were weak on earlier models. People could easily type over formulas or input nonsensical data, and corrupt all of the results.
After fixing a lot of these–at no expense to the client–new realities came into focus.
- Skipping the input controls in order to save money was not an option.
- Sometimes it’s possible to add new data directly in the storage area. Sometimes a separate form is a must. This depends on the quantity of data and the user’s comfort level with Excel.
- It’s gotta be expandable.
SECOND You learn to be strategic in working with data
MSD forces a lot of forethought and strategy which is excellent for learning how to work with Excel and data. Rather than jumping in, throwing raw data into a pivot table, and generating on-the-fly summaries, we have to slow down and think about what needs to be in each module.
We also learn the importance of a flat file. MDS doesn’t work with data that’s spread across multiple tabs; e.g., monthly tabs, region tabs, department tabs, etc. No! Keep the data in one “module” and use Excel’s power to dig out what’s needed when it’s needed.
THIRD Skip the tedious process of design and layout
- Colors
- Horizontal vs. Vertical Layout
- Should the Date field be above the name or on the same row?
To hell with all of those design details! Anyone who deals with design can testify that design eats up a lot of time and money. It doesn’t add to the technical functionality, but it does impact the usability of the final product. So, once an effective module has been created, the choice in a new project is:
Can you live with these design decisions or do we spend money on redesign? Either way, we aren’t starting completely from scratch.
THE REAL MAGIC IS IN THE GUTS!
In the Inventory System that I built, it’s set up with the invisible layers of VBA, formulas and hidden helper columns that do the real work of
- Managing required fields
- Moving the data from the Form Module to the Storage Module
- Protecting cells, sheets and formulas
- Uniformly formatting dates
- Warning users of incomplete or errant data
That’s what I call “the guts.” They aren’t seen but they keep the whole thing alive and integrated. Because of the guts, this inventory system has been easily modified for 2 other projects, managing
- expenses and revenue
- investments and cash-outs
THANKS CHANDOO!
We’ve already been doing this kind of development … somewhat. By putting the name to it–Modular Spreadsheet Development–we do get a different way of thinking. Rather than ad hoc recycling, we can look for ways to carve out clear modules that can be popped into place.
Check out Chandoo’s blogpost
lego photo credit: photography.andreas via photopin cc
