Problem 6: Forecasting You have an idea for a new service that offers customized products for subscribers, but you want to get an idea of how your business could grow by capturing portions of the potential market in the next 5 years (60 months). Construct a forecasting worksheet to calculate: the number of new clients each month (period), and the total client base (cumulative number of clients signed up) each month (period). There are the three key parameters values that impact your projections (Figure 12): 1. Total market potential 2. %remaining captured/period 3. Market growth/period A B Forecasting and Charting 1 2 3 4 5 Total market potential 10,000,000 % remaining captured/period 1.07% Market growth/period 2.50% Figure 11 - Forecasting Parameters In your model, make projections for 50 Periods (Months) based on these parameters for the two separate scenarios described below to project the number of new customers (Figure 13). Scenario 1: Constant Market - Total market potential is 10,000,000 customers. Each month you sign up 1.07% of customers in the market that have not yet signed up. Scenario 2: Growing Market - Total market potential is initially 10,000,000 customers but grows at 2.5% per month. Each month you sign up 1.07% of customers in the market that have not yet signed up. Both scenarios initially (Period ) have 0 new customers and total customers. Beginning Period 1, calculations must consider the previous period's Total_Clients values as well as the current period's New_Clients values. In the case of Scenario 2, the Period 2 Total_Market calculations accounts for the Market growth/period parameter value. Hint: Whenever you need to reference a value calculated in a previous row you need to use its cell address, not a range name; but remember that the rest of the formula should make use of named ranges. E F G H Period 0 1 2 3 4 D 25 26 Scenario 1: Constant Market 27 Period New_Clients Total_Clients 28 0 0 0 29 1 107,000 107,000 30 2 105,855 212,855 31 3 104,722 317,578 32 4 103,602 421,179 33 5 102,493 523,673 34 6 101,397 625,070 35 7 100,312 725,381 36 8 99,238 824,620 37 9 98,177 922,796 38 10 97,126 1,019,922 39 11 95,087 1,116,009 40 12 95,059 1,211,068 41 13 94,042 1,305, 109 42 14 93,035 1,398,145 43 15 92,040 1,490,185 44 16 91,055 1,581,240 45 17 90,081 1,671,320 Figure 12 - Two Scenarios of Market Capture (Partial View) 5 6 7 8 9 Scenario 2: Growing Market Total_Market New Clients Total_Clients 0 0 0 10,000,000 107,000 107,000 10,250,000 108,530 215,530 10,506,250 110,111 325,641 10,768,906 111,743 437,384 11,038,129 113,428 550,812 11,314,082 115,167 665,979 11,596,934 116,961 782,940 11,886,858 118,812 901,752 12,184,029 120,720 1,022,472 12,488,630 122,688 1,145,160 12,800,845 124,716 1,269,876 13,120,867 126,806 1,396,682 13,448,888 128,959 1,525,640 13,785, 110 131,176 1,656,816 14,129,738 133,460 1,790,277 14,482,982 135,812 1,926,089 14,845,056 138,233 2,064,322 10 11 12 13 14 15 16 17 ! Problem 6: Forecasting You have an idea for a new service that offers customized products for subscribers, but you want to get an idea of how your business could grow by capturing portions of the potential market in the next 5 years (60 months). Construct a forecasting worksheet to calculate: the number of new clients each month (period), and the total client base (cumulative number of clients signed up) each month (period). There are the three key parameters values that impact your projections (Figure 12): 1. Total market potential 2. %remaining captured/period 3. Market growth/period A B Forecasting and Charting 1 2 3 4 5 Total market potential 10,000,000 % remaining captured/period 1.07% Market growth/period 2.50% Figure 11 - Forecasting Parameters In your model, make projections for 50 Periods (Months) based on these parameters for the two separate scenarios described below to project the number of new customers (Figure 13). Scenario 1: Constant Market - Total market potential is 10,000,000 customers. Each month you sign up 1.07% of customers in the market that have not yet signed up. Scenario 2: Growing Market - Total market potential is initially 10,000,000 customers but grows at 2.5% per month. Each month you sign up 1.07% of customers in the market that have not yet signed up. Both scenarios initially (Period ) have 0 new customers and total customers. Beginning Period 1, calculations must consider the previous period's Total_Clients values as well as the current period's New_Clients values. In the case of Scenario 2, the Period 2 Total_Market calculations accounts for the Market growth/period parameter value. Hint: Whenever you need to reference a value calculated in a previous row you need to use its cell address, not a range name; but remember that the rest of the formula should make use of named ranges. E F G H Period 0 1 2 3 4 D 25 26 Scenario 1: Constant Market 27 Period New_Clients Total_Clients 28 0 0 0 29 1 107,000 107,000 30 2 105,855 212,855 31 3 104,722 317,578 32 4 103,602 421,179 33 5 102,493 523,673 34 6 101,397 625,070 35 7 100,312 725,381 36 8 99,238 824,620 37 9 98,177 922,796 38 10 97,126 1,019,922 39 11 95,087 1,116,009 40 12 95,059 1,211,068 41 13 94,042 1,305, 109 42 14 93,035 1,398,145 43 15 92,040 1,490,185 44 16 91,055 1,581,240 45 17 90,081 1,671,320 Figure 12 - Two Scenarios of Market Capture (Partial View) 5 6 7 8 9 Scenario 2: Growing Market Total_Market New Clients Total_Clients 0 0 0 10,000,000 107,000 107,000 10,250,000 108,530 215,530 10,506,250 110,111 325,641 10,768,906 111,743 437,384 11,038,129 113,428 550,812 11,314,082 115,167 665,979 11,596,934 116,961 782,940 11,886,858 118,812 901,752 12,184,029 120,720 1,022,472 12,488,630 122,688 1,145,160 12,800,845 124,716 1,269,876 13,120,867 126,806 1,396,682 13,448,888 128,959 1,525,640 13,785, 110 131,176 1,656,816 14,129,738 133,460 1,790,277 14,482,982 135,812 1,926,089 14,845,056 138,233 2,064,322 10 11 12 13 14 15 16 17