I have completed the spreadsheet portion of this assignment, but need help with the written aspects of it.

SUBDOMAIN 319.1 - ACCOUNTING & FINANCE SUBDOMAIN 319.2 - INFORMATION TOOLS Competency 319.1.2: Ratios - The graduate determines the financial condition of a firm using financial ratios and other financial data. Competency 319.2.1: Technology Tools - The graduate uses information technology tools for specified business purposes. Objectives: 319.1.2-04: Determine a firm's financial condition by calculating and benchmarking specified ratios against other companies in the industry. 319.2.1-01: Use the Internet to gather information on a specified business topic. 319.2.1-02: Produce business letters or memorandums using a word processing application. 319.2.1-03: Develop a spreadsheet report that solves a business problem. Introduction: Company G operates a small chain of wholly owned home centers selling to consumers and contractors. Sales volume varies among the individual stores and ranges between $11 million and $20 million per year. The company is organized as a corporation and does not operate as a sub-chapter S corporation. Given: Consolidated financial statements for Company G are shown on the attached \"Statement Analysis Template.\" Sales volume shown is net sales. During Year 12 60% of net sales were on a credit basis. Inventory is stated at cost on a first-in, first-out basis. Depreciation expense totaled $1,875,000 for Year 12 and $1,644,000 for Year 11. Depreciation expense is not shown as a separate item on the income statements but is included in store operating expense and general and administrative expense. Depreciable assets are depreciated using the straight -line method over the estimated useful life of the assets. The market value for the company common stock at the end of Year 1 1 was $3.50/share and at the end of Year 12 it was $5.75/share. For this task, you will use financial statements and pertinent industry data to interpret Company G's financial condition. The required ratios have been calculated for Year 11 and are shown on the attached \"Statement Analysis Template.\" Ratios gathered from financial statements that were submitted to banks and other financing institutions by a large number of home centers are also included on the template. The industry ratios included on the template were generated from statements submitted by stores with annual sales between $10 million and $24 million. As you develop your response, you should consider Company G trends by comparing Year 12 ratios to the same ratios for Year 11, consider what is depicted by the horizontal analysis, and benchmark Company G's performance and condition by comparing Year 12 ratios with the same ratios for the home center industry. Task: A. Calculate each of the thirteen indicated ratios for Year 11 indicated in the attached \"Statement Analysis Template.\" Complete the attached spreadsheet with this information. B. Complete a horizontal analysis of the income statements using the attached spreadsheet with this information. Note: Round the percentages to the nearest hundredth of a percent, i.e. 6.84%. Answers like 6.80% or rounded up to 7% are not acceptable. C. Complete a horizontal analysis of the balance sheets using the attached spreadsheet with this information. Note: Round the percentages to the nearest hundredth of a percent, i.e. 6.84%. Answers like 6.80% or rounded up to 7% are not acceptable. D. Write a business memorandum to the CEO of Company G (suggested length of 2-3 pages) in which you: 1. Explain for each ratio and trend whether the ratio or trend indicates a strength of the company; a likely weakness, threat, or emerging problem; or a satisfactory condition that management should not view as a strength or weakness. 2. Justify your identification of each ratio or trend as a strength, weakness, or satisfactory condition. 3. Evaluate the Company G ratios and trends against the available ratios and trends for the home center industry. Include the URL where you found the information you have entered on the template. Note: Information on trends can be gathered through Internet research. Some suggested websites are included in the Web Links section below. Please consider, however, that ratios you find on the Internet are generally computed as of the current date so ratios on the task might not match the numbers you see in your research. It is best to consider the data based on the trends you see for the industry and how Company G is doing related to those trends. E. When you use sources, include all in-text citations and references in APA format. Note: For definitions of terms commonly used in the rubric, see the attached Rubric Terms. Note: When using outside sources to support ideas and elements in a paper or project, the submission MUST include APA formatted in-text citations with a corresponding reference list for any direct quotes or paraphrasing. It is not necessary to list sources that were consulted if they have not been quoted or paraphrased in the text of the paper or project. Note: No more than a combined total of 30% of a submission can be directly quoted or closely paraphrased from outside sources, even if cited correctly. Here ar e some helpful APA resources: http://www.apastyle.org http://www.citationmachine.net http://owl.english.purdue.edu/owl/resource/560/01 References: Horngren, C. T., & Harrison, W. T. (2009) Accounting (7th ed.). Upper Saddle River, NJ: Prentice Hall. ISBN-13: 9780136072973. Company G Comparative Income Statements December 31, Years 12 and 11 NET SALES Cost of Merchandise Sold GROSS PROFIT Operating Expenses: Selling and Store Operating Pre-Opening General and Administrative Total Operating Expenses OPERATING INCOME Interest Income (Expense): Interest Income Less: Interest Expense Interest, net EARNINGS BEFORE INCOME TAXES The below rules are from Financial Accounting Principles, Wild, 18th ed. Horizontal Analysis Change % Inc (Dec) 11,178,375 9.04% 6,543,803 7.57% 4,634,573 12.43% Year 12 134,886,375 92,952,803 41,933,573 Year 11 123,708,000 86,409,000 37,299,000 25,827,000 222000 2,316,000 28,365,000 13,568,573 23,478,000 267000 2,163,000 25,908,000 11,391,000 2,349,000 -45,000 153,000 2,457,000 2,177,573 10.01% -16.85% 7.07% 9.48% 19.12% 183000 384000 -201000 13,367,573 117000 366000 -249000 11,142,000 66,000 18,000 48,000 2,225,573 56.41% 4.92% -19.28% 19.97% Apply the above mathematics rules to your work in this task. Enter --- in the answer field when appropriate. Ratio Analysis: Provision for Income Taxes NET EARNINGS 5,052,000 8,315,573 4,419,000 6,723,000 633,000 1,592,573 14.32% 23.69% This column is not graded. It is provided for students to use as they analyze the ratios for the essay. Company G Year 12 Year 11 Quartile Industry Data Select Strength, Weakness, or No Concern Ratio: Current Ratio ASSETS Current Assets: Cash and Cash Equivalents Short-Term Investments Accounts Receivable, net Merchandise Inventory Other Current Assets Total Current Assets Property and Equipment, at cost: Land Buildings Furniture, Fixtures and Equipment Less Accumulated Depreciation Net Property and Equipment TOTAL ASSETS LIABILITIES Current Liabilities: Accounts and Notes Payable Accrued Salaries and Related Expense Sales and Income Taxes Payable Other Accrued Expenses Total Current Liabilities Long-Term Liabilities: Notes Payable Other Long-Term Liabilities Total Long-Term Liabilities Total Liabilities STOCKHOLDERS' EQUITY: Common Stock ($1.00 Par) Paid In Capital Retained Earnings Treasury Stock (2,000,000 shares, at cost) Total Stockholders' Equity TOTAL LIABILITIES and EQUITY 3.1 2.1 1.4 Weakness 0.43 0.64 1.6 0.9 0.6 Weakness 5.2 6.1 13 10.2 8.3 Weakness Accounts Receivable Turnover 30.4 (This formula in Horngren only includes credit sales) 32.2 35.2 33.5 31.4 Weakness Day's Sales in receivables 11.1 15.1 13.5 11.3 Strength 29.47% 28.34% 30.0% 45.0% 66.0% Strength Times-interest-earned ratio Year 12 1.86 Acid-Test Ratio Company G Comparative Balance Sheets December 31, Years 12 and 11 1.80 35.33 31.12 29.7 17.2 8.1 Strength Rate of return on net sales 13.50% 5.43% 7.55% 6.12% 4.20% Strength Rate of return on total assets 12.44% 12.30% 17.20% 12.30% 8.60% Strength Rate of return on common stockholder's equity 35.34% 20.20% 18.60% 16.30% 12.80% No Concern Earnings per share of common stock $1.04 $0.672 0.9 0.87 0.83 Price earnings ratio $5.01 $5.21 7 6.3 5.5 Weakness Book value per share of common stock $5.90 $4.25 6 5.5 4.9 Strength Year 11 Inventory Turnover 4,050,000 36,000 2,638,000 20,503,000 688000 27,915,000 5,562,000 162,000 2,686,000 15,534,000 393000 24,337,000 -1,512,000 -126,000 -48,000 4,969,000 295,000 3,578,000 -27.18% -77.78% -1.79% 31.99% 75.06% 14.70% 12,843,000 24,933,000 9,411,000 47,187,000 8,235,000 38,952,000 66,867,000 11,484,000 21,939,000 7,860,000 41,283,000 6,360,000 34,923,000 59,260,000 1,359,000 2,994,000 1,551,000 5,904,000 1,875,000 4,029,000 7,607,000 11.83% 13.65% 19.73% 14.30% 29.48% 11.54% 12.84% 9,800,000 1,869,000 1,233,000 2,619,000 15,521,000 7,938,000 1,656,000 1,290,000 2,166,000 13,050,000 1,862,000 213,000 -57,000 453,000 2,471,000 23.46% 12.86% -4.42% 20.91% 18.93% 3,051,000 1,134,000 4,185,000 19,706,000 2,889,000 858000 3747000 16,797,000 162,000 276,000 438,000 2,909,000 5.61% 32.17% 11.69% 17.32% 10,000,000 13,530,000 28,251,000 -4,620,000 47,161,000 66,867,000 10,000,000 12,501,000 19,962,000 0 42,463,000 59,260,000 0 1,029,000 8,289,000 -4,620,000 4,698,000 7,607,000 0.00% 8.23% 41.52% n/a 11.06% 12.84% 11.8 (Horngren reference includes all sales. Modify the formula to use only credit sales. Cash sales are already collected.) Debt Ratio Strength FNT1 Task 2 319.1.3-01-10, 2.1-04, 2.5-05 Capital Budgeting Student Template Revised May 8, 2011 Student Name: Z Brandt Enter your first initial and last name in the fields above. Your Task 2 Data Years 1 & 2 Estimated Cash Receipts from tool sales each year. Estimated Cash Expenses for operations each year Initial Investment for equipment, etc. Marginal Tax Rate Restoration at the end of 8 years Working Capital required 3,000,000 2,400,000 2,750,000 28% 120,000 200,000 Years 3 & 4 3,200,000 2,400,000 Years 5 & 6 3,200,000 2,650,000 Years 7 & 8 3,400,000 2,500,000 Entrepreneur D Net Cash Flow per Year Years 1 & 2 Years 3 & 4 Years 5 & 6 Years 7 & 8 Expected annual sales of new product 3,000,000 3,200,000 3,200,000 3,400,000 Expected annual costs of new product Cash expenses Depreciation expense Income before taxes Income tax at marginal rate Net income Net annual cash flow for years shown 2,400,000 336,250 263,750 79,125 184,625 520,875 2,400,000 336,250 463,750 139,125 324,625 660,875 2,650,000 336,250 213,750 64,125 149,625 485,875 2,500,000 336,250 563,750 169,125 394,625 730,875 Cash Flows 520,875 520,875 660,875 660,875 485,875 485,875 730,875 730,875 180,000 60,000 (160,000) 12% PV Factors 0.893 0.797 0.712 0.636 0.567 0.507 0.452 0.404 0.404 0.404 0.404 Present Value 465,141 415,137 470,543 420,317 275,491 246,339 330,356 295,274 72,720 24,240 (64,640) 2,950,917 2,950,000 917 Net Present Value: Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Working Capital return Salvage return Building restoration costs Total Investment Net Present Value GRADERS: To provide for factor rounding, please allow plus or minus $2000 on this net present value solution. Internal Rate of Return: Cash Flows -2,950,000 520,875 520,875 660,875 660,875 485,875 485,875 730,875 810,875 Investment Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 + Working Capital + Salvage - Remodeling Internal Rate of Return using Excel Payback Period: 12.006% Investment Year 1 Year 2 Year 3 Year 4 Time for Cash Flow to Payback Investment -2950000 520875 -2429125 520875 -1,908,250 660875 -1247375 660875 -586500 Year 5 Year 6 Year 7 Year 8 485875 485875 730875 730875 Net Cash Flow State your answer below in years and full months rounding up as necessary. -100625 385250 1116125 1847000 5 Years Accounting Rate of Return 19.15% 2 Months *Working Capital requirements are not part of this calculation. SUBDOMAIN 319.1 - ACCOUNTING & FINANCE SUBDOMAIN 319.2 - INFORMATION TECHNOLOGY Competency 319.1.3 Capital Budgeting Analysis - The graduate correctly applies time value of money techniques and techniques that ignore present value for capital investment decisions. Competency 319.2.1 Technology Tools - The graduate uses information technology tools for specified business purposes. Competency 319.2.5 Information Management - The graduate selects appropriate technology applications to manage information and make decisions in given situations. Objectives: 319.1.3-01: Calculate net present value based on a given set of facts. 319.1.3-02: Apply the results of a net present value calculation to a given decision situation. 319.1.3-03: Calculate internal rate of return based on a given set of facts. 319.1.3-04: Apply the results of an internal rate of return calculation to a given decision situation. 319.1.3-05: Calculate the period of time required to recoup the money expended for new equipment in a given situation. 319.1.3-06: Calculate the accounting rate of return based on a given set of facts. 319.1.3-07: Explain the relationship of the accounting rate of return to the internal rate of return for the same capital investment alternative. 319.1.3-08: Calculate net cash flow in a given situation. 319.1.3-09: Explain the impact of depreciation on net cash flow. 319.1.3-10: Explain the role of the weighted average cost of capital in capital budgeting analysis. 319.2.1-04: Produce a computer-based presentation on a business topic. 319.2.5-05: Demonstrate the appropriate use of specified software application in a given situation. It is imperative that you enter your first initial and last name in the fields designated on the template. Your work and results will be based on an individualized dataset that will auto load in the template when you enter your first initial and last name. Your work will not be correctly graded if you fail to complete these fields. Introduction: Entrepreneur D supplements income from a professional practice by investing in start-up and other business opportunities that meet specific investment criteria. Entrepreneur D operates all of these extra business activities as a single limited liability company and utilizes discounted cash flow analysis as a primary tool for evaluating each potential investment. There is an opportunity to purchase the patent for a newly invented gardening tool that Entrepreneur D would manufacture and sell on a wholesale basis. Entrepreneur D has asked you to prepare an analysis of this investment opportunity and make a recommendation regarding the course of action to take. Given: Entrepreneur D plans to retire from professional practice and cease all business activities nine years from now. The plan for the garden tool is to produce and sell it for eight years and then sell the patent and production rights to a national company. Entrepreneur D has negotiated a tentative lease on a building that is well suited for this manufacturing process. The building must be remodeled to meet manufacturing needs, and then it must be restored to its original configuration at the end of the eight-year lease. At that time Entrepreneur D will be able to sell some of the non-specialized equipment (e.g., forklifts) for small salvage values. Building remodeling and new equipment purchases will require a front-end investment. The remodeling and equipment costs will be capitalized and depreciated over the eight-year period as one depreciation calculation using straight line depreciation. Realizable salvage value from disposing of the equipment at the end of eight years is estimated at approximately $60,000. There is no salvage value for the remodeling improvements. The remodeling cost is given on the template in your individualized dataset. Additional working capital will be required for business operations. The working capital required is given on the template in your individualized dataset. Estimated annual cash receipts from tool sales are forecasted for the eight years of expected operations. The expected cash receipts are given on the template in your individualized dataset. Estimated cash expenses for materials, salaries, supplies, utilities, and other cash expenses are projected for each of the eight years of expected operations. The expected cash expenses are given on the template in your individualized dataset. The lease on the building requires that it be restored to its original configuration at the end of the expected eight years of operation. The estimated restoration cost s number is given on the template in your individualized dataset. The working capital tied up in this project will become available for other types of investments at the end of the eight-year period. Entrepreneur D has asked you to assume a 12% applicable weighted average cost of capital. Entrepreneur D has also asked you to assume a combined federal and state income tax rate. The tax rate is given on the template in your indvidualized dataset. Task: A. Complete the attached \"Capital Budgeting Template\" by doing the following: 1. Calculate the net cash flow that should be used for each year in the discounted cash flow analysis. 2. Calculate the net present value (NPV) of this project using a discount rate equal to the company's weighted average cost of capital. Round all dollar amounts to the nearest whole dollar. 3. Calculate the expected yield on the project using the discounted cash flow internal rate of return (IRR) method. Round all dollar amounts to the nearest whole dollar. 4. Calculate the accounting rate of return for this project. 5. Calculate the unadjusted payback period. State your answers in years and months. B. Prepare a computer-based presentation in which you do the following: 1. Identify what the correct net cash flow for the second year would be if all cash expenses were as described in the scenario but there were no depreciation expense. a. Explain the impact of depreciation on net cash flow for the second year. 2. Based upon your NPV analysis in part A2, make a recommendation to Entrepreneur D regarding what decision to make. a. Explain why this is an appropriate action. 3. Based upon your IRR analysis in part A3, make a recommendation to Entrepreneur D 4. 5. 6. 7. regarding what decision to make. a. Explain why this is an appropriate action. Explain why the accounting rate of return on this project is different from the internal rate of return for the same capital investment. Explain the relative significance of the unadjusted payback period in this decision situation. Explain how the weighted average cost of capital should be used in capital budgeting analysis when utilizing the NPV method. Explain how the weighted average cost of capital should be used in capital budgeting analysis when utilizing the IRR method. C. When you use sources, include all in-text citations and references in APA format. Note: For definitions of terms commonly used in the rubric, see the attached Rubric Terms. Note: When using sources to support ideas and elements in a paper or project, the submission MUST include APA formatted in-text citations with a corresponding reference list for any direct quotes or paraphrasing. It is not necessary to list sources that were consulted if they have not been quoted or paraphrased in the text of the paper or project. Note: No more than a combined total of 30% of a submission can be directly quoted or closely paraphrased from sources, even if cited correctly. For tips on using APA style, please refer to the APA Handout web link included in the General Instructions section. Here are some helpful APA resources: http://www.apastyle.org http://www.citationmachine.net http://owl.english.purdue.edu/owl/resource/560/01