Problem 6: Forecasting You have an idea for a new service that offers customized training 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 1 Forecasting and Charting 2 Total market potential % remaining catured/period Market growth/period 2,000,000 1.2% 4 0.30% 5 Figure 12-Forecasting Parameters In your model, make projections for 60 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 2,000,000 customers. Each month you sign up 1.2% of customers in the market that have not yet signed up. Scenario 2: Growing Market- Total market potential is initially 2,000,000 customers but grows at 0.3% per month. Each month you sign up 1.2 % of customers in the market that have not yet signed up. Both scenarios initially (Period 0) have 0 new customers and 0 total customers. Beginning Period 1, calculations must take into account 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 TotalMarket calculations accounts for the Market growth/period parameter value. Hint:Whenever you need to reference a value calculated ina 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. Scenario 2: Growing Market Total Market New Clients Total Clients Scenario 1: Constant Market 26 Period Total Clients New Clients 27 Period 28 24,000 24,000 2,000,000 1 24,000 24,000 29 47,784 23,784 2,006,000 2 23,712 47,712 2 30 71,355 23,571 2,012,018 71,139 23,427 31 94,715 2,018,054 2,024,108 23,360 23,146 94,286 32 4 117,868 23,153 5 117,154 22,869 33 140,816 2,030,181 2,036,271 22,948 139,749 162,072 6 22,594 34 6 163,561 22,745 7 22,323 7 35 186,107 22,546 2,042,380 184,127 22,055 36 208,456 2,048,507 22,349 205,917 21,790 37 230,610 2,054,653 22,154 10 21,529 227,446 10 38 252,573 11 2,060,817 21,962 21,271 21,015 248,717 39 11 21,773 274,346 269,732 12 2,066,999 40 12 295,932 13 2,073,200 21,586 290,495 20,763 41 13 2,079,420 317,334 14 21,402 311,009 20,514 42 14 15 2,085,658 21,220 338,554 359,594 20,268 331,277 15 43 351,302 16 2,091,915 21,040 44 16 20,025 380,457 17 2,098,191 19,784 371,086 20,863 45 17 18 401,146 46 18 19,547 390,633 2,104,485 20,688 19 421,661 47 19,312 2,110,799 20,516 19 409,946 20,346 48 2,117.131 442,007 20 19,081 429,026 20 49 21 462,185 18,852 447,878 21 2,123,482 20,178 Figure 13-Two Scenarios of Market Capture (Partial View) Search Forecasting Saved Off AutoSave Acrobat Help View Review Data Formulas Page Layout Insert Home File General 28 Wrap Text 11A A X Cut Calibri 60 00 $ .% 9 h Copy Paste- Merge & Center A BIU Format Painter Number Aligement Font Clipboard F3 G D C B A Forecasting and Charting 2 Total market potential % remaining captured period Market growth/period 2,000,000 3 1.2 % 4 0.30% 5 6 7 Scenario 2: Growing Market Total Market New Clients Total Clients Scenario 1: Constant Market 8 Period Total Clients 9 Period New Clients 0 10 0 11 1 1 12 2 2 13 3 3 14 4 4 15 5 5 16 6 6 17 7 18 8 8 19 9 20 10 10 21 11 11 22 12 12 23 13 13 24 14 14 25 15 15 26 16 16 27 17 17 28 18 18 29 19 Sheet1 19 Type here to search 1080 TAKING YOU TO A EWREALITY Problem 6: Forecasting You have an idea for a new service that offers customized training 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 1 Forecasting and Charting 2 Total market potential % remaining catured/period Market growth/period 2,000,000 1.2% 4 0.30% 5 Figure 12-Forecasting Parameters In your model, make projections for 60 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 2,000,000 customers. Each month you sign up 1.2% of customers in the market that have not yet signed up. Scenario 2: Growing Market- Total market potential is initially 2,000,000 customers but grows at 0.3% per month. Each month you sign up 1.2 % of customers in the market that have not yet signed up. Both scenarios initially (Period 0) have 0 new customers and 0 total customers. Beginning Period 1, calculations must take into account 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 TotalMarket calculations accounts for the Market growth/period parameter value. Hint:Whenever you need to reference a value calculated ina 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. Scenario 2: Growing Market Total Market New Clients Total Clients Scenario 1: Constant Market 26 Period Total Clients New Clients 27 Period 28 24,000 24,000 2,000,000 1 24,000 24,000 29 47,784 23,784 2,006,000 2 23,712 47,712 2 30 71,355 23,571 2,012,018 71,139 23,427 31 94,715 2,018,054 2,024,108 23,360 23,146 94,286 32 4 117,868 23,153 5 117,154 22,869 33 140,816 2,030,181 2,036,271 22,948 139,749 162,072 6 22,594 34 6 163,561 22,745 7 22,323 7 35 186,107 22,546 2,042,380 184,127 22,055 36 208,456 2,048,507 22,349 205,917 21,790 37 230,610 2,054,653 22,154 10 21,529 227,446 10 38 252,573 11 2,060,817 21,962 21,271 21,015 248,717 39 11 21,773 274,346 269,732 12 2,066,999 40 12 295,932 13 2,073,200 21,586 290,495 20,763 41 13 2,079,420 317,334 14 21,402 311,009 20,514 42 14 15 2,085,658 21,220 338,554 359,594 20,268 331,277 15 43 351,302 16 2,091,915 21,040 44 16 20,025 380,457 17 2,098,191 19,784 371,086 20,863 45 17 18 401,146 46 18 19,547 390,633 2,104,485 20,688 19 421,661 47 19,312 2,110,799 20,516 19 409,946 20,346 48 2,117.131 442,007 20 19,081 429,026 20 49 21 462,185 18,852 447,878 21 2,123,482 20,178 Figure 13-Two Scenarios of Market Capture (Partial View) Search Forecasting Saved Off AutoSave Acrobat Help View Review Data Formulas Page Layout Insert Home File General 28 Wrap Text 11A A X Cut Calibri 60 00 $ .% 9 h Copy Paste- Merge & Center A BIU Format Painter Number Aligement Font Clipboard F3 G D C B A Forecasting and Charting 2 Total market potential % remaining captured period Market growth/period 2,000,000 3 1.2 % 4 0.30% 5 6 7 Scenario 2: Growing Market Total Market New Clients Total Clients Scenario 1: Constant Market 8 Period Total Clients 9 Period New Clients 0 10 0 11 1 1 12 2 2 13 3 3 14 4 4 15 5 5 16 6 6 17 7 18 8 8 19 9 20 10 10 21 11 11 22 12 12 23 13 13 24 14 14 25 15 15 26 16 16 27 17 17 28 18 18 29 19 Sheet1 19 Type here to search 1080 TAKING YOU TO A EWREALITY