Excel Activity: Financial Statements, Cash Flow, and Taxes

Laiho Industries's $2020$ and $2021$ balance sheets $($in thousands of dollars$)$ are shown.

Laiho Industries: Balance Sheets as of December $31($thousands of dollars$)$

The data has been collected in the Microsoft Excel file below. Download the spreadsheet and perform the

required analysis to answer the questions below. Do not round intermediate calculations. Enter your

answers in thousands. For example, an answer of $$1$ thousand should be entered as $1,$ not $1,000.$ Round

your answers to the nearest whole number. Use a minus sign to enter negative values, if any.

a$.$ Sales for $2021$ were $$472,150,000,$ and EBITDA was $16\%$ of sales. Furthermore, depreciation and amortization were $18\%$ of net fixed assets, interest

was $$8,903,000,$ the corporate tax rate was $25\%,$ and Laiho pays $47\mathrm{.}25\%$ of its net income as dividends. Given this information, construct the firm's

$2021$ income statement.

Laiho Industries: Income Statement for Year Ending December $31,2021$

$($thousands of dollars$)$

$2021$

b$.$ Construct the statement of stockholders' equity for the year ending December $31,2021,$ and the $2021$ statement of cash flows. Hint: The difference in

accumulated depreciation from one year to the next is the annual depreciation expense for the year.

Laiho Industries: Statement of Stockholders' Equity, December $31,2021($thousands of dollars$)$

Common Stock

Retained Earnings

Total Stockholders'

Equity

Balances, December $31,2020$

$

Common stock issue

$2021$ Net income

Cash dividends

Addition to retained earnings

Balances, December $31,2021$

Laiho Industries: Statement of Cash Flows for $2021($thousands of dollars$)$

$2021$

Operating Activities

Net income

Depreciation and amortization

Increase in accounts payable

Increase in accruals

Increase in accounts receivable

Increase in inventories

Net cash provided by operating activities

Investing Activities

Additions to property, plant, and equipment

Net cash used in investing activities

Financing Activities

Increase in notes payable

Increase in long$-$term debt

Increase in common stock

Payment of common dividends

Net cash provided by financing activities

Summary

Net increase$/$decrease in cash

Cash at the beginning of the year

Cash at the end of the year

c$.$ Calculate $2020$ and $2021$ net operating working capital $($NOWC$)$ and $2021$ free cash flow $($FCF$).$ Assume the firm has no excess cash.

NOWC ${?}_{2020}$ : $

thousand

NOWC ${?}_{2021}$ : $

thousand

$FC{F}_{2021}$ : $

thousand

d$.$ If Laiho increased its dividend payout ratio, what effect would this have on corporate taxes paid? What effect would this have on taxes paid by the

company's shareholders?

If Laiho increased its dividend payout ratio, the firm would pay the same amount of $vv$ corporate taxes and the company's shareholders would pay

taxes on the dividends they would receive.

e$.$ Assume that the firm's after$-$tax cost of capital is $10\mathrm{.}5\%.$ What is the firm's $2021$ EVA?

$

thousand

f$.$ Assume that the firm's stock price is $$24$ per share and that at year$-$end $2021$ the firm has $10$ million shares outstanding. What is the firm's MVA at

year$-$end $2021?$

$

thousandc. Calculating $2020$ and $2021$ net operating working capital $($NOWC$)$ and $2021$ free cash flow $($FCF$)$

Excesss cash

$NOW{C}_{2020}($in thousands of dollars$)$

$NOW{C}_{2021}($in thousands of dollars$)$

$FC{F}_{2021}($in thousands of dollars$)$

e$.$ Calculating the firm's $2021$ EVA

After$-$tax cost of capital

$EV{A}_{2021}($in thousands of dollars$)$

f$.$ Calculating the firm's MVA at year$-$end $2021$

$MV{A}_{2021}($in thousands of dollars$)$