Question:
What is the Source for the Values in the KPI (Key Performance Indicators) on a Project Dashboard?
Answer:
The values are a combination of amounts gathered from the Microsoft Dynamics SL (a.k.a. Solomon) tables, the Spitfire tables and on-the-fly calculations, as described in the following table. Note that calculations using previously gathered or calculated numbers are shown in italics.
Contracts & Budgets
| Field | Explanation |
| Original Contract | = Current Contract – Committed PCO |
| Approved CO | = sum(xsfDocItem.RevenueAmount) where xsfDocHeader.status = approved or committed; xsfDocHeader.project = project; and DocType = PCO |
| Current Contract | = pjpdtrol.eac_amount where project = project and acct = Contract Value account category |
| Unapproved CO | = sum(xsfDocItem.RevenueAmount) where revenue and expenses have not yet been approved/posted. |
| Original Commitment | = sum(xsfDocItemTask.ExpenseAmount) where xsfDocHeader.status = committed for all of the Project‘s Commitments |
| Approved CCO | = sum(xsfDocItemTask.ExpenseAmount) where xsfDocHeader.status = approved for all of the Project‘s Commitment Change Orders |
| Current Commitment | = sum of Original Commitment + Approved CCO |
| Pending Commitment
Approved Pay Request |
= sum (xsfDocItemTask.ExpenseAmount) where xsfDocHeader.status = pending for all the Project‘s Commitment Change Orders and Commitments= sum(xsfDocItemTask.RevenueAmount) where xsfDocHeader.status = approved for all of the Project‘s subcontract pay requests |
| Original Budget
Estimate at Completion |
= sum(pjptdsum.Total_Budget_Amount) for Expense Account Categories
= sum(pjptdrol.eac_amount) where acct = Expense Account Category Types ** |
| Actual + Committed | = sum(pjdtdsum.actual_amount + pjdtdsum.commit) for Expense account categories |
| Forecast at Completion | = sum(pjptsum.fac_amounts) for Expense account categories |
| Variance | = FAC – EAC |
| Earned Value | = %spent * CurrentContract : details:
IF CurrentEacBudget <= 0 THEN 0 IF ActualExpense > CurrentEACBudget THEN CurrentContract OTHERWISE (CurrentContract * (ActualExpense / CurrentEacBudget)) |
| Under Billed | = Billed to date – Earned Income |
| Backlog aka Future Profit |
FAC Margin = Current Contract – Current FAC Expense EAC Margin = Current Contract – Current EAC Expense Actual Percent Spent = Raw Actual / Current EAC Expense Backlog = FAC Margin – ( EAC Margin * Actual Percent Spent)
|
AR Summary
| Field | Explanation |
| Aged AR | For selected Project and as of Today‘s date: |
| WHERE ardoc.projectid = @pProject AND ardoc.pc_status IN ( ‘1‘, ‘2‘, ‘9‘) and ardoc.rlsed = 1 and ardoc.opendoc = 1 and ardoc.docbal <> 0 | |
| Under 30 | and grouped by date range |
| 30-60 Days | |
| 60-90 Days | |
| Over 90 | |
| Total | = sum of Aged AR |
| Unbilled | For selected Project and as of Today‘s date: |
| JOIN solomonapp.dbo.pjinvhdr pjinvhdr ON pjinvdet.draft_num = pjinvhdr.draft_num WHERE (pjinvdet.project = @pProject or pjinvdet.project_billwith = @pProject) and pjinvdet.bill_status IN (‘U‘, ‘S‘) | |
| Under 30 | and grouped by date range |
| 30-60 Days | |
| 60-90 Days | |
| Over 90 | |
| Total | = sum of Unbilled |
Project Cash Flow
| Field | Explanation |
| For selected Project and as of Today‘s date: | |
| Billed to Date | = sum pjtran.amount when PJTRAN.acct = Billed-to-Date AcctCat from Project Controller Setup |
| Less Open AR | = Aged AR total from Column 2 |
| Less Retention | = sum pjtran.amount when PJTRAN.acct = AR Retention AcctCat from Project Controller Setup |
| Cash In | = Billed to Date + Less Open AR + Less Retention |
| For selected Project and as of Today‘s date: | |
| AP Cost to Date | Sum of AP Voucher, Credit and Debit in accounting that have been released: =sum OrigDocAmt WHERE apdoc.rlsed = 1 and apdoc.doctype in (‘VO‘,‘AC‘,‘AD‘) [multiply AD amount by -1] |
| Less Open AP | Sum of the remaining balance on the AP Voucher, Credit and Debit in accounting that have been released and partially relieved: =sum DocBal WHERE apdoc.OpenDoc = ‘1‘ and apdoc.rlsed = 1 and and acct <> APRetention Acct and apdoc.doctype in (‘VO‘,‘AC‘,‘AD‘) [multiply AD amount by -1] |
| Less Retention | =sum DocBal WHERE apdoc.OpenDoc = ‘1‘ and apdoc.rlsed = 1 and acct = APRetention Acct and apdoc.doctype in (‘VO‘,‘AC‘,‘AD‘) [multiply AD amount by -1] |
| AP Cash Out | = AP Cost to Date + Less Open AP + Less Retention |
| Other Cash Out | = Actual + Commit to Date from Column 1 – AP Cash Out |
| Net Cash Flow | = Cash In – (AP Cash Out + Other Cash Out) |
| Cash Conversion | = Open AR + Retention + Under Billed (if any) |
| Cash Funding | = Open AP + AP Retention + Over Billed (if any) |
| Net Cash Demand | = Cash Conversion – Cash Funding |
Additional Comments:
See KBA-01228 for more information on KPI formulas. The information in this KBA is dated and static. Actual formulas are rule driven
KBA-01152; Last updated: November 9, 2020 at 22:11 pm;
Keywords: KPI