Hello all,
I am looking for a formula(s) that can translate data in the customer subscription log into the monthly recurring revenue (MRR) schedule. The MRR schedule represents the desired outcome, but is all hardcoded. What formulas can I use? Grateful in advance for any pointers in the right direction!
Additional context/definitions:
The log tracks customers on a monthly subscription and their payment activity each month. The monthly recurring revenue (MRR) schedule breaks revenue into different categories, listed and defined below. MRR is just a fancy definition for what customers pay each month.
Retained MRR: Customers that pay the same amount as the month prior. Ex: Customer #1 provides retained MRR after Jan-22.
New Sales MRR: New customers, paid nothing in the prior month. Ex: Customer #2 is new sales MRR in Feb-22.
Expansion MRR: The positive difference from an existing customer's payment from the prior month. Ex: Customer #5 upgrades their subscription in Jun-22 and Sep-22.
Contraction MRR: The negative difference from an existing customer's payment from the prior month. Ex: Customer #4 downgrades their subscription in Jun-22 and Aug-22.
Churned MRR: Customers that paid in the prior month, but not in the current month. Ex: Customer #3 canceled their subscription at the end of May-22, making them churned as of Jun-22.
I am looking for a formula(s) that can translate data in the customer subscription log into the monthly recurring revenue (MRR) schedule. The MRR schedule represents the desired outcome, but is all hardcoded. What formulas can I use? Grateful in advance for any pointers in the right direction!
Additional context/definitions:
The log tracks customers on a monthly subscription and their payment activity each month. The monthly recurring revenue (MRR) schedule breaks revenue into different categories, listed and defined below. MRR is just a fancy definition for what customers pay each month.
Retained MRR: Customers that pay the same amount as the month prior. Ex: Customer #1 provides retained MRR after Jan-22.
New Sales MRR: New customers, paid nothing in the prior month. Ex: Customer #2 is new sales MRR in Feb-22.
Expansion MRR: The positive difference from an existing customer's payment from the prior month. Ex: Customer #5 upgrades their subscription in Jun-22 and Sep-22.
Contraction MRR: The negative difference from an existing customer's payment from the prior month. Ex: Customer #4 downgrades their subscription in Jun-22 and Aug-22.
Churned MRR: Customers that paid in the prior month, but not in the current month. Ex: Customer #3 canceled their subscription at the end of May-22, making them churned as of Jun-22.
| MRR.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | Customer Subscription Log | ||||||||||||||
| 2 | Jan 22 | Feb 22 | Mar 22 | Apr 22 | May 22 | Jun 22 | Jul 22 | Aug 22 | Sep 22 | Oct 22 | Nov 22 | Dec 22 | |||
| 3 | Customer #1 | 500.00 | 500.00 | 500.00 | 500.00 | 500.00 | 500.00 | 500.00 | 500.00 | 500.00 | 500.00 | 500.00 | 500.00 | ||
| 4 | Customer #2 | 250.00 | 250.00 | 250.00 | 250.00 | 250.00 | 250.00 | 250.00 | 250.00 | 250.00 | 250.00 | 250.00 | |||
| 5 | Customer #3 | 300.00 | 300.00 | 300.00 | 300.00 | ||||||||||
| 6 | Customer #4 | 400.00 | 400.00 | 300.00 | 300.00 | 200.00 | 200.00 | 100.00 | 100.00 | 100.00 | |||||
| 7 | Customer #5 | 100.00 | 100.00 | 100.00 | 400.00 | 400.00 | 400.00 | 600.00 | 600.00 | 600.00 | 600.00 | ||||
| 8 | |||||||||||||||
| 9 | Monthly Recurring Revenue (MRR) Schedule | ||||||||||||||
| 10 | Jan 22 | Feb 22 | Mar 22 | Apr 22 | May 22 | Jun 22 | Jul 22 | Aug 22 | Sep 22 | Oct 22 | Nov 22 | Dec 22 | |||
| 11 | Retained MRR | $0.00 | $500.00 | $1,050.00 | $1,150.00 | $1,550.00 | $1,150.00 | $1,450.00 | $1,350.00 | $1,350.00 | $1,450.00 | $1,450.00 | $1,450.00 | ||
| 12 | New Sales MRR | $500.00 | $550.00 | $100.00 | $400.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | ||
| 13 | Expansion MRR | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $300.00 | $0.00 | $0.00 | $200.00 | $0.00 | $0.00 | $0.00 | ||
| 14 | Contraction MRR | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | -$100.00 | $0.00 | -$100.00 | $0.00 | -$100.00 | $0.00 | $0.00 | ||
| 15 | Churned MRR | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | -$300.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | ||
| 16 | Total MRR | $500.00 | $1,050.00 | $1,150.00 | $1,550.00 | $1,550.00 | $1,450.00 | $1,450.00 | $1,350.00 | $1,550.00 | $1,450.00 | $1,450.00 | $1,450.00 | ||
Customer Log | |||||||||||||||
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| B3:M7 | Whole number | >=0 |
