Baseline a project populates baseline cost based on $1.00 when the project and ETC starts before the rate matrix actually starts

Document ID : KB000023258
Last Modified Date : 14/02/2018
Show Technical Document Details

Problem:   

If your rate matrix starts after a project's ETC starts, then when creating a baseline for the project you will get baseline cost based on $1.00 for the time period where there is no rate matrix information.

Steps to Reproduce:

  1. Create a new cost/rate matrix with the 'Resource' attribute as a column in the matrix
  2. Add one row with start May 1 and finish December 31
    • The matrix does not have any other rows that go beyond this date range
  3. Set the Resource name to * (asterisk) and set the rate and costs to any value EXCEPT $0 or $1 (such as $5)
  4. Set the default Project financial properties, transaction Labor rate to the rate matrix you created 
  5. Create a new project to start April 11 and finish on June 10
  6. Add a resource with default allocation 100% 
  7. This should create the effort task with the resource assigned with 1 FTE or 360 hours ETC  
  8. Run the Rate Matrix Extraction job with all three parameters selected (options #1,#2,#3)
  9. Baseline the project  
  10. View the project baseline cost 

Expected Result : To see Baseline Cost of (360 * $5) = $1,800
Actual Result : Baseline Cost is $1,320 

Cause:   

This is working as expected.  Based on the configuration described above, the Rate Matrix does not have any rates defined that extend before May 1, 2016.  Therefore any dates that are not covered by the Rate Matrix will automatically get a $1.00 cost rate when a baseline is captured or when a timesheet is posted.

For this specific example, there are 120 hours within the month of April for which the baseline will use a $1 rate and the remaining 240 hours will use the $5 rate.

(120 *$1) + (240 * $5) = $120 + $1200 = $1320

ETC Hours and Cost

Resolution:   

 

Create the rate matrix row to start the same date as the project or earlier.  Having extended dates within the matrix allows the Rate Matrix Extraction to create additional rows of data to cover the range of the project.  
It is important to review and understand the data that is extracted from the Rate Matrix Extraction job into the NBI_PROJ_RES_RATES_AND_COSTS before creating a baseline or posting timesheets.  The Baseline and Post Timesheets actions uses the rate from this table where task_id = -1.
To check this data, use the following query, replacing the '5000000' value with the project internal record id
 
SELECT * FROM NBI_PROJ_RES_RATES_AND_COSTS WHERE TASK_ID = -1 AND PROJECT_ID = 5000000
 
Timesheets with incorrect rates:
If timesheets are posted with the $1 rate or incorrect rateand this is not the desired rate, you can make a timesheet adjustment with the current matrix configuration to set the time entry hours down to zero (0).  This will allow the cost to be backed out successfully with the same amount as it was added.
  • Then after posting this adjustment to back out the incorrect rate, update the Rate Matrix with the correct cost/rate data  
  • Execute the Rate Matrix Extraction job and check the data in the extraction table to ensure you have the desired rates    
  • Now make another timesheet adjustment to set the hours back to the desired amount 
  • After making these changes, you should also run the 'Update Costs Totals' action from the project and update or create a new baseline  

Additional Information:   

Reference TEC474741 : What does the Rate Matrix Extraction job do and how is it used?