Section 3 of the Manual gives you a tour of the Model and describes how you can work with the Model off-line. You may also consider reviewing this chapter prior to purchasing a Model. Each chapter of Section 3 is dedicated to one of the tabs present in the Excel file of the Model. For ease of reference the chapters are ordered to match data flow in the Model.

Please note that Excel file of the Model has fields marked with yellow background to highlight data input fields you can re-populate with your inputs without any risk to altering the structural layout of the Model.

In compliance with Principle 20 of ICAEW’s Twenty Principles for good spreadsheet practice, all worksheets of the Model are locked except for designated data input fields. If you need to change working areas of the Model you can unlock any tab by going to the Review menu at the top of Excel and clicking on ‘unprotect sheet’ button. Default password is finrobot, but you may wish to substitute a password of your own choice in place of the default. We recommend the Model is locked again after any planned changes to avoid accidental overwrites by end users.

Please ensure you make a back-up after downloading your Model.

‘Navigation’ tab allows clickable navigation between all tabs in Excel file of the Model. By clicking on the block with any tab name, you will be instantly 'jumped' to that tab.

Figure 3.1. Navigation flowchart in the Excel file of the Model

Navigation hyperlink is located in the upper left corner of each tab. Clicking it will return you to 'Navigation' tab. 

Figure 3.2. Upper left corner of ‘Supporting Calc' tab showing 'Navigation' link

To make hyperlinks work cell A1 in each tab has a hidden marker containing tab’s name. Although cell A1 appears empty, it is essential for the Model’s navigation to work properly. Do not remove this cell. 

There are three fields at the centre of the tab. When the Model is opened for the first time, the fields show the following:

Figure 3.3. Centre of 'Cover' tab showing default values

You can edit these legends by going to the tab called 'General Settings' ('Global'). At the top of ‘Global’ tab you can type in your own legends for the cover page, including the project name, version or date. The latter, unless manually overridden, will always show the current date whenever the file is re-opened. 

 Figure 3.4. Centre of 'Cover' tab showing new parameters

In addition to Cover settings described above, ‘Global’ tab contains general data inputs required by all other tabs of the Model to function properly.

 

If you populated all fields when assembling the Model online, there is nothing in the Global tab that requires your immediate attention. However, if you skipped some online Steps, ‘Global’ tab would be a good place to start populating the Model with your own data as follows:

Input field

Comment

Project & Model attributes

[Rows 7 – 9]

Your project or business name, model version and date (as illustrated immediately above  in section 3.2 'Cover' tab)

Calendar

[Rows 11 - 19]

The next block of cells deals with the calendar and periodicity of the Model. Whilst you can easily change the Model's Start Date (cell H11) we generally do not recommend changing its periodicity. If, however, it is absolutely necessary, please consider that:

·         Any changes to the Model’s periodicity should match with the period counters in lines 14 to 16 (counters of months, quarters and years) and cell G17 (number of periods).

·         If not done properly, some or all period dependent functions and calculations including interest charges, amortisation schedules and annual summaries may not perform as expected and should be checked for errors.

 

Resetting the Model’s periodicity is for advanced users only. FinRobot does not guarantee the Model will respond to change and will work correctly.

 

 Figure 3.5. Inputs for the Model’s calendar and periodicity

Income tax, currency and units fields are located immediately below the calendar items.

Input field

Comment

Income Tax

[Row 20]

Default value for Corporate or Income tax rate is 20% unless changed during the assembly stage

Currency

[Row 21]

Type in your own currency code in the field provided. The field is pure text and is not restricted to any currency code. For example, you may opt for GBP or £. Automatically applies to all financial items throughout the Model.

Currency Unit

[Row 22]

Currency unit or scale is set to 000s by default. The field is pure text label, make sure its value is consistent with volume scale of the Model inputted in the cell below (Volume Scale).

Volume Scale

[Row 23]

Factor used to make sure that your financials and other data consistently appear in thousands, millions or any other user defined scale. If you don’t’ wish to apply any scale input value of 1.

 

All remaining editable areas of ‘Global’ tab All remaining editable areas of ‘Global’ tab located in row 25 and down to the end of the sheet are labels for various line items used elsewhere in the Model.  Unless changed during the assembly stage these will show default values. You can replace any default label with something more suitable for your business. Your inputs will be picked up throughout the Model automatically.

Figure 3.6. Relabeling COGS items

 

Input field

Comment

Cost of Goods Sold Items

Labels for your Cost of Goods Sold items. These labels are picked up in Product Lines’ tabs of the Model. You will see as many fields as the number of COGS selected during the on-line assembly.

Units Items

Labels for measuring your COGS input costs, such as M3 or Kg. Similar to COGS’ labels these are picked up in Product Lines’ tabs and match the number of COGS selected during the online assembly.

Central Cost (Overhead) Items

Labels for your central costs and overhead items applied to Overhead’s tab of the Model. You will see as many fields as the number of Overheads selected during the on-line assembly.

Working Capital Items

Labels for working capital items picked up by Working Capital tab and Opening Balance Sheet tab.

Debt and Long Term Balance Sheet Items

Labels picked up by Opening Balance Sheet tab.

CapEx

Labels for your Capital Expenditure Items picked up by Capex & Depreciation tab. The Model provides for up to five elements of CapEx but you don’t have to use them all. Please see details in Section on CapEx.

 

If you entered financial data for your opening balance sheet positions during the online assembly stage, then it will be present in the purchased Model and can be edited in this tab as required. Rates for cash and debt funding positions are also inputted in this tab alongside respective balance sheet line items.

If balance sheet structure for your business is more detailed or itemised than what is provided for in the Model, we advise you to aggregate similar line items.

If the total amounts of assets and liabilities match, then the check field at the bottom of the tab will be green and show 'OK'. If there is a mismatch, the check field will turn red and show the amount of discrepancy between the total assets and the total liabilities.

There is one more 'OK'/’Error’ check field at the top of 'Opening balance' sheet tab. ‘OK’ status indicates that closing balance sheets for all future dates in the financial statements of the Model are balanced. This integrated all-period check is reproduced in all tabs of the Model to alert users if a new input causes any closing balance sheet 'going off'.

The number of tabs for product lines’ revenues and cost of goods sold (COGS) is determined by your choice made during the on-line assembly stage. The tabs are marked with letters from A to J. For example, if only two product lines were ordered online, then only ‘Product A’ and ‘Product B’ tabs will be present in the Model.

All product tabs are structurally equivalent. Online assembly populates all product tabs with identical data from your online entry for Product A. You will need to review content of Product B to [J] and replace inputs with your own assumptions.

Revenues for each product line is a function of uptime, output rate and price per unit. The top area of the tab deals with inputs and calculation for operation uptime as shown in the following Figure 3.7.

Figure 3.7. Inputs and Calculations for Operations Uptime.

The production volume is calculated based on the total hours worked in a period multiplied by the output rate as shown in the following figure. In turn, revenue is a function of volume output times the product’s average price.

Figure 3.8. Revenue calculation area of a product tab.

The Model’s structure assumes that each product has its own input values for direct costs, or COGS. Depending on choices made during online assembly you will see between one and ten COGS line items. The type of driver for each COGS element is also determined by your selection during the online assembly and can be any of the following:

 

Driver Type

Comment

Per unit of production

Based on number of units produced during a period. For example, an input value of 0.5 means that your business consumes 1 unit of input cost for 2 units of produced product. The Model will calculate total input units consumed off total units produced during a period. To calculate total cost for a line item you will need to provide cost per input unit and a price index to drive it forward.

Per work shift

 

Based on number of shifts worked during a period. The model will calculate total input units consumed off total shifts worked during a period. Helpful for modelling costs associated with processes linked to start/end of a shift like cleaning services, retooling or JIT deliveries. To calculate total cost for a line item you will need to provide cost per input unit and a price index to drive it forward.

Per hour of production

 

Based on hours worked during a period. The model will calculate total input units consumed off total hours worked (uptime) during a period. Helpful for modelling costs associated with operational uptime and/or contract work such as per hour pay to personnel or contractors

Fixed per period

Remains constant in real terms, or indexed, if desired. Describes costs which are not dependent on utilisation / uptime schedules such as rent

 

The following diagram illustrate an example of a typical cost driver.

Figure 3.9. Example of a COGS’s item computation block.

'Central Costs' tab contains inputs and calculations related overhead and central costs items such as administrative and marketing expenses.

The structure and computations should match the assumptions provided during the on-line assembly stage – please refer to Section 2 of the Manual for details. If you skipped input entries during online assembly, then your Model would contain dummy numbers. Depending on your selection online each cost element is forecast forward as either variable or fixed as shown in the diagram below:

Figure 3.10. Central Costs and Overhead assumptions.

Note that when the downloaded Model is opened for the first time, growth and margin drivers are set flat over time. You can assume any desired trajectory for each driver. For example, annual revenue growth may decelerate whilst costs as % of revenue may demonstrate improvements.

 

Note that the Model takes in growth rates expressed in annual terms. If your Model is quarterly or monthly, the Model will calendarise growth rates accordingly.

 

'Capex & Dep’n (depreciation) Schedule' tab contains all inputs and workings necessary to drive investment and depreciation fed into financials.

When adding or changing assumptions in this tab you should note the following:

Input field

Comment

Capex Input

Input area to change depreciation assumptions for each class. Any class of fixed assets can be renamed or re-labelled in 'Global' tab.

Input area for manual entry of your CapEx programme going forward. Each class of assets may have individual investment profile. The totals will be picked up to calculate funding, tax and cash flows for the business

Depreciation – Existing PP&E

The existing fixed assets are considered as one single group of assets with one average input for their remaining life. Gross value and accumulated depreciation amounts are picked up from 'Opening balance' sheet tab

Depreciation – New Assets

No additional input or assumptions required. The workings compute depreciation charges for each asset class for each period. The totals are picked for by tax computations and financials

 

Key input fields are illustrated below.

Figure 3.11. Inputs required for computing depreciation charges

Figure 3.12. Investment program input schedule

‘Working_Cap’ (capital) tab calculates working capital requirements for your business or project. Any data provided during the on-line assembly stage with respect to working capital items will appear in this tab. Otherwise the tab will be populated with dummy numbers. 

Please note that the principles behind working capital computations are as follows:

Input field

Comment

Debtors

Driven by % of Revenue expressed in days. Opening value picked up from 'Opening Balance' sheet tab, name can be changed in ‘Global’ tab

Inventory

Driven by % of COGS and is expressed in days. Opening value picked up from 'Opening Balance' sheet tab, name can be changed in ‘Global’ tab

Raw Materials

Driven by % of COGS and is expressed in days. Opening value picked up from 'Opening Balance' sheet tab, name can be changed in ‘Global’ tab

Creditors

Driven by % of Total Costs and is expressed in days. Opening value picked up from 'Opening Balance' sheet tab, name can be changed in ‘Global’ tab

Other CA and Other CL

Other items do not have explicit drivers. These are extras for manual adjustments if needed. For example, if there is a one-off settlement item in the future which needs to be booked into accounts without disrupting regular receivables and payables computations

 

The Model default layout assumes that drivers expressed in terms of days do not vary over time. If you operate under a different assumption, override the formula cells to the right of the yellow inputs (as per illustration below)

 Figure 3.13. Inputting assumptions for working capital projections.

3.9. 'Funding & Tax’ Tab

'Funding & Tax' tab contains workings for financing and corporate tax computations. The on-line assembly stage does not provide for any user pre-selected variation of the funding structure or for corporate tax module’s layout. However, you can input your own assumptions, as desired.

Similar to other balance sheet items the opening positions for debt elements and their respective labels are picked up from 'Opening Balance' sheet tab and 'Global' tab respectively.

To change interest rates assumptions, please, go to 'Opening Balance' sheet tab. Note that rates are expressed in annual terms and automatically calendarise depending on the chosen periodicity of the Model. There is no need to adjust anything if your model is quarterly or monthly.

By default, any period interest charge for any debt obligation is calculated based on the opening position. If there are large fluctuations due to borrowing and/or repayments this method can skew the computed result from what is actually expected.

The Model allows more accurate calculations of interest charges by switching to computing interest charges based on average debt positions. This would require the Model to go circular. To switch to average balances calculation turn on the ‘iterate interest’ switch located in the upper left corner of 'Financials' tab. Note, that if the switch is on, Excel settings (options) should have iterations (cyclical) options turned on in the Excel options menu. Otherwise, Excel will show a circular reference warning.

To make the Model compute your cash surplus or requirements you may set the minimum amount of cash balance required in the business at each forecast period (as shown below).  Entering an amount as negative disallows such balance from being available for debt repayments.

Figure 3.14. Minimum Cash Balance

The Manufacturing Model’s capital structure has two type of debts: short term revolver and long term debt funding. Revolver is a fully automated overdraft facility, which repays and borrows against available cash flows. Long term debt has a manual borrowing / repayment profile. You can use lines 32-33 highlighted in yellow in the following example to populate assumptions for long term debt consistent with your business’s borrowing terms. If your business has no long term debt, leave these fields blank and the Model will skip this calculation block.

Figure 3.15. Long term debt profile layout.

The tax calculations part of 'Funding & Tax' tab contains workings for your tax liability and cash tax payments. The schedule takes earnings before tax (EBT) from 'Financials' tab, allows for manual adjustment of your taxable earnings vis-à-vis reported earnings, and finally provides for any loss carried forward in case there is a taxable loss in any given period, which is offset against taxable income in the future.

The default assumption is that taxable turnover matches the reported in 'Financial' tab, and no manual adjustments are necessary.

FinRobot does not provide tax advice and the Model is not attempting to represent a real tax environment. You should seek advice from a tax specialist if you wish to model a tax environment compliant with tax laws and regulations relevant to your business.

'Financials' tab contains three standard financial reports, viz. profit and loss, balance sheet and cash flows. The tab does not require user inputs except for Exceptional Items and Equity distributions as described below. All other data are picked up from tabs covered in the preceding sections of the Manual.

The financial statements are purposefully generic. As our clients are located in various countries and operate under different accounting standards we cannot make the Model comply with a specific accounting standard.

 

Instead, we make reports relatively simple and easy to navigate and adjustable. Our experience shows that the majority of our clients are satisfied with our approach, particularly for the purposes of preparing management accounts and/or to facilitate an investment decision analysis.

The following table summarizes entry areas present in ‘Financial’ Tab of the Model.

Input field

Comment

The Net Exceptional Items & Adjustments

[Row 23 & 69]

 

Allows for manual entry of exceptional items, which are not practical to model, but are known occurrences within the forecast period.

For example, a known gain from a currency translation. Note that the Model implicitly assumes that any exceptional loss or gain is (i) a cash item, and is (ii) after tax (row 23 is linked to row 69).

If you have a non-cash exceptional item, you may need (i) to disconnect the link between row 23 and row 69 and (ii) to link row 23 to a corresponding line of the balance sheet (e.g. write off  / write up of a balance sheet position). Note that such adjustments would require good working knowledge of the Model. Otherwise, there is a risk that the balance sheet would 'go off' and the check flag would indicate red.

Equity Issue / (Buy Back or Dividend)

[Row 73]

Allows for manual entry of any forecast cash distributions (dividends or buybacks) or capital fundraising (issue). A positive entry means equity is raised. Negative means cash is returned to shareholders. Note that row 73 is linked to equity line of the balance sheet (row 53). Distributing cash to shareholders would reduce balance sheet equity, and any issue would increase shareholder funds.

You can automate the logic of dividend pay-outs by linking row 73 to % of net income from P&L or designing any other rule as required.

‘Iterate Interest’ Switch

[Cell G2]

Allows switching interest charges from beginning of the period balances to average balances. Please refer to section 3.8 above for details.

 

Annual Summary' tab is designed to automatically aggregate data for monthly and quarterly models into an annual summary. The tab does not require any user input.

Please note that if your monthly or quarterly forecast periods do not accrue to full number of years, the last forecast year in 'Annual Summary' tab will pick up the residual period of less than one full year.

The minimum number of years shown in 'Annual Summary' tab is three. Hence, if your project is less than two years you are likely to see blanks in the last column of the summary. Note that for any length of the project the summary would pick up last available projected balance sheet irrespective whether its date falls on a year end, or not.

'DCF Analysis' tab provides valuation metrics with respect to your project or business sourcing data from 'Annual Summary' tab. Hence, all financial information is presented on annual basis irrespective of the underlying periodicity of the Model.

The outputs are presented in grid form to show Firm Value and Equity Value based on discounted cash flows approach. IRR analysis is presented on Firm Value basis only.

Additional analysis is available with respect to the terminal value for going concern exit value. You can compare implied perpetuity growth to assumed multiple for terminal value and vice versa.

Please note that if your project is finite (for example an SPV) and its length does not accrue to full years of forecast, then NPV and IRR may require adjustments as set out below. For projects with duration of less than two years, we advise setting Terminal Value to zero. 

 

To run and interpret data with the help of 'DCF Analysis' tab please consider the following:

 

Input field

Comment

Terminal Value Exit Multiple

Insert your input for terminal value EBITDA multiple into the yellow input cell provided. The model will populate the output grid based on a step of +/-0.5x

If your project is finite you may consider assigning zero for the exit EBITDA multiple. This will make sure there is no terminal value to account for going concern value beyond your forecast horizon.

If the number of your forecast periods do not accrue to full years there may be an issue: Annual Summary will pick up less than the full year of cash flows and EBITDA for terminal value computations. As a result, terminal value and NPV for the business will come out less than expected. There is a quick fix to correct this by increasing the exit multiple accordingly. For example, if your last annual summary contains only six months of cash flows, adjust your exit multiple by increasing it by 2x

WACC

This is the rate at which unlevered free cash flows are discounted. You need to insert one central value to the left of the output grid and the Model will populate the grid vertically based on a step of +/-1%

Additionally, in case of timeline not matching to full number of years you should consider adjusting the discount rate for the last year of forecasts. To do this, in row 59 (calculation of average annual discount rate) in the column corresponding to the last year of forecasts (incomplete year), the discount factor step up from the preceding year should be changed from 1 to a different number. For example, if the last (incomplete)  year contains only 3 months, then the step up in discount factor should be equal to 0.5+(3/12)*0.5 = 0.625

 

          Figure 3.16. WACC and exit multiple assumptions

 

Input field

Comment

Capital Invested

By default, capital invested in the business to date equals to the amount of net operating assets as per the opening balance sheet, and can be adjusted upwards or downwards if the actual capital spent is higher or lower respectively. Note that for new greenfield projects the capital invested amounts may equal zero

Valuation Date

Balance Sheet Date

Investment Date

The Valuation Date is used to value projects at a specific date other than the start of the project. The Balance Sheet date will carry net debt and investments forward to the Valuation Date to make sure Firm Value and Equity Value are computed on the same basis. The Investment Date is used to calculate IRR. It is helpful if you want to analyse returns on investments done in the distant past relative to future cash flows. For greenfield projects the Investment Date is irrelevant

Unlevered Tax Schedule

'DCF Analysis' tab contains a separate tax schedule in order to compute unlevered tax charge consistent with application of WACC (as per MM2 theorem). The unlevered tax schedule provides for manual adjustments to book items disallowed for tax relief purposes

 

IRR function implies that either there is some invested capital upfront or that first period cash flow is a negative. If this is not the case, for example, you project shows positive cash flows for all periods and requires no upfront capital IRR calculation would return an error.