Financial Modelling in Microsoft Excel/Specifying a model

Find out first –

• what exactly it is for, & what it needs to do
• what inputs it needs and where the data & assumptions will come from
• what business logic is required
• what level of detail it needs to provide
• what flexibility is required – i.e. what needs to be able to change
• who will use it & how, & what are their skills
• how often it will be used, & for how long
• how soon it is needed

For anything except a small model with a clearcut specification and design, it is a good idea - and essential in many cases - to document the specification clearly,

It is extremely important to fully understand and document the business logic, i.e. all the business rules that need to go into the model. For example, most business models involve tax, so you’d expect some rules on how tax affects the model. As you will see, errors in logic – and especially omissions – are among the hardest to find – and they can be extremely dangerous.

You should always ensure you have copies of all relevant correspondence or documents, and anything that will help you understand the business. For example, if you are modelling a workers’ compensation portfolio, you would need documentation on the benefits that are paid, previous actuaries’ reports, publications that might indicate recent trends or issues, etc.

Where other people will be using the model, ensure you know their skill level and experience, and find out how they will use the model (e.g. daily or weekly; briefly or for hours on end; in depth, using all the features, or just using basic features; etc.). It doesn’t matter how clever your model is, if no-one can use it!

Writing a specification

A specification is a written description of your model. It should cover

• what your model will and won’t do
• what data and other information it needs
• what results it will produce
• documentation and support (if applicable)
• maintenance (if applicable)
• timing
• cost