Question $1$

$3\mathrm{.}85/10$

Submit

Question Content Area

Excel Activity: Forecasting Financial Statements

Morrissey Technologies Inc.$\text{'}$s $2021$ financial statements are shown here.

Morrissey Technologies Inc.: Balance Sheet as of December $31,2021$

Cash $ $204,000$ Accounts payable $ $408,000$

Receivables $408,000$ Accrued liabilities $204,000$

Inventories $816,000$ Notes payable $60,200$

Total current assets $ $1,428,000$ Total current liabilities $ $672,200$

Long$-$term debt $102,000$

Fixed assets $1,632,000$ Common stock $2,040,000$

Retained earnings $245,800$

Total assets $ $3,060,000$ Total liabilities and equity $ $3,060,000$

Morrissey Technologies Inc.: Income Statement for December $31,2021$

Sales $ $4,080,000$

Operating costs including depreciation $3,718,914$

EBIT $ $361,086$

Interest $21,086$

EBT $ $340,000$

Taxes $(25\%)85,000$

Net income $ $255,000$

Per share data:

Common stock price $ $51\mathrm{.}00$

Earnings per share $($EPS$)$ $ $2\mathrm{.}55$

Dividends per share $($DPS$)$ $ $1\mathrm{.}02$

Suppose that in $2022,$ sales increase by $12\%$ over $2021$ sales. The firm currently has $100,000$ shares outstanding. It expects to maintain its $2021$ dividend payout ratio and believes that its assets should grow at the same rate as sales. The firm has no excess capacity. However, the firm would like to reduce its operating costs$/$sales ratio to $87\mathrm{.}5\%$ and increase its total liabilities$-$to$-$assets ratio to $35\%.($It believes its liabilities$-$to$-$assets ratio currently is too low relative to the industry average.$)$ The firm will raise $40\%$ of the $2022$ forecasted interest$-$bearing debt as notes payable, and it will issue long$-$term bonds for the remainder. The firm forecasts that its before$-$tax cost of debt $($which includes both short$-$ and long$-$term debt$)$ is $12\mathrm{.}5\%.$ Assume that any common stock issuances or repurchases can be made at the firm's current stock price of $$51.$

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.

Download spreadsheet Forecasting Financial Statements$-$fb$756$a$.$xlsx

Construct the forecasted financial statements assuming that these changes are made. What are the firm's forecasted notes payable and long$-$term debt balances? What is the forecasted addition to retained earnings? Round your answers to the nearest cent.

Morrissey Technologies Inc.: Pro Forma Income Statement for December $31,2022$

Sales $

fill in the blank $2$

Operating costs including depreciation

fill in the blank $3$

Earnings before interest and taxes $($EBIT$)$ $

fill in the blank $4$

Interest

fill in the blank $5$

Earnings before taxes $($EBT$)$ $

fill in the blank $6$

Taxes $(25\%)$

fill in the blank $7$

Net income $($NI$)$ $

fill in the blank $8$

Dividends $

fill in the blank $9$

Addition to retained earnings $

fill in the blank $10$

Morrissey Technologies Inc.: Pro Forma Balance Sheet as of December $31,2022$

Assets

Cash $

fill in the blank $11$

Receivables

fill in the blank $12$

Inventories

fill in the blank $13$

Total current assets $

fill in the blank $14$

Fixed assets

fill in the blank $15$

Total assets $

fill in the blank $16$

Liabilities and Equity

Accounts payable $

fill in the blank $17$

Accrued liabilities

fill in the blank $18$

Notes payable

fill in the blank $19$

Total current liabilities $

fill in the blank $20$

Long$-$term debt

fill in the blank $21$

Total liabilities $

fill in the blank $22$

Common stock

fill in the blank $23$

Retained earnings

fill in the blank $24$

Total common equity $

fill in the blank $25$

Total liabilities and equity $

fill in the blank $26$

If the profit margin remains at $6\mathrm{.}25\%$ and the dividend payout ratio remains at $40\%,$ at what growth rate in sales will the additional financing requirements be exactly zero? In other words, what is the firm's sustainable growth rate? $($Hint: Set AFN equal to zero and solve for g$.)$ Round your answer to two decimal places.

fill in the blank $27$

$\%$