SUMIFS used in Customer Cubes/ARR analysis in Software IB/PE
More of an excel related question for tech IB/PE:
When you get a raw file of bookings, what are the sumif functions used to
show upsell/downsell/churn etc. How is this done for variable contracts? Seperately?
Asking more regarding the excel taxonomy: What's the best way to do moving conditional sumifs for 4 scenarios (+1 bonus scenario) over a set period of time:
1. Value increases from one period to the next period (upsell)
2. Value decreases from one period to the next period (downsell)
3. Value goes to 0 from the prior period (churn).
4. Value goes from 0 to a value greater than 0 (new ARR):
5. Value goes to negative (?) - why does this happen? Does this need to be cleaned in the bookings data?
Based on the most helpful WSO content, here's how you can use SUMIFS in Excel to analyze upsell, downsell, churn, and new ARR in a raw file of bookings for tech IB/PE:
Excel Taxonomy for Moving Conditional SUMIFS
1. Value Increases from One Period to the Next Period (Upsell)
2. Value Decreases from One Period to the Next Period (Downsell)
3. Value Goes to 0 from the Prior Period (Churn)
4. Value Goes from 0 to a Value Greater Than 0 (New ARR)
5. Value Goes to Negative
Steps to Implement in Excel:
By following these steps and using the provided formulas, you can effectively analyze customer bookings data in Excel for tech IB/PE scenarios.
Sources: DCF Myth 3.2: If you don't look, its not there!, Help with Writing ER Reports for Beginners (Value Investing), PE recruiting technical questions (software specific), Framework for Understanding Results, delete delete delete delete
This is so extremely wrong lmao
You’re an Associate in IB asking this question……
pivoting from a non tech group so want to understand any nuances here + best practices
You don't use sumifs, you just make separate sections going across the tab
Raw data -> New (If(AND(t2>0,t1=0,t2-t1,0)) -> Churn (If(AND(t2=0,t1>0,t2-t1,0)) -> upsell/downsell (T2-new-churn-t1) -> upsell (if(upsell/cross sell>0,upsell/downsell,0) -> downsell(if(upsell/downsell0,upsell/downsell,0) -> Check (t2-upsell-downsell-churn-new-t1=0)
Negatives are from refunds and should be normalized but TBH unless it's material I kinda just ignore it and treat it like everything else.
Would also add there are other complexities such as cross sell, price and winbacks that will change the above but this is the most rudimentary version of a bridge.
Omnis ab voluptas eius. Possimus fugiat delectus ut et est. Ipsum dolor quia voluptas error voluptas consequuntur. Doloribus in corporis soluta dolores.
Occaecati sint praesentium nesciunt magnam iure. Dolores iure adipisci illum quo assumenda in. Ea nemo et magnam et. Eos sapiente possimus deleniti.
Quo harum voluptas ut harum ipsum est nisi. Et voluptas quo voluptatum neque id numquam similique. Cumque aut excepturi aut cum sed quasi fugit. Ea velit expedita ab in impedit rerum molestiae iure.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...