Calculate the total budget at completion for the project

Question

Problem

A project manager surveyed his earned value table at the conclusion of month 24.

The results are shown in the table below. ($ values shown).

The planned payment schedule is shown with % Complete which can be used to calculate earned value for each activity.
Pay attention to the Actual Costs (payments).

Copy the table below into Excel and show your work by expanding or adding the necessary rows and columns and fill in as needed.

Add a small table at the end for these items:

A. Calculate the Total Budget at Completion (BAC) for the project
B. Calculate the Schedule Variance and the SPI for this project at the end of the 24th month
C. Calculate the Cost Variance and the CPI for the project at the end of the 24th month
D. Calculate the Estimate at Completion
E. Is the project on budget / over budget / under budget? What is showing you this?
F. What might this mean in terms of upcoming payments for this project?
G. Is the project on schedule / ahead of schedule / behind schedule? What is showing you this?

Activity	4	8	12	16	20	24	28	TOTAL	% Complete
Concept Design	$500	$1,500	$2,000	$3,600	$3,000	$1,500		$12,100	85%
Proposal		$450	$4,500	$100	$100	$4,500		$9,650	90%
Negotiations		$500		$1,200				$1,700	100%
Resource Acquisition			$500					$500	100%
Training			$300	$750	$950			$2,000	100%
Modeling				$400			$1,000	$1,400	75%
Planning				$2,000	$500	$3,000	$2,000	$7,500	60%
Testing					$1,000		$4,000	$5,000	10%
Control						$1,200	$2,000	$3,200	30%
Quality Assurance		$100	$500	$500	$500	$500	$3,500	$5,600	50%
Scope Verification			$1,000		$1,000		$6,000	$8,000	30%
Contract Closure					$2,000		$4,000	$6,000	40%
Total
Cumulative Total
Actual Cost	$500	$3,500	$11,000	$5,500	$8,000	$8,000
Cumulative Actual Cost