Analyze Minor Variance in Microsoft Project

Background Information

Before you can analyze variance in a Microsoft Project schedule, you must save a baseline in the project. When you save a baseline, the software captures the current values for all tasks, resources, and assignments into several major fields, and a smaller number of minor fields as well. For task data, the major fields include Baseline Start, Baseline Finish, Baseline Duration, Baseline Work, and Baseline Cost. Each of these major fields includes an accompanying variance field which you can use to analyze variance in the project. These major variance fields include Start Variance, Finish Variance, Duration Variance, Work Variance, and Cost Variance.

The minor fields captured in the baseline for tasks include Baseline Budget Cost, Baseline Budget Work, Baseline Deliverable Start, and Baseline Deliverable Finish (used only with Project Online and Project Server), Baseline Fixed Cost, and Baseline Fixed Cost Accrual. For each of these minor baseline fields, Microsoft Project does not include an accompanying variance field. If you want to analyze variance for these minor baseline fields, you must create your own variance field for each of them. In this article, I will address how to analyze variance on only the Fixed Cost and Fixed Cost Accrual fields, but you should be able to use this information to create custom fields to analyze the other types of minor variance.

Analyzing Fixed Cost and Fixed Cost Accrual Variance

Project Managers can use the Fixed Cost field to track additional fixed costs associated with a milestone task, a detailed task, a summary task, or even the entire project (the Project Summary Task). Project Managers can use the Fixed Cost Accrual field to determine whether the fixed cost amount is applied at the start of the task, the end of the task, or evenly applied across the duration of the task.

Suppose that a project manager enters a Fixed Cost estimate of $1,000 for a task during the planning process and also sets the Fixed Cost Accrual value to Prorated. After the project has been baselined and is in the Execution stage, the project manager changes the Fixed Cost amount to $2,500 and changes the Fixed Cost Accrual value to End. Because there are no default fields named Fixed Cost Variance and Fixed Cost Accrual Variance, it is not easy to determine whether the Fixed Cost value and the Fixed Cost Accrual value changed during the life of the project.

To address these limitations, you can create your own custom fields so that you can analyze these two types of variance. Complete the following steps to create the Fixed Cost Variance custom field:

1. Open a Microsoft Project schedule in which you have entered Fixed Cost information.

2. Apply any task view, such as the Gantt Chart

3. Click the View tab to display the View

4. In the Data section of the View ribbon, click the Tables pick list button and select the Cost table in the menu.

5. Click the Project tab to display the Project

6. In the Properties section of the Project ribbon, click the Custom Fields

7. In the Custom Fields dialog, click the Type pick list and select the Cost type of field, shown in Figure 1.

Figure 1 – Select the Cost type of field

8. In the list of available Cost custom fields, select the first unused field and then click the Rename button.

9. In the Rename Field dialog, enter Fixed Cost Variance as the new name of the field, and then click the OK button, as shown in Figure 2.

Figure 2 – Rename Field dialog

10. In the Custom attributes section of the Custom Fields dialog, click the Formula button.

11. In the Formula dialog, manually type [Fixed Cost] – [Baseline Fixed Cost] as the formula for the field as shown in Figure 3, and then click the OK button.

Figure 3 – Formula for Fixed Cost Variance field

12. In the Custom Fields dialog, select the Use formula option in the Calculation for task and group summary rows section of the dialog, and then click the OK button.

13. In the Cost table, right-click on the Fixed Cost column header, select the Insert Column item on the shortcut menu, and then select the Baseline Fixed Cost column in the list of available columns.

14. In the Cost table, right-click on the Fixed Cost Accrual column header, select the Insert Column item on the shortcut menu, and then select the Fixed Cost Variance column in the list of available columns.

After creating the Fixed Cost Variance field, complete the following additional steps to create the Fixed Cost Accrual Variance custom field:

1. In the Properties section of the Project ribbon, click the Custom Fields

2. In the Custom Fields dialog, click the Type pick list and select the Text type of field, shown in Figure 4.

Figure 4 – Select the Text type of field

3. In the list of available Text custom fields, select the first unused field and then click the Rename button.

4. In the Rename Field dialog, enter Fixed Cost Accrual Variance as the new name of the field, and then click the OK button, as shown in Figure 5.

Figure 5 – Rename Field dialog

5. In the Custom attributes section of the Custom Fields dialog, click the Formula button.

6. In the Formula dialog, manually type IIf( [Fixed Cost Accrual] = [Baseline Fixed Cost Accrual], “”, “Different”) as the formula for the field, as shown in Figure 6, and then click the OK button.

Figure 6 – Formula for Fixed Cost Variance field

7. In the Custom Fields dialog, select the Use formula option in the Calculation for task and group summary rows section of the dialog, and then click the OK button.

8. In the Cost table, right-click on the Fixed Cost Accrual column header, select the Insert Column item on the shortcut menu, and then select the Baseline Fixed Cost Accrual column in the list of available columns.

9. In the Cost table, right-click on the Total Cost column header, select the Insert Column item on the shortcut menu, and then select the Fixed Cost Accrual Variance column in the list of available columns.

After creating the two custom columns and inserting them into the Cost table, you are ready to analyze fixed cost variance. To do this, click the AutoFilter pick list button in the Fixed Cost Variance column header. In the AutoFilter menu, deselect the $0.00 checkbox and then click the OK button, as shown in Figure 7.

Figure 7 – AutoFilter the Fixed Cost Variance column

Figure 8 shows a sample project in which there are a number of tasks where the Fixed Cost Variance column contains a value greater than $0.00, which means that the value in this column changed at some time during the life of the project. You can also see that the value in the Fixed Cost Accrual column changed on those tasks as well. Study the variance shown in the two new custom fields. When you finish the variance analysis process, press the F3 key on your computer keyboard to remove the AutoFilter and display the list of all tasks again.

Figure 8 – Fixed Cost variance analysis