Please answer using Excel showing formulas, I am totally lost. A-D

Business Valuation Bonus Team Problem BUS 6070 - Sp22 NOTE: You will work in teams of 2 or 3 (no more than 3) to provide an Excel solution to Canvas. Valuation Problem (Discounted Cash Flow Method) Red Company, a software company is considering the purchase of Blue Company, also a software company. After several days of due-diligence and analysis, Red Company has derived the following projected cash flow for Blue Company: Year 1 Year 2 Year 3 Year 4 Sales Revenue 5 18,000,000 S 24,000,000 5 27,500,000 5 28,500,000 Less: Va riable Costs (6,000,000) (10,000,000) (12,000,000) (12,500,000) Less: Fixed Costs (600,000) (600,000) (600,000) (600,000) Less: Depreciation (2,000,000) (2,000,000) (2,000,000) (2,000,000) Plus: Depreciation 2 000 000 2 000 000 2 000 000 2 000 000 Less: Working Capital (WC) Requirements (1,500,000) (900,000) (350,000) (100,000) Less: Capital Expenditure (CAPEX) Requirements (4,000,000) (3,000,000) (2,500,000) (2,000,000) In addition, Red Company believes that Blue Company's Free Cash Flow in Year 4 will grow 3.6% annually in perpetuity. You have also been provided the following information regarding Red Company: - . Red Company's current (book value) capital structure is 40% debt, 10% preferred, and 50% common, and its target capital structure is 50% debt, 5% preferred, and 45% common 0 Red Company's marginal tax rate is 25% Based upon the above information: a. Determine Red Company's WACC (discount rate) b. Determine the appropriate terminal value for Blue company c. Determine the business (or enterprise) value of Blue Company using the discounted cash flow method (Hint: use the NPV function in Excel to determine the present value of the future cash flows and terminal value). d. Assuming that Blue Company currently has $30,000,000 of debt outstanding, what is the value of Blue Com pany's equity