Spreadsheet: 768719d1645098815-amount-not-pulling-through-pivot-table-2022-01-pettycash
Sheet: INSTRUCTIONS

INSTRUCTIONS ON USING THE PETTY CASH FILE
The Instruction sheet is divided into the following sections:
 | 1) | Preparing file for the new month
 | 2) | Recording Transactions
 | 3) | Generating the JDE Import Journal
 | 4) | Balancing and Reports
1) | PREPARING FILE FOR THE NEW MONTH |  |  |  |  | For steps a) to f) ---> Run Script (Menu = Petty Cash / Initialise)
 | a) | Copy the "Net Balance Carried Forward" amount to cell "D9" using Paste Special / Values.
 | b) | Enter new date in cell "I1" - NB ALWAYS USE THE 1ST DAY OF THE MONTH.
 | c) | Ensure ALL Hidden Rows are Unhidden.
 | d) | Delete previous month's transactions by copying blank rows and pasting over existing transactions.
 |  | - For RECEIPTS section - copy columns A to F.
 |  | - For EXPENDITURE section - copy columns A to O.
 | e) | Goto the "PIVOT - SUMMARY" sheet. If the Pivot Table have not updated automatically, click on the
 |  | Google [NAME] refresh icon. Always values must be Zero.
 | f) | Goto the "JNL" sheet. Select the whole green shaded range starting at cell "O9" and delete the contents
 |  | by pressing delete on the keyboard
 | g) | Return to the "PETTYCASH" sheet, and Save the File for New Month.
2) | RECORDING TRANSACTIONS
 | For both the Receipts and the Expenditure sections, Columns A to F entries are similar:
 |  | Date - | Enter the Date of the Receipt or Expenditure, as per the Petty Cash Voucher.
 |  | Ref - | Enter a [NAME] reference for each Transaction. A Transaction for Petty Cash is ALL
 |  |  | entries required for 1 Petty Cash Voucher.
 |  |  | For Receipts - use PCRMMMYY@
 |  |  | For Expenditure - use PCMMMYY##
 |  |  |  | @ = Alphabetical A - Z
 |  |  |  | MMM = 3 Character Month name, i.e. JAN for January
 |  |  |  | ## = Numerical 01 - 99 as 2 digits
 |  |  |  | YY = 2 digit Year
 |  | Description - | Enter a Short Description to Explain the Receipt / Expense.
 |  | Value incl VAT - | Enter the Total Value of the transaction including any VAT - See NOTE ON VALUE
 |  |  | AND VAT  below.
 |  | VAT - | VAT is automatically calculated. If calculated VAT is incorrect, overwrite it with the
 |  |  | correct amount - See NOTE ON VALUE AND VAT  below.
 |  | Value excl VAT - | Automatically calculated, and MUST NOT be changed.
 | NOTE ON VALUE AND VAT
 | You MUST enter 1 Transaction for EACH combination of Cost Center allocations for the Expenditure.
 | e.g. | Fuel for Forklift - expense is split 50% POU and 50% RUM allocated to Warehouse - Therefore create
 |  | 2 transactions each to the value of 50% of the total amount of the expense.
 |  | Stationery for Warehouse and Office - expense is split between Warehouse and G&A as appropriate. Also the
 |  | Warehouse portion is split 50% POU and 50% RUM, and the G&A expense is split 80% POU and 20% RUM
 |  | Therefore create 4 transactions.
 | VAT must be split in the same ratio as the expenses.
 | For Expenditure section only, Columns G to K must also be completed for each transaction
 | ALL columns are completed by selecting the correct value from the dropdown list, or copying from a previous entry.
 | NOTE - While the value for EMPLOYEE is mostly "000 - BLANK", you must ensure that the correct Employee
 | is selected if the Cost Center is "320 - …."
3) | GENERATING THE JDE IMPORT JOURNAL
 | NOTE - You should balance the Cash first (refer point 4 below) before generating the JDE Import Journal.
 | a) | Goto the "PIVOT - SUMMARY" sheet. Right Click on cell "A5" and Select "Refresh" - This will generate new
 |  | values for the current month.
 | b) | Check that the Total generated agrees to the Total of VALUE excl VAT column on the "PETTYCASH" sheet.
 | c) | Count number of value lines in the generated report.
 | d) | Goto the "JNL" sheet. Ensure the are enough Green shaded lines - must be the same number of lines as on
 |  | the generated report from c) above. ADD or DELETE rows as appropriate.
 |  | - To Delete Rows - Select the appropriate number of Rows within the Green shaded area, Right Click within
 |  | the selection and choose Delete Rows.
 |  | - To Add Rows - Select 1 Row in the Green shaded area, Right Click within the selection and [NAME].
 |  | Now Select the appropriate number of Rows within the Green shaded area, Right Click within the selection
 |  | and choose "Insert Copied Cells"
 | e) | Copy the generated report from "PIVOT - SUMMARY" sheet, columns A to B, and Paste Special / Values
 |  | to the Green Shaded area on the "JNL" sheet.
 | f) | Ensure the Journal balances.
4) | BALANCING AND REPORTS
 | The following Balancing checks must be done:
 | a) | The Generated Report on sheet "PIVOT - SUMMARY" must agree to the Total of the VAT Exclusive expenses
 |  | on the "PETTYCASH" sheet.
 | b) | The Journal on the "JNL" sheet must balance - i.e. TEST must be Zero.
 | c) | Cash in the Pettycash box must agree to the "NET BALANCE CARRIED FORWARD" amount on the
 |  | "PETTYCASH" sheet.
 | To balance the [NAME] (point c) above), complete the schedule on the "CASH COUNT" sheet.
 | NOTES
 | a) | The BLUE shaded area is for Excessive amounts of coins held in separate bags, and are only completed
 |  | when you change the coins in the bags.
 | b) | Complete the GREEN shaded areas only, by counting the number of notes and coins and entering the number
 |  | in the appropriate cell (DO NOT ENTER THE VALUE OF NOTES OR COINS COUNTED)
 | c) | For [NAME] amount, enter the total value of Vouchers in the box but not yet recorded in the Transactions area
 |  | of the "PETTYCASH" sheet.
 | Printing of Reports
 | a) | Print only the Predefined reports on the sheets "PETTYCASH" and "JNL"
 | b) | Before printing the "PETTYCASH" report, HIDE all the unused rows in the Expenditure section.