LTCF Training
LTCF Update Guide
Setup
Make sure that you have set up Excel to be able to run the model. Instructions are in the below document.
To be able to update the data (refresh to GMI Data Warehouse “GDW”) or submit data to the GDW you need to either be on the GMI network (in the office) or have the VPN connected. The VPN is FortiClient, check by searching from your desktop (type “VPN” into the search bar in the bottom left of your desktop).
Please note that in the NW the Wi-Fi is set up differently so you will have to have the VPN connected when refreshing or submitting, even if in the office.
If you do not have the VPN installed, please raise an IT support ticket to request this.
You should have been given access to the GDW, but if you are having issues please raise a IT support ticket to check or request access.
Running the Model
There is a sheet called “INFO” that provides high-level instructions on running the model.
The model update involves 6 steps
- Rollover
- Get data (refresh from the GDW)
- Manual updates
- Update the file data sheets (press of a button)
- Internal review (Commercial)
- Submit to GDW & email model to Finance
There are buttons that automate steps 1,2,4 & 6 these are on the INFO sheet, and buttons for 2 & 4 are also given again on the LTCF and CostAnalysis sheets.
Cells shaded pale yellow are where you are expected to input information, if the cell is not shaded pale yellow you generally should not overtype it, but there can be occasions when you do, if you do, please shade the cell an obvious colour so its clear it has been overtyped.
Roll Over
The model is updated each month rather than being a new blank file each month. This means that what you input last month is kept and just needs to be updated rather than starting again from scratch.
On the info sheet press the button marked “roll over”
This will bring up a pop-up message which should have automatically picked the next month, but this is a pick list if you need a different month.
Click Apply, and the model will update ready for the next month.
It is recommended that you save a copy of the model for each month that you do an update so that you keep the history of what you submitted. Finance will keep a copy too.
Finance may roll over your file for you and send it out pre-done, this is where updates to the model are needed, please always use the version sent by finance if this is the case otherwise you maybe working on an old version.
You will be issued a new template each new Financial year (October).
Get Data from the GDW
Before you run this update make sure that your model has the correct project number on the INFO sheet cell C4. This tells the GDW which project data to return.
The run this click the Get Data button on the info sheet. The button is also available on the LTCF and CostAnalysis sheet
Wait for the message box to pop up to say it is completed. If you need the VPN connected but it is not, you will get an error message to say this. If you do, connect the VPN and try again.
You can use the Get Data button at anytime it doesn’t matter if it pulls through ‘future’ months data this is just available in the pivot table views, it doesn’t pull future data onto the input sheets (LTCF & CostAnalysis).
If you get the below pop up, check the box that is ‘ignore …’ and select save
If you have a split project (2 numbers) please check with Finance that you have a version that will manage this as in a single file (as a total of both).
Manual Updates
The LTCF template will eventually be an integrated part of the new CVR model rather than a separate model. If you are using the New CVR you will still need to do the new LTCF for the time being, you will be advised when this changes.
Start on the INFO sheet and update the key data about your project, this is the dates and %’s for retention. It is important that these are correct.
The Contract number should be the Main number you use. The file uses this to select data from the GDW.
The table with start date etc, has 2 columns for dates, the first column is the contract date the second is the actual date (when the PC cert and MGD cert is expected).
Finance should have provided the correct VAT information.
Client retention needs to be the rate you are on “now”.
The Cert date is the day number you usually have the external value certified. Eg if this is the 28th of the month put in 28. This should be the expected date “this month”.
Pay terms is the number of days the client pays from the cert date in days e.g 14 for 14 days. Please make sure this has no text in the number field.
You can add notes in the section provided.
LTCF sheet update
This should reflect the numbers in your CVR rather than “Cash”. Finance will calculate the cash profile from the CVR information provided. This has been done to make the template easier to understand and match the CVR more closely.
Cash in is completed on the LTCF tab. This is the top section of the LTCF sheet as below. This uses grouped rows and columns which can be shown or hidden with the + or – above the column or next to the row.
Columns E, G and H should come from your CVR end position and interim position. Update these first. This is External value and before any adjustments e.g. insurance claims or client claims. Adjustments go in the adjustments section, if you need more rows than provided please ask finance to add these rather than insert yourself.
Rows 10-14 are information for you to see what has been posted on the ERP to date.
Input the monthly amount of external value you expect to certify each month in the month it relates to eg a Certified External Value related to Feb is put in the Feb column (not when paid). The model works out when this is likely to be paid (rows 19-23).
This may not display perfectly e.g. if you certify on 5th and have 14 days (paid by 19th) the model will show this paid ‘next month’ even though it is paid in the same month. This is fine as when the data is submitted, this will be corrected in the data warehouse which will recalculate based on date and payment terms.
Future versions will improve this display.
You should enter the external value GROSS of retention; the retention is calculated on row 21 (deductions) and it calculates the expected return of retention on row 22 based on the actual dates you put on the INFO sheet as PC and MGD. It does assume a delay in addition.
If your project has a retention that has varied month to month you will need to overtype the % on row 18. This is fine even though not shaded yellow. You may also need to overtype the retention taken if you have an unusual profile (do this on row 21 to show the correct retention month on month)
For adjustments put the amounts you expect to be paid in the month it is expected to be PAID (not certified).
Column I is a check to make sure that the phased amounts agree to the total (column E). You file will not allow you to update if you have not cleared any checks to 0.
The overall cash in in column E should equal your final CVR external value (including any value adjustments).
Cash-out is completed via the LTCF and CostAnalysis sheet.
First update the LTCF sheet Columns E, G and H in the same way that you did for Value. You will need to split the interim position to give you the total of cost + accrual for each section, as you will be balancing to this total on the Cost Analysis sheet.
For Subcontractors, Materials, Provisional Sums, Fees and Prelims you do the phasing on the CostAnalysis Sheet
For Fixed price Inflation & Contingency, please phase this monthly in the months you expect it to be used (liability not cash basis). This can only be future months.
Defects reserve automatically spreads the total evenly over 13 months between PC and MGD dates. You can overwrite this if you wish
All rows need to balance to zero (column I check column). Some prelims will have prepopulated, you resolve this check on the CostAnalysis sheet.
Cost Analysis Sheet update
This sheet has been created to help you phase your major cost lines. It works by having a section for each area (Subcontractors, Materials, Provisional Sums, Fees, Prelims), which balances back to the LTCF sheet (the amounts you put in columns E & G).
For example if you put the below on the LTCF sheet
Then the Subcontracts will show this. If no individual Subbies are added then the whole amount will be a balance figure (as below)
You should add the major subcontract packages (those with the highest spend) in the relevant section, so that you can phase those packages specifically. You can separate as many or as few as you like, and then you phase the remaining balancing figure.
Ideally you should amend the CVR ref column (column B) to show the package ref you use in your CVR. The only constraint on this field is it must be unique.
You will need to manually add the end position and interim position for that package from your CVR.
To get costs to automatically pull through you need to add either the Subcontractor code or the Costhead. Costhead teams to work better for Fees and prelims but either can be used. If both the Supplier code and the Costhead column are completed the model picks Supplier code in preference.
If you go to the sheet called “pCLE2” this will list all the amounts booked to your project. You can copy and paste the Supplier code, name, Costhead code and name from this sheet to the Cost Analysis sheet.
The model requires the correct code as listed in the ERP it does not work on names.
The pCLE sheet can also be used to pick out lines that have big numbers that you may want to separately phase on the Cost Analysis sheet.
When you put in a Supplier code the model will so get the Supplier payment terms and retention % as listed in the ERP. The VAT code must be updated manually, the assumed default is in your template.
The main one that will need to be amended is insurance which is always zero.
If you are unsure about VAT treatments, please ask finance.
If you believe the pay terms are incorrect please do not amend highlight and advise finance. This field has to be in a specific format.
Retention can be overwritten if needed, this should be entered as a simple number i.e. 5 for 5% (do not put in a % as this will cause it to calculate incorrectly).
Once you have added a supplier or costhead and input the end and interim position you will need to phase the remaining costs by month. The actuals to date will have loaded if you have added the supplier code or costhead code correctly.
For subbies on Webcontractor there is additional information, this is in columns P&Q, you may also want to view the hidden column R.
P&Q show the webcontractor data for the last 2 certifications, this should be a strong indication of what you are likely to pay in the next 2 months. Take care to check that the amount in month +1 isn’t already paid.
Webcontractor data is shown on sheet pWebC
You should phase the outstanding amount (difference between ERP to date and end position) monthly. Due to the way that Subcontractors are currently loaded to the ERP this should reflect the date payment is expected rather than certification.
The amounts should be Gross of retention.
There is a review in progress about how data from Webcontractor is loaded to the ERP however this will not be addressed until the migration to a new system (Webcontractor is to be migrated to PayApps next year).
For rows where the supplier is not on WebContractor you should phase based on liability ie when you expect to certify. The actuals costs to date should give you a reasonable guide to profile.
You must phase the remaining “balancing rows” at the end of each section and make sure that column S is 0 for every row.
The more suppliers / costheads you separate out the better your cashflow profile is likely to be.
The model calculates the expected Supplier retention (end position * retention %). The monthly amounts is calculated from your phased amounts in the background of the model (when the Update is run). Amounts you enter on the Cost Analysis sheet should always the gross of retention, and you need to check the retention % column is correct.
As a final check you must make sure that all the costs on the ERP have been profile to date. Row 10 tells you if you have a difference.
These amounts go on the balancing lines, and then you may need to change your future forecast on the balancing line if you are putting some of it in the actuals to date.
When you are finished go back to the LTCF sheet and check you have no errors (Check column).
The cash-out will not update until you press the Update button.
Update the file
You can press the “Update” button at any time but you must press it when you are finished on the cost analysis sheet you need to press the Update button (Info sheet, LTCF sheet or Cost Analysis sheet). This calcaulates the cash out and updates the Graph on sheet Graph1.
Internal review
Review the cashflow within the commercial team to be confident that it is a fair representation of your expected monthly profile.
Accuracy KPIs have now been launched, these have the below target for Cash-in (external value).
Month 1:
within 2%
(within £20k per £1m)
Month 2:
within 5%
(within £50k per £1m)
Month 3:
within 8%
(within £80k per £1m)
Month 4:
within 11%
(within £110k per £1m)
These are tough, but on Value we should have a reasonably accurate view for Month1 as the LTCF model is being completed mid-month of Month 1. The LTCF models that have been submitted this financial year often have significant variances just for month1 and we need to improve this
This means each month you submit a LTCF we will look back to it for the following 4 months to see how close it was. An example of how this works is below
A KPI on cash-out is being considered. We will track the % and £ difference by project (cash out in total not by section) and report this. This will inform how we improve the LTCF model and identify where training may be required.
Submit to GDW & email model to Finance
Once agreed with the Commercial Director, please press the Submit button to send the data to the GDW. This will also create and email automatically attaching your file.
Summary
The new template is not perfect and will need to be improved. Please provide feedback to finance on any issue and your thoughts on how it could be made easier to use.