The first step in starting a new business venture is being able to explain it. The next is to quantify your idea with the help of a financial model. If you model it, you can explain it. Although there are many types of financial models available, the most widely used are the ones for new business ventures. A venture could relate to a new business undertaking or to consider the viability of investment an ongoing business. Either way, there are features common to both.

There are some fundamental "good habits" and things you can keep in mind when building models. They will not only improve the quality of your model but also increase the likely hood that the end result will be useful.

Focus on your CRaFT

CRaFT is an acronym for the overall design considerations when creating a model. Adherence to your CRaFT will give you a higher quality model making adoption more likely and ending up with a polished final product.

Consistency

Have a standard design methodology throughout. For instance, each of the tabs in the model should have the same formatting. Use a standard set of colours and fonts. For example, all highlighted sections in the worksheets like totals should be bold with a standard border. As you get better at implementing great visual design, it can be implemented as a standard template in future models.

Robustness

A high quality model will have all high level numbers validated. It will also be easy for the user to sanity check the figures against their understanding of the relationships between elements. Any #REF! or #VALUE! errors will be bought to the users attention painlessly. More on this in the testing section.

Flexibility

You can expect any reasonably complex model to have several iterations. Even the end product is subject to change if there is a shift in focus or a new requirement. A flexible model can take these changes in its stride without having to do any major rewiring. Small good habits can go a long way like not hard coding values into formulas when they are derived from other areas of the model. Modularizing areas that can be considered independent.

Transparency

Transparency is how easily the user of the model can work out how the numbers were derived. A consistent logical flow when creating the model will aid readability with the user being able to quickly get details about how a number was calculated.

Success

What success looks like

Although you won't normally undertake a financial enterprise unless you expect it to be profitable in the future, in some cases you need to focus on other goals in addition to the obvious.
For example:

  1. You could be implementing a new software product in order to remain competitive or try to become the industry leader.
  2. You are creating a model in a highly variable environment and prioritize prediction over profit.
  3. Balancing out an investment portfolio by according to cashflow

A model's critical success factors need to be listed out in the beginning and then a framework built around it.

Decide on the inputs and outputs early in the process

There could be multiple inputs to the model from different areas of the enterprise. For example, the sales team will give details about sales volume while production will have to work out how it will be produced.
The earlier you can determine the level of detail and granularity the model goes into the better as this will set the scene about what data is to be collected. As a rule of thumb, it is best to get data at the lowest level of detail possible. It is easier to summarise than to expand.

Use an agile software development methodology

Creating models are like creating software products. Getting requirements, building based on those requirements, building prototypes, testing and handover have the features of a software implementation. Add to this, agile features like customer satisfaction and an iterative approach to development, gives you a great methodology within which to build high quality models.

Focus on the three statements

All investment models will eventually boil down to 3 documents, the profit and loss account, the cash flow statement and the balance sheet. Regardless of how complex or detailed a model is, the final decision maker of the model will want to look at these documents. All activities should eventually flow into these three documents and the end product will not be complete without them.

Success

A proper testing strategy

There are 2 types of errors on a spreadsheet.

  • Formula errors are the most obvious and are caused by:

    • a syntax error
    • the formula relating to a cell which already has an error in it
    • a formula pointing to a cell that no longer exists
  • Sanity errors:

    • not including the whole range in a formula
    • using the wrong cells for formula construction

Of the errors, the most dangerous are the sanity errors because there is nothing telling you that there is an error unless the number doesn't look quite right to you.

Having a proper testing framework means if you make changes to the model, there will be checks that these changes haven't broken anything. Testing can include things from having check sums per page to having an entire page summarizing tests carried out in each page.

Organize for cause and effect

In a well designed model will be structured in a way the user will be able to see how changes in operating parameters in one area affect the model as a whole. This makes it easy to run different scenarios and compare them side by side.

In the end, we can go back to the old adage that all models are wrong but some are useful. Using these tips might increase the likelihood of the model you create being adopted and used.