Hi all
We have released an updated CVR template. This email explains what has changed in the template file, and how it can be applied to new and existing projects.
What has changed
The template has three new pages: Commitments, Transactions and Pivots. Commitments and Transactions are transaction level reports, pulled directly from Focalpoint. All three are refreshed using the existing Refresh Project Data button on the CVR page.
Commitments
Every purchase order line on the project, showing the order value, the amount invoiced to date, and the remaining commitment.
The original SSRS report has been expanded with the following columns to enable improved visibility, accuracy and control.
- Released Commitment: any remaining commitment on the line when the line was closed in Focalpoint.
- Line Over Invoiced Amount: the commitment on an individual purchase order line cannot be negative. If a purchase order line has been over-invoiced, the commitment on the line is set to zero and the over-invoiced amount is recorded here.
The over-invoiced amount is then moved to a new Unallocated Invoices line, generated by the report for that order, where it reduces commitment on the order as a whole, provided the order as a whole is not over-invoiced. Commitment on the whole order can therefore never drop below zero either. - Order Over Invoiced: a flag to show where the order as a whole has been over invoiced.
- Order Line Over Invoiced: a flag to show where the individual order line has been over invoiced.
- Order Line Status: a flag to show whether the order line is Open or Closed in Focalpoint.
- Line Should Be Closed: a flag to show open order lines with zero commitment, already invoiced to their full value, a prompt to close them in Focalpoint.
Over-invoiced orders are highlighted on the worksheet by conditional formatting. An over-invoiced order may indicate that a credit is due. Credit notes are not always matched to the purchase order in Focalpoint, so a credit may not show against the order on the Commitments page, but it will appear on the Transactions page once it has been posted to the project.
Needs Review highlights what has changed since the CVR was last refreshed. An order shows as Needs Review where it has been added, or has had an invoice or credit posted against it, since the previous refresh. It is a visual guide to help reviewers focus on what has moved, and the filter can be cleared to see every line.
Slicers at the top of the worksheet filter the table: by Supplier, by Review Status, and by Order Over Invoiced to go straight to the orders needing attention.
Transactions
Every cost and sales transaction posted to the project. The worksheet is the same as the original SSRS report and should balance exactly to the actuals on the front page.
Needs Review works the same way as on the Commitments page, flagging transactions posted since the CVR was last refreshed, and the filter can be cleared to see every line.
Slicers at the top of the worksheet filter the table in the same way.
Pivots
Two configurable pivot tables, one of Transactions and one of Commitments, enabling you to see actual costs and commitment side by side for the same part of the project. They update automatically with each refresh.
Deployment
New projects. We will take your existing template, add the new worksheets, and publish the updated template to your subdivision document library. The AROC process will then use the new template when creating CVRs for new projects.
Existing projects. Existing projects continue on their current CVR files and nothing changes for them. If you have a long running project you would wish to migrate manually to the new template, follow the following process.
- Download the new template for your division from your subdivision document library.
- Set it up to match the latest copy of the project's CVR in SharePoint exactly: the project number and all your manual entries (forecasts, provisions, manual adjustments and notes).
- Check it agrees with the latest copy before uploading.
- Take a manual backup of the latest copy before overwriting it, in case of error.
- Upload it over the top of the latest copy in SharePoint, replacing the existing file with the same name and location.
- The AROC process will then pick up the new template when it creates next month's CVR, and every month after.
SSRS Reports
The Project Commitments and Project Transactions reports have also been updated. They use the same logic as the CVR worksheets and carry the same extra columns, so the reports and the CVR always agree.