Please help me with this one

FIN2221-Final Project

Weight: 20% of Final Grade - Due: End of Week 14

Complete the following assignment in an Excel document - use a new sheet in Excel for each question. Submit the complete document prior to the due date via Blackboard.Late submissions will be penalized by 20% per day.

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 presentation of data and formatting of the financial document.

Formatting Requirements:

1.Questions must be done in the correct order and format in Excel

2.Use a separate sheet for each part 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 A:Historical Portion

Place the Balance Sheet and Income Statement of:

Luxio Golf Corp. in Excel - See Luxio Golf Income Statement and Balance Sheet file

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.

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

a)What is the IRR for each of these projects? Based on IRR decision rule, which project should the company accept?

b)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?

c)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.

Appendix A:Ratio Calculations and Analysis

Short-term solvency ratios

-Current ratio____________________________________

-Quick ratio____________________________________

-Cash ratio____________________________________

Asset utilization ratios

-Total asset turnover___________________________________

-Inventory turnover___________________________________

-Receivables turnover___________________________________

Long-Term solvency ratios

-Debt-equity ratios___________________________________

-Times interest earned ratio_____________________________

-Cash coverage ratio___________________________________

Profitability ratios

-Profit margin___________________________________

-Return on assets___________________________________

-Return on equity___________________________________

Market value ratios

-Price-earnings ratio___________________________________

-Dividends per share___________________________________

-Market to book ratio___________________________________

FIN2221-Final Project Weight: 20% of Final Grade - Due: End of Week 14 Complete the following assignment in an Excel document - use a new sheet in Excel for each question. Submit the complete document prior to the due date via Blackboard. Late submissions will be penalized by 20% per day. 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 presentation of data and formatting of the financial document. Formatting Requirements: 1. 2. 3. 4. 5. Questions must be done in the correct order and format in Excel Use a separate sheet for each part of the assignment Make gridlines and row and column headings visible for all pages printed from Excel. (In Excel go to print preview) Round all numbers to two decimal places Format all ratios to the proper symbol: ie, ($), (%) etc. Part A: Historical Portion Place the Balance Sheet and Income Statement of: Luxio Golf Corp. in Excel - See Luxio Golf Income Statement and Balance Sheet file 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. 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 a) What is the IRR for each of these projects? Based on IRR decision rule, which project should the company accept? b) 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? c) 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. Appendix A: Ratio Calculations and Analysis Short-term solvency ratios - Current ratio ____________________________________ - Quick ratio ____________________________________ - Cash ratio ____________________________________ Asset utilization ratios - Total asset turnover ___________________________________ - Inventory turnover ___________________________________ - Receivables turnover ___________________________________ Long-Term solvency ratios - Debt-equity ratios ___________________________________ - Times interest earned ratio - Cash coverage ratio _____________________________ ___________________________________ Profitability ratios - Profit margin ___________________________________ - Return on assets ___________________________________ - Return on equity ___________________________________ Market value ratios - Price-earnings ratio ___________________________________ - Dividends per share ___________________________________ - Market to book ratio ___________________________________