What are the excel formulas necessary to get the numbers in the yellow column in the top sensitivity analysis box? The numbers there already are manually put there.
E G M N 0 P Q B C D Proforma Income Statement 2017 2018 2019 1,000.00 $ 1,050.00 $ 1.102.50 $ 650.00 652.50 $ 716G35 350.00 367.50 $ 385.83 20.00 21.00 S 22.05 30.CUS 346.50 S 31.5 15.00 $ 15.00$ 15.00$ 315.00$ 331.50 348.835 1.025 $ 116.03 $ 122.09 $ 204.75S 215.495 226745 $ $ S S $ 5 $ $ $ H Assumption Weighted Average cost of capital Percentage Debt Percentage Equity Operating Tax Rate Cost of Debt |out deity WACC 2020 2021 2022E 1.157 63 1,215.51 $ 1 276.29 792.45 $ 790.00 $ 829.58 405.17 425 435 44470 2315 24.11 25.53 382 026 401.12 $ 421.17 15.00$ 15.00$ 15.00 367.02 $ 386.12$ 405.17 128.46 135.14 142.16 238 56S 250.98 264.01 5.00% 65 00 2.00% 35.00 J K Assumptions: Income Statement 30.00 Sales Growth 70.00% Operating Expenses to Sales 35.00% Depreciation to Sales 1.50 Operating Tax Rate B.SOX 0.001325 2 3 Sales 4 Operating Experts 5 EBITDA 6 Depreciation perse 7 EBIT B interest Expense 9 EBT 10 Tax Egence 11 Net in me 12 13 14 Free Cash Flow 15 Discount Factor 16 Terminal Value 17 Dienunter 18 19 Discounted Cash Flow 20 Present Value 21 22 Shares Outstanding in mill 23 24 25 26 27 $ $ $ S $ $ S Free Cash Flow 123.59 $ 1.07 135.82 $ 1.14 148.36 $ 121 161.53 $ 1.29 $ 175.35 1.38 3,861.03 1. Sensitivity Analysis: Tax Rate 20.00% 22.50% 25.00% 27.50% 30.000 32.50% 35.00% 37.50% 40.00% 42.50% 45.00% 47.50 50.00% S 10.03 0. 9.75 9.90 9.85 9.92 0.97 10.02 10.09 10.15 10.21 10.27 10.3 10. You will need to create this table highlights the sensitivity to the given tax rates $ 116.17 119.45 $ 122 36 $ S 124.94 $ $ 2927.80 3.410.72 340.00 S S $ Implied Price $ 10.03 NOTE: THIS TAB AND NUMBERS HAVE NOTHING TO DO WITH THE PRO FORMA AND VALUATION TABS YOU CREATED. IT IS INDEPENDENT $ 10,03 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 Sensitivity Analysis: Tax Rate 20.00% 22.50% 25.00% 27.50% 30.00% 32.50% 35.00% 37.50% 40.00% 42.50% 45.000 47.50% 50.006 E G M N 0 P Q B C D Proforma Income Statement 2017 2018 2019 1,000.00 $ 1,050.00 $ 1.102.50 $ 650.00 652.50 $ 716G35 350.00 367.50 $ 385.83 20.00 21.00 S 22.05 30.CUS 346.50 S 31.5 15.00 $ 15.00$ 15.00$ 315.00$ 331.50 348.835 1.025 $ 116.03 $ 122.09 $ 204.75S 215.495 226745 $ $ S S $ 5 $ $ $ H Assumption Weighted Average cost of capital Percentage Debt Percentage Equity Operating Tax Rate Cost of Debt |out deity WACC 2020 2021 2022E 1.157 63 1,215.51 $ 1 276.29 792.45 $ 790.00 $ 829.58 405.17 425 435 44470 2315 24.11 25.53 382 026 401.12 $ 421.17 15.00$ 15.00$ 15.00 367.02 $ 386.12$ 405.17 128.46 135.14 142.16 238 56S 250.98 264.01 5.00% 65 00 2.00% 35.00 J K Assumptions: Income Statement 30.00 Sales Growth 70.00% Operating Expenses to Sales 35.00% Depreciation to Sales 1.50 Operating Tax Rate B.SOX 0.001325 2 3 Sales 4 Operating Experts 5 EBITDA 6 Depreciation perse 7 EBIT B interest Expense 9 EBT 10 Tax Egence 11 Net in me 12 13 14 Free Cash Flow 15 Discount Factor 16 Terminal Value 17 Dienunter 18 19 Discounted Cash Flow 20 Present Value 21 22 Shares Outstanding in mill 23 24 25 26 27 $ $ $ S $ $ S Free Cash Flow 123.59 $ 1.07 135.82 $ 1.14 148.36 $ 121 161.53 $ 1.29 $ 175.35 1.38 3,861.03 1. Sensitivity Analysis: Tax Rate 20.00% 22.50% 25.00% 27.50% 30.000 32.50% 35.00% 37.50% 40.00% 42.50% 45.00% 47.50 50.00% S 10.03 0. 9.75 9.90 9.85 9.92 0.97 10.02 10.09 10.15 10.21 10.27 10.3 10. You will need to create this table highlights the sensitivity to the given tax rates $ 116.17 119.45 $ 122 36 $ S 124.94 $ $ 2927.80 3.410.72 340.00 S S $ Implied Price $ 10.03 NOTE: THIS TAB AND NUMBERS HAVE NOTHING TO DO WITH THE PRO FORMA AND VALUATION TABS YOU CREATED. IT IS INDEPENDENT $ 10,03 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 Sensitivity Analysis: Tax Rate 20.00% 22.50% 25.00% 27.50% 30.00% 32.50% 35.00% 37.50% 40.00% 42.50% 45.000 47.50% 50.006
