FinancialsReport

Financials Report

We're now publishing a monthly financial report to keep people more in touch with DoES Liverpool's money situation. The report is based on an export from a FreeAgent Profit & Loss report which shows figures based on invoice dates rather than when funds are received, as such it should only be used as an indication rather than an accurate report of DoES Liverpool's income and outgoings. If payments come in a later month after the report has been generated then they may never show on these wiki pages. Similarly if a report gets regenerated later then this may cause the numbers to change (as previously unpaid invoices may now have been paid).

The FreeAgent report also comes with this statement:

Monthly Operating Profit excludes Depreciation and Income/Corporation Taxes

We aim to publish this report as close to the beginning of each month as possible. That then makes it available to the Directors Meeting and the Community Meeting each month.

This was inspired by monthly reports put out by Nottinghack: https://wiki.nottinghack.org.uk/wiki/Category:Financials

Categories

FreeAgent actually has a great description of the categories on their website. Some additional information follows:

• Sales - This basically covers most of our income, including desk rental, material sales, almost everything except..
• Grant Income - This basically covers anything that is not a sale, most of this will be "Friend of DoES" membership although if we were ever to take funding, most likely for specific events, it would show up here.
• Office Costs - Service charge will show up here – this covers a long list of things that the landlord pays for and passes costs on to us for, e.g. cleaning windows, cleaning the communal areas, various fire and health & safety compliance testing, and more. Electricity and water rates show up here too. Other general things might show up here
• Internet & Telephone - We get VOIP services from Andrews & Arnold and internet from Baltic Broadband. Besides this we occasionally use a 3G SIM when attending events.
• Web Hosting - We use various online services to host DNS, websites and other online services (e.g. the laser booking system) payments for those should show up here (unless they get bunched into office costs).
• Fixtures & Fittings - These do not actually show up on these reports.

Generation of the reports

The reports are generated using a ruby script found here: https://github.com/DoESLiverpool/financial-report/blob/master/lib/tasks/generate.rake

It's a Rake task as part of a larger Rails app that generates graphs used in the yearly money blog posts. The script takes a CSV file that has been exported from FreeAgent for the Profit & Loss data and a manually compiled YAML file containing the summary information and notes.

To get the best CSV file from FreeAgent you should look at the Monthly report for the last 3 months, then edit the URL so that the parameters start on the 1st of the month and end on the last day of the month, e.g.: https://doesliverpool.freeagent.com/accounting/monthly_profit_and_loss/2020-10-01_2020-10-31. That way you will only get a single month's data and if you click on any of the categories to see more information you only get the information for that month (whereas if you left it as a 3 month report you would see transactions across the three months).

Collecting Data

The YAML file contains a few sections. The summary can contain multiple subsections, the items within these subsections will be totalled. For example:

summary:
  "Current Assets":
    "Current Account": 5941.96
    "Petty Cash (approximate)": 189.80
    "Paypal": 904.67
    "Deposit Account": 4120.16
  "Liabilities":
    "Out of Hours Deposits (approximate)": 7140
    "Business Rates": 0

Will be output as:

• Current Assets: £11,156.59
  • Current Account: £5,941.96
  • Petty Cash (approximate): £189.80
  • Paypal: £904.67
  • Deposit Account: £4,120.16
• Liabilities: £7,140
  • Out of Hours Deposits (approximate): £7,140
  • Business Rates: £0

The assets are taken from FreeAgent and show the values at the end of the month. For a February 2024 report the easiest way to get the figures is to look at the bank account report for March 2024 as the first row will be "Balance brought forward" and show the figure for the end of February.

Liabilities show larger liabilities, i.e. bills that we have to pay. We generally list the business rates and out of hours deposits. In theory every one of our members could ask for their out of hours deposits back at once, at which point this value would be relevant. It's difficult to pin it down exactly so it's generally updated by searching in FreeAgent for "deposit" and checking what deposits were taken or returned within the relevant month, then using that to update the previous month's figure. The business rates figure is also taken by simple looking in FreeAgent for what is owed to the council.

"Money Owed" tells us how much of the invoices we generated within the relevant month have not yet been paid. This is manually checked by looking on FreeAgent, on the homepage in the "Invoice Timeline" widget, hovering over the red part of the graph for the relevant month. Ideally this is done on the 10th of the following month (i.e. for the March report the figure is checked on the 10th April) but sometimes this is missed, the date is reported each month.

"Turnover Last 12 Months" was added early 2024 so that we can see how close we are getting to hitting the VAT threshold. If we go over this threshold (previously £85k but from 2023 now £90,000) then we must register for VAT. It's found by doing a Yearly Profit & Loss report for a custom range covering the 12 months to the end of the month being reported on. So if it's a March 2024 report the range would be 1st April 2023 - 31st March 2024.

Generating the Report

To generate our report you would use a command line similar to the following:

rake generate:profit_and_loss_summary[~/Downloads/monthly_profit_and_loss_2020-10-01_2020-10-31.csv,~/Coding/doesliverpool/financial-report-summaries/2020/financial-summary-202010.yaml,0]

Those arguments are:

• ~/Downloads/monthly_profit_and_loss_2020-10-01_2020-10-31.csv - location of the CSV profit and loss file
• ~/Coding/doesliverpool/financial-report-summaries/2020/financial-summary-202010.yaml - location of the YAML summary file
• 0 - which column of the CSV to use indexed from zero but skipping the first column