first picture are the instructions w/ an example. second picture are the numbers i will be using to solve question A and B and orange cells also need to be calculated

assignment, all inputs should be labeled as named ranges (for example, don't call it "c2", call it "startup_cost". It is easier to do this as you go, which is why I want to warn you up front. Sheet 1 Sheet 1 should be named "WACC". On this sheet you will need to take in Inputs necessary to calculate the WACC. Use the screenshot below as a guide. Cells with no/white background are entered manually. Calculate cells with an orange background using the other inputs. All inputs and outputs need to be formatted appropriately (as percentages, dollars, etc.). The following two questions can be answered in the excel document. a) Why is the corporate tax rate only applied to the rate of return on debt (not common stock or preferred stock)? b) If the stock market crashes, and the prices of your common stock and preferred stock drop significantly, what happens to your WACC? A B D 1 7 3 4 Dividend Growth Last Dividend Stock Price Return on Equity 596 $1.20 $56 7.2526 5 6 7 Prefered Stock Price Prefered Stock Dividend Return on Preferred Stock $86 $4.30 5.00% 9 10 11 12 13 Return on debt (YTM) Corporate tax rate 6% 30%. 15 16 17 11 19 20 21 22 Cammon shares outstanding 34,000,000 Preferred stock outstanding 5,000,000 Value of debt oustanding $500,000,000 Value of Common Stock $1,904,000,000 Value of Preferred Stock $430,000,000 Value of the firm $2,834,000,000 WACC 6.371 WACG NPW 10 als Data Window Help AutoSave Assignment 8 Insert Draw Page Layout Formulas Data Review View X lo Calibri (Body) 12 A A a. Av % Tell me Id Conditional Formatting 2 Formats Table Call Styles HAH BIU Number Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. x fe B D E F G H Dividend Growth Last Dividend Stock Price Return on Equity 6% $1.25 $60 Preferred Stock Price preferred Stock Dividend Return on Preferred Stock $90 $4.55 Return on debt Corporate tax rate 8% 35% 37,000,000 7,000,000 $600,000,000 Common shares outstanding Preferred stock outstanding Value of debt outstanding value of common stock value of preferred stock value of the firm WACC A) Why is the corporate tax rate only applied to the rate pf return on debe (not common stock or preferred stock)? B) if the stock market crashes, and the prices of your common stock and preferred stock drop significantly, what happens to your WACC? WACC NPV and IRR +