Required Homework Questions This assignment is a more advanced version of the "Simple Company Model' developed in Developing a Model Workbook 7) to implement various hypothesis driven forecasts. The yellow cells below in column F are cells in which you must enter the "hypothesized scenarios' in Rows 125 to Row 149. In each Hypothesized Scenario, you are asked to change the numbers in one or more of the yellow boxes from the default currently in the yellow box. Only change the figures you are asked to change and leave all others set to the default. For comparative purposes, we have made a copy of the default nalive forecast in column H so you may contrast it against the results of the forecasts of each hypothesis. Default (Leave as As) "Nalive 2018 2019 2020E 100 110 10% 121 10% 70 51 60 64 46 42% Income Statement Revenues Growth Cost of Revenues Gross Profits Gross Margin Operating Expenses (Opex) OpEx Percent of Revenues EBIT Net Interest Income (Expense) Interest Rate Pretax Income Taxes Tax Rate Net Income 121 10% 70 51 42% 19 15% 40 40% 15 15% 17 15% 25 5 29 5 5% 42% 19 15% 32 7 5% 38 6 16% 32 30 7 23% 34 5 16% 29 32 7 5% 38 6 16% 32 23 Balance Sheet Cash 166 100 15 55 166 18 18 53 53 20 Accounts Receivable Days of Sales Outstanding (DSO) PP&E Accounts Payable Days of Payables Outstanding (DPO) Equity 131 16 53 33 12 68 121 47 13 10 47 13 68 61 100 68 144 144 29 32 32 3 4 1 Cash Flow Statement Net Income + Depreciation - Increase in Accounts Receivable + Increase in Accounts Payable - Capital Expenditures (Capex) Dividends (1) 2 (10) 8 4 (2) 1 (10) (2) 1 (10) 9 (8) 6 9 9 9 28% 28% 35 -7 Dividends 6 8 8 Payout Ratio 26% 28% 9 = Increase (Decrease) in Cash 31 35 0 1 2 3 we have created hypothesized scenarios for you to forecast: 4 5 Hypothesized Scenario 1 6 Assume a new competitor enters the market and that drives Gross Margin to 25% -7 What is the forecasted Net Income? 8 9 What is the forecasted Cash balance? 0 1 2 Hypothesized Scenario 2 3 Set the DSO equal to 10 days 4 Leave the Gross Margin at 25% 5 What is the forecasted Cash balance? 6 -7 8 See how the: 9 Reduction of DSOs reduced Accounts Receivable' on the Balance Sheet and -0 Impacted Increase in Accounts Receivable' on the Cash Flow Statement, -1 Which impacts 'Cash' on the Balance Sheet. -2 3 Hypothesized Scenario 3 4 Construct a new plant increasing CapEx to $100 (remember that this is a -100 in the model as it is a use of cash) -5 Leave the Gross Margin at 25% and the DSO at 10% -6 What is the forecasted Cash balance? -7 -8 What is the forecasted PP&E balance? -9 0 1 See how the: 2 Investment in Capex has an impact on PP&E, 3 Affects Cash Flow on the Cash Flow Statement, 4 Reduces the Cash Balance on the Balance Sheet and 5 Lowers GAAP Net Income as Depreciation rises, 6 7 Required Homework Questions This assignment is a more advanced version of the "Simple Company Model' developed in Developing a Model Workbook 7) to implement various hypothesis driven forecasts. The yellow cells below in column F are cells in which you must enter the "hypothesized scenarios' in Rows 125 to Row 149. In each Hypothesized Scenario, you are asked to change the numbers in one or more of the yellow boxes from the default currently in the yellow box. Only change the figures you are asked to change and leave all others set to the default. For comparative purposes, we have made a copy of the default nalive forecast in column H so you may contrast it against the results of the forecasts of each hypothesis. Default (Leave as As) "Nalive 2018 2019 2020E 100 110 10% 121 10% 70 51 60 64 46 42% Income Statement Revenues Growth Cost of Revenues Gross Profits Gross Margin Operating Expenses (Opex) OpEx Percent of Revenues EBIT Net Interest Income (Expense) Interest Rate Pretax Income Taxes Tax Rate Net Income 121 10% 70 51 42% 19 15% 40 40% 15 15% 17 15% 25 5 29 5 5% 42% 19 15% 32 7 5% 38 6 16% 32 30 7 23% 34 5 16% 29 32 7 5% 38 6 16% 32 23 Balance Sheet Cash 166 100 15 55 166 18 18 53 53 20 Accounts Receivable Days of Sales Outstanding (DSO) PP&E Accounts Payable Days of Payables Outstanding (DPO) Equity 131 16 53 33 12 68 121 47 13 10 47 13 68 61 100 68 144 144 29 32 32 3 4 1 Cash Flow Statement Net Income + Depreciation - Increase in Accounts Receivable + Increase in Accounts Payable - Capital Expenditures (Capex) Dividends (1) 2 (10) 8 4 (2) 1 (10) (2) 1 (10) 9 (8) 6 9 9 9 28% 28% 35 -7 Dividends 6 8 8 Payout Ratio 26% 28% 9 = Increase (Decrease) in Cash 31 35 0 1 2 3 we have created hypothesized scenarios for you to forecast: 4 5 Hypothesized Scenario 1 6 Assume a new competitor enters the market and that drives Gross Margin to 25% -7 What is the forecasted Net Income? 8 9 What is the forecasted Cash balance? 0 1 2 Hypothesized Scenario 2 3 Set the DSO equal to 10 days 4 Leave the Gross Margin at 25% 5 What is the forecasted Cash balance? 6 -7 8 See how the: 9 Reduction of DSOs reduced Accounts Receivable' on the Balance Sheet and -0 Impacted Increase in Accounts Receivable' on the Cash Flow Statement, -1 Which impacts 'Cash' on the Balance Sheet. -2 3 Hypothesized Scenario 3 4 Construct a new plant increasing CapEx to $100 (remember that this is a -100 in the model as it is a use of cash) -5 Leave the Gross Margin at 25% and the DSO at 10% -6 What is the forecasted Cash balance? -7 -8 What is the forecasted PP&E balance? -9 0 1 See how the: 2 Investment in Capex has an impact on PP&E, 3 Affects Cash Flow on the Cash Flow Statement, 4 Reduces the Cash Balance on the Balance Sheet and 5 Lowers GAAP Net Income as Depreciation rises, 6 7