Answered step by step
Verified Expert Solution
Question
00
1 Approved Answer
please show all work/formulas on Excel N DONNA'S BALANCE SHEET AND INCOME STATEMENT 2 Value Drivers 3 Sales growth 15% 4 Current assets/sales 20% This
please show all work/formulas on Excel N DONNA'S BALANCE SHEET AND INCOME STATEMENT 2 Value Drivers 3 Sales growth 15% 4 Current assets/sales 20% This sheet contains Donna Company's B/S, US and value drivers. Assume that Donna Company pays $6,000 5 Current liabilities/sales 14% of its debt every year and that the interest rate, paid and earned, is on average debt and cash balance, 6 Net foxed assets/sales 80% respectively, and the depreciation is on average foxed assets. Construct a 5-year pro forma model for Donna 7 Costs of goods sold sales 45% Company and compute free cash flows, 8 Depreciation rate 10% Assume that the firm's terminal value growth rate is 3% and cost of capital is 15%, find the value of equity. 9 Interest rate on deb 8% Perform a sensitivity analysis with cost of capital: 10%, 15% and 20%; terminal growth rate: 1% 3% and 5% 10 Interest earned on average cash balances 5% 11 Tax rate 36% 12 Dividend payout ratio 30% 13 Annual debt repayments 6.000 14 15 Income statement 16 Year 0 1 3 17 Sales 45.000 18 Cost of goods sold (COGS) 33.000 19 Depreciation -4.000 20 Interest on cash 80 21 Interest payments on debt -150 22 Profit before tax 7.930 23 Tax (38%) -2.855 24 Profit after tax 5,075 25 Dividend -1.523 26 Retained earnings 3.553 27 28 Balance sheet 29 Assets 30 Cash 10000 31 Current assets 4.700 32 Foed assets 33 Al cost 47.000 34 Accumulate depreciation - 14.000 35 Net Fored assets 33.000 36 Total assets 47.700 37 38 Liabilities and Equity 39 Current liabilities 4.000 40 Debt 30.000 41 Equity 42 Equity 10.000 43 Accumulated retained earnings 3.700 44 Total liabilities and equity 47,700 45 46 47 Free cash flow calculation 48 Year 1 2 3 5 49 50 51 52 53 54 55 56 Free cash flow 0 SSSSS88 N DONNA'S BALANCE SHEET AND INCOME STATEMENT 2 Value Drivers 3 Sales growth 15% 4 Current assets/sales 20% This sheet contains Donna Company's B/S, US and value drivers. Assume that Donna Company pays $6,000 5 Current liabilities/sales 14% of its debt every year and that the interest rate, paid and earned, is on average debt and cash balance, 6 Net foxed assets/sales 80% respectively, and the depreciation is on average foxed assets. Construct a 5-year pro forma model for Donna 7 Costs of goods sold sales 45% Company and compute free cash flows, 8 Depreciation rate 10% Assume that the firm's terminal value growth rate is 3% and cost of capital is 15%, find the value of equity. 9 Interest rate on deb 8% Perform a sensitivity analysis with cost of capital: 10%, 15% and 20%; terminal growth rate: 1% 3% and 5% 10 Interest earned on average cash balances 5% 11 Tax rate 36% 12 Dividend payout ratio 30% 13 Annual debt repayments 6.000 14 15 Income statement 16 Year 0 1 3 17 Sales 45.000 18 Cost of goods sold (COGS) 33.000 19 Depreciation -4.000 20 Interest on cash 80 21 Interest payments on debt -150 22 Profit before tax 7.930 23 Tax (38%) -2.855 24 Profit after tax 5,075 25 Dividend -1.523 26 Retained earnings 3.553 27 28 Balance sheet 29 Assets 30 Cash 10000 31 Current assets 4.700 32 Foed assets 33 Al cost 47.000 34 Accumulate depreciation - 14.000 35 Net Fored assets 33.000 36 Total assets 47.700 37 38 Liabilities and Equity 39 Current liabilities 4.000 40 Debt 30.000 41 Equity 42 Equity 10.000 43 Accumulated retained earnings 3.700 44 Total liabilities and equity 47,700 45 46 47 Free cash flow calculation 48 Year 1 2 3 5 49 50 51 52 53 54 55 56 Free cash flow 0 SSSSS88
please show all work/formulas on Excel
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started