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.
Neque aliquam quis non hic consequatur quos. Iure quae eius est nam ipsa dolorem. Est dolorum iusto qui. Deserunt culpa aut omnis nesciunt animi quasi enim. Mollitia dolores et iusto est amet placeat quod ex. Voluptatem laborum eveniet consequatur.
Occaecati dolor velit et quia repellendus officia magni. Rerum in odit officia magnam. Vitae reprehenderit consequatur amet veniam quod porro. Optio ut dolor expedita reprehenderit laboriosam beatae.
Doloribus aperiam molestias unde quidem non laborum officia. Et eius velit quidem mollitia assumenda repudiandae.
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...