Featured Models
Click button to view in Excel Online. Zoom tool is located bottom right.
NB: Models are Read-Only and unfortunately Non-Downloadable to protect IP.
NB: This page will not display correctly on Tablet/iPad.
Gym chain model
Long Range Planning Model with P&L, Cash Flow, KPIs and Valuation
This is a medium/large size, medium complexity long range planning model with P&L (two versions), Cash Flow, and simple KPIs. It facilitates the input of actuals. The model includes an EBITDA multiple-based valuation mechanism and is aimed at a growing SME interested in fund raising and/or future sale.
Revenue, cost and other (e.g. financing) assumptions are entered in yellow worksheets, along with actuals. These pass to green calculation worksheets, and are output in the blue worksheets 1-5 & Central, representing five gyms and Central costs. These are then consolidated, along with valuation and graphs.
This is, in my opinion, a good example of a best practice model with a relatable subject matter, gyms. The approach used is very easily expanded to, for instance, 50 gyms, or 100 gyms etc. and so is relevant to any size of business. A fundamentally similar, but much smaller, model is created by myself and participants of my Excel Modeller course - it encompasses all the same techniques.
Fashion designer financial model
Long Range Planning Model with Integrated Statements
This is a medium size, medium complexity long range planning model with integrated statements (P&L, Direct Cash Flow & Balance Sheet), of which there is a Forecast, Actuals and Variance version. There is also a Sales output. These are the Blue worksheets.
Inputs are the Yellow worksheets and the Yellow cells. Sales Actuals are input at a very granular level in their own dedicated worksheet. More granular than is ideal in a model, but which is useful here as an aide to track sales, inventory and popularity of different sizes, styles, colours etc.
The model is built at a Monthly level and summarised at a Seasonal and Annual level. This example holds plenty of spare capacity for additional product lines and cost types as can be seen by the prevalence of '[blank]' labelling throughout, and so may be suitable for a growing business.
Property portfolio model
Property Portfolio Model with Investor Returns
This is a small size, medium complexity mock property portfolio model aimed at real estate investment companies. In reality there is likely to be a greater deal of complexity in the calculations and customisation to the investment in hand.
The model features a range of outputs covering pro formas, cash flows and metrics for each property, a consolidated or Portfolio-level view of the same, and then Investor-level calculations and outputs for the investment as a whole.
Broadly speaking, inputs are entered into the Yellow cells in the Assumptions worksheet, are passed to the Green worksheets which process the bulk of the model's calculations, and are then 'looked up' and displayed in the Blue output worksheets.
This approach is particularly powerful when modelling multiple investments as spare capacity can be included in the calculations for future additional properties. An existing output is simply duplicated and then the Yellow input cell in the top left corner set to look up the appropriate calculations. This makes it much faster to expand, shrink and edit the model, increases consistency, and dramatically cuts down the number of 'unique formula' within the model, as opposed to a model where inputs, calculations and outputs are all held on the same property-level worksheet.
Investment Metrics & DCF
Example of the calculation of typical investment metrics over a defined period
This content demonstrates the calculation of several typical investment metrics – Enterprise & Equity Value via Discounted Cash Flow Analysis (DCF), IRR, Payback and Peak Funding. The DCF and IRR metrics are calculated for the number of years defined by the user.
The content provides a good example of the use of ‘flags’ in modelling (also known as ‘masks’). Flags help break down a calculation and often concern the timing element of the calculation. In this case they help define which periods will be included in the DCF and IRR, the period in which Peak Funding occurs, and the number of months until Payback.
It should be noted that different modellers may use slightly different approaches when compiling their investment metrics. For instance, some will employ mid-year discounting which concerns the adjustment of the discount factor to more accurately arrive at the Present Value of cash flows – it assumes cash flows occur over the course of a period, rather than at the end. The method featured here is considered the basic method, for simplicity.
Financing – Simple Senior Debt
Example of the calculation of loan balances and repayments under a range of assumptions
This content demonstrates a simple calculation of a senior debt arrangement, namely the timing and calculation of drawdowns, interest, and interest & principle repayments.
There is no one set way to calculate debt or loan agreements as the circumstances of the agreement vary. For instance, in the accompanying example, interest is assumed to be paid from a defined date and at defined monthly intervals, with the first payment covering all interest accrued to date. This treatment may vary in an alternative agreement.
However, a few general approaches can and should be applied to debt modelling to increase transparency:
- Inputs, calculations and output should be clearly separated.
- ‘Flags’ or ‘Masks’ should be employed to breakdown calculations and clearly show the timing of various elements of the loan.
- Corkscrew calculations (i.e. an opening balance, closing balance and lines for additions and subtractions in-between) should be used to model balances.
- MAX() and MIN() should be used to aid corkscrew calculation logic.
- Ideally OFFSET() should be avoided due to it’s lack of transparency, issues with auditing software, and its volatile nature forcing unnecessary model recalculation.
Gym chain model
Long Range Planning Model with P&L, Cash Flow, KPIs and Valuation
This is a medium/large size, medium complexity long range planning model with P&L (two versions), Cash Flow, and simple KPIs. It facilitates the input of actuals. The model includes an EBITDA multiple-based valuation mechanism and is aimed at a growing SME interested in fund raising and/or future sale.
Revenue, cost and other (e.g. financing) assumptions are entered in yellow worksheets, along with actuals. These pass to green calculation worksheets, and are output in the blue worksheets 1-5 & Central, representing five gyms and Central costs. These are then consolidated, along with valuation and graphs.
This is, in my opinion, a good example of a best practice model with a relatable subject matter, gyms. The approach used is very easily expanded to, for instance, 50 gyms, or 100 gyms etc. and so is relevant to any size of business. A fundamentally similar, but much smaller, model is created by myself and participants of my Excel Modeller course - it encompasses all the same techniques.
Fashion designer financial model
Long Range Planning Model with Integrated Statements
This is a medium size, medium complexity long range planning model with integrated statements (P&L, Direct Cash Flow & Balance Sheet), of which there is a Forecast, Actuals and Variance version. There is also a Sales output. These are the Blue worksheets.
Inputs are the Yellow worksheets and the Yellow cells. Sales Actuals are input at a very granular level in their own dedicated worksheet. More granular than is ideal in a model, but which is useful here as an aide to track sales, inventory and popularity of different sizes, styles, colours etc.
The model is built at a Monthly level and summarised at a Seasonal and Annual level. This example holds plenty of spare capacity for additional product lines and cost types as can be seen by the prevalence of '[blank]' labelling throughout, and so may be suitable for a growing business.
Property portfolio model
Property Portfolio Model with Investor Returns
This is a small size, medium complexity mock property portfolio model aimed at real estate investment companies. In reality there is likely to be a greater deal of complexity in the calculations and customisation to the investment in hand.
The model features a range of outputs covering pro formas, cash flows and metrics for each property, a consolidated or Portfolio-level view of the same, and then Investor-level calculations and outputs for the investment as a whole.
Broadly speaking, inputs are entered into the Yellow cells in the Assumptions worksheet, are passed to the Green worksheets which process the bulk of the model's calculations, and are then 'looked up' and displayed in the Blue output worksheets.
This approach is particularly powerful when modelling multiple investments as spare capacity can be included in the calculations for future additional properties. An existing output is simply duplicated and then the Yellow input cell in the top left corner set to look up the appropriate calculations. This makes it much faster to expand, shrink and edit the model, increases consistency, and dramatically cuts down the number of 'unique formula' within the model, as opposed to a model where inputs, calculations and outputs are all held on the same property-level worksheet.
Investment Metrics & DCF
Example of the calculation of typical investment metrics over a defined period
This content demonstrates the calculation of several typical investment metrics – Enterprise & Equity Value via Discounted Cash Flow Analysis (DCF), IRR, Payback and Peak Funding. The DCF and IRR metrics are calculated for the number of years defined by the user.
The content provides a good example of the use of ‘flags’ in modelling (also known as ‘masks’). Flags help break down a calculation and often concern the timing element of the calculation. In this case they help define which periods will be included in the DCF and IRR, the period in which Peak Funding occurs, and the number of months until Payback.
It should be noted that different modellers may use slightly different approaches when compiling their investment metrics. For instance, some will employ mid-year discounting which concerns the adjustment of the discount factor to more accurately arrive at the Present Value of cash flows – it assumes cash flows occur over the course of a period, rather than at the end. The method featured here is considered the basic method, for simplicity.
Financing – Simple Senior Debt
Example of the calculation of loan balances and repayments under a range of assumptions
This content demonstrates a simple calculation of a senior debt arrangement, namely the timing and calculation of drawdowns, interest, and interest & principle repayments.
There is no one set way to calculate debt or loan agreements as the circumstances of the agreement vary. For instance, in the accompanying example, interest is assumed to be paid from a defined date and at defined monthly intervals, with the first payment covering all interest accrued to date. This treatment may vary in an alternative agreement.
However, a few general approaches can and should be applied to debt modelling to increase transparency:
- Inputs, calculations and output should be clearly separated.
- ‘Flags’ or ‘Masks’ should be employed to breakdown calculations and clearly show the timing of various elements of the loan.
- Corkscrew calculations (i.e. an opening balance, closing balance and lines for additions and subtractions in-between) should be used to model balances.
- MAX() and MIN() should be used to aid corkscrew calculation logic.
- Ideally OFFSET() should be avoided due to it’s lack of transparency, issues with auditing software, and its volatile nature forcing unnecessary model recalculation.
Subscribe to receive our Top 10 Excel Tips eBook straight to your Inbox!
*We'll email a link to confirm subscription. You agree to receive free content and occasional marketing emails.
