Your mission is to learn about earned value rations, work with Excel, and mimic my sheet as best you can — including the weird colored lines and the hand-written note. If you make a perfect match with formulas where formulas are called-out, you’ll get 30 points. If you implement the extra credit, I’ll add two (2) points to whatever score you earned.
- Watch the video
- create an Excel spreadsheet using the presenter’s numbers
- create formulas to calculate the CPI, CV, SPI, SV, and totals. Cells: E2:E6, G12:G16, H16, I16, J11:M16.
Hints:
a. I used Draw/Tools/Draw for freehand writing
b. I used Home/Styles/Conditional Formatting to colorize the CPI and SPI cells. I created a red rule for < .97, a green rule for between .97 and 1.03, and a blue rule for < 1.03.
c. I hard coded everything in the sheet except for ACWP, BCWP, CPI, CV, SPI, SV, and the totals at the bottom. This means for you to receive full credit you must use formulas (not literal values) in the following cells: E2:E6, G12:G16, H16, I16, J11:M16. I’ll award two (2) extra points if you implement a conditional formula in J15 and L15 to handle the division by zero problem.
d. I made the blue box outlines and red vertical line by formatting the appropriates sides of the boxes in the Gantt chart.
e. I formatted the percents as Percentages
f. I played with the number of decimal places in certain columns.
g. I could have used a conditional formula to fix the div/zero cells but I got lazy.
Sample Solution
