66 Developing a Model 67 The number of New (Acquired) Subscribers (or the related acquisition spend) would typically be the input that drives the model (the independent variable). 68 Following is a description of what the various columns represent 69 Year 70 Month 71 New (Acquired) Subscribers We have initially assumed that New Subscribers will grow at a constant 1% monthly 72 2nd Month Subscribers Calculated as number of New Subscribers previous month multiplied by the second month retention rate (1 - 30% churn) 73 3rd Month Subscribers Calculated as number of 2nd Month Subscribers previous month multiplied by the retention rate (1 - 10% churn) 74 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). 75 Base CPA We entered Match and Index functions in column G to reference the appropriate CPA base for the related month. 76 The Match and Index functions are presented in the next Workbook 77 CPA Elasticity We entered a VLOOKUP function in column H to reference the appropriate CPA Elasticity given the targeted number of customers acquired. 78 The VLOOKUP function is presented in the next Workbook. 79 Actual CPA Multiply the Base CPA by 1 plus the CPA Elasticity 80 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. 81 Cost of Revenues The unit cost ($100) multiplied by the total of New, 2nd, 3rd and 4+ Month Subscribers 82 Gross Margin Revenues minus Cost of Revenues 83 SGBA Acquisition Expense (Actual CPAX number of New Subscribers) + Distribution Expense ($10 shipping and handling x total of New, 2nd, 3rd and 4+ Month Subscribers) 84 EBIT Gross Margin minus SG&A 85 Cumulative EBIT Previous month's Cumulative EBIT plus this month's EBIT 86 87 Model Assumptions 88 Subscribers 89 New 2nd Month 3rd Month 4. Month 90 Revenue/Month $5 $100 $120 $140 91 Cost of Revenues/Month $100 $100 $ $100 $100 92 Churn/Month 30% 10% 5% 93 94 95 Model 96 Subscribers CPA Income Statement Cumulative 97 Year Month New 2nd Month 3rd Month 4+ Month Base Elasticity Actual Revenues Cost of Revenues Gross Margin SG&A EBIT EBIT 98 2022 3,400 $100 096 99 2022 Feb 3,434 $100 096 100 2022 Mar 3,468 $100 0% 101 2022 Apr 3,503 $100 0% 102 2022 May 3,538 $100 0% 103 2022 Jun 3,573 $100 096 104 2022 Jul 3,609 $ $100 0% 105 2022 Aug 3,645 $100 106 2022 Sep 3,682 $100 096 107 2022 Oct 3,719 $110 09 108 2022 Nov 3,756 $110 0% 109 2022 Dec 3,793 $150 0% 110 2023 3,831 $100 09 111 2023 Feb 3,870 $100 0% 112 2023 Mar 3,908 $100 096 113 2023 Apr 3,947 $100 0% 114 2023 May 3,987 $100 % 096 115 2023 Jun 4,027 $100 0% 116 2023 Jul 4,067 $100 0% 117 2023 Aue 4,108 $100 0% 118 2023 Sep 4,149 $100 % 096 119 2023 Oct 4,190 $110 09 120 2023 Nov 4,232 $110 096 121 2023 Dec 4,274 $150 09 122 2024 Jan 4,317 $100 0% 123 2024 Feb 4,360 $100 0% 124 2024 Mar 4,404 $100 0% 125 2024 Apr 4,448 $100 09 126 2024 May 4,492 $100 0% 4.537 $100 09: Jan 090 Jan 127 2924 Jun 0 m TI a I s 10.606 Mar 2031 187 2029 Jun 9,698 $100 1096 188 2029 Jul 9.795 $100 10% 189 2029 Aug 9,892 $100 1096 190 2029 Sep 9.991 $100 10% 191 2029 Oct 10,091 $110 20% 192 2029 Nov 10,192 $110 20% 193 2029 Dec 10,294 $150 2096 194 2030 Jan 10.397 $100 2096 195 2030 Feb 10,501 $100 20% 196 2030 Mar $100 $ 2096 197 2030 Apr 10.712 $100 20% 198 2030 May 10,819 $100 2096 199 2030 Jun 10.927 $100 20% 200 2030 Jul 11,037 $100 2096 201 2030 Aug 11.147 $100 20% 202 2030 Sep 11,259 $100 2091 203 2030 Oct 11 371 $110 2096 204 2030 Nov 11,485 $110 20% 205 2030 Dec 11,600 $150 20% 206 2031 Jan 11,716 $100 20% 207 2031 Feb 11,833 $100 2096 208 2031 11.951 $100 20% 209 2031 Apr 12,071 $100 2096 210 2031 May 12 191 $100 2096 211 2031 Jun 12,313 $100 20% 212 2031 Jul 12,436 $100 2096 213 2031 Aug 12,561 $100 20% 214 2031 Sep 12,686 $100 $ 2096 215 Oct 12.813 $1101 20% 216 2031 Nov 12,941 $110 2096 217 2031 Dec 13,071 $150 20% 218 219 Write SUMIF functions in each of the green cells to SUM the EBIT values for each year. 220 Use the S'in the formula such that the formula in cell C222 can be copied down to the subsequent years. 221 222 2022 223 2023 224 2024 225 2025 226 2026 227 2027 228 2028 229 2029 230 2030 231 20311 232 Terminal Value 233 234 Calculate the Net Present Value (NPV) selecting a 10% Discount Rate of the above annual time series. 235 236 Calculate the Internal Rate of Return (IRR) of the above annual time series. 237 238 Remember the two above values for a couple of minutes 239 We are now going to model alternative hypothesized scenarios 240 The Formula in the New Subscribers' Column has been written to permit you to change parameters. 241 Change the Month Selection in the grey box in cell 044 on the DATA TABLE' Sheet to 'Exclude Dec'. 242 Change the 'Monthly Growth Rate' in the grey boxin cell B45 on the 'DATA TABLE' Sheet to 1.5% 243 Notice that the NPV in B235 and the IRR in B237 hav changed. 244 These values are 'dynamic' because they change as input parameters are changed. 245 246 Creating a Data Table 247 You are going to want to complete this Sheet with the 'DATA TABLES' Sheet arranged to the right. 248 Instructions on how to view multiple Sheets are on Row 95 of the 'EXCEL Financial Functions Sheet of this Workbook. 249 The data table(s) related to this Sheet start on row 42 of the 'DATA TABLES Sheet. 250 251 You are now going to calculate a variety of measures for a variety of Monthly Growth' measures for alternative Months to Advertise'. 252 Create the following Data Table by selecting the Data Table..." option in the What-if Analysis'icon on the 'Data'tab. 253 Set the "Row Input Cell:'to Cell C44 on the DATA TABLE Sheet. 254 Set the Column Input Cell:' to Cell C45 on the 'DATA TABLE Sheet. 255 What is the scenario that results in the highest Internal Rate of Return (IRR)? 256 257 Why do you think this scenario generates the highest IRRs? 258 259 Calculate the Net Present Value (NPV) selecting a 10% Discount Rate of the above annual 10-year time series excluding the terminal value 260 261 The vast majority of the NPV including the terminal value comes from the terminal value. 262 This is often the case. The majority of the value of most businesses is the value of the 'ongoing concern'. 263 That is why financial analysts often use Discount Rates significantly higher than the weighted average cost of capital to recognize the risk of business over the very long term 66 Developing a Model 67 The number of New (Acquired) Subscribers (or the related acquisition spend) would typically be the input that drives the model (the independent variable). 68 Following is a description of what the various columns represent 69 Year 70 Month 71 New (Acquired) Subscribers We have initially assumed that New Subscribers will grow at a constant 1% monthly 72 2nd Month Subscribers Calculated as number of New Subscribers previous month multiplied by the second month retention rate (1 - 30% churn) 73 3rd Month Subscribers Calculated as number of 2nd Month Subscribers previous month multiplied by the retention rate (1 - 10% churn) 74 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). 75 Base CPA We entered Match and Index functions in column G to reference the appropriate CPA base for the related month. 76 The Match and Index functions are presented in the next Workbook 77 CPA Elasticity We entered a VLOOKUP function in column H to reference the appropriate CPA Elasticity given the targeted number of customers acquired. 78 The VLOOKUP function is presented in the next Workbook. 79 Actual CPA Multiply the Base CPA by 1 plus the CPA Elasticity 80 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. 81 Cost of Revenues The unit cost ($100) multiplied by the total of New, 2nd, 3rd and 4+ Month Subscribers 82 Gross Margin Revenues minus Cost of Revenues 83 SGBA Acquisition Expense (Actual CPAX number of New Subscribers) + Distribution Expense ($10 shipping and handling x total of New, 2nd, 3rd and 4+ Month Subscribers) 84 EBIT Gross Margin minus SG&A 85 Cumulative EBIT Previous month's Cumulative EBIT plus this month's EBIT 86 87 Model Assumptions 88 Subscribers 89 New 2nd Month 3rd Month 4. Month 90 Revenue/Month $5 $100 $120 $140 91 Cost of Revenues/Month $100 $100 $ $100 $100 92 Churn/Month 30% 10% 5% 93 94 95 Model 96 Subscribers CPA Income Statement Cumulative 97 Year Month New 2nd Month 3rd Month 4+ Month Base Elasticity Actual Revenues Cost of Revenues Gross Margin SG&A EBIT EBIT 98 2022 3,400 $100 096 99 2022 Feb 3,434 $100 096 100 2022 Mar 3,468 $100 0% 101 2022 Apr 3,503 $100 0% 102 2022 May 3,538 $100 0% 103 2022 Jun 3,573 $100 096 104 2022 Jul 3,609 $ $100 0% 105 2022 Aug 3,645 $100 106 2022 Sep 3,682 $100 096 107 2022 Oct 3,719 $110 09 108 2022 Nov 3,756 $110 0% 109 2022 Dec 3,793 $150 0% 110 2023 3,831 $100 09 111 2023 Feb 3,870 $100 0% 112 2023 Mar 3,908 $100 096 113 2023 Apr 3,947 $100 0% 114 2023 May 3,987 $100 % 096 115 2023 Jun 4,027 $100 0% 116 2023 Jul 4,067 $100 0% 117 2023 Aue 4,108 $100 0% 118 2023 Sep 4,149 $100 % 096 119 2023 Oct 4,190 $110 09 120 2023 Nov 4,232 $110 096 121 2023 Dec 4,274 $150 09 122 2024 Jan 4,317 $100 0% 123 2024 Feb 4,360 $100 0% 124 2024 Mar 4,404 $100 0% 125 2024 Apr 4,448 $100 09 126 2024 May 4,492 $100 0% 4.537 $100 09: Jan 090 Jan 127 2924 Jun 0 m TI a I s 10.606 Mar 2031 187 2029 Jun 9,698 $100 1096 188 2029 Jul 9.795 $100 10% 189 2029 Aug 9,892 $100 1096 190 2029 Sep 9.991 $100 10% 191 2029 Oct 10,091 $110 20% 192 2029 Nov 10,192 $110 20% 193 2029 Dec 10,294 $150 2096 194 2030 Jan 10.397 $100 2096 195 2030 Feb 10,501 $100 20% 196 2030 Mar $100 $ 2096 197 2030 Apr 10.712 $100 20% 198 2030 May 10,819 $100 2096 199 2030 Jun 10.927 $100 20% 200 2030 Jul 11,037 $100 2096 201 2030 Aug 11.147 $100 20% 202 2030 Sep 11,259 $100 2091 203 2030 Oct 11 371 $110 2096 204 2030 Nov 11,485 $110 20% 205 2030 Dec 11,600 $150 20% 206 2031 Jan 11,716 $100 20% 207 2031 Feb 11,833 $100 2096 208 2031 11.951 $100 20% 209 2031 Apr 12,071 $100 2096 210 2031 May 12 191 $100 2096 211 2031 Jun 12,313 $100 20% 212 2031 Jul 12,436 $100 2096 213 2031 Aug 12,561 $100 20% 214 2031 Sep 12,686 $100 $ 2096 215 Oct 12.813 $1101 20% 216 2031 Nov 12,941 $110 2096 217 2031 Dec 13,071 $150 20% 218 219 Write SUMIF functions in each of the green cells to SUM the EBIT values for each year. 220 Use the S'in the formula such that the formula in cell C222 can be copied down to the subsequent years. 221 222 2022 223 2023 224 2024 225 2025 226 2026 227 2027 228 2028 229 2029 230 2030 231 20311 232 Terminal Value 233 234 Calculate the Net Present Value (NPV) selecting a 10% Discount Rate of the above annual time series. 235 236 Calculate the Internal Rate of Return (IRR) of the above annual time series. 237 238 Remember the two above values for a couple of minutes 239 We are now going to model alternative hypothesized scenarios 240 The Formula in the New Subscribers' Column has been written to permit you to change parameters. 241 Change the Month Selection in the grey box in cell 044 on the DATA TABLE' Sheet to 'Exclude Dec'. 242 Change the 'Monthly Growth Rate' in the grey boxin cell B45 on the 'DATA TABLE' Sheet to 1.5% 243 Notice that the NPV in B235 and the IRR in B237 hav changed. 244 These values are 'dynamic' because they change as input parameters are changed. 245 246 Creating a Data Table 247 You are going to want to complete this Sheet with the 'DATA TABLES' Sheet arranged to the right. 248 Instructions on how to view multiple Sheets are on Row 95 of the 'EXCEL Financial Functions Sheet of this Workbook. 249 The data table(s) related to this Sheet start on row 42 of the 'DATA TABLES Sheet. 250 251 You are now going to calculate a variety of measures for a variety of Monthly Growth' measures for alternative Months to Advertise'. 252 Create the following Data Table by selecting the Data Table..." option in the What-if Analysis'icon on the 'Data'tab. 253 Set the "Row Input Cell:'to Cell C44 on the DATA TABLE Sheet. 254 Set the Column Input Cell:' to Cell C45 on the 'DATA TABLE Sheet. 255 What is the scenario that results in the highest Internal Rate of Return (IRR)? 256 257 Why do you think this scenario generates the highest IRRs? 258 259 Calculate the Net Present Value (NPV) selecting a 10% Discount Rate of the above annual 10-year time series excluding the terminal value 260 261 The vast majority of the NPV including the terminal value comes from the terminal value. 262 This is often the case. The majority of the value of most businesses is the value of the 'ongoing concern'. 263 That is why financial analysts often use Discount Rates significantly higher than the weighted average cost of capital to recognize the risk of business over the very long term