CONSULTANT TRAINING FOR PROJECT COST REDISTRIBUTION SPREADSHEET

1. CONSULTANT TRAINING FOR PROJECT COST REDISTRIBUTION SPREADSHEET

2. INTRODUCTION The project cost initiative involves the improvement of project cost reporting by capturing detailed project information for the purpose of providing better data for decisions, managing resources and forecasting at the project and program levels.

3. Download Project Cost Redistribution Excel spread sheet from FDOT’s Procurement Internet site at project costing initiative webpage http://www.dot.state.fl.us/procurement/project%20costing% 20initiative.shtm

4. For MS 2003 users, this message will be displayed when you open the file. Click on Show help

5. Review displayed information about how to change the priority from High to low in order to enable the Macros associated with this spread sheet.

6. Click on Enable Macros.

7. For Microsoft 2007 Users, Security Warning is displayed with ‘Options’ Click on ‘Options’.

8. Security Alert – Macros & Active X window is displayed. Click on ‘Enable This Content’ and then on ‘OK’

9. Excel spread sheet is displayed and you are now ready to enter data. First click on Create File button to view all error messages.

10. Click on OK.

11. All required fields are in error. Look carefully on the right hand top corner in each cell, a red mark is visible.

12. By moving the cursor near the cell, you will see an error message is displayed.

13. Enter valid format for ‘Contact Email’.

14. Enter valid ‘Contract number’. Contract number should be five characters long. Special characters are not allowed. Error message is displayed if invalid contract # is entered. (Valid Format = CXXXX)

15. Enter valid ‘DOT Invoice’. Invoice numbers are between 0001 to 9999.

16. From Project and To Project ID • Always 11 characters long, do not worry about entering spaces or dashes in this spreadsheet • Characters 1-6 is Item work program, 7 is Item segment, 8-9 is Phase and 10-11 is Sequence number • Example : 123456 7 89 12 Phase Item Work Program Sequence Number Item Segment

17. Enter ‘From Project Id’. Do not enter spaces or dashes, spreadsheet will format automatically with dashes when you move to another field! Number should be a valid FM # which is included in the contract in CITS. To view all Valid ‘From Project IDs’ Log into CITS, go to your contract, and select projects.

18. ‘From Project id’ is formatted automatically. Enter valid ‘To Project Id’ (This number must be given to you by your FDOT Project Manager.) Do not enter spaces/dashes, spreadsheet automatically formats with dashes after you move to the next field!

19. ‘To Project id’ is formatted automatically. Enter valid ‘Amount’.

20. ‘Total Amount’ is generated automatically.

21. Enter valid ‘Project Description’. This is an optional field and is not a part of data which gets transmitted to FDOT server within Project Cost Redistribution file.

22. To add new lines, type in a number and select “Insert”. You may enter any valid number from 1 to 1000

23. If you type “1” in the Enter # of Rows to enter field and select “Insert”, you will notice that one blank line is added. Now enter From and To Project Id, amount and description in newly added line.

24. From and To Project’s are formatted automatically. Total amount is automatically generated correctly. Project description is entered and converted into uppercase letters.

25. In order to create a file, you should not have any blank rows in your file. If there is a blank row/rows remaining, select ‘Delete Blank Rows’

26. In order to clear worksheet data select “Clear Worksheet”. Use extreme caution when using this button, you will loose ALL entered data!!

27. Once required information is entered. select “Create File”.

28. Default Drive is C: At this point if you want to change the drive ( D:, E: ) you can. A new window is displayed. File name (Contract number and Invoice number) is automatically populated. Do not change the file name. Select “Save”.

29. File is saved, Go to C: drive and locate the file. Now open the file and make sure data is available in this file.

30. Close the file and you are now ready to send an email to PCRload@dot.state.fl.usbe sure to attach this file. NOTE: If your signature block contains a logo that is an image file, please be sure to delete this as the system will assume you have submitted two attachments, thus making an invalid transfer.

31. Open your E-mail and begin a new message. • Requirements for E-mail message: • Subject must be FDOT.PCR.FILE CXXXXNNNN. • Mail should be sent to PCRLOAD@dot.state.fl.us

32. To email id‘PCRLOAD@dot.state.fl.us Subject Line FDOT PCR FILE C8443584 Attachment C8443584.txt (7 kb) ‘To’ must be ‘PCRLOAD@dot.state.fl.us’ ‘Subject line must be ‘FDOT.PCR.FILE CCCCCNNNN’ Where CCCCC=Contract Number (C8443 First five characters of file name) NNNN=DOT Invoice Number (584 Last three or four characters of the filename)

33. To email id‘PCRLOAD@dot.state.fl.us Subject Line FDOT PCR FILE C8443584 Attachment C8443584.txt (7 kb) Send email. If there are any errors, you will receive a response back from ‘PCRLOAD@dot.state.fl.us’with an error description.

34. At this point you can save the spread sheet with data for future reference. Click on ‘Yes’ file PCR_Redist_mmddyy.xls saved. (ex: mmddyy=030508)

35. Additional Info: • If you would like to review the status of your invoices, please log in to CITS, click on the contract of interest, click on ‘Invoices’, then click ‘View all Invoices’, the last column you should see in the table is titled ‘Distribution Status’. After you submit a Distribution file, you can periodically check this column to see the status of your submittal. • QUESTION : WHAT DOES THIS STATUS MEAN? REC:/PEN The system is waiting for the invoice to be paid. FAL: The distribution is incorrect or invalid (PCR must be corrected and re-submitted) DIS: Redistribution has been completed (Note is sent to Vendor) EXM: This specific invoice has been exempt from the requirement of sending a PCR file. All task work orders are already issued to the specific project. Redistribution file is not necessary.