Developing a Model The number of New (Acquired) Subscribers (or the related acquisition spend) would typically be the input that drives the model (the independent variable). Following is a description of what the various columns represent: Year Month New (Acquired) Subscribers We have initially assumed that New Subscribers will grow at a constant 1% monthly 2nd Month Subscribers Calculated as number of New Subscribers previous month multiplied by the second month retention rate (1-30% churn) 3rd Month Subscribers Calculated as number of 2nd Month Subscribers previous month multiplied by the retention rate (1 - 10% churn) 4+ Month Subscribers Calculated as number of 3rd Month Subscribers previous month plus the number of 4+ Month Subscribers previous month multiplied by the retention rate (1-5% churn). Base CPA We entered Match and Index functions in column G to reference the appropriate CPA base for the related month. The Match and Index functions are presented in the next Workbook. CPA Elasticity We entered a VLOOKUP function in column H to reference the appropriate CPA Elasticity given the targeted number of customers acquired The VLOOKUP function is presented in the next Workbook. Actual CPA Multiply the Base CPA by 1 plus the CPA Elasticity Revenues Use SUMPRODUCT to mulitply each number of customers in column C through F by the average Revenues in the respective columns of row 90. Cost of Revenues The unit cost ($100) multiplied by the total of New, 2nd, 3rd and 4+ Month Subscribers Gross Margin Revenues minus Cost of Revenues SG&A Acquisition Expense (Actual CPA x number of New Subscribers) + Distribution Expense ($10 shipping and handling x total of New, 2nd, 3rd and 4+ Month Subscribers) EBIT Gross Margin minus SG&A Cumulative EBIT Previous month's Cumulative EBIT plus this month's EBIT Model Assumptions Revenue/Month Cost of Revenues/Month Churn/Month New $5 $100 Subscribers 2nd Month 3rd Month $100 $120 $100 $100 30% 10% 4+ Month $140 $100 5% Model Subscribers 2nd Month 3rd Month Income Statement Cost of Revenues Gross Margin Cumulative EBIT Month New 4+ Month Actual Revenues SG&A EBIT Year 2022 Jan 2022 Feb Mar 2022 2022 Apr May Jun Jul Base $100 $100 $100 $100 $100 $ $100 $100 $100 3,200 3,232 3,264 3,297 3,330 3,363 3,397 3,431 3,465 3,500 3,535 2022 2022 2022 2022 2022 2022 2022 CPA Elasticity 0% 0% 0% 0% 0% 0% 0% VA 0% 0% 0% 0% 0% Aug Sep Oct Nov $100 $110 $1101 Developing a Model The number of New (Acquired) Subscribers (or the related acquisition spend) would typically be the input that drives the model (the independent variable). Following is a description of what the various columns represent: Year Month New (Acquired) Subscribers We have initially assumed that New Subscribers will grow at a constant 1% monthly 2nd Month Subscribers Calculated as number of New Subscribers previous month multiplied by the second month retention rate (1-30% churn) 3rd Month Subscribers Calculated as number of 2nd Month Subscribers previous month multiplied by the retention rate (1 - 10% churn) 4+ Month Subscribers Calculated as number of 3rd Month Subscribers previous month plus the number of 4+ Month Subscribers previous month multiplied by the retention rate (1-5% churn). Base CPA We entered Match and Index functions in column G to reference the appropriate CPA base for the related month. The Match and Index functions are presented in the next Workbook. CPA Elasticity We entered a VLOOKUP function in column H to reference the appropriate CPA Elasticity given the targeted number of customers acquired The VLOOKUP function is presented in the next Workbook. Actual CPA Multiply the Base CPA by 1 plus the CPA Elasticity Revenues Use SUMPRODUCT to mulitply each number of customers in column C through F by the average Revenues in the respective columns of row 90. Cost of Revenues The unit cost ($100) multiplied by the total of New, 2nd, 3rd and 4+ Month Subscribers Gross Margin Revenues minus Cost of Revenues SG&A Acquisition Expense (Actual CPA x number of New Subscribers) + Distribution Expense ($10 shipping and handling x total of New, 2nd, 3rd and 4+ Month Subscribers) EBIT Gross Margin minus SG&A Cumulative EBIT Previous month's Cumulative EBIT plus this month's EBIT Model Assumptions Revenue/Month Cost of Revenues/Month Churn/Month New $5 $100 Subscribers 2nd Month 3rd Month $100 $120 $100 $100 30% 10% 4+ Month $140 $100 5% Model Subscribers 2nd Month 3rd Month Income Statement Cost of Revenues Gross Margin Cumulative EBIT Month New 4+ Month Actual Revenues SG&A EBIT Year 2022 Jan 2022 Feb Mar 2022 2022 Apr May Jun Jul Base $100 $100 $100 $100 $100 $ $100 $100 $100 3,200 3,232 3,264 3,297 3,330 3,363 3,397 3,431 3,465 3,500 3,535 2022 2022 2022 2022 2022 2022 2022 CPA Elasticity 0% 0% 0% 0% 0% 0% 0% VA 0% 0% 0% 0% 0% Aug Sep Oct Nov $100 $110 $1101