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

choldgraf commented 1 year ago

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 https://github.com/2i2c-org/team-compass/issues/663#issuecomment-1482921455

choldgraf commented 1 year ago

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

choldgraf commented 1 year ago

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)

2i2c-org / team-compass