Recently a client of ours came to us with an interesting story which highlighted that although spreadsheets can be powerful tools they can be hard to use by the uninitiated.
“This excellent outcome highlights just how versatile and valuable spreadsheets can be“
Our client, a construction engineer had decided to build a house made of concrete building blocks. After drawing up the plans he went to a supplier for the blocks and asked for a quote. The supplier’s sales representative came back saying that he has a backlog of at least three weeks on his quotes. Our client was stunned and asked why the holdup. Well, the sales representative replied it can take me several hours to draw your plans on graph paper so that I can estimate the number of blocks required. Our client went away and immediately thought he could help using his trusty spreadsheet skills. A few hours later he had developed a spreadsheet using a few simple formulas to calculate the number of blocks required and was able to get his quote the next day. This excellent outcome highlights just how versatile and valuable spreadsheets can be.
Our client then shared his spreadsheet with a project manager who was familiar with spreadsheets. The project manager thought it was great for some of his projects and could he use it. Our client was pleased and thought he would also help the sales representative by giving him the spreadsheet so he no longer had to use graph paper and could quote more rapidly. He showed the sales representative how to use the spreadsheet by doing the following:
- Copy a sample wall into a new section of the sheet or into a new sheet
- Resize the wall to the specified dimensions
- Insert any opening such as windows and doors
- Apply the appropriate blocks
- Check the report
- Repeat for all the walls
“… although the estimation spreadsheet was a great tool for an experienced spreadsheet practitioner it was daunting for a novice.”
The Sales representative was not a regular spreadsheet user but under the direction of the structural engineer was able to use the spreadsheet to create an estimate. Some weeks later our client asked him how he was going and if the spreadsheet had solved his quoting backlog issue. The sales representative explained that the next time he received a quote he tried to use the spreadsheet but the walls were quite a different shape and when he went to resize the walls he forgot to change a range formula and got the wrong result. He also found it hard to remove the existing windows in the example. He explained it was all too hard and went back to using graph paper after that.
“… even though the process seemed logical, multiple steps were involved and required knowledge on how spreadsheets worked.”
The structural engineer realised that although the estimation spreadsheet was a great tool for an experienced spreadsheet practitioner it was daunting for a novice. And when he thought about it even though the process seemed logical, multiple steps were involved and required knowledge on how spreadsheets worked. Furthermore, each step could result in mistakes being made. He could spend time improving the spreadsheet to help make it more robust to errors but he realised that it would be very difficult if not impossible to eliminate even the common causes of errors. Therefore, he came to the conclusion that in order to move the sales representative off graph paper he would need an easier to use and less error prone solution. He mapped the six steps onto a wizard with simple entry forms and came to us to see if we could build a solution using the form and workflow builder in the Xelleron framework. The process of how this was done and the outcomes will be left to a follow up article.
