d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Note
reference
Concessionary travel for older and disabled people: guidance on
(click to
return to
reimbursing bus operators
relevant
section)
Reimbursement Analysis Tool
(Issued: January 2008)
Introduction
This simple spreadsheet tool is designed to assist Travel Concession Authorities (TCAs) and operators in calculating
reimbursement due from concessionary fares schemes. It should be used in conjunction with the appropriate written DfT
guidance the latest version of which can be found on the DfT website.
As the written guidance makes clear, TCAs may use the methodology of their choice in calculating reimbursement for bus
operators, subject to ensuring that operators are left no better and no worse off. The written guidance, and this analysis tool,
are based on DfT's preferred methodology.
Hyperlinks are provided throughout this tool to assist in its use. Clicking on the relevant note number to the left of the various
rows in the analysis worksheets will bring you back to this instructions sheet and the relevant detailed note. Clicking on the
note reference on this sheet will take you back to the relevant section of the analysis sheet. To the right of the notes on this
page are weblinks taking you to the relevant section of the written guidance on the DfT website.
This tool is in three parts. The Elasticity Analysis tool can be used to estimate a base elasticity based on any observed
change in fares for which there is corresponding data on the change in trips. Typically we would expect TCAs or operators to
use data on the move from half to free fares here, although the methodology will be valid for any observed change.
The Reimbursement Analysis tool can also be used to estimate the reimbursement owing to an operator for a given period.
TCAs have the option of utilising the elasticity derived in the Elasticity Analysis worksheet, or can use an elasticity derived
from another source if they prefer. The Reimbursement Analysis worksheet modifies the elasticity derived from the Elasticity
Analysis worksheet to take account of time elapsed since the date of the observed change used to estimate elasticity
originally.
Page 1 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Also included is a small average fare analysis tool. This can be used to calculate the appropriate weighted average fare for
use in reimbursement estimation. It allows parties to consider which tickets concessionaires would have purchased in the
absence of a scheme and in what proportions. It requires users to make explicit assumptions about the number of trips to
assign to each type of ticket.
Throughout both worksheets, users should only enter data into cells shaded yellow. Cells shaded grey cannot be changed -
they show either the outcome of calculations or hardcoded DfT assumptions.
Elasticity Analysis Tool - Instructions for use
This part of the RAT is designed to help estimate bus passenger elasticity based on historic data. To work correctly users
need data on concessionary trips taken before (Year 1) and after (Year 2) a change in fares. Typically this section could be
used to estimate elasticity using data from before and after the move to free local travel. However it should work equally well
for any change in fares for which there is observed trip data.
The tool requires data on the number of pass holders in each year and the applicable average commercial full fare. Users
will also need to take a view on new passholder trip rates and whether any trips should be added / subtracted from the Year
2 total to account for underlying changes to trip numbers. The tool will work for any area where there is corresponding data
on pass issues and use, including the area served by an individual operator.
Select if you wish to use calendar or financial years. This effects the choices that appear in drop-down menus in the tool and
1
activates the spreadsheet. Ignore any error messages that may appear until you have completed all the yellow boxes.
Enter the years before (Year 1) and after (Year 2) your observed fare change. These dates will then be reflected in prompts
2
throughout the worksheet.
Enter the average number of pass holders for each of Year 1 and Year 2. Use the same basis for the calculation for each
3 year. One basis could be adding the number of passholders at the start and end of each year and dividing by two to give a
reasonable average.
Enter the average commercial full fare in Year 1 that would have applied to concessionary passengers in the absence of a
4 scheme, e.g. an estimate based on a basket of single, return and day passes. The average fare analysis tool can be used
for this calculation.
Page 2 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Enter the average concessionary fare for each of the two years for which data is being used. This is the fare before and after
the observed fare change took place. If analysing data from before and after the move from half to free fares set the Year 2
5
fare to zero. If using an actual rather than a zero fare then the Year 2 fare level should be expressed in nominal rather than
real terms. It will be deflated to provided a real fare change elsewhere in the model.
6 Enter the number of concessionary fare trips recorded in each year.
7 This calculated figure shows the total observed change in trips between Year 1 and Year 2; i.e. Year 2 trips less Year 1 trips.
This, and notes 9 and 10, refer to changes to total trips between Year 1 and Year 2 that can be attributed to factors other
than the change in fares. It is important to strip these effects out so that the change in trips figure used to calculate elasticity
is as close as possible to reflecting only the change in trips that is due to the change in fare. The first such change is an
allowance to reflect the long term trend in concessionary bus patronage. So if there is evidence that even with no change in
8
fare the number of trips would have gone down, we need to add this number of trips back into our estimate of the change in
trips due to the fare change. Accordingly a negative percentage should be entered here if the underlying trend in
concessionary bus patronage is downward. The equivalent number of trips will then be added back to the change in trips
figure to isolate only the effect of the fare change.
See note 8. This allows TCAs to further modify the change in trips figure by allowing for the impact of changes to substitutes
on trips taken. So the opening of a large car park may adversely impact bus patronage and reduce the level of trips. This
9
figure will then need to be added back into the trip change figure. The converse is true if substitutes, such as car travel,
become less attractive and result in an increase in trips. This number will need to be taken out of the change in trips figure.
See notes 8 & 9. Similarly the impact on trip numbers from changes to bus quality (e.g.. more low floor buses), and supply
(e.g.. more buses on a particular route) will also need to be factored in. Enter a positive percentage if there is evidence that
10
changes to bus quality or supply increased the number of trips between years 1 and 2. A negative percentage will imply the
opposite assumption.
This calculated figure shows a revised estimate of the change in trips between year 1 and year 2 to allow for the underlying
11
effects described in notes 8-10.
This is a calculated number giving the existing pass holder trip rate. It is expressed as trips per year and is calculated by
12
dividing the number of Year 1 trips by the number of year 1 pass holders.
Page 3 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
13 This gives the change in pass holders from Year 1 to Year 2.
Enter an assumption about the trip rate of new pass holders (in Year 2) as expressed in terms of trips per passholder. Use
the trip rate of existing passholders in the previous year (note 12) as a starting point and consider the derived percentage of
14
the trip rate of existing pass holders (note 16) as a check. The rate should be looked at closely if it appears that fare
elasticity is particularly high or low.
This calculated figure shows the trip rate of existing passholders in Year 2 that is derived from the assumption about the trip
15
rate of new passholders entered at note 14.
This calculated figure shows the trip rate of new passholders in Year 2 as a percentage of the derived trip rate of existing
passholders in the same year. This figure should be used as a plausibility check for the assumption entered at note 14.
16
Local evidence should be used whereever possible but in general terms this percentage should be in the range of 50-100%
and certainly not exceed 100%.
This calculated figure shows the number of trips that were estimated to have been made in Year 2 by new pass holders. It is
based on the assumption entered at note 14 which is used to derive a new passholder trip rate in terms of trips per
17
passholder. The number of trips assigned to new passholders is based on this assumption and on the number of new
passholders (note 13).
This shows the total adjustment to the change in trips between Year 1 and Year 2 after taking account of underlying impacts
18 (notes 8-10) and trips taken by new passholders. This will generally be a positive number to be subtracted from the Year 2
total because the impact of new passholders tends to be greater than that of other underlying changes.
This gives a revised total number of trips for Year 2 having taken account of both underlying effects and those trips taken by
19 new passholders. This is the figure that will be used for the elasticity calculation and is the 'q2' figure from the elasticity
formula in the written guidance.
20 This gives the estimate of the change in trips after taking account of the adjustments and new pass holders.
21 This expresses the figure at note 20 as a percentage of Year 1 trips.
22 This gives the real change in fare levels by deflating the Year 2 fare (using RPI data) to express it in Year 1 prices.
Page 4 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
This gives the fare elasticity at the commercial fare level that applied in year 1 (as given at note 4). It is calculated using the
23
exponential demand function formula set out in the written guidance.
This gives the fare elasticity at a £1 fare and can be used to compare against the benchmarks given in the guidance and be
24
used as a test for the reasonableness of the new pass holder trip rate assumption (note 14).
Reimbursement Analysis Tool - Instructions for use
The second part of the RAT is designed to help parties estimate the actual reimbursement due to an operator for a given
period. It can be used as a budgeting tool, or to perform calculations based on actual data. To calculate reimbursement it
utilises the full fare elasticity estimated in the Elasticity Analysis tool adjusted to reflect further changes over time. It also
allows for TCAs to use another elasticity figure and to vary some of the key assumptions to exemplify their impact on
reimbursement.
The tool therefore allows for TCAs to generate reimbursement estimates under a variety of assumptions: [i] using elasticity
already derived from the RAT, and key DfT assumptions, [ii] using a RAT-derived elasticity but varying key assumptions on
the basis of local evidence, or [iii] simply inputting a TCA-calculated elasticity.
Select if you wish to use calendar or financial years. This effects the choices that appear in drop-down menus in the tool and
25
activates the spreadsheet. Ignore any error messages that may appear until you have completed all the yellow boxes.
This shows the year used as Year 1 in the initial elasticity analysis from the previous worksheet. This is required to calculate
26
how many years have elapsed since the basis for the elasticity calculation.
Here enter the year for which you want to estimate reimbursement. This year will be used in prompts throughout the tool
27
once entered.
This gives the difference between years at notes 26 and 27 - the number of years elapsed since the initial elasticity
28
calculation.
Enter the average commercial fare that would have applied to concessionary passengers in the absence of a scheme, i.e.
29 based on a basket of tickets. If you wish to use the average fare analysis tool enter its suggested weighted average fare
here.
Page 5 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
This deflates the fare entered in the cell above to Year 1 (i.e. the year of the initial elasticity calculation) prices taking
30
account of retail price inflation. This is to calculate the change in real fares and is based on RPI data.
31 This is the Year 1 fare taken from the earlier elasticity analysis (see note 4).
This shows the calculated change in real fares as a percentage by comparing the deflated fare at note 30, with the fare from
32
the original elasticity calculation (note 31).
33 DfT assumption about retail price inflation beyond 2007/8. Used to compare fares on a real, rather than nominal, basis.
Elasticity will be affected by the change in real fares over time. This assumption determines the extent of that change. TCAs
34
can enter their own assumption in the yellow box.
There is evidence that it takes time for the impact of fare changes to be fully realised. As a consequence elasticities will tend
to increase over time, irrespective of the impact of real fare changes. This assumption determines the extent to which an
35 elasticity derived in the past will increase over the following five years. TCAs can enter their own assumptions in the yellow
box. The evidence suggests that there will be no further increases after five years. TCAs should be cautious in entering
assumptions here. Long term elasticities over -1.2 are considered unlikely.
36 This is the full fare elasticity derived from the Elasticity Analysis tool (note 23).
This shows the elasticity from note 36 modified to take account of the impact of real fare changes. The impact will depend
37 on the assumption entered at note 34. The two calculated figures show the impact of using the DfT and the TCA
assumptions on this.
This further modifies the original elasticity to take account of the long term trend in elasticity. The impact will depend on the
38 assumptions entered at note 35. The two calculated figures show the impact of using the DfT and the TCA assumptions on
this.
This is the calculated elasticity for the year in which reimbursement is being analysed. This figure is the same as given at
39
note 38.
40 This gives the generation factor derived from the elasticity calculated for the year in question.
41 This gives the equivalent reimbursement factor.
Page 6 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
A TCA-originated fare elasticity derived from alternative local or national sources can be entered here that will generate
42
reimbursement factors and generation rates, without using the historic data analysis part of the RAT.
43 This gives the generation factor derived from the TCA elasticity entered above.
44 This gives the equivalent reimbursement factor.
45 Enter forecast or actual recorded concessionary trips for the year in question.
Enter an estimate of additional cost per generated trip in £s. This needs to be determined independently. Default ranges are
46
given in the guidance.
Enter an estimate of the total of other additional costs that are unrelated to generated travel (e.g. implementation costs)
47
based on operator evidence.
This gives the estimate of the number of generated trips based on the data entered above. The three figures calculated are
48 based on (i) using the RAT-derived elasticity and DfT assumptions; (ii) using the RAT-derived elasticity and TCA
assumptions; and (iii) using TCAs own elasticity figure for the year in question.
49 This gives the number of non-generated trips, i.e. trips that would have been made in the absence of a scheme.
This gives the revenue reimbursement due - i.e.an estimate of the revenue that would have been received in the absence of
50
a scheme
51 This gives the total additional cost due, including both that linked to generated trips and other additional costs.
52 This gives the estimate of the total reimbursement due based on data entered above
Average Fare Analysis Tool - Instructions for use
This small spreadsheet tool is designed to assist TCAs and operators in deciding on the most reasonable average fare to
apply to their reimbursement calculations.
To use the tool parties will need to consider all types of ticket which would have been purchased by concessionaires in the
absence of a scheme and make key assumptions about how many trips would have been taken with each ticket purchased.
They will also need to consider the proportion of total trips that would have been taken by concessionaires holding each type
of ticket.
Page 7 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Enter the type of ticket here. Some examples are provided. Operator or survey evidence will be helpful in deciding which
53
tickets to include.
54 Enter the price of each ticket here in £s.
Here enter an assumption about how many trips would typically be taken by holders of this type of ticket. Although this is
55 reasonably obvious for single and return tickets it requires some judgements to be made on the use of pass-type tickets.
Again, hard evidence from operators or surveys will be helpful in deciding what assumptions to make here.
56 This shows a calculated estimate of how much revenue would be generated by each trip using this type of ticket.
Here TCAs should enter assumptions about the proportion of total trips that would have been made by eligible
57
concessionaires in the absence of a scheme using each type of ticket. These must add up to 100%.
This shows a calculated estimate of the revenue that would be generated by each trip using this type of ticket weighted by
58 how many trips would have been taken with this type of ticket based on the assumptions already entered. The weighted
average fare for reimbursement calculations is given at the bottom.
Page 8 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Links to
relevant
section of
guidance on
DfT website
Page 9 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
3.4.10
3.5.1
Page 10 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
3.5.1
3.7
3.4.17
3.4.19
3.4.18
Page 11 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
3.4.14
3.4.14
3.4.9
Page 12 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
3.5.4
Page 13 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
3.4.22
3.4.22
3.4.23
3.4.24
3.4.23
3.4.24
Annex B
3.4.7
Page 14 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
3.7
3.6.1
3.6.4
3.5.1
Page 15 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
3.5.6
3.5.6
3.5.6
3.5.6
Page 16 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Page 17 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Page 18 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Page 19 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Page 20 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Page 21 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Page 22 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Page 23 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Page 24 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Page 25 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Page 26 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Page 27 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Page 28 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Page 29 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Page 30 of 34
d216d2e6-253e-4e12-98c5-8edc7b4797c1.xls
Page 31 of 34
RAT Elasticity Analysis
ELASTICITY ANALYSIS
Notes
Year 1 Year 2
Step 1 - enter key TCA parameters:
1 Select if using calendar or financial years
2 Enter years before (Year 1) and after (Year 2) observed fare change Year List List Actual YearMerged Years RPI Deflator
3 Enter average number of TCA Pass holders for each year (consistent basis) 1985/86 Calendar 1985 1985/86 Year of fare (year 2) #VALUE!
4 Enter average commercial fare (ff) for Year 1 1986/87 Financial 1986 1986/87
5 Enter concessionary fare (f1 and nominal f2) for each year 1987/88 1987 1987/88 Year 1 #VALUE!
6 Enter total concessionary trips made in each year (q1 for Year 1) 1988/89 1988 1988/89 Year 2 index #VALUE!
1989/90 1989 1989/90 Year 1 index #VALUE!
Step 2 - modify change in trips to isolate effect of fare change: 1990/91 1990 1990/91 126.1 9.5 Index increase % of Y1 #VALUE!
7 Total observed change in trips Year 1 to Year 2 1991/92 1991 1991/92 133.5 5.9 year 2 fare nominal 0
8 Enter assumed impact on trips (as a % of y1 trips) from underlying bus travel trend 0 trips 1992/93 1992 1992/93 138.5 3.7 Year 2 fare deflated #VALUE!
9 Enter assumed impact (as a % of y1 trips) from changes to substitutes 0 trips 1993/94 1993 1993/94 140.7 1.6
10 Enter assumed impact (as a % of y1 trips) from changes to bus quality / service 0 trips 1994/95 1994 1994/95 144.1 2.4
11 Modified change in trips Year 1 to Year 2 allowing for these effects 1995/96 1995 1995/96 149.1 3.5
12 Existing passholder trip rate in Year 1 trips per passholder 1996/97 1996 1996/97 152.7 2.4
13 Change in passholders between Year 1 and Year 2 1997/98 1997 1997/98 157.5 3.1
14 Enter assumed new passholder trip rate in Year 2 expressed in terms of trips per passholder trips per passholder 2000/01 1998 1998/99 162.9 3.4
15 Implied trip rate of existing passholders in Year 2 trips per passholder 2001/02 1999 1999/00 165.4 1.5
16 Implied new passholder trip rate in Year 2 as a % of the rate of existing passholders of existing passholder trip rate 2002/03 2000 2000/01 170.3 3.0
17 Trips made by new passholders in Year 2 to subtract from observed change in trips trips 2003/04 2001 2001/02 173.3 1.8
18 Total trips subtracted from Year 2 to isolate effect of fare change trips 2004/05 2002 2002/03 176.2 1.7
2005/06 2003 2003/04 181.3 2.9
Step 3 - consider derived outputs: 2006/07 2004 2004/05 186.7 3.0
19 Year 2 trips adjusted for underlying changes and new passholder trips (q2) 2007/08 2005 2005/06 192.0 2.8
20 Trip change figure for elasticity estimation allowing for passholder and underlying effects trips 2008/09 2006 2006/07 198.1 3.2
21 Trip change as a percentage of Year 1 trips 2009/10 2007 2007/08 203.1 2.5
22 Fare change expressed in real terms (f1 - f2) 2010/11 2008 2008/09 208.1 2.5
2011/12 2009 2009/10 213.3 2.5
23 Estimated average full fare elasticity Year 1 2012/13 2010 2010/11 218.7 2.5
2013/14 2011 2011/12 224.1 2.5
24 Fare elasticity at £1 2014/15 2012 2012/13 229.7 2.5
2015/16 2013 2013/14 235.5 2.5
2016/17 2014 2014/15 241.4 2.5
2017/18 2015 2015/16 247.4 2.5
2018/19 2016 2016/17 253.6 2.5
2019/20 2017 2017/18 259.9 2.5
2020/21 2018 2018/19 266.4 2.5
2021/ 2019 2019/20 273.1 2.5
0 2020 2020/21 279.9 2.5
0 2021 2021/ 286.9 2.5
7:57 AM 12/20/2011
Reimbursement Analysis
REIMBURSEMENT ANALYSIS
Notes
Notes
Step 1 - Estimate elasticity for year in question:
25 Select if using calendar or financial years Key assumptions: DfT TCA
26 Year 1 as used for initial elasticity analysis 33 Inflation assumption (future years) 2.5% 1985/86 Calendar 1985 1985/86
27 Select year for reimbursement estimation 34 Impact of real fares on elasticity 20% 1986/87 Financial 1986 1986/87
28 Elapsed years 35 Long term increase in elasticity: 1987/88 1987 1987/88
29 Enter average commercial fare (nominal) for : Year 1 0% 1988/89 1988 1988/89
30 Real fare equivalent in prices Year 2 25% 1989/90 1989 1989/90
31 Average commercial fare from Year 1 elasticity calculation Year 3 38% 1990/91 1990 1990/91
32 Percentage change in real fares Year 4 44% 1991/92 1991 1991/92
>=Year 5 50% 1992/93 1992 1992/93
Step 2 - Consider derived variables: 1993/94 1993 1993/94
DfT assumptions TCA assumptions 1994/95 1994 1994/95
36 Full fare elasticity in (from elasticity tool note 23) 1995/96 1995 1995/96
37 Elasticity modified for real fare increase 1996/97 1996 1996/97
38 Elasticity modified for long term trend 1997/98 1997 1997/98
39 Estimate of elasticity for : 1998/99 1998 1998/99
40 Derived Generation Factor: TCA derived elasticity: 1999/00 1999 1999/00
41 Derived Reimbursement Factor: 42 Estimate of full fare elasticity for : 2000/01 2000 2000/01
43 Derived Generation Factor: 2001/02 2001 2001/02
Step 3 - Enter details for : 44 Derived Reimbursement Factor: 2002/03 2002 2002/03
45 Total recorded / estimated concessionary trips 2003/04 2003 2003/04
46 Estimate of additional cost per generated trip (£'s) 2004/05 2004 2004/05
47 Estimate of total of other additional costs (£'s) 2005/06 2005 2005/06
2006/07 2006 2006/07
Step 4 - Consider results: RAT-derived RAT-derived TCA-derived 2007/08 2007 2007/08
elasticity / DfT elasticity / TCA elasticity
assumptions assumptions 2008/09 2008 2008/09
48 Generated trips 2009/10 2009 2009/10
49 Non-generated trips 2010/11 2010 2010/11
50 Revenue reimbursement due 2011/12 2011 2011/12
51 Additional cost due 2012/13 2012 2012/13
52 TOTAL REIMBURSEMENT 2013/14 2013 2013/14
2014/15 2014 2014/15
2015/16 2015 2015/16
2016/17 2016 2016/17
2017/18 2017 2017/18
2018/ 2018 2018/
7:57 AM 12/20/2011
RAT - Average Fare Analysis
AVERAGE FARE ANALYSIS
Type of ticket Price £ Assumed trips Implied % of total trips Weighted
per ticket revenue per trip with this ticket revenue per
purchased (£s) type ticket
Notes: 53 54 55 56 57 58
Single (1 mile)
Return (>1 mile)
Daily pass
Weekly pass
Total 0%
Weighted Average Fare: £0.0000
7:57 AM 12/20/2011