Please note that in Excel file of the Model fields marked with yellow background are data input fields which you can re-populate with your own data without any risk of altering the structural layout of the Model. Nevertheless, as it happens with financial and operating inputs, these have to be meaningful in the context of your business. For example, negative interest rates or tax rates would not yield a sensible IRR result.  Most importantly, be careful when making changes to periodicity of the Model. Please refer to the Model’s calendar in section 3.3.2 below.

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.

This tab allows for clickable navigation between all tabs in the Excel file of the Model. By clicking on the block with any tab's name, you will be instantly 'jumped' to the respective tab.
 

 


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


In the upper left corner of each tab there is the Navigation hyperlink. By clicking it, you will return to the 'Navigation' tab.

 

 
Figure 3.2. Upper left corner of the tab Capex_DA' with link to 'Navigation' hyperlink.

NB: In order to create hyperlinks in the 'Navigation' tab, cell A1 of all other tabs has a hidden marker for the tab’s respective name. Although cell A1  in each tab appears empty it is essential for navigation of the Model to work properly. Do not remove this cell.

 

There are three lines in the centre of the 'Cover' tab. When the file is opened for the first time off-line the fields show the following:
 

1

The first line states that this is the Model for Financial Robot

2

The second line states that this version of FinRobot Model is the Base Model

3

The third line shows the current date

 

 Figure 3.3. Centre of the 'Cover' tab with default texts.

You can change the above markings on the cover page by going to the tab called 'General Settings' ('Global'). At the top of the tab you can assign your own values to the cover page, including the project title, version or date attributes. The latter, unless manually overridden, will always show the current date whenever the file is opened again. 

 Figure 3.4. Relabeling items on the ‘Cover' tab.

In addition to Cover settings (as per 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 on-line there is nothing in the Global tab that requires your immediate attention. However, if you left the on-line form with dummy or default values, ‘Global’ tab would be a good place to start populating the Model with your own data as follows:
 

Input Area

Comment

Project & Model attributes

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

Calendar

The next block of cells deals with the calendar and periodicity of the Model.  Whilst you can easily change the Model's start date 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, 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 calendar periodicity is for advanced users only. FinRobot does not guarantee the Model respond to change and will work correctly.


 

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

Input Area

Comment

Income Tax

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

 

Figure 3.6. Relabeling COGS items, if required.
 

Input Area

Comment

Currency

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 £

Currency Unit

Currency unit or scale is set to 000s by default. The field is pure text label, so if you wish to scale your Model in millions, etc. your volume and pricing per unit assumptions should be scaled accordingly

Cost of Goods Sold Items

Shows labels for your Cost of Goods Sold items. These labels are picked up in Product tabs of the Model.

Central Cost Items

Shows labels for your central costs and overhead items applied to Overhead’s tab of the Model

Capex & Dep'n Items

Shows labels for your fixed assets picked up by CapEx and Depreciation tab of the Model

Working Capital Items

The next input area shows labels for current assets and liabilities picked up by the Working Capital and Financials’ tabs. Please note that there are logical limitations for renaming working capital items. First three items are driven by days’ ratios, whilst the last two are extras for booking manual adjustments such as one-offs and non-recurring items (for details please see the section on ‘Working Capital’ tab below).

Debt & Long Term Balance Sheet Items

Contains labels for your debt financing and long term balance sheet positions picked up by Funding and Financials’ tabs. The total number of debt items shown would depend on the choice made during the on-line assembly.

 

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 changed in this tab as required. If your project or business is a greenfield, then your opening balance sheet positions could be set to zero. Rates for your opening and forecast debt financing are inputted in this tab alongside respective balance sheet position.

The labels for the balance sheet items in this tab should match data provided during the on-line assembly stage and/or changes made to 'Global' tab as explained above.

If balance sheet structure for your business is more detailed or itemised than what is provided for in the Model, we advise you to group 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 an input error, the check field will be 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 all forecast balance sheets in the financial statements of the Model are balancing properly for all forecast periods. This integrated all-period balance sheet check is reproduced in all tabs of the Model to alert users if a new input makes balance sheets 'going off'.

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

All product tabs are identical structurally. However, note that COGS drivers differ between fixed and variable depending on the choice made during the assembly stage for each product line. Unless user specific data was submitted on-line the tabs will be populated with dummy numbers.
 

Input Area

Comment

Revenue assumptions

Shows volume of sales (in units) and  average prices for each product line for the first forecast period followed by computed volumes and prices based on growth drivers for all subsequent forecast periods. The Revenue line is computed automatically.

Please, make ensure that the scale of your units and currency matches what is required. E.g. 000s of units and price per 000s units will result in revenues expressed in millions.

COGS

Absolute amount for each COGS element for the first forecast period (base) followed by computed COGS driven by growth rate or margin (% of revenues) assumption depending on choice made during the online assembly of the Model. Please refer to section 2 of the Manual for further consideration

 

Note that when the downloaded Model is opened for the first time, growth and margin drivers are set flat over time but can be changed to 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. For example, input of 10% annual growth rate in a quarterly model will compute as 2.5% for quarter-on-quarter calculations. There is no need for manual adjustments.

Finally, if you do not require revenues driven by both volume and pricing assumptions, you can set sales volume to 1 and assign 0% growth rate to the volume factor going forward. The price line will then equal revenues.
 

Figure 3.7. Inputs for sales volume and average product pricing.

 

Figure 3.8. Example of Inputs for variable COGS element.

 

Figure 3.9. Example of Inputs for fixed COGS element.

 

'Central Costs' tab contains all inputs in connection to various overhead 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 no business specific data were provided during the on-line assembly stage the Model would show dummy numbers. You should change them accordingly –
 

Input Area

Comment

Central Costs

Provide absolute amounts for each Central Cost element for the first forecast (base) period followed by computed costs driven by growth rate or margin (% of revenues) assumption depending on choice made during the online assembly. Please refer to section 2 of the Manual for further consideration.

 

Note that when the downloaded Model is opened for the first time, growth and margin drivers are set flat over time but can be changed to 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. For example, input of 10% annual growth rate in a quarterly model will compute as 2.5% for quarter-on-quarter calculations. There is no need for manual adjustments.

'Capex & Dep’n (depreciation) Schedule' tab contains all inputs and workings necessary to drive investment and depreciation fed into financials. The tabs’ structure and inputs are determined by the on-line assembly stage – please refer to Section 2 of the Manual for details.

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

Input Area

Comment

Capex Input

Any class of fixed assets can be renamed or re-labelled in 'Global' tab and the number of classes shown at the top of the tab should match on-line configuration.

Input area to change depreciation assumptions for each class.

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 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


The key entry fields are illustrated below.

 

Figure 3.10. Inputs required for computing depreciation charges.

 

Figure 3.11. Investment program input schedule.

‘Working Capital’ tab calculates working capital requirements for your business or project. Any data were 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 Area

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

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.12. Inputting working capital assumptions.

 

'Funding & Tax' tab contains workings for financing and corporate tax computations. The structure should match the assumptions provided during the on-line assembly stage – please refer to Section 2 of the Manual for details.

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.

The interest rates are set and can be changed in 'Opening Balance' sheet tab. 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 by turning on the cyclical interest calculation switch. The switch is located in the upper left corner of 'Financials' tab. Note, that if the switch is on, then Excel settings (options) should have iterations (cyclical) options turned on too.

To complete your debt schedule of 'Funding & Tax' tab you may set the minimum amount of cash required in the business at each forecast period (as shown below).  Entered as a negative minimum cash requirement restricts cash on books becoming available for debt repayments.

Figure 3.13. Minimum Cash Balance.
 

Each debt element except for overdraft facility would have its own repayment profile (as shown below).
 

Figure 3.14. Standard 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 from 'Financials' tab, allows for manual adjustment to reported earnings, and finally provides for any loss carried forward in case there is a taxable loss in any given period, which can be 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 earlier section 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 Base Model compliant with a particular accounting standard.

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

The Net Exceptional Item 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 disposing of non-core other assets. Note that the Model implicitly assumes that any exceptional loss or gain is a cash item. If your circumstances are such that an exceptional item is non-cash, you need to disconnect the link between extraordinary P&L items and cash flow items and book your P&L item somewhere to a corresponding line of the balance sheet. 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.

The Equity Issue line of the cash flow 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. If you wish to use the line for a dividend programme, it is possible to link up the cash flow equity line to a % net income from P&L.

'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 amounts. For example, if the Model’s timeline is 38 months long, then year four of the summary will show results from operations for two month only.

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 0 in the last column of the summary. Note that for any length of the project the summary would pick up the correct last available projected balance sheet whether it falls on a year end, or not.

'DCF Analysis' tab provides valuation metrics with respect to your project or business as outlined in the Model. The outputs are presented in grid from for Firm Value and Equity based on NPV computations and as % IRR on Firm Value basis.

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

'DCF Analysis' tab picks up the data from 'Annual Summary' tab. Hence, all financial information is presented on annual basis irrespective of the underlying periodicity of the Model.

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

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

Input Area

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. Note that any projects with life of less than two years would not have any Terminal Value computed as the minimum forecast length to capture Terminal Value impact is set to three years or more

If the number of your forecast periods do not accrue to full years there may be a problem with how the terminal value is computed. The 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 the 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 line 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.15. WACC and exit multiple assumptions.
 

Input Area

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. Note that any projects with life of less than two years would not have any Terminal Value computed as the minimum forecast length to capture Terminal Value impact is set to three years or more

If the number of your forecast periods do not accrue to full years there may be a problem with how the terminal value is computed. The 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 the 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 line 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

IRR function implies that either there is some invested capital upfront or that first period cash flow is negative, e.g. cash flow series have to start with a negative number. 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.