Accounting Analysis#

This analyses two data streams that represent 2i2c’s financial activity.1 It is meant to be used for both financial analysis and projection, as well as defining a few KPIs for sustainability and efficiency.

Last updated: Jan 27, 2023

Data source

Accounting base in AirTable that contains data that we use in these analyses. It is accessed programmatically via an API key. See these instructions to create an API key for yourself.

Financial summary#

Provides an overview of our costs, revenue, and burn rate.

Our financial summary is generated from CS&S’s monthly accounting data dumps. It is less user-friendly than the AirTable data used to define revenue, but has complete cost information, and so we use it to define our own costs and the Source of Truth for our financial situation.

What’s in this data. These contain every transaction that 2i2c has ever recorded with CS&S.

To update this Table with the latest data

  • Go to the 2i2c Financial Statements folder with CS&S (only accessible to CS&S and 2i2c admins)

  • Open the latest financial statement (new ones are loaded each month)

  • In the first tab (Account Transactions), copy all of the records (excluding header names and footer content). This usually starts on Row 9.

  • Go to the Accounting Transactions Table

  • Select all cells on the table (ctrl/cmd + A as a shortcut)

  • Paste all of the copied records into this table. From the top, it should look like nothing has changed, but there should now be new records at the bottom.

Costs#

Monthly costs broken down by major category.

Costs are generated from CS&S’s monthly accounting data dumps (see above).

Revenue#

Our revenue data is defined in the Invoices AirTable. It is synced from the CS&S AirTable that contains all invoices for 2i2c.

What’s in this data. Includes all invoices but does not contain some revenue and costs. Excludes payments to employees as well as grant-based payments.

To sync this Table with the latest data

  • Click on the Invoices table

  • Click on the downward caret (v)

  • Click on ⚡Sync Now

Same plots but with grants removed because they are quite high.

Hub Service revenue with a monthly average

3-month average revenue from hub service and development: $24,992.51

Monthly contract revenue (total revenue minus grants) as a percentage of monthly costs.

Accounting tables#

Summary tables of revenue and cost by major category. Note that these are reversed in time. They begin with the latest updated month and end with our earliest month.

Overview#

Date November, 2022 October, 2022 September, 2022 August, 2022 July, 2022 June, 2022 May, 2022 April, 2022 March, 2022 February, 2022 January, 2022 December, 2021 November, 2021 October, 2021 September, 2021 August, 2021
Category                                
Revenue $26,692 $1,790,515 $96,624 $43,250 $29,000 $231,501 $3,750 $17,346 $15,563 $6,400 $5,000 $212 $0 $2,004 $2,581 $669,219
Cost $-226,456 $-64,314 $-70,757 $-60,455 $-112,160 $-61,722 $-47,175 $-47,868 $-73,261 $-34,263 $-51,722 $-112,574 $-43,913 $-42,191 $-54,950 $-38,834
Net $-199,764 $1,726,201 $25,867 $-17,205 $-83,160 $169,779 $-43,425 $-30,522 $-57,698 $-27,863 $-46,722 $-112,362 $-43,913 $-40,188 $-52,369 $630,385
Cash on Hand (end of month) $1,797,041 $1,996,805 $270,604 $244,737 $261,942 $345,102 $175,323 $218,748 $249,270 $306,968 $334,831 $381,553 $493,915 $537,829 $578,016 $630,385

Cost#

Date November, 2022 October, 2022 September, 2022 August, 2022 July, 2022 June, 2022 May, 2022 April, 2022 March, 2022 February, 2022 January, 2022 December, 2021 November, 2021 October, 2021 September, 2021 August, 2021
Category                                
Bank Charges $104 $104 $114 $150 $38 $78 $38 $38 $38 $38 $38 $38 $38 $38 $38 $0
Costs Rebillable to Customers $4,015 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Fiscal Sponsor Fee Expense $161,908 $8,569 $6,223 $7,874 $23,886 $5,284 $2,812 $1,252 $2,658 $750 $0 $70,383 $688 $0 $0 $30,000
Foreign Exchange Gain or Loss $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Miscellaneous Expenses $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $50 $0 $0 $0 $0 $0
Office Expenses $117 $117 $2,975 $69 $0 $78 $78 $1,914 $0 $0 $179 $0 $1,034 $0 $0 $0
Personnel Costs $60,313 $55,525 $61,445 $52,363 $85,986 $56,281 $44,246 $44,664 $70,565 $33,474 $51,384 $42,153 $42,153 $42,153 $54,912 $8,834
Program Expenses $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $70 $0 $0 $0 $0 $0
Revenues $0 $0 $0 $0 $2,250 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Sum $226,456 $64,314 $70,757 $60,455 $112,160 $61,722 $47,175 $47,868 $73,261 $34,263 $51,722 $112,574 $43,913 $42,191 $54,950 $38,834

An expected annual total, used to calculate our expected operating costs over a single year. Calculated by either summing across the last 12 months. For months that do not have 12 previous months of historical data, we calculate the sum of the available data, and then add mean(available_data) * n_missing_months. This might introduce some skew into our data for months with unusually high costs.

Date November, 2022 October, 2022 September, 2022 August, 2022 July, 2022 June, 2022 May, 2022 April, 2022 March, 2022 February, 2022 January, 2022 December, 2021 November, 2021 October, 2021 September, 2021 August, 2021
Expected Annual Costs $2,717,475 $1,744,621 $1,446,110 $1,265,948 $1,281,943 $1,191,731 $1,102,355 $1,036,363 $1,018,893 $958,119 $927,441 $962,729 $780,186 $758,063 $742,256 $720,635

Revenue#

Date November, 2022 October, 2022 September, 2022 August, 2022 July, 2022 June, 2022 May, 2022 April, 2022 March, 2022 January, 2022 December, 2021 October, 2021 September, 2021 August, 2021
Category                            
Development $9,635 $9,635 $20,000 $20,000 $20,000 $40,000 $0 $0 $0 $0 $0 $0 $0 $0
Grant $492,680 $0 $1,755,765 $0 $0 $111,013 $0 $0 $14,063 $0 $0 $0 $0 $669,219
Hub Service $13,808 $3,750 $10,125 $30,750 $12,000 $66,478 $9,740 $42,480 $7,000 $11,400 $15,740 $2,004 $2,581 $0
Sum $516,122 $13,385 $1,785,890 $50,750 $32,000 $217,491 $9,740 $42,480 $21,063 $11,400 $15,740 $2,004 $2,581 $669,219
1

Inspired by James’ AirTable demo.