of all fees for accounting purposes.
If the project assets are accounted for by using the finance debtor accounting treatment then this component will contain calculations of:
finance debtor balance
finance debtor balance amortisation
unitary charge control account balance.
The finance component contains all funding items, non-trading income and high level statement calculations. In particular the items contained are:
equity and dividends
subordinated debt, repayments and interest
mezzanine debt, repayments and interest
senior debt, repayments and interest
interest earned and received on reserves and cash balances
retained earnings balance with net profit in period as a counter flow from the financial statements
retained cash balance with net cash in period as a counter flow from the financial statements.
The tax component deals with all government tax issues. In particular:
VAT or equivalent
tax depreciation (writing down allowances for assets allowed for tax purposes)
corporation tax accrued and paid
tax loss balance.
The financial statements are the P&L, the CFW and the BS.
The actual management accounts are made up of a series of actuals that have happened and are fixed and logged. It may be that at some point in the future changes are made but these are rare. For this book, the actual management accounts are made up of:
P&L actuals to date
CF actuals to date
BS actuals to date
tax loss balance at last actual date
written down allowance balance for all assets at last actual date
at cost value of all asset classes (we call this the basis balance) for depreciation calculations.
The analysis component deals with all calculations and results that use the financial statements as a source for the data. This component includes calculations carried out with an alternative point of view to that of the company, such as shareholder returns. In particular, the items here are:
shareholder returns
present values of shareholder cash flows
company project return
present value of company cash flows
banking cover ratios such as ADSCR, PLCR and LLCR.
Modelling components
Modelling components are necessary to give the model further important functionality beyond core business modelling. These components may or may not also provide a name for the sheet on which they reside.
‘Inputs’ is the abbreviated form for ‘forecast inputs’ since the historic data is called actuals. These forecast inputs cover all other component titles and are all similar in that they are hard-coded numbers. Inputs can be formatted in a variety of ways, such as %, £k in 0,000, decimals in 0.000 format, and dates in dd-mmm-yy format.
The time component includes all modelling flags that turn calculations on and off throughout the model. The flags are in turn driven mostly by dates from the inputs component.
This component provides the manager and the modeller with a single set of navigational links to all parts of the model as well as any documentation on VBA forms.
The control panel component is a temporary area where results and inputs can be collected in order to drive the model. Once the analysis has been performed the inputs can be re-instated in their original location and the links to the results deleted.
This component has an identical structure to the financial statements except the values are hard-coded, hence the name reference.
This component has an identical structure to the financial statements except the values are the difference between the live financial statements and the hard-coded reference financial statements as described in the previous component.
The track component contains hard-coded sets of results derived from changing actuals and changing inputs. Each tracked result set is date and file stamped. This component is the core audit trail in the model.
The check component contains all error checks from throughout the model and summarises these into one single error check.
Each sheet in the model will have a header containing dates, period type labels, sheet name and check & track header (a collection of high-level important information coloured flags). The check & track header is available throughout the model to provide summary information on checks, alerts, and track and input changes.
The template component is a sheet that the modeller can use as a template to create new sheets in the model.
Formulae in modelling
I like to keep the formulae as simple as possible.
There is a frequently encountered and often laudable human desire to achieve perfection. When considering Excel formulae it is possible that some combination of formulae will do what we want, so this becomes the end goal. Experience then shows us that the model can suffer in a number of ways that can be hugely detrimental to the purpose of the model. Here is a list of those areas:
complex formulae are harder to understand and maintain
complex formulae copied and repeated can substantially add to the file size
complex formulae can cause unforeseen Excel problems such as inter-sheet link excess that then causes a loss of basic Excel functionality
complex formulae need to be properly understood to avoid unforeseen behaviour as this can be very time consuming to solve.
Event flags and switches
The simplest way to control when and if calculations happen is through the use of flags and switches.
Event flags are ‘on and off’ switches mostly governed by input dates that turn calculations on and off for distinct periods of time, ranging from single periods to all periods. Flags are time-dependent. The values that a flag can take are 1 or 0 for each period across
