Effective Training and Improvements for Transportation Invoice Procedures
Comprehensive training and improvements for the Department of Transportation invoice processes in October/November 2015. The content covers agenda, site logistics, invoice spreadsheet definitions, payment methods, contractual relationships, budget types, modifications, and overall enhancements for better treatment and automation. Specific changes include a streamlined input process and enhanced budget worksheet.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
DEPARTMENT OF TRANSPORTATION Invoice Training October/November 2015
Agenda Site Logistics Invoice Spreadsheet Definitions Overall Improvements Step by step instructions Compiling monthly PDF Invoice Flow of information between firms Modifications (Amendments / Revisions) Converting Older Invoicing Files Questions
Definitions Payment Methods ACFF 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.
Definitions Payment Methods - continued UNIT - 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 contract MILE - Milestone: Costs are derived as a series of lump sum payments, each made at the completion of a milestone. Contractual Relationships PRIME firms are contracted with MDOT. TIER 1 subconsultants are contracted with the Prime firm. TIER 2 subconsultants are contracted with a Tier 1 firm.
Definitions Budget Type As 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. Modifications Any adjustment to the original budget (Amendment, Revision, or Transfer).
Overall Improvements Better 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.
Specific Changes General: 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.
Specific Changes INVOICE Worksheet: Added subtotals for different payment methods to show properly. Reorganized invoice summary per consultant section.
Specific Changes INVOICE Worksheet Continued: Moved invoice number and date information from JNA & JN1. Added section to adjust current OH & FCCM billing rates and tracking.
Specific Changes JN 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.
Specific Changes OVERAGES 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)
Specific Changes As Needed JN List New 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)
Specific Changes Implementation of this version will result in greater consistency among firms, less errors, more efficient reviews for MDOT, and potentially faster payments.
Step-by-Step Instructions Getting started Make 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.
Step-by-Step Instructions Setting up the invoice file Priced 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.
Step-by-Step Instructions Setting up the invoice file - Continued INVOICE Worksheet: Complete all Blue highlighted cells. Use the pull down menu under the firm name to select the firm. Select the appropriate budget type.
Step-by-Step Instructions Setting up the invoice file - Continued PAYMENT VOUCHER Worksheet: Complete all Blue highlighted cells representing contract dates.
Step-by-Step Instructions Setting up the invoice file - Continued DBE 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.
Step-by-Step Instructions Setting up the invoice file INVOICE 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
Step-by-Step Instructions Monthly 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.
Step-by-Step Instructions Monthly 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.
Step-by-Step Instructions Monthly 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.
Step-by-Step Instructions Monthly 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.
Step-by-Step Instructions Monthly 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.
Step-by-Step Instructions Monthly invoicing Demo of Monthly Invoicing
Compiling Monthly Invoice Signatures: 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.
Compiling Monthly Invoice Prime Firms - Create one PDF invoice: PRIME FIRM Payment Voucher Standard Consultant Invoice JN Summary Form Labor Detail (ACFF/AC/LDHRLY) or Invoice Detail (UNIT/LSUM/MILE) ODC Summary Invoice Detail Tier 1 Sub (UNIT/LSUM/MILE) DBE Payments EACH SUBCONSULTANT Standard 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 billing Labor Detail (ACFF/AC/LDHRLY) or Invoice Detail (UNIT/LSUM/MILE) ODC Summary Invoice Detail Tier 2 Sub (UNIT/LSUM/MILE)
Compiling Monthly Invoice Invoicing 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.
Flow of Information Between Firms Project Initiation Authorization from MDOT Prime Firm Prepare and forward Sub Agreement and Authorization to Subs Subconsultants Review, sign and forward Agreement to Prime firm Execute agreement and send to Subs. Setup invoicing file File agreement. Setup invoicing file Communicate invoicing schedule to subs. Setup billing cycle to follow Prime Firm s schedule.
Flow of Information Between Firms Monthly Invoicing Cycle Prime Firm Subconsultants Prepare monthly invoice including Subs costs. Review, sign and upload to ProjectWise. Prepare monthly Invoice, sign and email to Prime firm. MDOT Payment Process Receive EFT payment and process payments to Subs. Receive payment.
Changes in Overhead and FCCM Rates JN(A-50) Worksheets If the OH, FCCM and FF amounts were not fixed during the billing process, do so before making any changes. INVOICE Worksheets Change the OH or FCCM rates in accordance with MDOT Annual Financial Prequalification Approvals JN(A-50) Worksheets Enter invoice data.
Modifications Save a copy of the invoicing spreadsheet before proceeding. JN(A-50) Worksheets If 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 Worksheet Select Unhide Orig - M20 macro button.
Modifications M(1-20) Worksheets Select, copy and paste values from OUTPUT file into the appropriate M worksheet. BUDGET Worksheet Verify that the budget is correct and the billed amounts are correct.
Converting Older Invoicing Files All 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.
Converting Older Invoicing Files Conversion Procedure - Continued On 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.
