OCS Study

MMS - 2001 - 017

Forecasting Environmental and Social

Externalities Associated with OCS Oil and Gas

Development: The Offshore Environmental

Cost Model

Volume 1: Model Methodology, Documentation,

and Sample Output

April 2001

MMS U.S. Department of the Interior

Minerals Management Service

Environmental Division
OCS Study

MMS - 2001 - 017

Forecasting Environmental and Social

Externalities Associated with OCS Oil and Gas

Development: The Offshore Environmental

Cost Model

Volume 1: Model Methodology, Documentation,

and Sample Output

Author

Jason R. Plater

Project Manager

William W. Wade

Energy & Water Economics, Inc.

810 Walker St.

Columbia, TN 38401

for

Minerals Management Service

Environmental Division

Prepared under MMS Contract

1435-01-99-CT-30996

Published by

U.S. Department of the Interior

Minerals Management Service

Herndon, Virginia April 2001

Volume 1: Offshore Environmental Cost Model iii

OECM Volume 1.doc 05/22/01 2:35 PM

DISCLAIMER

This report was prepared under contract between the Minerals Management Service (MMS) and

Foster Associates, Inc. This report has been technically reviewed by the MMS and approved for

publication. Approval does not signify that the contents necessarily reflect the views and

policies of the MMS, nor does mention of trade names or commercial products constitute

endorsement or recommendation for use. It is, however, exempt from review and compliance

with MMS editorial standards.

REPORT AVAILABILITY

Extra copies of the report may be obtained from:

Environmental Studies Branch

U.S. Department of the Interior

Minerals Management Service

381 Elden Street (MS 4041)

Herndon, VA 20170-4817

Telephone: 703-787-1717

CITATION

Suggested citation:

Plater, J.R and William W. Wade. 2001. Forecasting Environmental and Social Externalities

Associated with OCS Oil and Gas Development: The Offshore Environmental Cost Model,

Volume 1: Model Methodology, Documentation, and Sample Output, MMS OCS Study

2001-017. U.S. Dept. of the Interior, Minerals Management Service, Environmental Studies

Branch, Herndon, VA. 76 pp.

Volume 1: Offshore Environmental Cost Model v

OECM Volume 1.doc 05/22/01 2:35 PM

ABSTRACT

The Minerals Management Service (MMS) is required by Congress to balance benefits

associated with offshore oil and gas development with corresponding environmental and social

costs. In order to estimate these costs for the 2002 - 2007 5-Year Leasing Program, MMS

commissioned an update to its existing General Purpose Environmental Cost Model (GPECM)

developed in 1991. The revised model, called the Offshore Environmental Cost Model (OECM),

makes significant improvements in usability and flexibility over the 1991 GPECM model. Core

model parameters such as production, installation and removal of offshore facilities, and per-unit

environmental and social costs may all be specified by the user. The model, which runs on any

PC running Microsoft Excel 97 or later, is capable of forecasting environmental and social costs

associated with OCS exploration and development for a period of 55 years. Environmental

benefits associated with decreases in imported oil due to OCS production are also estimated.

The net social and environmental costs from the OECM are combined with estimates of private

benefits calculated in MMS' Net Economic Value (NEV) model and estimates of consumer

surplus benefits from the Market Simulation Model to arrive at net benefits from OCS

production.

The model’s documentation is divided into two volumes. The first describes the structure and

use of the spreadsheet model. The second volume documents the research on environmental and

social costs that led to the cost coefficients used in the model including a description of all

assumptions, data sources, methodology, and economic damage drivers.

Volume 1: Offshore Environmental Cost Model vii

OECM Volume 1.doc 05/22/01 2:35 PM

 ACKNOWLEDGEMENTS

Foster Associates received substantial support for this project. Many people provided data and

factual information. The following people and organizations were especially helpful in

supplying information and resources, and in some cases, reviewing subsections. Without their

contributions, this report could not have been written. Errors that remain are the authors'.

Cheryl Anderson, Minerals Management Service

Dr. Rodney Cluck, Minerals Management Service

Deborah French, Applied Science Associates, Inc.

Steve Hampton, California Department of Fish and Game

Douglas Helton, National Oceanic and Atmospheric Administration

Brian Julius, National Oceanic and Atmospheric Administration

Dr. William King, Minerals Management Service

Vernon Leeworthy, National Oceanic and Atmospheric Administration

Dr. Larry Leistritz, U. of North Dakota Dept of Agricultural Economics

Tony Penn, National Oceanic and Atmospheric Administration

Thierno Sow, Minerals Management Service

Volume 1: Offshore Environmental Cost Model ix

OECM Volume 1.doc 05/22/01 2:35 PM

TABLE OF CONTENTS

1. Introduction to the OECM..................................................................................................... 1

1.1 Overview of Offshore Environmental Cost Model .....................................................................1

1.1.1 Purpose of Model.......................................................................................................................................1

1.1.2 Relationships to Other Models...................................................................................................................1

1.1.3 Model Structure .........................................................................................................................................2

1.1.4 Basic Model Inputs ....................................................................................................................................3

1.1.5 Basic Model Outputs..................................................................................................................................5

2. Offshore Environmental Cost Model User Guide ................................................................ 7

2.1 Hardware and Software Requirements and Installation ...........................................................7

2.2 Model User Interface .....................................................................................................................7

2.3 Overview of Modeling Steps .......................................................................................................10

2.4 Importing Exogenous Impact Drivers .......................................................................................10

2.4.1 Importing E&D Scenarios—the OECM Drivers......................................................................................10

2.4.2 Using the E&D Scenario Database ..........................................................................................................14

2.4.3 Calculation of 55-Year Impact Driver Series...........................................................................................17

2.5 Setting Model Parameters...........................................................................................................18

2.5.1 Global Parameters....................................................................................................................................19

2.5.2 Oil Spill Parameters .................................................................................................................................22

2.5.3 Cost Sector Parameters ............................................................................................................................25

2.6 Generating and Exporting Model Results .................................................................................26

2.6.1 Cost Sector Model Output........................................................................................................................27

2.6.2 Summary Model Output...........................................................................................................................30

3. Structure of the OECM........................................................................................................ 33

3.1 Common Components of Excel Spreadsheets ...........................................................................33

3.1.1 General Approach to Calculations ...........................................................................................................34

3.1.2 Specific Spreadsheet Functions................................................................................................................35

3.1.3 Visual Basic for Applications (VBA) Code and Controls........................................................................37

3.1.4 Model User Interface ...............................................................................................................................37

3.2 OECM.xls Workbook .................................................................................................................39

3.2.1 Assumptions Worksheet ........................................................................................................................39

3.2.2 Oil Spills Worksheet................................................................................................................................41

3.2.3 Ranges Worksheet .................................................................................................................................45

3.2.4 Drivers Worksheet...................................................................................................................................45

3.2.5 CostSector Worksheet............................................................................................................................45

3.2.6 Environmental Cost Sectors.....................................................................................................................46

3.2.7 Summary Worksheet ..............................................................................................................................53

3.2.8 VBA Modules ..........................................................................................................................................54

3.3 E&D.xls Workbook.....................................................................................................................54

3.3.1 E&D Data Worksheet..............................................................................................................................54

3.3.2 Schedules Worksheet.............................................................................................................................55

3.3.3 Drivers Worksheet...................................................................................................................................56

3.4 E&D.mdb Database .....................................................................................................................57

3.4.1 Data Tables ..............................................................................................................................................57
TABLE OF CONTENTS (CONT.)

x Volume 1: Offshore Environmental Cost Model

OECM Volume 1.doc 05/22/01 2:35 PM

3.4.2 Queries.....................................................................................................................................................58

3.4.3 Forms .......................................................................................................................................................58

3.5 Query E&D Data.dqy ................................................................................................................60

3.5.1 Troubleshooting Access Link Errors .......................................................................................................61

3.5.2 Cleaning Up ExternalData# Range Names............................................................................................62

3.6 Accessory Files .............................................................................................................................62

3.6.1 E&D Schedules.xls................................................................................................................................62

3.6.2 Coefficient Storage.xls..........................................................................................................................63

3.6.3 Oil Spill Damages.xls............................................................................................................................64

3.6.4 OECM Result Store.xls.........................................................................................................................66

4. OECM Model Results .......................................................................................................... 68

4.1 Baseline Model Results................................................................................................................68

4.2 Test 2 Scenario Results................................................................................................................73

4.3 Test 3 Scenario Results................................................................................................................75

4.4 Test 4 Scenario Results................................................................................................................77
Volume 1: Offshore Environmental Cost Model xi

OECM Volume 1.doc 05/22/01 2:35 PM

LIST OF FIGURES

Figure 1.1 Relationship of OECM Model to Other Models and OCS Leasing Decisions.................................2

Figure 1.2 Overview of OECM Model Structure.................................................................................................3

Figure 1.3 Specifying Import Backout Effects .....................................................................................................4

Figure 1.4 Cost Element Worksheet Output ........................................................................................................6

Figure 2.2 OECM Impact Drivers.......................................................................................................................10

Figure 2.1 Overview of OECM Modeling Steps.................................................................................................11

Figure 2.3 Managing E&D Scenarios with E&D.mdb and E&D.xls...............................................................12

Figure 2.4 The E&D Data Worksheet.................................................................................................................13

Figure 2.5 The Add-Edit Scenarios Form in E&D.mdb................................................................................15

Figure 2.6 The Choose OECM Scenarios Form ............................................................................................15

Figure 2.7 Updating the ODBC link to the Access Database E&D.mdb .........................................................17

Figure 2.7a Alternative Database Link Prompt...................................................................................................18

Figure 2.8 Global Model Parameters..................................................................................................................20

Figure 2.9 Updating GDP Deflator .....................................................................................................................21

Figure 2.10 Oil Spill Input Parameters.................................................................................................................23

Figure 2.11 Specifying Oil to Shore Mode, Multiple Shipment Factor, and Percent Reaching Shore............24

Figure 2.12 Cost Sector Worksheet Input Parameters........................................................................................26

Figure 2.13 Specifying a Cost Sector Impact Driver ...........................................................................................27

Figure 2.14 Cost Element Worksheet Output ......................................................................................................28

Figure 2.15 Updating National Costs ....................................................................................................................29

Figure 2.16 Summary Worksheet Results ............................................................................................................31

Figure 2.17 Using the Recovery Formulas on the Summary Worksheet..........................................................32

Figure 3.1 Overview of OECM Components .....................................................................................................33

Figure 3.2 The Visual Basic Editor .....................................................................................................................38

Figure 3.3 Finding the Macro Associated with A Spreadsheet Control...........................................................39

Figure 3.4 Structure of the OECM.xls Workbook ............................................................................................40

Figure 3.5 Table of Cost Sector Worksheet Names ...........................................................................................41

Figure 3.6 Overview of Oil Spill Modeling Steps ...............................................................................................42

Figure 3.7 Sample Cost Sector Impact Driver Coefficients ..............................................................................47

Figure 3.8 Calculating National Costs ................................................................................................................49

Figure 3.9 The Update_All_National_Costs VBA Routine...........................................................................51

Figure 3.9 (Cont) The Update_All_National_Costs VBA Routine ................................................................52

Figure 3.9 (Cont) The Update_All_National_Costs VBA Routine ................................................................53

Figure 3.10 Structure of the scenario_data table..............................................................................................58

Figure 3.11 The Add-Edit Scenarios Form .......................................................................................................59

Figure 3.13 Opening Query E&D Data.dqy with MS Query ............................................................................60

Figure 3.12 Contents of Query E&D Data.dqy ..................................................................................................60

Figure 3.14. Access Link Error Dialog ..................................................................................................................61

Figure 3.15. ExternalData Range Names .............................................................................................................62

Figure 3.16 Using Coefficient Storage.xls ........................................................................................................64

Figure 3.17 Using the OECM Result Store.xls workbook................................................................................67

Figure 4.1 Baseline Scenario: Total OECM Environmental Costs ..................................................................69

Figure 4.2 Comparison of Cost Sectors' Contribution to Total Environmental Costs...................................70

Figure 4.3 Comparison of Regional vs. National Costs .....................................................................................70

Figure 4.4 Effect of Imported oil Backout on Total OCS Environmental Costs.............................................71

Figure 4.5 Distribution of Imported Oil Backout Environmental Benefits .....................................................71

Figure 4.6 Total OECM Environmental Costs: Test 2 Scenario ......................................................................73

Figure 4.7 Total OECM Environmental Costs: Test 3 Scenario ......................................................................75

Figure 4.7 Total OECM Environmental Costs: Test 4 Scenario ......................................................................77

Volume 1: Offshore Environmental Cost Model xiii

OECM Volume 1.doc 05/22/01 2:35 PM

LIST OF TABLES

Table 4.1. Summary of Environmental Costs by Planning Area: Baseline Scenario.....................................72

Table 4.2. Summary of Environmental Costs by Cost Sector: Baseline Scenario ..........................................73

Table 4.3. Summary of Environmental Costs by Planning Area: Test 2 Scenario.........................................74

Table 4.4. Summary of Environmental Costs by Cost Sector: Test 2 Scenario..............................................74

Table 4.5. Summary of Environmental Costs by Planning Area: Test 3 Scenario.........................................76

Table 4.6. Summary of Environmental Costs by Cost Sector: Test 3 Scenario..............................................76

Table 4.7. Summary of Environmental Costs by Planning Area: Test 4 Scenario.........................................78

Table 4.8. Summary of Environmental Costs by Cost Sector: Test 4 Scenario..............................................78

Section 1: Introduction to the OECM 1

OECM Volume 1.doc 05/22/01 2:35 PM

1. Introduction to the OECM

1.1 Overview of Offshore Environmental Cost Model

This section provides the user with a brief introduction to the OECM. The model's purposes,

structure, data inputs, and outputs are identified. To help the user understand the model's basic

requirements, the hardware and software used to develop the model are discussed. In addition,

memory needs and processing times are discussed.

1.1.1 Purpose of Model

The production of oil and natural gas on the U.S. OCS creates costs not included in the

traditional accounting of production costs. These costs, which can be termed environmental

costs, are associated with unavoidable degradation of natural resources and consequences of oil

spills. Consideration of environmental costs is an important step in evaluating the suitability of

leasing the nation's OCS resources for energy development.

The purpose of the OECM is to produce and document estimates of the environmental costs

related to the development of leased and unleased oil and natural gas resources in the 26 OCS

Planning Areas. The model accomplishes this purpose by providing estimates for up to 14 userselected cost elements for each of the 26 OCS Planning Areas

1.1.2 Relationships to Other Models

The OECM is not the first modeling effort sponsored by MMS. Among a number of models that

MMS has developed to support decision-making for OCS leasing programs are two that have

been used in the development of this model.

The 1991 General Purpose Environmental Cost Model (GPECM), used to support the 1992-1997

and 1997-2002 5-year programs, significantly expanded upon previous modeling efforts.1 The

current model largely represents a software update of the 1991 model; where possible and where

applicable, software components of the previous model are “recycled” here. There are

significant differences between the current model and the 1991 model, however, both in terms of

the scope of the model’s objectives and in the software components. The most significant

change in the revised model is the exclusion of internalized costs. The 1991 model included

many calculated costs that are borne by operators (e.g., regulatory compliance costs). Because

these costs are included in the operators’ costs of doing business, they are not “externalized”

costs to society and thus are not in the scope of social costs relevant to social cost-benefit

analysis.

 

1

 MMS, 1991. “Estimating the Environmental Costs of OCS Oil and Gas Development and Marine Oil Spills: A

General Purpose Model.” Prepared by A.T. Kearney under contract. The 1991 model, in turn, was constructed

partially based on an earlier model: MMS. “Estimates of Potential Social Costs of Developing the Oil and Gas

Resources in Each OCS Planning Area of the 5 Year Leasing Program Mid-1987 to Mid-1992: Proposed Final.” The

model was developed by T.A. Grigalunas and J.J. Opaluch.
2 Section 1: Introduction to the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

The second model that contributes significantly to the OECM was developed by MMS to

estimate the net economic value of OCS resources. To make those estimates, the model (called

NEV) developed a methodology to allocate the production of oil and natural gas resources in

OCS Planning Areas based on a set of variables defining the time dimensions of that production.

Figure 1.1 depicts how the OECM relates to other models and MMS’s final leasing decisions.

The foundation for both the benefits (NEV Model) and Costs (OECM) are the Exploration and

Development (E&D) scenarios developed by MMS’s resource evaluation division. The E&D

scenarios provide estimates of oil and gas production and associated infrastructure components

like platforms and pipelines. The NEV model uses these data to estimate production costs and

net economic value of production. The OECM uses the same data to estimate external

environmental and social costs. The results of the NEV Model, the OECM, and the market

simulation model, which estimates consumer surplus benefits, are then used to calculate the net

benefits of the E&D scenario. The E&D scenarios that generate the largest surplus of benefits

over costs are proposed for leasing in the 5-Year Plan.

Figure 1.1 Relationship of OECM Model to Other Models and OCS Leasing Decisions

1.1.3 Model Structure

The OECM resides in a Microsoft Excel 97/2000 workbook. The structure of the model is

depicted in Figure 1.2. Worksheet names are labeled in Italics. External data are imported from

E&D scenarios and combined with global assumptions and unit environmental costs factors built

Relationship of GPECM Model to Other Models 

and OCS Leasing Decisions

Environmental Costs 

per Unit of Input:

-Ecology & Wildlife

-Fisheries

-Property Values

Economics Literature

Inputs:

-Platforms

-Pipelines

-Shorebases

-Production

E & D Scenarios

-Calculate oil spills

-Calculate Env.

 Damages

-Calculate Present

 Value of Env. Costs

GPECM Model

Calculate economic 

benefits of OCS 

production scenarios

NEV Model

Compare benefits 

and costs of OCS 

production

Cost-Benefit Analysis

Leasing 

Decision

Prices, Quantities, 

and Consumer 

Surplus Estimates

Market Sim Model
Section 1: Introduction to the OECM 3

OECM Volume 1.doc 05/22/01 2:35 PM

into the model to estimate the environmental and/or social costs associated with each of the

defined cost sectors. Example cost sectors include air quality, property values, and commercial

fisheries. The model is designed to accommodate insertion of additional cost sectors in the

future. Finally, results are aggregated by cost sector and by planning area in a summary

worksheet. These operations are described in detail in Section 2.

1.1.4 Basic Model Inputs

To produce estimates of environmental costs, the model uses inputs that are either user-supplied

(exogenous data) or are calculated within the model. Inputs supplied by the user include oil and

natural gas resources projected to be sold in leases and the schedule of those lease sales. Also

included are other drivers of cost such as the number of wells, platforms, pipeline landfalls, and

miles of pipeline installed.

Global 

Assumptions

Impact Drivers

(from E&D 

Scenarios)

Etc.

Property Values

Fisheries

Air Quality

Individual Cost 

Sector 

Worksheets

Inputs

Calculations

Results

Assumptions Drivers Oil Spills

Oil Spill 

Calculation/ 

Regional 

Allocation

Summary Results 

by Planning Area 

& by Cost Sector

Summary

Figure 1.2 Overview of OECM Model Structure
4 Section 1: Introduction to the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

Among the principal inputs to cost estimates are variables that are generated from within the

model. Three of the most important are :

• the annual quantity of oil and natural gas production expressed in billions of barrels of oil

(BBO) for oil, billion cubic feet (BCF) for gas, and billions of barrels of oil equivalent

(BBOE) for the combination of oil and gas;

• the rate of oil spillage per BBO from various spill sources; and

• the rate at which spilled oil reaches shore.

These and other variables are combined with unit costs (e.g., dollars per barrel of oil reaching

shore) to produce the estimates of costs. These costs are constructed in constant-dollar terms for

up to 55 years and are also discounted to their net present values.

The variables and parameters in the model have been derived from a variety of sources including

the MMS Environmental Studies Program. Research continues on many of the topics appearing

in this model; thus, assumptions and estimates based on existing literature are subject to change

as new information becomes available.

All model outputs are available in both regional and national cost tabulations. Regional costs

count spill-related damages in the location where they occur, while national costs trace spillrelated damages back to the source of production and count them in the producing region.

Regional and national costs are described in detail in Section 2.6.1.

Model output can be set to display the offsetting environmental effects resulting from decreased

importation of foreign oil that results from expanded OCS production (called “Import Backout”).

Because fewer tankerloads of foreign oil pass through U.S. waters as OCS production increases,

there are fewer spills and resulting environmental costs from foreign oil imports. The user can

display results in one of three ways:

• Import Backout Disabled—no import backout effects are included;

• Import Backout Enabled—import backout effects are included as offsetting decreases in

environmental costs due to OCS production; or

• Import Backout Effects Only—only the decreases in environmental costs resulting from

reduced foreign oil importation are shown.

The user can also specify one of three alternative distributions of foreign oil imports. Import

backout effect calculations are described in detail in Section 3.2.2.

Figure 1.3 Specifying Import Backout Effects
Section 1: Introduction to the OECM 5

OECM Volume 1.doc 05/22/01 2:35 PM

1.1.5 Basic Model Outputs

The OECM provides a number of different types of output results. Most output from the OECM

is dynamically updated. Only in certain instances do aspects of the model have to be re-run to be

updated with the most recent results. Each cost sector worksheet contains four tables of output,

all relating only to the costs associated with that cost sector, as shown on Figure 1.4. The tables

to the left calculate the present value of environmental costs over the 55-year forecasting period.

The tables show the present values for each of the three cost sector subcategories defined above

and the total.2 The tables to the right, which are truncated in Figure 1.4, show the environmental

costs calculated annually for each of the 55 years in the forecast period.

In addition to the individual cost sector output, summary results over all the model’s cost sectors

are calculated on the Summary worksheet. The top section of the Summary worksheet looks

almost identical to a cost sector worksheet—except the calculations are summing over all cost

sectors in the model. Below this set of tables is another summary table that tabulates total

environmental costs by cost sector rather than by planning area.

The top pair of tables in Figure 1.4 calculates the regional costs associated with the cost sector,

while the bottom tables show national costs. Regional costs, the model’s default, count

environmental costs in the planning area in which they occur. The national cost tabulation, on

the other hand, counts environmental costs based on the area where the production took place.

The different cost tabulations merely allocate environmental costs between planning areas. The

grand total sum over all planning areas is identical under either cost tabulation. They are

discussed in more detail in Section 2.6.1.

 

2

 See Section 2.5.3 for information about cost sector subcategories.
6 Section 1: Introduction to the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

Test Case Scenario Test Case Scenario

Present Value of Environmental Costs Annual Environmental Costs

Regional Cost Allocation Regional Cost Allocation

Commercial Fisheries Commercial Fisheries

($2000) (000) ($2000) (000)

OCS Region Planning Area 2003 2004 ### 2057

Planning Area

R North Atlantic - - - - North Atlantic - - - - 

Mid-Atlantic - - - - Mid-Atlantic - - - - 

E South Atlantic - - - - South Atlantic - - - - 

Straits of Florida - - - - Straits of Florida - - - - 

G G Eastern Gulf 29.7 0.6 - 30.3 Eastern Gulf - 0.0 - - 

o Central Gulf 731.6 16.6 - 748.2 Central Gulf - 0.5 - - 

I M Western Gulf 281.7 8.2 - 289.9 Western Gulf - 0.3 - - 

Southern California - - - - Southern California - - - - 

O Central California - - - - Central California - - - - 

Northern California - - - - Northern California - - - - 

N Washington/Oregon - - - - Washington/Oregon - - - - 

Gulf of Alaska - - - - Gulf of Alaska - - - - 

A Cook Inlet 22.6 - - 22.6 Cook Inlet - - - - 

A Kodiak - - - - Kodiak - - - - 

L Shuimagin - - - - Shuimagin - - - - 

L North Aleutian Basin - - - - North Aleutian Basin - - - - 

Aleutian Arc - - - - Aleutian Arc - - - - 

A St. George Basin - - - - St. George Basin - - - - 

C St. Matthew Hall - - - - St. Matthew Hall - - - - 

S Bowers Basin - - - - Bowers Basin - - - - 

O Aleutian Basin - - - - Aleutian Basin - - - - 

K Navarin Basin - - - - Navarin Basin - - - - 

S Norton Basin - - - - Norton Basin - - - - 

A Hope Basin - - - - Hope Basin - - - - 

T Chukchi Sea - - - - Chukchi Sea - - - - 

Beaufort Sea - - - - Beaufort Sea - - - - 

S Total 25.4 1,065.6 - 1,091.0 Total 0.8 - - - 

OK

Test Case Scenario Test Case Scenario

Present Value of Environmental Costs Annual Environmental Costs

National Cost Allocation National Cost Allocation

Commercial Fisheries Commercial Fisheries

($2000) (000) ($2000) (000)

Updated:

OCS Region

Planning Area Planning Area 2003 2004 ### 2057

N North Atlantic - - - - North Atlantic - - - - 

Mid-Atlantic - - - - Mid-Atlantic - - - - 

A South Atlantic - - - - South Atlantic - - - - 

Straits of Florida - - - - Straits of Florida - - - - 

T G Eastern Gulf 29.7 0.6 - 30.3 Eastern Gulf - 0.0 - - 

o Central Gulf 731.6 16.6 - 748.2 Central Gulf - 0.5 - - 

I M Western Gulf 281.7 8.2 - 289.9 Western Gulf - 0.3 - - 

Southern California - - - - Southern California - - - - 

O Central California - - - - Central California - - - - 

Northern California - - - - Northern California - - - - 

N Washington/Oregon - - - - Washington/Oregon - - - - 

Gulf of Alaska - - - - Gulf of Alaska - - - - 

A Cook Inlet 22.6 - - 22.6 Cook Inlet - - - - 

A Kodiak - - - - Kodiak - - - - 

L Shuimagin - - - - Shuimagin - - - - 

L North Aleutian Basin - - - - North Aleutian Basin - - - - 

Aleutian Arc - - - - Aleutian Arc - - - - 

A St. George Basin - - - - St. George Basin - - - - 

C St. Matthew Hall - - - - St. Matthew Hall - - - - 

S Bowers Basin - - - - Bowers Basin - - - - 

O Aleutian Basin - - - - Aleutian Basin - - - - 

A

T

L

P

A

C

Total

Total

7/6/00 1:24 PM

A

T

L

P

A

C

 Platform 

Effects 

 Pipeline 

Effects 

 Cost 

Subcat. 3 

 Cost 

Subcat. 3 

 Platform 

Effects 

 Pipeline 

Effects 

! 55 Years "

! 55 Years "

Figure 1.4 Cost Element Worksheet Output
Section 2: OECM User Guide 7

OECM Volume 1.doc 05/22/01 2:35 PM

2. Offshore Environmental Cost Model User Guide

This section provides an overview of basic model installation and use. For a more detailed

explanation of model structure, refer to Section 3.

2.1 Hardware and Software Requirements and Installation

The components of the OECM are included in several files. The only hardware requirement is a

PC with Excel 973 or better loaded. The model has been successfully tested using Windows 98,

NT4, and 2000 operating systems. To use the optional database link, MS Access 97, MS Query,

and the Excel ODBC driver must be installed. To work the database link to OECM, you must

have the Analysis Toolpak – VBA activated in MS Excel. To determine its status, in Excel go to

“Tools”, “Add Ins”. Check to see if "Analysis Toolpak - VBA" is checked. If not, check it. If it

is not listed, then it has to be installed. While you are in there, see if the “ODBC Add-In" is

present and checked. Then, completely exit Excel and restart it (you don't have to reboot the

computer).

To install the OECM, use MS Windows Explorer to create a new folder for this material on your

hard drive. (Use the “File” “New” “Folder” command sequence.) You might want to name the

new folder “OECM.” Using Explorer, copy (drag and drop) the “OECM” folder directly from

the CD to the new folder on your hard drive. Remove the read-only attribute from all model files

using the Explorer “File” “Properties” command sequence and toggling off the read-only

attribute. Open OECM.xls and E&d.xls in MS Excel. Note that the OECM contains Visual

Basic macros, which are required to perform some model operations. If Excel prompts you with

a macro virus warning, choose to “Enable Macros.” Open E&d.mdb in MS Access. Now, you

are ready to run the OECM with all its capabilities.

2.2 Model User Interface

The model structure employs numerous techniques to make it powerful yet easy-to-use.

Cell Formatting

Throughout the model, a consistent set of cell formatting rules is used, which provide the user

with instant visual cues to whether a particular region houses input parameters, calculated cells,

or simple text labels. Most important is the coloring of cell contents. The following color rules

are used throughout:

Red = user input parameter

Blue = calculated cell, usually from other cells on the active worksheet

Dark blue = calculated cell drawn from another worksheet

Burgundy = calculated cell linked to an external workbook

Black = text label or other unchanging parameter

 

3

 Excel 97 Service Release 2 (SR-2) or better is recommended. Identify the version from the Help...About dialog.
8 Section 2: OECM User Guide

OECM Volume 1.doc 05/22/01 2:35 PM

For example, as shown in the oil spill input data form below, areas of the spreadsheet where the

user provides input parameters are shaded light gray. Specific data input cells have a white

background and red text. Red-colored text anywhere in the spreadsheet signifies an area where

the user can modify a model parameter. Moving or making any modifications to cells formatted

in blue could potentially destroy the integrity of the model.

The summary environmental cost table shown below consists mostly of calculated cells. The

user changes nothing on the table; it

consists entirely of calculated cells

(blue) and text labels (black). Note that

the name of the cost sector

(“Commercial Fisheries”) and the

names of the cost categories are blue.

These cells are not simply text labels

but are calculated cells. If you want to

change the labeling, do so above in the

input section. While at first this may

appear unnecessarily complicated, it

ensures that the labels are consistent

throughout the model—the user

changes the value once, and every

dependent cell throughout the entire

model is automatically updated. The

OCS regions are also color-coded in

this example to provide a visual

reference as one reads across a row.

Color-coding is added for readability.

These colors appear on-screen, but the

default option for printing is black-andwhite. Only black and white (not color

or grayscale) will come out of the

printer unless the user modifies the

print settings.
Section 2: OECM User Guide 9

OECM Volume 1.doc 05/22/01 2:35 PM

Cell Comments

Another technique employed throughout the model to help users understand its operations is use

of cell comments, a sample of which is shown below. These comments describe what a certain

calculation is doing and give a plain-English description of the formula where applicable. Cells

with comments are indicated by the small red triangle in the upper right-hand corner of the cell.

Placing the cursor over the triangle will display the cell comment.
10 Section 2: OECM User Guide

OECM Volume 1.doc 05/22/01 2:35 PM

User Input Filters and Validation

Another feature of the user interface involves the use of input filters and data validation. Usually

implemented as a pull-down menu, you are presented with a list of acceptable values for an input

cell. These types of protections are important for large spreadsheets such as the OECM. A

single faulty value in a cell can cause a series of cascading error values throughout the model

that can be hard to track to the source. Worse yet, an invalid entry may go unnoticed and cause

erroneous model results.

2.3 Overview of Modeling Steps

Figure 2.1 is a flow diagram showing the steps in the OECM

modeling process. The italic type in the sub-boxes is the name of the

worksheet or external file where the activity is accomplished. The

modeling steps are described in detail in the following sections.

2.4 Importing Exogenous Impact Drivers

Impact Drivers are the estimates of oil and gas production, wells,

platforms, pipelines, shorebases, etc., which drive estimates of

environmental and social costs in the OECM. The model can

accommodate up to twenty impact drivers. Most of the parameters

are based on E&D scenarios developed by MMS’s planning division.

Figure 2.2 shows the impact drivers in the baseline model. For most

offshore facilities, there is a driver for annual incremental additions

(“...Inst.”) and a cumulative measure of all facilities installed less

retirements [“(Cum.)”]. The last five drivers, which relate to barrelof-oil-equivalent production and oil spills, are calculated

endogenously in the OECM.

2.4.1 Importing E&D Scenarios—the OECM Drivers

Figure 2.3 describes the process of developing E&D scenario impact

drivers and importing them into the OECM. Impact driver variables

Figure 2.2 OECM

Impact Drivers
Section 2: OECM User Guide 11

OECM Volume 1.doc 05/22/01 2:35 PM

are calculated in the Excel workbook E&D.xls and then imported into the OECM. In addition, a

Microsoft Access 97 database E&D.mdb is provided for storing alternative E&D scenarios and

choosing which to include in the current OECM run. E&D.xls performs three main functions:

1. It either stores E&D scenarios or imports them from a database;

2. It tabulates relevant data from the E&D scenarios and arranges them by planning area; and

3. It develops the 55-year time series of OECM impact drivers and links them to the OECM

model.

E&D scenario data is either entered or imported on the top section of the E&D Data worksheet, as

shown in Figure 2.4. The top section consists of two rows of column labels (the first a

descriptive label and the second a field name keyed to the external database) and then the E&D

Specify Global 

Model 

Parameters

Assumptions

E&D.xls, Drivers

Import 

Exogenous 

Impact Drivers

Modify Impact 

Driver Cost 

Coefficients

Cost Sectors

Assumptions

Cost Sectors 

Specify Oil Spill & 

Import Backout 

Settings

Update Model 

Calculations

Cost Sectors

Print Model 

Output

Cost Sectors, 

Summary

Export Model 

Results

Summary

Add/Remove 

Cost Sectors

Oil Spills,

Cost Sectors 

E&D.mdb

Choose or Add 

New E&D 

Scenario

Figure 2.1 Overview of OECM Modeling Steps

Italics specify worksheet in OECM.xls where activity occurs. Bold italics specify

other files.
12 Section 2: OECM User Guide

OECM Volume 1.doc 05/22/01 2:35 PM

scenarios arranged line by line. If manually importing E&D scenario data, the user must ensure

that the data exactly conform to the column structure of the input table. Columns cannot be

rearranged. The bottom section of the spreadsheet automatically parses the raw E&D scenario

inputs by planning area, aggregating if multiple scenarios are present for a single planning area.

Note that the top section of the spreadsheet uses more columns that the bottom one. This is

because the OECM does not use every E&D scenario parameter in developing the OECM

drivers. Also note that there are three available E&D parameters (listed as Drivers 13 – 15) that

can be used to develop new OECM impact drivers.

Store and 

Manage E&D 

Scenarios

E&D.mdb

Import / Input 

E&D Scenario 

Parameters

E&D Data

E&D Production 

Profiles and 

Facilty Schedules

Schedules Export OECM 

Impact Drivers to 

OECM.xls

OECM.xls

Calculate 55-year 

Impact Driver 

Series

Drivers

Figure 2.3 Managing E&D Scenarios with E&D.mdb and E&D.xls

Italics specify worksheet in E&D.xls where activity occurs. Bold italics specify other

files.
Section 2: OECM User Guide 13

OECM Volume 1.doc 05/22/01 2:35 PM

Currently Selected Scenarios from E&D.mdb Database Connectivity

ID Scenario Name High - Low 

Case

Planning Area 

Number Planning Area Include in OECM

Number of 

Lease Sales

Oil Production 

(BBO)

Gas Production 

(TCF)

ID Name_Scen Case PlanArea_NuPlanArea Include NumSales Prod_Oil Prod_Gas

17 EGoM 5-YR Plan - Expected Expected 5 Eastern Gulf 1 2 0.27 2.3299999

16 CGoM 5-YR Plan - Expected Expected 6 Central Gulf 1 5 1.475 14.8

15 WGoM 5-YR Plan - Expected Expected 7 Western Gulf 1 5 0.25 6.25

21 Gulf of Alaska 5-YR Plan - ExpecteExpected 12 Gulf of Alask 1 1 0.34 0

20 Cook Inlet 5-YR Plan - Expected Expected 13 Cook Inlet 1 1 0.195 0.29

19 Chukchi/Hope Basin 5-YR Plan - E Expected 25 Chukchi Sea 1 1 2.095 0.105

18 Beaufort 5-YR Plan - Expected Expected 26 Beaufort Sea 1 2 1.275 0

Summary of E&D Scenario Input Data by Planning Area and Lease Year

Planning Area

E&D 

Scenario 

Count

Years of 

Activity

BBO oil 

produced

BCF gas 

produced Wells Inst. Wells (Cum) Prod. Wells Inst.

Years Prod_Oil Prod_Gas Wells_Total Wells_Total Wells_Prod

1 North Atlantic

Year 1 Sales - - - - - - - 

Year 2 Sales - - - - - - - 

Year 3 Sales - - - - - - - 

Year 4 Sales - - - - - - - 

Year 5 Sales - - - - - - - 

2 Mid-Atlantic R O W S H I D D E N

5 Eastern Gulf

Year 1 Sales 1 40 0.27 2,330 710 710 355 

Year 2 Sales - - - - - - - 

Year 3 Sales - - - - - - - 

Year 4 Sales - - - - - - - 

Year 5 Sales - - - - - - - 

6 Central Gulf

Year 1 Sales 1 35 1.48 14,800 4,500 4,500 2,250 

Year 2 Sales - - - - - - - 

Year 3 Sales - - - - - - - 

Year 4 Sales - - - - - - - 

Year 5 Sales - - - - - - - 

7 Western Gulf

Year 1 Sales 1 35 0.25 6,250 1,200 1,200 600 

Year 2 Sales - - - - - - - 

Year 3 Sales - - - - - - - 

Year 4 Sales - - - - - - - 

Year 5 Sales - - - - - - - 

8 Southern California

Year 1 Sales - - - - - - - 

Year 2 Sales - - - - - - - 

Access Link Enabled

Raw E&D scenario input data OECM drivers tabulated by Planning Area

Figure 2.4 The E&D Data Worksheet

Raw E&D scenario data is imported in the top section of the worksheet. In the lower

section, OECM drivers are distributed by Planning Area and year of lease sale.
14 Section 2: OECM User Guide

OECM Volume 1.doc 05/22/01 2:35 PM

2.4.2 Using the E&D Scenario Database

A Microsoft Access 974 database, E&D.mdb, is provided to input, store, and specify E&D

scenarios to include in the OECM. The database can store an almost unlimited number of E&D

scenarios, from which the user can choose any subset to include in OECM runs. This feature is

provided for users who already have experience using MS Access and, optimally, MS Query.

Users unfamiliar with MS Access may be better off to disable the Access link (described below)

and enter E&D data manually.

All of the E&D scenarios are stored in the table scenario_data. Users can enter or modify E&D

scenarios stored on this table in E&D.mdb in a number of ways:

1. Use the Add-Edit Scenarios form, which is displayed in Figure 2.5. The form provides a userfriendly front-end to incorporate data into the table;

2. Add or edit records directly on the scenario_data table;

3. Use an append query to add records to the table (advanced users only).

If all of the scenarios have already been entered and the user simply wants to choose which scenarios

to include in the OECM run, use the form Choose OECM Scenarios, which is shown in Figure 2.6.

Linking E&D Database to the E&D.xls Spreadsheet

For the database link to work, the user’s machine must have the Excel ODBC driver and MS

Query installed. These components may not be included in default installations of Excel or

Microsoft Office. If the database link operation is unsuccessful, data can still be entered into the

E&D.xls spreadsheet manually. To use the direct database link to the database E&D.mdb, check

the box at the top of the E&D Data worksheet (see Figure 2.4). The database link is refreshed

each time the spreadsheet is opened. To update the link during a user session, hit the Refresh

button. To disable the database link, uncheck the box. It is possible Excel will not be able to

find the location of the Access database. In this case, the error dialog shown in Figure 2.7 or

Figure 2.7a will be presented. First, in Excel go to Tools, Add Ins check to see if "Analysis

Toolpak - VBA" is checked. If not, check it, exit, and restart Excel. If the error dialog still

appears, follow the steps in the figure to update the link5

.

 

4

 Note that while Excel 97 and 2000 files are perfectly compatible (i.e., they use the same file format), the same is

not true of Access 97 and 2000. Opening the Access 97 database in Access 2000 can cause the file format to change

such that the file is no longer usable in Access 97. The database link to E&D.xls works in either version of Access. 5

 This process should not be necessary if both E&D.xls and E&D.mdb are stored in the same directory and the

current path (i.e., the path that appears when go to open a file in Excel) is this directory. You can set the directory to

be the active path by opening E&D.xls from within Excel rather than double clicking in Windows Explorer.
Section 2: OECM User’s Guide—Draft 

OECM Volume 1.doc 05/22/01 2:35 PM

Figure 2.5 The Add-Edit Scenarios Form in E&D.mdb

Use the Find Existing Scenario pulldown menu to findexisting scenarios or use the navigation buttons on thebottom. Hit OK when changes are complete. Hit Delete(the trashcan) to remove a scenario record.

Figure 2.6 The Choose OECM Scena

This form is used only to chto include in the OECM. Puunder Include for each scenOECM.

Section 2: OECM User Guide 17

OECM Volume 1.doc 05/22/01 2:35 PM

2.4.3 Calculation of 55-Year Impact

Driver Series

The E&D scenarios only provide grand totals

over the entire period of OCS activity for oil

and gas produced and corresponding facilities

installations. For the OECM to calculate

damages annually, the grand totals from the

E&D scenarios must be distributed through

time. This is accomplished using explicit

production profiles and facility

installation/removal profiles found on the

Schedules worksheet of E&D.xls. Schedules

are developed for six different lengths of

offshore activity ranging from 25 to 50 years

in 5-year increments. Timing is specified in

the Years of Activity column of the E&D

scenario input data6. The year (1 – 5) of the

lease sale is entered in the Start_Year column.

The E&D scenario data for each planning area

are then multiplied by the appropriate

scheduling matrices to generate 55-year series

of impact driver variables for each planning

area. This calculation takes place on the

Drivers worksheet of E&D.xls. This generates

26 (one for each planning area) [55x15]

arrays of impact driver variables that are then

automatically linked to the OECM model

OECM.xls. If both the E&D.xls and OECM.xls

workbooks are open simultaneously, any

changes made to the E&D scenarios will

automatically be reflected in OECM results.

If the user is finished making any

modifications to E&D scenarios, E&D.xls can

be closed to decrease system resource usage

and speed calculation time in OECM.xls. The

most recent values of impact driver variables

will be retained in OECM.xls. Closing

E&D.xls will increase processing speed in

OECM.xls 10-20% in most instances.

 

6

 Note that each of the scheduling matrices contains three columns corresponding to the unused impact driver

variables 13 – 15. Currently these columns contain all zeroes.

Hit the OK button.

Hit Database...Don’t change the Login or

Password

Specify the path to the location of E&D.mdb and

hit OK. Finally, hit OK on the Login dialog box.

Figure 2.7 Updating the ODBC link to the

Access Database E&D.mdb

You may be presented with the

dialog box in Figure 2.7a instead of

the top frame above.
18 Section 2: OECM User Guide

OECM Volume 1.doc 05/22/01 2:35 PM

2.5 Setting Model Parameters

In addition to the external E&D scenario data, several parameters can be changed within the

OECM. User parameters are always displayed in red or with drop-down menus. This section

explains how to specify model parameters and the significance of each. These parameters fall

into three categories:

1. Global Parameters—These settings affect the entire model. Examples include the name of

the current scenario, the base year of analysis, and the discount rate used for present value

calculation. They are found on the Assumptions worksheet, with a couple of exceptions noted

below.

2. Oil Spill Parameters—These are parameters specifically related to how the model estimates

the volume and location of oil spills. They are found on the Oil Spills worksheet.

3. Cost Sector Parameters—These parameters apply only to individual cost sectors, and are

specified on each cost sector’s worksheet. These include the amount of environmental cost

associated with impact driver variables (called cost sector coefficients) and the dollar-year of

the cost sector coefficients.

Figure 2.7a Alternative Database Link Prompt

Instead of receiving the prompt in the top frame of Figure 2.7, you may instead

receive the above prompt. In this case, choose MS Access 97 Database from

the Machine Data Source tab. (If MS Access 97 Database isn’t included in

the list, the proper ODBC drivers are not loaded.) You should then see a

dialog like the second frame in Figure 2.7. Continue as indicated in the figure.
Section 2: OECM User Guide 19

OECM Volume 1.doc 05/22/01 2:35 PM

2.5.1 Global Parameters

Refer to Figure 2.8 to see the global model parameters found on the Assumptions worksheet.

They are described below in the order in which they appear on the worksheet.

Scenario Name

Specify the name for the current scenario in the box. The name should fit inside the box

margins.

Imported Oil Backout

The production of oil in the OCS reduces the need to import from foreign sources. In the

absence of OCS production, an equivalent amount of oil would be imported, creating

environmental costs due to tanker spills and other spills associated with transshipment. These

tankers would spill oil and degrade the environment in much the same way that tankers carrying

OCS oil generate environmental costs. Enabling the imported oil backout subtracts the

environmental costs of the foreign oil that is replaced by OCS production.

The user can display import backout results in one of three ways:

• Import Backout Disabled—no import backout effects are included;

• Import Backout Enabled—import backout effects are included as offsetting decreases in

environmental costs due to OCS production; or

• Import Backout Effects Only—only the decreases in environmental costs resulting from

reduced foreign oil importation are shown.

The user also can specify one of three alternative distributions of foreign oil imports. These are

specified in the “Imported Oil Distribution” section of the Oil Spills worksheet.

Base Years for Analysis

The timing of the model is variable; that is, it can be used for any 55-year forecasting period.

The year specified as the “first year of offshore activity” is defined as year 1 of the model.

Dollar Conversion and Discount Rate

The model calculates all environmental costs in constant dollars, which can be expressed in the

dollars of any year. In the case of the example, OCS activity begins in 2003, but since the

analysis is being done in 2000, the model converts the results to 2000 dollars. The U.S. implicit

GDP deflator is used to convert from one year’s dollars to another’s. For past years, historic

GDP data are used. The baseline model includes actual GDP data through 2000. As future GDP

releases become available, they can be entered into the model to replace the forecasts. To update

GDP deflator data, find the range v_GDPdef on the Ranges worksheet, as depicted in Figure 2.9.

Simply replace the calculated figure (in blue) with the actual value. For future years, an annual

factor is assumed for all years. This is the figure entered in the “Future Year-Dollar Conversion

Factor (GDP deflator)” parameter.
20 Section 2: OECM User Guide

OECM Volume 1.doc 05/22/01 2:35 PM

Figure 2.8 Global Model Parameters
Section 2: OECM User Guide 21

OECM Volume 1.doc 05/22/01 2:35 PM

In addition to year-dollar conversions, the model also calculates the net present value of

environmental costs over the 55-year forecasting period. The present value discount factor is

entered in the box for “Real Discount Rate (excluding inflation).” Note that since the model runs

on constant dollars, the discount rate includes no inflation.

Cost Sector Definitions

The addition and naming of cost sectors is handled on this section of the Assumptions worksheet.

The model can accommodate up to 14 different cost sectors. The names applied to each of the

cost sectors are specified here. The table contains two columns: the descriptive name of the cost

sector and the name of the Excel worksheet where the cost sector is located (i.e., the name of the

“worksheet tab” for the cost sector). The user should never have to provide the Worksheet

Name, as it will be automatically updated. Maintenance of this table is critical to proper model

operation. Whenever any changes are made to the structure or descriptions of cost sector

worksheets, the Update Table button should be depressed. These changes include any of the

following:

• Deleting a cost sector;

• Adding a new cost sector;

• Renaming a cost sector worksheet (i.e., changing the text on the

worksheet tab).

The macro attached to the Update Table button refreshes the model

structure to incorporate any of these changes.

Figure 2.9 Updating GDP

Deflator
22 Section 2: OECM User Guide

OECM Volume 1.doc 05/22/01 2:35 PM

Adding A New Cost Sector

To add a new cost sector to the model, do the following:

1. Depress the Add New Cost Sector button. The process of creating the worksheet takes a few

seconds to complete. When it is complete, the dialog below should appear7.

2. The new worksheet is given a default name CostSector (x) where “x” will vary. If you wish to

change the name of the worksheet tab, do so (double-click the tab and add the new name).

3. Switch back to the Assumptions worksheet and type in the descriptive name for the new cost

sector.

4. Depress the Update Table button. The new cost sector should now be listed in the table.

2.5.2 Oil Spill Parameters

The OECM estimates the amount of OCS oil spilled and the amount of spilled oil reaching shore

based on the amount of oil production specified in the E&D scenarios. Oil spill parameters are

specified on the Oil Spills worksheet. The oil spill input section is shown in Figure 2.10. The

user provides estimates of the average frequency of oil spills per BBO produced for platforms,

pipelines, and tankers containing OCS and imported oil; the average size of each spill; and the

fraction of spilled oil that makes it to shore. Small spills are tabulated separately.

 

7

 Should the automated process fail, new cost sectors can be added manually. To do so, right-click on the worksheet

tab of the CostSector worksheet and choose ...Move or Copy. A dialog box like that shown below appears. Leave

the “To book” as is—it should be the current workbook. Choose “(move to end).” Put a check next to “Create a

copy.” You will be prompted with a series of dialogs regarding identical ranges on origin and destination

worksheets. Choose “yes” each time the prompt appears. Switch back to Assumptions and click Update Table.

Continue with Step 2 above.
Section 2: OECM User Guide 23

OECM Volume 1.doc 05/22/01 2:35 PM

The only difference in spill rates between planning areas is specified through the “Oil to Shore

Mode” parameter, which is shown in Figure 2.11. This specifies whether oil is transported to

shore via tanker, pipeline, or some combination of the two. In addition, an “imported oil

multiple shipment” factor can be assigned to each planning area to scale-up spills from imports if

oil is transshipped from tankers to barges. Last, the percentage of spilled oil reaching shore for

platform, pipelines, and tankers is specified.

Spill Location

Because oil produced in one region may spill from a tanker in another, the oil spill risk is

distributed among all affected OCS Planning Areas. This function is currently only used for

Alaskan oil, which is tankered down the West Coast, thus creating spill risk outside the

producing area. Out-of area spill risk is defined using a 26 x 26 matrix, with rows being the

location of production and columns being the location of spills. This is called the tanker spill

risk allocation matrix, and is only applied to tanker spills. For most planning areas there are no

Figure 2.10 Oil Spill Input Parameters
24 Section 2: OECM User Guide

OECM Volume 1.doc 05/22/01 2:35 PM

outside-of-region spill effects, so the matrix has a 1 on the diagonal. Only for Alaskan

production are there off-diagonal elements to the spill risk allocation matrix.

Once the global spill parameters are specified and the E&D scenario impact drivers have been

imported, the remainder of the Oil Spills worksheet estimates the amount of oil spilled and the

amount of that spilled oil that makes it to shore in each forecast year and for each planning area.

Spills are calculated for (a) tankers and (b) platforms and pipelines. If the import backout is

enabled, spill amounts in the tanker section may be negative for some planning areas. This

means that decreases in imported oil causes a net decrease in the amount of oil spilled in the

planning area.

Figure 2.11 Specifying Oil to Shore Mode, Multiple Shipment Factor, and Percent

Reaching Shore
Section 2: OECM User Guide 25

OECM Volume 1.doc 05/22/01 2:35 PM

2.5.3 Cost Sector Parameters

Data on per-unit environmental costs for an individual cost sector are entered on the cost sector

worksheets, as shown in the sample for Commercial Fisheries in Figure 2.12. The steps to add a

cost sector to the model are described in Section 2.5.2. The following assumes that the

environmental cost sector worksheet has already been created.

The OECM calculates its results be multiplying a 55-year series of impact drivers from the E&D

scenarios by a set of impact driver cost coefficients, which are developed for each cost sector. A

cost sector may use up to nine different impact driver variables to estimate environmental costs.

They may be grouped into three sub-categories, each sub-category consisting of up to three

impact drivers. In Figure 2.12, two sub-categories are defined: “Platform Effects” and “Pipeline

Effects.” The sub-categories only function to provide additional detail in labeling model

results—their use is optional and any of the nine columns can be used to define impact drivers

and associated cost coefficients.

Creating an Impact Driver and Defining Its Cost Coefficients

To associate one of the impact driver variables with a particular cost sector, click on any of the

nine cells in the Impact Driver row, as shown in Figure 2.13. The user is presented with a dropdown menu listing all of the model’s available impact driver variables. Choose the desired

impact driver. The corresponding units of measure are located in the cell immediately below the

impact driver. The units will automatically update when the impact driver variable is chosen.

For example, in Figure 2.12, the user has chosen the impact driver variable Platforms (Cum). The

appropriate unit to use with this impact driver is $ / Platform. Note that unless the impact driver is

production based (i.e., oil, gas, or BOE-equivalent production), all of the units are specified on

an annual (e.g., $ per platform per year) basis. For any of the nine impact driver columns not

used, set the impact driver to None using the pull-down menu.

Last, the dollar-year of the cost coefficients must be specified. In the cell provided, enter the 4-

digit year of the year-dollars on which the cost coefficients are based. This feature ensures that

calculations remain consistent as the user chooses different base years for the analysis.8 For

example, the user may choose to view model results in constant year 2000 dollars. The cost

coefficients for commercial fisheries shown in Figure 2.12 are defined in $1999. The cost

coefficients for another cost sector may be defined in $1998 or $2001. The model will

automatically adjust its calculations to the appropriate dollar basis to ensure that model output is

based on a consistent dollar-year.

Once the impact driver variable is specified, the corresponding environmental cost coefficients for

each of the 26 OCS planning areas are entered below. For columns where no impact driver has

been specified, this area should be blank. Even if the impact driver variable above is set to None,

the column should still be clear of numbers to avoid erroneous calculations. Note that the units of

the cost coefficients must correspond to the units displayed below the impact driver. For example,

the unit of measure for spilled oil is $ / BBL. If an outside data source defines oil spill damages in

“dollars per gallon,” the values must be adjusted to match the units defined in the OECM.

 

8

 See Section 2.5.1 for information on defining the model’s base year.
26 Section 2: OECM User Guide

OECM Volume 1.doc 05/22/01 2:35 PM

2.6 Generating and Exporting Model Results

The OECM provides a number of different types of output results. Most output from the OECM

is dynamically updated. Only in certain instances do aspects of the model have to be re-run to be

updated with the most recent results. Model results are available in different levels of

aggregation and in various tabulations. This section describes the different types of OECM

output and the steps to generate them.

Figure 2.12 Cost Sector Worksheet Input Parameters
Section 2: OECM User Guide 27

OECM Volume 1.doc 05/22/01 2:35 PM

2.6.1 Cost Sector Model Output

Each cost sector worksheet contains four tables of output, all relating only to the costs associated

with that cost sector, as shown on Figure 2.14. The tables to the left calculate the present value

of environmental costs over the 55-year forecasting period. The tables show the present values

for each of the three cost sector subcategories defined above and the total.9

 The tables to the

right, which are truncated in Figure 2.14, show the environmental costs calculated annually for

each of the 55 years in the forecast period. These calculations sum over all cost subcategories.

Note that the dollar amounts shown on the tables are in thousands of dollars. The year-dollars of

the results, shown on each table, is equal to the base year assigned on the Assumptions worksheet.

Regional vs. National Costs

The top pair of tables in Figure 2.14 calculates the regional costs associated with the cost sector,

while the bottom tables show national costs. Regional costs, the model’s default, count

environmental costs in the planning area in which they occur. E.g., if Alaskan crude spills from

a tanker off California, the damage is attributed to California. The benefit of the regional cost

tabulation is that the environmental costs occurring in each planning area are accurately counted,

even if the damage is caused by tanker spills of oil produced far away. Under the regional cost

tabulation, a planning area can experience environmental costs even if there is no OCS activity

occurring there.

The national cost tabulation, on the other hand, counts environmental costs based on the area

where the production took place. The national cost tabulation counts the spill of Alaskan crude

off California in its Alaskan production area. The benefit of the national cost tabulation is that it

accurately counts the total environmental costs associated with OCS production from a planning

area, regardless of where the damage actually occurs. The distinction between regional and

national costs is only relevant for tanker spills—all other environmental costs are assumed to

occur in the planning area where production takes place. The different cost tabulations merely

allocate environmental costs between planning areas. The grand total sum over all planning

areas is identical under either cost tabulation.

 

9

 See Section 2.5.3 for for information about cost sector subcategories.

Figure 2.13 Specifying a Cost Sector Impact Driver
28 Section 2: OECM User Guide

OECM Volume 1.doc 05/22/01 2:35 PM

Test Case Scenario Test Case Scenario

Present Value of Environmental Costs Annual Environmental Costs

Regional Cost Allocation Regional Cost Allocation

Commercial Fisheries Commercial Fisheries

($2000) (000) ($2000) (000)

OCS Region Planning Area 2003 2004 ### 2057

Planning Area

R North Atlantic - - - - North Atlantic - - - - 

Mid-Atlantic - - - - Mid-Atlantic - - - - 

E South Atlantic - - - - South Atlantic - - - - 

Straits of Florida - - - - Straits of Florida - - - - 

G G Eastern Gulf 29.7 0.6 - 30.3 Eastern Gulf - 0.0 - - 

o Central Gulf 731.6 16.6 - 748.2 Central Gulf - 0.5 - - 

I M Western Gulf 281.7 8.2 - 289.9 Western Gulf - 0.3 - - 

Southern California - - - - Southern California - - - - 

O Central California - - - - Central California - - - - 

Northern California - - - - Northern California - - - - 

N Washington/Oregon - - - - Washington/Oregon - - - - 

Gulf of Alaska - - - - Gulf of Alaska - - - - 

A Cook Inlet 22.6 - - 22.6 Cook Inlet - - - - 

A Kodiak - - - - Kodiak - - - - 

L Shuimagin - - - - Shuimagin - - - - 

L North Aleutian Basin - - - - North Aleutian Basin - - - - 

Aleutian Arc - - - - Aleutian Arc - - - - 

A St. George Basin - - - - St. George Basin - - - - 

C St. Matthew Hall - - - - St. Matthew Hall - - - - 

S Bowers Basin - - - - Bowers Basin - - - - 

O Aleutian Basin - - - - Aleutian Basin - - - - 

K Navarin Basin - - - - Navarin Basin - - - - 

S Norton Basin - - - - Norton Basin - - - - 

A Hope Basin - - - - Hope Basin - - - - 

T Chukchi Sea - - - - Chukchi Sea - - - - 

Beaufort Sea - - - - Beaufort Sea - - - - 

S Total 25.4 1,065.6 - 1,091.0 Total 0.8 - - - 

OK

Test Case Scenario Test Case Scenario

Present Value of Environmental Costs Annual Environmental Costs

National Cost Allocation National Cost Allocation

Commercial Fisheries Commercial Fisheries

($2000) (000) ($2000) (000)

Updated:

OCS Region

Planning Area Planning Area 2003 2004 ### 2057

N North Atlantic - - - - North Atlantic - - - - 

Mid-Atlantic - - - - Mid-Atlantic - - - - 

A South Atlantic - - - - South Atlantic - - - - 

Straits of Florida - - - - Straits of Florida - - - - 

T G Eastern Gulf 29.7 0.6 - 30.3 Eastern Gulf - 0.0 - - 

o Central Gulf 731.6 16.6 - 748.2 Central Gulf - 0.5 - - 

I M Western Gulf 281.7 8.2 - 289.9 Western Gulf - 0.3 - - 

Southern California - - - - Southern California - - - - 

O Central California - - - - Central California - - - - 

Northern California - - - - Northern California - - - - 

N Washington/Oregon - - - - Washington/Oregon - - - - 

Gulf of Alaska - - - - Gulf of Alaska - - - - 

A Cook Inlet 22.6 - - 22.6 Cook Inlet - - - - 

A Kodiak - - - - Kodiak - - - - 

L Shuimagin - - - - Shuimagin - - - - 

L North Aleutian Basin - - - - North Aleutian Basin - - - - 

Aleutian Arc - - - - Aleutian Arc - - - - 

A St. George Basin - - - - St. George Basin - - - - 

C St. Matthew Hall - - - - St. Matthew Hall - - - - 

S Bowers Basin - - - - Bowers Basin - - - - 

O Aleutian Basin - - - - Aleutian Basin - - - - 

A

T

L

P

A

C

Total

Total

7/6/00 1:24 PM

A

T

L

P

A

C

 Platform 

Effects 

 Pipeline 

Effects 

 Cost 

Subcat. 3 

 Cost 

Subcat. 3 

 Platform 

Effects 

 Pipeline 

Effects 

! 55 Years "

! 55 Years "

Figure 2.14 Cost Element Worksheet Output
Section 2: OECM User Guide 29

OECM Volume 1.doc 05/22/01 2:35 PM

The model automatically updates any changes to regional costs due to input driver, model

parameter, or import backout specification. National costs, on the other hand, must be manually

updated whenever any of these changes occur. The model provides some visual cues that

national cost need to be updated. When the grand totals of regional and national costs no longer

match, the model will prompt the user with a black box with bold red text reading “Update

National Costs,” as shown in Figure 2.15. The user should not assume that the absence of the

warning box necessarily means that national costs are current, however.10 Another cue to

whether national costs need to be updated is the date stamp applied to each national cost table, as

shown in the figure.

To update the national costs for a cost sector, simply depress the Update button. This triggers a

routine that updates the national cost table for the current cost sector. Depending upon computer

speed and the number of cost sectors in the model, the national costs update may take a few

minutes to complete. Do not disturb the spreadsheet while the model is running. It is OK to

switch to other applications or browse the web while the model is running, but Microsoft Excel

should be left alone. When the run is complete, a dialog box will pop up. Note that it only takes

marginally more time to update national costs for all cost sectors in the model than to update a

single sector. If changes have been made that potentially affect the national cost tabulation of

other costs sectors, the global national costs update should be run instead by depressing the

Update All button.

 

10 For example, if a change is made that affects the allocation of costs but not the grand total amounts, the national

costs update warning will not be triggered.

Figure 2.15 Updating National Costs
30 Section 2: OECM User Guide

OECM Volume 1.doc 05/22/01 2:35 PM

2.6.2 Summary Model Output

In addition to the individual cost sector output, summary results over all the model’s cost sectors

are calculated on the Summary worksheet. The top section of the Summary worksheet, depicted

in Figure 2.16, looks almost identical to a cost sector worksheet—except the calculations are

summing over all cost sectors in the model. E.g., the line for the North Atlantic is the sum of

environmental cost in the North Atlantic planning area over all of the individual cost sector

worksheets. The top section calculates regional costs, and the section below tabulates national

costs. The present value tables only contain one column because the cost subcategories on

individual cost sector worksheets are not necessarily uniform.

Note that the national costs table has a button similar to the individual cost sector spreadsheets to

perform the national costs update routine. The Update All button updates the national cost for all

of the model’s cost sectors.

Below this set of tables is another summary table that tabulates total environmental costs by cost

sector rather than by planning area. Of course, the grand totals will match those for the above set

of tables. This table contains one row for each of the up to 14 cost sectors that can be specified

in the model. If not all of the 14 potential cost sectors are included in the model, the table will

return rows with values of “FALSE” throughout them. This is by design. To hide the rows that

aren’t used in the model, click the Hide Unused Rows button. To display all rows, click the Show

All Rows button.

Verifying Summary Output and Using Recovery Formulas

Most Excel users have had the experience of inadvertently causing parts of or an entire

spreadsheet to return error values like #REF! or #VALUE! after moving cells, inserting or

deleting rows or worksheets, etc. The Summary worksheet is particularly susceptible to these

kinds of problems because it pulls its results from a number of worksheets and must adjust the

calculations as cost sector worksheets are added or removed. Using the macros on the

Assumptions worksheet to add and update cost sectors (see Section 2.5.1) and maintaining the

proper ordering of worksheets in the model (see “Do’s and Don’ts”) should prevent any errors on

the Summary worksheet. But if unforeseen problems occur, the following steps may be able to

recover the calculations.

1. The first step is to verify that formulas on Summary are including all of the appropriate

worksheets in their calculations. Click on any cell in the regional or national cost tables on

the Summary Worksheet and observe the formula in Excel’s formula bar (see figure below).
Section 2: OECM User Guide 31

OECM Volume 1.doc 05/22/01 2:35 PM

The =SUM(...) formula in the formula bar should include the names of two worksheets, in

this example Air and CommFish (i.e, commercial fisheries). Verify that all of the cost sector

worksheet tabs are arranged adjacent to one another, with no other model worksheet inbetween. Then verify that the worksheet immediately to the right of the CostSector

worksheet is the first reference in the =SUM function (Air in the example above) and that the

last cost sector worksheet in the model is the second reference in the =Sum function

(CommFish in the sample).

Test Case Scenario

Annual Environmental Costs

Regional Cost Allocation

Summary of Environmental Costs - All Cost Sectors

($2000) (000)

OCS Region Annual Totals

Planning Area 2003 2004 2005

R North Atlantic - - - - 

Mid-Atlantic - - - - 

E South Atlantic - - - - 

Straits of Florida - - - - 

G G Eastern Gulf 470,769 - 533 541 

o Central Gulf 4,654,583 - 4,093 52,496 

I M Western Gulf 1,472,596 - 1,265 16,356 

Southern California 3,907 - 2 8 

O Central California 5,468 - 3 11 

Northern California - - - - 

N Washington/Oregon - - - - 

Gulf of Alaska 58,459 - 89 1,207 

A Cook Inlet 57,426 - 180 1,923 

A Kodiak - - - - 

L Shuimagin - - - - 

L North Aleutian Basin - - - - 

Aleutian Arc - - - - 

A St. George Basin - - - - 

C St. Matthew Hall - - - - 

S Bowers Basin - - - - 

O Aleutian Basin - - - - 

K Navarin Basin - - - - 

S Norton Basin - - - - 

A Hope Basin - - - - 

T Chukchi Sea 499,924 - 604 925 

Beaufort Sea 211,512 - 209 1,177 

S Total - 7,434,644 6,978 74,643 

Recovery Formulas

Present Value Formula =sum('Air:Property'!g51)

Annual Total Formula =sum('Air:Property'!k51)

Test Case Scenario

Annual Environmental Costs

National Cost Allocation

Summary of Environmental Costs - All Cost Sectors

($2000) (000)

Updated:

OCS Region Annual Totals

Planning Area 2003 2004 2005

N North Atlantic - - - - 

Mid-Atlantic - - - - 

A South Atlantic - - - - 

Straits of Florida - - - - 

T G Eastern Gulf 470,094 - 532 540 

o Central Gulf 4,651,034 - 4,091 52,490 

I M Western Gulf 1,471,995 - 1,265 16,355 

Southern California - - - - 

O Central California - - - - 

Northern California - - - - 

N Washington/Oregon - - - - 

Gulf of Alaska 57,079 - 88 1,205 

A Cook Inlet 57,422 - 180 1,923 

A Kodiak - - - - 

L Shuimagin - - - - 

L North Aleutian Basin - - - - 

7/6/00 1:24 PM

Present Value

Present Value

A

T

L

P

A

C

A

T

L

P

A

C

! 55 Years "

! 55 Years "

Test Case Scenario

Annual Environmental Costs

Disabled

Summary of Environmental Costs - All Planning Areas

($2000) (000)

Annual Totals

Cost Sector 2003 2004 2005

Air Quality 6,896,007 - 6,652 73,648 

Commercial Fisheries 1,091 - 1 7 

Ecology and Recreation 530,180 - 283 942 

Subsistence Resources 7,366 - 42 45 

Infrastructure and Fiscal Impacts - - - - 

Property Values - - - - 

Total - All Cost Sectors - 7,434,644 6,978 74,643 

Present Value

Figure 2.16 Summary Worksheet Results
32 Section 2: OECM User Guide

OECM Volume 1.doc 05/22/01 2:35 PM

2. If there are any error values in the summary tables, or if the results of (1) are not as expected,

click the Fix Summary Tables button. This triggers a routine that attempts to recover the

proper formulas in the summary tables.

3. If the macro fails, the formulas can be manually updated. The technique for doing so is

described in a cell comment below each summary table, as shown in Figure 2.17.

Figure 2.17 Using the Recovery Formulas on the Summary Worksheet
Section 3: Structure of the OECM 33

OECM Volume 1.doc 05/22/01 2:35 PM

3. Structure of the OECM

This section provides a more detailed description of the calculations and structure of the OECM

model components than is provided in the User Guide (Section 2). It is intended to be used as a

technical addendum to the User Guide for the analyst wanting more insight into how the

numbers in the model arise. This section is not intended to function as a step-by-step description

of how the model is used; it does not replace the User Guide. Rather, each of the components of

the model is described in detail.

The components of the model and the section in this document where they are discussed are

shown in the table below.

OECM Component Function Described in 

Section

All spreadsheets Common components 3.1

OECM.xls Estimates environmental costs. 3.2

E&D.xls Retrieves E&D scenarios from 

database, generates impact driver 

variable series.

3.3

E&D.mdb Database. Stores E&D scenarios. 3.4

Query E&D data.dqy Query containing SQL code linking 

E&D.mdb and E&D.xls.

3.5

Accessory Workbooks Perform miscellaneous operations. 3.6

Figure 3.1 Overview of OECM Components

3.1 Common Components of Excel Spreadsheets

Although the complete OECM is contained in a number of different files, the general approach to

calculations and presentation is consistent throughout. This section describes the general

approach to model calculations, why the approaches were used, and gives a brief description of

some of the less-common spreadsheet functions the user must be familiar with to fully

comprehend the calculations taking place or to make modifications.

The MMS specified a minimum set of specifications for the OECM compared with the 1991

GPECM model. These have all been met:

• It runs in Microsoft Excel 97.

• The use of macros has been minimized. Macros are utilized only where their use is

unavoidable or for non-critical functions. Macros are self-documented in the Visual Basic

modules.

• It has been designed to be far easier to comprehend and use than the 1991 model.
34 Section 3: Structure of the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

• It generates annual and present-value costs for a variable number of cost sectors for each of

26 OCS planning areas. The model structure is flexible in that addition or deletion of cost

sectors is possible with only minor adjustment.

• A variable, 55-year forecasting period is employed to allow the model to simulate any 5-year

program period and the following 50 years.

• The model tabulates environmental costs both in the OCS planning area where the

environmental injury occurs (regional costs), and in the region where the oil or gas resource

is produced (national costs).

• The environmental effects of reductions in imported oil resulting from OCS production can

be calculated.

These specifications should be kept in mind when reviewing the model’s content in the following

sections.

3.1.1 General Approach to Calculations

The approach to mathematical calculations in the model involves balancing three potentially

incompatible goals:

• Enable the spreadsheet to automatically update all baseline model calculations dynamically,

which begs economy in the amount of spreadsheet cells used to keep recalculation times

acceptable.

• Make the calculation process intuitive to the user, which requires that not too many

calculations be performed in each cell.

• Design the model so that it is flexible in the ability to add or remove cost sectors, change the

impact drivers that determine environmental damage, etc. This requires that the model be

“over-built” in order to accommodate potential future needs.

The resulting model structure embodies a reasonable compromise between these three goals.

The model spreadsheets require fewer calculations, use less space in the worksheet, and calculate

faster than the 1991 model. It is also more flexible, comprehensible, and fault-tolerant. It

departs from the 1991 model on three fronts:

• Cells are dynamically linked (with certain exceptions) so model results are instantly updated

when input parameters are changed. Macros are not required to update the core model

results.

• Thought was put into efficiently combining spreadsheet calculations in order to eliminate

needless intermediate calculations. Part of the reason the 1991 model is so large is that in

many cases the authors used an extraordinary large number of intermediate calculations to

get to their final result. The structure of the new model is more efficient yet still provides a

clear flow of operations.

• Array and matrix multiplication operations get significant use in the model. The advantages

of this approach are many:
Section 3: Structure of the OECM 35

OECM Volume 1.doc 05/22/01 2:35 PM

- A single formula can perform thousands of calculations and calculate numerous cells

simultaneously. For example, many ranges in the model contain a single array formula,

entered one time, but generating results for 55 years over 26 planning areas—almost

1,500 cells.

- Potential for an erroneous calculation is minimized because a formula only has to be

entered once, as opposed to 1,500 times in the example described above.

- Matrix calculations in particular are very efficient in the amount of “number crunching”

that can be done over a small range of cells. This minimizes the size of the spreadsheet

and the calculation time, letting the computer’s FPU do the work without having

constantly to update large swaths of cells on the screen. Large spreadsheets are often

slow and unstable not due to the amount of mathematical computation taking place, but

because the system must redraw thousands of cells on the screen with each calculation,

overtaxing the system’s memory and video resources.

3.1.2 Specific Spreadsheet Functions

Some of the formulas found in the model may be unfamiliar to some spreadsheet users. The

most prevalent ones are described here.

Array Calculations

Array calculations are able to perform large numbers of calculations in a small number of cells.

They are required for matrix multiplication operations. The following is from the Microsoft

Excel 97 Help:

About array formulas and how to enter them

An array formula can perform multiple calculations and then return either a single result or multiple results.

Array formulas act on two or more sets of values known as array arguments. Each array argument must have

the same number of rows and columns. You create array formulas the same way that you create basic, singlevalue formulas. Select the cell or cells that will contain the formula, create the formula, and then press

CTRL+SHIFT+ENTER to enter the formula.

Enter an array formula

When you enter an array formula, Microsoft Excel automatically inserts the formula between { } (braces).

1 If the array formula will return one result, click the cell in which you want to enter the array formula. If the

array formula will return multiple results, select the range of cells in which you want to enter the array

formula.

2 Type the array formula.

3 Press CTRL+SHIFT+ENTER.

Matrix Multiplication (MMULT) Worksheet Function, TRANSPOSE function

Many of the core calculations of the model involve matrix multiplication. The individual

elements of a matrix product A of the matrices B and C are defined as follows:

∑=

=

n

k

Ai j BikCkj

1

,
36 Section 3: Structure of the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

where i is the row number, j is the column number, and n is the number of columns in matrix B

and rows in matrix C. Matrix operations must be entered as array formulas, and multiple

operations can be included in the same formula. Often it is necessary to transpose the row and

column elements of matrices for them to have the proper row by column orientation. This is

accomplished using Excel’s TRANSPOSE function.

E.g., a formula to calculate the regional costs for a planning area, defined as:

=TRANSPOSE(MMULT(MMULT(TRANSPOSE(m_drivervar_r1),m_coef_r1),v_3x1))*YearDollarConv

first computes the matrix product of the transpose of m_drivervar_r1 and m_coef_r1, multiplies the

resulting product times the vector v_3x1, transposes the result, and finally multiplies the resulting

matrix times a scalar YearDollarConv. This one formula is all that is required to perform the

thousands of calculations involved in determining the 55-year series of environmental costs for a

single planning area. This efficiency of calculation is why matrix operations see wide use in the

OECM.

Named Ranges in Formulas

To the extent possible in the calculations, cells are referenced by named ranges rather than by

cell addresses. The advantages are twofold:

(1) ranges are given descriptive names11 which may provide a more intuitive understanding of

the formulas in individual cells in the model; and

(2) formulas and macros that reference cell ranges are generally more resilient to changes in a

spreadsheet when they reference named ranges as opposed to ephemeral hard-wired cell

addresses.

INDIRECT Worksheet Function

Range names are also frequently used with the INDIRECT() function. Again, from Excel 97

help:

INDIRECT

Returns the reference specified by a text string. References are immediately evaluated to display their contents.

Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula

itself.

Syntax

INDIRECT(ref_text)

 

11 To keep range names an acceptably length, a shorthand naming approach is used. While the range names may at

first appear obtuse, they have been created using a systematic approach that shouldn’t be hard for the informed user

to understand. The casual user would likely have no need to work with named ranges at all. The following

shorthand notation is used:

v_ means the range is of a [1 x n] or [n x 1] dimension; i.e., like a vector;

m_ means the range consists of multiple rows and columns; i.e., [m x n], like a matrix;

_r# (# means there is a number there) means the OCS Planning Area, or region number 1 through 26.

So, for example, the range “m_drivervar_r22” refers to the matrix of impact driver variables for Planning Area 22,

the Navarin Basin.
Section 3: Structure of the OECM 37

OECM Volume 1.doc 05/22/01 2:35 PM

If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.

When you create a formula that refers to a cell, the reference to the cell will be updated if the cell is moved by

using the Cut command to delete the cell or if the cell is moved because rows or columns are inserted or

deleted. If you always want the formula to refer to the same cell regardless of whether the row above the cell is

deleted or the cell is moved, use the INDIRECT worksheet function.

CONCATENATE Worksheet Function

Concatenation functions build text strings by combining other text strings or numbers. The

CONCATENATE function is often combined with the INDIRECT function to return the named

range defined by the string developed in the concatenation function. E.g., the following segment

of a calculation in E&D.xls:

INDIRECT(CONCATENATE("Schedules!m_sched_",ED_Years_r2_y5,"_y5"))

Is evaluated by Excel as follows:

1. The CONCATENATE operator returns the string “Schedules!m_sched_35_y5” because the

value of the named range ED_Years_r2_y5 (the activity period associated with the lease

sales in Planning Area 2, lease sale year 5) is 35 years.

2. The INDIRECT function returns the values associated with the named range

“Schedules!m_sched_35_y5”.

3.1.3 Visual Basic for Applications (VBA) Code and Controls

VBA is the programming language Microsoft has integrated into all of its Office applications.

VBA routines can provide much more flexibility than regular Excel worksheet functions in

performing calculations and designing a user environment. The model uses many VBA routines,

though mostly for non-essential functions. To view the VBA code associated with an Excel file,

open the Visual Basic Editor (shown in Figure 3.2) by keying Alt-F11 while in Excel.

VBA procedures are often activated by controls such as buttons and checkboxes. All the buttons

found throughout the OECM trigger VBA routines that can be viewed in the Visual Basic Editor.

To determine what macro is triggered by a button in a spreadsheet, right-click the button and

choose “Assign Macro,” as shown in Figure 3.3. The user is then presented with a list of all

macros in currently open projects, with the macro associated with the current control highlighted.

Pressing the “Edit” button will transport the user to the location of the macro in the Visual Basic

Editor. In the figure, the user is transported to the location of the Modify_Import_Allocations

macro. As shown in the bottom panel of the figure, the VBA routines employed in the OECM

are self-documenting. While there should be no reason for users to need to alter (or even view)

VBA code, those with a programming background will find the Visual Basic syntax quite

straightforward and can gain additional insights into model operations by reviewing it.

3.1.4 Model User Interface

Model components are designed with unique color-coding attributes to differentiate user-input

cells from calculated cells, cells imported from other workbooks, etc. The model also makes use

of cell comments and cell input validation to explain what formulas are doing and to prevent
38 Section 3: Structure of the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

users from inadvertently entering inappropriate values into input data cells. The user interface is

explained in detail in Section 2.2 of the User Guide.

Figure 3.2 The Visual Basic Editor
Section 3: Structure of the OECM 39

OECM Volume 1.doc 05/22/01 2:35 PM

Figure 3.3 Finding the Macro Associated with A Spreadsheet Control

3.2 OECM.xls Workbook

OECM.xls contains all of the core model calculations. The structure of the workbook is depicted

in Figure 3.4. The workbook consists of six worksheets, plus one worksheet for each of up to 14

cost sectors specified in the model. OECM.xls also contains Visual Basic modules.

3.2.1 Assumptions Worksheet

This worksheet performs two basic functions:

1. It contains global model settings such as scenario names, discount rates, and import backout

settings; and

2. It maintains a table of cost sector worksheet names, the count of worksheets, and the

interface through which cost sectors are added to the model.
40 Section 3: Structure of the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

Proper maintenance of the table of cost sector worksheet names, shown in Figure 3.5, is critical

to model operations. In particular, the VBA routine that estimates national costs relies on the

contents of the “Worksheet Names” column and the correct count of cost sectors. The VBA

routine will fail or provide incorrect results if any of these data are inaccurate. To assist the user

in maintaining this table, a macro attached to the “Update Table” button refreshes its contents

with the current state of the model. When the user performs spreadsheet actions like adding or

removing cost sector worksheets, a prompt is presented reminding her to update/check the table.

If the macro fails, the table can be manually updated.

The macro attached to the “Add New Cost Sector” button will create a new, blank cost sector

worksheet by copying the CostSector worksheet to the end of the workbook and refreshing the

worksheet names table and cost sector count. The steps to adding a new worksheet are described

in Section 2.5.1 of the User Guide.

Ranges 

Worksheet

Global 

Assumptions Impact Drivers

Etc.

Property Values

Fisheries

Air Quality

Individual Cost 

Sector 

Worksheets

Inputs

Calculations

Results

Assumptions Drivers Oil Spills

Oil Spill 

Calculation/ 

Regional 

Allocation

Summary Results 

by Planning Area 

& by Cost Sector

Summary

Others

CostSector 

Worksheet

Visual Basic 

Modules

Figure 3.4 Structure of the OECM.xls Workbook

Worksheet names are shown in Italics.
Section 3: Structure of the OECM 41

OECM Volume 1.doc 05/22/01 2:35 PM

3.2.2 Oil Spills Worksheet

Most of the environmental cost impact drivers are provided exogenously from the E&D

scenarios. Oil spills, however, are calculated within the OECM. All oil spill calculations are

found on the Oil Spills worksheet. A flow diagram of the oil spill and resulting damage

calculations is shown in Figure 3.6. An enhancement over the 1991 model is the use of the

NRDAM/CME to track oil spill fates, and the separate tracking of tanker and platform/pipeline

spills. The distinction between oil spilled from tankers versus stationary sources is important

due to the ability of tankers to spill oil in a Planning Area other than where the oil was produced.

The ultimate product of the oil spill calculations are four OECM impact driver variables: the

amount of oil spilled and the amount of spilled oil reaching shore from two types of spill

sources: (1) platforms and pipelines; and (2) tankers.

Global Oil Spill Parameters

The oil spill input section is found on the top section of the Oil Spills worksheet. The user

provides estimates of the average frequency of oil spills per BBO produced for platforms,

pipelines, and tankers; the average size of each spill; and the fraction of spilled oil that makes it

to shore. These inputs are based on oil spill scenarios and associated spill fates from the

NRDAM CME model.

The sources of oil transport-related spills are specified through the “Oil to Shore Allocations.”

These specify whether oil is transported to shore via tanker, pipeline, or some combination of the

Figure 3.5 Table of Cost Sector Worksheet Names
42 Section 3: Structure of the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

two. The spill parameters are assigned for each planning area. In addition, a “multiple

shipment” factor can be assigned to imported oil to scale-up oil spills if oil is transshipped.

From these data the amount of oil spilled and oil onshore is calculated for each planning area and

spill source per BBO of production. These are the values that, with some refinements, are

multiplied by oil production to arrive at the 55-year oil spill impact driver series.

Spill Location

Because oil produced in one region may spill from a tanker in another, the oil spill risk is

distributed among all affected OCS Planning Areas. This function is only used for Alaskan oil,

which is tankered down the West Coast, thus creating spill risk outside the producing area. The

Impact Drivers

Oil Produced

Amount Spilled

-Tankers

Spill Parameters 

by Spill Source

-Spills / BBO

-Avg. spill size

Tanker Oil Spill 

Allocation Matrix

NRDA Type A 

Model

Distribution of 

Spilled Oil

Physical Fate of 

Spilled Oil

Damage from Oil 

Spills Economics 

Literature

- Damages in 

Dollars

NRDA Type A 

Model

- Ecological 

Damages

Inputs Calculations

Cost Element Worksheets

Oil Spills Worksheet

Inputs Worksheet

Worksheet

Amount Spilled

-Pipelines & 

Platforms

Import Backout

Subtract Foreign 

Oil Spill Amounts if

Enabled

Figure 3.6 Overview of Oil Spill Modeling Steps
Section 3: Structure of the OECM 43

OECM Volume 1.doc 05/22/01 2:35 PM

parameters are input into a 26 x 26 matrix, with rows being the location of production and

columns being the location of spill risk. This is called the “Tanker Oil Spill Risk Allocation

Input Area.” For most planning areas with no outside-of-region spill effects, the matrix has a 1

on the diagonal (i.e., like an identity matrix). Only for Alaskan production are there off-diagonal

elements to the spill allocation matrix.

The next section below is called the “Regional / National Costs Tanker Oil Spill Risk Allocation

Matrix.” This matrix looks similar to the “Tanker Oil Spill Risk Allocation Input Area,” except

the cells are calculated rather than user-input. The calculated cells in this matrix dynamically

create variations of the “Tanker Oil Spill Risk Allocation Input Area” for use in, among other

things, the calculation of National Costs (described in detail in Section 3.2.6).

Imported Oil Backout

Oil produced from the OCS decreases the amount of oil imported into the United States,

reducing the amount of imported oil spilled from tankers. The user has the option of calculating

the offsetting environmental effects of decreases in imported oil resulting from OCS production.

The imported oil backout uses the following input parameters:

1. The distribution of foreign oil imports into the United States: Similar to the OCS spill risk

allocations, these settings specify the planning areas where imported oil arrives into U.S.

waters. These are specified in an input area called the “Imported Oil Distribution.” Three

alternative distributions can be specified (the user chooses one on the Assumptions

worksheet). The “Base” distribution data come from the 1999 Petroleum Supply Annual.

2. The fraction of spill risk occurring in the U.S: Since oil tankers may be travelling from across

the world and only entering U.S. waters on their final approach to port, the spill risk is

discounted to the amount expected to occur in U.S. waters. This is entered in the cell labeled

“U.S. Share of Imported Oil Tanker Spills.” The default value is 50%, carried forward from

the 1991 model.

3. An adjustment for oil transshipment: If oil is transshipped from tankers to barges, spill risk is

increased. The user can specify a factor for each of the 26 planning areas. The default value

is 1—no effect.

To calculate, the user simply chooses whether to enable the imported oil backout on the

Assumptions worksheet, after which the model automatically updates all cost calculations. When

the backout is enabled, the model adjusts the amount of spilled oil and spilled oil reaching shore

for decreases in imports. The labels of all relevant tables automatically change to indicate that

imported oil backout is enabled.

Because some planning areas that receive imported oil have little or no OCS activity, it is

possible that the imported oil backout will yield negative amounts of spilled oil in these areas,

and corresponding negative environmental costs.
44 Section 3: Structure of the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

Oil Spilled and Oil on Shore Impact Driver Variables

These impact driver variables are created endogenously in the model for (1) tanker spills and (2)

platform and pipeline spills. Tanker spills are the more complex of the two. There are two

major components to the calculation:

Spills of imported oil distribution of oil imports

Oil production spills per BBO distribution of spillsTanker spills annually by planning area

×

× ×

=

The second part of the calculation, which calculates the offsetting decreases in oil spills due to

fewer oil imports, is only activated if the import backout is enabled.

Describing the above calculation in more detail:

[ ]

[] []

[TR]Imported oil distribution [26 x 1] annual imported oil offset [1 55]

spilled oil per BBO for each planning area [26 x 1]

Spill risk allocation matrix 26 x 26 annual production by region 26 x 55

Tanker spilled oil annually by planning area 26 x 55

1

× × ×

+

×

× ⊗

=

IOFactor

binary

Where binary1 is a switch that sets the first stage of the calculations to zero if the user has

specified to view the Import Backout effects only, IOFactor is the fraction of spill risk occurring

in U.S. waters as described above, and ⊗ means a simple array multiplication operation as

opposed to a matrix product. The annual imported oil offset is the sum of OCS production in all

regions multiplied by the “U.S. Share of Imported Oil Tanker Spills” parameter described above.

Spilled oil on shore is calculated identically, with the array of spilled oil making it to shore

substituted for the 26 x 1 array of spilled oil per BBO.

The spill calculations for pipelines and platforms are simpler because they are not affected by the

import backout, and all oil is spilled in the Planning Area of production. These calculations are

simply a linear combination of oil production annually by planning area and the corresponding

amount of oil spilled per BBO of production.

The ultimate results of the calculations on the Oil Spills worksheet are four [26 x 55] arrays:

1. Oil spilled from tankers by planning area and year;

2. Spilled oil from tankers making it to shore by planning area and year;

3. Oil spilled from platforms and pipelines by planning area and year; and

4. Spilled oil from platforms and pipelines making it to shore by planning area and year.

These arrays are used to populate the oil-spill related impact drivers found on the Drivers

worksheet. From this point, damages from oil spills are calculated in the same fashion as

damages based on any other of the impact drivers.
Section 3: Structure of the OECM 45

OECM Volume 1.doc 05/22/01 2:35 PM

3.2.3 Ranges Worksheet

This worksheet contains various ranges of information that are referenced in calculations

throughout the OECM. The user should seldom have any reason to view or modify information

on the Ranges worksheet. Its contents include the following:

• The list of impact driver variables and associated units of measure (if the user chooses to add

or modify impact drivers, the changes are made here);

• Special arrays/vectors used to perform various functions in matrix algebra calculations;

• A listing of the calendar years corresponding to the 55-year forecasting period, and the

associated factors used to convert annual data to present values;

• The GDP deflator series used to convert dollars between different base years (this series can

be updated as new GDP data are released);

• The listing of Planning Areas and associated OCS Regions;

• Cells that track whether the import backout is enabled and generate text strings used to label

output tables;

• The factor used to convert BCF of gas to BBOE of oil; and

• The version number of the model.

3.2.4 Drivers Worksheet

This worksheet contains all of the impact drivers used in OECM cost calculations. It contains 26

[20 x 55] matrices of impact driver variables, one per OCS planning area. The rows of the

matrices are the twenty impact driver variables, and the columns are the values of impact driver

variables for each of 55 years. The first 15 of 20 rows of impact driver variables are directly

linked to cells in the E&D.xls workbook. If E&D.xls is open when the user opens OECM.xls, the

cells will automatically refresh to the current values in E&D.xls. If E&D.xls is not open when

OECM.xls is opened, the user will be presented with a prompt asking if the user wants to update

the linked cells. Choosing “Yes” will update the cells in OECM.xls.

The last five rows in each Planning Area’s list of impact drivers relate to BBOE production, oil

spilled, and spilled oil making it to shore. These cells are calculated endogenously on the Oil

Spills worksheet within OECM.xls.

3.2.5 CostSector Worksheet

The CostSector worksheet is the template from which cost sectors are added to the model. This

worksheet must never be deleted. All of the impact driver coefficients on the worksheet are set

to zero to prevent calculations on it from being erroneously added to the results from defined
46 Section 3: Structure of the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

cost sectors. If the user wishes to make a global adjustment to the model’s cost sector

worksheets, the most fail-safe technique to do so would be to do the following12:

1. Remove all of the defined cost sector worksheets from the model (the impact driver

coefficients should be stored elsewhere during this process so they can later be restored);

2. Make any modifications to the CostSector template worksheet;

3. (If desired) Change the version number of the model on the Ranges worksheet to differentiate

the new versions of the cost sectors from the old;

4. Generate new copies of the cost sector worksheets using the “Add New Cost Sector” button

found on Assumptions (this is described further in Section 2.5.1 of the User Guide); and

5. Restore the impact driver coefficients to the proper cost sectors.

An accessory workbook, Coefficient Storage.xls, is provided in the Utilities subdirectory of the

model’s directory to streamline this process. This is an unsupported feature described more fully

in Section 3.6.2.

3.2.6 Environmental Cost Sectors

How to specify environmental cost sectors and impact driver variable coefficients, and use them

to generate model results, is described in Sections 2.5.3 and 2.6 of the User Guide. The model

can accommodate up to 14 cost sectors. Each cost sector is designed with the following

specifications: (The cost sector data input sheet shown in Figure 3.7 should illuminate the

structure.)

• Up to three subcategories of costs can be specified for each cost element. In the sample in

Figure 3.7, commercial fisheries impacts are divided between Platform Effects, Pipeline

Effects, and an unused third category.

• Each cost subcategory can be a function of up to three driver variables.

• Impact coefficients can be specified for each of 26 OCS planning areas.

• There are twenty potential impact driver variables, each of which is developed for a 55-year

period based on the E&D scenarios (found on the Drivers worksheet) imported from E&D.xls.

Note that there are drivers for oil production, gas production, and BOE-equivalent

production. The latter variable might be used as a proxy for overall activity, whereas BBO

would drive oil spill-related effects. Three unused impact driver variables are available for

future use in the model. They can be added should the analyst want to specify costs based on

an impact driver not currently included in the active set.

 

12 It is recommended to save a copy of the original OECM.xls in case something goes awry or if the changes made to

the CostSector template produce errors or other unexpected results. Due to the complex nature of this model,

changes to the CostSector template could easily cause unexpected output or render some model functions

inoperable. This technique should only be employed when absolutely necessary.
Section 3: Structure of the OECM 47

OECM Volume 1.doc 05/22/01 2:35 PM

Regional Damage Cost Functions

The following describes how costs are calculated for a single environmental cost sector. All cost

sectors are calculated in an identical fashion.

From the input driver coefficients entered above, a series of 26 [20 × 3] matrices are created, one

for each planning area. The (row i, column j) elements of these matrices are (impact driver

coefficient, cost subcategory). That is, each of the three columns is composed of the total

damage resulting from a unit of each of the twenty impact driver variables. These are the Impact

Driver Coefficient Matrices referred to below.

Environmental costs are tabulated two ways:

1. By present value for each cost subcategory and planning area (i.e., 26 x 3). This output is

composed of 26 vectors, one for each planning area, calculated as follows:

Figure 3.7 Sample Cost Sector Impact Driver Coefficients
48 Section 3: Structure of the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

[ ]

[ ] []

 present value factors[ ] 55 x 1

Impact Drivers 55 x 20 Driver DamageCoefficient Natrices 20 x 3 TR 

55 - year Present Value of Costs by Planning Area 1 x 3

×

× ×

=

Where “TR” means the matrix was transposed to have the proper row x column dimensions

for multiplication.

2. Annually for each planning area over all cost subcategories (i.e., 26 x 55). This output is

composed of 26 vectors, one for each planning area, calculated as follows:

[ ]

[ ] []

aggregation vector [ ] 3 x 1 [TR]

Impact Drivers 55 x 20 Driver DamageCoefficient Matrices 20 x 3

AnnualCosts for Planning Area 1 x 55

×

× ×

=

Where the aggregation vector is simply a vector of 1’s used to sum over the three cost

categories. The result of this calculation is then transposed to a [1 × 55] vector. This

calculation is performed for each of the 26 planning areas.

National Costs

As discussed above, the model’s default regional costs tabulation counts costs in the planning

area where they occur. The national cost tabulation, on the other hand, counts environmental

costs based on the area where the production took place. For example, oil produced in Alaska is

tankered from Valdez down the West Coast to California, adding spill risk to California as well

as the Alaskan production area. Accordingly, oil spill effects in California result from the

commingled production of multiple planning areas in Alaska as well as from California’s own

production. This is only relevant for oil spills—all other environmental costs are assumed to

occur in the planning area where production occurs.

Mathematically, regional costs are defined as follows:

∑= ≠

= +

26

1,

Regional Cost AllCosts , Tanker SpillCosts r r i

i i i r,i

Where i and r represent each of 26 OCS Planning Areas, the first subscript in the cost elements

on the right-hand side refers to the location of production, and the second subscript represents

the location of damages. The first term in the equation counts all environmental costs associated

with all activities in planning area i. The second term counts the damages occurring in planning

area i from oil that is produced elsewhere but spilled in planning area i.

National costs are defined (using the same notation as above):

∑= ≠

= +

26

1,

NationalCost AllCosts , Tanker SpillCosts ,

r r i

i i i i r
Section 3: Structure of the OECM 49

OECM Volume 1.doc 05/22/01 2:35 PM

Note that in the national costs equation, the second term is counting the oil-spill related damages

from production in Planning Area i that spills in any/all other Planning Areas.

The Excel spreadsheet does not provide the capability to prize national costs from the default

regional cost calculations. To estimate national costs, a VBA technique was developed to

consider the environmental costs of oil spills from a planning area regardless of where they

occur, and to separate these costs from those arising from production in other planning areas.

The process to extract national costs for a single cost sector is shown in Figure 3.8. The steps in

the diagram are as follows:

1. Tanker spills are set to zero for all Planning Areas. This is accomplished by multiplying the

“Regional / National Costs Tanker Oil Spill Risk Allocation Matrix” on the Oil Spills

worksheet by a vector of zeroes.

2. The grand total annual environmental costs over all 26 Planning Areas without tanker spills

are stored.

3. The annual environmental costs without tanker spills are stored for each of the 26 Planning

Areas.

The next steps occur in a loop for each of the 26 Planning Areas

Set tanker spills to 

zero in all planning 

areas

Store total costs 

over all planning 

areas [1 x 55]

Activate tanker 

spills in planning 

area i (only)

Store total costs 

over all planning 

areas [1 x 55]

Calculate 

difference: 

Step 5 - Step 2

123

4

Store costs for 

each planning 

area [26 x 55]

5 6

Add difference to 

costs from (3) for 

planning area i

7 Output to national 

costs table for 

planning area i

8

Next i

For i = 1 to 26

Figure 3.8 Calculating National Costs
50 Section 3: Structure of the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

4. Tanker spills are re-enabled in Planning Area i only.

5. The grand total environmental costs are again recalculated (as in Step 2), except this time

these costs include oil spill damages from production in Planning Area i. This is the only

difference between the totals from steps (2) and (5).

6. The total annual environmental costs from step 2 are subtracted from those in step 5. This

[1 x 55] array represents the costs associated with oil produced in Planning Area i that is

spilled in other planning areas.

7. The result from step 6 is added to the corresponding [1 x 55] array for Planning Area i

calculated in step 3. These are the national costs for Planning Area i.

8. The national cost results are output back to the cost sector spreadsheet.

9. A similar set of calculations is performed for the present values tables.

The procedure described above is triggered by the National_Costs macro. Steps 4 – 8 above are

repeated for each of 26 Planning Areas. Each of the 26 steps requires the spreadsheet to

recalculate. Because this calculation is fairly time-consuming, the national cost routine’s speed

would be unacceptable if these steps had to be performed for each cost sector worksheet in turn.

Instead, a variant of the algorithm (called Update_All_National_Costs) was developed that

performs similarly to that described above, except the variables and calculations are performed in

three dimensions instead of two, the third dimension being the identically-constructed, “stacked”

cost sector worksheets. Under this structure, all corresponding row elements of all cost sector

worksheets are processed in-between calculations of the spreadsheet, resulting in a time savings

of approximately 80 percent over a process that steps through individual worksheet one at a time.

The code for the Update_All_National_Costs routine is shown in Figure 3.9.13 Updating all model

sectors only takes about 30-40 percent longer than running a single cost sector, so the “Update

All” feature should be used if changes affect more than a single cost sector.

The model cannot determine with certainty whether the values shown in the national cost

summary table reflect the most recent changes to model inputs. However, the model compares

the sums of the national and regional costs, which should always be identical. If they are not, the

user is presented with an error message (“Update National Costs” in red with a black

background) prompting her to update the national costs. It is possible that the sums of regional

and national costs can be equal although the national costs allocation is not current. The absence

of the error message is a necessary but not sufficient indicator that national costs are up-to-date.

Due to the large number of calculations that take place and the need to recalculate the entire

workbook frequently during the macro routine, it can take several minutes to run. This begs that

the user only update national costs when necessary.

 

13 These national costs calculations are the only portions of the OECM that do not (1) dynamically update and (2)

require VBA code to accomplish. While other aspects of model operation are streamlined by use of the models

VBA macros, the macros are not absolutely necessary.
Section 3: Structure of the OECM 51

OECM Volume 1.doc 05/22/01 2:35 PM

Figure 3.9 The Update_All_National_Costs VBA Routine

Sub Update_All_National_Costs()

' Updates national cost calculations for all cost sectors in the model

'

' i Planning Areas

' j years

' k cost sectors

On Error GoTo Errorhandler

' Start timer

begintime = Now

'Application.ScreenUpdating = False

'Specify the number of cost sectors in the model

Dim n As Integer

 n = Worksheets("Assumptions").Range("CostSectorCount")

'Declare variables

ReDim annual_totals_baseline(55, n) 'total costs sans oil spills (baseline)

ReDim annual_totals(55, n) 'total costs with current planning area

ReDim annual_total_costs_diff(55, n) 'costs with current area minus baseline total costs

ReDim regional_costs(26, 55, n) 'total regional costs by planning area

ReDim nonspill_costs_active_area(55, n) 'current area costs with oil spill costs

ReDim natl_cost_current(55, n) 'national costs for current planning area

ReDim costmod(55, n)

Dim natl_cost_current_cv(55) As Variant 'output vector or national costs for active

planning area

'Declare variables pv

ReDim annual_totals_baseline_pv(3, n) 'total costs sans oil spills _pv(baseline)

ReDim annual_totals_pv(3, n) 'total costs with current planning area

ReDim annual_total_costs_diff_pv(3, n) 'costs with current area minus baseline total costs

ReDim regional_costs_pv(26, 3, n) 'total regional costs by planning area

ReDim nonspill_costs_active_area_pv(3, n) 'current area costs with oil spill costs

ReDim natl_cost_current_pv(3, n) 'national costs for current planning area

ReDim costmod_pv(3, n)

Dim natl_cost_current_pv_cv(3) As Variant 'output vector or national costs for active

planning area

Application.ScreenUpdating = False

'Reset regional costs

 Worksheets("Oil Spills").Activate

 Range("currentregion").Select

 Selection.Value = 0

 Range("national").Value = True

'Loop through cost sector worksheets

For k = 1 To n

 Let cursht = Worksheets("Assumptions").Range("CostShtName" & k).Value

 Worksheets(cursht).Activate

'Update last run time

 Worksheets(cursht).Activate

 Worksheets(cursht).Range("natl_costs_lastrun").Select

 Selection.Value = begintime

 calc_off

'STORE "BASELINE" REGIONAL COSTS RESULTS

'Store initial totals vector

 For j = 1 To 55

 Worksheets(cursht).Activate

 Worksheets(cursht).Range("v_total_cost_annual").Activate

 annual_totals_baseline(j, k) = ActiveCell.offset(0, j - 1)

 Next j

'Store initial totals vector pv

 For j = 1 To 3
52 Section 3: Structure of the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

Figure 3.9 (Cont) The Update_All_National_Costs VBA Routine

 Worksheets(cursht).Activate

 Worksheets(cursht).Range("v_total_cost_pv").Activate

 annual_totals_baseline_pv(j, k) = ActiveCell.offset(0, j - 1)

 Next j

'Store regional costs matrix

 For i = 1 To 26

 For j = 1 To 55

 Worksheets(cursht).Activate

 Worksheets(cursht).Range("m_annual_costs").Activate

 regional_costs(i, j, k) = ActiveCell.offset(i - 1, j - 1)

 Next j

'Store regional costs matrix pv

 For j = 1 To 3

 Worksheets(cursht).Activate

 Worksheets(cursht).Range("m_annual_costs_pv").Activate

 regional_costs_pv(i, j, k) = ActiveCell.offset(i - 1, j - 1)

 Next j

 Next i

Next k

'CALCULATE NATIONAL COSTS AND OUTPUT RESULTS TO WORKSHEET

'Cycle through 26 regions

For i = 1 To 26

 Worksheets("Oil Spills").Activate

 Range("currentregion").Select

 Selection.Value = i

'Update calculated values and then disable calculation

 calc_on

 calc_off

'Calculate current - baseline total

 For k = 1 To n

 Let cursht = Worksheets("Assumptions").Range("CostShtName" & k).Value

 Worksheets(cursht).Activate

 For j = 1 To 55

 Worksheets(cursht).Range("v_total_cost_annual").Activate

 annual_totals(j, k) = ActiveCell.offset(0, j - 1)

 annual_total_costs_diff(j, k) = annual_totals(j, k) -

annual_totals_baseline(j, k)

'Store active region costs with oil spills

 Worksheets(cursht).Range("m_annual_costs").Activate

 nonspill_costs_active_area(j, k) = ActiveCell.offset(i - 1, j - 1)

 natl_cost_current(j, k) = regional_costs(i, j, k) + _

 annual_total_costs_diff(j, k)

 Next j

'Calculate current - baseline total pv

 For j = 1 To 3

 Worksheets(cursht).Activate

 Worksheets(cursht).Range("v_total_cost_pv").Activate

 annual_totals_pv(j, k) = ActiveCell.offset(0, j - 1)

 annual_total_costs_diff_pv(j, k) = annual_totals_pv(j, k) -

annual_totals_baseline_pv(j, k)

'Store active region costs with oil spills pv

 Worksheets(cursht).Range("m_annual_costs_pv").Activate

 nonspill_costs_active_area_pv(j, k) = ActiveCell.offset(i - 1, j - 1)

 natl_cost_current_pv(j, k) = regional_costs_pv(i, j, k) +

annual_total_costs_diff_pv(j, k)

 Next j

'Output national costs by region

 'Fill vector with correct elements for each of 55 years for current cost sector and region

 For j = 1 To 55 'j years

 natl_cost_current_cv(j) = natl_cost_current(j, k) 'cv = current vector
Section 3: Structure of the OECM 53

OECM Volume 1.doc 05/22/01 2:35 PM

Figure 3.9 (Cont) The Update_All_National_Costs VBA Routine

3.2.7 Summary Worksheet

The Summary worksheet culls results from all individual cost sectors and planning areas. The

structure of the worksheet is explained in detail in Section 2.6.2 of the User Guide. The

calculations on the worksheet are straightforward: they simply sum over the grand total or OCS

Planning Area rows of the individual cost sector worksheets. The only complexity to the

calculations involves the 3-dimensional nature of the calculations—that is, they sum over

multiple worksheets. For more information on these formulas, consult Excel’s online help for

“3-D references.”

 Next j

 'Output vector to cost sector worksheet

 Worksheets(cursht).Range("v_natl_costs_r" & i).Value = natl_cost_current_cv

'Output national costs by region pv

 'Fill row with correct elements for each of 3 categories

 For j = 1 To 3 'j subcategories

 natl_cost_current_pv_cv(j) = natl_cost_current_pv(j, k) 'cv = current vector

 Next j

 'Output vector to cost sector worksheet

 Worksheets(cursht).Range("v_natl_costs_pv_r" & i).Value = natl_cost_current_pv_cv

 Next k

Next i

'Reset regional costs

 Worksheets("Oil Spills").Activate

 Range("currentregion").Select

 Selection.Value = 0

 Range("National") = False

'Finish Up

 Worksheets("Summary").Range("natl_costs_lastrun").Value = begintime

 calc_on

 Application.ScreenUpdating = True

 Worksheets("Assumptions").Activate

 Call FinishDialog

Exit Sub

Errorhandler:

 MsgBox ("An error occurred while processing cost sector " & costnum _

 & " planning area " & i & ". Resetting regional costs.")

' Place an error warning on summary worksheet

 Worksheets("Summary").Range("natl_costs_lastrun").Value = _

 "An error occurred in the last global cost update."

 'Reset regional costs

 Worksheets("Oil Spills").Activate

 Range("currentregion").Select

 Selection.Value = 0

 Range("National") = False

 calc_on

99 End Sub
54 Section 3: Structure of the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

To ensure that the results found on the Summary worksheet are accurate, the following conditions

must be met:

1. All of the cost sector worksheets must be arranged adjacent to one another in the workbook

(that is, the worksheet tabs must be adjacent to one another with no other worksheets in

between). This begs the following restriction on use of the model: Never rearrange the order

of worksheets in OECM.xls.

2. The first element of the 3-D formula must be the first cost sector in the model (that is, the one

directly to the right of the CostSector worksheet.

3. The second element of the 3-D formula must be the last cost sector in the model (this should

be the rightmost worksheet tab).

Because it is possible for these formulas to become scrambled if worksheets are added/removed

from the model, the User Guide Section 2.6.2 documents multiple approaches to ensuring the

integrity of the calculations.

3.2.8 VBA Modules

OECM.xls contains two VBA modules, GUI and Model_Operations. GUI contains VBA routines

which perform tasks such as loading the “About” dialog box and transporting the user about the

workbook. Model_Operations contains routines that perform more significant actions such as the

national cost calculations.

3.3 E&D.xls Workbook

The E&D scenarios provided by MMS’s Resource Evaluation Division provide the fundamental

data on OCS operations that are used to drive the OECM. However, the data need to be

converted into a form that allows for estimation of annual impacts. E.g., To estimate annual

costs, however, the OECM needs to know how many platforms are installed, operating, and

removed in each year of the 55-year forecasting period. E&D.xls performs this data conversion.

It has two major functions:

1. It imports E&D scenarios from the database E&D.mdb; and

2. It converts the E&D scenario data into 55-year series of OECM impact drivers.

The link to the database is optional—data can be manually entered into E&D.xls. However, the

database link enhances the model’s flexibility, allowing E&D scenarios to be quickly cycled in

and out of the model. This section provides additional detail on the spreadsheet not found in

Section 2.4 of the User Guide. Refer to the User Guide for a description of how to use the file.

3.3.1 E&D Data Worksheet

This worksheet performs two primary functions:

1. It houses the E&D scenario data imported from the database (or manually entered); and
Section 3: Structure of the OECM 55

OECM Volume 1.doc 05/22/01 2:35 PM

2. It distributes them by planning area and by lease sale year for subsequent conversion to

impact driver series.

Database Link

The top section of the worksheet contains the E&D scenarios imported from the database. The

checkbox control labeled “Access Link Enabled” on the top of the worksheet invokes a Visual

Basic routine that either enables or disables an ODBC link to E&D.mdb. The “Refresh” button

runs the query. Both operations require the use of VBA routines and the query definition found

in the file Query E&D Data.dqy. Unfortunately, Microsoft’s implementation of the query

functionality is not very resilient to variations in the user’s environment and machine

configuration. Although the VBA routines have been designed as flexibly as possible and with

some error trapping capability, and have been tested under a number of environments, it is

possible that the user will have to redefine the query link manually. For this, we refer the user to

Excel’s online help on “What you need to retrieve external data.” The Access ODBC driver

must be installed to run the query, and MS Query must be installed to make any modifications to

it. The VBA routines that enable and refresh the query are named EnableQuery and

Refresh_ED_query, respectively. See Section 3.5.1 for information regarding troubleshooting the

Access link.

Distribution of E&D Data

The lower portion of the E&D Data worksheet begins the process of arranging the E&D scenario

data into a form from which impact driver series can be calculated. Because lease sales can

occur in any year of the 5-year program, this section of the spreadsheets tracks each lease sale

year-planning area combination separately. That is, E&D scenario data are tracked for 26

planning × 5 lease sale years for each. The ranges are populated using Excel SUMIF functions

with the appropriate criteria to retrieve the desired values. Matching of Planning Area-lease sale

year combinations is achieved through assigning a unique code to each of the 26 x 5 potential

combinations in the database. This column in the database, called Loc_yr_code, is calculated as

follows:

Loc_yr_code = ( ) Planning Area Number -1 × 5 + Lease Sale Year

The proper row in the E&D Data worksheet is populated when the number in the far right column

of the lower portion of the worksheet, labeled “Loc_yr_code”, matches the value in the

Loc_yr_code column of the imported E&D database record.

3.3.2 Schedules Worksheet

Each E&D scenario has associated with it a period of activity. These range from 25 to 50 years

in 5-year increments. For each of these periods of activity, unique schedules of oil and gas

production, installation (and later, removal) or platforms, etc. are developed. These are referred

to as scheduling matrices. They are found on the Schedules worksheet. There are no calculated

values on this worksheet; the numbers are calculated in the unsupported accessory workbook
56 Section 3: Structure of the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

E&D Schedules.xls14 and pasted as values into E&D.xls. The only characteristic of the schedules

that bears explanation is that the activity years include negative years as well as positive. This is

done to make a single set of scheduling matrices accommodate the different activity timings that

results when lease sales occur in different years. There are thirty potential scheduling matrices

available: the six activity periods (25, 30, 35, 40, 45, 50) × each beginning in lease sale years 1 to

5. In essence, the scheduling matrices are “shifted up or down” to mimic what the schedule

would be in various start years. Refer to the table on the far left of the Schedules worksheet

labeled “Lease Sale Year-Activity Year Correspondence” to decode the activity periods

associated with sales occurring in different years.

3.3.3 Drivers Worksheet

The Drivers worksheet generates the impact driver variable series that are imported into the

OECM. The worksheet is arranged in a similar fashion to the Drivers worksheet found in

OECM.xls.15 The 26 OCS Planning Areas are arrayed down the worksheet, each containing a [20

x 55] array of 20 impact driver variables forecast throughout the 55-year period. Placeholders are

left for the last five of the 20 impact drivers, because they are endogenously calculated in

OECM.xls.

The calculations of the impact driver variables are comparably simple in concept, although the

spreadsheet formulas are complex. The general layout of the calculation for the driver variable

matrix for a single Planning Area is as follows:

E & D Inputs Schedule Matrix [15 x 55]

E & D Inputs Schedule Matrix [15 x 55]

E & D Inputs Schedule Matrix [15 x 55]

E & D Inputs Schedule Matrix [15 x 55]

E & D Inputs Schedule Matrix [15 x 55]

Driver Variables[15 x 55]

Year 5 Year 5, Activity Years

Year 4 Year 4, Activity Years

Year 3 Year 3, Activity Years

Year 2 Year 2, Activity Years

Year 1 Year 1, Activity Years

×

× +

× +

× +

× +

=

where Year x is the year of the 5-year plan in which the lease sale occurs, Activity Years is the

average activity years associated with the lease sale16, and the [i impact drivers x j years]

notation indicates the dimensions of the arrays. The entire [15 x 55] driver variables matrix is

populated with a single array formula. The calculations are Excel array multiplication

 

14 This spreadsheet is briefly described in Section 3.6.1. Use the “Update Schedules” button to invoke a macro to

automatically update the E&D.xls with the values contained in E&D Schedules.xls. 15 There is no compelling reason why E&D.xls must be a separate file from OECM.xls. The two files are only kept

separate to increase calculation speed—each workbook will recalculate much faster if the other is closed. As

computer speeds increase, this may become irrelevant, in which case a competent analyst could combine the two

files into one, replacing the top 15 rows of the Drivers worksheet in OECM.xls with the formulas found on the

corresponding worksheet in E&D.xls. Some adjustment to Visual Basic code would also be necessary, replacing

references to “E&D.xls” with “OECM.xls.”

16 Note that if multiple lease sales in the same year occur, they will be “combined” into one with the activity period

being the average (rounded to the nearest increment of 5) of the individual lease sales’ activity periods.
Section 3: Structure of the OECM 57

OECM Volume 1.doc 05/22/01 2:35 PM

operations, not matrix multiplication operations. Thus the timing of lease sales is accurately

reflected in the driver variables.

3.4 E&D.mdb Database

E&D.mdb is a Microsoft Access 97 database that holds E&D scenario data. A detailed

description of how to use the database and link it with the OECM model can be found in Section

2.4.2 of the User Guide.

3.4.1 Data Tables

Tables are the location in the database where all underlying data are stored. E&D.mdb contains

four tables.

Scenario_Data

The primary table in the database, scenario_data, houses the E&D scenario data. The structure of

the table is shown in Figure 3.10. Although all of the columns in the table are imported into

E&D.xls, only those columns specified in bold type on Figure 3.2 are used as potential impact

drivers. The only columns that are required (not null) are Name_Scen (scenario name),

Start_Year (year of 5-year plan in which sale occurs), and Years (period of activity). ID is an

auto-generated counter. Additionally, Years is limited to values of 25, 30, 35, 40, 45, or 50.

The table contains two lookup columns:

1. PlanArea_Num is a lookup column keyed to the listing of planning areas and corresponding

numbers found in tblPlanningAreas. It creates a pseudo-column called PlanningArea, which

displays the name of the planning area when the table is viewed (although the table actually

stores the planning area’s corresponding number 1 through 26).

2. CaseID is a lookup column that is keyed to the values found in tblCase. The user is presented

with a drop-down menu limited to the contents of tblCase. This column is optional.

tblCase

As described above this table holds descriptive information for the CaseID column. The default

options are “High”, “Low”, and “Expected”. The user is free to add additional Case

descriptions.

tblPlanningAreas

This table merely contains a listing of OCS Planning Areas and associated ID numbers 1 through

26. The table is used to provide the data for the lookup function that associates planning area

numbers with planning area names.

tblVersion

This contains the version number of the E&D.mdb file.
58 Section 3: Structure of the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

3.4.2 Queries

Queries are used to gather selected information from database tables. E&D.mdb contains two

queries.

qryOECMData

This query generates the records that are exported to E&D.xls. It selects records from the

scenario_data table that have been selected to be included in the OECM run (where the Include

column is set to TRUE) and generates the Loc_yr_code column described in Section 3.3.1.

QryScenarioNamesIDs

This query serves as the record source for the “Find Existing Scenario” functionality on the AddEdit Scenarios form.

3.4.3 Forms

Two forms are included to provide a user-friendly interface for inserting, deleting, and editing

E&D scenarios. Although use of the forms is not required (data can be edited directly on the

Columns

 Name Type Size

ID* Number (Long) 4

Name_Scen* Text 255

CaseID Number (Long) 4

PlanArea_Num Number (Integer) 2

Include Yes/No 1

NumSales Number (Long) 4

Prod_Oil Number (Single) 4

Prod_Gas Number (Single) 4

Years* Number (Integer) 2

Start_Year* Number (Integer) 2

Platforms Number (Double) 8

Wells_ED Number (Double) 8

Wells_Prod Number (Double) 8

Pipelines Number (Double) 8

Landfalls Number (Long) 4

Boat_Trips Number (Double) 8

Heli_Trips Number (Double) 8

Shorebases Number (Long) 4

FAC_Process Number (Long) 4

FAC_Waste Number (Long) 4

Drl_Fluids Number (Double) 8

Drl_Cuttings Number (Double) 8

Prod_H2O Number (Double) 8

BAD_Plat Number (Double) 8

BAD_Pipe Number (Double) 8

Expl_Plat Number (Long) 4

Figure 3.10 Structure of the scenario_data table.

Items in bold type are (potentially) used to develop

impact drivers in E&D.xls. Columns marked with a

* are required. Columns in italics are lookup fields.
Section 3: Structure of the OECM 59

OECM Volume 1.doc 05/22/01 2:35 PM

tables), the forms provide input on the relevant units and a description of each database field. A

description of how to use the forms is found in Section 2.4.2 of the User Guide.

Add-Edit Scenarios

This form, shown in Figure 3.11, provides a user-friendly environment in which to add, delete,

and modify E&D scenarios. The form is broken down into two sections: “Required Fields” and

“Optional Fields.” The “required” fields are those included as impact drivers for the OECM or

for maintenance of the database link with E&D.xls. In actuality, only Name_Scen, Start_Year and

Years have not null constraints applied to them, but the others are listed as “Required” to avoid

unforeseen problems with null values in OECM driver variables. The “Find Existing Scenario”

drop-down menu in the header of the form functions by calling the mcrScenario Pick List macro.

The “OK” and Trash Can (delete) buttons also call simple macros found in the macro collection

mgrpFormNavigation.

Choose OECM Scenarios

This form lists all of the scenarios in the database by name and presents the user with a checkbox

to choose whether each scenario is to be included in the OECM run. This form is useful if the

user has already specified the E&D scenarios and wants to choose quickly various scenario

combinations to run in the OECM.

Figure 3.11 The Add-Edit Scenarios Form
60 Section 3: Structure of the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

3.5 Query E&D Data.dqy

This is simply a text file that describes the query connecting E&D.mdb to E&D.xls. It must be

placed in the same directory as E&D.xls. The contents of the file are shown in Figure 3.12. It

describes the file locations, some ODBC parameters, the SQL code of the query itself, and the

columns into which the data is imported. The user ordinarily should have no reason to modify

the file.

If for some reason the query file must be modified, it can be opened in MS Query. The most

straightforward approach is to right-click the Query E&D Data.dqy file in Windows Explorer and

choose “Edit with Microsoft Query” as shown in Figure 3.13. If this option is not listed on the

menu, MS Query may not be installed on the system. Search the drives for the file

MSQRY32.EXE. If the file is found, it can be executed by

double-clicking the file. Otherwise, it must be installed

from the MS Office installation disk. Even if MS Query

in not installed, the database link feature in E&D.xls

should still function, and the query file can be edited in a

text editor.

Note that the references to columns of E&D scenario

variables on the E&D Data worksheet of E&D.xls are hardwired. If a change to the query file adds, deletes, or

changes ordering of columns, the spreadsheet will return

incorrect information to the model.

Figure 3.13 Opening Query E&D

Data.dqy with MS Query

XLODBC

1

DSN=MS Access Database;DBQ=E&D.mdb;DriverId=281;FIL=MS

Access;MaxBufferSize=2048;PageTimeout=5;

SELECT qryOECMData.ID, qryOECMData.Name_Scen, qryOECMData.Case,

qryOECMData.PlanArea_Num, qryOECMData.PlanArea, qryOECMData.Include,

qryOECMData.NumSales, qryOECMData.Prod_Oil, qryOECMData.Prod_Gas,

qryOECMData.Years, qryOECMData.Start_Year, qryOECMData.Loc_yr_code,

qryOECMData.Platforms, qryOECMData.Wells_ED, qryOECMData.Wells_Prod,

qryOECMData.Pipelines, qryOECMData.Landfalls, qryOECMData.Boat_Trips,

qryOECMData.Heli_Trips, qryOECMData.Shorebases, qryOECMData.FAC_Process,

qryOECMData.FAC_Waste, qryOECMData.Drl_Fluids, qryOECMData.Drl_Cuttings,

qryOECMData.Prod_H2O, qryOECMData.BAD_Plat, qryOECMData.BAD_Pipe,

qryOECMData.Expl_Plat FROM `E&D`.qryOECMData qryOECMData ORDER BY

qryOECMData.PlanArea_Num

ID Name_Scen Case PlanArea_Num PlanArea Include NumSales

Prod_Oil Prod_Gas Years Start_Year Loc_yr_code Platforms

Wells_ED Wells_Prod Pipelines Landfalls Boat_Trips Heli_Trips

Shorebases FAC_Process FAC_Waste Drl_Fluids Drl_Cuttings

Prod_H2O BAD_Plat BAD_Pipe Expl_Plat

Figure 3.12 Contents of Query E&D Data.dqy
Section 3: Structure of the OECM 61

OECM Volume 1.doc 05/22/01 2:35 PM

3.5.1 Troubleshooting Access Link Errors

If the Access Link fails when the user attempts to place a check in the “Access Link Enabled”

checkbox on the E&D Data worksheet of E&D.xls, the user is presented with an error dialog box. A

sample of the dialog box is shown in Figure 3.14. The dialog box contains two components: (1) a

warning that an error occurred and general advice on how to repair the problem (found in the top

section of the dialog box); and (2) the Excel VBA error code and a description (if one exists) of the

error code. The dialog box is designed this way so that general users are not presented with a

cryptic error message by the system. Instead, they are offered advice that should solve most issues

with the Access link. Advanced users, however, will find the Excel error code and error

description helpful in debugging the problem if the simple solutions proposed above don’t work.

The Access link may trigger macro errors upon update/refresh under the following

circumstances:

• Excel’s ODBC drivers are not loaded

Solution: Install the ODBC drivers from the installation disk

• The file is Query E&D Data.dqy missing, corrupt, or not located in the same directory as

E&D.xls (this spawns the error shown in Figure 3.14).

Solution: Ensure that Query E&D Data.dqy is installed in the same directory as E&D.xls. Restore

the Query E&D Data.dqy file with the one located in the “Backup” directory on the CD-ROM.

• The system cannot determine the path to the database or query file.

Solution: Open E&D.xls from within Excel and attempt to re-establish the Access link. Ensure

that E&D.mdb, E&D.xls, and Query E&D Data.dqy are all located in the same directory. Restore

the Query E&D Data.dqy file with the one located in the “File Copy” directory on the CD-ROM.

Figure 3.14. Access Link Error Dialog
62 Section 3: Structure of the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

3.5.2 Cleaning Up ExternalData# Range Names

Every time the Access Link is disabled and re-enabled, Excel creates a new named range

ExternalData followed by an incrementing number, as shown in Figure 3.15. This does not occur

when an existing Access Link is refreshed. The range corresponds to the area on the E&D Data

worksheet of E&D.xls where E&D scenario data are imported from Access. The user should

periodically examine the table of defined range names (invoke by keying CTRL-F3) to ensure

that a large number of these references are not being stored in the file. If many do appear, then a

macro routine is provided to clear the range names. The “Clear Range Names” button on the

E&D Data worksheet of E&D.xls triggers it. Note that the Access Link must be disabled and

then re-enabled after this operation is completed because the name of the active link is deleted

with the obsolete range names.

Figure 3.15. ExternalData Range Names

3.6 Accessory Files

Four accessory files are provided. These files contain macros that are helpful in automating

infrequent tasks that an advanced user may occasionally need to perform. These are largely

undocumented utilities. Use of these files should only be undertaken by advanced users who are

able to adjust the VBA code if necessary, and backup copies of all model files should be made

first.

3.6.1 E&D Schedules.xls

This file contains the calculations used to develop the activity period schedules found on the

Schedules worksheet of E&D.xls. As discussed in Section 3.2.2, the scheduling matrices in

E&D.xls are values, not calculated cells. The “Update Schedules” button on the lower portion of

the Schedules worksheet invokes a VBA routine that copies the calculated scheduling matrices

from E&D Schedules.xls, then pastes the updated values into E&D.xls. Both files must be open

before the macro is run.
Section 3: Structure of the OECM 63

OECM Volume 1.doc 05/22/01 2:35 PM

3.6.2 Coefficient Storage.xls

This file backs-up and restores the user input parameters for individual cost sector worksheets. It

consists of 15 worksheets; one called Admin and the rest corresponding to the 14 potential OECM

cost sector worksheets. It is helpful for backing-up and restoring user-input parameters in the

event a change is made to the cost sector template and the cost sector worksheets must be

recreated.

Backing-Up OECM.xls Cost Sector Parameters

To back up OECM parameters do the following:

1. Open both OECM.xls and Coefficient Storage.xls.

2. Go to the Admin worksheet of Coefficient Storage.xls, as shown in Figure 3.16. The

worksheet contains two buttons, a copy of the worksheet names table, and two counts of cost

sectors.

3. Enter the number of cost sectors currently defined in OECM.xls in the box labeled “Cost

Sectors to Backup/Restore.” The number above is linked to OECM.xls and reflects the

actual count of cost sectors in the model. The two numbers must match for the routine to

run. Press the “Copy from OECM.xls” button. The following information is copied to

Coefficient Storage.xls and stored as non-calculated cell values.

• The table of worksheet names;

• Each cost sector worksheet’s input area for impact driver coefficients and related units,

cost sector subcategory definitions, dollar-year value, and worksheet tab name.

Each is stored on an individual worksheet in Coefficient Storage.xls.

Restoring Cost Sector Parameters to OECM.xls

If the cost sector worksheet parameters have been previously backed-up, they can be restored to

OECM.xls by the following procedure:

1. Open both OECM.xls and Coefficient Storage.xls.

2. If necessary, add blank cost sector worksheets to OECM.xls so that the number or cost sectors

in OECM.xls matches the number of cost sectors to be restored. This is done using the “Add

Cost Sector” macro on the Assumptions worksheet. It is OK for the new cost sectors to have

the default names applied to newly added cost sectors “CostSector (x)”. The worksheet

names will be restored later.

3. Go to the Admin worksheet of Coefficient Storage.xls, as shown in Figure 3.16, and depress

the “Restore to OECM.xls” button. The following information is restored to OECM.xls:

• Each cost sector worksheet’s input area for impact driver coefficients and related units,

cost sector subcategory definitions, dollar-year value, and worksheet tab name.

4. When the process is complete, a dialog box prompts the user to verify that the restore

operation completed successfully.
64 Section 3: Structure of the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

3.6.3 Oil Spill Damages.xls

The oil spill-related damage spreadsheet calculates economic damages for all spill-related cost

elements including:

1. Beach recreation damages

2. Ecological habitat damages

3. Wildlife death damages

4. Recreational fishing damages

5. Commercial fishing damages

6. Subsistence harvest damages

7. Natural resource damage assessment administrative costs

8. Private property value damages

This section serves two purposes. First, the spreadsheet supplements the spill-related damage

analysis presented in Volume 2. The spreadsheet allows one to follow the step-by-step

calculations presented in Volume 2, Section 3.5. This section describes the organization of the

spreadsheet and presents the linkages between different parts of the spreadsheet. Note that this

section is not meant to provide the reader with a description of the methodologies for estimating

spill-related damages. The reader interested in methodological details, including assumptions

and data sources, is referred to Volume 2.

Figure 3.16 Using Coefficient Storage.xls
Section 3: Structure of the OECM 65

OECM Volume 1.doc 05/22/01 2:35 PM

The second purpose of this section is to allow the user to change some of the assumptions behind

the spill-related damage estimates and obtain revised estimates. Note that the original

spreadsheet should be saved to allow one to return to the original settings.

The spill-related damage spreadsheet performs all the step-by-step calculations described in

Section 3.5 of Volume 2. In addition, the spreadsheet calculates spill-related damages in a perbarrel-spilled metric. The spreadsheet is organized into six parts:

Part I - This section presents the user input data that can be revised by the user. These data

include assumptions regarding the NRDAM/CME adjustment factors, spill-related closure

periods, consumer surplus values, restoration costs, restoration effectiveness, and oil spill

frequencies.

Part II - This section presents the injury quantification and damage estimates for each of the 150

oil spill scenarios (see Section 3.2 and 3.3 of Volume 2 for more details).

Part III - This section presents input data for each OCS Planning Area including physical

characteristics, baseline visitation rates, and baseline economic values.

Part IV - This section presents the economic damages for each of the three spill magnitudes

(small spill from any source, large platform/pipeline spill, and large tanker spill) under low and

high economic assumptions for each OCS Planning Area. For OCS Planning Areas with

multiple spill scenario locations, the spreadsheet presents the average damages across all

locations.

Part V - This section presents the economic damages in a per-barrel-spilled metric. The section

also estimates per-barrel-spilled damages under low and high economic assumptions that

combine the three spill magnitudes. The combined values reflect assumptions about the

probability of spills of each magnitude.

Part VI - This section provides OECM drivers for platform/pipeline and tanker spills.

Additionally, a storage area for non-spill related impact drivers is provided. For many OECM

drivers, both low (expected) and high-case cost coefficients are provided. The user can choose

which to use if the user has directly linked the OECM drivers to the cost element worksheets in

OECM.xls.

The inputs into the spreadsheet are found in Parts I through III. Parts II, IV, V, and VI present

the damage outputs. The unadjusted NRDAM/CME injury outputs are all found in Part II,

columns C through S. These injury estimates are adjusted by the adjustment factors in Part I.A

(see Section 3.4 of Volume 2 for more details on the NRDAM/CME adjustment factors). The

final injury estimates for each spill-related cost element are obtained by combining the adjusted

NRDAM/CME output with the relevant data from Part III. For example, to estimate the injury to

beach recreation, the estimate of beach closure (measured in meter-days) needs to be multiplied

by the beach visitation per meter-day. The injury estimates are then multiplied by economic

values to obtain an injury estimate. The economic values for most cost elements are found in

Part I.B. For example, the high and low consumer surplus estimates for beach recreation are

presented in Part I.B. Additional data on the effectiveness of compensatory restoration used to

estimate ecological habitat damages is found in Part I.C (see Section 6.4 of Volume 2 for more
66 Section 3: Structure of the OECM

OECM Volume 1.doc 05/22/01 2:35 PM

details on the compensatory restoration calculations). Part II estimates the final economic

damages for all spill-related cost elements. Where applicable, damages are presented for low

and high economic assumptions.

The economic damages from Part II are aggregated for each OCS Planning Area in Part IV.

Damages are presented for each of the three spill magnitudes and the low and high economic

assumptions for a total of six sets of values. Damages are given for each spill-related cost

element as well as for total damages. For OCS Planning Areas with only one spill scenario

location, the values in Part IV are the same values in Part II. For OCS Planning Areas with

multiple spill scenario locations, the values in Part IV represent an average across the spill

scenario locations. For example, the Central Gulf of Mexico Planning Area has six spill

locations so the data in Part IV are an average over the six scenarios.

The damages in Part IV need to be presented in a metric that is compatible with the OECM

structure. Part V converts the data in Part IV to a per-barrel-spilled metric by dividing by spill

size. Part V gives damage estimates for each spill-related cost element and for total damages

under the low and high economic assumptions for each spill size. Then, Part V aggregates the

data across different spill size to estimate damages per-barrel-spilled under assumptions about

spill frequencies. Part VI segments the impact drivers into pipeline/platform and tanker spills.

The distinction between the two is important because while tanker spills are potentially affected

by out-of-producing-area spills and the imported oil backout, platform/pipeline spills are not.

The spreadsheet can be used to explore the impact of changes in the model's assumptions. For

example, a user may wish to explore the impact of different assumptions about closure periods,

consumer surplus values, or spill frequencies. The only data that should be changed by the user

is information in Part I. An original copy of the spreadsheet should be saved to avoid the loss of

the original assumptions.

Any value in Part I can be changed simply by entering a new value. The spreadsheet will

automatically update the damage estimates. Changing any of the input data in Parts I.A, I.B, or

I.C will change damage estimates in Parts II, IV, V, and VI. Changing the spill frequencies in

Part I.D will only change damage estimates in Parts V and VI. A change in the spill frequencies

will not affect the damage estimates for individual spill scenarios.

3.6.4 OECM Result Store.xls

OECM Result Store.xls is an accessory file that allows OECM results to be exported. A macro

included in the workbook copies the Summary worksheet from OECM.xls and pastes it as a new

worksheet with all calculated cells converted to values. The file can be useful if results of

numerous scenarios need to be compared or archived.

To use OECM Result Store.xls, open the file while OECM.xls is also open. If you have not

previously copied results to the file, it will only contain one worksheet named Source. This

worksheet looks similar to the Summary worksheet in OECM.xls. Near the top of the worksheet is

a button labeled “Store Current Model Results,” as shown in Figure 3.17. Depressing this button

will cause a new worksheet to be created with the numerical contents of the Summary worksheet

of the current OECM run.
Section 3: Structure of the OECM 67

OECM Volume 1.doc 05/22/01 2:35 PM

The user is then presented with a dialog box asking the name to give to the new worksheet. The

default name is the name given to the current scenario being analyzed in OECM.xls. Note that the

name entered must conform to the naming standards for Excel worksheets. The example shown

in Figure 3.17 would fail because slash (/) characters are not permitted in worksheet names.

Once a name is entered, a new worksheet with that name appears in the OECM Result Store.xls

workbook. The user may add as many worksheets as the user desires with the results of various

OECM scenarios. Other than the Source worksheet, the user may feel free to delete worksheets

no longer needed.

Figure 3.17 Using the OECM Result Store.xls workbook
68 Section 4: Model Results

OECM Volume 1.doc 05/22/01 2:35 PM

4. OECM Model Results

This section presents summary results of the OECM model. Results are presented for four sets of

Exploration and Development input scenarios, some of which have high and low cases

associated with them. The four scenarios are as follows:

1. Baseline—This scenario contains the E&D scenarios that may be proposed for the

2002–2007 5-year plan. The Baseline scenario includes lease sales in seven of the 26 OCS

planning areas. It has three variations on the level of activity: Low Case, Expected, and High

Case.

Scenarios 2 through 4 are based on the 1997–2002 5-year program. Because these scenarios

were also analyzed with the OECM’s precursor, the GPECM, use of these scenarios allows for a

comparison of the results of the two models.

2. The Test 2 scenario is based on the ranking of all planning areas for the draft proposed

program.

3. The Test 3 scenario is based on the ranking of the program areas for the proposed program.

4. The Test 4 scenario is based on valuation of the preferred alternative for the proposed

program.

In addition to low and high case scenarios for the E&D scenarios (that is, the impact drivers of

the model), in many cases low and high values are provided for environmental cost coefficients

(the damage done per unit amount of an impact driver). Although the “low” and “high”

descriptors of the cost coefficients imply that the most likely damage would fall roughly halfway

between them, the authors of Volume 2 indicate that the “low” cost coefficients more closely

reflect the consensus values found in the literature. High case values represent an upper bound

of reasonable values found in the literature. Accordingly, the “low” cost numbers are reported

here unless otherwise indicated. Selected results are presented in this section; complete results

of each scenario can be found in the accessory file Test Scenario Results.xls.

4.1 Baseline Model Results

The Baseline scenario includes E&D activity in seven OCS Planning Areas: all three in the Gulf

of Mexico Planning, and four in Alaska. The bulk of leasing activity occurs in the Central and

Western Gulf Planning Areas. The grand total annual environmental costs associated with this

production are presented in Figure 4.1. The figure contains four lines representing lower and

upper bounds as well as the expected value of environmental costs associated with the 2002-

2007 5-year plan. The lowest line on the chart is the combination of the low case E&D scenarios

with low estimates of environmental cost coefficients. Similarly, the highest line represents high

case development with the high estimates of environmental costs. The middle two lines show

the comparison of expected level of E&D activity with the low and high cost coefficients. There

is almost an order of magnitude of difference between the lowest and highest damages presented,

with maximum annual costs of about $44 million annually with low activity and low damages to

over $375 million annually with a high level of development and high damages estimates.
Section 4: Model Results 69

OECM Volume 1.doc 05/22/01 2:35 PM

While Figure 4.1 sums over all environmental cost sectors, Figure 4.2 shows the relative

contributions of each cost sector to the grand total. The results are shown for year 2010, a year

of high but not peak activity. The relative levels of environmental costs attributable the each

cost sector are roughly constant, with Air Quality amounting to over 90% of total costs in all

years. The next greatest contributor is ecological costs to Wildlife and Habitat. Of roughly

similar magnitude are Infrastructure and Fiscal costs, but in this case the costs are negative. The

negative result means that OCS activity is a net benefit to affected communities.

Figure 4.3 compares the results of the baseline scenario under the two alternative cost allocation

schemes in the OECM. The model’s regional costs are shown in the crosshatch section, while

national costs are shown in the shaded bars. Under the national cost tabulation, which allocates

all costs back to the producing area, only the seven Planning Areas where production occurs see

any environmental costs. Under, the regional cost tabulation, however, Figure 4.3 shows that the

Gulf of Alaska and the West Coast planning areas experience environmental costs from oil that

is spilled as it is tankered down from Alaska. Because Gulf of Mexico-based production by and

large is not tankered out of its producing area, the regional and national costs are identical.

The scenarios used in Figure 4.3 exclude another feature of the model that allocates costs outside

the producing area in order to focus on the distinction between national and regional costs. This

feature is Imported Oil Backout, which subtracts the environmental costs associated with foreign

oil imports that are displaced by OCS production. The total effects of the Imported Oil Backout

on the Baseline scenario are shown in Figure 4.4. Because over 75% of imported oil travels

through the Gulf of Mexico, the Gulf Planning Areas experience the greatest environmental

benefit from decreased dependence on foreign oil. Next in significance is the Mid Atlantic

$0

$50

$100

$150

$200

$250

$300

$350

$400

$450

2003200520072009201120132015201720192021202320252027202920312033203520372039204120432045204720492051205320552057

$Millions

High Case - High Damages

Expected Case - High Damages

Expected Case - Low Damages

Low Case - Low Damages

Figure 4.1 Baseline Scenario: Total OECM Environmental Costs

Low vs. High Damage Estimates
70 Section 4: Model Results

OECM Volume 1.doc 05/22/01 2:35 PM

Planning Area, which receives about 15% of imported oil. Figure 4.5 shows the distribution of

the environmental benefits (negative costs) among OCS planning areas for year 2010 due to

decreased imports of foreign oil.

$33.3

$0.1

$1.8

-$1.7

$0.04

$0.0004

$0.04

-$5 $0 $5 $10 $15 $20 $25 $30 $35 $40

Air Quality

Commercial Fisheries

Wildlife and Habitat

Recreation

Subsistence Resources

Infrastructure and Fiscal

Property Values

Figure 4.2 Comparison of Cost Sectors' Contribution to Total Environmental Costs

Year 2010, Baseline Scenario, $Millions

$ 1.0

$ 0.1 $ 0.1 $ 0.1

$ 1.7

$ 0.9

$ 19.2 $ 6.9

$ 2.1 $ 1.8

$0.0

$0.5

$1.0

$1.5

$2.0

$2.5

$3.0

Eastern Gulf

Central Gulf

Western Gulf

Southern California

Central California

Washington/Oregon

Gulf of Alaska

Cook Inlet

Chukchi Sea

Beaufort Sea

$Millions

Regional Costs National Costs

Figure 4.3 Comparison of Regional vs. National Costs

Year 2010, Baseline Scenario, Import Oil Backout Disabled, $Millions. Note that

the upper portion of the Y-Axis is truncated.
Section 4: Model Results 71

OECM Volume 1.doc 05/22/01 2:35 PM

For Gulf Coast Planning Areas, the Imported Oil Backout causes a slight decrease in net

environmental costs from OCS production. The planning areas in Figure 4.5 that experience no

OCS activity, especially the Mid Atlantic Planning Area, have no positive environmental costs.

In this case, OCS activity results in an overall net decrease in environmental costs associated

with the oil industry. This is why the regional costs scenarios show negative numbers for these

planning areas. Under the national cost tabulation, the Imported Oil Backout is still calculated,

but it is distributed in the same manner as environmental costs: back to the area where OCS

production occurred.

$0

$10

$20

$30

$40

$50

$60

$70

$80

$90

$100

200320052007200920112013201520172019202120232025202720292031203320352037203920412043

Import Backout Enabled Import Backout Disabled

Figure 4.4 Effect of Imported oil Backout on Total OCS Environmental Costs

Baseline Scenario, $Millions

(86.6)

(2.0)

(147.6)

(91.9)

(16.8) (10.8) (0.2) (0.2)

-

(6.8)

-$160

-$140

-$120

-$100

-$80

-$60

-$40

-$20

$0

Mid-AtlanticSouth AtlanticStraits of FloridaEastern GulfCentral GulfWestern GulfSouthern CaliforniaCentral CaliforniaNorthern CaliforniaWashington/Oregon

$Thousands

Figure 4.5 Distribution of Imported Oil Backout Environmental Benefits

Year 2010, Baseline Scenario, $Thousands
72 Section 4: Model Results

OECM Volume 1.doc 05/22/01 2:35 PM

The model also computes the present value of the environmental costs for each planning area.

The present values of the baseline scenarios depicted on an annual basis in Figure 4.1 are shown

in Table 4.1. As is clearly shown in both the chart and the table, the range of possible

environmental costs varies widely. It is the opinion of the authors, however, that the “Low

Costs” values are more representative of the damage costs typically found in the economics

literature. So while the “High Costs” values may represent an upper bound of environmental

damages, the “Low Costs” numbers do not represent a lower bound. Rather, they are mare akin

to expected values. Results shown below for Test Cases 2 – 4 are based on low case cost

coefficients. Table 4.2 shows the same scenarios with the results grouped by cost sector. Of

course, the grand totals are identical between Tables 4.1 and 4.2. Only the allocation of costs

differs between the two tables.

Table 4.1. Summary of Environmental Costs by Planning Area: Baseline Scenario

National Costs, ($2000) (in $000)

Low Case Expected Case E&D 

Scenario High Case E&D Scenario

OCS Region

Planning Area

North Atlantic - - - - - 

Mid-Atlantic - - - - - 

South Atlantic - - - - - 

Straits of Florida - - - - - 

G Eastern Gulf 31,717 10,557 104,380 52,878 173,951 

o Central Gulf 406,064 208,020 1,207,716 604,109 1,796,528 

M W estern Gulf 140,235 60,903 406,344 219,567 635,544 

Southern California - - - - - 

Central California - - - - - 

Northern California - - - - - 

Washington/Oregon - - - - - 

Gulf of Alaska 19,347 14,974 67,141 23,720 82,830 

Cook Inlet 13,238 8,234 52,501 18,241 72,381 

A Kodiak - - - - - 

Shuimagin - - - - - 

L North Aleutian Basin - - - - - 

Aleutian Arc - - - - - 

A St. George Basin - - - - - 

St. Matthew Hall - - - - - 

S Bowers Basin - - - - - 

Aleutian Basin - - - - - 

K Navarin Basin - - - - - 

Norton Basin - - - - - 

A Hope Basin - - - - - 

Chukchi Sea 70,610 40,192 209,863 101,028 300,204 

Beaufort Sea 42,204 28,235 123,493 56,173 164,289 

Total 723,415 371,114 2,171,439 1,075,716 3,225,727 

Low Costs Low Costs High Costs

A

T

L

P

A

C

Low Costs High Costs
Section 4: Model Results 73

OECM Volume 1.doc 05/22/01 2:35 PM

Table 4.2. Summary of Environmental Costs by Cost Sector: Baseline Scenario

($2000) (in $000)

Cost Sector

Air Quality 691,991 350,381 2,139,555 1,033,602 3,182,951 

Commercial Fisheries 2,161 1,019 2,161 3,304 3,304 

Wildlife and Habitat 41,429 25,677 41,429 57,182 57,182 

Recreation 945 430 945 1,461 1,461 

Subsistence Resources 24 21 70 27 73 

Infrastructure and Fiscal (13,503) (6,622) (13,503) (20,384) (20,384) 

Property Values 366 207 780 525 1,141 

Total - All Cost Sectors 723,415 371,114 2,171,439 1,075,716 3,225,727 

Low Costs Low Costs High Costs Low Costs High Costs

4.2 Test 2 Scenario Results

The Test 2 Scenario represents the ranking of all planning areas for the draft proposed program

of the 1997–2002 5-year program. There are no low and high cases for Test 2, only an Expected

case. An annual plot of the total environmental costs associated with the Test 2 scenario is

shown in Figure 4.6. The present values of the scenario are shown by planning area and by cost

sector in Tables 4.3 and 4.4, respectively.

$0

$10

$20

$30

$40

$50

$60

200320052007200920112013201520172019202120232025202720292031203320352037203920412043

Figure 4.6 Total OECM Environmental Costs: Test 2 Scenario

Import Backout Enabled, $ Millions
74 Section 4: Model Results

OECM Volume 1.doc 05/22/01 2:35 PM

Table 4.3. Summary of Environmental Costs by Planning Area: Test 2 Scenario

National Costs, ($2000) (in $000)

OCS Region Expected Case

Planning Area

North Atlantic 15,093

Mid-Atlantic 25,834

South Atlantic 7,379

Straits of Florida 2,344

G Eastern Gulf 21,799

o Central Gulf 123,533

M W estern Gulf 67,661

Southern California 40,480

Central California 19,878

Northern California 23,530

W ashington/Oregon 3,299

Gulf of Alaska 4,015

Cook Inlet 1,108

A Kodiak -

Shuimagin -

L North Aleutian Basin -

Aleutian Arc -

A St. George Basin -

St. Matthew Hall -

S Bowers Basin -

Aleutian Basin -

K Navarin Basin 1,981

Norton Basin -

A Hope Basin -

Chukchi Sea 39,934

Beaufort Sea 7,959

Total 405,827

A

T

L

P

A

C

Table 4.4. Summary of Environmental Costs by Cost Sector: Test 2 Scenario

($2000) (in $000)

Expected Case

Cost Sector

Air Quality 361,015

Commercial Fisheries 23,789

Wildlife and Habitat 25,347

Recreation 1,673

Subsistence Resources 17

Infrastructure and Fiscal (7,208)

Property Values 1,194

Total - All Cost Sectors 405,827
Section 4: Model Results 75

OECM Volume 1.doc 05/22/01 2:35 PM

4.3 Test 3 Scenario Results

The Test 3 Scenario represents the ranking of the program areas for the proposed program of the

1997–2002 5-year program. Test 3 has both a low and high case set of E&D scenarios. An

annual plot of the total environmental costs associated with the Test 3 scenario is shown in

Figure 4.7. The present values of the scenario are shown by planning area and by cost sector in

Tables 4.5 and 4.6, respectively.

$0

$10

$20

$30

$40

$50

$60

$70

$80

$90

200320052007200920112013201520172019202120232025202720292031203320352037203920412043

Low Case High Case

Figure 4.7 Total OECM Environmental Costs: Test 3 Scenario

Import Backout Enabled, $ Millions
76 Section 4: Model Results

OECM Volume 1.doc 05/22/01 2:35 PM

Table 4.5. Summary of Environmental Costs by Planning Area: Test 3 Scenario

National Costs, ($2000) (in $000)

OCS Region

Planning Area

North Atlantic - - 

Mid-Atlantic 7,587 5,098 

South Atlantic - - 

Straits of Florida - - 

G Eastern Gulf 5,836 3,667 

o Central Gulf 345,090 264,251 

M Western Gulf 186,420 102,423 

Southern California - - 

Central California - - 

Northern California - - 

Washington/Oregon - - 

Gulf of Alaska 6,440 1,060 

Cook Inlet 15,012 6,753 

A Kodiak - - 

Shuimagin - - 

L North Aleutian Basin - - 

Aleutian Arc - - 

A St. George Basin - - 

St. Matthew Hall - - 

S Bowers Basin - - 

Aleutian Basin - - 

K Navarin Basin - - 

Norton Basin - - 

A Hope Basin - - 

Chukchi Sea 101,028 40,192 

Beaufort Sea 51,207 25,740 

Total 718,620 449,185 

A

T

L

P

A

C

Low 

Case

High 

Case

Table 4.6. Summary of Environmental Costs by Cost Sector: Test 3 Scenario

($2000) (in $000)

Cost Sector

Air Quality 669,749 428,119 

Commercial Fisheries 6,843 4,657 

Wildlife and Habitat 52,763 23,430 

Recreation 1,307 953 

Subsistence Resources 25 20 

Infrastructure and Fiscal (12,458) (8,232) 

Property Values 393 238 

Total - All Cost Sectors 718,620 449,185 

Low 

Case

High 

Case
Section 4: Model Results 77

OECM Volume 1.doc 05/22/01 2:35 PM

4.4 Test 4 Scenario Results

The Test 4 Scenario represents valuation of the preferred alternative for the proposed program of

the 1997–2002 5-year program. Test 4 has both a low and high case set of E&D scenarios. An

annual plot of the total environmental costs associated with the Test 4 scenario is shown in

Figure 4.8. The present values of the scenario are shown by planning area and by cost sector in

Tables 4.7 and 4.8, respectively.

$0

$10

$20

$30

$40

$50

$60

$70

200320052007200920112013201520172019202120232025202720292031203320352037203920412043

Low Case High Case

Figure 4.7 Total OECM Environmental Costs: Test 4 Scenario

Import Backout Enabled, $ Millions
78 Section 4: Model Results

OECM Volume 1.doc 05/22/01 2:35 PM

Table 4.7. Summary of Environmental Costs by Planning Area: Test 4 Scenario

National Costs, ($2000) (in $000)

OCS Region

Planning Area

North Atlantic - - 

Mid-Atlantic 4,811 3,328 

South Atlantic - - 

Straits of Florida - - 

G Eastern Gulf 4,320 2,676 

o Central Gulf 265,148 90,833 

M Western Gulf 54,579 10,883 

Southern California - - 

Central California - - 

Northern California - - 

Washington/Oregon - - 

Gulf of Alaska 6,440 1,060 

Cook Inlet 15,012 6,753 

A Kodiak - - 

Shuimagin - - 

L North Aleutian Basin - - 

Aleutian Arc - - 

A St. George Basin - - 

St. Matthew Hall - - 

S Bowers Basin - - 

Aleutian Basin - - 

K Navarin Basin - - 

Norton Basin - - 

A Hope Basin - - 

Chukchi Sea 101,028 40,192 

Beaufort Sea 51,207 25,740 

Total 502,546 181,466 

Low 

Case

High 

Case

A

T

L

P

A

C

Table 4.8. Summary of Environmental Costs by Cost Sector: Test 4 Scenario

($2000) (in $000)

Cost Sector

Air Quality 453,361 159,356 

Commercial Fisheries 3,747 1,947 

Wildlife and Habitat 52,519 22,577 

Recreation 1,085 372 

Subsistence Resources 25 20 

Infrastructure and Fiscal (8,528) (2,937) 

Property Values 338 131 

Total - All Cost Sectors 502,546 181,466 

Low 

Case

High 

Case