Cost Benefit Analysis Spreadsheet For NOVA YEAR 0 YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 TOTAL Must be given: 1. Net economic benefit from tangible benefit worksheet goes in row 7 cell C7. 2. Discount rate: Row 8 Cell A8 as a fraction; i.e., 12% would be entered as .12 3. One time cost, which will be placed in row 13, cell B13; make sure to enter the cost as a negative. 4. Recurring costs place in row 15-cell C15, and make sure to enter the cost as a negative. Net Economic Benefit $0 $0 $0 $0 $0 $0 0 1 1.00000 1 1 1 1 PV of Benefits $0 $0 $0 $0 $0 $0 NPV of all Benefits $0 $0 $0 $0 $0 $0 $0 One-Time Costs $0 Recurring Costs $0 $0 Discount Rate 1 1 PV of Recurring Costs $0 $0 SS $0 $0 $0 $0 1 1 1 1 $0 $0 $0 $0 NPV of all Costs $0 $0 $0 $0 $0 $0 $0 Overall NPV Overall ROI Break-even Analysis Yearly NPV Cash Flow $0 Overall NPV Cash Flow $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Use the first year of positive cash flow to calculate break-even fraction. Actual break-even occurs at Post Step 3 here! BEP #DIV/0! $0 Calculating the Break Even Point 1. Find the last year row 30 is negative; go up the column to the year listed in row 6. Write down that result as the whole year. Then do Steps 2 and 3 below to calculate fraction to add to the whole year value. 0 2. First year row 30 is positive, take row 29, and subract row 30 in same column, and then divide the result by the row 29 value/row 29. 3. ADD Step 1 (full year value) with Step 2 (fractional year value) = Cost-Benefit Analysis Cost-Benefit Analysis Spreadsheet for Alternative C Here are files that you will need for this assignment (cost benefit analysis). Spreadsheet Template : Use this to calculate the NPV, ROI, and BEP. Data Information >>: Use this data to complete the worksheet above before submitting. You will use the data information and complete the CBA spreadsheet template to calculate the NPV, ROI, and BEP. Don't forget to submit your assignment. Contains: Alternative "C" Economic Analysis 1. Tangible Benefits Worksheet 2. One Time Cost Worksheet 3. Recurring Cost Worksheet 4. Discount rates Used to create Cost/Benefit Analysis Spreadsheets for possible alternative solutions. Cost/Benefit Analysis spreadsheet is located in Files. Description of Alternative C-Nova Corporation. 1. Industry Leader in CRM solutions 2. Large and complex 3. Pricing only based on modules purchased 4. License for the use of the system is middle of the row 1. Tangible Benefits Worksheet - Nova Nova - Alternative "C" Tangible Benefits Worksheet A. Cost reduction or avoidance B. Error reduction C. Increased Flexibility D. Increased speed of activity $12,500 $ 3,500 $ 5,500 $ 7,500 E. Improvement management/planning control F. Ease of interfacing with business partners Total Tangible Benefits $15,000 $16,000 Nova Alternative "C" One Time Costs Worksheet A. Development Costs B. New Hardware C. Software License or Purchase cost D. User Training E. Site Preparation Total One Time Costs 3. Recurring Cost Worksheet - Nova $ 2,000 $1,000 $19.500 $ 3,500 $ 4,000 $30,000 Nova - Alternative "C" Recurring Cost Costs Worksheet A. Software Maintenance B. Incremental Data Storage C. Communications D. Supplies E. Other Total Recurring Costs 4. Discount Rate-12% $60,000 Need to let Management Team know what: 1. What the Overall NPV is. 2. What the Return on Investment is. 3. What the Break Even Point is. Also discuss in the Baseline Project Plan Report. $2,500 $ 2.500 $ 9,000 $ 7,000 $ 4,000 $25,000 2. One Time Cost Worksheet - Nova