110 70 0:00 0 25 un 34 38 77 78 This assignment is a more advanced version of the 'Simple Company Model' developed in Developing a Model (Workbook 7) to implement various hypothesis driven forec 79 The yellow cells below in column Fare cells in which you must enter the 'hypothesized scenarios' defined in Rows 125 to Row 149. 80 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. 81 Only change the figures you are asked to change and leave all others set to the default. 82 For comparative purposes, we have made a copy of the default nave forecast in column H so you may contrast it against the results of the forecasts of each hypothesis. 83 84 Default (Leave as As) 85 2019 2020 2021E 'Nave' 86 Income Statement 87 Revenues 100 121 88 Growth 10% 10% 89 Cost of Revenues 60 90 Gross Profits 40 46 51 91 Gross Margin 40% 42% 42% 92 Operating Expenses (OpEx) 15 19 93 OpEx Percent of Revenues 15% 15% 15% 94 EBIT 29 32 95 Net Interest Income (Expense) 5 7 96 Interest Rate 5% 5% 97 Pretax Income 30 98 Taxes 7 5 6 99 Tax Rate 16%) 16% 100 29 32 101 102 Balance Sheet 103 Cash 100 131 139 166 104 Accounts Receivable 15 105 Days of Sales Outstanding (DSO) 106 PP&E 37 47 107 Accounts Payable 12 108 Days of Payables Outstanding (DPO) 61 68 109 Equity 121 121 110 111 Cash Flow Statement 112 Net Income 32 113 + Depreciation 4 114 - Increase in Accounts Receivable (1) (2) 115 + Increase in Accounts Payable 2 (12) 1 116 - Capital Expenditures (Capex) (8) (10) (10) 117 - Dividends 6 9 118 Payout Ratio 26% 28% 28% 119 = Increase (Decrease) in Cash 31 35 120 23% Net Income 23 16 18 55 53 53 20 33 10 13 68 100 144 29 3 4 16 ) 000 00 9 8 00 a 123 we have created hypothesized scenarios for you to forecast: 124 125 Hypothesized Scenario 1 126 Assume a new competitor enters the market and that drives Gross Margin to 25% 127 What is the forecasted Net Income? 128 129 What is the forecasted Cash balance? 130 131 132 Hypothesized Scenario 2 133 Set the DSO equal to 10 days 134 Leave the Gross Margin at 25% 135 What is the forecasted Cash balance? 136 137 138 See how the: 139 Reduction of DSOs reduced 'Accounts Receivable' on the Balance Sheet and 140 Impacted '- Increase in Accounts Receivable' on the Cash Flow Statement, 141 Which impacts 'Cash' on the Balance Sheet. 142 143 Hypothesized Scenario 3 144 Construct a new plant increasing CapEx to $100 (remember that this is a -100 in the model as it is a use of cash) 145 Leave the Gross Margin at 25% and the DSO at 10% 146 What is the forecasted Cash balance? 147 148 What is the forecasted PP&E balance? 149 150 151 See how the: 152 Investment in Capex has an impact on PP&E, 153 Affects Cash Flow on the Cash Flow Statement, 154 Reduces the Cash Balance on the Balance Sheet and 155 Lowers GAAP Net Income as Depreciation rises. 156 107 110 70 0:00 0 25 un 34 38 77 78 This assignment is a more advanced version of the 'Simple Company Model' developed in Developing a Model (Workbook 7) to implement various hypothesis driven forec 79 The yellow cells below in column Fare cells in which you must enter the 'hypothesized scenarios' defined in Rows 125 to Row 149. 80 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. 81 Only change the figures you are asked to change and leave all others set to the default. 82 For comparative purposes, we have made a copy of the default nave forecast in column H so you may contrast it against the results of the forecasts of each hypothesis. 83 84 Default (Leave as As) 85 2019 2020 2021E 'Nave' 86 Income Statement 87 Revenues 100 121 88 Growth 10% 10% 89 Cost of Revenues 60 90 Gross Profits 40 46 51 91 Gross Margin 40% 42% 42% 92 Operating Expenses (OpEx) 15 19 93 OpEx Percent of Revenues 15% 15% 15% 94 EBIT 29 32 95 Net Interest Income (Expense) 5 7 96 Interest Rate 5% 5% 97 Pretax Income 30 98 Taxes 7 5 6 99 Tax Rate 16%) 16% 100 29 32 101 102 Balance Sheet 103 Cash 100 131 139 166 104 Accounts Receivable 15 105 Days of Sales Outstanding (DSO) 106 PP&E 37 47 107 Accounts Payable 12 108 Days of Payables Outstanding (DPO) 61 68 109 Equity 121 121 110 111 Cash Flow Statement 112 Net Income 32 113 + Depreciation 4 114 - Increase in Accounts Receivable (1) (2) 115 + Increase in Accounts Payable 2 (12) 1 116 - Capital Expenditures (Capex) (8) (10) (10) 117 - Dividends 6 9 118 Payout Ratio 26% 28% 28% 119 = Increase (Decrease) in Cash 31 35 120 23% Net Income 23 16 18 55 53 53 20 33 10 13 68 100 144 29 3 4 16 ) 000 00 9 8 00 a 123 we have created hypothesized scenarios for you to forecast: 124 125 Hypothesized Scenario 1 126 Assume a new competitor enters the market and that drives Gross Margin to 25% 127 What is the forecasted Net Income? 128 129 What is the forecasted Cash balance? 130 131 132 Hypothesized Scenario 2 133 Set the DSO equal to 10 days 134 Leave the Gross Margin at 25% 135 What is the forecasted Cash balance? 136 137 138 See how the: 139 Reduction of DSOs reduced 'Accounts Receivable' on the Balance Sheet and 140 Impacted '- Increase in Accounts Receivable' on the Cash Flow Statement, 141 Which impacts 'Cash' on the Balance Sheet. 142 143 Hypothesized Scenario 3 144 Construct a new plant increasing CapEx to $100 (remember that this is a -100 in the model as it is a use of cash) 145 Leave the Gross Margin at 25% and the DSO at 10% 146 What is the forecasted Cash balance? 147 148 What is the forecasted PP&E balance? 149 150 151 See how the: 152 Investment in Capex has an impact on PP&E, 153 Affects Cash Flow on the Cash Flow Statement, 154 Reduces the Cash Balance on the Balance Sheet and 155 Lowers GAAP Net Income as Depreciation rises. 156 107