answer those question please

Enter formulas in the yellow cells to create a model. Industry Unit Mode 2016 2017 2018 2019 CAGR Estimated Total Market Size in Units 610 702 807 928 15% Growth Rate 15% 15% 15% Estimated Percent Direct 33% 40% 48% 58% 20% Direct Market Size in Units 203 281 387 534 Retail Market Size in Units 610 701 806 927 Company Competitor Company Unit Models 2016 2017 2018 2019 CAGR 2016 2017 2018 2019 CAGE Estimated Direct Market Share 50% 45% 40% 35% -11% 50% 55% 1 9% Estimated Retail Market Share 70% 75% 80%% 85% 7% 30% 25%% 20% -21% Direct Units Sol 102 126 155 187 23% 102 154 232 347 51% Retail Units Sold 427 526 645 788 23% 183 175 161 139 -9% Total Units Sold 528 652 800 975 23%% 285 330 394 486 20%% 0% 0%% Revenues Model 2016 2017 2018 2019 2016 2017 2018 200 CAGR 2019 0% 180 CAGR Priceto Direct 200 200 200 190 170 160 125 125 125 -6%% Priceto Retailers 125 125 0% 125 125 125 0% Direct Revenues 0% 0% Retail Revenues 0% Total Revenues Gross Profit Model 2016 2017 2018 2019 CAGE 2016 2017 2018 2019 CAGR Unit Cost 100 100 100 100 Direct Cost of Revenues 09% Retail Cost of Revenues Direct Gross Profit Retail Gross Profit Total Gross Profit EBIT ( Operating Profit) Model 2016 10% 2017 10% 2018 10% 2019 CAGR CAGR Direct SG&A Perce 5%% 10% 2016 10% 2017 2018 2019 10% Retail SGRA Percent 5% 5 %% 5% 10% 10% Direct SGRA Retail SG&A Direct EB IT Retail EBIT Total EB IT Use the model you have created to answer the following questions. What was the CAGR of the overall market in units? What was the CAGR of the'Direct Market Sizein Units"? What was the CAGR of the competitor's 'Total Revenues"? What was the CAGR of the competitor's priceto Direct What was the CAGR of the company's total EBIT Did either company earn the majority of profits in Direct sales. What was the impact of the competitors aggressive pricing strategy?You are responsible for developing a model that analyzes the potential investment, providing valuations of alternate strategies and a sensitivity analysis of the results. The following model should include only the incremental impact on the Income Statement, Balance Sheet and Cash Flow Statement. 33 Please enter formula in the yellow boxes below to develop the model. 2020 2021 2022 2023 2024 2025 Hints Income Statement Finished Goods Expense 200,000 200,000 200,000 200,000 200,000 Shown as positive because it is an expense that will cease to exist 38 Depreciation (60,000) (60,000 (60,000) (60,000) (60,000) 39 Labor 100,000) (100,000) (100,000) 100,000) (100,000) Overhead (10,000) (10,000) (10,000) (10,000) (10,000) EBIT 30,000 30,000 30,00 $0,000 30,000 Interest Income (5% interest rate) Interest Income is based on a 5% interest rate applied to prior year Net Cash (Cash minus Debt) Pretax 30,000 30,000 30,000 30,000 30,000 Tax (30%tax rate) 9,000 9,000 9,000 9,000 9,000 Net Income 21,000 21,000 21,000 21,000 21,000 46 47 Balance Sheet (change in account) 48 Cash (150,000) Cash is equal to that of the previous year plus Cash Generated PP&E 300,000 PP&E is equal to that of the previous year minus CapEx (shown on Cash Flow Statement as negative) minus Depreciation 50 Debt 150,000 Debt is equal to that of the previous year plus Issues (Retirement) of Debt Equity Equity is equal to that of the previous year plus Net Income (there are no Dividends) Cash Flow Statement Net Income Net Income from the Income Statement Depreciation Investment Required spread of the useful life (5 years) Cash Flow from Operations CapEx (300,000) Cash Flow from Investing (300,000) Issues (Retirement) Debt 150,000 Debt based on Percent Debt Financed with 1/5th retired each year 60 Cash Flow from Financing 150,000 61 Cash Generated (150,000) 63 Write a fomula that calculates the Net Present Value (NPV) of the cash flows in Row 61 using a 10% discount factor. 64 65 66 Change the 'Investment Required', cell D27, to $200,000 and enter the resulting NPV value (not the formula) in the yellow box below. 67 68 Keeping the $200,000 'Investment Required' change the 'Percent Debt Financed' to 0% and enter the resulting NPV in the yellow box below. 70 71 72 Below we present a Excel Data Table. The Excel Data Table functionality permits users to identify a set of potential value for two independent variables . 74 The functionality automatically analyzes a formula that is dependent on these two variables and presents the results of all combinations of the two independent variables. 75 The 'EXCEL Financial Functions' Sheet presents how to create a Data Table, but, the output is presented here for those that do not want to dive any deeper. 76Hints for Working on this Assignment This exercises is a bit like solving a puzzle. First, you do the easy formulas to fill in the box. So, first solve for the Revenues in the forecast year in column F by growing the prior year by the growth rate provided in red. #N/A We did this for you in the Revenue Box without shading. Again, solving the puzzle we fill in the blanks we know for sure. So, your next steps it calculate the historical Gross Profits (in D57 and E57) and the historical Gross Margin (in D58 and E58). Once you know these answers, you can apply the historical model to the forecast. This means you will forecast the Gross Margin and use that information to forecast the Gross Profits. Easy, right? Nowyou have learned how to build this model and can proceed with the rest of the exercise. #N/A Errors in the model will turn red. The exercise is designed such that an error early in the assignment will not adversely impact later grading. 2018 2019 2020E Relationship Notes Income Statement Revenues 10,048 11,053 Business Revenues is driven by the growth rate Growth Rate 10% Driver Cost of Revenues 6,029 6,742 Accounting Gross Profits Business Gross Profits is calculated as Revenues multiplied by Gross Margin Gross Margin Driver Operating Expenses (OpEx) 1500 1700 Business OpEx is calculated as Revenues multiplied by OpEx Percent Revenues OpEx Percent of Revenues Driver EBIT Accounting EBIT is calculated as Gross Profits minus Operating Expenses (OpEx) Net Interest Income (Expense) 73 56 73 NA 5% Interest Income is calculated as Interest Rate multiplied by Cash (as there is no Debt Interest Rate Driver Pretax Income Accounting Pretax Income is calculated as EBIT plus Net Interest Income (Expenses) Taxes Business Taxes is calculated as Pretax Profits multiplied by Tax Rate Tax Rate Driver Net Income Balance Sheet Cash 1,452 Accounting Cash is previous year Cash plus Increase (Decrease) in Cash Accounts Receivable 1.500 1,600 Accounts Receivable is calculated as Revenues multiplied by Days of Sales Outstanding (DSO) / 365 Days of Sales Outstanding (DSO) Days of Sales Outstanding (DSO) is calculated as Accounts Receivable / Revenues * 365 PP&E 15,832 Accounting PP&E in a year is calculated as PP&E in the previous year plus CapEx in the current year minus Depreciation in the current year. Accounts Payable 1,000 1,200 Business Accounts Payable is calculated as Cost of Revenues multiplied by Days of Payables Outstanding (DPO) / 365 Days of Payables Outstanding (DPO) Driver Days of Payables Outstanding (DPO) is calculated as Accounts Payable / Cost of Revenues * 365 Equity 2,500 Equity in a year is calculated as Equity in the previous year plus Net Income minus Dividends ALERT: Because the value of the Dividends are shown as a negative on the Cash Flow Statement the value should be added to subtract Dividends Hints for Working on the Cash Flow Statement Cash Flow Statements always aggregate (sum up) all the Inflows and Outflows of Cash Flow during a period. Inflows of Cash are positive numbers and Outflows of Cash are negative numbers on the Cash Flow Statement. To help you, we show You should remember that Increases in Assets, actually decrease Cash so you are subtracting an increase. In contrast, increases in Liabilities, Debt and Equity increase Cash so you are adding an increase. Capital Expenditures (CapEx) are investments of Cash and an Outflow of Cash on the Cash Flow Statement. As an investment, CapEx isn't less than zero, but ber because it is an Outflow Dividends are an Outflow of Cash and also shown as a negative number, but Dividends are never less than zero Cash Flow Statement Net Income Accounting + Depreciation 1,583 Accounting The company uses 10-year straight line depreciation (10% of PP&E in the previous year) Increase in Accounts Receivable Minus the increase in Accounts Receivable (the Accounts Receivable in the current year minus that in the previous year) + Increase in Accounts Payable Accounting Increase in Accounts Payable (the Accounts Payable in the current year minus that in the previous year) Capital Expenditures (CapEx) (800) (1,000) (1,000) Accounting Dividends Accounting Dividends is the negative of the Net Income multiplied by Payout Ratio Payout Ratio Business = Increase (Decrease) in Cash 783 Accounting