Employee Time Card Detail Spreadsheet

Document Sample
Employee Time Card Detail Spreadsheet Powered By Docstoc
					                                                                                                    (Revised 06/22/09)

                                            TEXAS FOREST SERVICE
                                     TMG INVOICE BILLING DETAIL SCHEDULE
                                               INSTRUCTIONS



Overview

        This Excel workbook file includes four tabs:
              Instructions provides a step-by-step process for obtaining the TMG data and producing
              reports to include with the voucher.
              Download enables the excess TMG data columns to be deleted.
              Detail provides an entry area for account numbers and formats a report for the voucher.
              AcctSummary produces a summary by 15-digit FAMIS account number for the voucher.

        NOTE: Since columns will be deleted on the Download tab while processing TMG transactions, you must
        begin with a new Excel workbook for each TMG invoice.


Download Transactions from TMG into Excel

        Log into the TMG website:
        http://www.transmontaigne.com/es/logout.tmg
        If you need a log in set up, please contact the Compliance Reporting Officer at (979) 458-6682 for
        assistance.

        Select “Retail” located in the blue tool bar.

        Select “Retail Transactions.”


        Highlight your “Customer ID(s).” You may select multiple IDs by holding down the control key.

        Enter the dates of the invoice period. For example, the November 2008 invoice will be dated
        10/01/2008 (Posted Date From) through 10/31/2008 (Posted Date To). This will show the
        transactions for the month of October.

        Click on the “Search” button on the lower right side to pull up your request.

        This will take you to the “Tax Exempt Transaction List Report” that will show all the transactions
        for your billing ID for the period selected.

        Click on “Download Additional Details” which will provide you with your data in an Excel
        spreadsheet on your computer.


6485cd5f-e22b-4af5-b140-4c762509d2c2.xls                1                                        1/31/2011 1:57 PM
                                                                                                       (Revised 06/22/09)

                                            TEXAS FOREST SERVICE
                                     TMG INVOICE BILLING DETAIL SCHEDULE
                                               INSTRUCTIONS




   You must begin with a new, clean copy of this Excel workbook each time you process a TMG bill.


Download Tab of this Excel Workbook

        Copy the data from the TMG spreadsheet into the Download tab starting in cell A11.
        Confirm that the transaction total in cell CV5 matches the total on the emailed TMG invoice.

        Keep the columns with no marking on row 7. These columns are NOT shaded.
        Delete the columns marked DELETE on row 7. These columns have a rose-colored shading.

        When completed, the data should be in columns A through P.
        Confirm that the transaction total in cell P5 matches the total on the emailed TMG invoice.

        IMPORTANT: What is the Excel row number for the last transaction line? This determines the number of
        lines needed in the next step.


Detail Tab of this Excel Workbook

        IMPORTANT: If you change the shaded row marked "* * * LAST LINE -- DO NOT DELETE * * *" you
        probably will have to start over with a new Excel workbook.

        You must have enough blank lines to copy the transaction data ABOVE the shaded row marked "* * *
        LAST LINE -- DO NOT DELETE * * *". The shaded row must be below the row number for the last
        transaction line in the Download tab.

        For example, if the last transaction in the Download tab is on row 92, you must insert enough blank rows
        in the Detail tab to push the shaded LAST LINE to at least row 93. Inserting a few extra rows provides a
        "margin of error" when copying the data from the Download tab to the Detail tab.


        Insert enough blank rows between row 11 and the shaded row marked "* * * LAST LINE -- DO NOT DELETE
        * * *". Extra blank lines are OK.

        In the Download tab, highlight the data from cell A11 through the last data line of column P. Copy the
        highlighted data into the Detail tab starting in cell A11.



6485cd5f-e22b-4af5-b140-4c762509d2c2.xls                2                                         1/31/2011 1:57 PM
                                                                                                      (Revised 06/22/09)

                                            TEXAS FOREST SERVICE
                                     TMG INVOICE BILLING DETAIL SCHEDULE
                                               INSTRUCTIONS



        Delete the TMG column headings if they appear on line 11.
        Delete all blank rows between row 10 and the shaded row marked "* * * LAST LINE -- DO NOT DELETE * *
        *."

        Confirm that the transaction total in cell P5 matches the total on the emailed TMG invoice.

        Sort Instructions:
        Highlight cells A10 through cell T on the shaded Last Line.
        On the menu, click Data - Sort. Then select (1) Vehicle Number - ascending, (2) Transaction Date -
        ascending, (3) Trans # - ascending. Click Header Row, then OK.
        This sequence of the transactions should closely match the individual transaction logs for each card on
        your TMG invoice.

        Enter the account number (SL, SA, and object code) in columns Q through S for each transaction.

        Copy the formula from cell T8 into column T for each transaction. The formula must appear on every row
        from row 11 through the row immediately above the shaded LAST LINE.


AcctSummary Tab of this Excel Workbook

        This table provides a summary by account number for entry on the voucher. The table MUST be refreshed
        after any account number change is made in the Detail tab.

        Place the cursor in any of the account number cells in the pivot table.
        Right click the mouse, then click on Refresh Data.

        Ensure that no amount is allocated to the lines "(blank)" or "--".

        Confirm that the transaction total at the bottom of the pivot table matches the total on the emailed TMG
        invoice.


Print Reports for the Voucher

        Print the worksheets from the Detail tab and the AcctSummary tab. Please do not reformat these reports.




6485cd5f-e22b-4af5-b140-4c762509d2c2.xls                  3                                       1/31/2011 1:57 PM
TEXAS FOREST SERVICE
TransMontaigne Product Services, Inc
Invoice Billing Detail Transaction Download


                     DELETE                   DELETE         DELETE                DELETE                                   DELETE                     DELETE               DELETE                                                     DELETE           DELETE

Trans # TMG Trans Identifier               Batch Number   Account Code   Account Code Description   Customer ID Customer ID Description       Merchant Invoice Number   Credit Or Debit   TMG Invoice Number   Transaction Date   Transaction Time   Posted Date




6485cd5f-e22b-4af5-b140-4c762509d2c2.xls                                                                                                  4                                                                                                               1/31/2011 1:57 PM
    DELETE               DELETE                                     DELETE                                               DELETE                                                                DELETE                 DELETE

Report Level Merchant Code                 Merchant Name   Merchant Address1   Merchant City   Merchant State   Merchant Postal Code   Vehicle Number   Card Number(Last 4 Digits)   Employee(E) Or Vehicle(V)   Cardholder Name




6485cd5f-e22b-4af5-b140-4c762509d2c2.xls                                                                             5                                                                                                             1/31/2011 1:57 PM
         DELETE                  DELETE    DELETE              DELETE                 DELETE                                   DELETE           DELETE                 DELETE                     DELETE                     DELETE

Employee Number             Trip Number MCC Category   Driver License Number   Driver License State   Vehicle Description   VIN Number Vehicle License Plate   Vehicle Reg Expiration   Controlled Spend Misc #1   Controlled Spend Misc #2




6485cd5f-e22b-4af5-b140-4c762509d2c2.xls                                                                                            6                                                                                                         1/31/2011 1:57 PM
           DELETE                      DELETE         DELETE           DELETE          DELETE       DELETE        DELETE                                     DELETE                  DELETE

Prompted ID Number              POS Name        Odometer Reading   Trailer Number   Miles Driven Cost per Mile Product Code Product Description   Billing And Rpt Currency Cd   Unit of Measure   Product Quantity   Product Price Net   Product Total Cost Net




6485cd5f-e22b-4af5-b140-4c762509d2c2.xls                                                                                         7                                                                                                                     1/31/2011 1:57 PM
       DELETE                              DELETE                  DELETE                        DELETE                        DELETE                        DELETE                        DELETE                        DELETE

Transaction Fees         Cust Standard Profile Number   Reporting Hierarchy Level 1   Reporting Hierarchy Level 2   Reporting Hierarchy Level 3   Reporting Hierarchy Level 4   Reporting Hierarchy Level 5   Reporting Hierarchy Level 6




6485cd5f-e22b-4af5-b140-4c762509d2c2.xls                                                                                                8                                                                                                   1/31/2011 1:57 PM
               DELETE                                 DELETE                        DELETE                         DELETE                         DELETE                         DELETE                         DELETE                         DELETE

Reporting Hierarchy Level 7                Reporting Hierarchy Level 8   Reporting Hierarchy Level 9   Reporting Hierarchy Level 10   Reporting Hierarchy Level 11   Reporting Hierarchy Level 12   Reporting Hierarchy Level 13   Reporting Hierarchy Level 14




6485cd5f-e22b-4af5-b140-4c762509d2c2.xls                                                                                                      9                                                                                                                   1/31/2011 1:57 PM
                DELETE                                  DELETE                         DELETE                   DELETE                    DELETE                         DELETE                        DELETE                         DELETE

Reporting Hierarchy Level 15               Virtual Card Vendor Inv Number   Virtual Card Vendor Number   Customer PO Number   Virtual Card Warranty Report   Virtual Card Warranty Period   Virtual Card Repair Order #   Virtual Card Total Parts Cost




6485cd5f-e22b-4af5-b140-4c762509d2c2.xls                                                                                             10                                                                                                                   1/31/2011 1:57 PM
                DELETE                                DELETE                     DELETE                       DELETE                         DELETE                 DELETE                   DELETE         DELETE             DELETE           DELETE

Virtual Card Total Labor Cost              Virtual Card Total Tax Cost   Virtual Card Misc Cost   Merchant Chain or Brand Code   Merchant Chain or Brand Desc   Re-Price Status   Re-Price Description   Approval Flag   Approval Comment   Approval User




6485cd5f-e22b-4af5-b140-4c762509d2c2.xls                                                                                                     11                                                                                                             1/31/2011 1:57 PM
      DELETE               DELETE           DELETE          DELETE              DELETE           DELETE         DELETE        DELETE             DELETE     DELETE             DELETE                        DELETE               DELETE    DELETE

Approval Date        Approval Time         GL Code Valid Retailer Address   TMG Retailer ID Retailer County   Index Type Index Date Used    Index City    Index State Repriced Commodity Code   Repriced Commodity Description   Base Price Adder




6485cd5f-e22b-4af5-b140-4c762509d2c2.xls                                                                                               12                                                                                                            1/31/2011 1:57 PM
                                                                  (Revised 06/22/09)




                                                               Transaction Total
                                                                            -

                               DELETE           DELETE

Repriced PPG Repriced Amount Due           Discount Amount   New Amount Due




6485cd5f-e22b-4af5-b140-4c762509d2c2.xls                                               13   1/31/2011 1:57 PM
                                                                                                                                                                                                                                                                   (Revised 06/22/09)

                                                                TransMontaigne Product Services, Inc
                                                                Invoice Billing Detail Transaction Listing
                                                                                                                                                                                                                         Transaction Total
                                                                   Customer ID:      0                                                                                                                                                 -

                                                                Invoice Number:      0



                                                                                                                                                                                                    Product
                  Transaction                                                     Merchant   Vehicle   Card Number                                                        Product       Product                   Repriced   New Amount                                   Object
                                          Merchant Name          Merchant City                                            Vehicle Description      Product Description                             Total Cost                                   SL            SA
                     Date                                                          State     Number    (Last 4 Digits)                                                    Quantity     Price Net                    PPG         Due                                       Code
                                                                                                                                                                                                      Net




OT DELETE * * *                   * * * LAST LINE -- DO NOT DELETE * * *                               99999             * * * LAST LINE -- DO NOT DELETE * * *          * * * LAST LINE -- DO NOT DELETE * * *                       -      * * * LAST LINE -- DO NOT DELETE * * *




                     6485cd5f-e22b-4af5-b140-4c762509d2c2.xls                                                                          Page 14 of 16                                                                                                 Printed 1/31/2011 1:57 PM
                                                                           (Revised 06/22/09)




                            TransMontaigne Product Services, Inc
                            Invoice Billing Summary by FAMIS Account

                                                 Customer ID:
                                               Invoice Number:


                                           Sum of New Amount Due
                                           Acct15                  Total
                                           (blank)                  0.00
                                           Grand Total              0.00




6485cd5f-e22b-4af5-b140-4c762509d2c2.xls                   15                                   1/31/2011 1:57 PM
                                                                                                 (Revised 06/22/09)
                                 TEXAS FOREST SERVICE
                       FUEL CARD BILLING DETAIL FORM CHANGE LOG




 Revision                                        Description of Changes



06/22/09    Issued June 22, 2009
            Modified sort instructions to put transactions in Vehicle Number order rather than card number
            order.
            This change makes it easier for the voucher preparer to put the card use logs into the same
            sequence as the worksheet, since the card number does not appear on the logs.


12/23/08    Issued December 23, 2008
            New workbook to format TMG download data for processing with the credit card voucher.

				
DOCUMENT INFO
Description: Employee Time Card Detail Spreadsheet document sample