Hi all. I’ve been asked to develop a project costing model for a reasonably sized financial institution that provides services to 6 million customers, as a staff member and not as a contract developer. I've just about got the skills required to meet the user requirements, and those skills I don’t have I will have by the end of the build. But looking at the quite demanding requirements got me thinking:

  • Is Excel is the best tool for this? Especially given issues raised in this blog post over at DDOE
  • If Excel is the best tool, can I capitalise off of something some else has already done? Does someone out there already has something up and running that meets these requirements that they might be interested in turning into a template and reselling?

I’d love to hear your thoughts on these points.

I worry that non-developers really understand that when the are asking for a spreadsheet that does all this stuff really well, they are actually asking for a well developed application that happens to be presented in a spreadsheet, and not a spreadsheet model that can be banged out in a fortnight.

I’ve tried to illustrate this point by showing them the part of the book "Professional Excel Development" that divides excel applications (and the developors that create them) into 5 different categories, and highlighting that this application is in the 4th category: an application that is robust, fast, easy to use, and maintainable. (To make the 5th category, the application must be capable of self replication and time travel).

I’ve also indicated to them that if they want a totally satisfactory result, it will take a lot longer to build this new model than the existing model. That existing model is about as flexible as rigor mortis. It’s about as user friendly as death, but somewhat less robust. So there’s not a lot I can reuse from it. On the upside, whatever I do will be an improvement.

I’ve also highlighted the development process will be highly iterative, given that I don’t know their business or their users, and given the inherent tensions and tradeoffs between flexibility, usability, and robustness.

The requirements document is asking for a great leap in all three of these flexibility, usability, and robustness aspects compared to the existing model. Yet where Excel is concerned I often find that the more you emphasise one aspect, the more you find yourself needing to do stuff to hold the other two aspects constant, because in Excel, for every three problems you solve, the last two of them were probably design challenges that cropped up because your approach to fixing the first one.

I’ve also told them that if they take a shortcut on this stuff, it will come back and bite them. A quick job will result in something that people won't use, or won't be able to maintain. Or worse, a quick job will work fine until I've been hit by a bus, and then it will break, at which point they will find out that the reason they got something so quickly is because I didn't have the chance to really think about (and refine) the structure of the code or to fully document changes made during the refinement process. (And then the poor sod who has to try to make sense of it all will finally flip out, dig up my corpse, and shake it violently from side to side whilst loudly screaming HOW DOES IT WORK?)

Anywayyyyyyy….I’d love to hear your thoughts, comments, and insight on this.

Here’s a general summary of the user requirements for what they are worth, in case someone has a app they might be able to put a price on. The model should:

1. Bring together summarised cost benefit information on up to 4 different options for easy comparison. This cost benefit information to include
  • Total one off costs – operating and capital
  • Total capital costs over the life of the asset
  • Total operating costs over the life of the asset
  • Total financial benefits over the life of the asset and per annum long term financial benefits
  • Total economic benefits over the life of the asset and per annum long term financial benefits
  • Financial NPV over the life of the asset
  • Economic NPV over the life of the asset

2. Allow automated calculation of ranges and contingencies for costs, benefits and NPVs. All projects should be able to show the breakdown of the costs, benefits and NPV calculation annually over at least 10 years (with the ability to extend up to 20 years)

3. Totals should be expressed in ranges. The magnitude of the range would be estimated by the project based on perception of uncertainty or from sensitivity analysis and would need to be able to be changed for different types of costs and benefits. Ranges on costs would usually be entered by + X% and –x% around the expected costs or the expected costs with or without contingency. Ranges on benefits would usually be calculated by applying a range on the % change to the baseline costs.

4. Allow for all of the following aspects of benefits modelling to be incorporated:

  • Cost per item for the metric affected
  • Current volume of the metric affected
  • Total current costs
  • Estimated growth in the metric over time if we did nothing and therefore costs over time
  • Estimated long term % change in the metric as a result of the project
  • Estimated uptake or capability increase over time affecting proportion of long term maximum achieved each year until maximum achieved.
  • Calculation of estimated total future state costs per annum over time and difference between current and future state i.e benefit per annum.

5. Allow for flexibility for projects to present additional information e.g. individualised tables or charts to present different aspects of the analysis

6. Allow flexibility for benefit totals to input into summary worksheets from a different source if benefit modelling doesn’t fit the standard methodology

7. Allow options or flexibility for methods to enter costs in different ways depending on complexity and skills and experience of those doing the costings.

8. Enable users to enter resourcing information in multiple ways, such as–

  • using a standardised format which is comprehensive to help inexperienced users with simple projects to ensure they have covered everything or
  • from a standardised monthly resourcing sheet for larger, longer projects where the FTE by month for all project personnel can be entered. This sheet should include the ability to input contractor/internal resource split estimates for different resource types or
  • by linking to another spreadsheet of different resourcing information

9. Allow default input rate assumptions to be overridden by expert users if necessary

10. Provide the ability to re-combine parts of costings and associated benefits to remodel different scope and phasing options.

11. Provide the ability to see confidence levels of different inputs. ie whether an assumption is a complete guess, or if we have good supporting data

12. Support versioning control and method of documenting differences between versions.

13. Ability to incorporate previous years funding and expenditure

14. Allowmethod for combining outputs from several different models to give a programme/portfolio view.