DEPARTMENT OF TRANSPORTATION Agenda Site Logistics Invoice Spreadsheet Definitions Overall Improvements Step by step instructions Compiling monthly PDF Invoice Flow of information between ID: 1028274
Download Presentation The PPT/PDF document "Invoice Training October/November 2015" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
1. Invoice TrainingOctober/November 2015DEPARTMENT OF TRANSPORTATION
2. AgendaSite LogisticsInvoice SpreadsheetDefinitionsOverall ImprovementsStep by step instructionsCompiling monthly PDF InvoiceFlow of information between firmsModifications (Amendments / Revisions)Converting Older Invoicing FilesQuestions
3. DefinitionsPayment MethodsACFF – Actual Cost Plus Fixed Fee: Actual costs including labor, overhead, FCCM, other direct costs and fixed fee LSUM(A) - Lump Sum payment method but costs are derived based on ACFF.MILE(A) - Milestone payment method but costs are derived based on ACFF.AC - Actual Cost: Actual costs including labor, overhead, FCCM and other direct costs. No fixed fee.LDHRLY - Loaded Hourly: Labor costs are derived based on hours and a loaded hourly rate. No overhead, FCCM or Fixed Fee applies beyond the loaded rate. ODCs may be applied.
4. DefinitionsPayment Methods - continuedUNIT - Unit Cost: Costs are based on unit rates and the number of units proposed. No overhead, no FCCM or Fixed Fee applies. ODCs may be applied.LSUM - Lump Sum: Costs are derived as one lump sum for the entire contractMILE - Milestone: Costs are derived as a series of lump sum payments, each made at the completion of a milestone.Contractual RelationshipsPRIME firms are contracted with MDOT.TIER 1 subconsultants are contracted with the Prime firm.TIER 2 subconsultants are contracted with a Tier 1 firm.
5. DefinitionsBudget TypeAs Needed - A project where the budget is set up for the contract but the JN's are unknown at the time of the proposal. Or, a project with a derived budget for one or more JN's but the services are "As-Needed" (Design Assistance During Construction, etc.). Fixed fee payments are based on the % of hours expended in a billing period.Derived - A project where there is a derived budget for one or more JN's that are known at the time of the proposal. Fixed fee payments are based on % project completed in a billing period.ModificationsAny adjustment to the original budget (Amendment, Revision, or Transfer).
6. Overall ImprovementsBetter treatment of Tier 2 firms and firms with payment methods other than Actual Cost Plus Fixed Fee (ACFF).Greater automation for hiding worksheets, collapsing and expanding rows / columns, and saving to a PDF file using macros.More centralized data entry.Enhanced guidance for usage.
7. Specific ChangesGeneral:Added a one step input process that is more seamless.Added room for 20 budget modifications.Improved the BUDGET Worksheet to help monitor budget progress for each firm by JN.PAYMENT VOUCHER Worksheet:Added space below for MDOT approvals and final invoice status.
8. Specific ChangesINVOICE Worksheet:Added subtotals for different payment methods to show properly.Reorganized invoice summary per consultant section.
9. Specific ChangesINVOICE Worksheet – Continued:Moved invoice number and date information from JNA & JN1.Added section to adjust current OH & FCCM billing rates and tracking.
10. Specific ChangesJN SUMMARY Worksheet:Reorganized to show more clearly and minimize confusion. Totals are on the left and all JN’s are to the right. Columns will be hidden to only show JN’s with costs for a given billing period.ODC SUMMARY Worksheet:Automated appearance of each item and the total.DBE PAYMENTS Worksheet: Updated based on new form.Added cells for DBE % Proposed and % DBE attained and reason for not attaining DBE % Proposed.
11. Specific ChangesOVERAGES Worksheet:“Detached” the calculation of overages from the invoice.This worksheet is a manual entry to communicate overages if they occur by JN or overall contract.Invoice data bills up to contract limits and enter the amount over budget on the overage worksheet. Submit worksheet with invoice.JN(A-J) Worksheets (Derived)Invoice number and associated dates are copied from the INVOICE Worksheet.Added % Complete (ACFF) and % Complete (Non-ACFF).% Complete (ACFF) = % project completed during billing period (manual)% Complete (Non-ACFF) = % of budget spent during billing period (auto)
12. Specific ChangesAs Needed JN ListNew worksheet to record up to 50 As Needed JN’s in one location. Do not duplicate.JN(1-50) Worksheets (As Needed):Invoice number and associated dates are copied from the INVOICE Worksheet.Added % Complete (ACFF) and % Complete (Non-ACFF).% Complete (ACFF) = % of hours spent during billing period (auto)% Complete (Non-ACFF) = % of budget spent during billing period (auto)
13. Specific ChangesImplementation of this version will result in greater consistency among firms, less errors, more efficient reviews for MDOT, and potentially faster payments.
14. Step-by-Step InstructionsGetting startedMake sure that the priced proposal information is the proper version.Understand the budget type (As-Needed vs Derived). Have the Authorization Information available.Know the contact person responsible for billing.Make certain “Enable Editing” or “Enable Macros” is selected when prompted or set in Excel settings.
15. Step-by-Step InstructionsSetting up the invoice filePriced Proposal Spreadsheet:Update the priced proposal file with the proper dates, JN’s, phases, contract numbers, etc. before importing.Create or obtain the “OUTPUT” file from the priced proposal.ORIG Worksheet:Select, copy and paste the values from the OUTPUT file into the ORIG Worksheet.Under the “Data” menu, select “Edit Links” and “Break Links” for any files listed to remove the link.BUDGET Worksheet:Select “Hide Orig – M-20” or “Unhide Orig-M20” macro buttons to hide or unhide all worksheets related to the original priced proposal and subsequent modifications.
16. Step-by-Step InstructionsSetting up the invoice file - ContinuedINVOICE Worksheet:Complete all “Blue” highlighted cells.Use the pull down menu under the firm name to select the firm.Select the appropriate budget type.
17. Step-by-Step InstructionsSetting up the invoice file - ContinuedPAYMENT VOUCHER Worksheet:Complete all “Blue” highlighted cells representing contract dates.
18. Step-by-Step InstructionsSetting up the invoice file - ContinuedDBE PAYMENTS Worksheet:Describe services performed by each DBE firm.AS NEEDED JN LIST Worksheet:For As Needed projects, enter all known JN’s. Add new JN’s when necessary. Do not duplicate.
19. Step-by-Step InstructionsSetting up the invoice fileINVOICE Worksheet:Select “Hide Unused Worksheets” macro button to hide unused worksheets.If you want to see all worksheets, select “Unhide All Worksheets”Demo of Invoice Setup
20. Step-by-Step InstructionsMonthly invoicing:INVOICE Worksheet:Select appropriate billing period from pull down list (cell B20).Enter invoice number, billing period begin and dates, and invoice date.Select the “Hide Unused Worksheets” macro button.
21. Step-by-Step InstructionsMonthly invoicing:JN(A-50) Worksheets:Select the “COL” macro button in the upper left of the worksheet to collapse the extraneous rows and columns.Enter invoice data for each firm for each category.Costs for all JN’s for a given billing period must use the same billing period number. For example, if only JN1 was billed for the first 4 billing periods before JN2 began, data for JN2 would begin in billing period 5.After the invoice amounts have been verified and corrected as needed, “fix” the amounts for OH, FCCM and FF by copying and pasting values to overwrite the formulas. This prevents previous amounts from future changes in budget, OH rates, etc. % Complete (ACFF) is a manual entry for derived budgets. % Complete (ACFF) for as-needed budgets and % Complete (Non-ACFF) payment methods are automatic calculations.
22. Step-by-Step InstructionsMonthly invoicing:ODC SUMMARY Worksheets:Add dates and costs for each appropriate ODC item totaling the amount at the bottom.Provide backup for any single ODC category exceeding $2,500.
23. Step-by-Step InstructionsMonthly invoicing:DBE PAYMENTS Worksheets:Enter amounts previously paid to DBE firms and any deductions.Only have DBE firm sign the form if it is the final invoice.
24. Step-by-Step InstructionsMonthly invoicing:Save to PDF: INVOICE Worksheet, select “Hide Unused Rows, Columns & Worksheets then Save to PDF File” macro button. Enter filename and path when prompted.Verify that amounts on all PDF forms match the JN Worksheet data.Verify that all appropriate forms are included in the PDF file.
25. Step-by-Step InstructionsMonthly invoicingDemo of Monthly Invoicing
26. Compiling Monthly InvoiceSignatures:The Prime firm will be the only digitally encrypted signature on the invoice.All subconsultants should sign digitally but print the signed PDF file to another PDF file to “rasterize” the signature before emailing to the prime firm.
27. Compiling Monthly InvoicePrime Firms - Create one PDF invoice:PRIME FIRMPayment Voucher Standard Consultant InvoiceJN Summary FormLabor Detail (ACFF/AC/LDHRLY) or Invoice Detail (UNIT/LSUM/MILE)ODC Summary Invoice Detail – Tier 1 Sub (UNIT/LSUM/MILE)DBE PaymentsEACH SUBCONSULTANTStandard Consultant Invoice (Tier 1 Subs - ACFF/AC/LDHRLY) JN Summary (Tier 1 Subs - ACFF/AC/LDHRLY) JN Summary (Tier 1 Subs – UNIT/LSUM/MILE) if Tier II sub is also billingLabor Detail (ACFF/AC/LDHRLY) or Invoice Detail (UNIT/LSUM/MILE)ODC SummaryInvoice Detail – Tier 2 Sub (UNIT/LSUM/MILE)
28. Compiling Monthly InvoiceInvoicing responsibility:Prime firms must complete their own invoices.Prime firms must include all Sub’s invoice data in their invoicing spreadsheet.All ACFF/LDHRLY/AC firms must complete their own invoicing spreadsheet.Tier 1 UNIT/LSUM/MILE subconsultant costs may be included in the Prime Firm’s invoice. Tier 2 UNIT/LSUM/MILE subconsultant costs may be included in the Tier 1 Firm’s invoice.UNIT/LSUM/MILE subconsultants do not need to complete their own invoicing spreadsheets. Company issued invoices from these organizations will be used as invoice detail.
29. Flow of Information Between FirmsProject InitiationPrime FirmSubconsultantsReview, sign and forward Agreement to Prime firmPrepare and forward Sub Agreement and Authorization to SubsExecute agreement and send to Subs. Setup invoicing fileFile agreement.Setup invoicing fileCommunicate invoicing schedule to subs.Setup billing cycle to follow Prime Firm’s schedule.Authorization from MDOT
30. Flow of Information Between FirmsMonthly Invoicing CyclePrepare monthly Invoice, sign and email to Prime firm.Prepare monthly invoice including Subs costs. Review, sign and upload to ProjectWise.Prime FirmSubconsultantsMDOTPayment ProcessReceive payment.Receive EFT payment and process payments to Subs.
31. Changes in Overhead and FCCM RatesJN(A-50) WorksheetsIf the OH, FCCM and FF amounts were not “fixed” during the billing process, do so before making any changes.INVOICE WorksheetsChange the OH or FCCM rates in accordance with MDOT Annual Financial Prequalification ApprovalsJN(A-50) WorksheetsEnter invoice data.
32. ModificationsSave a copy of the invoicing spreadsheet before proceeding.JN(A-50) WorksheetsIf the OH, FCCM and FF amounts were not “fixed” during the billing process, do so before making any changes.PRICED PROPOSAL Spreadsheet:Make sure all firms are included in the same order as the original INFO worksheet.INVOICE WorksheetSelect “Unhide Orig - M20” macro button.
33. ModificationsM(1-20) WorksheetsSelect, copy and paste values from OUTPUT file into the appropriate M worksheet.BUDGET WorksheetVerify that the budget is correct and the billed amounts are correct.
34. Converting Older Invoicing FilesAll new projects beginning December 1 will require using the new invoicing spreadsheet.Converting to the new spreadsheet is optional. However, it is suggested for projects that are not too far along. Conversion Procedure:Prepare a priced proposal spreadsheet for the original contract and each modification.Input each OUTPUT file as appropriate.Follow the instructions for setting up the invoice file.
35. Converting Older Invoicing FilesConversion Procedure - ContinuedOn the INVOICE Worksheet, In the INVOICE NUMBER and DATE INFORMATION chart, change billing period 1 to represent the last billing period invoiced. The billing period begin date should match the first billing period and the billing period end date should match the last billing period invoiced. The Invoice number and invoice date should match the last invoice.In the JN(A-50) Worksheets, under the column representing the most recently invoiced billing period, enter the total amounts billed to date from the previous invoicing spreadsheet.Compare the data in both versions of the spreadsheet to verify that all previously billed amounts are correct.Continue invoicing.
36. Questions