Hello,

Any help on these problems?

I need all work shown step by step / excel document with formulas.

Thank you.

Finance 486 Final Exam 1. EFN Calculation - 25 points The most recent Financial Information for Golf Pro Inc. are shown here: Income Statement Balance Sheet Sales $3,400 Costs 2,800 Taxable Income 600 Taxes @ 34% 204 Net Income $ 396 Current Assets $4,400 Current Liabilities $880 Fixed Assets 5,700 Long Term Debt 3,580 Equity 5,640 Total 10,100 Total $10,100 Assets, costs and current Liabilities are proportional to sales. Long -term Debt and equity are not. The company maintains a constant 50% dividend payout ratio. As with every other firm in its industry, the next year's sales are projected to increase by exactly 15%. What is the external financing needed? Show your work on how you came up with the EFN needed. 2. Financial Statement Analysis - 25 points The Financial Statements for Johnson Inc. for the year ended December 31, 2009, follow. Johnson Inc Income Statement For the year ended December 31, 2009 Sales Revenue Less: Cost of goods sold Gross profits Less: Operating Expenses Selling Expenses General and Admin Lease Expense $160,000 106,000 $ 54,000 $ 16,000 10,000 1,000 Johnson Inc Balance Sheet For year ended December 31, 2009 Assets Cash Marketable Securities Accounts Receivable Inventories Total Current Assets Land $ 500 1,000 25,000 45,500 $ 72,000 $ 26,000 Depreciation Total Operating Expense Operating Profits Less: Interest Expense Net Profits before Taxes Less: Taxes Net profit after taxes 10,000 $ 37,000 $ 17,000 6,100 $ 10,900 4,360 $ 6,540 Buildings & Equip 90,000 Less : Accumulated Depreciation 38,000 Net Fixed Assets $ 78,000 Total Assets $ 150,000 Liabilities and Stockholders' Equity Accounts Payable $ 22,000 Notes Payable 47,000 Total Current Liabilities $ 69,000 Long Term Debt $ 22,950 Common Stock $ 31,500 Retained Earnings $ 26,550 Total Liabilities and Equity $ 150,000 Use the preceding financial information to complete the following table. Assume the industry averages given in the table are applicable for both 2008 and 2009. Ratio Current Ratio Quick Ratio Inventory turnover Average collection period Debt Ratio Gross Profit margin Net profit margin Return on total assets Industry Avg 1.80 .70 2.50 37.5 days 65% 38% 3.5% 4.0% Actual 2008 Actual 2009 1.84 .78 2.59 36.5 days 67% 40% 3.6% 4.0% A. Calculate the above ratios for 2009 and comment on how they relate to the actual ratios for 2008 and the industry average. 3. NPV - 15 points Calculate the net present value (NPV) for the following 20 year projects. Comment on the acceptability of each. Assume that the firm has an opportunity cost of 14%. a. Initial investment is $10,000; cash inflows are $2,000 per year b. Initial investment is $25,000; cash inflows are $3,000 per year c. Initial investment is $30,000 ; cash inflows are $5,000 per year 4. Pro forma Income Statement Preparation - 15 points The sales department of PreFab Inc. estimates that its sales in 2010 will be $1.5 million. Interest expense is expected to remain unchanged at $35,000, and the firm plans to pay $70,000 in cash dividends during 2010. PreFab's income statement for the year ended December 31, 2009, is given below, along with a breakdown of the firm's cost of goods sold and operating expenses. PreFab Inc Income Statement For the year ended December 31, 2009 Sales Revenue Less: Cost of goods sold Gross Profits Less Operating Expenses Operating Profits Less: Interest Expense Net Profits before taxes Less: Taxes @ 40% Net profits after taxes Less: Cash Dividends To Retained Earnings $1,400,000 910.000 490,000 120,000 $ 370,000 35,000 $ 335,000 134,000 $ 201,000 66,000 $ 135,000 a. Use the percent-of-sales method to prepare a pro forma income statement for the year ended December 31, 2010 5. Cash conversion cycle - 10 points American Products is concerned about managing cash efficiently. On the average, the inventories have an age of 90 days, and accounts receivable are collected in 60 days. Accounts payable are paid approximately 30 days after they arise. The firm has annual sales of about $30 million. Assume there is no difference in the investment per dollar of sales in inventory, receivables, and payables; and a 365day year. a. Calculate the firm's operating cycle. b. Calculate the firm's cash conversion cycle. c. Calculate the amount of resources needed to support the firm's cash conversion cycle. 6. Sunk and opportunity Cash flows - 10 points Don and Betsy Draper have been living at their present home for the past 6 years. During that time, they have replaced the water heater for $375, replaced the dishwasher for $599, and have had to make miscellaneous repair and maintenance expenditures of approximately $1,500. They have decided to move out and rent the house for $975 per month. Newspaper advertising will cost $75. Don and Betsy intend to paint the interior of the home and power-wash the exterior. They estimate that will run about $900. The house should be ready to rent after that. In reviewing the financial situation, Don views all of the expenditures as being relevant, and so he plans to net out the estimated expenditures discussed above from the rental income. a. Do Don and Betsy understand the difference between sunk costs and opportunity costs? Explain the two concepts to them. b. Which of the expenditures should be classified as sunk costs and which should be viewed as opportunity cash flows