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.
Eveniet molestiae expedita similique quae. Voluptates voluptatum ducimus sequi maiores iste placeat. Autem ipsa eos porro accusantium. Soluta autem aut voluptatem ut.
Rerum tempora quisquam quae architecto delectus. Ullam aut similique doloribus possimus sunt ea ut. Occaecati omnis officia animi aspernatur aspernatur voluptas.
Assumenda quas dicta cupiditate quasi est sed accusamus sint. Corrupti id quasi est nihil corrupti delectus et. Sint at vero modi omnis omnis. Nihil soluta debitis reprehenderit totam aut. Aut eum nihil sit dolores odio vel.
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...