When considering buying or selling a business, a financial model forms the crux of the analysis. It's a recipe that guides your success. If you identify the right ingredients and you put them together in the right order, you will end up with a result worth your time and effort.
At Nash Advisory, we review hundreds of models every year and we see the same mistakes arise. These tips will hopefully save you time and build clear, robust and well-structured models, and help you avoid some of the reasons why businesses fail to sell.
An optimised business sells for optimum prices. For advice and solutions tailored to your needs, book your consultation with our specialists today.
1. Keep it simple
Avoid large, complex formulas in financial models. It is always better to break large models into several shorter formulas. Splitting the formulas makes it easier to follow the audit trail. The logic of the model’s calculations will also be apparent to an external parties, like banks or advisors.
2. Use conditional flags
Use conditional flags, which can be as simple as 1/0 triggers. These will ensure that the balance sheet is balanced. Conditional flags are also good when setting thresholds.
For example, a machine that has been modelled into operations may have a capacity of 100,000 widgets. Once this threshold has been hit, then a conditional threshold can trigger the purchase of an additional machine. All this will flow through the financial model.
3. Consistency is key
We recommend that you separate out the spreadsheets into:
- (i) inputs
- (ii) model calculations
- (iii) outputs
We also recommend colour code the inputs, so that the user can immediately identify them. Also consider applying restraints that prevent people from amending the formula or entering hard codes into formula sheets. This stops users from overriding the outputs to get the desired result.
4. No need to hide
Do not hide any sheets or rows that are used in the calculations. Instead of hiding rows or columns, group them so that the user knows that they are not visible.
5. Add as many checks as possible
Use checks throughout a document. This ensures that calculations can be verified under different methods. Error checks should be standard where balances are required. For example, cash in the balance sheet should match the cash level in the cashflow statement.
6. Put on auto save
We recommend putting on the auto save function. We also recommend setting up a simple naming convention with the discipline of recording and saving changes after each major amendment. This reduces the risk of work being lost, and provides an audit trail. The author of each version should also be known. We also recommend that all major changes to the model be tracked and noted at the front of the spreadsheet.
7. Wrong sums
Only use the sum function once when summing totals. The issue with using the sum function is that cells not intending to be summed are often included.
- One solution is to use the SUBTOTAL function instead of the SUM function.
- The SUBTOTAL function will not add up the results of other SUBTOTAL functions, so your single line items will not be added multiple times.
8. Hard values
Never put hard codes in any formulas. We recommend using drop down lists which apply the assumptions into the relevant input sheet. This stops people putting in the wrong data into input cells.
9. Circular references
Circular references can cause a model to break. They also increase the file size dramatically. If you need to use a circular reference because the calculation is actually circular, you should use VBA macros to solve the issue.
10. Nested Ifs
We recommend avoiding the use of nested ifs in your formulas. Sometimes they are required due to the sequence of formulas, although they reduce transparency. Nested ifs are likely to contain errors due to the multiple combinations of steps within the logical order and sequence.
11. NPV function
The NPV function is usually not used correctly. When using the formula, the outflow at time zero needs to be subtracted from the future cash flows (those cash flows after T=0). One solution is to use the XNPV (which is similar to the XIRR), which incorporates the cash at T=0.
12. Avoid using VLOOKUP
VLOOKUP is limited in its functionality and use. There are several problems with it:
- The formula cannot lookup and return a value which is to the left of the lookup value.
- It only works with data which is arranged vertically, as opposed to HLOOKUP, which works horizontally.
- The formula does not work if you delete or add a new column in your data
- It requires more processing power to evaluate the entire table array you have selected.
We recommend the use of INDEX-MATCH function. This can address all these issues as it leads to fewer errors and gives you a lot more flexibility.
13. Model dates as numbers
The Excel function for dates is based on a simple number format. This allows someone to easily model your time axis more efficiently as you can easily reference dates with logical functions.
These functions include the EOMONTH function (returns the last day of the month) and the EDATE (returns the first day of the month). If a project is delayed by a period, then all you need to do is change the start date and the rest of the date formulas should amend automatically.
14. Link to the original source and avoid daisy chains
A daisy chain is a series of links where the last link is not a direct link to the original input or calculation. It means that data is drawn from multiples sources, even though the same data is available from one input sheet. The major issue with daisy chains is that it reduces the navigational effectiveness by forcing the user to follow a series of steps in order to locate the original input or calculation. It also increases the file size.
For example, take a hypothetical tourism business:
- The number of passenger for a tourism business will determine the revenue and costs.
- In your business model, always use the original source of the passenger numbers.
- Do not link passenger numbers from the revenue sheet, even if those figures are listed in that sheet.
