Finance Manager completes monthly cashflow, categorising all received payments from bank statements and entering them into cashflow.
Finance Manager reconciles closing balance of cashflow with actual closing balance.
Finance Manager reviews and approves cashflow and categorisation
Finance Manager completes budget trackers for each region and sends to Managers of budgets, with accompanying notes, and arranges call to discuss variance and future spending.
Note: Please ensure to use the latest version of the Cashflow saved in the shared drive.
You always need to download the shared version to work from each month, this is due to the Finance Manager making changes to the forecast during the month. To upload another version to Sharepoint would overwrite the changes made.
Download the SOHK bank statements from CAF for the Current, VAT, Delivery, Wales and Scotland accounts.
In excel categorise the transactions into credit and debits
In the Accounts sheet create two new tabs, naming them '[Month] debit/credit'. Import the transaction data.
Catagorise the transactions assigning them to programme, event, overheads etc.
Pro tip: Check Xero for transactions you're unfamiliar with or double check the coding allocated to similar transactions in the previous month's transactions.
Once all transaction have been categorised insert a pivot table (highlight credit/debit and cashflow category columns, click insert, then pivot table. This will open a new tab rename this Credit/Debit [month] sum).
Import all data form the Pivot tables, except internal transfers, into the Cashflow.
When starting from the latest version of the Cashflow and after importing the data collated from the Accounts sheet, you need to check for figures that are in the Cashflow but don't then actually appear on the Bank Statements.
Pro tip: As well as adding the debits from the Accounts sheet, you also need to remove any figures that are in the Cashflow forecast but didn't actually take place. If these figures are over £1k, alert the CEO to see if this has a significant effect on SOHK.
Pro tip: try adding up every row in the '[Month] credit sum' except internal transfer. The figure you get should be same as the figure in that month's row 58 in the Cashflow. If these figures are different you've either left a figure in the Cashflow that was from the forecast and didn't take place, or you've copied one over incorrectly.
You can do the same check on outgoings - add up all the lines in '[month] debit sum' that aren't loan interest, VAT payments or internal transfer, and that figure should match the figure in that month's row 107 in the Cashflow.
Finally, you need to take the closing balance for that month from CAF for every account and add up those figures into the next month's row 4 in the Cashflow.
If you then subtract the loan balance (L116), the figure in row 4 should equal the figure in row 7. If it doesn't, then we have either copied across some figures wrong or we have missed out some transactions from CAF when downloading the bank statements.
The formula will look like this: =SUM([balance from current account]+[balance from Scotland account]+[other balances etc.])-L116
Once complete, use the tool 'format painter' on the Home tab, highlight the formatting from the previous month's column and paste it on the current month, so that it turns blue.
Send to CEO for approval.
Once approved upload to Sharepoint.
