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' defined 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 nave forecast in column H so you may contrast it against the results of the forecasts of each hypothesis. Default (Leave as As) 'Nave 2019 2020 2021E Income Statement Revenues Growth 100 110 121 121 10% % 10% 60 70 70 10% 64 46 42% 40 51 40% 15 17 Cost of Revenues Gross Profits Gross Margin Operating Expenses (Opex) OpEx Percent of Revenues EBIT Net Interest Income (Expense) Interest Rate 51 42% 19 15% 32 15% 15% 42% 19 15% 32 7 25 5 29 5 5% 5 7 5% 30 34 7 5 Pretax Income Taxes Tax Rate Net Income 5% 5 38 6 16% 32 38 6 16% 32 23% 16% 23 29 Balance Sheet 100 131 166 16 15 55 166 18 53 53 Cash Accounts Receivable Days of Sales Outstanding (DSO) PP&E Accounts Payable Days of Payables Outstanding (DPO) Equity 18 53 47 20 33 12 47 13 13 10 61 68 68 68 100 121 144 144 29 32 32 Cash Flow Statement Net Income + Depreciation - Increase in Accounts Receivable 3 4 4 (1) (2) (2) 3 4 4 (1) (2) (2) 2 1 1 + Depreciation - Increase in Accounts Receivable + Increase in Accounts Payable - Capital Expenditures (CapEx) - Dividends Payout Ratio = Increase (Decrease) in Cash (8) (10) (10) (10) 6 8 9 9 26% 28% 28% 28% 31 35 35 We have created hypothesized scenarios for you to forecast: Hypothesized Scenario 1 Assume a new competitor enters the market and that drives Gross Margin to 25% What is the forecasted Net Income? 32 What is the forecasted Cash balance? 166 Hypothesized Scenario 2 Set the DSO equal to 10 days Leave the Gross Margin at 25% What is the forecasted Cash balance? See how the: Reduction of DSOs reduced Accounts Receivable' on the Balance Sheet and Impacted '-Increase in Accounts Receivable' on the Cash Flow Statement, Which impacts 'Cash' on the Balance Sheet. Hypothesized Scenario 3 Construct a new plant increasing CapEx to $100 (remember that this is a -100 in the model as it is a use of cash) Leave the Gross Margin at 25% and the DSO at 10% What is the forecasted Cash balance? What is the forecasted PP&E balance