Chapter 16, Problem 15SP

Comprehensive/Spreadsheet Problem

  16-15 FORECASTING FINANCIAL STATEMENTS Use a spreadsheet model to forecast the financial statements in Problems 16-13 and 16-14.

16-13 ADDITIONAL FUNDS NEEDED Morrissey Technologies Inc.'s 2014 financial statements arc shown here.

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

Morrissey Technologies Inc.: Income Statement for December 31, 2014

Sales	$3.600.000
Operating costs including depreciation	3,279,720
E81T	$ 320.280
Interest	20.280
EST	$ 300.000
Taxes (40%}	120,000
Net Income	$ 180.000
Per Share Data:
Common stock price	$45.00
Earnings per share (EPS)	$ 1.80
Dividends per share (DPS)	$ 1.08

Suppose that in 2015, sales increase by 10% over 2014 sales. 1hc firm currently has 100,000 shares outstanding. It expects to maintain its 2014 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.5% and increase its total liabilities-to-assets ratio to 30%. (It believes its liabilities – to-assets ratio currently is too low relative to the industry average.) The firm will raise JOO/o of the 2015 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-term and long-term debt) is 12.5%. Assume that any common stock issuances or repurchases can be made at the firm's current stock price of S45.

1. a. 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?
2. b. If the profit margin remains at S'/o and the dividend payout ratio remains at 60%, 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.)

16-14 EXCESS CAPACITY Krogh Lumber's 2014 financial statements are shown here.

Krogh Lumber: Balance Sheet as of December 31, 2014 (Thousands of Dollars)

Krogh Lumber: Income Statement for December 31, 2014 (Thousands of Dollars)

Sales	$36,000
Operating costs including depredation	30,783
Earnings before interest and taxes	$ 5,217
Interest	1,017
Earnings before taxes	$ 4,200
Taxes (40%)	1680
Net income	$ 2.520
Dividends (60%}	$ 1,512
Addition to retained earnings	$ 1,008

1. a. Assume that the company was operating at full capacity in 2014 with regard to all items except fixed assets; fixed assets in 2014 were being utilized to only 75% of capacity. By what percentage could 2015 sales increase over 2014 sales without the need for an increase in fixed assets?
2. b. Now suppose 2015 sales increase by 25% over 2014 sales. Assume that Krogh cannot sell any fixed assets. AU assets other than fixed assets will grow at the same rate as sales; however, after reviewing industry averages, the firm would like to reduce its operating costs/sales ratio to 82% and increase its total liabilities-to-assets ratio to 42%. The firm will maintain its 60% dividend payout ratio, and it currently has 1 million shares outstanding. The firm plans to raise 35% of its 2015 forecasted interest-bearing debt as notes payable, and it will issue bonds for tile remainder. The firm forecasts that its before-tax cost of debt (which includes both short-term and long-term debt) is 11%. Any stock issuances or repurchases will be made at the firm's current stock price of $40. Develop Krogh's projected financial statements like those shown in Table 16.2 What are the balances of notes payable, bonds, common stock, and retained earnings?

Summary Introduction

To prepare: The forecast the financial statement of M Incorporation and KL for the year ended 2015 using spreadsheet excel in Problem 16-13.

Additional Funds Needed (AFN) Equation:

The AFN equation explains the amount of money that a company needs to fulfill the financial needs of the company. It gives the information related to the external financing, as the options available to a company to finance through external financing methods. This equation basically gives a new capital structure that includes an optimum mix of debt, preferred and common stock.

Financial Statement:

The financial statement is final accounts of the company. Financial statement of the company contains the income statement, statement of retained earnings, balance sheet and cash flow statement.

Explanation of Solution

Prepare Income statement,

Table (1)

Prepare statement of retained earnings statement

Table (2)

Table (3)

Working notes:

Calculate increase in sales

$\begin{array}{c}\text{Sales}=\$3,600,000+\left(\$3,600,000\times 10\%\right)\\ =\$3,600,000+\$360,000\\ =\$3,960,000\end{array}$

Calculate operating cost including depreciation

$\begin{array}{c}\text{Operating}\text{costs}\text{includying}\text{depreciation}=\$3,960,000\times 87.5\%\\ =\$3,465,000\end{array}$

Calculate the dividend amount

$\begin{array}{c}\text{Dividend}=\text{Dividend per share}\times \text{Number of outstanding shares}\\ =\$1.08\times 100,000\\ =\$108,000\end{array}$

Calculate dividend payout ratio

$\begin{array}{c}\text{Dividend payout ratio}=\frac{\text{Dividend}}{\text{Net Income}}\\ =\frac{108,000}{180,000}\\ =60\%\end{array}$

Calculate AFN equation,

$\begin{array}{c}\text{AFN}=\left[\begin{array}{l}\left(\left(\frac{\$2,700,000}{\$3,600,000}\right)\times \$360,000\right)-\left(\left(\frac{\$596,000}{\$3,600,000}\right)\times \$360,000\right)\\ -\left(\left(0.05\times \$3,960,000\right)\times \left(1-0.60\right)\right)\end{array}\right]\\ =\$270,000-\$59,600-\$79200\\ =\$131200\end{array}$

Calculate the distribution of additional funds

$\begin{array}{c}\text{Additional Funds}=\text{Notes payable }+\text{ Long term bonds}\\ =\text{AFN}\left(30\%\right)+\text{AFN}\left(70\%\right)\\ =\$131,200\left(30\%\right)+\$131,200\left(70\%\right)\\ =\$39,360+\$91,840\\ =\$131,200\end{array}$

Calculation of interest on bonds

$\begin{array}{c}\text{Interest on bonds}=\$20,280+\left(\$91,840\times 12.5\%\right)+\left(\$39,360\times 12.5\%\right)\\ =\$20,280+\$11,480+\$4,920\\ =\$36,680\end{array}$

Calculation of taxes is

$\begin{array}{c}\text{Taxes}=\$463,240\times 40\%\\ =\$185,296\end{array}$

Calculate increase in cash

$\begin{array}{c}\text{Cash}=\$180,000+\left(\$180,000\times 10\%\right)\\ =\$180,000+\$18,000\\ =\$198,000\end{array}$

Calculate increase in receivables

$\begin{array}{c}\text{Receivables}=\$360,000+\left(\$360,000\times 10\%\right)\\ =\$360,000+\$36,000\\ =\$396,000\end{array}$

Calculate increase in inventories

$\begin{array}{c}\text{Inventories}=\$720,000+\left(\$720,000\times 10\%\right)\\ =\$720,000+\$72,000\\ =\$792,000\end{array}$

Calculate increase in fixed assets

$\begin{array}{c}\text{Fixed assets}=\$1,440,000+\left(\$1,440,000\times 10\%\right)\\ =\$1,440,000+\$144,000\\ =\$1,584,000\end{array}$

Calculation of notes payable

$\begin{array}{c}\text{Notes payable }=\$56,000+\$39360\\ =\$95,360\end{array}$

Calculate total liability

$\begin{array}{c}\text{Total Liability}=\text{Total assets}\times 30\%\\ =\$2,970,000\times 30\%\\ =\$891,000\end{array}$

Calculate accrued liability

$\begin{array}{c}\text{Accrued liability}=\$180,000+\text{Accrued Interest}\\ =\$180,000+\$114,80+\$4,920\\ =\$196,400\end{array}$

Calculate the amount of accounts payable

$\begin{array}{c}\text{Accounts payable}=\text{Total liabilities}-\text{Notes payable-Accrued liability}-\text{Bonds}\\ =\$891,000-\$95,360-\$196,400-\$191,840\\ =\$407,400\end{array}$

Calculation of dividend 2015

$\begin{array}{c}\text{Dividend}=\text{Net income}\times 60\%\\ =\$277,944\times 60\%\\ =\$166,766.4\end{array}$

Summary Introduction

To compute: The growth in sales using AFN equation and forecast the financial statement in Problem 16-13.

Explanation of Solution

Given:

Sales in 2014 is $3,600,000

Sales in 2015 is $3,960,000

Current liability at the end of 2014 is $596,000. It includes $360,000 of accounts payable, $56,000 of notes payable, and $180,000 of accrued liability

Forecasted Profit margin is 5%

Forecasted retention ratio is 60%.

The formula to calculate the additional funds is

$\begin{array}{c}\text{AFN}=\left(\begin{array}{l}\text{Projected}\text{increase}\text{in}\text{assets}\\ -\text{Spontaneous}\text{increase}\text{in}\text{liabilities}\\ -\text{Increase}\text{in}\text{retained}\text{earnings}\end{array}\right)\\ =\left(\frac{{\text{A}}_0}{{\text{S}}_0}\right)\times \Delta \text{S}-\left(\frac{{\text{L}}_0}{{\text{S}}_0}\right)\times \Delta \text{S}-{\text{MS}}_1\left(1-\text{payout}\right)\end{array}$

Where,

• ${\text{A}}_0$ is original assets.
• ${\text{S}}_0$ is current sales.
• ${\text{L}}_0$ is original liabilities.
• $\Delta \text{S}$ is increase in sales.
• ${\text{MS}}_1$ is profit margin.

Substitute $2,700,000 for current assets, $3,600,000 for current sales, $3,960,000 for expected sales, assume growth rate of sales is g

$\begin{array}{c}\text{AFN}=\left[\begin{array}{l}\left(\left(\frac{\$2,700,000}{\$3,600,000}\right)\times \text{g}\right)-\left(\left(\frac{\$596,000}{\$3,600,000}\right)\times \text{g}\right)\\ -\left(\left(0.05\times \$3,960,000\right)\times \left(1-0.60\right)\right)\end{array}\right]\\ 0=0.75\text{g}-0.165g-\$79200\\ 0.585\text{g}=\$79,200\\ \text{g}=\frac{\$79,200}{0.585}\\ \text{g}=\$135,384.615\end{array}$

Conclusion

Thus, the growth in sales of M Incorporation is $135,384.615.

Summary Introduction

To find: The percentage increase in sales and forecast the financial statement in Problem 16-14.

Explanation of Solution

Current sale is $36,000 and capacity utilization is 75%.

$\begin{array}{c}\text{Sale}\text{at}100\%\text{capacity}=\frac{\text{Current}\text{sales}}{\text{Current}\text{utilization}}\times 100\%\\ =\frac{\$36,000}{75\%}\times 100\%\\ =\$48,000\end{array}$

Increase in sale $12,000 $(\$48,000-\$36,000)$

Percentage increase in sale is 33.33% $\left(\frac{12,000}{36,000}\times 100\right)$

Conclusion

Thus, increases in sales are $12,000 and its percentage is 33.33%.

Summary Introduction

To prepare: Projected financial statement and forecast the financial statement in Problem 16-14.

Explanation of Solution

Prepare Income statement:

Table (4)

Prepare statement of retained earnings:

Table (5)

Prepare Balance sheet:

Table (5)

Working Note:

Calculate increase in sales

$\begin{array}{c}\text{Sales}=\$36,000+\left(\$36,000\times 25\%\right)\\ =\$36,000+\$9,000\\ =\$45,000\end{array}$

Calculate operating cost including depreciation

$\begin{array}{c}\text{Operating}\text{costs}\text{includying}\text{depreciation}=\$45,000\times 82\%\\ =\$36,900\end{array}$

Calculate AFN equation,

$\begin{array}{c}\text{AFN}=\left[\begin{array}{l}\left(\left(\frac{\$46,800}{\$36,000}\right)\times \$9,000\right)-\left(\left(\frac{\$13,192}{\$36,000}\right)\times \$9,000\right)\\ -\left(\left(0.07\times \$45,000\right)\times \left(1-0.60\right)\right)\end{array}\right]\\ =\$11,700-\$3,298-\$1,260\\ =\$7,142\end{array}$

Calculate the distribution of additional funds

$\begin{array}{c}\text{Additional Funds}=\text{Notes payable }+\text{ Long term bonds}\\ =\text{AFN}\left(35\%\right)+\text{AFN}\left(65\%\right)\\ =\$7,142\left(35\%\right)+\$7,142\left(65\%\right)\\ =\$2,500+\$4,642\\ =\$7,142\end{array}$

Calculation of interest on bonds

$\begin{array}{c}\text{Interest on bonds}=\$1,017+\left(\$2500\times 11\%\right)+\left(\$4,642\times 11\%\right)\\ =\$1,017+\$275+\$511\\ =\$1,803\end{array}$

Calculate increase in cash

$\begin{array}{c}\text{Cash}=\$1800+\left(\$18,000\times 25\%\right)\\ =\$1,800+\$4,500\\ =\$2,250\end{array}$

Calculate increase in receivables

$\begin{array}{c}\text{Receivables}=\$10,800+\left(\$10,800\times 25\%\right)\\ =\$10,800+\$2,700\\ =\$13,500\end{array}$

Calculate increase in inventories

$\begin{array}{c}\text{Inventories}=\$12,600+\left(\$12,600\times 25\%\right)\\ =\$12,600+\$3,150\\ =\$15,750\end{array}$

Calculation of notes payable

$\begin{array}{c}\text{Notes payable }=\$3,472+\$2,500\\ =\$5,972\end{array}$

Calculate total liability

$\begin{array}{c}\text{Total Liability}=\text{Total assets}\times 42\%\\ =\$51,300\times 42\%\\ =\$21,546\end{array}$