Use MS Excel to create Figure 4-4 andFigure 4-7

For Figure 4-4, you must create formulas forCash Flow and NPVto receive full credit,

AND

For Figure 4-7, you must do an Excel chart forWeighted Score by Projectto receive full credit.

A B C D E F G 1 Discount rate 10% 2 3 PROJECT 1 YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 TOTAL 4 Benefits SO $2,000 $3,000 $4,000 $5,000 $14,000 5 Costs $5,000 $1,000 $1,000 $1,000 $1,000 $9,000 6 Cash flow ($5,000) $1,000 $2,000 $3,000 $4,000 $5,000 7 NPV- $2,316 8 Formula =npv(b1,b6:f6) Note that totals are 9 equal, but NPVs are 10 PROJECT 2 YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 TOTAL not because of the time value of money 11 Benefits $1,000 $2,000 $4,000 $4,000 $4,000 $15,000 12 Costs $2,000 $2,000 $2,000 $2,000 $2,000 $10,000 13 Cash flow ($1,000) SO $2,000 $2,000 $2,000 $5,000 14 NPV- $3,201 15 Formula =npv(b1,b13:f13) 16 17A B C D E F 1 Criteria Weight Project 1 Project 2 Project 3 Project 4 2 Supports key business objectives 25% 90 90 50 20 3 Has strong internal sponsor 15% 70 90 50 20 4 Has strong customer support 15% 50 90 50 20 5 Uses realistic level of technology 10% 25 90 50 70 6 Can be implemented in one year or less 5% 20 20 50 90 7 Provides positive NPV 20% 50 70 50 50 8 Has low risk in meeting scope, time, and cost goals 10% 20 50 50 90 9 Weighted Project Scores 100% 56 78.5 50 41.5 10 11 12 Weighted Score by Project 13 14 Project 4 15 16 17 Project 3 18 19 Project 2 20 21 Project 1 22 23 0 24 20 40 60 80 100 25 26