mymeeshell
New Member
Hi forum,
I am building a dashboard for a software as a service company that has recurring revenues (MRR= monthly recurring revenue, and ARR=annual recurring revenue). I want one tab to contain all the objective data about each customer contract, and the Actual Metrics sheet to roll up all this info to show each of the following by month:
1) beginning revenue (= last months ending revenue)
2) all new revenue from contracts closed this month
3) all revenue from contracts from EXISTING customers whose previous contract lapsed but who renewed at a HIGHER rate ("Expansion"). This is the difference from the previous contract
4) all revenue from contracts from EXISTING customers whose previous contract lapsed but who renewed at a LOWER rate ("Contraction"). This is the difference from their previous contract
5) all recurring revenue we lost because a customer's contract lapsed and they did not renew at all (Churn)
I have a sample that achieves most of what I want above (though if there's any easier way to do this I'm all ears!!) but there are two formulas I don't know how to build:
1) On the Input tab (column R) I want to calculate the difference in contract values for customers who sign a second contract. That is, look for line items that have the same customer name in them, and take the ARR value for the later contract and subtract out the the ARR value from the previous contract. There could be more than two contracts (e.g., one that lapsed in 2015, one that began in 2015 and lapsed in 2016, and one that is new as of now that won't lapse til 2017. Can I use SUMIFS to do this? Add a helper column? Another formula?
2) To calculate Churn (Actual Metrics row 10), I need to calculate the negative of the sum of MRR where contract end month and year are equal to the date above (I used SUMIFS(MRR,RevenueEndMonth,C$3,RevenueEndYr,C$2)
AND where there is no other contract in the inputs tab whose start date is at or earlier than the date above. It's that second piece I don't know how to do.
Sample file attached.
I am building a dashboard for a software as a service company that has recurring revenues (MRR= monthly recurring revenue, and ARR=annual recurring revenue). I want one tab to contain all the objective data about each customer contract, and the Actual Metrics sheet to roll up all this info to show each of the following by month:
1) beginning revenue (= last months ending revenue)
2) all new revenue from contracts closed this month
3) all revenue from contracts from EXISTING customers whose previous contract lapsed but who renewed at a HIGHER rate ("Expansion"). This is the difference from the previous contract
4) all revenue from contracts from EXISTING customers whose previous contract lapsed but who renewed at a LOWER rate ("Contraction"). This is the difference from their previous contract
5) all recurring revenue we lost because a customer's contract lapsed and they did not renew at all (Churn)
I have a sample that achieves most of what I want above (though if there's any easier way to do this I'm all ears!!) but there are two formulas I don't know how to build:
1) On the Input tab (column R) I want to calculate the difference in contract values for customers who sign a second contract. That is, look for line items that have the same customer name in them, and take the ARR value for the later contract and subtract out the the ARR value from the previous contract. There could be more than two contracts (e.g., one that lapsed in 2015, one that began in 2015 and lapsed in 2016, and one that is new as of now that won't lapse til 2017. Can I use SUMIFS to do this? Add a helper column? Another formula?
2) To calculate Churn (Actual Metrics row 10), I need to calculate the negative of the sum of MRR where contract end month and year are equal to the date above (I used SUMIFS(MRR,RevenueEndMonth,C$3,RevenueEndYr,C$2)
AND where there is no other contract in the inputs tab whose start date is at or earlier than the date above. It's that second piece I don't know how to do.
Sample file attached.