Cost Benefits Analysis (CBA) Template Excel Free Download

Cost Benefits Analysis (CBA) Template Excel Free Download

Cost Benefits Analysis (CBA) Template Excel Free Download
Cost Benefits Analysis (CBA) Template Excel Free Download

Wikipedia defined Cost–benefit analysis, sometimes also called benefit–cost analysis, is a systematic approach to estimating the strengths and weaknesses of alternatives used to determine options which provide the best approach to achieving benefits while preserving savings.

The Cost Benefits Analysis (CBA) Template is an Excel Workbook containing three worksheets:

  1. Tangible Changes
  2. Project Costs
  3. Investment Summary
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.

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 Tangible Changes:
Tangible Changes
Tangible Changes

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 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 ( 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 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 five fiscal year period, but must be provided for a minimum of one fiscal year of program operations. If a change in operational costs or revenues is realized during part of a fiscal year, the amount of the change should be based on the months in which the increase/decrease is actually realized and the prorated change should be shown in that fiscal year. 

Step 3 - Enter any changes in program-related revenues, external contributions and other fiscal offsets in section F of the spreadsheet.  Enter any reductions in revenues as negative values, and enter any increases in revenues as positive values. 

Step 4 - Go to the “Character of Program Cost Change Estimate” table. In this table, identify the type and level of uncertainty associated with the estimates.

Indicate the type of estimate by categorizing it as one of the following:
  • Detailed/rigorous – Estimate determined based on detailed  and known requirements for the system to be built or service to be purchased. 
  • Not to exceed – Estimate is an  an upper limit  based on mostly known requirements and estimates. 
  • Order of magnitude – Determines an estimate that could vary between 50-100 percent based on very high-level requirements for the system or service involved.
Indicate the level of uncertainty in the estimates provided.  For example, an uncertainty percentage of 10% indicates that the noted estimates are within +/- 10% of the estimate.

2. Go to the Project Costs worksheet
Project Costs worksheet
Project Costs worksheet

Step 1 - Enter those estimated costs directly related to the project in the “Project Costs Table”.  Project cost elements include salaries and expenses of FTEs (e.g., state employees, OPS and staff augmentation contractor), consultant fees, hardware and software purchases, network infrastructure, training, travel, and others (to be specified when used).  

Enter costs by fiscal year. Total costs for the proposed project can include information technology application and infrastructure costs, programming costs, testing, project monitoring, and project management costs. If development and implementation costs will be incurred for only a portion of a fiscal year, estimate the prorated costs for that fiscal year.

Do not include the program-related operational costs in this table; identify only those costs related to the project itself. Expected operational costs changes are captured in the TangibleChanges_1 worksheet (see Step 1 for TangibleChanges_1). 

The cells in this table are linked to other worksheet cells and the “Total Project Cost” carries forward to Row II in the third worksheet, InvestmentSummary_3. 

Step 2 - Go to the “Character of Project Costs Estimate” table. As in Step 1.4 above, document the type of estimate and the uncertainty associated with the estimates.

3. Go to the Investment Summary worksheet
Investment Summary worksheet
Investment Summary worksheet

Step 1 - This sheet contains the final calculated “Investment Summary” with the resulting Return on Investment (ROI) analysis as determined from the data entered in the previous two worksheets.  Note that this table is write-protected.  You do not need to fill in any cells in this table.  However, review the results in the “Investment Summary” table.

The ‘Cost of Capital’ values are obtained from the Technology Review Workgroup’s data that in turn is based on forecasts obtained from the State of Florida’s Office of Economic and Demographic Research. The values in row I ‘Net Savings Resulting from the Project’ and row II ‘Total Project Cost’ are populated from the data that was entered in the previous two worksheets.  

The ‘Return on Investment’ (row III) is the difference between the Net Savings and Total Costs. The ‘Payback Period’ (row IV) is the period of time required to recoup the investment costs. The Breakeven Fiscal Year (row V) is the fiscal year when the project’s tangible benefits recoup costs.

The Net Present Value (NPV) and Internal Rate of Return (IRR) (rows VI and VII) are measures of the time value of money. The NPV provides the future net cash flow of the project in today’s dollars and is calculated from balancing the future benefit inflows against the project’s cost outlays.  IRR is defined as the discount rate which makes the net present value of a project equal to zero. 

The IRR calculation considers the positive and negative cash flows from a proposed project and generates an interest rate. This rate represents the value another investment would need to generate in order to be equivalent to the cash flows of the investment being considered. You can think of IRR as the rate of growth a project is expected to generate. Generally speaking, the higher a project's internal rate of return, the more desirable it is to undertake the project. 

Note:  If the project is a cost-only business case or a cost of ownership analysis, it will not have a tangible benefit to payback the project’s costs. In these situations, the Payback Period, NPV, and IRR measures typically are not useful. 

Step 2 - Complete the “Proposed Funding Sources for Project Costs by Fiscal Year” if known.  Identify each of the prospective funding sources and enter the funding levels expected from each source. The totals should reflect the amounts required from each source by fiscal year. This will indicate whether existing funding sources are adequate or available and whether additional funds will be required.

Download also:

Post a Comment

Previous Post Next Post