Guided project · 6 steps Cellwise Plus
Recurring cleaning revenue in Excel
Turn a client list into visits per year, monthly recurring revenue and how much rides on your biggest clients.
You will use: IFS, ROUND, SUM, SUMIF, MAX
Starting data
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Client | Frequency | Price per visit | Visits per year | Annual revenue | Monthly revenue | Monthly recurring revenue | |
| 2 | Priya | Weekly | 230 | Share from weekly clients | ||||
| 3 | Marcus | Biweekly | 185 | Largest client's share | ||||
| 4 | Elena | Biweekly | 290 | |||||
| 5 | Tom | Monthly | 165 | |||||
| 6 | Grace | Weekly | 255 | |||||
| 7 | Office suite 2B | Weekly | 340 | |||||
| 8 | Hana | Biweekly | 205 | |||||
| 9 | Leo | Monthly | 140 |
Build it step by step
Step 1
Visits per year depend on frequency: 52 for Weekly, 26 for Biweekly, 12 for Monthly. Write it with IFS in D2 and fill to D9.
Step 2
Annual revenue for each client: price per visit times visits per year. Write it in E2 and fill to E9.
Step 3
Monthly revenue is the annual figure divided by 12, rounded to cents. Write it in F2 and fill to F9.
Step 4
Total monthly recurring revenue in I1.
Step 5
What share of it comes from weekly clients? SUMIF their monthly revenue and divide by I1. Write it in I2.
Step 6
How much rides on one client? Divide the largest monthly revenue by the total, in I3.