Review Xero setup with book keeper

[iteles]

In order to clear some of the backlog in financial tasks and getting #381 in, I am reviewing our existing setup with our book keeper.

This should also help with #383 and #382, I'll update those issues as relevant.

[iteles]

Left the in-progress label here until I was able to write up the findings of the day.

The following will need to be dealt with:

• Reconciliations have all been done as 'money spent' rather than reconciling between banks or with any actual bills, which is incorrect
• There are no freelancer invoices or invoices of any kind from 2017 in Xero - these will all need to be uploaded manually before FY17/18 Year End Accounts #406 can be done
  • Invoices can be emailed directly to Xero which speeds up reconciliation
• VAT returns can and should be submitted directly through Xero from now on so that Xero and HMRC match (this was done for our most recent VAT return by the ihorizon accountants 👍 )
• Coop Bank feeds can be manually updated in less than 5 mins
• Payroll was set up correctly though dividends were not and will need to be dealt with

Documentation will need to be written once processes for this are ironed out.

Will look into #383 and #382 in more detail themselves when bookkeeper is available again in late January.

[iteles]

Actions:

• Get Xero set up so that invoices go straight there where possible
• Write up process for submitting VAT returns
• Obviously don't pay anyone dividends before the year is over and we know if we will be turning a profit
• Write up process for updating Coop bank feed
• Write up process for reconciling Xero properly.

[iteles]

Write up process for updating Coop bank feed

Adding the images here as I go along, to be written up in readme (I'll do this once I'm done with the process)

Get export from Co-operative Bank

Updating the export

Xero will not be able to read the export just as it is so we need to update the formatting to make it compatible with Xero.

1. Open the CSV in Numbers (on Mac) or Excel (on Windows)
2. Delete all of the rows apart from the field titles row and the transactions rows

• Note that if there are a lot of transactions, you may end up with a row that says Page 1 of 1 in between the transaction rows

3. Manyally merge the Bank Reference and Customer Reference columns together so that the column you end up with contains both the client name and the invoice reference if one was given:

Before:

After:

4. Manually merge the Credit and Debit columns so that debits appear as negative values (the credits remain the same)

• Note: All the credit figures include VAT which we pay back to HMRC but you don't have to worry about this now as it's dealt with separately in the VAT and bank reconciliation processes

Before:

After:

5. Delete all columns except:

• Date
• Customer Reference (which now contains the information from Bank Reference as per step 3 above)
• Credit (which now contains the information from Debit as per step 4 above)

6. Export your 3 column table to CSV
   

Import to Xero

In Xero, go to the '3 dot' menu on the Co-operative Bank account on the dashboard and click Import a Statement

Follow the steps to upload & import your CSV.

NOTE: The first couple of times you do this and then sporadically from then on, a table will appear at the bottom of the import page - you must make sure that Xero is interpreting the lines correctly.

For example, in the screenshot below, Xero recognised that the first column was the transaction date correctly but couldn't recognise the second column:

Choose the correct column name from the drop-down menu (in this case, description of the transaction):

Success!

[rub1e]

@iteles check you out - loving it!

If you want to just confirm you're happy with it, I can add it into the md file (unless you're keen for the green square... 😉 )

I'm thinking of just slicing this and the Xero reconciliation off into its own guide as the general finances one is getting pretty long!

[iteles]

Haha, no it's fine, I need to add some wording around the first bit, might as well do it straight into the readme.

YES, I think the finance.md needs some more organisation, happy for it to be a folder of mds on finance topics rather than a file. Let's open an issue in the right repo for it 👍

[iteles]

Moved writing up the reconciliations process to dwyl/process-handbook#128 so we can now close this issue 👍