Case Builder Model is designed for building, analysing and comparing of up to five forecast scenarios, or operating cases, for a business or a project. The Model allows toggling between cases to run an active case through full set of financial statements. You can control which case is being run (is active) in the Model by toggling inputs on Navigation tab or DCF tab as described below.

Note that headers for all tabs would indicate what the active Case is. The feature helps to avoid mistakes as well as assists in exporting data for reporting and presenting purposes. Note that although it is only possible to run one Case at a time Case Analysis report helps to combine outputs from all cases present in the Model. Please see below for details.

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.

‘Navigation’ 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.

You can also control from ‘Navigation’ tab where the active Case selector is. You can switch Cases on ‘Navigation’ tab itself as shown on the diagram below, or, alternatively, activate the switch on ‘DCF’ tab. The latter option is helpful when conducting valuation analysis.

 

Figure 3.1. Navigation tab 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 the tab Capex_DA' with ‘Navigation' hyperlink

NB: To make hyperlinks work cell A1 of all tabs has a hidden marker containing tab’s name. Although cell A1 in each tab 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 with default values

You can change the above references on the cover page by going to the tab called 'General Settings' ('Global'). At the top of ‘Global’ tab you can assign your own labels 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 project name

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 periodicity is for advanced users only. FinRobot does not guarantee the Model will respond to change and work correctly.

 

 Figure 3.5. Inputsfor the Model’s calendar and periodicity

Income tax field is located immediately below the calendar items.
 

Input Area

Comment

Income Tax

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


All remaining editable areas of ‘Global’ tab - as described below - 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 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

Central Cost Items

Shows labels for your Cost of Goods Sold and Central Cost (Overhead) items. These labels are picked up by Cases’ tabs 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.

Cases

The last input area allows re-labelling Cases. If a Case name is changed in this area the Model would automatically update names for Cases in all headers and reports throughout the Model. Your version of the Model would show as many entry fields as the number of Cases selected during on-line assembly stage.


 

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 present in this tab are sourced from ‘Global’ tab and will react to any changes made to ‘Global' tab as per 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 providing calculations for product revenues and costs matches the number of Cases selected during the on-line assembly stage. The tabs are marked with letters from ‘A’ to ‘E’. For example, if only two Cases were selected at the stage of online assembly, then only ‘Case A’ and ‘Case B’ tabs will be present in the Model. You can name cases as desired (see ‘Global’ tab for details).

All Case tabs are identical structurally. However, note that COGS and Central Costs (Overhead) drivers differ between fixed and variable depending on the choice made during the assembly stage. 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

Central Costs

Input absolute amount for each COGS and Central Costs element for the first forecast period (base) followed by computed costs 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.

 

As detailed in Section 2 of the Manual, 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, it is not required to drive revenues 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 revenue line.

Figure 3.7. Inputs for sales volume and average product pricing

Figure 3.8. Example of inputs for variable cost elements

Figure 3.9. Example of Inputs for fixed cost elements

'Case Selector' tab acts as a data bridge between operating Cases and the rest of the Model. Data for active (selected) Case flows through this tab and is picked up by the Model’s financials and supporting schedules. The tab is designed to enhance integrity of the Model and no data entry is required. 

The master control switch allows assigning active switch to either ‘Navigation’ or ‘DCF’ tabs for Case selection. The control box located in the top left of the tab shows the current status of case selection such as which tab - ‘Navigation’ or ‘DCF’ - has an active Case switch, which Case is selected on each of these two tabs, and, finally, what is the active Case being run through the Model.

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

 

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.
 

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 Case Builder Model compliant with 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. 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

Picks up midpoint of terminal value EBITDA multiple from ‘Case Analsys’ tab as it is Case specific (refer to next section for details). 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

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

The last tab of the Model called ‘Case Analysis’ is designed to generate reports for all operating cases present in the Model with one click of a button. Click ‘Generate Report’ button located at the top of the tab and a built-in macro would do the rest.

To run this tab marco should be enabled in your general Excel settings. Normally, when you open a file with a macro-code inside it, Excel would automatically ask whether you want such content enabled. If for security reasons you wish to disable macro, the code behind Generate Report button would not work.

Figure 3.17. Case Analysis report – generating outputs and customizing KPIs

Another customization feature present in ‘Case Analysis’ tab is the possibility to vary exit assumptions across cases (as shown below). The rationale is that different operating assumptions behind each case can imply different risk and growth profile, hence, translating into exit multiples being case specific.

Note that the Model’s active case selector would pick up the right exit multiple assumption depending on the data in the table shown below. When report for all cases is generated it would cycle through this input and match each Case valuation to its exit assumption.

Figure 3.18. Case Analysis report – customising exit assumptions