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.
The team at Nash Advisory are experts in offering businesses accessible advice for both acquisitions and sales. To find out more about how financial models can help you achieve the best sales outcome for your business, contact us today.
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.
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.
We recommend that you separate out the spreadsheets into:
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.
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.
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.
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.
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.
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.
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.
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.
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.
VLOOKUP is limited in its functionality and use. There are several problems with it:
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.
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.
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:
Following these steps will help you accurately prepare your business for sale. By avoiding these mistakes, you should be able to create a financial model which is easy to follow, simple to understand and delivers you with a result that can assist with your business needs.
Talk to Nash Advisory to find out more about how financial models can drive outstanding sales outcomes.