MFM GST Return Procedures

Prepare paper folders

  • 1 x monthy foolscape pocket folder
  •  Nov 2019 on: (Now we have GST on Aliexpress purchases etc)
    • 2 x Manilla folders:
      • Kiwibank / Paypal payables in one folder (includes Paypal/Trademe PURCHASES not made on personal CC)
      • All Personal CC Trans in second folder (includes Domestic & international (e.g. Aliexpres, Mouser, etc etc)
    • Sales invoices at bottom of foolscape folder
    • Then above 2 x manilla folders
    • Then Bank statements, GST declarations, Cashbook hardcopy
  • Nov 18 on
    • 2 x Manilla folders:
      • Kiwibank payables in one folder
      • Trademe / paypal / Personal CC Trans in second folder
      • Sales invoices at bottom of foolscape folder
      • Then above 2 x manilla folders
      • Then Bank statements, GST declarations, Cashbook hardcopy
  • Pre Nov 18 return:
    • 5 x Manilla folders:
      • Receivables
      • KB / Paypal Payables
      • Trademe
      • Paypal
      • Personal CC Txns

Prepare new period in cashbook:

  • Copy cashbook to backup and name per last edit date
  • Open cashbook
  • Copy / Paste previous periods rows to a new space below leaving 2 clear rows between and previous and new
  • In new period remove all keyed txns, copy down calculated columns from top row to bottom to repair formulas
  • Enter previous period closing as new period opening for KB, TM, PP balance columns

MFM KB Bank txns

  • Get/Print bank statement for target period
  • Check Worksheet KB Opening Bal = Statement Opening Bal
  • Get KB Bank acct Txns into Cash book
    • Log into IB
    • Export all txns from first to last day of return period (Basic export = a csv file)
    • Open export in Excel (is the default for .csv) and check first / last txns are per bank statements
    • Take first 6 column column headings (‘Date’ to ‘Total Amount’) from cashbook and paste as first row into export
    • Populate & cleanup pere column headings:
      • Date = date
      • Name = per bank export desc
      • Columns ‘Cheque/refer’ & ‘Code’ should be empty
      • Set ‘Acct’ column to ‘KB’ for all rows
      • Total Amount = txn amount (Note from 1/4/2020 no need to set as inverse as workbook is now fixed)
    • Copy -> Paste as values the exported txn range (not headings) from the csv into the top of the the cashbook period
  • Code txns
    • For each payable check and file invoice in KB / PPal Payables manilla folder
    • File receivables in bottom of monthly main folder
    • No GST on:
      • GST payment
      • SOME Imports
      • Youshop payment
      • Interaccount transfers
      • Drawings
      • Bank fees
  • Check Calculated KB Closing Bal = Statement Closing Bal

PayPal Txns

  • Note1: If payments are HELD over periods (months) this will get a little more complicated, sort it out if and when it happens, the procedures here assume this is not the case…
  • Note2: Purchases paid for from PPal balance are not covered in this document, just use your nous, we try to avoid these so will expand on them if they become more prevalent, however we observe:
    • In the Paypal Monthly CSV export, for foreign currency purchases there maybe multiple 2 records and out of order, so delete unnecessary records
  • Get PPal txns into Cashbook
    • Print/Export txns:
      • Login
      • Go to Reports->Statements->Monthly
      • Request PDF & CSV
      • Print PDF statement
        • Check Worksheet PP Opening Bal = PPal printed Opening Bal
      • Open CSV
      • Cleanup:
        • Delete payment held / released rows
      • Take first 6 column column headings (‘Date’ to ‘Total Amount’) from cashbook and paste in PPal worksheet out to the right
      • We need to get 2 lots of txns for pasting into our cashbook::
        1. All txns
          • Populate the columns:
            • Date = Date
            • Name = Name from available fields e.g. ‘Website Payment Sandy Campbell OL9997’
            • Cheque/ref = empty
            • Code = 210 for sales leave empty for anything else (transfers, purchases)
            • ACCT = PP
            • Total Amount = Gross
          • Copy / Paste as values into Cashbook (This is all TXNS)
          • Code remaining transactions
            • No GST on Inter-account transfers
            • Check no GST on import payments
              • if in foreign currency actual NZ$ amounts may need to be entered manually here
        2. Fees
          • Back in Paypal worksheet copy the rows we prepped above below themselves (so we can go back in the previous step if we don’t balance)
          • In our new copy, remove any rows where there are no PPal fees (Fees = $0.00)
            • Change output columns:
              • Code = 307
              • ‘Total Amount’ = Fee
          • Copy / Paste as values into Cashbook (This is FEES)
          • Check Calculated PP Closing Bal = PPal Printed Closing Bal
          • Finish coding fees:
            • No GST as IRD won’t tell us if PPal is GST registered
    • For each payable check and file invoice in KB / PPal Payables manilla folder
    • File receivables in bottom of monthly main folder (We just lump all receivables (invoice) together regardless of how paid

Trademe Txns

  • Login to each TM account and in My Account:
    • Print GST receipt for each month in the target GST period
    • Print txn listing, sufficient pages to cover the target GST period
      • label opening and closing balances
      • Total opening & closing balance across all TM accounts
      • Check opening is correct per previous period
    • Manually enter txns into Cashbook
      • 1 per each Paypal topup txn (890 = Interacct transfer, no GST)
      • 1 per monthly GST receipt printed above
      • Check closing balance reconciles

Cash / Personal Txns

  • Any cash sales or purchases key manually, Acct = LSR
  • Visa purchases
    • Login into bank & search for target period txns, print hardcopy
    • Mark on hardcopy MfM txns
    • In Aliexpress search for all orders in GST Period and print a record of purchases (just for filing)
    • Open Stock workbook:
      • Reconcile payments against Stock workbook
      • Enter payments manually from Visa txn print in Cashbook
        • Acct = LSR
        • Usually no GST
    • Enter a final Txn ‘Owner Contributions’ Code = 501 (no GST) to bring LSR column down to 0.00

Final checks:

  • Check closing balances are correct per each of:
    • KB
    • TM
    • PPal
    • LSR (must be $0.00)
  • Check txns with no GST by code:
    • 890 = Interact tfr
    • GST remittances
    • Bank fees
    • Imports / payments made to foreign entities

Fixed Asset sales/purchases:

  • Enter into Assets worksheet

To convert dates currently in US m/d/y format to REST OF THE WORLD (Stupid gringos!) d/m/y:

  1. Select the column of dates
  2. Under DATA, select “Text to Columns”
  3. Select Delimited – Next
  4. Untick ALL delimiters – Next
  5. Select column format “Date: MDY” – Finish
    1. Note this data format should be what the dates are currently in, not what you want them to be
  6. The text will now be recognised by Excel as a date and appear as right-justified in the column