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 co
ect 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:
· ‘Cu
ent 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’ cu
ent 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:
Yea
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 Sold 205,132.00
Depreciation 21,950.00
Earnings Before Interest & Tax $ 58,678.00
Interest Paid 9,875.00
Taxable Income $ 48,803.00
Taxes (35%) 17,081.05
Net Income $ 31,721.95
Dividends $ 18,000.00
Addition to Retained Earnings 13,721.95
LUXIO GOLF CORP.
2008 & 2009 Balance Sheets
Liabilities & Owner's Equity
XXXXXXXXXX
Cu
ent AssetsCu
ent Liabilities
Cash18,270.00$ XXXXXXXXXX,150.00$ Accounts Payable16,215.00$ XXXXXXXXXX,318.00$
Accounts Receivable12, XXXXXXXXXX, XXXXXXXXXXNotes Payable8, XXXXXXXXXX,000.00
Inventory21, XXXXXXXXXX, XXXXXXXXXXOther11, XXXXXXXXXX,451.00
Total Assets52,169.00$ XXXXXXXXXX,891.00$ Total35,360.00$ XXXXXXXXXX,769.00$
Long-term Debt80,000.00$ XXXXXXXXXX,000.00$
Fixed Assets
Net Plant & Equipment168,326.00$ XXXXXXXXXX,735.00$ Owner's Equity
20,000.00$ XXXXXXXXXX,000.00$
Retained Earnings85, XXXXXXXXXX,857.00
Total105,135.00$ XXXXXXXXXX,857.00$
Total Assets220,495.00$ XXXXXXXXXX,626.00$ XXXXXXXXXX,495.00$ XXXXXXXXXX,626.00$
Assets
Common Stock & paid in
Total Liabilities & Owner's
Sheet1
LUXIO GOLF CORP.
2008 & 2009 Balance Sheets
Assets Liabilities & Owner's Equity
2008 2009 2008 2009
Cu
ent Assets Cu
ent Liabilities
Cash $ 18,270.00 $ 22,150.00 Accounts Payable $ 16,215.00 $ 17,318.00
Accounts Receivable 12,315.00 13,865.00 Notes Payable 8,000.00 10,000.00
Inventory 21,584.00 24,876.00 Other 11,145.00 14,451.00
Total Assets $ 52,169.00 $ 60,891.00 Total $ 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.00 Owner's Equity
Common Stock & paid in Surplus $ 20,000.00 $ 20,000.00
Retained Earnings 85,135.00 98,857.00
Total $ 105,135.00 $ 118,857.00
Total Assets $ 220,495.00 $ 245,626.00 Total Liabilities & Owner's Equity $ 220,495.00 $ 245,626.00