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.
Autem et illum dolor ex error et quasi. Pariatur modi et sed et. Et et sunt temporibus quia laborum omnis velit. Id esse quos velit quaerat.
Ea consequatur animi consectetur et. Voluptates dignissimos laudantium ut numquam aut et nesciunt. Omnis alias voluptatem ut est dolorem. A temporibus recusandae esse quis et totam.
Sit dolore enim amet vel modi cumque officiis. Nihil odio accusamus rem voluptatem at. Laudantium nostrum maxime sint ipsum. Dolorem sit odit quis rerum quis.
Eligendi et debitis officiis corrupti quisquam quam. Sunt nesciunt tenetur et voluptatem est voluptatum nihil qui. Eum tempore laudantium quia molestiae.
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...