80.2K

Verified Solution

Link Copied!

Developing a Model The number of New Acquired) Subscribers for 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 a 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 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 SGRA Cumulative EBIT Previous month's Cumulative EBIT plus this month's EBIT Model Assumptions New Revenue/Month Cost of Revenue/Month Churry Month $5 $100 Subscribers 2nd Month 3rd Month $100 $120 $100 $100 30% 10% 4. Month $140 $100 SN Model Subscribers 2nd Month trd Month Income Statement Cost of Revenues Gross Margin Cumulative EBIT 4+ Month Actual Revenues SGAA $100 Year 2021 2021 2021 2021 2021 2021 2021 2021 Month lan Feb Mar Apr May New 3.100 2.131 3,162 3,194 3.226 3.258 3291 $100 $100 $100 $100 $100 $100 $100 $100 $110 CPA Elasticity ON ON ON ON ON ON ON ON ON ON Jul 2001 Aug Sep Oct 3,124 3,357 3390 2021 Developing a Model The number of New Acquired) Subscribers for 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 a 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 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 SGRA Cumulative EBIT Previous month's Cumulative EBIT plus this month's EBIT Model Assumptions New Revenue/Month Cost of Revenue/Month Churry Month $5 $100 Subscribers 2nd Month 3rd Month $100 $120 $100 $100 30% 10% 4. Month $140 $100 SN Model Subscribers 2nd Month trd Month Income Statement Cost of Revenues Gross Margin Cumulative EBIT 4+ Month Actual Revenues SGAA $100 Year 2021 2021 2021 2021 2021 2021 2021 2021 Month lan Feb Mar Apr May New 3.100 2.131 3,162 3,194 3.226 3.258 3291 $100 $100 $100 $100 $100 $100 $100 $100 $110 CPA Elasticity ON ON ON ON ON ON ON ON ON ON Jul 2001 Aug Sep Oct 3,124 3,357 3390 2021

Answer & Explanation Solved by verified expert

See Answer