Need assistance with the below section of the workbook. Please include the cell formula in the explanation for better understanding.
A B C D E F G H Free Cash Flow Valuation 2 3 (in '000 $) 2021 2022 2023 2024 2025 TV 4 Income before taxes 1,737 1,834 1,966 2,120 2,301 5 + Interest 6 = Income before interest and taxes (EBIT) 7 8 EBIAT 9 - Change in Working Capital 10 - Change in Other assets Growth rate 11 Free Cash Flow (FCF) 3.0% 12 PV of FCF = EV 13 - Existing Debt 14 = Value of equityB C D E F G H Actual Projected Income Statement (in '000 $) 2020 2021 2022 2023 2024 2025 Notes 11 Research and development 1,022 1.068 1,116 1.166 1.219 1.274 4.5% 12 Service expense 579 608 638 670 704 739 Increase at a 5.0% 13 Sales and Administrative 481 491 500 510 521 531 yearly rate of 2.0% 14 Depreciation amortization 332 342 352 363 374 385 3.0% 15 Total Operating Expenses 3,512 3,650 3,795 3,945 4,101 4.264 16 Income before taxes 1.656 1,737 1.834 1,966 2,120 2,301 18 Income tax expense/(benefit) 348 (365) (385) (413) (445) (483) 19 Net Income 1,308 1,372 1,449 1,553 1,675 1,818 20 Balance Sheet 21 Cash and investments 1,866 1,959 2,057 2,160 2.268 2.382 5.0% 22 Accounts receivable 1,391 1.461 1.534 1.610 1,691 1,775 Increase at a 3.0% 23 Inventory and supplies, net 1,144 1.195 1.249 1.305 1.364 1.426 yearly rate of 4.5% 24 Other 188 196 205 215 224 234 4.5% 25 Current Assets 4.589 4.812 5.045 5.290 5,547 5,817 26 Other 6,120 6,395 6.683 6.984 7.298 7,627 Increase at 4.5% 27 Total Assets 10,709 11,207 11,729 12,274 12,846 13,443 28 29 Bank Loan 4,215 3,377 2.485 1,514 447 (735) Plug account 30 Payables 265 276 287 298 310 322 4.0% 31 CPLTD 48 49 50 Increase at a 51 52 53 2.0% 32 Other 99 101 103 105 107 109 yearly rate of 2.0% 33 Current Liabilities 4.627 3.802 2,924 1.968 916 (250) 34 LTD 1,988 1,939 1,889 1,838 1,786 1,733 35 Equity 4.094 5.466 6.915 8.468 10,143 11,961 36 Total Liabilities & Equity 10,709 11,207 11,729 12,274 12,846 13,443 37 38 Working Capital 4.177 4.386 4,606 4.836 5.078 5.332 Exclude Bank LoanA B C D Component Maxwell Barston Notes Net Income (MS) 31.00 39.00 3 Earnings per share (EPS) 3.50 4.00 4 # of shares (M) 8.86 9.75 Net Income / EPS 5 Price per share 16.00 19.00 6 Market Value - Equity (M) 142 185 # of shares x price per share Market Value - Debt (M) 95 119 8 Market Value - Total (M) 237 304 9 - % Debt 40% 39% Debt as a per cent of Total MV 10 - % Equity 60% 61% Equity as a per cent of Total MV 11 Beta (levered) 0.80 0.75 as reported in Value Line 12 Beta (unlevered) 0.48 0.46 Beta (levered) x % Equity 13 Average Beta (unlevered) 0.47 Average of the two Beta (unlevered) 14 15 Values for Wilson: 16 % Debt 40.0% 17 % Equity 60.0% 18 Beta (relevered) 0.78 19 Risk free rate 3.0% 20 Market risk premium 6.0% 21 Expected equity return (CAPM) 7.7% 22 Expected cost of debt 6.0% 23 Tax rate 21.0% 24 Weighed average cost of capital (WA 6.5%
