Tips for Structuring your Financial Model
Kevin Gallen, who has over 20 years’ experience in Public company / Senior Corporate Finance Advisory, delivered some of his insights on financial modelling to the participants of the Irish Stock Exchange’s IPOready programme.
A Company’s financial model is a key requirement of an IPO process both in terms of formulating the investment case, determining valuation and forming the foundation for the working capital statement. It can also be used by debt providers in their lending assessment and be a key input in the setting of employee incentive schemes both pre and post IPO. The financial model can also become a useful and integral part of running the business, in terms of assessing new markets or modelling the impact of other strategic initiatives including acquisitions and /or disposals.
The financial model therefore, has to be able to cater for the requirements of multiple users with different objectives, and needs to be readily adaptable to changing circumstances, particularly in the current uncertain / volatile economic environment.
Here Kevin outlines some of the key attributes that you should consider in developing a financial model for your company:
Flexible and Dynamic
- The Model should be fully integrated with all key financial statements: profit and loss, cash flow and balance sheet fully interlinked.
- The Model should be flexible and dynamic to be able to incorporate changes in circumstances / key assumptions quickly and efficiently.
- The Model should potentially cater for multiple different users and allow each of those users to run scenarios, make modifications and to see the cumulative impact across the forecast period on profits, balance sheet and of course, most importantly of all, CASH FLOWS.
Relevant / bespoke to the business
- The Model should reflect the key business assumptions and avoid becoming cluttered with unnecessary detail.
- All assumptions / scenarios should be included in a separate tab within the Model and be clearly labelled.
Start with audited information / consider required outputs in design phase
- It is best to start where possible with audited historical information.
- Consider how you will want your business presented to investors and ultimately shareholders in terms of for example segmental analysis as this can impact on the level of detail that will be required in the Model.
- Have a separate output section that is independent from the rest of the Model.
Consistent structure throughout
- Adopt a consistent approach to structuring workbooks, worksheets and formulas as this will save time when building / learning / maintaining the Model.
- The profit and loss, cash flow and balance sheet and supporting schedules for e.g. fixed assets and working capital calculations should be in one tab rather than in separate tabs.
- Where possible avoid links to external workbooks as this greatly increases the chance of errors.
- Use a consistent time period (preferably monthly) throughout each worksheet as this will help to reduce the chance of errors and confusion.
- Use simple, clear formulas that can be understood by all users … more complex elements / calculations should be broken down into a number of different individual parts.
- Avoid hardcoding of numbers in formulas.