Subscribers -6 Developing a Model 7 The number of New (Acquired) Subscribers (or the related acquisition spend) would typically be the input that drives the model (the independent variable). 8 Following is a description of what the various columns represent: -9 9 Year "O Month 1 New (Acquired) Subscribers We have initially assumed that New Subscribers will grow at a constant 1% monthly 2 2nd Month Subscribers Calculated as number of New Subscribers previous month multiplied by the second month retention rate (1 - 30% churn) 3 3rd Month Subscribers Calculated as number of 2nd Month Subscribers previous month multiplied by the retention rate (1 - 10% churn) 4 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). "5 Base CPA We entered Match and Index functions in column G to reference the appropriate CPA base for the related month. 6 The Match and Index functions are presented in the next Workbook. 7 CPA Elasticity We entered a VLOOKUP function in column H to reference the appropriate CPA Elasticity given the targeted number of customers acquired 8 8 The VLOOKUP function is presented in the next Workbook. 9 Actual CPA Multiply the Base CPA by 1 plus the CPA Elasticity -0 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 1 Cost of Revenues The unit cost ($100) multiplied by the total of New, 2nd, 3rd and 4+ Month Subscribers -2 Gross Margin Revenues minus Cost of Revenues -3 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) 4 EBIT Gross Margin minus SG&A -5 5 Cumulative EBIT Previous month's Cumulative EBIT plus this month's EBIT -6 -7 Model Assumptions 8 -9 New 2nd Month 3rd Month 4+ Month 0 Revenue/Month $5 $100 $120 $140 1 Cost of Revenues/Month $100 $100 $100 $100 2 Churn/Month 30% 10% 3 4 5 5 Model 6 Subscribers CPA Income Statement 7 Year Month New 2nd Month 3rd Month 4+ Month Base Elasticity Actual Revenues Cost of Revenues Gross Margin SG&A EBIT 8 2021 Jan 3,500 $100 0% 9 2021 Feb 3,535 $100 0% 00 2021 Mar 3,570 $100 0% 01 2021 Apr 3,606 $100 0% 02 2021 May 3,642 $100 09 03 2021 Jun 3,679 $100 0% 34 2021 3,715 $100 0% 05 2021 Aug 3,752 $100 0% 06 2021 Sep 3,790 $100 0% 07 2021 Oct 3,828 $110 0 0% 08 2021 Nov 3,866 $110 % 0% 09 2021 Dec 3,905 $150 % 0% 10 2022 3,944 $1001 0% 5% Cumulative EBIT Jan Write SUMIF functions in each of the green cells to SUM the EBIT values for each year. Use the "s' in the formula such that the formula in cell C222 can be copied down to the subsequent years. 2021) 2022 2023 2024 2025 2026 2027 2028 2029 2030 Terminal Value Calculate the Net Present Value (NPV) selecting a 10% Discount Rate of the above annual time series. Calculate the Internal Rate of Return (IRR) of the above annual time series. Remember the two above values for a couple of minutes We are now going to model alternative hypothesized scenarios! The Formula in the "New Subscribers" (Column C) has been written to permit you to change parameters. Change the 'Month Selection' in the grey box in cell 044 on the 'DATA TABLE' Sheet to "Exclude Dec'. Change the 'Monthly Growth Rate' in the grey box in cell B45 on the 'DATA TABLE Sheet to 1.5%. Notice that the NPV in B235 and the IRR in B237 hav changed. These values are 'dynamic' because they change as input parameters are changed. Creating a Data Table You are going to want to complete this Sheet with the 'DATA TABLES' Sheet arranged to the right. Instructions on how to view multiple Sheets are on Row 137 of the 'EXCEL Financial Functions' Sheet of this Workbook. The data table(s) related to this Sheet start on row 42 of the 'DATA TABLES' Sheet You are now going to calculate a variety of measures for a variety of "Monthly Growth' measures for alternative "Months to Advertise'. Create the following Data Table by selecting the 'Data Table.' option in the What-if Analysis" icon on the "Data" tab. Set the "Row Input Cell:" to cell 044 on the 'DATA TABLE'Sheet. Set the 'Column Input Cell:" to cell 045 on the 'DATA TABLE' Sheet. What is the scenario that results in the highest Internal Rate of Return (IRR)? Why do you think this scenario generates the highest IRRS? Calculate the Net Present Value (NPV) selecting a 10% Discount Rate of the above annual 10-year time series excluding the terminal value. The vast maiority of the NPV including the terminal value comes from the terminal value. Subscribers -6 Developing a Model 7 The number of New (Acquired) Subscribers (or the related acquisition spend) would typically be the input that drives the model (the independent variable). 8 Following is a description of what the various columns represent: -9 9 Year "O Month 1 New (Acquired) Subscribers We have initially assumed that New Subscribers will grow at a constant 1% monthly 2 2nd Month Subscribers Calculated as number of New Subscribers previous month multiplied by the second month retention rate (1 - 30% churn) 3 3rd Month Subscribers Calculated as number of 2nd Month Subscribers previous month multiplied by the retention rate (1 - 10% churn) 4 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). "5 Base CPA We entered Match and Index functions in column G to reference the appropriate CPA base for the related month. 6 The Match and Index functions are presented in the next Workbook. 7 CPA Elasticity We entered a VLOOKUP function in column H to reference the appropriate CPA Elasticity given the targeted number of customers acquired 8 8 The VLOOKUP function is presented in the next Workbook. 9 Actual CPA Multiply the Base CPA by 1 plus the CPA Elasticity -0 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 1 Cost of Revenues The unit cost ($100) multiplied by the total of New, 2nd, 3rd and 4+ Month Subscribers -2 Gross Margin Revenues minus Cost of Revenues -3 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) 4 EBIT Gross Margin minus SG&A -5 5 Cumulative EBIT Previous month's Cumulative EBIT plus this month's EBIT -6 -7 Model Assumptions 8 -9 New 2nd Month 3rd Month 4+ Month 0 Revenue/Month $5 $100 $120 $140 1 Cost of Revenues/Month $100 $100 $100 $100 2 Churn/Month 30% 10% 3 4 5 5 Model 6 Subscribers CPA Income Statement 7 Year Month New 2nd Month 3rd Month 4+ Month Base Elasticity Actual Revenues Cost of Revenues Gross Margin SG&A EBIT 8 2021 Jan 3,500 $100 0% 9 2021 Feb 3,535 $100 0% 00 2021 Mar 3,570 $100 0% 01 2021 Apr 3,606 $100 0% 02 2021 May 3,642 $100 09 03 2021 Jun 3,679 $100 0% 34 2021 3,715 $100 0% 05 2021 Aug 3,752 $100 0% 06 2021 Sep 3,790 $100 0% 07 2021 Oct 3,828 $110 0 0% 08 2021 Nov 3,866 $110 % 0% 09 2021 Dec 3,905 $150 % 0% 10 2022 3,944 $1001 0% 5% Cumulative EBIT Jan Write SUMIF functions in each of the green cells to SUM the EBIT values for each year. Use the "s' in the formula such that the formula in cell C222 can be copied down to the subsequent years. 2021) 2022 2023 2024 2025 2026 2027 2028 2029 2030 Terminal Value Calculate the Net Present Value (NPV) selecting a 10% Discount Rate of the above annual time series. Calculate the Internal Rate of Return (IRR) of the above annual time series. Remember the two above values for a couple of minutes We are now going to model alternative hypothesized scenarios! The Formula in the "New Subscribers" (Column C) has been written to permit you to change parameters. Change the 'Month Selection' in the grey box in cell 044 on the 'DATA TABLE' Sheet to "Exclude Dec'. Change the 'Monthly Growth Rate' in the grey box in cell B45 on the 'DATA TABLE Sheet to 1.5%. Notice that the NPV in B235 and the IRR in B237 hav changed. These values are 'dynamic' because they change as input parameters are changed. Creating a Data Table You are going to want to complete this Sheet with the 'DATA TABLES' Sheet arranged to the right. Instructions on how to view multiple Sheets are on Row 137 of the 'EXCEL Financial Functions' Sheet of this Workbook. The data table(s) related to this Sheet start on row 42 of the 'DATA TABLES' Sheet You are now going to calculate a variety of measures for a variety of "Monthly Growth' measures for alternative "Months to Advertise'. Create the following Data Table by selecting the 'Data Table.' option in the What-if Analysis" icon on the "Data" tab. Set the "Row Input Cell:" to cell 044 on the 'DATA TABLE'Sheet. Set the 'Column Input Cell:" to cell 045 on the 'DATA TABLE' Sheet. What is the scenario that results in the highest Internal Rate of Return (IRR)? Why do you think this scenario generates the highest IRRS? Calculate the Net Present Value (NPV) selecting a 10% Discount Rate of the above annual 10-year time series excluding the terminal value. The vast maiority of the NPV including the terminal value comes from the terminal value