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 Excels 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 assetstotal 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.