t Financial Model Databook 3 August 2006 Amended 6 February 2007 IRG 16/062 Contents 1 Introduction 5 1.1 Overview 5 1.2 Objectives and high level overview 5 1.3 Model limitations 6 1.4 High level model structure 6 1.5 Detailed model structure 7 2 Technical guide – getting started 8 2.1 Macros 8 2.2 Dates (#Name?) 9 2.3 Auto calculation 9 2.4 Model parameters 9 2.5 Indicative timeframe for model 9 2.6 Opening monthly balance sheet and latest year-end 10 3 Overview of worksheets 12 4 Inputs 15 4.1 Cover 15 4.2 I_Income(Base) 15 4.3 I_Costs (Base) 21 4.4 Service developments (I_SDev 1-5) 28 4.5 PFI (I_PFI and I_PFI (Memo)) 29 4.6 I_CIP 30 4.7 I_Infl 31 4.8 I_Budgt Perf 31 4.9 BS_Hist 32 3 4.10 BS_For 32 4.11 I_NE 36 5 Key outputs 38 5.1 O_FS 38 5.2 O_Bridge 39 5.3 O_KPI 40 6 Key checks 41 7 Scenario/sensitivities 42 7.1 S_Activity (Base) 42 7.2 S_Input 43 7.3 Scenario running 43 4 5 1 Introduction The purpose of this databook is to provide guidance on the methodology and workings of Monitor’s financial model used as part of the foundation trust application process. 1.1 Overview The approach to building the combined long term and working capital model (the model) has been to liase with Monitor’s assessment team and external parties to define and agree what the model will do, to build, test, and externally verify the model. The overriding aim has been to keep the financial model as simple as possible, while still meeting the agreed objectives and business requirements. We have built upon the long term model and working capital model used in previous assessments and have incorporated the lessons learned. The level of information requested is similar to that provided in the original long term model but we have redefined the level and structure of the financial data to ensure it aligns with Monitor’s assessment process and outputs. 1.2 Objectives and high level overview The overall objective has been to build one fully integrated financial model (income & expenditure (I&E), BS, CF) centred around the key drivers and assumptions underpinning the applicant trust’s financial projections. The model has been built to include three years’ of historical data, the current year outturn and five years’ of forecasts. The first two-three years’ of projection (dependent on the opening balance sheet position) are on a monthly basis. For trusts with major Private Finance Initiatives (PFIs) there will be a need for them to incorporate their projections and assumptions over a 10-year period and the model has been constructed in such a way as to allow for this. The outputs are consistent with Monitor’s annual planning process and Compliance Framework. The model outputs are automatically generated using the input data. Users will be able to run high level sensitivity and scenario analysis both easily and quickly. These sensitivities and scenarios can then be saved as a separate workbook to assist with business planning. 6 1.3 Model limitations The model is an integrated strategic planning tool and not a detailed budget setting tool. It requires information from the trust’s own detailed income and activity/capacity based modelling to form the basis of the income analysis. 1.4 High level model structure The model has been built in a modular fashion using a layered approach • The layered approach starts with inputs for the BASE level income, activity/capacity and cost information, in REAL prices (i.e. net of inflation). Service developments are input separately using the sheets allocated, the model will combine these with the base input information and inflation assumptions to deliver nominal aggregated outputs in the output sheets. • Assumptions are first input on an annual basis (i.e. income, costs and capital expenditure) these are then phased over each year using a percentage input for each month, to arrive at the monthly forecast. Base Assumptions -Income by activity/speciality -Costs -Capex Service developments -Income by activity -Costs -Capex Balance sheet assumptions Key Assumptions : tariff Inflation, pay inflation etc Sensitivity Analysis CALCULATION ENGINE Outputs -I&E -BS -CF -Charts -Scenario Analysis PFI -Income by activity -Unitary Charge -Capex Trust detailed Activity modelling t 1.5 Detailed model structure • The model has been constructed in modular fashion split into input sheets (denoted by an “I” – yellow boxes below), calculation sheets (denoted by a “C” – orange boxes below), and output sheets (denoted by an “O” – blue boxes below. General: • Users should not attempt to insert extra rows in the model as this could cause the model to fail. If you require further input rows please contact Monitor. • Each of the sheets is covered in detail in the “Overview of Worksheets” section of this databook. Input Sheets: • cells available for user inputs are shaded in yellow; • the input cells are split by year and then by month as outlined in “Objectives and high level overview” above (scroll towards the right on the input sheets to find the monthly input areas); and • all units should be input in £000’s unless otherwise stated. Calculation and output sheets: • All calculation and output cells are password protected. CONTROL (SCENARIOS + SENSITIVITIES) I_Income (Base) I_Costs (Base) S_Dev (1-5) Sens_Input I_Infl I_BS(Hist) I_BS For C_I&E (REAL) I_ PFI I_ CIP I_ NE C_I&E (Nominal) C_Cash C_BS O_FS O_Bridge O_KPI O_Risk Rating O_Charts Key I&E Input Limited I&E InputKey BS Input Calculations Outputs Sens_Activ ity (BASE) 8 2 Technical Guide – getting started 2.1 Macros The financial model includes a couple of simple Macros to calculate the scenarios and to print the model (these will not harm your computer). When the model is opened, a message box will appear as below. Please click on ‘Enable Macros’. If you cannot run the scenario or print Macros then you may need to adjust your security settings for Macros. To do this: 1. go to the “Tools” menu (on the toolbar between “Format” and “Data”); 2. select “Macro” and click on “Security…” (you may need to select the downward pointing arrows to extend the menu if you cannot see “Macro”); and 3. click on the “Security level” tab and click on “Medium”. Press “OK”. Now close out of Excel and reopen the model. 9 2.2 Dates (#Name?) The model works of number of date functions within Excel. Whilst these are standard functions they may need to be switched on in your version of Excel through the add-in facility. When you have opened the model if the dates in the worksheets appear as #NAME? then you will need to incorporate your analysis tool pack add-in. To do this 1. go to the “Tools” menu (on the toolbar between “Format” and “Data”); 2. select “Add-Ins…” (you may need to select the downward pointing arrows to extend the menu if you cannot see “Add-Ins…”); and 3. click on “Analysis ToolPak” and “Analysis ToolPak-VBA”. Press OK. The (#NAME?) should have now cleared. If this does not work then you may need to contact your system administrator. 2.3 Auto Calculation In order for the model and the macros to work efficiently -the auto calculation functionality has to be switched on. To do this: 1. go to the “Tools” menu (on the toolbar between “Format” and “Data”); 2. select “Options…” (you may need to select the downward pointing arrows to extend the menu if you can’t see “Options…”); and 3. select the “Calculation” tab and click “Automatic”. Press “OK”. . 2.4 Model parameters • All yellow shaded cells are inputs. • All input sheets are prefixed with I_ • All units are £000’s unless otherwise stated • All calculation and output cells are password protected. 2.5 Indicative timeframe for model Assuming the current year is 2005-06 then model requires: 10 • three years’ of historical information for years ended 2002-03, 2003-04 and 2004-05; • two years’ of monthly phased information covering remaining portion of current year 2005-06 and 24 months ended 31 March 2008; • additional information for a further three years’ (if no PFI) to 31 March 2011; and • for PFIs a further seven years’ additional information to 31 March 2015. 2.6 Opening monthly balance sheet and latest year-end The model starting point for the monthly forecasts is the latest year-to-date balance sheet position. To do this – go to the “Control” sheet and: • choose the latest year-end balance sheet date by clicking on the dropdown box in cell F11; and • choose the opening balance sheet date by clicking on the dropdown box in cell F13. 11 12 3 Overview of worksheets Input worksheets • Cover – This is the cover sheet for the model • Model structure – this worksheet provides a diagram to show how the model is structured. Each box represents a workbook. Those workbooks coloured completely in yellow represent the input worksheets. Semi-yellow sheets show sensitivity worksheets, those coloured orange represent the calculation worksheets and the blue boxes represent the output worksheets, the information contained within these worksheets are generated from the input sheets (NB: users do not need to do anything with the orange or blue worksheets). • Control -on this worksheet you can control which sensitivities are run on an individual basis and you can run scenarios where sensitivities can be combined to view the overall effects. This worksheet contains four graphs to show the impact of each sensitivity (or multiple sensitivities – scenario) on normalised earnings, cash flow and cash at bank • Income base (I_Income(Base)) -this worksheet is for inputting base income assumptions for your underlying business excluding any major service developments. Assumptions are based annually with monthly phasing for working capital forecasts. This includes historical data and forecasts are on real terms. • Cost base (I_Costs(Base)) -this input worksheet has been designed to capture the base cost assumptions for both pay, non pay ,cost improvement programmes (CIP), and capital expenditure on a historic and projected basis. Again these are annual and phased monthly for working capital forecasts, with forecasts in real terms. • Service development worksheets (1-5) (I_SDev1-5) – one sheet per major service/strategic development (five input sheets in total). These worksheets will capture the income and costs associated with each major service development, these will also need to be phased on a monthly basis over the first three years’. Only developments which have a proportionally significant impact on the plan should be outlined here, smaller immaterial developments should be incorporated into the base sheets. 13 • PFI worksheet (I_PFI) – need only be completed by those applicants who have a PFI. This workbook is designed to capture all incremental PFI specific income and costs, in addition to those outlined in the base income and cost sheets that are directly attributable to the PFI scheme. For example unitary payment. These are annual assumptions but will also need to be phased on a monthly. There are two PFI worksheets, this and the PFI memo the purpose of which is outlined below. • PFI memo (I_PFI(Memo)) -this is a standalone PFI information worksheet. The details entered into this worksheet do not feed into the model outputs. The purpose of this worksheet is to capture the PFI as a standalone scheme to enable transparency and assist with understanding how the scheme will impact on the income and expenditure account and cash flow. • CIP worksheet (I_CIP) – is designed to capture historic CIP information and any additional CIP savings which can be identified going forward that do not relate to staff or non-staff costs as already identified within the cost base worksheet. Note that the cost base worksheet CIP’s will automatically link through to this workbook in the output cells provided. • Inflation worksheet (I_Infl)– this worksheet is used to for inputting annual inflation for income, costs (including capital expenditure) and unitary charges for PFI schemes for the base case plus two scenario cases. • Normalised earnings (I_NE) – this worksheet normalises the trust’s earnings, in other words all non-recurring income and costs needs to be stripped out of income and expenditure to ascertain the underlying position. • Budget performance (I_Budgt Per) – this worksheet captures historical actual verses budget performance information at a high level. This is a requirement of the assessment process which previously was not captured in the old long term model. • Balance sheet history (I_BSHist) – this worksheet captures the last three years’ historical balance sheet positions and the opening balance sheet position. • Balance sheet forecast (I_BSFor) – this worksheet is for inputting all future balance sheet assumptions such as working capital, fixed assets, reserves, funding on an annual and monthly basis. 14 • Activity base (S_Activity(Base)) – this worksheet is designed to capture any change in activity assumptions for the two scenario cases. • Sensitivity input (S_Input) – this worksheet should be populated with the income and cost amounts (in monetary terms) that are assumed within each of the two scenario cases in real terms unless specified. The sensitivity sheets are intended to facilitate high level scenario analysis for the trust’s internal processes and are not expected to be completed as part of the application. Assessors will discuss with applicant trusts what scenario analysis they have performed. Output worksheets • Financial statements (O_FS) – this worksheet outlines the I&E, Balance sheet and cash flow on an annual and monthly basis. It also calculates for the Prudential Borrowing Code (PBC), risk rating , and private patient cap ratios. • Bridge analysis (O_Bridge and O_BCharts) – this worksheet provided an analysis of the key drivers of movement in income and costs form one year to the next based on inputted assumptions. The analysis is graphed in a bridge chart both for short term and long term. • Key performance indicators (O_KPI) – this worksheet summarises the key performance metrics based on assumptions and form the basis of high level benchmarking. • Risk rating (O_Rating) – this worksheet details the financial risk rating for the trust based on criteria and metrics as outlined in the Compliance Framework. This is available from Monitor’s website, www.monitor-nhsft.gov.uk. 15 4 Inputs 4.1 Cover • Please insert the name of the trust in Cell D18 in the “Cover” sheet. 4.2 I_Income(Base) As explained previously the model is structured to capture a trust’s base underlying “as is” business with any service/strategic developments overlayyere on the top. In this sheet users should outline their historic and projected income for the underlying/normalised business. Income forecasts should be presented on annual basis in real terms (i.e. excluding inflation) with the income phased monthly for the first two-three years’ of the forecast. Where you have a PFI scheme the income and costs associated with the activity in the PFI scheme should be presented in your base underlying business (i.e. “I_Income(Base)” and “I_Cost(Base)”). The incremental additional PFI specific income and costs are to be provided in the I_PFI input sheet. Items included in this section are : • tariff/funding support; • unitary payment; • mobilisation costs; and • dual running costs. 4.2.1 NHS Clinical income NHS clinical income is split by activity (elective, non-elective, outpatient, A&E, other) broken down by tariff and non-tariff income. Income needs to provided at full tariff excluding Market Forces Factor (MFF) where applicable, (i.e. on elective). For each activity you need to provide: 16 • Income data on an annual basis, historic and projected, broken down by your top 10 specialties. Please group the remaining income in the other category. See below for example on elective income • The PBR (clawback)/relief and transitional gain/(loss). Please note the transitional gain/(loss) feeds into the bridge analysis not the bottom line income. PBR example for elective Example 1 -assume total transitional gain for elective is £7m calculated at 2004-05 prices. Actual Actual Actual Outturn Forecast Forecast Forecast Forecast Forecast Forecast Forecast Forecast Forecast Mar -03 Mar -04 Mar -05 Mar -06 Mar -07 Mar -08 Mar -09 Mar -10 Mar -11 Mar -12 Mar -13 Mar -14 Mar -15 PBR -Gain Elective Example Total gain on elective 7,000.0 Price profile Nominal Nominal Nominal Nominal Real Real Real Real Real Real Real Real Real Inflation assumption 10% Transition path 25% 50% 75% 100% 100% 100% 100% 100% 100% 100% Total gain using 05/06 current prices 7,700.0 PBR -Elective PBR (Clawback)/Relief £000's (5,775) (3,850) (1,925) 0 0 0 0 0 0 0 PBR Transitional Gain/(loss) -Feeds into Bridge Analysis £000's 1,925 3,850 5,775 7,700 7,700 7,700 7,700 7,700 7,700 7,700 Example 2 -assume total transitional loss for elective is £7m calculated at 2004-05 prices. Example: top 10 elective specialties PBR example see below MFF funding Activity related to income Forecasts in REAL terms 17 Mar -03 Mar -04 Mar -05 Mar -06 Mar -07 Mar -08 Mar -09 Mar -10 Mar -11 Mar -12 Mar -13 Mar -14 Mar -15 PBR -Loss Elective Example Total loss on elective 7,000.0 Price profile Nominal Nominal Nominal Nominal Real Real Real Real Real Real Real Real Real Inflation assumption 10% Transition path 25% 50% 75% 100% 100% 100% 100% 100% 100% 100% Total loss using 05/06 current prices 7,700.0 PBR -Elective PBR (Clawback)/Relief £000's 5,775 3,850 1,925 0 0 0 0 0 0 0 PBR Transitional Gain/(loss) -Feeds into Bridge Analysis £000's (1,925) (3,850) (5,775) (7,700) (7,700) (7,700) (7,700) (7,700) (7,700) (7,700) Please note that the transition path to full PBR is different for nonelecctive • MFF (Market Forces Factor should be entered in as a separate funding stream on elective activity). • Phase income on a monthly basis through entering %’s. Ensure monthly phasing total 100%. • Activity related to the income (elective (spells), non-elective (spells), outpatients (first and follow-up), A&E (attendances), other (user specified). 18 4.2.2 Non-NHS clinical income Please provide historic actual (not real), year-end outturn, and forecast on an annual basis and phased monthly for the first two–three years’. Forecasts to be provided in real terms. Historical on actual basis. Private patient income This is income arising from the treatment of private patients. Legislation requires that the proportion of private patient income as a percentage of total patient related income of NHS foundation trusts should not exceed its proportion whilst the body was an NHS trust in 2002-03. If the predecessor NHS trust was not in existence in 2002-03 the proportion in the first year of existence of the predecessor NHS Trust should be used. Guidance on the calculation of the private patient charges cap is provided in the NHS Foundation Trust Financial Reporting Manual on Monitor’s website, www.monitor-nhsft.gov.uk. Other non protected non clinical income This is income related to items such as road traffic accidents (RTAs) and Ministry of Defence income etc. 4.2.3 Other operating income Research & Development This is income which is received for the purposes of undertaking research & development. Education & Training This includes income from • MADEL – Medical and Dental Education Levy; and • SIFT – Service Increment for Teaching. Non-patient services to other bodies This is income resulting from the provision of non-patient services to other bodies e.g. laundry, ambulance services, laboratory services Transfers from donated asset reserve Please provide the historic accounting income relating to the transfer from donated asset reserve. For the year-end outturn and the forecast years this will be automatically calculated based on assumptions (depreciation on 19 donated assets, profit/loss on donated assets, and donated asset impairments) provided in the “I_BSFor” sheet. Please note for cash flow purposes, income related to the transfer of donated asset reserve is assumed to be non-cash. 4.2.4 Non-recurring income To enable the assessment team to understand the underlying/normalised earnings position – please outline any non-recurring income included in historical and forecast results. Assumptions about non-recurring income and costs feed into the bottom line. For example where non recurring income items have been previously been incorporated into clinical income they need to be removed and presented in the non-recurring section, to avoid double counting. Even where non-recurring income is matched by a non-recurring cost – please provide the income detail in this section and the cost details in the non-recurring cost section in the I_Cost (BASE) sheet. Examples of nonrecuurrin income and costs are detailed in Appendix 1. Forecasts in REAL terms20 4.2.5 KPIs – key performance indicators The KPI information provided here does not drive the forecast but is for information purposes. We have assumed definitions are consistent across providers. If you own trust methodology is different please provide trust calculations but inform assessors where your definitions are different. Average length of stay (days) Average length of stay = total number of days in all spells ended in the period (with spells representing day case admissions treated as one day in length), divided by the total number of spells ended in the same period. Average length of stay excl day cases (days) Average length of stay = total number of days in all spells ended in the period (excluding spells representing day case admissions), divided by the total number of spells ended in the same period (excluding spells representing day case admissions). Bed occupancy (%) Bed occupancy = number of overnight stays in period/bed capacity in the period, where: Number of overnight stays Total number of days in the period for all spells beginning in or before the period and ending in or after the period (excluding spells representing day case admissions). Bed capacity Number of beds, trolleys, etc. available and intended for use for admitted patients for overnight stays (i.e. spells of >1 day duration) controlled by the Trust each night in the period cumulatively over all the nights in the period. Theatre utilisation (%) Utilisation of potential theatre time, which is an examination of the time used in held sessions with the potential time available had no session cancellations taken place (i.e. it includes unused sessions). Day case percentage (day cases/spells) DC Percentage = total number of spells representing day case admissions in the period divided by the total number of spells (including spells representing day case admissions) ended in the same period. 21 New to follow up outpatient ratio Based on trust’s calculation methodology. Number of consultant programmed activity sessions per week Average number of consultant programmed activity sessions a week as agreed through consultant’s contract. Number of beds Maximum total number of beds, trolleys, etc. available and intended for use for admitted patients for overnight stays (i.e. spells of >1 day duration) controlled by the Trust. Local population Estimate of the local population in catchment area. 4.3 I_Costs (Base) As explained previously the model is structured to capture your base underlying business with any significant service/strategic developments over-layered on the top. In this sheet users should outline your historic and projected costs for the underlying business. Cost forecasts should be presented on annual basis in real terms (i.e. excluding inflation) with the costs phased monthly for the first two-three years’ of the forecast. 4.3.1 Pay Costs Pay costs are broken down into non-agency and agency costs for the following categories: • consultants • junior medical o all registrars and senior registrars o senior house officers and house officers o other medical hospital grades • nursing, midwifery & health visitors o nurse consultants -NA o nurse managers -NO o qualified -N(1 to 7) o unqualified -P and N (8 to 9) • dental 22 o consultants o other career grades o all registrars and senior registrars o senior dentist house officers and dentist house officers o other dental hospital grades • scientific, therapeutic and technical o professions allied to medicine S*(A to H) (excludes speech therapists) o scientific and professional staff S*(J,L to Q) (includes speech therapists) o professional and technical -. S*(R to T) o other scientific, therapeutic & technical staff -S*K , S*X and S*U • other clinical o healthcare assistants o ambulance staff • non-clinical o senior managers and mangers o administrative & clerical o maintenance & works staff o other employees Non-agency pay cost information requirements. For each pay cost category please provide the historic and outturn year cost in nominal terms. For the forecasts (on an annual basis) please outline the driving factors of cost growth in real terms. These are broken down into % increases for the following: • staff – growth +%; • staff CIP savings -% (i.e. reduction in costs); • national cost pressures +ve %; and • other +/-ve %. The growth factors entered for the outturn year are for information only. The forecast average number of staff will be automatically calculated based on assumptions around staff growth and CIP staff savings. The costs should be phased monthly for the first two to three years’ of the forecast, based on an inputted % phasing figure. 23 Note: Please note even in a situation where your average staff numbers are expected to be in balance due to staff growth in one area and CIP staff savings in another – users need to fully reflect this. This is to ensure that the CIP staff savings are captured in the analysis for Cost improvement programmes. Please see example below. The CIP information feeds into the I_CIP sheet. Agency pay. Please provide historic, outturn and forecast (real terms) agency costs by staff category. 4.3.2 Other pay related CIP If there are any cost improvements in addition to those outlined for staff savings in pay costs then please outline them based on a recurrent and non recurrent basis. Assumptions for outturn year are for information only Staff growth Cost saving assumption National cost pressures Calculated based on staff assumptions Forecasts in REAL terms24 4.3.3 Drug costs. Please provide the historic and outturn year cost. For the forecasts (on an annual basis) please outline the driving factors of cost growth in real terms. These are broken down into % increases for: • activity growth; • developments (e.g. Royal College guidance); • NICE guidelines; and • other. The growth factors entered for the outturn year are for information only. For monthly forecasts please provide monthly phasing on a % basis Drugs CIP If there are any cost improvements in relation to drug costs then please outline them based on a recurrent and non recurrent basis. For example Any additional pay CIPs (forecast in real terms 25 price savings resulting from better procurement should be outlined here rather than applying low inflation rates 4.3.4 Clinical supplies and services costs. Please provide the historic and outturn year cost. For the forecasts (on an annual basis) please outline the driving factors of cost growth in real terms. These are broken down into % increases for: • activity growth; and • other. The growth factors entered for the outturn year are for information only. For monthly forecasts please provide monthly phasing on a % basis Clinical supplies and services CIP If there are any cost improvements in relation to clinical supplies and service costs then please outline them based on a recurrent and non recurrent basis. 4.3.5 Other costs Other costs are broken down into the following categories: • general supplies and services; • establishment expenditure; • premises and fixed plant; • ambulance services; and • other. Please provide the historic and outturn year and forecast costs on an annual basis, phased monthly for the first two years’. Annual forecasts should be provided in real terms. Other costs CIP If there are any cost improvements in relation to other costs then please outline them based on a recurrent and non-recurrent basis. 26 4.3.6 Non recurring costs To enable the assessment team to understand the underlying earnings position – please outline any non-recurring costs. Assumptions about non -recurring costs and income feed into the bottom line. Therefore, to avoid double counting the non-recurring costs should be excluded from the cost categories above and inserted separately into this section. An example list of non-recurring income and costs is detailed in Appendix 1. 4.3.7 Marginal costs for additional activity – for information to run activity sensitivity In order to run an activity sensitivity at a high level – please outline the marginal cost (%) for additional activity for elective, non-elective, outpatient, A&E, and other. Please also break down the weighting of the marginal cost (%) into a % breakdown of pay costs, drug costs, clinical supplies and services and other costs. See example below The marginal cost for additional activity assumptions feed into in the activity sensitivity sheet (S_Activity). Note: Please note that this is a high level aggregate methodology to run activity sensitivities as marginal costs for additional activity may differ widely across specialities. In addition based on the input assumptions a high level on overall aggregate marginal cost can be determined – this is shown in the O_KPI sheet and will be utilised as a basis for discussion between the trust and Monitor to fully understand the costs associated with increased activity. 27 4.3.8 Capital expenditure for base business Please outline historic, outturn and forecast annual capital expenditure on an accrual basis and phased monthly. Capital expenditure is broken down into: • maintenance capex – capital expenditure required to maintain and operate the existing asset base; and • non-maintenance capex – any strategic capital schemes (excluding major service developments). Capital expenditure related to major service developments should be outlined in the service development input sheets, discussed below. Elective activity – average marginal cost is 60% weighted by 50% pay, 40% drug costs, 10% clinical supplies 28 4.4 Service developments (I_SDev 1-5) Please outline the income, costs and capital expenditure related to any MAJOR service developments. These are new strategic developments that impact the landscape in which you operate. For each of the service developments please outline outturn and forecast income, costs and capital expenditure, with forecasts in real terms, on an annual basis and then phased monthly for the working capital forecast. There are five service development sheets. It is not envisaged that an applicant will have more than five major schemes in the forecast period. If you require more then please contact Monitor 29 4.5 PFI (I_PFI and I_PFI (Memo)) 4.5.1 I_PFI sheet. Please outline historic, outturn and forecast PFI specific income and costs on annual basis and phased monthly for the first two years’ after the current year. Unitary payment The forecast unitary charge payable should be provided in real terms. Please provide the total unitary payment as a negative amount and the residual interest as a positive amount. The I&E charge will be sum of the unitary charge and residual interest and the cashflow will take account of the full unitary payment including residual interest. Please also outline if there any release of PFI deferred assets as part of the scheme. 30 4.5.2 I_PFI (Memo) Details provided in the sheet are for INFORMATION only and do not feed into the model. You should be aware that Monitor’s assessors will require this level of detail on all PFI schemes to complete their assessments. Where you have a PFI scheme please outline the I&E and cashflow impacts of the PFI transaction on a standalone basis. 4.6 I_CIP This sheet captures the information related to cost improvement programmes as per assumptions in I_Cost (Base) sheet. The key inputs required relate to providing information about historic CIP targets (recurrent and non-recurrent) and the historical achievement of those targets. 31 Users must also quantify the net financial impacts of any other CIPs which have not automatically been captured within the I_CIP tab. These other CIPs may relate to agency pay cost savings. Please note that this is not in relation to adding further CIPs but simply quantification of items which have not been already captured or identified as being CIPs within the I_income (base) or I_cost (base) tabs. Please enter these in nominal terms on the I_CIP sheet. 4.7 I_Infl Please outline the annual inflation assumptions for the key metrics for the base case and the scenario cases. Inflation assumptions are required for the following: • Payment by Results (PbR) income (i.e. tariff inflation); • Non-PbR income (i.e. non tariff income); • Non-NHS clinical income inflation; • education & training; • research & development; • other income; • pay costs; • drug costs; • clinical supplies and services; • other costs; • unitary charge indexation; and • capex inflation. 4.8 I_Budgt Perf This sheet captures information about budget against actual performance for two historical years’ and the year-to-date position. The year-to-date actual EBITDA achieved as a proportion of the budget will be the calculated figure used for the risk rating metric (EBITDA achieved) in the outturn and first forecast year. For future years this risk rating metric will be assumed to be 100%. 32 4.9 BS_Hist Please outline your historic and year-to-date (YTD) financial statements (I&E, BS and CF). For the cash flow the historic EBITDA will feed from data entered in the base income and cost sheets. Please note the working capital movements in the YTD cashflow are calculated based on movements in the balance from latest year-end. Assumptions for working capital, fixed assets, funding are entered in the I_BSFor sheet. Please ensure that the movements in balance sheet items for the YTD position have been appropriately reflected in the annual assumptions in the income and cost sheets and also in the I_BS For sheet. This is to ensure the cash-flow works appropriately 4.10 BS_For 4.10.1 Working capital balances The calculation methodology and inputs for working capital are split into two different time based assumptions. Monthly assumptions For trade debtors, non-trade debtors, and trade creditors – the working capital balances are derived on the basis of an ageing debt/payment schedule. To calculate debtor balances users are required to enter % assumptions of the cash anticipated to be received in the 30, 60, or 90 days categories. The sum of the inputs must equal 100%. 33 o For assumptions of cash received in 30 days this is multiplied by the current period income. o For assumptions of cash received in 60 days this is multiplied by the previous period income. o For assumptions of cash received in 90 days this is multiplied by the income accrued two periods ago. This methodology applies to trade creditors but this is in terms of cash payment. The key driver for • NHS trade debtors is NHS clinical income; • Non-NHS trade debtors is non-NHS clinical income; • trade creditors is total non pay costs. For all other working capital items please outline the increase/decrease in relevant working capital balances. 34 Annual assumptions NHS and non-NHS trade debtor and trade creditor balances are derived based on assumptions around debtor/creditor day assumptions. For all other working capital items please outline the increase/decrease in relevant working capital balances. 4.10.2 Fixed assets Key assumptions required in this section both on a monthly and annual basis are: • donated additions (please note owned capital additions are captures in the I_Cost (Base) and service development sheets); • capital creditors – (increase)/decrease; • depreciation – owned and donated; Annual Working capital day assumptions 35 • revaluation – owned and donated; • gross book value of disposal – owned and donated; • accumulated depreciation – owned and donated; • cash receipt from asset sales – owned and donated; and • impairments – owned and donated. Transfers from the donated asset reserve are calculated based on the above assumption and are taken to the I&E. 4.10.3 Public Dividend Capital (PDC) dividend The PDC dividend is calculated on the basis of the average net relevant assets multiplied by the dividend rate. To avoid the circularity of the dividend payable in the I&E reserve. The definition of the average net relevant assets in this calculation is: average(PDC balance + revaluation reserve balance + adjustment). 36 Users have the ability to adjust the net relevant assets through an annual input assumption to arrive to the appropriate dividend payment especially in the outturn year, where it is likely that the users know that exact level of the dividend. The annual dividend payable is accrued on a monthly basis based on inputted monthly phasing and users are required to put a 1 against the cash payment dates for the dividend. i.e. in the months of September and March. 4.10.4 Cash balance Please outline the annual interest rate assumptions for overdraft and cash balances. The calculation of interest receivable/(payable) is based on the average cash balance excluding interest receivable/(payable). 4.11 I_NE The sheet calculates the underlying earnings position by removing any nonrecuurrin income/costs as outlined in earlier assumptions. 37 Please profile non-recurrent items in the “I_cost(base)” and the “I_income(base)” pages. These items then automatically transfer to the I_NE page, so no further input is required. Please avoid using the yellow cells in the I_NE as it makes the assessment of the movements in your base position easier for the assessment team. It is also worth noting that for the I&E on the “O_FS” page the model will allocate all of the non-recurrent items on the “I_cost(base)” page against other costs and all non-recurrent items on the “I_income(base)” page against other income. 38 5 Key Outputs Once the relevant input assumptions have been entered – users should then spend some time analysing the outputs to ensure they reflect their business plans appropriately. 5.1 O_FS This presents the I&E, BS and CF annually and monthly 5.1.1 Key ratios The Prudential Borrowing Code (PBC) ratios are calculated and tested against the ratio limits to check if there has been any breach. The limit test in the PBC (maximum debt/asset) ratio is driven off the overall risk rating calculation. 39 Definitions as per model • Long term borrowing = total loans and finance leases. • Total assets = total fixed + current assets + long term debtors. • Revenue available for debt service = EBITDA + interest receivable. • Annual interest = all interest payable on loans and leases. 5.1.2 Risk rating The financial metrics that feed into deriving the financial risk rating are calculated here. The risk rating metrics and definitions can be found in the Compliance Framework available on Monitor’s website, www.monitornhssftgov.uk, and are outlined below. The overall risk rating is calculated in the O_Rating sheet. 5.2 O_Bridge Based on the inputted assumptions – the bridge analysis is constructed i.e. breaking down into variable components that drive the changes in net surplus from one year to the next. For example, on NHS clinical income the breakdown of movement from one year to the next can be broken down into the following: 40 1) PbR gain/(loss); 2) activity growth (based on activity assumptions); 3) case mix/change in unit price (based on the difference between real income growth exc PbR and activity growth); 4) service developments; and 5) inflation. This is summarised in the bridge charts on a short term and long term basis. 5.3 O_KPI This sheet presents and calculates some high level key performance indicators. 41 6 Key checks Once you have completed the model – please check the key outputs to confirm that they represent a reflection of your plan. Throughout the model there are a number of checks – please ensure that you have populated only relevant cells and entered data in the units and sign convention specified. Please check there are no “false” checks showing in the model. Please check to ensure all forecasts have been entered in REAL terms where applicable. For example some information on the CIP and PFI sheets are in nominal terms. In addition there are some high level checks in the “control sheet”. 42 7 Scenario/sensitivities 7.1 S_Activity (Base) This sheet allows users to sensitise activity as outline in the base underlying business for two cases. The activity sensitivity is broken down into elective, non-elective, outpatients, A&E, other) Users determine a sensitivity by entering an increase/decrease in value (or volume) terms in activity for a particular activity and speciality. The income effect is calculated on the basis of the average real price as determined in the I_Income (Base) sheet and cost effect is calculated on the basis of the marginal cost assumptions. 43 7.2 S_Input This sheet allows users to outline any further sensitivities within two cases. These can consist of any income/cost assumptions, capex, disposals and PDC changes and are in REAL terms unless specified. Adjustments to the base case need to be entered both annually and phased monthly for working capital purposes. 7.3 Scenario running Once you have populated the relevant sensitivity sheets (I_Infl, S_Activity(Base), S_Input) you can run scenarios and sensitivities. To run the base case plus the two scenario cases automatically and generate the charts as per the “Control” sheet – you need to click on the ‘Run Scenario’ button 44 If you want to run a scenario in isolation then click on the ‘Scenario’ switch and click “ON” – go to cell M40 and click on the case you would like to run. To run individual sensitivities that make up a scenario: • ensure the scenario switch is set to “OFF”; • click on any of the sensitivities and chose the relevant case you would like to run. For example see below – running a tariff inflation sensitivity; and • you can see the impact of running a sensitivity on the chart. Where case 1 represents a sensitivity on tariff inflation 45 Example list – not exhaustive Non-recurring income and costs – typical items • I&E brokerage received and repaid/offered and reimbursed. • One-off funding (e.g. cancer funding) and awards (e.g. doctor distinction, Department of Health performance fund), primary care trust/strategic health authorities support (e.g. consultant contract, European Working Time Directive etc). Usually this is matched by a non-recurrent cost. Assessors will investigate such items further. • Technical accounting adjustments, e.g. o capital-to-revenue transfers/revenue-to-capital transfers (including capitalisation of expenditure). • Income and expenditure associated with exceptional events, e.g. o non-recurring costs associated with transfer/closure of services/introduction of new services. • Restructuring costs (including redundancy costs). • Exceptional income and expenditure items, e.g. impairments, profit and loss on disposal. • I&E prior year surplus/(deficit) received/repaid (non-cash). • Rates rebase, VAT rebate, back pay adjustments. • Write-off of clinical negligence provisions. Typical items that are not included are: • step change (e.g. recurrent marginal costs and recurrent income associated with step growth or introduction of services); • payment of under-or over-performance on activity below/above negotiated block contract if this amount is included in the baseline for following year; and • non-material items/adjustments. Please note that assessors will investigate and challenge the nature of nonrecuurren items and will agree the final normalised position with you.