**DEFINITIONS** Ensure you are familiar with the following SaaS ARR movement definitions: **Contraction ARR (Negative Movements)** - **Churn**: Customer ARR → $0 across ALL products/geographies (complete loss) - **Downsell**: ARR decreases but remains >$0 (same products/geographies maintained but reduced spend) - **Downgrade**: ARR → $0 in specific product/geography, but customer continues elsewhere or using other products **Expansion ARR (Positive Movements)** - **Upsell**: ARR increases in existing product where previous month >$0 - **Cross-sell**: ARR increases in new product/geo for existing customer where previous month =$0 **New ARR (New Customer Movements)** - **New Logo**: Brand new customer (no previous ARR anywhere in historical data) - **Returning Customer**: Previously churned customer who returns after having zero ARR for at least one period --- **OVERVIEW** **Objective**: Analyze month-over-month ARR movements and identify trends. Create a structured template layout in the user's currently selected sheet. **Approach**: Analyze the rest of the workbook to identify existing themes, formatting patterns, and data structure. Recreate the template following the established style and conventions found in the workbook. **Workflow**: Execute all steps sequentially unless genuine ambiguity requires user confirmation. Do not ask if finished - keep executing until complete. --- **STEP 1: CREATE TIMELINE INFRASTRUCTURE** **1.1 Discovery Phase** Search the workbook to identify existing timeline infrastructure: - Look for sheets named: "Timeline", "Setup", "Monthly ARR", or similar - Identify the raw customer data sheet containing period-by-period ARR values - Examine the date range, format, and fiscal period structure used - Note: The raw customer data sheet should have one row per customer, one column per period, with ARR values **1.2 Layout Timeline Infrastructure** Create timeline header rows matching the format found in the rest of the workbook. **Minimum Required Timeline Rows** (include additional rows if found in workbook): - Start date - First day of each period - End date - Last day of each period - Fiscal Year - e.g., FY2020, FY2021, 2019, 2020 - Fiscal Quarter - e.g., Q1, Q2, Q3, Q4 or 1, 2, 3, 4 - Fiscal Month - e.g., M1, M2...M12 or 1, 2...12 - Column Counter - Sequential period numbering: 1, 2, 3... **1.3 Critical Formatting Requirements** - **Date Format**: UK format (dd/mm/yyyy) for all dates - MANDATORY - **Year Format**: Plain number format 0 - NO thousand separators (e.g., 2019 not 2,019) - **Background Colors**: Full-width backgrounds extending to [END_COL] for all timeline rows - **Date Logic**: If no precedent exists, analyze raw customer data to identify the monthly range and construct timeline dynamically using date formulas (EDATE, EOMONTH, etc.) - **Override Rule**: Prompt specifications ALWAYS override template formatting when conflicts arise --- **STEP 2: CREATE ARR WATERFALL LABELS** **2.1 Label Structure** Create waterfall labels in the same column as timeline infrastructure labels. Follow formatting patterns established in the rest of the workbook. **2.2 ARR Waterfall Section** **Section Title**: ARR (in reporting currency - identify from workbook: £'000, $millions, etc.) **Standard Labels** (use if data supports full disaggregation): - BoP (Beginning of Period) - Churn - Downsell - Downgrade - Upsell - Cross-sell - New Logo - Returning Customers - EoP (End of Period) - Check (reconciliation row) **Simplified Labels** (use if customer data is aggregated): - BoP - Churn - Contraction (combined Downsell + Downgrade) - Expansion (combined Upsell + Cross-sell) - New Logo - Returning Customers - EoP - Check **2.3 Logo Waterfall Section** **Section Title**: Logos **Logo Labels** (logos cannot contract or expand - they either exist or don't): - BoP (Beginning of Period) - Churn - New Logo - Returning Customers - EoP (End of Period) - Check (reconciliation row) **CRITICAL DISTINCTION**: Unlike ARR, logo counts only track whether a customer exists or not. Therefore: - **No Contraction row**: A customer either has ARR (logo exists) or doesn't (logo churned) - there's no partial logo - **No Expansion row**: A customer can't "expand" as a logo - they're either counted or not counted - **Only movements that affect logo count**: Churn (-1), New Logo (+1), Returning Customers (+1) **2.4 Further KPIs Section** **Section Title**: Further KPIs **Required Metrics**: - Total ARR - Total Customers - GRR (Gross Revenue Retention) - NRR (Net Revenue Retention) **2.5 Date Headers in Data Columns** In the ARR, Logos, and Further KPIs title rows, populate dates across all data columns in format: **mmm-yyyy** (e.g., Jan-2020, Feb-2020) **CRITICAL**: Apply **right alignment** to these date headers to match the formatting of the data columns below them. **2.6 Add Units Column** Create a units column to indicate the measurement unit for each row: **Placement Rules**: - Position: Immediately to the right of the label column, before the first data column - Minimum column: Column C or later (NEVER place units in columns A or B) - Typical placement: Column D or E depending on label structure **To determine placement**: 1. Identify which column contains the section labels (e.g., "BoP", "Churn", etc.) 2. Place units column immediately to the right of the label column 3. Ensure units column is at least column C (if labels are in A or B, place units in C) **Currency Detection**: - Examine the customer data sheet to identify the currency symbol used for ARR values - Check the numberFormat patterns in the workbook (look for £, $, €, ¥, etc.) - Use the same currency symbol consistently throughout the template **ARR Waterfall Section Units:** - BoP through EoP: Currency symbol only (£, $, €, ¥, etc.) - Check: Leave empty **Logo Waterfall Section Units:** - BoP through EoP: # - Check: Leave empty **Further KPIs Section Units:** - Total ARR: Currency symbol - Total Customers: # - GRR: % - NRR: % **Important**: The magnitude indicator ('000, millions, etc.) appears in the section title only (e.g., "ARR (£'000)" or "ARR ($M)"), NOT in the units column. The units column shows only the base currency symbol. **2.7 Confirmation Checkpoint** **ONLY STOP AND ASK FOR CONFIRMATION IF:** - The data structure is ambiguous (cannot determine if standard vs simplified labels should be used) - Multiple conflicting formatting patterns exist in the workbook - The raw customer data sheet cannot be identified Otherwise, proceed directly to Step 3 using best judgment based on workbook patterns. --- **STEP 3: CREATE RETURNING CUSTOMER HELPER SECTION** **CRITICAL**: This step MUST be completed before populating waterfall formulas. The helper section provides full auditability for returning customer calculations. **3.1 Purpose** Create an auditable helper section in the raw customer data sheet that identifies returning customers for each period. This section mirrors the customer data structure but outputs ARR values only when a customer meets the "returning" criteria: - **Active now**: Customer has ARR > 0 in current period - **Churned last period**: Customer had ARR = 0 in previous period - **Was active before**: Customer had ARR > 0 in any period before the previous period **3.2 Placement** Place the helper section in the [CUSTOMER_SHEET]: - **Location**: 2 rows below the last customer data row - **Example**: If customer data ends at row 450, start helper section at row 452 **3.3 Structure Layout** ``` Row [HELPER_HEADER_ROW]: "RETURNING CUSTOMER TRACKER" Row [HELPER_HEADER_ROW+1]: (blank row) Row [HELPER_LABEL_ROW]: Column headers Row [HELPER_START_ROW] onwards: Helper formulas for each customer ``` **3.4 Header and Label Rows** **Header Row** ([HELPER_HEADER_ROW]): ``` segment C[HELPER_HEADER_ROW] RETURNING CUSTOMER TRACKER format C[HELPER_HEADER_ROW] bold ``` **Column Label Row** ([HELPER_LABEL_ROW] = [HELPER_HEADER_ROW]+2): ``` segment C[HELPER_LABEL_ROW] Customer ID segment D[HELPER_LABEL_ROW] Cohort segment E[HELPER_LABEL_ROW] Sign Up ARR segment F[HELPER_LABEL_ROW]:AV[HELPER_LABEL_ROW] =F10 format C[HELPER_LABEL_ROW]:[END_COL][HELPER_LABEL_ROW] bold text-#FFFFFF bg-#3D3D3D numberFormat F[HELPER_LABEL_ROW]:[END_COL][HELPER_LABEL_ROW] mmm\-yyyy ``` Replace #3D3D3D with the actual background color used in the customer data section headers. **3.5 Customer Mirror Columns** These columns copy the identifying information from the original customer data: ``` segment C[HELPER_START_ROW]:C[HELPER_END_ROW] =C11 segment D[HELPER_START_ROW]:D[HELPER_END_ROW] =D11 segment E[HELPER_START_ROW]:E[HELPER_END_ROW] =E11 ``` Where: - C11, D11, E11 = first customer row in original data - [HELPER_START_ROW] = [HELPER_LABEL_ROW] + 1 - [HELPER_END_ROW] = [HELPER_START_ROW] + (number of customers - 1) **3.6 Returning Customer Detection Formulas** **First two period columns (F and G)** - No returning customers possible: ``` segment F[HELPER_START_ROW]:F[HELPER_END_ROW] 0 segment G[HELPER_START_ROW]:G[HELPER_END_ROW] 0 ``` **Remaining period columns (H onwards)** - Active detection: ``` segment H[HELPER_START_ROW]:H[HELPER_END_ROW] =IF(AND(H11>0,G11=0,SUM($F11:F11)>0),H11,0) segment I[HELPER_START_ROW]:I[HELPER_END_ROW] =IF(AND(I11>0,H11=0,SUM($F11:G11)>0),I11,0) segment J[HELPER_START_ROW]:J[HELPER_END_ROW] =IF(AND(J11>0,I11=0,SUM($F11:H11)>0),J11,0) ... continue for each period through [END_COL] ``` **Formula Logic Breakdown**: - `H11>0`: Customer has ARR in current period (active now) - `G11=0`: Customer had zero ARR in previous period (churned last period) - `SUM($F11:F11)>0`: Customer had ARR in any prior period before G (was active before) - **Result**: If all conditions TRUE → return current period ARR; else return 0 **Key Formula Features**: - `$F11` - Absolute column reference ensures history always starts from first period - `F11` - Relative end reference expands as formula is copied across (F, then F:G, then F:H, etc.) - `11` - Relative row reference adjusts for each customer when copied down **3.7 Number Formats for Helper Section** ``` numberFormat D[HELPER_START_ROW]:D[HELPER_END_ROW] mmm\-yyyy numberFormat E[HELPER_START_ROW]:E[HELPER_END_ROW] \$#,##0_);\(\$#,##0\) numberFormat F[HELPER_START_ROW]:[END_COL][HELPER_END_ROW] "£"#,##0_);\("£"#,##0\) ``` Adjust currency symbols to match the rest of the workbook. **3.8 Implementation Example** For a workbook with 440 customers (rows 11-450) and data through column AV: ``` segment C452 RETURNING CUSTOMER TRACKER format C452 bold segment C454 Customer ID segment D454 Cohort segment E454 Sign Up ARR segment F454:AV454 =F10 format C454:AV454 bold text-#FFFFFF bg-#3D3D3D numberFormat F454:AV454 mmm\-yyyy segment C455:C894 =C11 segment D455:D894 =D11 segment E455:E894 =E11 segment F455:F894 0 segment G455:G894 0 segment H455:H894 =IF(AND(H11>0,G11=0,SUM($F11:F11)>0),H11,0) segment I455:I894 =IF(AND(I11>0,H11=0,SUM($F11:G11)>0),I11,0) ... (continue through AV) numberFormat D455:D894 mmm\-yyyy numberFormat E455:E894 \$#,##0_);\(\$#,##0\) numberFormat F455:AV894 "£"#,##0_);\("£"#,##0\) ``` --- **STEP 4: POPULATE ARR AND LOGO WATERFALL DATA** **4.1 Prerequisites** - Timeline infrastructure is set up (date/period headers) - ARR and Logo waterfall labels are in place - Returning customer helper section exists (Step 3 completed) - Raw customer data sheet identified with: - One row per customer - One column per time period - ARR values tracking customer revenue over time **4.2 ARR Waterfall Formulas** **First Period Column (No Prior Period Comparison)** **BoP through Returning**: Leave empty (no prior period exists) **EoP**: ``` =SUM([CUSTOMER_SHEET]![FIRST_PERIOD_COL]$[FIRST_ROW]:$[LAST_ROW]) ``` Sums all customer ARR values for the first period. **Subsequent Period Columns** **BoP**: ``` =[PREVIOUS_PERIOD_EOP] ``` Links to previous period's EoP value. **Churn**: ``` =SUMPRODUCT(([CUSTOMER_SHEET]![PRIOR_COL]$[FIRST_ROW]:$[LAST_ROW]>0)*([CUSTOMER_SHEET]![CURRENT_COL]$[FIRST_ROW]:$[LAST_ROW]=0)*([CUSTOMER_SHEET]![CURRENT_COL]$[FIRST_ROW]:$[LAST_ROW]-[CUSTOMER_SHEET]![PRIOR_COL]$[FIRST_ROW]:$[LAST_ROW])) ``` Logic: Had ARR in prior period AND zero in current period → capture negative change. **Contraction**: ``` =SUMPRODUCT(([CUSTOMER_SHEET]![PRIOR_COL]$[FIRST_ROW]:$[LAST_ROW]>0)*([CUSTOMER_SHEET]![CURRENT_COL]$[FIRST_ROW]:$[LAST_ROW]>0)*([CUSTOMER_SHEET]![CURRENT_COL]$[FIRST_ROW]:$[LAST_ROW]<[CUSTOMER_SHEET]![PRIOR_COL]$[FIRST_ROW]:$[LAST_ROW])*([CUSTOMER_SHEET]![CURRENT_COL]$[FIRST_ROW]:$[LAST_ROW]-[CUSTOMER_SHEET]![PRIOR_COL]$[FIRST_ROW]:$[LAST_ROW])) ``` Logic: Had ARR in both periods AND current < prior → capture negative change. **Expansion**: ``` =SUMPRODUCT(([CUSTOMER_SHEET]![PRIOR_COL]$[FIRST_ROW]:$[LAST_ROW]>0)*([CUSTOMER_SHEET]![CURRENT_COL]$[FIRST_ROW]:$[LAST_ROW]>[CUSTOMER_SHEET]![PRIOR_COL]$[FIRST_ROW]:$[LAST_ROW])*([CUSTOMER_SHEET]![CURRENT_COL]$[FIRST_ROW]:$[LAST_ROW]-[CUSTOMER_SHEET]![PRIOR_COL]$[FIRST_ROW]:$[LAST_ROW])) ``` Logic: Had ARR in prior period AND current > prior → capture positive change. **New Logo**: ``` =SUMPRODUCT(([CUSTOMER_SHEET]![PRIOR_COL]$[FIRST_ROW]:$[LAST_ROW]=0)*([CUSTOMER_SHEET]![CURRENT_COL]$[FIRST_ROW]:$[LAST_ROW]>0)*[CUSTOMER_SHEET]![CURRENT_COL]$[FIRST_ROW]:$[LAST_ROW])-[RETURNING_CELL] ``` Logic: Zero in prior period AND positive in current period → capture new ARR, THEN subtract returning customers to avoid double-counting. **CRITICAL**: The subtraction of [RETURNING_CELL] is essential. Without it, returning customers would be counted twice (once in New Logo, once in Returning Customers). **Returning Customers**: ``` =SUM([CUSTOMER_SHEET]![HELPER_CURRENT_COL][HELPER_START_ROW]:[HELPER_CURRENT_COL][HELPER_END_ROW]) ``` Logic: Sum all ARR values from the helper section for this period. Only customers meeting the "returning" criteria will have non-zero values. Example: For column U (Apr-2020): ``` =SUM('Structured ARR Data'!U455:U894) ``` **EoP**: ``` =SUM([BOP_CELL]:[RETURNING_CELL]) ``` Sum of all waterfall components in the column. **Check**: ``` =[EOP_CELL]-[TOTAL_ARR_CELL] ``` Reconciliation: EoP should match Total ARR for that period. Should equal zero. **4.3 Logo Waterfall Formulas** **CRITICAL**: Logo counts only track customer existence. A customer either has ARR (counted as 1 logo) or doesn't (counted as 0). Therefore, there are NO Contraction or Expansion rows in the logo waterfall - only Churn, New Logo, and Returning Customers affect logo count. **First Period Column** **BoP through Returning**: Leave empty **EoP**: ``` =SUMPRODUCT(([CUSTOMER_SHEET]![FIRST_COL]$[FIRST_ROW]:$[LAST_ROW]>0)*1) ``` Counts customers with positive ARR. **Subsequent Period Columns** **BoP**: ``` =[PREVIOUS_PERIOD_EOP] ``` **Churn**: ``` =SUMPRODUCT(([CUSTOMER_SHEET]![PRIOR_COL]$[FIRST_ROW]:$[LAST_ROW]>0)*([CUSTOMER_SHEET]![CURRENT_COL]$[FIRST_ROW]:$[LAST_ROW]=0)*-1) ``` Logic: Had ARR in prior, zero in current → count as -1 (churned customer). **New Logo**: ``` =(SUMPRODUCT(([CUSTOMER_SHEET]![PRIOR_COL]$[FIRST_ROW]:$[LAST_ROW]=0)*([CUSTOMER_SHEET]![CURRENT_COL]$[FIRST_ROW]:$[LAST_ROW]>0)*1)-[RETURNING_CELL]) ``` Logic: Zero in prior, positive in current → count as +1, THEN subtract returning customer count to avoid double-counting. **CRITICAL**: The subtraction of [RETURNING_CELL] is essential for the same reason as in ARR calculation. **Returning Customers**: ``` =COUNTIF([CUSTOMER_SHEET]![HELPER_CURRENT_COL][HELPER_START_ROW]:[HELPER_CURRENT_COL][HELPER_END_ROW],">0") ``` Logic: Count non-zero values in the helper section for this period. Example: For column U (Apr-2020): ``` =COUNTIF('Structured ARR Data'!U455:U894,">0") ``` **EoP**: ``` =SUM([BOP_CELL]:[RETURNING_CELL]) ``` **Check**: ``` =[EOP_CELL]-[TOTAL_CUSTOMERS_CELL] ``` Reconciliation: EoP should match Total Customers for that period. Should equal zero. **4.4 Total ARR and Total Customers** These appear in the "Further KPIs" section and serve as reconciliation points. **Total ARR**: ``` =SUM([CUSTOMER_SHEET]![CURRENT_COL]$[FIRST_ROW]:$[LAST_ROW]) ``` Direct sum of all customer ARR in the period. **Total Customers**: ``` =SUMPRODUCT(([CUSTOMER_SHEET]![CURRENT_COL]$[FIRST_ROW]:$[LAST_ROW]>0)*1) ``` Count of customers with ARR > 0 in the period. **4.5 GRR and NRR Metrics** Starting from **Second Period Column** (first period with prior comparison): **GRR (Gross Revenue Retention)**: ``` =([ARR_BOP]+[ARR_CHURN]+[ARR_CONTRACTION])/[ARR_BOP] ``` - **Formula**: (BoP + Churn + Contraction) / BoP - **Note**: Churn and Contraction are stored as negative values. - **Interpretation**: Measures revenue retained from existing customers (excluding expansion). **NRR (Net Revenue Retention)**: ``` =([ARR_BOP]+[ARR_CHURN]+[ARR_CONTRACTION]+[ARR_EXPANSION])/[ARR_BOP] ``` - **Formula**: (BoP + Churn + Contraction + Expansion) / BoP - **Interpretation**: Measures revenue retained plus expansion from existing customers. **4.6 Number Formats** **ARR Values**: ``` #,##0_);[Red]\(#,##0\);"--"_);@ ``` Shows: 90,292 for positive, (90,292) in red for negative, -- for zero (with proper alignment). **Logo Counts**: ``` #,##0_);[Red]\(#,##0\);"--"_);@ ``` Shows: 5 for positive, (5) in red for negative, -- for zero (with proper alignment). **GRR & NRR Percentages**: ``` 0.0%_);[Red]\(0.0%\);"--"_);@ ``` Shows: 99.5% for positive, (99.5%) in red for negative, -- for zero (with proper alignment). **4.7 Key Implementation Notes** - **Use SUMPRODUCT**: Use SUMPRODUCT for all calculations (not SUMIFS) - provides better logical condition handling for complex array operations. - **Operational numbers**: The multipliers 0, 1, -1 in formulas are operational logic for conditions/counting (NOT hardcoded assumptions - these are acceptable). - **Mixed references**: Use $ on rows (e.g., $[FIRST_ROW]:$[LAST_ROW]) to lock customer range when dragging formulas across periods. - **Negative values**: Churn and Contraction formulas produce negative values by design (they represent decreases). - **Dynamic ranges**: Adjust customer data range based on actual extent of customer rows in data sheet. - **Helper section dependency**: New Logo and Returning Customers formulas MUST reference the helper section created in Step 3. Do not use placeholder values. - **Auditability**: Users can click any Returning Customers cell and trace through to the helper section to see exactly which customers contributed to that value. - **Logo waterfall structure**: The logo waterfall contains ONLY BoP, Churn, New Logo, Returning Customers, EoP, and Check. Do NOT include Contraction or Expansion rows for logos - these concepts don't apply to customer counts. --- **STEP 5: ADD END OF SHEET NAVIGATION MARKERS** **5.1 Purpose** Create invisible navigation markers to help users easily select and hide unused rows/columns. These markers are functional, not presentational, and should blend seamlessly with section header formatting. **5.2 Placement** Place the marker row **10 rows below** the last row containing any content or formulas. **5.3 Marker Row Format** Extend the row across all columns from A to [END_COL]: - **Column A**: Place "x" (lowercase) - **Column C** (or middle label column): Place text "End of sheet" - **Column [END_COL]**: Place "x" (lowercase) - **All other columns**: Leave empty **5.4 Formatting Requirements** **Background and Text Style**: Match the section header row formatting exactly (typically bold text-#FFFFFF bg-#3D3D3D or similar). - Extend formatting across ALL columns from A to [END_COL], including label columns A and B - This makes the row visually identical to section headers **Text Color for "x" markers**: Set text color to match the background color (e.g., text-#3D3D3D if background is bg-#3D3D3D). - Makes "x" invisible but navigable via Ctrl+F search **Text Color for "End of sheet"**: Keep the same as section header text (typically white: text-#FFFFFF). **Purpose**: The invisible "x" markers allow users to search (Ctrl+F for "x"), jump to first/last column markers, then select and hide all unused rows/columns. The row blends seamlessly as if it were another section header. **5.5 Implementation Pattern** **Step 1**: Identify section header formatting by examining the ARR or Logo section header rows - Note the exact format pattern (typically: bold text-#FFFFFF bg-#3D3D3D) - Note the background color hex code (e.g., #3D3D3D) **Step 2**: Apply the pattern to the marker row ``` segment A[MARKER_ROW] x segment C[MARKER_ROW] End of sheet segment [END_COL][MARKER_ROW] x format A[MARKER_ROW]:[END_COL][MARKER_ROW] bold text-#FFFFFF bg-#3D3D3D format A[MARKER_ROW],[END_COL][MARKER_ROW] text-#3D3D3D ``` Replace values: - [MARKER_ROW] = Last content row + 10 - [END_COL] = Last data column (e.g., AV) - #3D3D3D = Background color from section headers - #FFFFFF = Text color from section headers Note: The "x" characters are intentionally invisible (background-colored text on same background) for clean presentation while enabling quick navigation. --- **INTERNAL VALIDATION CHECKS** Perform these checks internally - DO NOT create check sections in the workbook or report check status to the user. Simply ensure compliance: ✓ All dates are in UK format (dd/mm/yyyy) ✓ Currency symbols match the rest of the workbook ✓ Number formats are correctly applied with proper alignment ✓ ARR waterfall Check rows reconcile to zero ✓ Logo waterfall Check rows reconcile to zero ✓ Date headers in ARR/Logos/KPI sections are right-aligned ✓ End of sheet navigation markers are placed 10 rows below last content ✓ End of sheet marker row formatting matches section header formatting exactly ✓ Returning customer helper section exists in raw data sheet ✓ Helper section formulas correctly identify returning customers ✓ Waterfall Returning Customers rows link to helper section (not hardcoded) ✓ New Logo formulas subtract Returning Customers to avoid double-counting ✓ Helper section formatting matches main data section ✓ Logo waterfall does NOT contain Contraction or Expansion rows ✓ Units column is present and correctly populated --- **PLACEHOLDER KEY** Use these placeholders when writing formulas - replace with actual references during execution: **Customer Data References** - [CUSTOMER_SHEET] = Name of sheet containing raw customer ARR data - [FIRST_PERIOD_COL] = First data period column (e.g., F) - [CURRENT_COL] = Current period being calculated - [PRIOR_COL] = Previous period column - [FIRST_ROW]:[LAST_ROW] = Range of customer data rows (e.g., 11:450) **Helper Section References** - [HELPER_HEADER_ROW] = Row number for "RETURNING CUSTOMER TRACKER" header - [HELPER_LABEL_ROW] = Row number for helper section column headers ([HELPER_HEADER_ROW] + 2) - [HELPER_START_ROW] = First row of helper formulas ([HELPER_LABEL_ROW] + 1) - [HELPER_END_ROW] = Last row of helper formulas ([HELPER_START_ROW] + number of customers - 1) - [HELPER_CURRENT_COL] = Current period column in helper section (e.g., U) - [CUSTOMER_ROW] = Row number in original customer data (changes for each customer) **Waterfall References** - [ARR_BOP], [ARR_CHURN], etc. = Cell references to respective ARR waterfall line items - [PREVIOUS_PERIOD_EOP] = EoP cell from the prior period column - [RETURNING_CELL] = Cell reference to Returning Customers in current waterfall column - [TOTAL_ARR_CELL] = Cell reference to Total ARR in Further KPIs section - [TOTAL_CUSTOMERS_CELL] = Cell reference to Total Customers in Further KPIs section **Sheet Structure References** - [END_COL] = Last column with data or formatting (e.g., AV) - [END_ROW] = Last row with data or formatting - [LABEL_COL] = First label column (typically column A or C) - [MIDDLE_COL] = Column for "End of sheet" text (typically column C) - [MARKER_ROW] = Row number for end of sheet marker (last content row + 10) - [CURRENCY_SYMBOL] = Currency symbol identified from customer data sheet (e.g., £, $, €) --- **EXECUTION ORDER SUMMARY** 1. Create timeline infrastructure (Step 1) 2. Create ARR/Logo/KPI labels (Step 2) - Remember: Logo section has NO Contraction/Expansion rows 3. Add units column (Step 2.6) 4. Stop ONLY if genuine ambiguity requires user input (Step 2.7) 5. Create returning customer helper section in raw data sheet (Step 3) - MUST be completed before Step 4 6. Populate all waterfall formulas linking to helper section (Step 4) 7. Apply number formats (Step 4.6) 8. Add end of sheet navigation markers matching section header formatting (Step 5) 9. Verify internal validation checks silently (do not report to user) --- **CRITICAL REMINDERS** - **Returning Customers MUST be auditable**: Always create the helper section in Step 3. Never use hardcoded values or placeholder zeros. - **Avoid double-counting**: New Logo formulas MUST subtract Returning Customers. The formula pattern is: ``` (SUMPRODUCT logic for new customers) - [RETURNING_CELL] ``` - **Helper section formulas**: Use the exact pattern from Step 3.6. The formula must check: 1. Active now (current > 0) 2. Churned last period (previous = 0) 3. Was active before (sum of all prior periods > 0) - **Timeline consistency**: Ensure helper section timeline headers mirror the main data section headers exactly. - **Execution order**: Step 3 (helper section) MUST be completed before Step 4 (waterfall formulas), as the formulas depend on the helper section range. - **Logo waterfall structure**: The logo waterfall is SIMPLER than the ARR waterfall: - **Include**: BoP, Churn, New Logo, Returning Customers, EoP, Check - **Exclude**: Contraction, Expansion (these don't apply to logo counts) - **Reason**: A customer logo either exists (1) or doesn't (0) - there's no partial logo or logo expansion - **Units column**: Must be placed immediately to the right of labels (column C or later, never A or B), showing currency symbol for ARR rows, # for logo counts, and % for retention metrics.