¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MINOR |
| Unique ID | 1030046 |
| Summary | Is there more than a 50% delta between future BCWS & ETC dollars for this chunk of work? (Or, if BCWS is missing, is there at least $1,000 of ETC without BCWSi?) |
| Error message |
¶ What causes this error?
The following text was generated by an AI tool and hasn't been reviewed for accuracy by a human! It might be useful, but it also might have errors. Are you a human? You can help by reviewing it for accuracy! Edit it as needed then remove this message.
The Data Integrity and Quality (DIQ) check titled "Incommensurate Future Budget & Estimates" is designed to identify potential discrepancies in the DS03 Cost table. This check is focused on the relationship between future Budgeted Cost of Work Scheduled (BCWS) and Estimate to Complete (ETC) values.
The test is triggered when there is more than a 50% difference between future BCWS and ETC values for a specific work chunk. In other words, if the absolute value of the difference between BCWS and ETC, divided by BCWS, is greater than 0.5, the test will flag this as a potential issue. This could indicate a significant discrepancy between the planned budget and the estimated cost to complete the work, which could lead to budget overruns or other financial issues.
Additionally, if BCWS is missing (i.e., BCWS equals zero), the test will also be triggered if there is at least $1,000 of ETC without corresponding BCWS. This could indicate a potential oversight in budget planning, as there are estimated costs for work that has not been budgeted.
The test is grouped by the period date, and only applies to records where the period date is later than the CPP status date. This ensures that the test is only applied to future work, and not to work that has already been completed or is currently in progress.
If this test is triggered, it may be necessary to review the budget planning and estimation processes for the affected work chunks. It may be that the BCWS and ETC values need to be adjusted to more accurately reflect the expected costs and budget for the work.
¶ Why do we check this?
This test is being performed to ensure that the future budgeted cost of work scheduled (BCWS) and the estimate to complete (ETC) the work are not significantly different. The test checks if there is more than a 50% difference between the future BCWS and ETC dollars for a particular chunk of work. If BCWS is missing, the test checks if there is at least $1,000 of ETC without BCWS.
The importance of this check is to ensure that the budgeting and estimation for future work are in line with each other. A significant difference between the two could indicate potential issues with budgeting or estimation processes. This could lead to financial mismanagement or project delays.
The severity of this check is marked as an MINOR. This means that while it may not immediately prevent data from being reviewed, it could potentially cause minor problems or indicate that the data does not follow all best practices. It is a signal to review the budgeting and estimation processes to ensure they are accurate and consistent.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_AreBCWSiAndETCiIncommensurate] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Rollup as (
SELECT WBS_ID_CA, WBS_ID_WP, period_date, EOC, is_indirect
FROM CostRollupByEOC_Get(@upload_ID)
WHERE (ISNULL(bcwsi_dollars,0) = 0 AND ABS(ISNULL(BCWSi_dollars,0) - ISNULL(ETCi_dollars,0)) > 1000)
OR ABS((ISNULL(BCWSi_dollars,0) - ISNULL(ETCi_dollars,0)) / NULLIF(BCWSi_dollars,0)) > .5
)
SELECT
C.*
FROM
DS03_Cost C INNER JOIN Rollup R ON C.PERIOD_DATE = R.PERIOD_DATE
AND C.WBS_ID_CA = R.WBS_ID_CA
AND ISNULL(C.WBS_ID_WP,'') = R.WBS_ID_WP
AND C.EOC = R.EOC
AND C.is_indirect = R.is_indirect
WHERE upload_ID = @upload_ID
AND C.period_date > C.CPP_status_date
AND R.period_date > C.CPP_status_date
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Minor organizational changes. |
| 2024-07-10 | Updated to include is_indirect |