Microsoft Project Tips Weekly

- [Bonnie] Hi, Bonnie Biafore here. Contingency funds represent money that management hands out to respond to issues that arise. In this tip, I'll show you how to create formulas to track contingency fund status. So here we have our project and we've already created the custom view to track these funds. And I have the four custom fields that I created in the previous tip already in the table. Now we're going to add calculations to the fields. We don't have to do anything with total contingency because that's just a value that's associated to the entire project and we've entered that value. So we're going to start with allocated contingency. I'm going to right-click that heading. And then on the shortcut menu, choose Custom Fields. Now, management may allocate the funds, not only to the project, but to the tasks within the project. What we want to do is we want to make sure that we are tracking how much has been allocated from that total. So once we select the field, I'm going to go down to where it says Calculation for task and group summary rows. I'm going to select Rollup. And the function that I want is sum. That Way, the allocated contingency is summed up all the way to the project summary task. So management will know when everything is handed out. Now we're going to go to Used Contingency and we're going to do exactly the same thing because we want to see that rolled up all the way to the top of the project. So I'm going to select Rollup in the calculation section and choose Sum. So the last thing we have to do is the remaining total contingency, and this does have a formula but it's really simple. I select that field and then click Formula. The first thing I insert is total contingency. I click the Field button, point at Cost, Custom Cost, and then choose Total Contingency. Then I'm going to add the minus sign and I'm going to subtract the used contingency. So I'm going to click Field, point at Cost again, go to Custom Cost, and this time choose used contingency. And that's the formula. So I'm going to click OK and then click OK to dismiss this message about the existing data. Once again, we want to roll this up. So in the calculation section, I'm going to select Rollup. But as opposed to using the sum, I'm going to use this Use formula button. So I select that, and then I'm going to click OK. You can see that the allocated contingency rolls up and the remaining total rolls up, but let's go ahead and go back to our task 13, which has $2,000 allocated. If you look over at the variance column, $1,300 was spent over what was budgeted. And let's say that that money was used to correct whatever the issue was. So let's add that into the used contingency cell for that task. When I do that, because I rolled up used contingency, you'll see that the summary tasks have that value. And when I go back up to the top, it rolls up all the way to the project summary task. And that way, you can see that the remaining total contingency has what's been used deducted from what management gave the project. And that's how you track the consumption of contingency funds with project custom fields.