Should this be built in Excel? Have you built a model with all these requirements?

[JeffreyWeir]

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.

[Ken Puls]

they are actually asking for a well developed application that happens to be presented in a spreadsheet

I'd agree that you've pretty much got that demand here. I can't give you anything that meets all these requirements, but I do have a capital budgeting workbook that can compare four different scenarios if you're interested. It does take into account Canadian CCA (amortization) tax shield effects, but if you're just looking for ideas...

[JeffreyWeir]

Absolutely looking for ideas, Ken, and would love to see anything you've put together to this end. My first thought on this is that while I can build this, what can I learn from other people who have done something similar. When I say this is a financial institution, it's actually a government institution as well, and like other government institutions they have to submit modeling to the Ministry of Finance for new initiatives. So I'm also going to talk to people at the finance ministry to see what kinds of models are currently being submitted there, and what kind of models they use themselves. But I'll probably find that whatever I put together will be as good as anything and possibly better... I work in a small country of 6 million, and I'm yet to find anyone else who has Professional Excel Development on their bookshelf :-)

[Ken Puls]

LOL!

Here's a copy of the capital budgeting model. I actually used this as one of the examples for a recent course on designing dynamic and stable financial models. Hopefully it's self explanatory... if there is any sheet protection active, there won't be a password on it. (I only use that to prevent accidental damage.)

[JeffreyWeir]

Thanks Ken. This helps underscore just how complicated what I'm going to have to build is, because my model will also have to derive your two inputs "Net initial investment" and "Net contribution to pre-tax operating income (end of yr)" from the ground up, and as such the template will have present just about any category of thing that could be part of those investments or costs to the end user, so that they don't miss anything and so that there is a cross-departmental method of deriving these.

For instance, they will have to indicate what kinds of human resource (i.e. project managager, UAT Tester, Mainframe Developer, ...) they will be using across time, so i can match these against standard rates for different types of staff, and work out the cashflow implications. And I must do similar for almost every conceivable type of cost and benefit.

So this thing will get very big very fast, and I'm going to have to have lots of radio buttons or hierarchical picklists that conditionally hide and unhide sections depending on user choices.

[Ken Puls]

Sounds like fun! I have a land development forecasting model that is built like that. We start from the raw land and have inputs for everything, flexed by sector, sector difficulty and more. The project took me over a year to spec and build. End result is a pretty stable model that is quite dynamic. But complicated? Wowsers... it's the one of the most complicated things I've ever built.

Some advice: We spent a LOT of time doing specs for it so that I wouldn't haven't to go back and retrofit pieces. This was a big thing, and we got some of our key players in to be part of it. Reason is that we knew the scope would keep changing if we didn't. While there were still scope changes (some that took a week to retrofit properly), they were kept to a minimum. I was also able to plan some obvious insertion points where we might insert more detailed calculations for certain parts.. just in case. It's worked fairly well.

[JeffreyWeir]

Crikey. Question: How many input cells does it have? How many worksheets is it spread over? Any chance of a sanitized screenshot or three so that I can see how you laid that baby out?