Financial Modeling

1. (10) You are examining a firm in the food industry (Feed Box). You have the following information for them and their nearest competitor concerning ROE.

ROE	Y0	Y1	Y2	Y3	Y4
Feed Box	8.0%	10.0%	11.0%	11.5%	25.0%
Snack Pack	7.0%	8.0%	9.0%	8.5%	7.8%

The total asset turnover for both firms is roughly 1 for all years. Net profit margin for Feed Box averages 6% across all years; it is 7% for Snack. Profit margin has minimal volatility for these firms. What conclusions (if any) can you draw about the performance of these two firms (Du Pont is useful here)?

1. (20) You are forecasting the balance sheet and income statement for Y1. Use the following assumptions: Sales and accounts receivable will grow by 20%; cost of goods sold, inventory and accounts payable will grow 25%. Because the firm needs to add capacity, the depreciation expense will rise to 40 and the net fixed assets will rise to 400. The accounts that will not change are entered in the table below. Fill in the blanks below and calculate the external financing needed.

SUNSHINE: INCOME STATEMENT (M$)

Fiscal Year Ending	Y0	Y1
Sales	500
Cost of Goods Sold	270
Depreciation	20
Earnings Before Interest & Tax (EBIT)	210
Interest Expense	10	10
Earnings Before Tax	200
Taxes (40%)	80
Net Income	120
Dividends	40	40

SUNSHINE: BALANCE SHEET (M$)

Fiscal Year Ending	Y0	Y1
Cash	10	10
Accounts Receivable	20
Inventories	30
Current Assets	60
Net Fixed Assets	260
TOTAL ASSETS	320
Accruals	15	15
Accounts Payable	5
Notes Payable	25	25
Current Liabilities	45
Long Term Debt	75	75
Common Stock	5	5
Retained Earnings	195
Total Liability & Equity	320

Time Value

1. (15) You are looking at the following investments: FV = future value. Interest rate is 6%

Investment	Invest Today	FV In 3 years Simple Interest	FV in 20 years Simple Interest	FV in 3 years Compound Interest	FV in 20 years Compound Interest
A	$100
B	$10 million

1. Complete the table.
2. Your new boss claims that simple interest results are close enough to compound interest, so you should just use simple interest. Based on your results, when does that claim create the most problems?

1. (10) Ida Pay will loan clients $1000. In exchange, the clients agree to pay $25 per week for 1 year. Find the APR and effective rate on the loan.

1. (15) An investment pays $30,000 per quarter for 5 years. The interest rate is 9% with quarterly compounding. Three students are working on the present value calculation. Solve each one for the present value based on the inputs below. Identify any error(s) made by the students.

Student	RATE	NPER	PMT	What is the PV result?
A	9.3%	5	360,000
B	2.25%	20	30,000
C	9%	5	360,000

1. (10) A refrigerator costs $1400. You pay $300 in cash and put the rest on your Chiller Charge store card. The interest rate on the card is 21% compound monthly. If you make the minimum $30 per month payment, how long will it take to re-pay the loan? Be sure to label the answer as days, months or years.

1. (10) Assume you have just taken a 30-year mortgage of $550,000 at 9% with monthly compounding. Further, suppose that in 10 years (immediately after the 120^th payment) interest rates fall to 6% with monthly compounding. You decide to refinance at that time to a 15-year loan at the new lower rate. Assume refinancing costs are 2% of the amount refinanced and will be rolled into the new loan. Find your new payment for the refinanced loan.

1. (10) Suppose you deposit $100,000 per year in a savings account paying 8% interest for 10 years. You wish to withdraw the funds in 5 equal installments, one year apart, starting in year 11 and have a balance remaining in your account of $75,000 immediately after you make your fifth withdrawal. How much can you withdraw per year? HINT: A timeline will help with this.