Software Biz Analysis for Investing: How to break down customer data based on multiple criteria and show on new tabs

[k3yn0t3]

Hi there. I have a sheet with ~5k rows of customer data for a SW company (see sample below). I want to break it down so that I can analyze things like recurring v. non-recurring revenue, total subscription revenue by product type by customer... Does anyone have any thoughts on how to do this?

Here’s what I’m thinking for layout.

Make four tabs, one tab for each of the four product types

On each Product tab
Columns = Months, then Quarters to the right, then Years to right of that
Rows = Customers

Note: for each sub-section below, will show all Customer IDs hardcoded, have a total row at bottom

Top section of each Product each tab
Three separate sections on each tab for each type of revenue:
- Subscription Software (Recurring)
- Perpetual Software (Non-Recurring)
- One-Time (Non- Recurring)

For the subscription revenue, I need to do an ARR snowball with accompanying metrics (new logos, churn, renewals, upsells, downsells,etc.). Do you agree that I should do all that below on same tab, then have summary metrics for Product Revenue, at bottom, then have all four products’ MRR and ARR roll up into a summary tab that is “RECURRING REVENUE SUMMARY”?

in middle of Product Tab:
Three additional sections that show summaries for revenue by type and total revenue
- Recurring Product Revenue Subtotal
- Non-Recurring Product Revenue Subtotal
- Total Product Revenue

Bottom of Product Tab
Do the ARR snowball & accompanying metrics
- New Logos
- Churn
- etc.

Sample Data

	Revenue Type	Software vs Non-Software	Recurring vs One-Time	Month	Billings Amount (per mo)	Product Type
Customer 10	One-Time Training	Non-Software	One-Time	3/1/2020​	$12.50​	Product 1
Customer 10	One-Time Training	Non-Software	One-Time	4/1/2020​	$12.50​	Product 1
Customer 10	One-Time Training	Non-Software	One-Time	5/1/2020​	$12.50​	Product 1
Customer 10	One-Time Training	Non-Software	One-Time	6/1/2020​	$12.50​	Product 1
Customer 10	One-Time Training	Non-Software	One-Time	7/1/2020​	$12.50​	Product 1
Customer 10	One-Time Training	Non-Software	One-Time	8/1/2020​	$12.50​	Product 1
Customer 10	One-Time Training	Non-Software	One-Time	9/1/2020​	$12.50​	Product 1
Customer 10	One-Time Training	Non-Software	One-Time	10/1/2020​	$12.50​	Product 1
Customer 10	One-Time Training	Non-Software	One-Time	11/1/2020​	$12.50​	Product 1
Customer 10	One-Time Training	Non-Software	One-Time	12/1/2020​	$12.50​	Product 1
Customer 10	Subscription Software	Software	Recurring	3/1/2020​	$12.50​	Product 1
Customer 10	Subscription Software	Software	Recurring	4/1/2020​	$12.50​	Product 1
Customer 10	Subscription Software	Software	Recurring	5/1/2020​	$12.50​	Product 1
Customer 11	Subscription Software	Software	Recurring	6/1/2020​	$12.50​	Product 1
Customer 11	Subscription Software	Software	Recurring	7/1/2020​	$12.50​	Product 1
Customer 11	Subscription Software	Software	Recurring	8/1/2020​	$12.50​	Product 1
Customer 11	Subscription Software	Software	Recurring	9/1/2020​	$12.50​	Product 2
Customer 11	Subscription Software	Software	Recurring	10/1/2020​	$12.50​	Product 2
Customer 11	Subscription Software	Software	Recurring	11/1/2020​	$12.50​	Product 3
Customer 11	Subscription Software	Software	Recurring	12/1/2020​	$12.50​	Product 4
Customer 11	One-Time Training	Non-Software	One-Time	9/1/2020​	$0.39​	Product 1
Customer 11	One-Time Training	Non-Software	One-Time	10/1/2020​	$0.39​	Product 1
Customer 11	One-Time Training	Non-Software	One-Time	11/1/2020​	$0.39​	Product 1
Customer 11	One-Time Training	Non-Software	One-Time	12/1/2020​	$0.39​	Product 1
Customer 11	One-Time Training	Non-Software	One-Time	9/1/2020​	$0.49​	Product 1
Customer 11	One-Time Training	Non-Software	One-Time	10/1/2020​	$0.49​	Product 1
Customer 11	One-Time Training	Non-Software	One-Time	11/1/2020​	$0.49​	Product 1
Customer 100	One-Time Training	Non-Software	One-Time	12/1/2020​	$0.49​	Product 1
Customer 100	One-Time Training	Non-Software	One-Time	9/1/2020​	$2.10​	Product 1
Customer 100	One-Time Training	Non-Software	One-Time	10/1/2020​	$2.10​	Product 1

 

[k3yn0t3]

I made a new tab for product one and am starting by pulling in "Subscription Revenue" from the master sheet based on:
- Customer Name
- Date (month)
- Type of Revenue (in this case, if it is Subscription Revenue)

However, two problems with this formula:
1/ I can't copy it across, bc it's an array
2/ Returns first instance it finds in data set v. summing all cells in the array)
Should i be doing SUMIFS / SUMIF(AND..) statements?
Formula
=+INDEX('Customer Master'!$F$2:$F$5000,MATCH(1,($B7='Customer Master'!$A$2:$A$5000)*('Product 1'!E$4='Customer Master'!$E$2:$E$5000)*('Product 1'!$B$3='Customer Master'!$B$2:$B$5000),0))

Subscription Software
#	Jan-18	Feb-18
Customer 1
Customer 2
Customer 3	10.591634​	10.59163​
Customer 4	#N/A​	#N/A​
Customer 5
Customer 6