Finance help - Three parts
This assessment relates to the following Course Learning Requirements: · CLR1: Use financial theories, concepts and techniques to analyze and solve problems facing individuals and businesses. · CLR2: Use spreadsheet software to solve quantitative financial problems. · CLR3: Identify the short-term and long-term types of financing available to businesses and individuals. Overview Spreadsheet models have become the dominant method for finance professionals in the business world to implement their financial knowledge. As a result, it is important that students learn how to build financial models in Excel. For each section of the project use a separate sheet in Excel. You will be marked on the presentation of data and formatting of the financial document. Instructions 1. Questions must be done in the correct order and in Microsoft Excel format. 2. Use a separate sheet for each part (A, B, C, and D) of the assignment 3. Make gridlines and row and column headings visible for all pages printed from Excel. (In Excel go to print preview) 4. Round all numbers to two decimal places 5. Format all ratios to the proper symbol: ie, ($), (%) etc. Part B: Financial Planning - Pro Forma Statements 1. Using the financial statements for 2009 as your ‘base’, assume that Luxio’s sales are 20% higher for 2010. Use this projection to prepare the pro forma statements following the requirements listed below. Assume the change in sales is permanent. 2. For the Income Statement: · Cost of Goods Sold rate is expected to remain constant; · ‘Depreciation’ and ‘Interest paid’ expenses are expected not to change; · The Tax rate is expected to decrease to 32%; and · Management is expected to increase the amount of dividends paid by 5% (therefore, the Dividend payout rate will increase by 5%). 3. For the Balance Sheet: · ‘Current assets’ change in direct proportion to sales; · ‘Fixed assets’ are being operated at 100% of capacity; · ‘Accounts payable’ changes in direct proportion to sales; · ‘Notes payable’ and ‘Other’ current liabilities do not change; · ‘Common stock’ remains unchanged; and · Use ‘Long-term debt’ as the plug variable. 4. Determine the amount of External Financing Needed (EFN) under the pro forma assumptions. Detail how this external financing is distributed. Part C: Ratio Calculations 1. Assume Luxio has 1000 shares of common stock outstanding, and the market price of the shares at the end of 2009 was $45. Also assume that the share price and number of outstanding shares does not change. 2. Using the exact formulas found in the textbook, compute all the ratios listed in Appendix A for 2008, 2009, and 2010 (write n/a where you don’t have enough information for a specific year). Place all your answers in the format found in Appendix A (attached). 3. Based entirely on your ratio analysis explain in detail the strengths and weaknesses of the company to a potential common equity investor. Summarize by making recommendations to the firm. Part D: Investment Decisions Now consider that Luxio has identified the following two mutually exclusive projects: Year Cash Flow (A) Cash Flow (B) 0 -$34,000 -$34,000 1 $16,500 $5,000 2 $14,000 $10,000 3 $10,000 $18,000 4 $6,000 $19,000. 1. What is the IRR for each of these projects? Based on IRR decision rule, which project should the company accept? 2. If the required return is 11%, what is the NPV for each of these projects? Based on the NPV decision rule, which project should the company accept? 3. Over what range of discount rates would the company choose project A? At what discount rate would the company be indifferent between these two projects? Explain. Project 6 Instructor: Due Date: Student Name: Grading Criteria: 1.Data Entry …………………………………………………/5 2.Financial Planning Exercise ……………………………... /30 3.Ratio Calculations ………………………………………… /25 4.Investment Analysis ……………………………………… /30 5.Presentation and Format ………………………………… /10 Total ………………………………………………………… /100 Page 4 of 4 Balance Sheet Sales285,760.00$ Cost of Goods Sold205,132.00 Depreciation21,950.00 Earnings Before Interest & Tax58,678.00$ Interest Paid9,875.00 Taxable Income48,803.00$ Taxes (35%)17,081.05 Net Income31,721.95$ Dividends18,000.00$ Addition to Retained Earnings13,721.95 2009 Income Statement LUXIO GOLF CORP. Sheet1 LUXIO GOLF CORP. 2009 Income Statement Sales$ 285,760.00 Cost of Goods Sold205,132.00 Depreciation21,950.00 Earnings Before Interest & Tax$ 58,678.00 Interest Paid9,875.00 Taxable Income$ 48,803.00 Taxes (35%)17,081.05 Net Income$ 31,721.95 Dividends$ 18,000.00 Addition to Retained Earnings13,721.95 LUXIO GOLF CORP. 2008 & 2009 Balance Sheets Liabilities & Owner's Equity 2008200920082009 Current AssetsCurrent Liabilities Cash18,270.00$ 22,150.00$ Accounts Payable16,215.00$ 17,318.00$ Accounts Receivable12,315.00 13,865.00 Notes Payable8,000.00 10,000.00 Inventory21,584.00 24,876.00 Other11,145.00 14,451.00 Total Assets52,169.00$ 60,891.00$ Total35,360.00$ 41,769.00$ Long-term Debt80,000.00$ 85,000.00$ Fixed Assets Net Plant & Equipment168,326.00$ 184,735.00$ Owner's Equity 20,000.00$ 20,000.00$ Retained Earnings85,135.00 98,857.00 Total105,135.00$ 118,857.00$ Total Assets220,495.00$ 245,626.00$ 220,495.00$ 245,626.00$ Assets Common Stock & paid in Total Liabilities & Owner's Sheet1 LUXIO GOLF CORP. 2008 & 2009 Balance Sheets AssetsLiabilities & Owner's Equity 2008200920082009 Current AssetsCurrent Liabilities Cash$ 18,270.00$ 22,150.00Accounts Payable$ 16,215.00$ 17,318.00 Accounts Receivable12,315.0013,865.00Notes Payable8,000.0010,000.00 Inventory21,584.0024,876.00Other11,145.0014,451.00 Total Assets$ 52,169.00$ 60,891.00Total$ 35,360.00$ 41,769.00 Long-term Debt$ 80,000.00$ 85,000.00 Fixed Assets Net Plant & Equipment$ 168,326.00$ 184,735.00Owner's Equity Common Stock & paid in Surplus$ 20,000.00$ 20,000.00 Retained Earnings85,135.0098,857.00 Total$ 105,135.00$ 118,857.00 Total Assets$ 220,495.00$ 245,626.00Total Liabilities & Owner's Equity$ 220,495.00$ 245,626.00