Conventions used in the Cost Benefit Analysis (CBA) Toolkit:
1. The CBA Toolkit is an Excel Workbook containing three worksheets: a. TangibleChanges_1 b. ProjectCosts_2 c. InvestmentSummary_3 2. Some worksheets contain locked cells and hidden/locked rows. Those cells in blue text are unlocked and indicate where data input is required. Cells with black text indicate those cells that are locked and cannot be edited. 3. Data entered into the first two worksheets (TangibleChanges_1 and ProjectCosts_2) are used for calculations in the third worksheet, InvestmentSummary_3.
Instructions for Completing CBA Calculations:
1. Go to the spreadsheet tab TangibleChanges_1: Step 1 - Enter the division or office name, project title, program(s) name(s) that this project is intended to benefit, and the name of the project sponsor. This information is carried forward to the headers of the other two CBA worksheets (ProjectCosts_2, InvestmentSummary_3). Step 2 - Enter the estimates for the expected tangible changes (expressed as positive or negative costs) resulting from the project implementation over the next five fiscal years. Refer to the tangible benefits identified in the Benefits Realization Table in the DEP Business Case. This will assist you in determining the changes to the program or programs that will be recorded in this form. Enter the tangible changes in the appropriate category listed in sections A E (e.g., Personnel, Plant & Facility, External Service Providers, Data Processing and Others.) For example: if the project is expected to reduce the number of staff augmentation contractor FTEs required to support the program by two in FY 2009-10, then in FY 2008-09 the value for change in baseline cost and number of contractor FTEs will be zero. However, in FY 2009-10 enter (-2) FTEs and a negative value to indicate the cost that will be saved. If these two contractor FTEs are to be permanently deleted from the program, the same (-2) FTEs and negative value for salary should be carried forward into each subsequent year to reflect the expected reductions from current baseline costs. Tangible changes may include anticipated reductions in program staff and operational costs, increased revenue and quantifiable (tangible) public benefits. Negative values would correspond to improved or reduced program operation costs. Do not include any intangible changes in this spreadsheet. Intangible changes (benefits) are only documented in the Benefits Realization Table in the DEP Business Case document. When entering estimates, consider the timing of the conclusion of the current operation and the startup of the new operation as well as the costs associated with each when identifying the expected changes in program costs. The estimated tangible changes should be identified for the
135534830.xls.ms_office Instructions Version 1.0 (1/22/08)
DEP IT Project Cost-Benefit Analysis Tangible Changes
DEP Division/Office: Division of X Program(s) Title: Program Y
Project: Project X Project Sponsor: Jane Doe
Tangible Changes in Program Operations resulting from Project Implementation
Program Operational Cost Elements
Changes in Program Operational Costs (Negative Values = Decreased Costs) A. Personnel -- Operational Costs A-1.a. State FTEs (Salaries & Benefits) A-1.b. State FTEs (# FTEs) A-2.a. OPS FTEs (Salaries) A-2.b. OPS FTEs (# FTEs) A-3.a. Staff Augmentation (Contract Cost) A-3.b. Staff Augmentation (# of Contract FTEs) B. Plant & Facility -- Operational Costs C. External Service Provider -- Operational Costs
FY 2008-09 $0 $0 0.00 $0 0.00 $0 0.00 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
C-1. Contractor Services C-2. Maintenance & Support Services C-3. Network / Hosting Services C-4. Data Communications Services C-5. Other Specify D. Data Processing -- Operational Costs D-1. Hardware D-2. Software D-3. Other Specify E. Others -- Operational Costs E-1. Training E-2. Travel E-3. Other Specify Subtotal of Operational Costs ( Rows A through E) F. Revenues / External Contribution / Fiscal Offsets F-1. Revenues Specify F-2. Federal Participation Specify F-3. Grants Specify Total Changes in Program Operational Costs (Sum of Rows A through E minus Row F) Cumulative Change InvesmentSummary sheet for investment summary calculations
FY 2009-10 ($25,000) $0 0.00 ($25,000) (1.00) $0 0.00 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 ($25,000) $50,000 $0 $0 $50,000 ($75,000) ($75,000)
FY 2010-11 ($50,000) $0 0.00 ($50,000) (2.00) $0 0.00 $0 ($250,000) $0 ($250,000) $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 ($300,000) $50,000 $0 $0 $50,000 ($350,000) ($425,000)
FY 2011-12 ($270,000) $0 0.00 ($20,000) (1.50) ($250,000) (2.00) ($25,000) ($300,000) $0 ($300,000) $0 $0 $0 $125,000 $125,000 $0 $0 ($24,000) $1,000 ($15,000) ($10,000) ($494,000) $50,000 $0 $0 $50,000 ($544,000) ($969,000)
FY 2012-13 ($250,000) $0 0.00 $0 0.00 ($250,000) (2.00) ($25,000) ($300,000) $0 ($300,000) $0 $0 $0 $25,000 $25,000 $0 $0 ($15,000) $0 ($15,000) $0 ($565,000) $0 $0 $0 $0 ($565,000) ($1,534,000)
TOTAL ($595,000) $0 0.00 ($95,000) (0.90) ($500,000) (0.80) ($50,000) ($850,000) $0 ($850,000) $0 $0 $0 $150,000 $150,000 $0 $0 ($39,000) $1,000 ($30,000) ($10,000) ($1,384,000) $150,000 $0 $0 $150,000 ($1,534,000)
Negative Values indicate decreased Costs (i.e., Tangible Benefits). Positive values indicate increased operational costs outside of those specified in the ProjectCosts spreadsheet. FY Totals are carried forward to the
Character of Program Cost Change Estimate
Choose Type
Detailed/Rigorous Not to Exceed Order of Magnitude
Level of Uncertainty - Enter % (+/-) +50%, -25%
135534830.xls.ms_office TangibleChanges_1 Version 1.0 (1/22/08)
Page 2 of 4
DEP IT Project Cost-Benefit Analysis
DEP Division/Office: Division of X Program(s) Title: Program Y
Division of X Program Y
Project: Project X Project Sponsor: Jane Doe
Project Cost Elements (Project Planning,Development & Implementation Only No Operational Costs) State FTEs (Salaries & Benefits) OPS FTEs (Salaries) Staff Augmentation (Contract Cost) Consultant Services Hardware Software Network Infrastructure Training Travel Other Specify Total Project Costs (*) Cumulative Project Costs Total Project Costs are carried forward to the InvestmentSummary form
Project Cost Table
FY 2008-09 $0 $15,000 $200,000 $100,000 $75,000 $250,000 $0 $1,000 $0 $0 $641,000 $641,000 FY 2009-10 $0 $10,000 $200,000 $0 $12,000 $0 $0 $1,000 $0 $0 $223,000 $864,000 FY 2010-11 $0 $0 $100,000 $0 $0 $0 $0 $0 $0 $0 $100,000 $964,000 FY 2011-12 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $964,000 FY 2012-13 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $964,000 TOTAL $0 $25,000 $500,000 $100,000 $87,000 $250,000 $0 $2,000 $0 $0 $964,000
Character of Project Costs Estimate
Choose Type
Detailed/Rigorous Not to Exceed Order of Magnitude
Level of Uncertainty - Enter % (+/-) 50%
135534830.xls.ms_office ProjectCosts_2 Version 1.0 (1/22/08)
Page 3 of 4
DEP IT Project Cost-Benefit Analysis Project Investment Summary
Agency: Division of X Program(s) Title: Program Y
Project: Project X Project Sponsor: Jane Doe Investment Summary
FY 2008-09 4.24% FY 2009-10 4.36% FY 2010-11 4.22% FY 2011-12 4.19% FY 2012-13 4.39% TOTAL
Cost of Capital (as published by State CFO)
Cost Benefit Analysis
I II
Net Savings Resulting from the Project
(TangibleChanges Form)
$0 $641,000
$75,000 $223,000
$350,000 $100,000
$544,000 $0
$565,000 $0
$1,534,000 $964,000
Total Project Cost (ProjectCosts Form)
Return on Investment Analysis
III IV V VI VII
Return on Investment (Row I minus Row II) Payback Period (years) Breakeven Fiscal Year Net Present Value (NPV) Internal Rate of Return (IRR)
($641,000) 4 2011-12 $387,435 19.82%
($148,000)
$250,000
$544,000
$565,000
$570,000
Payback Period is the time required to recover the investment costs of the project. Fiscal Year during which the project's investment costs are recovered. NPV is the present-day value of the project's benefits less costs over the project's lifecycle. IRR is the project's rate of return.
Proposed Funding Sources for Project Costs by Fiscal Year
Prospective Source(s) of Project Funding
Investment Summary
VIII General Revenue
FY 2008-09 $0 $0 $0 $0 $0
FY 2009-10 $0 $0 $0 $0 $0 $0
FY 2010-11 $0 $0 $0 $0 $0 $0
FY 2011-12 $0 $0 $0 $0 $0 $0
FY 2012-13 $0 $0 $0 $0 $0 $0
TOTAL $0 $0 $0 $0 $0 $0
Trust Fund X Federal Match XII Grants XII Other
IX
Specify Specify Specify Specify Project Funding Totals
$0
135534830.xls.ms_office InvestmentSummary_3 Version 1.0 (1/22/08)
Page 4 of 4
