I am trying to get help finding a solution to my for my final project for my Financial Accounting class. I am stuck on Part B and Part C. Part B gives you the numbers to fill in for Part C. The course is FIN2221.

I completed my Income Statement part but the Balance Sheet is throwing me off. I increased all my current assets by 20% as listed. My fixed assets are being operated at 100% of capacity (it is suppose to change but I don't understand how).

On the Liability side my accounts payable increased by 20% and notes payable and other stay the same. The long term debt is my plug in variable. It says common stock remains unchanged (it increased in my Income Statement part thou). Owners' Equity is throwing me off too.

I have to hand this assignment in on Sunday night so an answer or an explanation would be great!!.

I attached all the documents that are needed including my attempt.

LUXIO GOLF CORP. 2009 Income Statement Net Sales Cost of Goods Sold Depreciation Earnings before Interest & Taxes Interest Paid Income before Taxes Statement of Financial P $ $ $ Taxes (35%) Net Income Dividends Addition to Retained Earnings $ $ 18,000.00 13,721.95 285,760.00 205,132.00 21,950.00 58,678.00 9,875.00 48,803.00 Current Assets Cash Accounts Receivable Inventory 17,081.05 Total Assets Assets 31,721.95 Fixed Assets Net, Plant & Equipment Total Assets LUXIO GOLF CORP. Statement of Financial Position as of Decemeber 31, 2008 and 2009 2008 2009 2008 Assets Liabilities and Owners' Equity Current Liabilities $ 18,270.00 $ 22,150.00 Accounts Payable $ 16,215.00 $ 12,315.00 $ 13,865.00 Note Payable $ 8,000.00 $ 21,584.00 $ 24,876.00 Oher $ 11,145.00 $ 52,169.00 $ 60,891.00 2009 $ $ $ 17,318.00 10,000.00 14,451.00 Total $ 35,360.00 $ 41,769.00 Long-term Debt $ 80,000.00 $ 85,000.00 Owners' Equity Common Stock & Paid In Retained Earning Total $ 20,000.00 $ 20,000.00 $ 85,135.00 $ 98,857.00 $ 105,135.00 $ 118,857.00 $ 168,326.00 $ 184,735.00 $ 220,495.00 $ 245,626.00 Total Liabilities & Owners' Equity $ 220,495.00 $ 245,626.00 LUXIO GOLF CORP. Pro Forma Income Statement Sales (projection) Cost of goods sold Depreciation Earnings before Interest and Taxes Interest paid Taxable Income Taxes (32%) Net income Dividends Addition to retained earnings $ $ $ $ $ $ 18,900.00 25,251.45 342,912.00 246,158.40 21,950.00 74,803.60 9,875.00 64,928.60 20,777.15 44,151.45 Present Year Pro Forma LUXIO GOLF CORP. Pro Forma Balance Sheet Change in % Assets Current Assets Cash Accounts Receivable Inventory Total Assets $ $ $ $ Fixed Assets Net, Plant & Equipment Total Assets 22,150 13,865 24,876 60,891 $ $ $ $ 26,580 16,638 29,851 73,069 20% 20% 20% 20% $ 184,735 $ 184,735 0% $ 245,626 $ 257,804 LUXIO GOLF CORP. Pro Forma Balance Sheet Present Year Pro Forma Assets Current Assets Cash Accounts Receivable Inventory Total Assets $ $ $ $ Fixed Assets Net, Plant & Equipment Total Assets 22,150 13,865 24,876 60,891 $ $ $ $ 26,580 16,638 29,851 73,069 $ 184,735 $ 184,735 $ 245,626 $ 257,804 IO GOLF CORP. ma Balance Sheet Present Year Pro Forma Liabilities and Owners' Equity Current Liabilities Accounts Payable Note Payable Other Total $ $ $ $ 17,318 10,000 14,451 41,769 $ $ $ $ 20,782 10,000 14,451 45,233 Long-term Debt $ 85,000 $ 85,000 Owners' Equity Common Stock & Paid In $ 20,000 $ 20,000 $ 98,857 $ 124,108 $ 118,857 $ 144,108 $ 245,626 $ 274,341 Retained Earning Total Total Liabilities & Owners' Equity EFN = Total Assets - Total Liabilities & Owners Equity EFN = 257,804 - 274,341 EFN = $8,714 IO GOLF CORP. ma Balance Sheet Present Year Pro Forma Liabilities and Owners' Equity Current Liabilities Accounts Payable Note Payable Oher Total $ $ $ $ 17,318 10,000 14,451 41,769 $ $ $ $ 20,782 10,000 14,451 45,233 Long-term Debt $ 85,000 $ 85,000 Owners' Equity Common Stock & Paid In Retained Earning Total $ $ $ 20,000 $ 98,857 $ 118,857 $ 27,259 100,312 118,857 Total Liabilities & Owners' Equity $ 245,626 $ 257,804 Appendix A: Formula: Short-term solvency ratio: - Current Ratio: - Quick Ratio: - Cash Ratio: Current Assets / Current Liabilities Current Assets - Inventory / Current Liabilities (Cash + Cash Equivanlents) / Current Liabilities Asset utilization ratio: - Total Asset Turnover - Inventory Turnover - Receivable Turnover Sales / Total Assets Cost of goods sold / Inventory Sales / Accounts Receivable Long-term solvency ratio: - Debt-equity ratio - Times interest earned ratio - Cash coverage ratio (Total Assets - Total Equity) / Total Asssets EBIT / Interest [EBIT + Depreciation] / Interest Profitability ratio: - Profit margin - Return on asset - Return on equity Net Income / Sales Net Income / Total Assets Net Income / Total Equity Market value ratio: - Price- earned ratio - Dividends per share - Market to book ratio Price Per Share / Earning Per Share Market Value Per Share / Book Value per Share 2008 2009 $52,169 / $35,360 = 1.48 times $52,169 - $21,584 / $35,360 = .86 times $18,270 / $35,360 = .52 times $60,891 / $41,769 = 1.46 times $60,891 - $24,876 / $41,769 = .86 times $22,150 / $41,769 = .53 times N/A N/A N/A $285,760 / $245,626 = 1.16 times $205,132 / $24,876 = 8.25 times $285,760 / $13,865 = 20.61 times (220,495 - 105,135) / 220,495 = .52 times N/A N/A (245,626 - 118,857) / 245,626 = .52 times 58,678 / 9,875 = 5.94 times (58,678 + 21,950) / 9,875 = 8.16 times N/A N/A N/A 31,721.95 / 285,760 = .1110 or 11.10% 31,721.95 / 245,626 = .1291 or 12.91% 31,721.95 / 118,857 = .2669 or 26.69% 2010 ?????????? ?????????? ?????????? ????????? ????????? ????????? ????????? 74,803.60 / 9,875 = (74,803.60 + 21,950) / 9,875 = 44,151.45 / 342.912 = 44,151.45 / 257,804 = 44,151.45 / ??? Luxio has identified two mutually exclusive projects: Year 0 1 2 3 4 Cash Flow (A) $-34,000 16,500 14,000 10,000 6,000 Cash Flow (B) $-34,000 5,000 10,000 18,000 19,000 A)The IRR for project A is 16.60% The IRR for project B is 15.72% Based on the IRR decision rule project A should be the company they choose. B) Project A: NPV = -$34,000 + $16,500 / 1.11 + $14,000 / 1.11 2 + $10,000 / 1.113 + $6,000 / 1.114 NPV = -$34,000 + $14,864.8649 + $11,362.7141 + $7,311.9138 + $3,952.3858 NPV = -$34,000 + $14,864.86 + $11,362.71 + $7,311.91 + $3,952.39 NPV = -$34,000 + $37,491.87 NPV = 3,491.87 Project B: NPV = -$34,000 + $5,000 /1.11 + $10,000 / 1.11 2 + $18,000 / 1.113 + $19,000 / 1.114 NPV = -$34,000 + $4,504.50 + $8,116.22 + $13,161.44 + $12,515.89 NPV = -$34,000 + $38,298.05 NPV = $4,298.05 If a return of 11% is required Project B has the better NPV C) Year Project A -34,000 16,500 14,000 10,000 6,000 0 1 2 3 4 RRR (Discount) NPV A 0% 1% 2% 3% 4% Project B -34,000 5,000 10,000 18,000 19,000 Project A - Project B 0 11,500 4,000 -8,000 -13,000 NPV B $12,500.00 $11,532.56 $10,599.13 $9,698.10 $8,827.96 $18,000.00 $16,482.70 $15,028.51 $13,634.13 $12,296.47 $20,000.00 $15,000.00 $10,000.00 $15,000.00 5% 6% 7% 8% 9% 10% 11% 12% 13% 14% 15% 16% 17% 18% 19% 20% 21% 22% 23% 24% 25% 26% 27% 28% 29% 30% $7,987.29 $7,174.74 $6,389.05 $5,629.02 $4,893.52 $4,181.48 $3,491.88 $2,823.77 $2,176.24 $1,548.43 $939.52 $348.74 ($224.64) ($781.33) ($1,321.98) ($1,847.22) ($2,357.66) ($2,853.87) ($3,336.39) ($3,805.74) ($4,262.40) ($4,706.85) ($5,139.55) ($5,560.91) ($5,971.34) ($6,371.24) $11,012.62 $9,779.87 $8,595.66 $7,457.57 $6,363.34 $5,310.84 $4,298.06 $3,323.11 $2,384.20 $1,479.65 $607.87 ($232.66) ($1,043.34) ($1,825.52) ($2,580.48) ($3,309.41) ($4,013.46) ($4,693.71) ($5,351.17) ($5,986.83) ($6,601.60) ($7,196.36) ($7,771.93) ($8,329.11) ($8,868.65) ($9,391.27) $10,000.00 $5,000.00 $0.00 0% ($5,000.00) ($10,000.00) ($15,000.00) 5% Cross Over Rate 13.75% Crossover Rate 5% 10% 15% NPV A 20% NPV B 25% 30% 35% 35% FIN2221 - Final Project Instructor: Tim D'Souza 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 1 of 4 Page 2 of 4 Balance Sheet Page 3 of 4 Page 4 of 4 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 ___________________________________