Revenue projections

Revenue projections#

This document shows 2i2c’s historical revenue data by contract, and predicts 2i2c’s monthly income along with its costs using data from our Leads AirTable, which also pulls in data from our Contracts AirTable.

When built via Jupyter Book, all leads are anonymized. If you want de-anonymized leads, run the notebook locally.

To run this notebook locally

To see the visualizations locally, follow these steps:

  1. Get an API key for AirTable (see our team compass docs on AirTable) and store it in an environment variable called AIRTABLE_AP_KEY.

  2. Download the latest data:

    python book/scripts/download_airtable_data.py

  3. Run this notebook from top to bottom.

There are several important fields in both Leads and Contracts, they’re described below:

  • Start Date / End Date: The starting and ending date of a contract.

  • Amount: The total budget amount in the grant.

  • Amount for 2i2c: The budget that is available to 2i2c (if <100% of the amount total)

  • CSS %: The % that CS&S will take for their indirect costs.

How numbers are prioritized

This notebook tries to use the most accurate data that we’ve got. For example, contracts are usually more accurate than leads. For data about dates and $$ amounts, here’s the logic we follow:

  1. 2i2c Available $$. If we have manually specified an amount for 2i2c, use this above all else.

  2. Amount (from Contract). If we have a contract with CS&S for this Lead, use this.

  3. Amount (from Leads). If we have no contract, use our Leads airtable for a best estimate.

Costs#

Costs are manually calculated for now from this Google Sheet. Monthly costs are calculated from the table below. We’ll define a baseline cost as the average over the last three months of this table.

  • Assumed annual costs (no FSP): $1,855,416

  • Assumed monthly costs (no FSP): $154,618

Leads and contracts#

Leads are a precursor to contracts. Each has a % probability of success, and revenue is generally weighted by this chance. Leads follow this lifecycle:

../_images/leads_lifecycle.png

The leads lifecycle, see our Leads AirTable for the Leads data.#

Contracts are legal agreements with $$ attached to them, and their revenue is treated as 100% reliable.

We include contracts data with our leads: For any lead that has a contract, it is linked to a record in Contracts. Our Leads AirTable has several linked fields from these records, so we have the relevant contract information for each lead.

Sample leads from our Leads AirTable.
  Name Status Engagement Type % success Contract Type Start Date End Date Amount for 2i2c
0 Lead 0 Awarded - Active Partnership 1.000000 Contract 2022-12-01 2024-09-30 332775.000000
1 Lead 1 Awarded - Complete Hub: Research 1.000000 Contract 2023-06-01 2024-05-31 23587.500000
2 Lead 2 Awarded - Complete Hub: Research 1.000000 Contract 2023-06-01 2023-09-30 12537.500000
3 Lead 3 Awarded - Active Hub: Research 1.000000 Contract 2023-05-30 2023-08-18 5100.000000
4 Lead 4 Awarded - Complete Partnership 1.000000 Contract 2023-09-11 2024-02-28 59500.000000

Expected total amounts#

We add a column for the weighted total amount to account for the fact that the lead may not come through. This helps us calculate the total expected amount of revenue:

total expected amount = lead total amounts * probability of each lead

or if you’re a mathy person:

\[E \left[ \sum(leads) \right] = \sum_{1}^{n\_leads} lead\_total * lead\_probability = \sum \left( E[leads] \right) \]

Amortize leads across months#

For each lead, we spread the total amount into equal monthly amounts over the total lifetime of the contract. If it’s a lead we use anticipated start/stop/amount. If it’s a contract we use the contract values.

Historical revenue and costs#

First we show our historical revenue and costs to understand where each has trended over time. This only includes leads that have contracts, no “potential” leads are included.

We display types of revenue in different colors. Hover over each section to see more information about it.