Ask Code for Science and Society to have a dedicated category and invoice type for our re-billable cloud costs

[choldgraf]

We need a way to track whether we are re-couping our monthly cloud costs from individual communities. The closest to the "source of truth" for this is to have dedicated accounting codes for pass-through cloud cost recovery. This is an issue to track standardizing and documenting this.

Account category

Currently, we have an Account type that is dedicated for this kind of activity: 7101 Costs Rebillable to Customers.

This seems like a reasonable category to use, since cloud costs are by far the only thing I can think of where we'd "pass through" the cost.

Costs

Any of our ongoing monthly cloud bills should be categorized under this account type. We should ensure that CS&S knows to categorize Google Cloud, AWS, and Azure bills to this category.

Recovery

We should ensure that we charge monthly cloud fees in a separate invoice, or if on the same invoice, in a separate line item that we can track in our accounting. The cloud fees part of it should also be categorized under the account category above.

Comparison

With this information, we can roughly do this operation on our accounting data to figure out whether we are recovering our cloud costs:

• Filter by Account == 7101 Costs Rebillable to Customers
• Group by "Cost" vs. "Revenue"
• Resample by month and sum items together
• Do Revenue by month - Cost by month.
• Make sure the resulting Net is close to 0 in each month.

Actions

• Update our documentation with the instructions in Ask Code for Science and Society to have a dedicated category and invoice type for our re-billable cloud costs #663 (comment)

[choldgraf]

Update: now have both revenue and costs in "Costs Rebillable to Customers"

CS&S have updated their accounting tables, and we now have both revenues and costs in this category. Here is a quick update and we can update the documentation in the future with it:

The category for tracking cloud costs is 7101 Costs Rebillable to Customers.

There are two types of transactions in it, differentiated by the Source field:

• Spend Money: Money we've spent on behalf of other communities that must be reimbursed (e.g. cloud costs)
• Receivable Invoice: Invoices to communities to recover our costs

Receivable Invoice has a negative amount, reflecting the fact that it "removes" our costs. We want our costs to be 0 in this category, meaning we have recovered all of our cloud (or other) costs from communities.

Our current situation

Here's a table of our current rebillable cloud costs. Thus far, it seems like we have only billed OceanHackWeek and Alabama Water Institute for cloud costs, and we currently have $20,944.33 in unrecovered cloud costs.

Rebillable cloud costs table as of Jan 31, 2023

Date	Source	Contact	Description	Invoice Number	Reference	Net	Account Code	Grants
7101 Costs Rebillable to Customers
01 Jul 2022	Spend Money	Google LLC	Google LLC - 2i2c Cloud infrastructure costs for the communities that we are serving.	Joseph Hand	Joseph Hand	762.30	7101	2i2c: General
01 Aug 2022	Spend Money	Google LLC	Google LLC - cloud costs incurred by the m2lines hub	Joseph Hand	Joseph Hand	788.41	7101	2i2c: General
03 Aug 2022	Spend Money	Amazon Web Services	Amazon Web Services - Amazon Web Services - AWS Cloud Bill - infrastructure for communities running on AWS	EXPENSIFY-96617618	EXPENSIFY-96617618	923.48	7101	2i2c: General
01 Sep 2022	Spend Money	Google LLC	Google LLC - cloud costs incurred by the m2lines hub	Chris Holdgraf	Chris Holdgraf	853.12	7101	2i2c: General
01 Oct 2022	Spend Money	Google LLC	Google LLC - cloud costs incurred by the m2lines hub	Chris Holdgraf	Chris Holdgraf	242.78	7101	2i2c: General
01 Oct 2022	Spend Money	Google LLC	Google LLC - Cloud costs for the collection of communities that 2i2c serves via our shared cluster.	Chris Holdgraf	Chris Holdgraf	2,658.48	7101	2i2c: General
01 Nov 2022	Spend Money	Google LLC	Google LLC - Google Cloud Computing cost for running JupyterHub infrastructure for several communities that we se	Chris Holdgraf	Chris Holdgraf	4,015.33	7101	2i2c: General
02 Nov 2022	Spend Money	Amazon Web Services	Amazon Web Services - Amazon Web Services cloud infrastructure bill for running JupyterHubs for the communities that we are working with.	Chris Holdgraf	Chris Holdgraf	294.30	7101	2i2c: General
21 Nov 2022	Receivable Invoice	Alabama Water Institute	Alabama Water Institute - Cloud costs July 2022	INV-0313	INV-0313	(206.76)	7101	2i2c: General
21 Nov 2022	Receivable Invoice	Alabama Water Institute	Alabama Water Institute - Cloud costs August 2022	INV-0313	INV-0313	(605.74)	7101	2i2c: General
21 Nov 2022	Receivable Invoice	Alabama Water Institute	Alabama Water Institute - Cloud costs September 2022	INV-0313	INV-0313	(598.60)	7101	2i2c: General
21 Nov 2022	Receivable Invoice	Alabama Water Institute	Alabama Water Institute - Cloud costs October 2022	INV-0313	INV-0313	(771.47)	7101	2i2c: General
01 Dec 2022	Spend Money	Google LLC	Google LLC - Google Cloud Computing cost for running JupyterHub infrastructure for several communities that we se	Chris Holdgraf	Chris Holdgraf	4,036.78	7101	2i2c: General
02 Dec 2022	Spend Money	Amazon Web Services	Amazon Web Services - Amazon Web Services cloud infrastructure bill for running JupyterHubs for the communities that we are working with.	Chris Holdgraf	Chris Holdgraf	1,763.53	7101	2i2c: General
06 Dec 2022	Receivable Invoice	OceanHackWeek	OceanHackWeek - OceanHackWeek cloud costs cloud usage for August 2021 - October 2022	INV-0333	INV-0333	(2,056.21)	7101	2i2c: General
31 Dec 2022	Spend Money	Google LLC	Google LLC - Google Cloud Computing cost for running JupyterHub infrastructure for several communities that we se	Chris Holdgraf	Chris Holdgraf	4,240.64	7101	2i2c: General
31 Dec 2022	Spend Money	Amazon Web Services	Amazon Web Services - Amazon Web Services cloud infrastructure bill for running JupyterHubs for the communities that we are working with.	Chris Holdgraf	Chris Holdgraf	1,581.90	7101	2i2c: General
19 Jan 2023	Receivable Invoice	Alabama Water Institute	Alabama Water Institute - Cloud cost Nov 2022	INV-0362	INV-0362	(664.01)	7101	2i2c: General
19 Jan 2023	Receivable Invoice	Alabama Water Institute	Alabama Water Institute - Cloud cost Dec 2022	INV-0362	INV-0362	(736.62)	7101	2i2c: General
31 Jan 2023	Spend Money	Google LLC	Google LLC - Google Cloud Computing cost for the previous month. This covers the cost of running JupyterHub infrastructure for the communities that we serve that use this platform.	Chris Holdgraf	Chris Holdgraf	4,422.69	7101	2i2c: General
Total 7101 Costs Rebillable to Customers						20,944.33

Related issues

• https://github.com/2i2c-org/meta/issues/522
• Recover our un-billed cloud costs from FY 2022 #555
• Create a process for tracking and invoicing cloud infrastructure costs #519
• Calculate cloud costs for shared hubs on a cluster on Google Cloud #499

[choldgraf]

Spoke with CS&S they're working on making invoices track this information

I spoke with Joe about this via e-mail, and here's what he said:

• Yes, we are working on making that data available.
• Invoices in Airtable are currently the Invoice object, not the line items. We’re adding a table in Airtable for the Invoice line items to account for an invoice billed to multiple account codes or project/grant codes.

So once we get access to the Invoice line items table, we should be able to associate specific costs within an invoice to their intent (e.g. cloud cost recovery)