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. a The yellow cells below in column F are cells in which you must enter the 'hypothesized scenarios' defined in Rows 125 to Row 149. In each Hypothesized Scenario, you are asked to change the number(s) 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 nave forecast in column H so you may contrast it against the results of the forecasts of each hypothesis. Default (Leave as As) 'Nave' 2020 2021 2022E Income Statement Revenues 100.0 110.0 110.0 121.0 Growth 10% 10% 70.4 60.0 40.0 64.0 46.0 110.0 0.0 50.6 40% 42% 42% 17.0 0.0 18.7 Cost of Revenues Gross Profits Gross Margin Operating Expenses (OpEx) OpEx Percent of Revenues EBIT Net Interest Income (Expense) Interest Rate 15.0 15% 15% 15% 111 11 29.0 25.0 5.0 31.9 6.5 5.0 5% 5% 30.0 Pretax Income Taxes 34.0 5.3 38.4 6.0 7.0 Tax Rate 23% 16% 16% Net Income 23.0 28.7 32.5 Balance Sheet 138.7 Cash Accounts Receivable 100.0 15.0 130.7 16.0 166.1 17.6 54.8 53.1 53.1 20.0 33.0 37.0 47.0 Days of Sales Outstanding (DSO) PP&E Accounts Payable Days of Payables Outstanding (DPO) Equity 10.0 12.0 13.2 60.8 68.4 68.4 100.0 120.7 120.7 144.1 Cash Flow Statement Net Income 28.7 32.5 3.0 4.0 4.0 16.0 (12.0) + Depreciation - Increase in Accounts Receivable + Increase in Accounts Payable - Capital Expenditures (CapEx) - Dividends Payout Ratio = Increase (Decrease) in Cash (1.0) 2.0 (10.0) 8.0 (1.6) 1.2 (10.0) 9.0 (8.0) 6.0 26% 28% 28% 30.7 8.0 35.4 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. a The yellow cells below in column F are cells in which you must enter the 'hypothesized scenarios' defined in Rows 125 to Row 149. In each Hypothesized Scenario, you are asked to change the number(s) 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 nave forecast in column H so you may contrast it against the results of the forecasts of each hypothesis. Default (Leave as As) 'Nave' 2020 2021 2022E Income Statement Revenues 100.0 110.0 110.0 121.0 Growth 10% 10% 70.4 60.0 40.0 64.0 46.0 110.0 0.0 50.6 40% 42% 42% 17.0 0.0 18.7 Cost of Revenues Gross Profits Gross Margin Operating Expenses (OpEx) OpEx Percent of Revenues EBIT Net Interest Income (Expense) Interest Rate 15.0 15% 15% 15% 111 11 29.0 25.0 5.0 31.9 6.5 5.0 5% 5% 30.0 Pretax Income Taxes 34.0 5.3 38.4 6.0 7.0 Tax Rate 23% 16% 16% Net Income 23.0 28.7 32.5 Balance Sheet 138.7 Cash Accounts Receivable 100.0 15.0 130.7 16.0 166.1 17.6 54.8 53.1 53.1 20.0 33.0 37.0 47.0 Days of Sales Outstanding (DSO) PP&E Accounts Payable Days of Payables Outstanding (DPO) Equity 10.0 12.0 13.2 60.8 68.4 68.4 100.0 120.7 120.7 144.1 Cash Flow Statement Net Income 28.7 32.5 3.0 4.0 4.0 16.0 (12.0) + Depreciation - Increase in Accounts Receivable + Increase in Accounts Payable - Capital Expenditures (CapEx) - Dividends Payout Ratio = Increase (Decrease) in Cash (1.0) 2.0 (10.0) 8.0 (1.6) 1.2 (10.0) 9.0 (8.0) 6.0 26% 28% 28% 30.7 8.0 35.4