APPENDIX A 

INSTRUCTIONS FOR COMPLETING THE GITA BUSINESS 

CASE TEMPLATE 

This research publication comes with a CD containing business case template files. The files are 

organized into two folders: 

 The Business Case Template folder contains blank files for creating new, individual 

business case documents for each participant in a multi-agency project as well as blank files 

for creating new summary documents for multi-agency projects. 

 The Case Study folder contains the financial details workbooks for the case study found in 

Appendix D of the research publication. These examples should help clarify the template 

instructions. The case study spreadsheets are password-protected, but users can save copies 

of them in order to experiment with the sensitivity analysis variables cited in the case 

studies. 

The Business Case Template consists of three parts. 

 Part 1. Business Case Document 

This is a Microsoft® Word document called Business Case Template.dot. This template 

includes headings for the various components of a multi-agency business case. The 

instructions for Part 1 explain what goes under each of the headings. Refer to the case studies 

in the research publication for examples. 

 Part 2. Financial Details Workbook

This is a Microsoft® Excel workbook with a series of worksheets that capture the financial 

details of a project from a single participant’s perspective. These spreadsheets should be 

attached to the end of the business case document, and selected excerpts may be inserted into 

the text of the Microsoft® Word document. One workbook should be prepared for each 

participating agency. 

There are six different versions of the Microsoft® Excel Financial Details workbook. Three 

are for proposed (future) investments with project lives of 5, 10, or 20 years (called 

Financial Details – Future5.xls, Financial Details – Future10.xls, and Financial Details – 

Future20.xls, respectively). Three are for analyzing historical data for previous investments 

over a period of 5, 10, or 20 years (called Financial Details – Past5.xls, Financial Details – 

Past10.xls, and Financial Details – Past20.xls, respectively). 

The same template workbook should be used for each agency participating in the project. 

Part 2 of the instructions explains how to select and complete the appropriate workbook and 

A-1 
also how to adjust the project life to something other than 5, 10, or 20 years. The sample 

worksheets in the Case Study folder may help you interpret these instructions. 

 Part 3. Multi-participant Summary Workbook

This is a Microsoft® Excel workbook with two worksheets that consolidate the financial 

details for agencies participating in a project. These spreadsheets should be attached to the 

end of the business case document, and selected excerpts may be inserted into the text of the 

Microsoft® Word document. 

There are six different versions of the Microsoft® Excel Multiparticipant Summary 

workbook. Three are for proposed (future) investments with project lives of 5, 10, or 20 years 

(called Multiparticipant Summary – Future5.xls, Multiparticipant Summary – 

Future10.xls, and Multiparticipant Summary – Future20.xls, respectively). Three are for 

analyzing historical data for previous investments over a period of 5, 10, or 20 years (called 

Multiparticipant Summary – Past5.xls, Multiparticipant Summary – Past10.xls, and 

Multiparticipant Summary – Past20.xls, respectively). 

Part 3 of the instructions explains how to select and complete the appropriate workbook and 

also how to adjust the project life to something other than 5, 10, or 20 years. The sample 

worksheets in the Case Study folder may help you interpret these instructions. 

NOTE: These instructions assume the user has a working knowledge of Microsoft® Excel 

and can navigate with scrollbars to view large worksheets with “frozen” column and row title 

areas. The worksheets and workbook are protected to prevent users from inadvertently 

overwriting any formulas. Detailed unprotecting instructions are provided, but users should 

proceed with caution if they take these steps because many protected cells contain formulas 

and formula references which, if overwritten, will corrupt the financial analysis. 

A-2 
TABLE OF CONTENTS 

PART 1. BUSINESS CASE DOCUMENT.......................................................................A-5 

Creating the Document File ............................................................................................A-5 

Title Section....................................................................................................................A-5 

Executive Summary Section............................................................................................A-5 

Project Description Section.............................................................................................A-6 

Project Schedule And Cost Section .................................................................................A-7 

Financial Analysis Section ..............................................................................................A-8 

Strategic Analysis Section...............................................................................................A-9 

Strategic Benefits (Examples By Application Area)...................................................A-10 

Related Projects Subsection........................................................................................A-13 

Conclusion Section .......................................................................................................A-13 

PART 2. INSTRUCTIONS FOR THE FINANCIAL DETAILS ATTACHMENT .....A-13 

Project Setup Worksheet ...............................................................................................A-14 

Financial Analysis Worksheet.......................................................................................A-18 

Labor Rates Worksheet .................................................................................................A-18 

Deleting Job Category Rows ......................................................................................A-20 

Adding Job Category Rows ........................................................................................A-20 

Labor Cost Multipliers Worksheet ................................................................................A-21 

Internal Labor Usage Worksheet...................................................................................A-22 

Internal Labor Costs Worksheet....................................................................................A-22 

Contract Costs Worksheet .............................................................................................A-23 

Productivity Benefit (Summary) Worksheet..................................................................A-24 

A-3 
Other Benefits Worksheet .............................................................................................A-24 

Other Benefits (Examples By Application Area)........................................................A-24 

Productivity Benefits Detail Worksheet(S) ...................................................................A-33 

Labor Productivity Benefits (Examples By Application Area) ...................................A-33 

Performing Sensitivity Analysis....................................................................................A-42 

PART 3. INSTRUCTIONS FOR THE MULTI-PARTICIPANT SUMMARY............A-44 

Preparation....................................................................................................................A-44 

Summary Setup Worksheet ...........................................................................................A-45 

Financial Summary Worksheet .....................................................................................A-48 

A-4 
PART 1. BUSINESS CASE DOCUMENT 

The following instructions explain how to complete each section of a business case using the 

Microsoft® Word template file called Business Case Document.dot. 

Creating the Document File 

Double-click on the file Business Case Document.dot to open a new document file. Save the file 

with a name that follows your organization’s file naming conventions. 

Title Section 

Type the project name in the title section of the document. Spell out the complete name of the project 

and, if there is an acronym or abbreviated project name, include this shorter name in parentheses 

after the full name1

. 

Add the name or names of the analyst(s) that prepared the business case. Consider including job titles 

and department names. 

Executive Summary Section 

Skip the Executive Summary section until the rest of the business case is complete. Once you 

complete the remaining sections, add an executive summary with the following information: 

• Short Project Description 

• How the Project Supports the Mission and Goals of Participating Agencies 

• Project Cost and Time to Complete 

• Key Financial Indicators 

• Key Strategic Benefits 

• Conclusion Statement (Recommendation) 

 

1

 You can then use the shorter name or acronym throughout the body of the document. 

A-5 
Project Description Section 

The description should include the following elements: 

• Type of analysis (evaluation of proposed future investment or analysis of actual historical 

costs and benefits realized on a previous investment). An evaluation of a proposed future 

investment considers future costs and benefits. An analysis of actual costs and benefits 

realized on a previous investment considers historical costs and benefits. Do not blend 

historical (sunk) and future cash flows in a single business case. If the project is ongoing and 

you wish to document performance to date and then justify an additional investment, you 

will need to prepare two separate business cases. In the strategic analysis of the justification 

for future funding, you may wish to cite the results of the historical analysis. 

• The full names (and, if desired, abbreviated names) for each agency that is participating in 

the project, along with a brief description of the role the agency will play. 

• A discussion of commonalities in the mission and goals of the participating agencies, 

followed by an explanation of how the project supports these shared goals. 

• Outline of the project scope, including planning, design, development, and implementation 

activities. 

• Application areas addressed by the project. 

You can use the following list of project application areas as a reference for defining your project. 

Refer to the research publication Building a Business Case for Shared Geospatial Data and Services: 

A Practitioner’s Guide to Financial and Strategic Analysis for a Multi-participant Program for 

detailed descriptions and examples of each of these application areas.

– Mapping and Data Access

Automated Map Production, Data Maintenance, and Data Access 

– Emergency Management and Security

Emergency Preparedness and Response/Critical Infrastructure Protection 

– Government Administration

Property Appraisal/Legislative (Voting) Districting/Tax Assessment 

– Quality of Life

Public Health and Safety/Community Services/Code Enforcement 

– Field Infrastructure Management

Management of Roads and Utility Lines/Capital Improvements/Maintenance 

A-6 
– Facility Management

Management of Plants, Buildings, and Property/Capital Improvements/Maintenance 

– Service Delivery

Call Management/Outage Management/Dispatch 

– Customer Relationship Management

Usage or Consumption Analysis/New Service Planning/Customer Compliance 

– Engineering

Planning/Design/Construction/System Analysis/Network Modeling 

– Land Management

Development Review/Zoning/Permitting/Land Use Analysis 

– Regulatory Compliance

Permit Application/Monitoring Programs/Compliance Management 

– Environmental Management

Natural Resource Preservation/Watershed Protection/Weather Monitoring 

• Major purchases 

• New job positions or reclassifications 

• Clarification of related items and activities that are not included in the project 

Project Schedule and Cost Section 

Complete a Financial Details Workbook for up to 20 different participating agencies using Part 2 of 

these instructions. Each agency should use the same template file and should define the same project 

lifespan and same starting year. Departments within the same agency do not need their own 

workbooks. 

If more than 20 agencies are participating in the project, create groupings of similar agencies, and 

prepare a workbook for each grouping. For example, if each local government in a state will 

participate in a project by contributing data to a central repository, a single workbook can be created 

to reflect the cash flows anticipated (or realized) for local governments. If some local governments 

will be contributing more types of data than others, it may be useful to create two local government 

groupings to reflect that participation of small contributors and major contributors. When preparing 

financial workbooks to analyze future cash flows for agency groupings, it may be possible to 

extrapolate the cash flows for the entire group by analyzing a representative sample of agencies in 

the group. 

A-7 
After completing the financial details workbooks for each participating agency or group, complete a 

Multi-participant Summary workbook using Part 3 of these instructions. Then, fill in the Project 

Schedule and Cost section with the following information: 

• Date of project inception and the final year of project life designated for the financial 

analysis. You may wish to prepare a Gantt chart or similar graphical timeline to include as 

an attachment. 

• Total cost of the investment. If you cite the present value (or inception year value) of the 

costs (from your Financial Analysis Worksheet), be sure to indicate that you are stating the 

investment in Year XXXX dollars. If you prefer to cite the cash flows in terms of the actual 

dollars to be budgeted for each year of the project life (or in terms of the actual expenditures 

for a previous investment), you can find this information on the Financial Summary 

worksheet within the Multi-participant Summary workbook and in greater details in the 

Contract and Procurement Costs Worksheet and Internal Labor Costs Worksheet of the 

individual Financial Details workbooks. 

Note any costs that will continue beyond the final year of the designated project life. 

Financial Analysis Section 

Cite the key financial indicators: Net Present Value (or Net Value stated in inception year dollars for 

a historical analysis), Return on Investment, Breakeven Year and Payback Period2

. Provide the 

metrics from the Multi-participant Summary Workbook and indicate that these values reflect the 

financial performance of the entire investment rather than an individual agency’s experience. You 

may also wish to cite the financial details for specific participating agencies. Examples of opening 

paragraphs: 

• For a historical investment: This project’s Net Value to date is $4,500,000, stated in 2001 

dollars. The annual return on investment has been 17%. The project broke even in 2003, 

two years after project inception. Agency X has realized a net present value of $8,000,000 

and broke even in 2004. Agency Y and Agency Z each realized a net present value of 

$1,000,000 and broke even in 2002. The remaining net benefits were realized by the other 

participating agencies. 

• For a proposed future investment: The projected Net Present Value for this project is 

$4,500,000 with an estimated annual return on investment of 17%. This project is expected 

to break even within two years of the initial investment. Agency X anticipates a net present 

value of $8,000,000, and Agencies Y and Agency Z each anticipate a net present value of 

 

2

 These indicators can be found on the Financial Analysis worksheet and also on the Project Setup worksheet 

in the Financial Details Workbook. 

A-8 
$1,000,000. The other anticipated net benefits are distributed among the other project 

participants. 

• For a previous investment that did not break even: This project’s Net Value to date is 

($1,000,000) stated in 1999 dollars. This project has not yet broken even. 

It may be helpful to copy and paste the Financial Summary Worksheets (from the Multiparticipant Summary Workbook) and the Financial Analysis Worksheets (from the individual 

Financial Details Workbooks) into the text of your opening paragraph. 

Sensitivity Analysis Subsection

Summarize the findings of your sensitivity analysis3

 under the Sensitivity Analysis subsection. 

Part 2 of these instructions explains how to conduct the sensitivity analysis. Examples of 

sensitivity analysis summaries include: 

• If the application development effort is 50% more expensive than projected (i.e., cost 

overruns of 50%), the project’s Net Present Value drops to zero, so it is essential to manage 

contract costs. If the project development schedule slips by 12 months, the labor savings 

due to reduced map production workload will be delayed by a year. This will reduce Net 

Present Value to $3,500,000, but the project will still break even in the second year. 

• Although the project is not anticipated to break even, there is a possibility of a higher return 

if we are able to avoid a larger proportion of legal costs associated with infrastructure 

deterioration on or near others’ property. If we are able to cut litigation fees by $200,000 

annually (half our current spending) by Year 2 of the project, we will show a 6% return on 

investment. 

It may be helpful to copy and paste your sensitivity analysis Financial Analysis Worksheets (or 

other sections of the workbooks you created for the sensitivity analysis). 

Strategic Analysis Section 

In the list below, review some examples of strategic (non-quantifiable) benefits for the application 

areas covered by your project. Mark the benefits that apply to your project (to assist you in 

completing the strategic analysis). Note any other strategic benefits for your project. Describe each 

of the strategic benefits in the Strategic Analysis section on the Business Case Document. 

 

3

 For analyses of past investments, you do not need to include sensitivity analysis. 

A-9 
Strategic Benefits (Examples by Application Area) 

MAPPING AND DATA ACCESS 

Automated Map Production, Data Maintenance, and Data Access 

Strategic Benefit Examples

 Improve accuracy and completeness of spatial data 

 Make information more accessible to the organization and the community 

 Improve quality of decisions that rely on spatial data

EMERGENCY MANAGEMENT AND SECURITY 

Emergency Preparedness and Response / Critical Infrastructure Protection 

Strategic Benefit Examples

 Protect human life, public safety, private property, and critical public infrastructure - 

improvements to public safety and property as a result of better response, including 

avoided losses to people and property, reduced stress on medical and emergency 

response services 

 Minimize the social and cultural impact of natural disasters and other emergencies - 

reduced time to restore services after a disaster and translate to benefits to the public 

(commercial activity restored, citizens able to return home, reduced need for social 

services with disruption ended)

GOVERNMENT ADMINISTRATION 

Property Appraisal/Legislative (Voting) Districting/Tax Assessment 

Strategic Benefit Examples

 More public involvement in redistricting efforts

QUALITY OF LIFE 

Public Health and Safety/Community Services/Code Enforcement 

Strategic Benefit Examples

 Reduce crime – quality of life, attractiveness to new development 

 Improve public health – general productivity of citizens, quality of life 

 Improve public goodwill and community attractiveness

A-10 
Strategic Benefits (Examples by Application Area) 

FIELD INFRASTRUCTURE MANAGEMENT 

Management of Roads and Utility Lines/Capital Improvements/Maintenance 

Strategic Benefit Examples

 Improve work force safety – employee morale, public goodwill 

 Improve work force morale 

 Improve public goodwill (more reliable service, reduced service interruption) 

 Improve public safety (faster work site restoration) – (community appearance, reduced 

stress on medical and emergency systems)

FACILITY MANAGEMENT 

Management of Plants, Buildings, and Property/Capital Improvements/Maintenance 

Strategic Benefit Examples

 Improve work force safety 

 Improve work force morale 

 Improve public goodwill (more reliable service)

SERVICE DELIVERY 

Call Management/Outage Management/Dispatch 

Strategic Benefit Examples

 Improve public goodwill/customer service levels (faster response times, fewer transfers) 

 Improve public health and safety through faster emergency response times 

 Improve relationship with external agencies

A-11 
Strategic Benefits (Examples by Application Area) 

CUSTOMER RELATIONSHIP MANAGEMENT 

Usage or Consumption Analysis/New Service Planning/Customer Compliance 

Strategic Benefit Examples

 Improve public goodwill 

 Improve public health and safety (cross-connection control programs) 

ENGINEERING 

Planning/Design/Construction/System Analysis/Network Modeling 

Strategic Benefit Examples

 Improve public goodwill (reduce service disruption; reduce street cuts) 

 Improve staff confidence in models, willingness to use models 

 Improve public goodwill (by supporting modeling applications that improve customer 

service levels) – improved customer service (time to respond to complaints), compliance 

with regulatory guidelines 

 Better overall design, better planned infrastructure

LAND MANAGEMENT 

Development Review/Zoning/Permitting/Land Use Analysis 

Strategic Benefit Examples

 Reduce private development costs (expedite review process; improve relationship with 

development community) 

 More attractive community, better zoning

REGULATORY COMPLIANCE 

Permit Application/Monitoring Programs/Compliance Management 

Strategic Benefit Examples

 Improve public image (public goodwill) 

 Improve relationship with regulators (increase opportunity to shape regulations)

A-12 
Strategic Benefits (Examples by Application Area) 

ENVIRONMENTAL MANAGEMENT 

Natural Resource Preservation/Watershed Protection/Weather Monitoring 

Strategic Benefit Examples

 Good citizenship (public goodwill from efforts to protect the environment) 

 Healthier environment 

 Wildlife preservation

Related Projects Subsection 

If this project depends on (or depended on) other initiatives, or if it is (was) a prerequisite for future 

projects, cite these interdependencies in the strategic analysis. 

Conclusion Section 

For a proposed investment, the conclusion statement should recommend for or against the 

investment. For an analysis of a past investment, the conclusion should address lessons learned 

and/or summarize whether the project met expectations. 

After completing the Conclusion section, remember to return to the Executive Summary section 

and summarize the findings. 

Attach your completed financial worksheets to the business case template. 

PART 2. INSTRUCTIONS FOR THE FINANCIAL DETAILS ATTACHMENT 

The following instructions explain how to complete each of the financial details worksheets in the 

Microsoft® Excel workbook. 

Choose a Financial Details workbook to reflect the investment timing (past or future) and the length 

of the project life to be analyzed (5, 10, or 20 years). You can customize the length of the project life 

if desired4

. The names of the workbook templates indicate “Past” of “Future” followed by the project 

 

4

 It is easier to remove extra years than to add new ones, so if you want to customize the project length, start 

with a workbook that covers a longer period than what you desire. For example, if you want to analyze a 12-

year project life, start with one of the 20-year workbooks. 

A-13 
life (5, 10, or 20). Open the desired template workbook and save it under the file name of your 

choice. For multiparticipant projects in which more than one agency invests, a separate financial 

details workbook should be completed for each agency. Use the same template to build each 

agency’s workbook. 

Project Setup Worksheet 

Prepare for each financial analysis by completing the Project Setup sheet (first sheet) on your 

Financial Details workbook. Consult with the agency’s Finance Department as needed to complete 

the Project Setup information. 

• Enter the name of the project on the top line of the setup sheet. 

• Enter the date you intend to complete and submit your analysis. 

• Enter the name of the agency or group to which the financial details apply. This “participant 

name” will appear in the Multiparticipant Summary Workbook once the details workbook is 

stored and linked to the summary (see instructions in Part 3 for details.) 

• The next section of the set-up sheet summarizes the key financial metrics. These values will 

change automatically as you enter data into the various worksheets. The cells will contain 

zeroes or related error messages (such as #DIV/0 or #VALUE) until you have completed 

subsequent worksheets in the workbook. These metrics are specific to the agency 

documented in the workbook. The metrics for the overall multi-participant project will 

likely differ from the metrics for each individual participant. 

• Enter an average inflation rate for the project period. This inflation rate should reflect a 

general increase in the cost of goods and services, which may or may not be the same as the 

average rate of labor cost inflation within the organization for the same period. (There is an 

option further down the page to enter a different value for the inflation rate for internal labor 

costs.) The worksheet suggests a value of 2.5% for the general inflation rate, but you should 

replace this value with an inflation assumption authorized by your finance department. 

NOTE: It is acceptable to use different inflation rates within each agency’s worksheet, but 

for the summary financial analysis, you will have to select one inflation rate for 

determination of the investment’s discount rate. 

– Suggestion for United States business cases: Take the average of annual inflation rates 

reported by the federal government or other reliable source. For example, U.S. Consumer 

price index rates are available on-line from the Department of Labor Bureau of Labor 

Statistics at http://data.bls.gov. Following are annual percent changes in the broadest CPI 

measure for the last 10 years (retrieved from the BLS Web site): 

A-14 
1994 2.6% 1996 3.0% 1998 1.6% 2000 3.4% 2002 1.6%

1995 2.8% 1997 2.3% 1999 2.2% 2001 2.8% 2003 2.3%

The average inflation rate for this example is 2.5%. 

– Suggestion for Canadian business cases: See Statistics Canada at http://www.statcan.ca

for Canadian consumer price index data. For example, Statistics Canada reports that the 

October 2004 to October 2005 Consumer Price Index for the entire country is 2.6%, and 

the CPI for Alberta is 3.5%. Either of these numbers might be suitable for analyzing a 

short-term Alberta-based project that is completed sometime close to the year 2005. For 

longer term analyses, it would be appropriate to use an average of annual CPIs, which can 

be obtained for a small fee from the Statistics Canada Web site. 

• Enter the annual cost of capital for purposes of calculating present values5

. The worksheet 

suggests a value of 5%, but you should replace this value with an assumption authorized by 

your finance department. 

• Type labels of your choice (such as calendar year or fiscal year labels) in each of the 

investment year columns. These labels will carry forward to the rest of the workbook6

. 

• If you are using the standard project length in the workbook, skip the next two bullets. 

• If you need to reduce the length of the project, you will first need to unprotect the setup 

worksheet7

. Then, go to the last column in which project year labels are displayed and 

delete the entire column8

. IMPORTANT: Delete from the end of the project life, not the 

 

5

 This is the annual rate of return that could be expected from an alternative investment that is forgone in order 

to fund the project being analyzed. Some organizations use the return on a secure investment like government 

bonds. The cost of capital is used to convert future cash flows into current (present) values and to convert 

historical cash flows into inception year dollars. Refer to the research publication for a more detailed 

explanation of the opportunity cost of capital. 

6

 If the analysis covers a previous investment, the final year column should be the current or immediately 

completed fiscal year or the last year of the investment life. If the analysis covers a future investment, the first 

column should be the current fiscal year or the fiscal year that is about to commence. The analysis should 

cover either past years or future years, but not both. 

7

 From the Tools menu, choose Protection and then choose Unprotect Sheet. 

8

 Click inside the column header to highlight the entire column. Then, from the Edit menu, choose Delete and 

then Delete Column. Then, double-check the Project Life (for future projects) or Years Since Project 

Inception (for past projects) on the line directly below the year labels. It should have decreased by one for 

A-15 
beginning. Continue selecting and deleting columns until you have shortened the project life 

to the desired length. Then, re-protect the worksheet9

. Repeat this process (unprotecting, 

deleting columns from the end, and reprotecting) for all the remaining worksheets in the 

workbook (except the current labor rates worksheet)10. 

• If you need to increase the length of the project, you will first need to unprotect the setup 

worksheet11. Then, go to the last column in which project year labels are displayed and 

copy the entire column12. Then, highlight the entire blank column immediately to the right of 

the column you just copied and paste13. IMPORTANT: Copy and paste the entire column, 

not just the year labels. There are hidden cells in the columns that are necessary for the 

financial calculations. Continue copying and pasting columns until you have increased the 

project life to the desired length. Then, re-protect the worksheet14. Repeat this process 

(unprotecting, copying and pasting columns from the end, and reprotecting) for all the 

remaining worksheets in the workbook (except the current labor rates worksheet)15. 

 

each column you deleted. If this did not occur, you may not have highlighted/deleted the entire column. You 

can choose Undo from the Edit menu and try again. 

9

 From the Tools menu, choose Protection and then choose Protect Sheet. Accept the protection options 

displayed. 

10 There are 29 productivity benefit detail sheets available in the workbook. If you are going to analyze 

internal labor costs and productivity benefits for fewer than 29 different job categories, you do not need to 

delete the extra columns from the extra benefit detail sheets. For example, if you only have 10 labor 

categories, then you only need to delete the extra columns from the first 10 productivity benefit detail sheets. 

11 From the Tools menu, choose Protection and then choose Unprotect Sheet. 

12 Click inside the column header for the last year label to highlight the entire column. Then, from the Edit

menu, choose Copy. 

13 Click inside the column header for the first blank column after the year labels to highlight the entire column. 

Then, from the Edit menu, choose Paste. Then, double-check the Project Life (for future projects) or Years 

Since Project Inception (for past projects) on the line directly below the year labels. It should have increased 

by 1 for each column you added. If this did not occur, you may not have copied the entire column. You can 

choose Undo from the Edit menu and try again. 

14 From the Tools menu, choose Protection and then choose Protect Sheet. Accept the protection options 

displayed. 

15 There are 29 productivity benefit detail sheets available in the workbook. If you are going to analyze 

internal labor costs and productivity benefits for fewer than 29 different job categories, you do not need to 

copy and paste the new columns on the extra benefit detail sheets. For example, if you only have 6 labor 

categories, then you only need to copy and paste the extra columns into the first 6 productivity benefit detail 

sheets. 

A-16 
• Choose a method for determining future (or past) labor costs. You may select different 

options for each agency in a multi-participant project: 

– The default for future projects is “Derived by Applying Inflation Rate to Current Costs.” 

The default for past projects is “Derived by Applying Inflation Rate to Final Year Costs.” 

These options apply the inflation rate from the Project Setup Sheet to the data on the 

Labor Rates sheet to estimate what labor costs will be in the future (or what they were in 

the past). You can change this default option by selecting an alternative from the dropdown list in this field. 

– An alternative to this method is “Derived by Applying Average Cost of Living 

Adjustment to Current (of Final Year) Costs.” Choose this option if your internal labor 

costs are anticipated to grow (or actually grew) at a different rate than the general 

inflation rate (perhaps due to a wage freeze, long-term union contracts, or a market 

analysis and salary adjustment). If you choose this option, a label will appear to the 

immediate right of your selection to indicate the spot in which you should enter your 

organization’s average annual cost of living adjustment or similar inflation percentage for 

internal labor costs. 

– A third alternative for future projects is “Independent of Assumed Inflation Rates.” 

Choose this option if you want to manually enter estimated individual labor rates for each 

job category and each year of the project life16. If you choose this option, enter notes 

where indicated to explain the method you will be using to determine the labor costs. 

– For past projects, you may choose “Actual Historical Costs” if you want to manually 

enter actual burdened hourly rates or actual burdened annual costs for each job category 

and each year of the project life. 

• Note any other assumptions in the Notes section of the setup sheet. You may wish to note 

your approach to accounting for productivity benefits. For example, you may indicate 

whether you will quantify all labor savings or only those that are realized as payroll 

reductions. 

At this point, you should also determine whether you will treat any of your internal labor 

costs as a negative productivity benefit. In other words, will you track internal labor for 

project development/support as negative savings on the productivity benefit detail sheets 

(i.e., subtract the internal labor costs from the productivity “savings” to reduce the stated 

benefit)? This has no effect on NPV but increases the ROI. 

 

16 This option allows you to vary the rate of labor cost growth over the life of the project or across different job 

categories. 

A-17 
– This approach may be preferable for organizations that use program-based budgets and/or 

in situations where the staff involved with the internal labor costs would be (or would 

have been) retained regardless of the decision to proceed with the project. It may also be 

useful if you are going to limit benefit calculations to the elimination of FTEs from the 

budget, particularly if an ongoing internal labor cost of the project is counteracted by 

some labor savings for the same job category. By documenting the full savings on your 

benefit detail sheet and then documenting the internal costs as a negative benefit, you can 

arrive cleanly at the net eliminated FTEs. You may count some internal labor as a cost 

and other internal labor as a negative benefit. Be careful, however, to account for a 

particular internal labor effort on only one worksheet (internal labor usage or 

productivity benefit detail). 

– You do not necessarily have to document this decision in your notes, but you may wish to 

discuss your approach with your finance department. Refer to the research publication for 

more details on internal labor costs and savings. 

Financial Analysis Worksheet 

This worksheet summarizes costs and benefits, converts them into present values using the cost of 

capital and inflation rate from the Project Setup Worksheet, and calculates the net present value (or 

net value in inception year dollars), annual return on investment, breakeven point, and payback 

period. All fields on this worksheet are calculated automatically. You can refer back to this 

worksheet as you complete the rest of the workbook to see how various assumptions affect the 

financial analysis. 

Labor Rates Worksheet 

Complete this worksheet in consultation with your human resources department. Starting at the top 

of the job categories list, complete one row for each job category affected by the project. You may 

leave blank rows at the bottom of the list. You may wish to combine multiple job titles into a single 

category if they have similar hourly costs or salaries. Do not cut and paste labor category names from 

one row to the next. Each row on the Labor Rates worksheet contains the following columns: 

• Job Category – Job title or group of job titles with similar responsibilities and pay rates. 

Include categories that benefit from the project and categories that will contribute to 

(develop) the project. 

• Average Hourly Rate ($/Hour) – The average hourly wage for positions in this category. For 

future projects, use the current year’s rates. For historical projects, use the labor rates that 

were in effect during the last year of the project life (the last year labeled on the Project 

Setup Sheet). If the position is salaried, derive the hourly wage by dividing the annual salary 

by the typical hours in a work year. 

A-18 
• Fringe – This wage multiplier covers the cost of taxes, insurance, and related overhead items 

that contribute to the cost of employing someone in this category. Fringe rates typically 

range from 15% to 80%. 

• Burdened Hourly Rate – This is automatically calculated as (Hourly Rate) X (1 + Fringe 

Rate)17. 

• Average Annual Regular Hours – The average number of base (regular time) hours worked 

by the position. For salaried positions, divide the average annual salary by the average 

hourly rate. For hourly wage positions, use the standard number of paid hours in a work 

year18. 

• Average Annual Cost Before Overtime – This is automatically calculated as (Average 

Annual Regular Hours) X (Burdened Hourly Rate). 

• Average Annual Overtime Hours – Leave blank for salaried positions that do not receive 

overtime pay. For positions that qualify for overtime, list the average number of overtime 

hours worked annually by an individual in this job category. 

• Average Overtime Multiplier – Leave blank for salaried positions that do not receive 

overtime pay. For positions that qualify for overtime, list the most common overtime 

multiplier (typically 1.5 for “time and a half” pay). 

• Average Annual Cost of Position - This is automatically calculated as (Average Annual Cost 

Before Overtime) + [(Average Annual Overtime Hours) X (Burdened Hourly Rate) X 

(Average Overtime Multiplier)] 

You can type over data in a job category row to change the name of a job category. Do not cut and 

paste job category names within the list. The following instructions explain how to add and delete 

rows in the job categories list (if you prefer not to leave blank lines or if you need more than 29 job 

categories). If you do not need to add or delete rows, you can skip to the next section (the Labor 

Cost Multipliers Worksheet). 

 

17 If your organization has already established “standard internal billing rates” for your job categories, you may 

unprotect the Labor Rates workbook and manually enter these in the burdened labor rate column. This will 

overwrite the “base x fringe” formula. If you take this approach, you can leave the Average Hourly Rate and 

Fringe columns blank. Be sure to reprotect the worksheet when you are finished. 

18 This is typically 2080 hours in the United States and slightly less than 2000 hours in Canada. Consult your 

human resources department. 

A-19 
Deleting Job Category Rows 

If you have fewer than 29 job categories, you may leave blank rows at the bottom of the list. If you 

prefer to delete unused rows from this worksheet, you must also delete them from all the remaining 

labor-related sheets. 

To delete unused rows, begin by unprotecting the worksheet by selecting Protection – Unprotect 

Sheet from the Tools menu. Highlight an entire unused row by clicking in the row number label at 

the far left of the row. Then, choose Delete from the Edit menu. Be sure to delete the entire row. 

Continue deleting unused rows as needed. Then, re-protect the worksheet by selecting Protection – 

Protect Sheet from the Tools menu. Accept the protection defaults. 

You will need to delete these same rows on the following worksheets: Labor Cost Multipliers, 

Internal Labor Usage, Internal Labor Costs, and Productivity Benefits. 

If you have deleted unused rows, you should also delete the corresponding Productivity Benefit 

Detail worksheets. Start with the last of these sheets (#29). Unprotect the workbook by choosing 

Protection - Unprotect Workbook from the Tools menu. Then choose Delete Sheet from the Edit

menu. Continue deleting productivity benefit detail worksheets as needed, and then reprotect the 

workbook by choosing Protection – Protect Workbook from the Tools menu. Accept the 

protection defaults. 

Adding Job Category Rows 

If you have more than 29 job categories, you may insert copied rows on the Labor Rates worksheet 

and all the remaining labor-related sheets. First, unprotect the Labor Rates worksheet by selecting 

Protection – Unprotect Sheet from the Tools menu. Highlight the entire last blank row in the job 

category list by clicking in the row number label at the far left of the row. Then, choose Copy from 

the Edit menu. Next, highlight the entire blank row immediately beneath the last job category line 

and choose Copied Cells from the Insert menu. 

IMPORTANT: Be sure to use the Insert Copied Cells function rather than the Paste function; pasting 

will overwrite summary rows on some of the other worksheets. 

Continue copying and inserting copied cells at the end of the list as needed. Fill in the names of the 

additional labor categories. Then, reprotect the Labor Rates worksheet by selecting Protection – 

Protect Sheet from the Tools menu. Accept the protection defaults. 

Insert rows in the same manner on the following worksheets: Labor Cost Multipliers, Internal Labor 

Usage, Internal Labor Costs, and Productivity Benefits. NOTE: When inserting rows on the Internal 

Labor Costs and Productivity Benefits worksheets, highlight the yellow “Totals” row prior to 

inserting the copied cells; this will insert the new row immediately above the totals line. When 

inserting copied cells in these labor-related worksheets, double-check to ensure the proper job 

category name appears in the new row. If it does not appear, then you may not have copied the right 

A-20 
cell or inserted into the right area (the end of the list), and this can cause problems with calculations 

throughout the workbook. If the job category name does not appear as you entered it on Current 

Labor Rates, highlight and delete the row you just added and try again. 

Once you have updated the labor-related worksheets with the new job category rows, you will need 

to create corresponding Productivity Benefit Detail worksheets. Open the last of these sheets 

(Productivity Benefit Detail-29). Unprotect the workbook by choosing Protection - Unprotect 

Workbook from the Tools menu. Then choose Move or Copy Sheet from the Edit menu. Scroll to 

the end of the “Before Sheet” list and highlight “Move to End,” and click to check off the Create a 

copy box. Your new worksheet will be named Productivity Benefit Detail (2). Right-click in the 

name tab at the bottom of this sheet to rename the worksheet (as Productivity Benefit Detail-30, 

Productivity Benefit Detail-31, etc.) 

You will need to modify the formulas in each new Productivity Benefit Detail worksheet as follows: 

Unprotect the sheet by choosing Protection – Unprotect Sheet from the Tools menu. In the Job 

Category cell (Cell A1), increment the last number in the formula by 1. For example, change ="Job 

Category: "&'Current Labor Rates'!A30 from detail sheet #29 to ="Job Category: "&'Current Labor 

Rates'!A31 on detail sheet #30. Then, go to cell B2 and increment the formula’s cell reference by 1. 

For example, change =IF('Productivity Benefits'!$B$31="per Hour","Hours","FTEs") on detail sheet 

#29 to =IF('Productivity Benefits'!$B$32="per Hour","Hours","FTEs") on detail sheet #30. Repeat 

this modification to all remaining formulas in row 2 (or copy the corrected formula and paste it into 

subsequent years’ columns). Then, go to cell B18 and increment the formula’s cell reference by 1. 

For example, change =IF('Productivity Benefits'!$B$31="per Hour","Total Hours Saved for this Job 

Category","Total FTEs Saved for this Job Category") on detail sheet #29 to =IF('Productivity 

Benefits'!$B$32="per Hour","Total Hours Saved for this Job Category","Total FTEs Saved for this 

Job Category") on detail sheet #30. Then reprotect the worksheet by choosing Protection – Protect 

Sheet from the Tools menu. Follow the same procedure to add more detail sheets as needed. Then, 

re-protect the workbook by choosing Protection – Protect Workbook from the Tools menu. Accept 

the protection defaults 

Labor Cost Multipliers Worksheet 

Once you have filled out all job categories on the Current Labor Rates worksheet, move to the Labor 

Cost Multipliers worksheet. For each job category, indicate whether you will calculate labor costs 

(and productivity benefits) on a per hour or per FTE basis19. The default value is per hour, but you 

 

19 With the per hour method, you will estimate internal labor hours needed for project development as well as 

internal labor hours “saved” as a productivity benefit, and the workbook will use an hourly rate to determine 

the value of these invested (or saved) work hours. With the per FTE method, you will estimate the 

development work in terms of full-time positions dedicated to the project and will estimate productivity 

savings in terms of FTEs eliminated from the budget (or reassigned), and the workbook will use an annual 

burdened salary to calculate the value of this labor. 

A-21 
can choose per FTE from a drop-down list in each category. You can use different methods for 

different categories. You do not need to remove blank rows. 

If you are electing to manually enter historical labor rates or future labor rates (as opposed to 

deriving them based on current rates and inflation), you will need to enter a rate for each project year 

for each job category on the Labor Cost Multipliers worksheet. You will need to unprotect the sheet 

to access these cells20. 

Internal Labor Usage Worksheet 

Use this worksheet to capture all internal labor that will be treated as project costs. Do not include 

labor hours that will be treated as reduced or negative productivity benefits. 

• The first two columns of this worksheet refer to the job titles and valuation methods you just 

entered on the Current Labor Rates and Labor Cost Multipliers worksheets. 

• For each job category that works on project development or support, describe the nature of 

the work performed. Then, fill in usage measures for each year in which internal labor 

occurs for the job category. If you are using the per Hour valuation method, the usage 

measure is the total of all labor hours in that category in each year incurred21. If you are 

using the per FTE method, the usage measure is the total number of positions dedicated to 

the project in each project year. 

• For job categories that do not help develop or support the project (but simply benefit from 

it), leave the Nature of Work and usage cells blank. 

Internal Labor Costs Worksheet 

This worksheet automatically calculates internal labor costs based on the usage recorded on the 

Internal Labor Usage worksheet and the labor cost multipliers for each job category. You cannot edit 

this sheet. However, if the costs seem out of line for a particular job category, you may wish to 

revisit the earlier labor worksheets to confirm that your usage estimates are consistent with your 

 

20 Choose Protection – Unprotect Sheet from the Tools menu. Begin in cell E9 (the first year for the first job 

category), and enter either a burdened hourly rate (if using the per hour valuation method) or burdened annual 

labor cost (if using the per FTE method). Complete all cells (all years for all job categories). Then, reprotect 

the worksheet by choosing Protection – Protect Sheet from the Tools menu and accepting the protection 

defaults. 

21 If more than one person from the category is involved in the work, be sure to use the total hours for all 

positions/individuals as opposed to the average amount per person. 

A-22 
valuation method (i.e., you have estimated hours worked for the per hour method or dedicated FTEs 

for the per FTE method.) 

Contract Costs Worksheet 

• Complete the Contract Costs Worksheet. 

• Review the various cost categories on the worksheet, and list all one-time and ongoing 

project costs in the appropriate category. List specific contracts or purchase orders if 

possible. 

• For each contract or purchase order, specify dollar amounts in the year incurred. For 

historical cash flows, use the actual dollar amounts you spent for this project. For future 

cash flows, use future year dollars22. Include only those portions of a contract or purchase 

order that apply to this project. The worksheet will automatically calculate the total 

contract/procurement costs for each year of the project life. 

• You may leave categories blank, and you may edit the names of the cost categories. 

• If you wish, you may delete any categories that do not apply to your project or any unused 

rows within a category. To do so, unprotect the worksheet23, highlight the row(s) you wish 

to delete by clicking on the row number label(s) at the far left of the row(s), and choose 

Delete from the Edit menu. Then, reprotect the sheet24. 

• If additional rows are needed, you may insert additional rows. First, unprotect the 

worksheet25. Then, highlight the row above which you want to insert a new row. Choose 

Rows from the Insert menu. When you have inserted all the rows you need, reprotect the 

worksheet26. 

 

22 Remember to consider the impact of inflation on future cash flows. For example, if you have an ongoing 

cost from an application service provider, you might anticipate annual fee increases when entering future year 

costs. If you anticipate purchasing equipment in future years, consider what the cost of that equipment will be 

at the time it is procured. 

23 Choose Protection – Unprotect Sheet from the Tools menu. 

24 Choose Protection – Protect Sheet from the Tools menu and accept the protection defaults. 

25 Choose Protection – Unprotect Sheet from the Tools menu. 

26 Choose Protection – Protect Sheet from the Tools menu and accept the protection defaults. 

A-23 
Productivity Benefit (Summary) Worksheet 

This worksheet is for review purposes only. Return to this worksheet after you have completed all 

Productivity Benefit Detail worksheets (see below). The summary worksheet calculates productivity 

benefits based on the hours or FTEs recorded on the productivity detail worksheets and the labor cost 

multipliers for each job category. You do not need to enter any data on the summary sheet. 

However, if the benefits seem out of line for a particular job category, you may wish to revisit the 

earlier Labor Cost Multipliers worksheet and Productivity Benefit Detail worksheets to confirm that 

your savings estimates are consistent with your valuation method (i.e., you have estimated hours 

saved for the per hour method or FTEs eliminated for the per FTE method.) 

Other Benefits Worksheet 

In the list below, review the examples of Other Quantifiable (non-productivity) Benefits for the 

application areas covered by your project. Mark the benefits that apply to your project (to assist you 

in completing the Other Benefits worksheet). Note any other benefits for your project. 

Other Benefits (Examples by Application Area) 

MAPPING AND DATA ACCESS 

Automated Map Production, Data Maintenance, and Data Access 

Other Benefit Examples

 Reduce map production and distribution costs – avoid printing and shipping costs of 

distributing sets of map books. Calculate reduced investment in specialized plotting 

equipment. 

 Reduce map editing costs – determine the contract cost savings for any external map 

editing being reduced. 

 Avoid cost of liability for failure to provide infrastructure information to field personnel 

– estimate the cost if the buried infrastructure were damaged due to lack of access to 

maps.

A-24 
Other Benefits (Examples by Application Area) 

EMERGENCY MANAGEMENT AND SECURITY 

Emergency Preparedness and Response/Critical Infrastructure Protection 

Other Benefit Examples

 Reduce repair and service interruption costs associated with emergencies – calculate 

savings in avoided penalties for service interruptions to customers, income generated 

with faster service restoration, reductions in mileage and repair material costs due to 

better information prior to reaching emergency site. 

 Maximize and expedite claim recovery – calculate potential for reduced time to process 

claims, determine value of having the cash sooner and estimate potential for increased 

claim recovery. 

 Reduce cost of maintaining/staffing emergency operations centers and avoid/reduce 

other emergency preparedness costs through consolidation of roles/activities in the 

region – determine reduced operating costs for facilities excluding labor (covered in 

the Labor Productivity worksheet).

GOVERNMENT ADMINISTRATION 

Property Appraisal/Legislative (Voting) Districting/Tax Assessment 

Other Benefit Examples

 Increase tax revenues by identifying properties that are not on the tax rolls – determine 

potential extent of properties not on the tax rolls, estimate revenue that could be brought 

in by discovered properties. 

 Increase rate-based revenues by identifying undocumented users – determine potential 

extent of undocumented users, estimate potential revenue from these users. 

A-25 
Other Benefits (Examples by Application Area) 

QUALITY OF LIFE 

Public Health and Safety/Community Services/Code Enforcement 

Other Benefit Examples

 Raise property values by improving community livability – estimate potential increased 

tax revenue from increased property values. 

 Secure/retain grant funding for community services programs – estimate value of grant 

funding. Estimate value of community services programs implemented (improved 

neighborhoods resulting in increased property values). 

 Increase tax revenues by attracting new business – estimate potential increased revenue 

from new businesses. 

 Reduce field infrastructure repair costs through more effective management of wild 

animal populations (e.g., armadillos, moles) that damage infrastructure – estimate 

potential savings from reduced mileage due to better route planning (labor savings 

should be picked up in the Labor Productivity worksheet). 

 Reduce insurance rates – estimate potential reduction in insurance rates through 

provision of better information to insurance providers.

A-26 
Other Benefits (Examples by Application Area) 

FIELD INFRASTRUCTURE MANAGEMENT 

Management of Roads and Utility Lines/Capital Improvements/Maintenance 

Other Benefit Examples

 Reduce operations and maintenance costs by avoiding breakdowns and optimizing 

programmed activities (better planning and scheduling of proactive work, equipment 

take-downs, and routine repairs) – estimate potential non-labor savings from avoidance 

of breakdowns. Estimate increased revenue from having facilities operable more of the 

time (greater sales). 

 Reduce cost of inventory by streamlining requirements for on-hand stock (supporting 

more just-in-time procurement) – consider potential reduction in capital expenses 

through reduction in inventory. Estimate potential savings due to reduced storage 

requirements. 

 Reduce capital costs by extending asset life – estimate potential savings from avoidance 

of asset replacement. 

 Reduce cost of line locate program by eliminating trips to locations that do not have 

assets – estimate non-labor component of this benefit from reduced vehicle costs. 

 Reduce cost of special projects/repairs by sharing common costs with other agencies 

working in the area – reduced costs of trenching, backfilling, and pavement restoration. 

A-27 
Other Benefits (Examples by Application Area) 

FACILITY MANAGEMENT 

Management of Plants, Buildings, and Property/Capital Improvements/Maintenance 

Other Benefit Examples

 Reduce operations and maintenance costs by avoiding breakdowns and optimizing 

programmed activities (better planning and scheduling of proactive work, equipment 

take-downs, and routine repairs) – estimate savings in equipment replacement costs due 

to fewer breakdowns, estimate potential increased revenue from having facilities 

operable more of the time. 

 Reduce cost of inventory by streamlining requirements for on-hand stock (supporting 

more just-in-time procurement) – consider potential reduction in capital expenses 

through reduction in inventory. Estimate potential savings due to reduced storage 

requirements. 

 Reduce capital costs by extending asset life – estimate potential savings from avoidance 

of asset replacement, including avoided costs associated with issuing bonds. 

 Reduce operating costs by improving source quality (e.g., influent, power).

SERVICE DELIVERY 

Call Management/Outage Management/Dispatch 

Other Benefit Examples

 Reduce fees, fines, or damage mitigation costs associated with inadequate response times 

– estimate potential for avoided fees to regulatory agencies. 

 Increase rate-based revenues by minimizing the length of service interruptions – estimate 

potential increase in revenue with reduction in service interruptions.

A-28 
Other Benefits (Examples by Application Area) 

CUSTOMER RELATIONSHIP MANAGEMENT 

Usage or Consumption Analysis/New Service Planning/Customer Compliance 

Other Benefit Examples

 Reduce fees, fines, or damage mitigation costs associated with interrupting service to 

special-needs customers – estimate potential savings in fines/fees related to service 

interruption. 

 Reduce operating costs through more effective use of interruptible customer programs – 

estimate cost savings due to utilizing interruptible customers to avoid more general 

outages resulting in reduced revenues. 

 Increase revenues by attracting and retaining more customers. 

 Reduce wastewater treatment costs by improving influent quality and/or reducing illegal 

discharge to the sewer system. 

 Reduce storm water system maintenance costs by reducing illegal discharges/dumping 

 Increase revenues by expanding opportunities to sell/provide additional services and 

better targeting the new service offerings and marketing.

A-29 
Other Benefits (Examples by Application Area) 

ENGINEERING 

Planning/Design/Construction/System Analysis/Network Modeling 

Other Benefit Examples

 Reduce the cost of preliminary design by reducing/eliminating the need for ad hoc aerial 

photography and mapping. 

 Reduce the cost of preliminary design by expediting/automating the production of 

alternative designs (reduced consulting fees). 

 Reduce the cost of construction and/or land acquisition through design optimization and 

better site/corridor selection – evaluate the current costs for land acquisition and 

construction costs related to siting issues and make prediction of savings with improved 

information and analysis capabilities. 

 Reduce cost of debt through more accurate long-term revenue forecasts – assess costs of 

current imprecise forecasting, estimate realistic level of improved forecasts, calculate 

effect of improved forecasting on debt. 

 Increase rate-based revenues by ensuring there is sufficient capacity to meet demand – 

calculate impact of having to deny additional customers due to lack of capacity. 

 Reduce cost of model maintenance by consolidating generalized and detailed models, 

eliminating redundant maintenance of node and link data, and/or reducing software 

license requirements – evaluate software licenses for potential redundancy and calculate 

savings by reducing number of licenses. 

 Reduce cost of operations through more effective modeling – evaluate operating costs 

due to overbuilt or underbuilt systems. 

 Reduce or delay capital costs through more thorough examination of alternatives (better 

models) – evaluate cases where capital costs might have been avoided with better GIS 

data and identify potential for avoiding these costs on future projects.

A-30 
Other Benefits (Examples by Application Area) 

LAND MANAGEMENT 

Development Review/Zoning/Permitting/Land Use Analysis 

Other Benefit Examples

 Avoid costs associated with duplicate or otherwise undesirable street names or addresses 

(e.g., avoid dispatch to wrong location and/or confusion of field personnel – affects costs 

associated with liability for incorrect/delayed dispatch). 

 Increase business license revenues (more accurate license data). 

 Increase tax revenues by expediting activities that raise property values.

REGULATORY COMPLIANCE 

Permit Application/Monitoring Programs/Compliance Management 

Other Benefit Examples

 Avoid or minimize fines by preventing violations – estimate extent of potential fines and 

calculate cost-avoidance. 

 Reduce report development and submittal costs. 

 Reduce cost of sampling/monitoring by optimizing routes and sample point selection - 

estimate savings in vehicle costs due to optimal routing.

ENVIRONMENTAL MANAGEMENT 

Natural Resource Preservation/Watershed Protection/Weather Monitoring 

Other Benefit Examples

 Avoid or minimize fines or restoration costs associated with environmental damage. 

 Reduce production/treatment costs by anticipating uncontrollable conditions (e.g., 

weather) that affect operations. 

 Reduce production/treatment costs by controlling land use and management (e.g., 

agricultural techniques, waste disposal methods, pesticide/herbicide use) that affect 

operations. 

 Reduce reactive maintenance costs by assembling resources in advance of uncontrollable 

conditions (e.g., weather) that affect demand and/or operations.

 

A-31 
Complete the Other Benefits worksheet. 

• List each benefit noted in the previous table. Enter the dollars saved in the appropriate year 

columns27. 

• For historical benefits, use actual dollar amounts. For future cash flows, use future year 

dollars28. If a benefit depends on other investments outside the scope of this project, note 

that assumption in the benefit description, and adjust the dollar savings to reflect the 

probability that the outside investment will occur. If you are preparing analyses of both 

proposed investments, consider combining the investments into a single analysis29. 

• You may leave blank rows on this worksheet. If you prefer to delete unused rows, you can 

unprotect the worksheet30, highlight the row(s) you wish to delete by clicking on the row 

number label(s) at the far left of the row(s), end choose Delete from the Edit menu. Then, 

reprotect the sheet31. 

• If additional rows are needed, you may insert additional rows. First, unprotect the 

worksheet32. Then, highlight the row above which you want to insert a new row. Choose 

Rows from the Insert menu. When you have inserted all the rows you need, reprotect the 

worksheet33. 

 

27 For evaluations of proposed future investments, you may wish to reduce the total dollars saved based on a 

less than 100% probability that the benefit will occur. If you choose to do so, make a note of your assumption 

in the description of the specific benefit. For example, if you estimate a savings of one million dollars due to 

lawsuit avoidance, but you believe the lawsuit was only a 25% possibility in the absence of the GIT 

investment, you should note this in the benefit description, note that $1,000,000 X 25% = $250,000, and show 

a benefit of $250,000 in the relevant project year. If you are citing cost-avoidance benefits in an analysis of 

actual benefits realized, you may also wish to adjust the dollar values based on the probability that the avoided 

event would have occurred in the absence of the GIT investment. 

28 Consider the impact of inflation on future benefits. For example, if you have an ongoing benefit from a 

reduction in contract data maintenance fees and you expect to use the same level of service each year, you may 

wish to anticipate an increase in savings (due to an increase in the annual fee) over the course of the project 

life. 

29 Alternatively, you may wish to run a sensitivity analysis on the dependent benefit. 

30 Choose Protection – Unprotect Sheet from the Tools menu. 

31 Choose Protection – Protect Sheet from the Tools menu and accept the protection defaults. 

32 Choose Protection – Unprotect Sheet from the Tools menu. 

33 Choose Protection – Protect Sheet from the Tools menu and accept the protection defaults. 

A-32 
• The worksheet will automatically calculate the total non-productivity benefits for each year 

of the project life. 

Productivity Benefits Detail Worksheet(s) 

 In the following table, review the examples of Labor Productivity Benefits for the application areas 

covered by your project. Mark the benefits that apply to your project (to assist you in completing the 

Productivity Benefits Detail worksheets). Note any other productivity benefits for your project. 

Labor Productivity Benefits (Examples by Application Area) 

MAPPING AND DATA ACCESS 

Automated Map Production, Data Maintenance, and Data Access 

Labor Productivity Benefit Examples

 Reduce map production and distribution costs – determine the avoided labor cost of printing 

and shipping annual sets of map books (in house) as well as saving of internal staff hours 

associated with the production of this map product. 

 Reduce map editing costs (internal productivity) – calculate savings by estimating reduction 

in labor per change order/work order multiplied by number of changes per year 

 Eliminate costs of research/reconciliation of inconsistent data – calculate hours currently 

spent on this effort, estimate a realistic level of improvement, and show the difference as a 

labor savings. 

 Reduce the cost of research and decision-making by expediting/facilitating access to critical 

information – calculate hours currently spent on this effort, estimate realistic level of 

improvement, and show the difference as a labor savings.

A-33 
Labor Productivity Benefits (Examples by Application Area) 

EMERGENCY MANAGEMENT AND SECURITY 

Emergency Preparedness and Response/Critical Infrastructure Protection 

Labor Productivity Benefit Examples

 Reduce repair and service interruption costs associated with emergencies – estimate the 

reduction in staff labor. 

 Reduce the cost of preparing insurance claims – estimate the reduction in staff labor. 

 Reduce cost of maintaining/staffing emergency operations centers and avoid/reduce other 

emergency preparedness costs through consolidation of roles/activities in the region – 

estimate labor savings from consolidation of operations center staffing.

GOVERNMENT ADMINISTRATION 

Property Appraisal/Legislative (Voting) Districting/Tax Assessment 

Labor Productivity Benefit Examples

 Increase productivity of appraisal staff – estimate potential time that could be saved by staff. 

 Increase productivity of election planning/management staff – estimate potential time saved 

by staff.

QUALITY OF LIFE 

Public Health and Safety/Community Services/Code Enforcement 

Labor Productivity Benefit Examples

 Improve staff productivity by reducing travel time and numbers of trips – estimate potential 

savings in staff time. 

 Reduce field infrastructure repair costs through more effective management of wild 

animal populations (e.g., armadillos, moles) that damage infrastructure – estimate 

potential reduction staff time required for repairs.

A-34 
Labor Productivity Benefits (Examples by Application Area) 

FIELD INFRASTRUCTURE MANAGEMENT 

Management of Roads and Utility Lines/Capital Improvements/Maintenance 

Labor Productivity Benefit Examples

 Improve field crew productivity by reducing travel time – estimate potential reduction in staff 

travel time. 

 Reduce operations and maintenance costs by avoiding breakdowns and optimizing 

programmed activities (better planning and scheduling of proactive work, equipment takedowns, and routine repairs) – estimate potential savings from avoidance of breakdowns (staff 

time). 

 Reduce cost of repair by expediting access to asset and maintenance information (faster 

repairs) – estimate potential reduction in time to make repairs. 

 Reduce cost of capital project planning (better asset performance and condition information) – 

estimate potential savings in project planning cycle (reduced labor time). 

 Reduce cost of line locate program by eliminating trips to locations that do not have assets – 

estimate potential savings from reduced staff time. 

 Reduce cost of special projects/repairs by sharing common costs with other agencies working 

in the area – estimate potential labor savings from cost sharing (only digging once).

A-35 
Labor Productivity Benefits (Examples by Application Area) 

FACILITY MANAGEMENT 

Management of Plants, Buildings, and Property/Capital Improvements/Maintenance 

Labor Productivity Benefit Examples

 Reduce operations and maintenance costs by avoiding breakdowns and optimizing 

programmed activities (better planning and scheduling of proactive work, equipment takedowns, and routine repairs) – estimate potential savings from avoidance of breakdowns (staff 

time). 

 Reduce cost of repair by expediting access to asset and maintenance information (faster 

repairs) – estimate potential reduction in time to make repairs. 

 Reduce cost of inventory by streamlining requirements for on-hand stock (supporting more 

just-in-time procurement) – estimate potential for reduced storage facility staffing. 

 Reduce operating costs by improving source quality (e.g., influent, power) – estimate staff 

time savings.

SERVICE DELIVERY 

Call Management/Outage Management/Dispatch 

Labor Productivity Benefit Examples

 Improve productivity of field crews through reduced travel time and reduction/elimination of 

duplicate work orders – estimate potential savings in labor time. 

 Reduce cost of call management by reducing transfers and expediting call processing (internal 

productivity benefit and/or avoidance of new call taker positions) – estimate the labor hours or 

positions saved.

A-36 
Labor Productivity Benefits (Examples by Application Area) 

CUSTOMER RELATIONSHIP MANAGEMENT 

Usage or Consumption Analysis/New Service Planning/Customer Compliance 

Labor Productivity Benefit Examples

 Reduce wastewater treatment costs by improving influent quality and/or reducing illegal 

discharge to the sewer system. Model changes in wastewater treatment processes if there were 

no sudden loads to the system. Use results to estimate potential labor savings with greater 

equilibrium in system. 

 Reduce storm water system maintenance costs by reducing illegal discharges/dumping. Model 

effects of illegal discharges on storm water system. Model system without illegal discharges. 

Estimate potential reduction in maintenance costs (staff time) without illegal discharge. 

A-37 
Labor Productivity Benefits (Examples by Application Area) 

ENGINEERING 

Planning/Design/Construction/System Analysis/Network Modeling 

Labor Productivity Benefit Examples

 Reduce the cost of preliminary design by reducing/eliminating the need for ad hoc aerial 

photography and mapping – calculate the labor hours that would have been associated with 

managing the aerial photography and ad hoc mapping. 

 Reduce the cost of preliminary design by expediting/automating the production of alternative 

designs (internal productivity improvement or reduced consulting fees) - calculate the savings 

in time for producing an alternative design multiplied by the optimum number of alternative 

designs. 

 Reduce the cost of design by expediting access to relevant data (internal productivity) – 

estimate staff time savings. 

 Reduce the cost of construction and/or land acquisition through design optimization and better 

site/corridor selection – evaluate current costs for land acquisition and construction costs 

related to siting issues and make prediction of labor savings with improved information and 

analysis capabilities. 

 Reduce the cost of construction through more efficient inspections and construction 

management/coordination - estimate the time saved by providing data to inspectors in the 

field, including not only inspectors time but other staff time affected by this improvement. 

 Reduce cost of model maintenance by consolidating generalized and detailed models, 

eliminating redundant maintenance of node and link data, and/or reducing software license 

requirements – calculate labor costs of maintaining redundant data. 

 Reduce cost of communicating model results – calculate labor costs associated with this 

effort. 

 Reduce cost of operations through more effective modeling – evaluate labor-related 

operations costs associated with overbuilt/underbuilt systems. 

 Improve model accuracy and timeliness – estimate time saved through improved modeling, 

and determine labor saved as a result.

A-38 
Labor Productivity Benefits (Examples by Application Area) 

LAND MANAGEMENT 

Development Review/Zoning/Permitting/Land Use Analysis 

Labor Productivity Benefit Examples

 Reduce the cost of internal development review, permitting, and/or inspections (internal 

productivity) – estimate potential savings in staff time. 

 Reduce the cost of joint development review (better regional communication – reduce lag 

time and additional reviews; productivity) – estimate potential savings in staff time. 

 Avoid costs associated with duplicate or otherwise undesirable street names or addresses (e.g., 

avoid dispatch to wrong location and/or confusion of field personnel –estimate potential 

savings in staff time.

REGULATORY COMPLIANCE 

Permit Application/Monitoring Programs/Compliance Management 

Labor Productivity Benefit Examples

 Reduce the cost of assembling permit application/renewal documentation – estimate potential 

staff time savings. 

 Reduce report development and submittal costs (productivity benefits of automated reporting) 

– estimate potential staff time savings from automated reporting. 

 Reduce cost of sampling/monitoring by optimizing routes and sample point selection – 

estimate potential staff time savings.

A-39 
Labor Productivity Benefits (Examples by Application Area) 

ENVIRONMENTAL MANAGEMENT 

Natural Resource Preservation/Watershed Protection/Weather Monitoring 

Labor Productivity Benefit Examples

 Avoid or minimize fines or restoration costs associated with environmental damage – estimate 

potential labor savings due to avoiding environmental damage by modeling various damage 

scenarios and determining cost-avoidance opportunities. 

 Reduce production/treatment costs by controlling land use and management (e.g., agricultural 

techniques, waste disposal methods, pesticide/herbicide use) that affect operations – model 

effects on production/treatment of uncontrolled conditions. Determine optimum control 

methodology from a variety of options. Estimate potential staff time savings versus less 

desirable options. 

 Reduce reactive maintenance costs by assembling resources in advance of uncontrollable 

conditions (e.g., weather) that affect demand and/or operations – estimate potential savings 

from avoidance of assembling resources on an emergency basis (reduced labor).

A-40 
Complete a Productivity Benefit Detail worksheet for each job category that will have productivity 

benefits. The Productivity Benefit Details sheets are numbered 1 through 2934, and each displays one 

job category from your Current Labor Rates worksheet. 

• Open the first Productivity Benefit Detail sheet. On the first line under Specific Productivity 

Benefits, describe the source of a productivity benefit experienced by this job category. If 

you are quantifying the value of hours saved for this job category35, you may use the space 

in the Specific Benefit column to cite the hours saved per position and the total number of 

positions affected to arrive at a total hours saved. 

For each year of the project life, fill in the hours or FTEs saved as a result of this specific 

benefit36, 37. If you are quantifying hours saved, you should enter the total number of hours 

saved for all positions in this category. If you are valuing productivity gains strictly as FTEs 

eliminated, then enter the number of FTEs eliminated as a result of this benefit source. Use 

fractional FTEs only if you are eliminating part-time positions or you are eliminating a 

position in the middle of a particular year38. Repeat this process for additional sources of 

productivity benefits (each on a new line). If you are recasting an internal labor cost as a 

reduced productivity benefit, list the internal labor activity on a Specific Productivity 

Benefit line and enter the hours (or FTEs) as negative values. 

 

34 You may have more or fewer detail sheets if you added or deleted them while preparing your Current Labor 

Rates worksheet. 

35 See instructions for the Project Setup worksheet for more details on decisions related to the quantification of 

labor savings. 

36 For evaluations of proposed future investments, you may wish to reduce the total hours or FTEs based on a 

less than 100% probability that the benefit will occur. If you choose to do so, make a note of your assumption 

in the description of the specific productivity benefit. For example, if you estimate a labor savings of 1600 

hours per year due to elimination of manual research of utility availability, but you believe this benefit is only 

75% likely to occur (pending the outcome of a city code amendment that is under review, for example), you 

would note this in the benefit description. Note that 1600 X 75% = 1,200, and show a savings of 1,200 hours in 

the relevant project years. 

37 You may use this worksheet to indicate hours or FTEs saved by avoiding the creation of new positions in a 

particular job category if these new positions would be required in the absence of the project under analysis. 

However, consult with your finance department regarding the total number of positions you are assuming 

would have been (or would have to be) created in the absence of the project. 

38 If you are eliminating an FTE as the result of multiple productivity benefit sources, you can list them all on 

one line, or you can itemize them and show fractional FTE savings. If you choose to show fractional FTE 

savings, check the Totals line at the end to ensure that your line items add up to whole FTEs (unless you need 

a fractional total to account for part time positions or mid-year elimination of an FTE). 

A-41 
• If you need more lines, you can insert rows above the totals line39. 

• Complete a Productivity Benefit Detail worksheet for each job category that benefited from 

(or will benefit from) the project. If there are no benefits for a particular category, simply 

leave the corresponding sheet blank. 

• When you have completed all the detail sheets, return to the first Labor Productivity 

worksheet (the summary worksheet) to review the totals (see previous section). 

Performing Sensitivity Analysis 

If you are preparing an evaluation of a proposed future investment, you should run additional 

analyses to determine the sensitivity of your financial indicators to the values you have assumed 

(estimated) for key project costs and benefits. 

• First, save a copy of your completed Financial Details workbook under a new name. The 

name of this copy should clearly indicate that the file is used for sensitivity analysis (to 

minimize risk of confusion with the original workbook). 

• In the new copy of your workbook, modify a variable on which you wish to conduct 

sensitivity analysis. This may be the dollar value or the timing of one or more costs or 

benefits. 

– For example, if you projected internal labor usage at 2,000 hours, but there is a chance it 

may be as high as 6000 hours, modify the labor usage and review the impact on your 

financial indicators. If you anticipated an ongoing labor productivity benefit of two FTEs 

beginning in Year 2, but you may not be able to eliminate the FTEs until as late as Year 

5, modify the appropriate Productivity Benefit Detail sheet to delay the labor savings. 

– You may wish to consider the high and low ends of the ranges for various estimates. For 

example, if you have a regulatory fine avoidance benefit of $50,000, but the fine could be 

anywhere from $10,000 to $100,000, look at the impact of using both the low and the 

high values. Refer to the research publication A Practitioners Guide to Business Case 

Development and Return on Investment Methodology for Geospatial Information 

Technology for more information about the reasons for sensitivity analysis. 

 

39 Unprotect the sheet by selecting Protection - Unprotect Sheet from the Tools menu. Highlight the totals 

row by clicking on the row number label at the far left, and choose Rows from the Insert menu. When you are 

done inserting rows, choose Protection – Protect Sheet from the Tools menu and accept the protection 

defaults. 

A-42 
• Each time you change a variable, return to your Financial Analysis worksheet to see the 

impact on the key financial indicators. If you wish, you can save a separate workbook for 

each sensitivity analysis and attach this to your business case. Alternatively, you can simply 

make a note of the variable modified and the impact on the financial metrics. 

• To run sensitivity analysis for the overall project, first complete the instructions in Part 3 to 

set up your original Multiparticipant Summary Workbook. Then, follow the instructions in 

Part 3 to create a “working copy” of the Summary Workbook in a separate directory from 

the one that holds your original summary analysis. Save copies of each of the participant 

workbooks in the working directory. With your Summary Workbook open, modify 

assumptions and variables in one or more of the participants’ Financial Details workbooks. 

Check the Summary Workbook to see the impact on the financial matrics for the overall 

project. 

A-43 
PART 3. INSTRUCTIONS FOR THE MULTI-PARTICIPANT SUMMARY 

The following instructions explain how to complete the Microsoft® Excel workbook that provides 

the Multi-participant Summary financial metrics. 

Preparation 

Complete Financial Details Workbooks for each participating agency using the same template. You 

can link up to 20 different individual workbooks to a Multiparticipant Summary Workbook40. 

Choose the multi-participant workbook template that reflects the investment timing (past or future) 

and the length of the project life (5, 10, or 20 years) that was used for the each of the Financial 

Details Workbooks. You can customize the length of the project life if desired41. 

When you open the Multiparticipant Summary Workbook, you will be asked whether you wish to 

update links. Click the Don’t Update button42. 

From the Edit menu, choose the Links option. You will see a list of 20 linked data sources 

beginning with A.xls and running through T.xls. Select the first link (A.xls) and click the Change 

Source button. Use the file directory window to select the directory and file name of your first 

participant’s Financial Details Workbook. Then select the B.xls link, and change the source to your 

second participant’s Financial Details Workbook. Continue this process until you have linked all 

participants’ workbooks43. 

 

40 If you have more than 20 participants that require separate Financial Details Workbooks, you can create 

multiple “Child” Multi-participant Summary Workbooks and link these to the “Parent” Summary Workbook. 

If you use this technique, the “child” summaries should all be based on the same template file. Be sure to 

include a group name at the top of the “child” summary setup sheet; this name will carry forward to the 

“parent” summary setup sheet. 

41 It is easier to remove extra years than to add new ones, so if you want to customize the project length, start 

with a workbook that covers a longer period than what you desire. For example, if you want to analyze a 12-

year project life, start with one of the 20-year workbooks. 

42 You should not update links until you have defined your links to participants’ Financial Details Workbooks 

and saved your summary. After setting up the summary, you may choose to update values from linked sources 

each time you open the summary (or at any time by going to the Edit, Links menu.) 

43 If you have more than 20 workbooks to link, first create “child” summaries by linking up to 20 Financial 

Details Workbooks to a Multiparticipant Summary template. Then, create the master Multiparticipant 

Summary and link it to the child summaries. When you change the source from the Edit, Links menu, you 

may choose either a Financial Details Workbook or a “child” Multiparticipant Summary Workbook. In either 

case, the linked workbooks must use the same project period and starting year. 

A-44 
IMPORTANT: Start with the A.xls link. The Multiparticipant Summary Workbook takes its year 

labels from this source. 

IMPORTANT: The Multiparticipant Summary templates will open with worksheet protection turned 

off on the Project Summary Sheet so that you can easily create the links to the source Financial 

Details workbooks. You should protect this worksheet as soon as you have established the links so 

that you do not inadvertently overwrite a formula. From the Tools menu, choose the Protection

option, and then choose Protect Sheet. Accept the default protection options and click OK. 

NOTE: If you move or rename a participant’s Financial Details Workbook after you have linked it to 

the summary, Microsoft® Excel will not be able to locate it and will prompt you to specify the 

source location the next time you update links. Follow the prompts to reestablish the link to the new 

source file path and name. You will have to unprotect the worksheet to update the link and then 

reprotect it afterwards. 

You may add new participants at any time by unprotecting the summary sheet and using the Links

option from the Edit menu to change the source for the next available link. Remember to reprotect 

the worksheet afterwards. If you had previously deleted the letter “label” for the new participant on 

the summary sheet, retype the label, and the participant’s workbook information will appear. 

Summary Setup Worksheet 

Complete the Summary Setup sheet (first sheet) on the Multi-participant Summary Workbook. 

• Ensure that the Summary Setup Sheet is protected. From the Tools Menu, choose 

Protection. If you see the option Unprotect Sheet, this indicates that the worksheet is 

protected; click anywhere in the body of the worksheets to leave the Tools menu. However, 

if you see the option Protect Sheet, then click on the Protect Sheet option, accept the 

default protection settings and click OK to finish protecting the worksheet. 

• Enter the name of the project on the top line of the setup sheet. 

• Enter the date you intend to complete and submit your analysis. 

• Enter the name of the consortium that is supporting the project. If there is no formal group 

or consortium, use the Delete key to remove the contents of the consortium name cell. 

When the cell contents are deleted, the label at left will also disappear. It will reappear if 

you later decide to add a group name. 

• The next section of the setup sheet summarizes the key financial metrics. These values will 

change automatically as you link and update participating agencies’ Financial Details 

Workbooks. The cells will contain zeroes or related error messages (such as #DIV/0 or 

#VALUE) until you have linked at least one valid Financial Details Workbook. The metrics 

A-45 
on the Summary Setup sheet relate to the project as a whole and may differ from the metrics 

for each individual participant. 

• Enter an average inflation rate for the project period. This inflation rate should reflect a 

general increase in the cost of goods and services. The worksheet suggests a value of 2.5% 

for the general inflation rate, but you should replace this value with an inflation assumption 

that is acceptable to the finance departments of participating agencies. NOTE: It is 

acceptable to use different inflation rates within each agency’s individual Financial Details 

Workbook, but for the summary financial analysis, you will have to select one inflation rate 

for determination of the shared investment’s discount rate. 

• Enter the annual cost of capital for purposes of calculating present values44. The worksheet 

suggests a value of 5%, but you should replace this value with an assumption acceptable to 

the finance departments of each participating agency. NOTE: It is acceptable to use 

different costs of capital within each agency’s individual Financial Details Workbook, but 

for the summary financial analysis, you will have to select one cost of capital for 

determination of the shared investment’s discount rate. 

• The project life is taken from the workbook you selected when you changed the source for 

the A.xls link. If the project life is different than the standard life of your template file, you 

will need to adjust the project length in your Multiparticipant Summary Workbook: 

- If you need to reduce the length of the project, you will first need to unprotect the setup 

worksheet45. Highlight rows 57 through 106 (rows 58 through 105 are hidden). Choose 

Unhide from the Rows option on the Format menu. Then, go to the last column in 

which project year labels are displayed, select the cells in rows 61 through 105 for each 

of the columns you wish to eliminate, and then clear the cells by going to the Edit menu 

and selecting Clear, All. IMPORTANT: Clear from the end of the project life, not the 

beginning. Then, re-hide the rows and reprotect the worksheet46. Next, go to the 

Financial Summary worksheet. Unprotect the worksheet, highlight the full columns you 

 

44 This is the annual rate of return that could be expected from an alternative investment that is forgone in 

order to fund the project being analyzed. Some organizations use the return on a secure investment like 

government bonds. The cost of capital is used to convert future cash flows into current (present) values and to 

convert historical cash flows into inception year dollars. Refer to the research publication for a more detailed 

explanation of the opportunity cost of capital. 

45 From the Tools menu, choose Protection and then choose Unprotect Sheet. 

46 To re-hide the rows, highlight the entire row for rows 57 through 105, and then go to the Format menu and 

choose Rows, Hide. To reprotect the worksheet, go to the Tools menu and choose Protection and then choose 

Protect Sheet. Accept the protection options displayed. 

A-46 
wish to eliminate, and clear the cells by going to the Edit menu and selecting Clear, All 

Then, reprotect the worksheet. 

- If you need to increase the length of the project, you will first need to unprotect the setup 

worksheet47. Highlight rows 57 through 106 (rows 58 through 105 are hidden). Choose 

Unhide from the Rows option on the Format menu. Then, go to the last column in 

which project year labels are displayed and copy the cells in rows 61 through 10548. 

Then, click in the cell in row 61 that is immediately to the right of the column you just 

copied and choose Paste from the Edit menu. Continue copying and pasting cells until 

you have increased the project life to the desired length. Then, reprotect the worksheet49. 

Next, go to the Financial Summary worksheet. Unprotect the worksheet, highlight the 

farthest data column to the right and copy it50. Paste directly in the column to its 

immediate right51. Repeat this process until you have added as many new columns as 

you need. Then, reprotect the worksheet. 

• All of your linked financial workbooks will appear in the participant list on the summary setup 

sheet. The list includes an editable row label (A-, B-, etc.) and a read-only view of each 

participant’s name, workbook file name, and key financial assumptions and metrics from each 

participant’s internal analysis of the project. 

• The Summary Sheet will warn you if there is a discrepancy between the summary project length 

or starting year and a participant’s project length or starting year. NOTE: All participants’ 

workbooks must use the same project length and starting year. Financial data cannot be 

consolidated properly if participants use different time periods. If you see this error message, 

adjust the year labels in the participant’s Financial Details Workbook to match the standard for 

the project. 

 

47 From the Tools menu, choose Protection and then choose Unprotect Sheet. 

48 Click the mouse on the cell in row 61 and, holding the button down, drag the highlight down to include the 

cell in row 105. Then, from the Edit menu, choose Copy. 

49 From the Tools menu, choose Protection and then choose Protect Sheet. Accept the protection options 

displayed. 

50 Click on the column header to select the entire column. Then choose Copy from the Edit menu. 

51 Click on the column header to select the entire column. Then choose Paste from the Edit menu. 

A-47 
• If you have linked fewer than 20 source documents, delete the unused letter labels in the 

participant list. This will clear the row. If you later decide you want to use that link for a new 

participant, you can retype the label, and the linked document data will reappear52. 

Financial Summary Worksheet 

This worksheet summarizes costs and benefits for each participating agency, converts them into 

present values using the cost of capital and inflation rate from the Summary Setup Worksheet, and 

calculates the net present value (or net value in inception year dollars), annual return on investment, 

breakeven point, and payback period. All fields on this worksheet are calculated automatically. 

 

52 If you choose to break the links to unneeded sources in the Edit, Links menu, the links cannot be 

reestablished, and you will have to create a new summary workbook if you ever want to add participants. It is 

better not to break the links. 

A-48