How to create a cost benefit analysis in excel

Cost-Benefit Analysis

This solution evaluation process will be used to determine the potential success of your implemented solution. Success can be measured using both qualitative and quantitative methods, therefore it is important to determine which tools and metrics you will use to evaluate the data you have collected.

In this assignment, you will use cost-benefit analysis to evaluate the financial outcomes of your solution. Create a data chart or graph to most effectively display the cost-benefit evaluation metrics that you will use to measure the success of your solution. Review the study material "Cost Benefit Analysis Solution Evaluation" Excel spreadsheet to assist you. Then, write 250-word explanation of the degree of confidence you have regarding the cost-benefit analysis and the assumptions you are required to make for cost, risk, benefits, and the financial outcome of your proposed solution. Include an explanation of how you will measure whether the solution is successful in addressing the problem and meeting the needs of the business, employees, and customers.

Descriptors
Definitions:
Payback period - the period of time, expressed in months, that a project requires to recover the money invested in it (including the one-time savings which are often excluded by some calculators)
Benefit-cost ratio - an indicator that is often used to decide whether the benefits of a given project or solution outweigh the actual costs (the higher the ratio the better the investment)
Analysis horizon - the number of months the project is expected to be utilized before replacement or major upgrade
Implementation costs include capital costs (equipment, constructions, etc.), training, travel, outside professionals, lost of productivity during implementation & training, implementation costs such as installation, etc.
Ongoing costs include maintenance costs (monthly costs relating to the ongoing maintenance) and operational cost (internal labor, expendables, materials, supplies, etc.)
Hard savings - the direct benefits that affect the bottom line and can directly improve the financial performance of the organization (examples are: sales/price increase, cost reduction and productivity savings)
Soft savings - the indirect benefits which are difficult measure (they are mainly improved yield of a business process, the increased stakeholder satisfaction and the increased safety in the workplace)
One-time savings - examples are value of inventory reduction and sale of unneeded assets
Example
Call Center
$, hours, units E.g.: $/unit $ Year-1/partial year Year-2 Year-3
Cost/Benefit Unit Entry/Quantity Final/Total Description
Project Title C O S T S Implementation costs (one time) 8,000
CRM Rules for CCR Quality 1 Update rules in CRM 8,000 Cost to update rules
Solution Title/Description 2
3
Sponsor 4
CC Manager Ongoing costs (monthly) 0
Project Manager 1
J Doe 2
Timeframe for CBA (in months) 3
12 4
Estimated cost/savings year one (maximum 12) >>>Total Costs (monthly) 667 3,333 11,333 19,333
5 B E N E F I T S Hard and soft savings (monthly) 1,200
Average labor cost (hourly / daily) 1 Saving from reduced calls Calls 5000 1,000
$23.00 2 Saving from reduced QC QC 500 200
Assumptions/Comments 3
1 4
2 Savings (one time) 0
3 1
4 2
5 3
6 4
7 >>>Total benefits (monthly) 1,200 6,000 20,400 34,800
Benefit-cost ratio 1.80 667 Monthly payback Net cash flow Net gains 2,667 9,067 15,467
Monthly payback period 6.7 0 6.7 1,200 ROI 80.0%
Cost Benefit Analysis
$, hours, units E.g.: $/unit $ Year-1/partial year Year-2 Year-3
Cost/Benefit Unit Entry/Quantity Final/Total Description
Project Title C O S T S Implementation costs (one time)
1
Solution Title/Description 2
3
Sponsor 4
CC Manager Ongoing costs (monthly) 0
Project Manager 1
J Doe 2
Timeframe for CBA (in months) 3
4
Estimated cost/savings year one (maximum 12) >>>Total Costs (monthly)
5 B E N E F I T S Hard and soft savings (monthly) 0
Average labor cost (hourly / daily) 1 5000
2 500
Assumptions/Comments 3
1 4
2 Savings (one time) 0
3 1
4 2
5 3
6 4
7 >>>Total benefits (monthly)
Benefit-cost ratio Monthly payback Net cash flow Net gains
Monthly payback period 0 ROI