Underground Storage Tank Futures Forecast Tool


Underground Storage Tank Futures
Forecast	Too!

U.S. Environmental Protection Agency
Office of Underground Storage Tanks
EPA 510-B-24-002
December 2024

-------
Introduction

The UST Futures Forecast tool from the U.S. Environmental Protection Agency is part of a
response to the Association of State and Territorial Solid Waste Management Officials' February
2023 paper entitled, Sustainability of State Financial Assurance Funds for the Underground
Storage Tank Programs.1 ASTSWMO's report detailed their concerns about converging trends in
the transportation and UST universes as a result of an energy transition, including the potential
for increasing cleanup expenditures at the same time as many state funds experience decreasing
revenues, which may lead them to face financial constraints that can slow their ability to support
cleanup activities. Under some scenarios, state funds could even become insolvent. States may
experience funding challenges for UST/LUST programs if they do not take action to adjust how
they fund their program operations and cleanup programs.

The EPA has not predicted how much petroleum motor fuel will be used over time, and this
forecast tool also makes no such prediction. Rather, like retirement planning and bank stress
tests, the UST Futures Forecast Tool is intended to be used to explore the effects that potential
alternative fuel transition scenarios could have. Exploring alternative future scenarios allows UST
cleanup funds and cleanup programs to be prepared should petroleum motor fuel use decline
over time. While it is possible that a large fraction of UST systems nationally will close in the next
two decades due to declining transportation fuel use, several challenges make it difficult to
predict the pace of the decline in fuel use in the U.S. and the impact the decline will have on state
UST and LUST programs. Policy makers need to consider a range of possible scenarios when
evaluating the ability of their state UST cleanup fund to perform through the energy transition.
The EPA hopes the UST Futures Forecast Tool will help states anticipate challenges and test
potential solutions to state fund solvency concerns by helping them estimate UST corrective
action fund revenues and costs under a variety of declining fuel-use scenarios.

Background

Many variables affect future U.S. fuel demand. Increased vehicle efficiency and vehicle
electrification will reduce fuel demand, but other changes in the transportation industry may
increase fuel demand, offsetting demand reductions in other areas. There is uncertainty over the
rate of impact the variables will have on fuel demand, even if the direction of the impact is clear.
The impacts of some variables such as changes in driver preferences, commuting patterns, and
fleet operations are unknown. These variables may impact the future size of the national vehicle
fleet and the future averages of total U.S. miles driven annually.

1 Association of State and Territorial Solid Waste Management Officials Tanks Subcommittee and State Fund
Financial Responsibility Task Force: 2023 Sustainability of State Financial Assurance Funds for the Underground
Storage Tank Programs. February 2023. https://astswmo.org/2023-sustainabilitv-of-state-financial-assurance-
funds-for-the-underground-storage-tank-programs/.

-------
State funds revenues are currently approximately $700 million annually but are expected to
decline as fuel use declines and fewer USTs remain in operation. This decline will likely happen
gradually, and fuel storage in USTs will remain essential, even if the quantity of storage declines.

At the same time revenues are expected to decline, owners may begin closing the USTs at active
motor fuel facilities and temporarily out of service USTs that are no longer needed or
economically viable due to reduced sales. Closing more service stations could lead to more
release discoveries as many UST releases are discovered when owners assess sites during the UST
closure process. National data are limited, but recent data from closures in California between
January 2017 and December 2022 found releases were discovered at 29.7% of the 546 facilities
that had single wall UST systems closures or upgrades, and at 6.7% of the 1,042 facilities with
double wall UST system closures or upgrades23.

In addition, nationally there are approximately 55,000 confirmed releases with ongoing cleanups.
These 55,000 ongoing corrective action projects are in various stages of completion. Some have
had many years of active remediation and are currently just being monitored, while other
corrective action projects have not progressed past the release confirmation stage.

Most state funds currently operate with relatively modest reserves and do not have the cash
reserves to fund a large number of new cleanup projects. Most of the 35 states with state funds
currently accepting new releases rely significantly on taxes or fees on fuel sales to pay for
cleanups. Declining fuel sales will impact UST and LUST programs that receive funding from fuel
sales taxes or fees. In addition, a reduction in the number of operating UST systems will lead to
a decline in revenue for UST and LUST programs that receive funding from per tank fees. Some
states use per tank fees to partially, or wholly fund cleanups. Some states also use their fund
revenue to pay for other program activities.

2 California UST Leak Prevention: January-December 2022 Annual Report.

https://www.waterboards.ca.gov/ust/leak prevention/docs/epa-evaluations/2022-ian-dec-leak-prevention-
report.pdf

3 California has a deadline to close all single-wall UST systems by December 31, 2025. The state is specifically
tracking and reporting data about the different rates of releases identified at closures between single-wall and
double-wall UST systems in their UST Leak Prevention Reports. The EPA understands this type of information is
currently available from few, if any, other states.

-------
UST Forecast Futures Tool Overview

The primary goal of the tool is to facilitate state evaluation of potential mismatches between
state UST cleanup fund revenues and corrective action costs under a range of energy transition
scenarios. The states can examine the effects of various declining fuel use scenarios on facility
closures, cleanups, and state fund solvency.

The tool is designed to project challenges and test potential solutions to state fund solvency
concerns. The tool is designed to help states estimate the number of release discoveries, state
fund and program funding levels, and the potential number of abandoned sites in potential future
scenarios. The tool allows states to project the impact of different combinations of potential
solutions.

The states are encouraged to run the tool with ranges of assumptions to examine different ranges
of likely outcomes. Where firm values are not available, the tool can be run under a range of
assumptions to examine the range of likely outcomes. The EPA recognizes that specific
projections for many variables related to the changing transportation sector are not available,
especially at the state level, so the EPA has structured the tool as a simple spreadsheet to
facilitate easy and transparent modifications to fit specific program features. The transportation
universe and energy transition will continue to evolve, so states should plan to reevaluate their
situation and forecasts on a regular basis.

States that perform periodic actuarial reviews of their state funds may want to incorporate the
forecast tool into their analysis of future performance.

Data Input and Calculations

The tool is a spreadsheet with five worksheets - (1) Data Entry, (2) Fuel Use, Tanks, & Sites, (3),
Releases & Abandons (4) Corrective Action Costs, (5) Funding & Surplus. Details on the five
worksheets are described below. Data input is made in the first worksheet, and the projection
calculations are made in the subsequent four worksheets. The calculation worksheets can be
modified by the User. Rows can be added or subtracted to increase or decrease the number of
years and formulas can be modified to change the calculations to fit specific program features.

The calculations begin with fuel use, which drives fund revenue and how many USTs are needed.
Fund revenue and the changes in UST population are then combined with other variables to
project annual UST fees, UST removals, release discoveries, corrective action costs, and
ultimately fund surpluses or deficits. The calculation flow is shown graphically in Figure 1.

-------
Figure 1. Calculation Flow

Forecasting Cleanups, Revenue, and Deficits

Cleanup
Backlog

Cleanups
1

Fuel _
Use

Tax or
Fee

Reserves

Storage
Capacity

Release
Rates

* USTs —~

Annual
Fees

Fund Revenue

Discovery
Rate

• Closing
¦ USTs

Cost per
Cleanup

Cleanup
Expenses

Ded's& Exp's-

Abandoned
Sites

Surplus

or
Deficit

The calculations in the tool are easily modified so states can adapt it to fit their unique structures.
The tool includes charts that summarize the results for presentation to management, legislators,
and stakeholders. For example, this chart shows revenue projections for a fund.

Fund Revenue, ($millions)

$60 -|

$50 -
$40 -
$30 -
$20 -
$10 -
$0 -

0(N^rtDooorM^tDooorM^rtDooorM^rtDooo
rM
-------
Worksheet #1 -- Data Entry Worksheet

The Data Entry worksheet is found in the first tab. Values from the Data Entry worksheet are
used in calculations on the remaining worksheets in this spreadsheet. The input data on this
worksheet and the formulas they feed can be replaced with specific values on the calculation
worksheets that follow this sheet.

The data on this sheet is divided into four areas, corresponding to the remaining four worksheets.
Many of the cells in the Data Entry worksheet have comments (signified by the red triangle in the
upper right of the cell) that explain the purpose of the data and include suggestions on how to
establish values. Not all the data is needed to drive the model. For example, if the User does not
want to analyze the temporarily out of service USTs, they can leave that field blank.

Data Entry Worksheet, Data Input Area #1.

Tab 1: Fuel Use Decline, Number of Tanks, & Number of

Sites

Fuel Use Decline Scenarios

General Data

Straight Line Decline

Logistic Decline

Starting Year

2020

Yes/No (blank)

Yes/No (bl

YES

# Years

End fuel use (mb/d)

Initial Fuel Use (mb/d)

0.19

0.47

1. Data Input Area #1. Declining fuel use scenario and Initial Number of Active USTs and Sites.

The data from this section is linked to the second worksheet (Fuel Use, Tanks, & Sites) where
the calculations are performed.

a. There are two types of decline curves available in the model: straight line decline and
logistic decline curve, which is "S" shaped. The specifics of the curve can be adjusted
by selecting an end date for the transition and the shape of the decline curve.

i. Starting Year. This is the year at which the forecast begins. For presentation
of results the User may want to begin at peak motor fuel use for their state.
2020 was the peak motor fuel use for the U.S., overall.

ii. # Years. This is the number of years in the decline of fuel use. The User can
change this value, but if a longer time period is selected the calculation sheets
will need added rows.

iii. Initial Fuel Use (mb/d). This is the starting point for fuel use in millions of
barrels per day. State fuel use data should be available from the State Fund's
revenue calculation.

iv. Straight Line Decline. This is the simplest fuel use forecast method.

-------
1. Yes/No (blank). This toggles which fuel use decline formula to use.
Enter "YES" if using straight line decline; otherwise leave blank.

2. End fuel use (mb/d). This is the daily fuel use at the end of the forecast
period, selected above.

v. Logistic Decline. This formula creates a fuel use decline curve that is analogous
to many product sales over time curves. It is shaped like a backwards "S." The
fuel use decline starts slowly and increases each year until the inflection point,
when the rate of decline begins to taper.

1. Yes/No (blank). As with the Straight Line Decline, this toggles which
fuel use decline formula to use. Enter "YES" if using the Logistic Decline;
otherwise leave blank.

2. xo. This is the number of time periods to the inflection point.

3. k. This controls the slope of the decline curve and must be greater than
zero.

b. The data for the number of active USTs and sites includes:

i. Initial # Active USTs. Use data from state database. Does not include
Temporarily Out of Service or Closed sites. TOS is an abbreviation for
Temporarily Out of Service.

ii. #USTs/site.

iii. Initial # Active Sites does not include TOS or Closed sites.

iv. Avg. capacity of sites increases X% over Y years.

v. # sites closed and replaced by new sites X%/year.

vi. Initial # TOS sites.

vii. # TOS sites closed X%/year for Y years.

2. Data Input Area #2. The second data input area, Number of Releases and Abandoned Sites

focuses on release discovery, the number of sites in or entering corrective action, and the
number of sites that become abandoned. The data from this section is linked to the third
worksheet (Releases and Abandons) where the calculations are performed. The data in this
section includes:

a. Initial fraction of sites where new releases are discovered at closure and Final
fraction of sites where new release discovered at closure - X% and Y years. These
two sets of data allow the User to model the release discovery rate and its expected
decline as older single walled USTs being replaced with double walled USTs, which
have fewer new releases. National data are limited, but recent data from closures in
California between January 2017 and December 2022 found releases were discovered
at 29.7% of the 546 facilities that had single wall UST systems closures or upgrades,

-------
and at 6.7% of the 1,042 facilities with double wall UST system closures or upgrades45.
Make the initial and final fractions equal to keep the release discovery rate constant.

b. Initial # Backlog release sites, %
currently in ongoing cleanup and % in
MNA that will be closed. The backlog
sites that are already in corrective action
are included in the first cleanup year.

The MNA sites are given a different cost
structure. And the remaining fraction of
backlog sites are assumed to be stalled
at the beginning of the corrective action
process and will be addressed overtime.

Make the % in MNA that will be closed
0 if the User wants to apply the same
cleanup costs to all sites.

c. # years over which backlog site
cleanups are initiated. This data is used
to forecast when the corrective actions
of stalled, backlogged sites are
restarted. Make this a larger number if
expecting the backlogged sites to be
addressed over a longer period of time
and copy and paste the formulas on the Backlog to CA column on the Releases &
Abandons worksheet to extend out the years in which the corrective actions for back
logged sites are reinitiated.

d. # years over which backlog MNA site cleanups are closed. The model is set up to

show an annual expenditure for the MNA sites until the corrective actions are closed.
Make this a large number if expecting the MNA sites to remain open indefinitely and
copy and paste the formulas on the MNA columns on the Releases & Abandons and
Corrective Action Costs worksheets to extend out the years of payments for
monitoring costs.

e. % sites w/ release discovery w/ USTs repaired or replaced - %/year. This factor is to
create corrective action sites that continue to be service stations even after a release
is discovered during repair or replacement of their USTs.

4 California UST Leak Prevention: January-December 2022 Annual Report.

https://www.waterboards.ca.gov/ust/leak prevention/docs/epa-evaluations/2022-ian-dec-leak-prevention-
report.pdf.

5 California has a deadline to close all single-wall UST systems by December 31, 2025. The state is specifically
tracking and reporting data about the different rates of releases identified at closures between single-wall and
double-wall UST systems in their UST Leak Prevention Reports. The EPA understands this type of information is
currently available from few, if any, other states.

Tab 2: Number of Releases and Abandoned Sites

Initial fraction of sites
where new release
discovered at closure

23.6%

Final fraction of sites
where new release
discovered at closure
X% and Y years

7.8%

Initial # Backlog
release sites - #, %
currenty in ongoing
cleanup, and % in
MNA that will be
c1osed

420

10%

50%

# years over which
backlog site cleanups
are initiated

# years over which
backlog MNA site
cleanups are closed

% sites w/ release
discovery w/ USTs
repaired or replaced -
%/year

1.0%

% of Total Sites to
Corrective Action that
become abandoned

10%

-------
f. % of Total Sites to Corrective Action that become abandoned. It is expected that
abandonment will become more common as fuel use declines because many of the
less economically viable service stations are on smaller parcels, with fewer future use
options. The forecast includes Corrective Action Costs for Abandoned sites. Modify
the formula in column B of the Corrective Action Costs worksheet to remove
abandoned sites from the calculation if Abandoned Sites should be excluded from
Corrective Action costs. Make the % of Total Sites to Corrective Action that become
abandoned 0 if the User does not want to estimate abandoned sites.

3. Data Input Area #3. The third area of data input is Corrective Action Costs. The parameters
here establish the corrective action costs and the time over which the corrective actions take
place. While every site is unique, this forecasting tool uses average costs and time frames.
The data from this section is linked to the fourth worksheet {Corrective Action Costs) where
the calculations are performed.

Tab 3: Corrective Action Costs

Average Cost to
Complete the
Corrective Action; %_
of National Average

$212,222

150%

$141,481

$ spent in first X years

50%

Years to complete
cleanup

Annual MNA cost

$20,000

The data in this section includes:

a. Average Cost to Complete the Corrective Action; % of National Average. Two

methods for establishing average corrective action costs are allowed here. The User
can either express the corrective action costs as an average value for the state or as a
% of the national average cost from the ASTSWMO survey.

b. $ spent in first X years. This factor establishes the fraction of costs that are expended
in the first years of the corrective action. The remaining portion of the corrective
action costs are distributed evenly over the Years to complete cleanup, which is the
next data entry.

c. Years to complete cleanup. This is the average timeframe to complete the corrective
action.

d. Annual MNA cost. These are the annual monitoring costs for MNA projects. The MNA
cost formula in the Corrective Action Costs worksheets would have to be modified if
the User wishes to have a cost for a separate closure report.

Some funds maintain estimates of the ultimate cost of each ongoing corrective action project.

The spreadsheet could be modified to use the fund's estimates of remaining costs for

-------
individual sites, and the above calculations could be applied to future release discoveries and
to sites for which future cleanup costs have not been estimated.

4. Data Input Area #4. The fourth area of the data entry worksheet is Funding & Surplus. This
data encompasses the income and constraints on the fund, including:

a. Initial Surplus in State Fund ($ millions).

b. Tax or fee on the quantity of fuel ($/gal). The User
could model scenarios where fuel taxes or fees change
over time by directly entering the fees in the Funding
& Surplus worksheet.

c. Annual fee on each tank in service.

d. Reductions due to Fund Cap, or Legislative or
Executive action. The User should summarize in the
Data Entry worksheet the cap or legislative action that
is being modeled and create a formula to reflect the
constraint in the Funding & Surplus worksheet. In some
cases, such as fund revenue caps, the funding
constraints can be expressed as a logical formula. In
other cases, the User will need to modify the values in

e. Other Expenses e.g. transfers out, staff, 3rd party claims ($ millions). The User could
model scenarios where staff expenses change over time by directly entering the
charges in the Funding & Surplus worksheet or by creating a formula for staff expenses
that is tied to the number of corrective actions.

Tab 4: Funding & Surplus

Initial Surplus in State
Fund ($ millions)

$0,100

tax or fee on the
quantity of fuel ($/gal)

$0.0272

Annual fee on each
tank in service

$250

Reductions due to
Fund Cap, or
Legislative or
Executive action
($millions)

manual
adjust
columns Fl
and 1 on
"Funding &
Surplus" tab

Other Expenses eg
transfers out, staff,
3rd pty claims
($millions)

$19

the spreadsheet manually.

-------
Worksheets Two through Five - Calculation Worksheets

Values from the Data Entry worksheet are used in calculations on worksheets two through five.
The cells that refer back to the Data Entry worksheet are highlighted in yellow. The calculation
sequence is left to right, and top to bottom. The User can add or subtract rows to change the
time frame of the forecast. If adding rows, the last cell in each row with a formula needs to be
copied and pasted down forthe additional years. The calculations are based on two-year intervals
to allow the calculations to fit on a single screen. The User can modify the formulas or replace
them with specific data. Various charts are included on each worksheet to help visualize the data
and calculations of the worksheet.

Worksheet #2 - Fuel Use, Tanks & Sites.

Phase in

years

Storage

tank capacity needed

# UST Sites

# USTs

TOS Sites

tt Active

tt Sites

Cumulative

Initial

Fraction of

tt TOS

Total#

Motor

Initial tt

SitesJFuel

UST

Retired

Closed and

tt UST

tt Active

Initial TOS

TOS

Sites

Fuel Use

Active

Used

Sites

from Less

Replaced w/

Sites

USTs

TOS

Sites

Remainin

Sites

Year

(mtWd)

Sites

(Sites/mb/d)

Needed

Need

New Sites

Closed

1 Site

Needed

Sites

Closed

Cosed

Closed

2020

0.18943

3,026

15,972

3,026

113

2.8

8,472

200

5.0%

190

123

2022

0.18671

15,762

2,943

206

8,241

5.0%

180

103

2024

0.18512

15,552

2,879

312

8,061

5.0%

170

116

| 2026

0.18263

15,342

2,802

437

7,845

5.0%

160

135

2028

0.17877

15,131

2,705

125

589

7,575

5.0%

150

162

2030

0.17294

14,921

2,580

163

777

7,226

5.0%

140

198

2032

0.18435

14,711

2,418

210

1,011

6,770

5.0%

130

244

2034

0.15228

14,501

2,208

261

1,295

6,182

5.0%

120

293

203G

0.13622

14,291

1,947

305

1,619

5,451

5.0%

110

335

2038

0.11657

14,081

1,641

328

1,963

4,596

5.0%

100

354

2040

0.09471

13,871

1,314

318

2,295

3,679

5.0%

342

2042

0.07286

13,660

995

280

2,584

2,787

5.0%

300

2044

0.05321

13,450

716

224

2,815

2,004

5.0%

241

2046

0.03717

13,240

492

165

2,985

1,378

5.0%

180

2048

0.02508

13,030

327

115

3,104

915

5.0%

129

ZUbU

O.U164y

12,82U

Zll

((

3,183

b92

b.0%

2052

0.01066

12,610

134

3,235

376

5.0%

2054

0.00680

12,399

3,267

236

5.0%

2056

0.00431

12,189

0.5

3,288

147

5.0%

2058

0.00272

11,979

0.3

3,301

5.0%

2060

0.00171

11,769

0.2

3,321

2062

2064

2066

2068

2070

Total

3,026

295

100.0%

200

3,521

The second worksheet, Fuel Use, Tanks, & Sites is where the calculations begin. Calculations
included on this worksheet are listed and described below.

• The Motor Fuel Use column calculates the decline in motor fuel use in millions of barrels per
day (mb/d) based on which formula and parameters are selected in the Data Entry sheet.

• Initial # Active Sites (does not include TOS or Closed sites).

• # Active Sites/Fuel Used (Sites/mb/d) is calculated from the Initial Fuel Use and the Initial #
Tanks to determine how many sites are needed to provide the storage capacity needed for
fuel dispensing. As fuel use declines the number of sites needed should also decline. This

-------
factor is further modified by the capacity formula (Avg capacity of sites increases X% over Y
years) to account for new stations have larger UST capacities and greater monthly sales.

• # Active UST Sites Needed. This is a multiplication of the # Active Sites/Fuel Used and Motor
Fuel Use. The number of needed service stations declines with fuel use and increased
capacity of newer stations.

• # Sites Retired from Less Need. This is the difference between the number of USTs needed
from one time period to the next

• # Sites Closed and Replaced by New Sites. This is calculated from the # sites closed and
replaced by new sites X%/year from the Data Entry worksheet.

• Cumulative # Sites Closed. This total number of Sites closed from the beginning of the
forecast.

• # USTs/site. This is from the Data Entry sheet.

• # Active USTs Needed is the # Active UST Sites Needed multiplied by the # USTs/site.

• Initial # TOS Sites is the number of sites with TOS USTs at the beginning of the forecast. It is
assumed that none of these sites go back into service, and that they will be closed over time.

• Fraction of Initial TOS Sites Closed Each Year. This is taken from the Data Entry worksheet.

• #TOS Sites Closed. This is the Initial #TOS Sites multiplied by the Fraction of Initial TOS Sites
Closed Each Year.

• # TOS Sites Remaining. This will be used in calculating the total number of USTs in the fund
revenue calculations.

• Total # Sites Closed is the sum of the # Sites Retired from Less Need, # Sites Closed and
Replaced by New Sites, and # TOS Sites Closed.

-------
Worksheet #3, Releases & Abandons

The third worksheet, Releases & Abandons, builds on the calculations in the Fuel Use, Tanks, &

Sites worksheet to forecast the number of releases that enter Corrective Action and the number

of sites that become abandoned.

• Backlog to CA is based on the data
components: the sites that are in
active Corrective Action at the
beginning of the forecast and stalled
sites that are restarted and added to
Corrective Action.

• # Sites in MNA That Will Close is

based on the data in the Data Entry
worksheet and is calculated from the
initial number of sites in monitored
natural attenuation and decreases
over time as the corrective actions are
closed. The MNA sites that are not
expected to close should be included
in ongoing Corrective Action cost
calculations.

• Total # Sites Closed is from the Fuel
Use, Tanks, & Sites worksheet.

• Fraction of sites where new release discovered at closure is a calculation based on the Initial
fraction of sites where new release discovered at closure, and the Final fraction of sites
where new release discovered at closure from the Data Entry worksheet. The calculation is
to forecast the decline over time in the fraction of sites where releases are discovered at
closure. Discovery at Closure is the multiplication of Total # Sites Closed and Fraction of sites
where new release discovered at closure.

• # Releases Discovery w/ UST Repair or Replace is the multiplication of the # UST Sites
Needed and the fraction of sites that are expected to have releases discovered during their
operating life (# sites w/ release discovery w/ USTs repaired or replaced on the Data Entry
worksheet).

• Total # Sites to Corrective Action is the sum of Backlog to CA, Discovery at Closure , and #
Releases Discovery w/ UST Repair or Replace

• Abandoned Sites that Need Corrective Action is the multiplication of the sum of Total # Sites
to Corrective Action plus Backlog in MNA by % of Total Sites to Corrective Action that
become abandoned from the Data Entry worksheet. This forecast does not include the sites
that do not require Corrective Action that become abandoned.

in the Data Entry worksheet and comprises two

Sites w/ releases confirmed at UST

closure

Fraction of Sites

# Releases

Abandoned

# Sites in

Where New

Discovery

Total #

Sites that

MNA

Total #

Release

w/UST

Sites to

Need

Backlog

That Will

Sites

Discovered at

Discovery

Repair or

Corrective

Year

toCA

Closed

Closure

at Closure

Replace

Action

2020

210

123

24%

130

2022

189

103

23%

2024

168

116

22%

202G

147

135

21%

2028

126

162

20%

2030

105

198

20%

2032

244

19%

101

2034

293

18%

103

203G

335

17%

102

2038

354

16%

2040

342

16%

2042

300

15%

2044

241

14%

204G

180

13%

2048

129

13%

2050

12%

2052

11%

2054

10%

205G

2058

20G0

20G2

20G4

20GG

20G8

2070

Tolal

210

3,521

594

530

1,334

133

-------
Worksheet #4, Corrective Action Costs

The fourth worksheet, Corrective Action Costs, combines the calculations in the prior worksheets
to forecast costs from Newly Initiated or On-going Corrective Action Projects and MNA Projects
that are expected to close during the projection. It includes the calculations described below.

Costs by Year ($ millions)

Newly Initiated or On-qoinq Corrective Action Projects

MNA Projects

Total Costs

Total it
Sites to

Cost to
Complete

the
Corrective

Action
Cost

Corrective Action Costs for Each Period

New
and On-

Sites

MNA

Costs

Perio
d

Period

Cumulat

Year
2020

Correctiv
e Action
130

Spread
Over Time

2020

2022

tttt#

2026

2028

2030

2032

2034

tttt#

2038

2040

tttt tttt

«#

tttt

«#

It#

tttt

tttt tttt

Going
CA

MNA
That

per
Site

MNA

Costs

CA
Costs

ive CA
Costs

$ 212,222

$106,111

13.8

$13.8

210

$20,000

$4.2

$18.0

$18

2022

$21,222

2.8

9.8

$12.6

189

$3.8

$16.3

$34

2024

$21,222

2.8

2.0

9.8

$14.6

168

$3.4

$17.9

$52

2026

$21,222

2.8

2.0

10.0

$16.6

147

$2.9

$19.6

$72

2028

$21,222

2.8

2.0

10.2

$18.8

126

$2.5

$21.4

$93

2030

$21,222

2.8

2.0

10.5

$21.2

105

$2.1

$23.3

$116

2032

101

2.0

2.1

10.8

$20.8

$1.7

$22.5

$139

2034

103

2.0

2.1

2.2

10.9

$21.2

$1.3

$22.4

$161

2036

102

2.0

2.1

2.2

10.8

$21.3

$0.8

$22.1

$184

2038

2.0

2.1

2.2

10.2

$20.8

$0.4

$21.2

$205

2040

2.1

2.2

2.0

9.0

$19.6

$224

2042

2.2

2.0

1.8

7.4

$17.8

$242

2044

2.2

2.0

1.8

1.5 5.8

$15.5

$15.5 $258

2046

2.2

2.0

1.8

1.5 1.2

4.4

$13.1

$271

2048

2.0

1.8

1.5 1.2

0.9

3.3

$10.7

$281

2050

1.8

1.5 1.2

0.9

0.7

2.G

$8.6

$290

2052

1.5 1.2

0.9

0.7

0.5

0.9

$5.6

$296

2054

1.2

0.9

0.7

0.5

0.2

0.6

$4.0

$300

2056

0.9

0.7

0.5

0.2

0.1

0.4

$2.7

$302

2058

0.7

0.5

0.2

0.1

0.3

$1.8

$304

2060

0.5

0.2

0.1

0.1 0.2

$1.1

$305

2062

0.2

0.1

0.1 0.0

$0.5

$306

2064

0.1

0.1 0.0

$0.3

$306

2066

0.1

0.1 0.0

$0.2

$306

2068

0.1 0.0

$0.1

$306

2070

0.0

$0.0

$306

Tolal

$ 212,222

$283

$23

$306

• Total # Sites to Corrective Action. This is from the third worksheet, Releases & Abandons and
is the number of new and on-going Corrective Action sites.

• Average Cost to Complete the Corrective Action. This is from the Data Entry worksheet.

• Corrective Action Cost Spread Over Time. This is from the Data Entry worksheet. The User
can add more rows to this calculation to increase the average length of the Corrective Action.

• Corrective Action Costs for Each Period. The Total # Sites to Corrective Action and the
Corrective Action Cost Spread Over Time are multiplied in each of these columns to project
corrective action costs that initiate in each period. The User can add more columns and add
rows for longer projection periods.

• Total New and On-Going CA Costs. This column totals the Corrective Action Costs for Each
Period.

• # Sites in MNA That Will Close. This is the number of ongoing MNA projects from the third
worksheet, Releases & Abandons.

• Annual MNA Costs per Site. This is the average annual costs of the MNA sites from the Data
Entry worksheet.

• MNA Costs. This is the multiplication of the MNA Projects and the Annual MNA Costs per
Site.

• CA Costs. This is the sum of Total New and On-Going CA Costs and MNA Costs.

• Cumulative CA Costs. This is the cumulative sum of CA Costs.

-------
Worksheet #5 - Funding & Surplus

The fifth worksheet, Funding & Surplus, brings the calculations in the prior worksheets together
to compare the Corrective Action Costs with the State Fund Revenues. The calculations in this
worksheet are divided into three sections: (1) Revenue, (2) Reductions to Income and Expenses,
and (3) Periodic and Cumulative Surplus or Shortfalls.

Revenue ($ millions)

Reductions to Income a

nd Expenses ($ millions)

Periodic

Cumulative

Initial

Tax or

Revenue

Fee on

Reductions

due to Fund Cap, or

Net Income for

Surplus

Fee on

from Tax

Each

Revenue

UST Corrective

Period

Cumulative

in State

Fuel

or Fee on

UST

from Fee

Total Gross

Legislative or

Net

Other

Action

Period

surplus or

Cumulative

surplus or

Year

Fund

(S/gal)

Fuel

($/UST)

on USTs

Income

Executive action

Income

Expenses

CA Costs

(shortfall)

CA Costs

(shortfall)

1 2020

0.1

$0.02720

158

$250

4.3

162

$30m annual cap

601

2022

$0.02720

156

$250

4.2

160

$30m annual cap

2024

$0.02720

154

$250

4.1

158

$30m annual cap

2026

$0.02720

152

$250

4.0

156

$30m annual cap

2028

$0.02720

149

$250

3.9

153

$30m annual cap

2030

$0.02720

144

$250

3.7

148

$30m annual cap

(1)

116

2032

$0.02720

137

$250

3.5

141

$30m annual cap

(0)

139

2034

$0.02720

127

$250

3.2

130

$30m annual cap

(0)

161

2036

$0.02720

114

$250

2.8

116

$30m annual cap

(0)

184

2038

$0.02720

$250

2.3

100

$30m annual cap

205

2040

$0.02720

$250

1.9

$30m annual cap

224

2042

$0.02720

$250

1.4

$30m annual cap

242

2044

$0.02720

$250

1.0

$30m annual cap

258

2046

$0.02720

$250

0.7

(1)

271

2048

$0.02720

$250

0.5

(3)

281

2050

$0.02720

$250

0.3

(3)

290

2052

$0.02720

$250

0.2

(2)

296

2054

$0.02720

$250

0.1

(2)

300

2056

$0.02720

$250

0.1

(1)

302

2058

$0.02720

$250

0.0

(1)

304

2060

$0.02720

$250

0.0

(1)

305

2062

$0.02720

$250

(0.5)

306

2064

$0.02720

$250

(0.3)

306

2066

$0.02720

$250

(0.2)

306

2068

$0.02720

$250

(0.1)

306

2070

$0.02720

$250

(0.0)

306

Total

1,660

306

1. Revenue

a. Initial Surplus in State Fund. This is the fund surplus at the start of the projection
from the Data Entry worksheet.

b. Tax or Fee on Fuel ($/gal). This is the per gallon tax or fee that funds the state fund
from the Data Entry worksheet. This column can be modified to include changes in
the Tax or Fee on Fuel over time.

c. Revenue from Tax or Fee on Fuel. This is the multiplication of the Motor Fuel Use

from the second worksheet, Fuel Use, Tanks, & Sites.

d. Fee on Each UST ($/UST). This is the per tank fee or tax that some state funds collect
to add to their revenue from the Data Entry worksheet.

e. Revenue from Fee on USTs. This is the multiplication of the sum of the # Active USTs
Needed and # TOS Sites Remaining from the second worksheet by the Fee on Each
UST.

f. Total Gross Income. This is the sum of Revenue from Tax or Fee on Fuel and Revenue

from Fee on USTs and represents the total amount of revenue raised by taxes and
fees for the fund.

-------
2. Reductions to Income and Expenses

a. Reductions due to Fund Cap, or Legislative or Executive Action. This is a description
of any caps or reduction that reduce the revenue received by the state fund.

b. Net Income. This is the logical expression or formula that represent the reductions
described in the previous column.

c. Other Expenses. These are expenses that the fund incurs such as salaries, rent, and
inspection fees. The formula in this column calculates the Other Expenses to be
proportionate to the initial Other Expenses and the initial Net Income. Other
Expenses decrease as Net Income falls.

d. Net Income for UST Corrective Action. This is the subtraction of Other Expenses from
Net Income and represents the revenue available to support Corrective Action costs.

3. Periodic and Cumulative Surplus or Shortfalls

a. Period CA Costs. These are the costs by period from the fourth worksheet, Corrective
Action Costs.

b. Annual surplus or (shortfall). This is the subtraction of the Period CA Costs from the
Net Income for UST Corrective Action.

c. Cumulative CA Costs. These are the cumulative costs from the fourth worksheet,
Corrective Action Costs.

d. Cumulative surplus or (shortfall). This is the cumulative surplus or shortfall, including
the Initial Surplus in State Fund.

Questions and Suggestions

We hope you find the Forecasting Tool useful. If you have questions on how to use or modify it,

or suggestions to improve it please contact Tom Schruben (Schruben.thomas@epa.gov). Some

prompting questions are below, but any feedback is welcome.

• Does the tool provide insights that are beneficial to your program?

• Does the tool provide the graphics you need to explain the concerns and potential solutions
to your stakeholders?

• What factors have we not considered or included, that we should?

• How can we improve the tool?

• What other outcomes should we attempt to derive that could be helpful to your program?

• Do you foresee your program using this tool on a periodic basis?

• How difficult is it to develop the factors, and the ranges of factors that drive the forecasts?

• Did you modify any of the formulas to make the analysis better fit your program?

• Who used the tool - state program staff, a consultant, or an actuary?

• Does the tool need to forecast from the actual UST population in the state data base?

• Does the tool need to be able to run scenarios where the oldest stations are closed in order
of UST age, UST capacity, or the number of walls?

• Are the supporting materials sufficient?

-------