Posted: February 8th, 2019

No idea what to do

Individual Assignment FIN328 Corporate Finance Policy Fall 2012

Financial
Forecasting Using Excel

It
is now late Year-Zero and you have just been handed the proformas for Year-
Zero. You have a fair degree of confidence in these numbers since the fiscal
year is about to finish and most of the numbers are already in. These proformas
appear on the next page. The task you have been assigned is to forecast the
statements for Year-One. In order to carry out this task, you have obtained the
following relevant information.

The firm is forecasting a growth rate in
sales of 50% over Year-Zero sales. In conversations with several old hands at
the firm, you have determined that the “Cost of goods sold” item, which
includes depreciation, has historically increased in a proportionate manner
with sales, as have items like: Cash, Accounts Receivable, Inventory, Accounts
Payable, Selling and Administrative Expenses, and Accrued Wages and Taxes. The
cash from depreciation of existing fixed assets has historically been used to
update those fixed assets, and so cannot be diverted to “new capital”. This
implies that if no new fixed assets are purchased, net fixed assets for
Year-One would remain the same number as in Year-Zero.1
To accommodate increased production for the new growth in sales, your boss has
directed you to assume that the net fixed assets for Year-One will grow
proportionately with sales. Your boss has also told you that the firm desires
to maintain a debt ratio2
of 0.43 and a current ratio3
of 2.60 since these ratios have been deemed to be optimal for firms in your
industry. The tax rate is assumed to be 35%, and the dividend payout ratio4
is to be maintained at 30% (as it was last year). If new shares are to be
issued, it is assumed that these new shares will also receive the dividend. For
purposes of interest payment computations, you have been told to assume that
interest must be computed for the whole of Year-One, even if the debt is raised
on a gradual basis over the year. Specifically, you are to assume that the debt
is to be raised as soon as possible in Year-One, and that interest will accrue
on this new debt for the whole of Year-One.

A
call to your investment bankers has revealed the following information:
Interest rates on new short term debt will be 9% per year and on long term
debt, 11%. The entire amount of $10 Million in notes payable as of YEAR0 fiscal
year-end will be refinanced at the new short term debt interest rate of 9%. The
long term bonds of $72 million carry a coupon rate of 10%, and this will not
change for YEAR1. However, new long term debt will cost the firm 11% per year.
For purposes of new equity issuance, you have been told that a domestic
insurance company located in NJ will buy new shares from your firm. Since this
will be a private placement of equity, no SEC registration will be required,
and hence the stock offering can be completed expeditiously.
.0/msohtmlclip1/01/clip_image001.gif”>

1If
this were not the case, net fixed assets for any year would be equal to the
fixed assets from the prior year minus the depreciation for the year.

2Debt
ratio= (total liabilities)/(total assets).

3Current
ratio= (current assets)/(current liabilities).
4
Dividend payout ratio=
(dividends)/(Net income).

1

Individual
Assignment

FIN328
Corporate Finance Policy

Fall 2012

All dollar values in $millions

Year-Zero

Percentage
of

Year-One

of
Sales

Income Statement

Net Sales

$500.00

100.0%

Cost of Goods Sold

($400.00)

80.0%

General & Administrative
Expenses

($52.00)

10.4%

Earnings before interest and
taxes

$48.00

9.6%

Interest expense

($8.00)

n.a.

Earnings before taxes

$40.00

n.a.

Taxes (40%)

($14.00)

n.a.

Net income

$26.00

n.a.

Dividends (30%)

($7.80)

n.a.

Addition to retained earnings

$18.20

n.a.

Balance Sheet

Cash

$10.00

2.0%

Accounts receivable

$85.00

17.0%

Inventory

$100.00

20.0%

Current assets

$195.00

n.a.

Net fixed assets

$150.00

30.0%

Total Assets

$345.00

n.a.

Accounts payable

$65.00

13.0%

Notes payable (short term debt)

$10.00

n.a.

Current liabilities

$75.00

n.a.

Long-term debt

$72.00

n.a.

Total Liabilities

$147.00

Common stock

$150.00

n.a.

Retained Earnings

$48.00

n.a.

Total Equity

$198.00

Total Liabilities & Owners’
Equity

$345.00

n.a.

2

Individual Assignment FIN328 Corporate Finance Policy Fall 2012

Use the model developed in the
Excel spreadsheet to answer the following questions (see detailed instruction
on the next page):

What
is the EFN to achieve the projected 50% growth rate (change the Notes
Payable, Long-term debt, and common equityto make the balance
sheet balanced)?

In
your financial forecast, what would happen to the EFN, if the dividend
payout ratio were increased to 35%? Provide the numerical answer. In
addition to the numerical answer, try to explain it from the interaction
involving the different variables on the income statement and the balance
sheet.

If
the firm is reluctant to use additional external financing due to the
tight credit market and equity market, what is the maximum growth rate in
sales that you forecast for the firm? Assume that the firm do not want to
increase its financial risk (i.e. current ratio>=2.6 and debt
ratio<=0.43). As a financial consultant, what is your advice on its dividend payout policy, if the firm wants to achieve a higher growth rate? Support your advice in light of your answer to the question above. Your submission should include: 1) A cover page containing: a. Course number and Name of the course b. Assignment title: Financial Forecasting Using Excel c. Due date of the assignment d. Your Name and your section number 2) Answers to questions 1 through 3 a. Type the question b. Type your answer to each question 3) Exhibit (Excel worksheet printout) a. Choose the appropriate page layout for your exhibit so that all columns are presented on thesame page with the Year-Zero profoma statement. You DO NOT need to include the “Assumptions” box on your printout. 3 Individual Assignment FIN328 Corporate Finance Policy Fall 2012 Instructions: Download the Excel template from Course Site. The spreadsheet includes the assumption block and the proforma statement for Year-Zero. You need to compute relevant numbers for Year-One in a separate column. In order to complete the task, you need to use Excel’s solver function by following the steps below: Prepare the income statement and balance sheet for Year-One taking into account the projected sales growth rate and the implications of additional external financing. For question #1, change the numbers for “notes payable”, “long-term debt”, and “common stock” to make the balance sheet balanced. For question #2, similar to question #1 but with different dividend payout ratio. For question #3: Create a cell below your last row for Year-One and type in the formula for growth rate. This will be your “Set target cell” cell for the solver function. Create a cell below the one created in the above step. In this cell, type in the formula: “total assets—total liabilities & owners’ equity”. This will be one of your “Subject to the constraints” cells for the solver function. Create a cell below the one created in the above step. In this cell, type in the formula for the debt ratio using your Year-One numbers. This will be one of your “Subject to the constraints” cells for the solver function. Create a cell below the cell created in the above step. In this cell, type in the formula for the current ratio using your Year-One numbers. This will be one of your “Subject to the constraints” cells for the solver function. Start the solver function and choose to set the target cell to “Max”. In the “By Changing Cells” box, type in the cell containing “Net Sales”. In the “Subject to the constraints” box, you want to set the relevant cells to the desired values. Then click on solve. 4