This assignment consists of two parts.
The first part is calculations on the excel workbook.
Below are the instructions for part 1 (excel workbook)
Answer all questions in this workbook. Be sure to read the introductory text on tabs 1 and 3 as well as these instructions.
Keep in mind that the focus of this project is corporate finance. The information generated by the accounting system is important; but in finance, decisions are driven by an analysis of cash flows rather than profits.
|
Tab 1 contains a series of exercises on the concept of the time value of money. These exercises do not relate directly to the issues facing LGI.
Tab 2 focuses on the concept of annuities. The first few questions do not pertain specifically to LGI; the latter questions do.
Tab 3 pertains to whether LGI should acquire assets that may enhance the company's productivity and thus improve financial performance.
Part 2 is five questions regarding the calculations from part 1. |
Project 4 Questions - Report Template Project 4 Questions - Report Template Instructions: Answer the five questions below. Base your analysis only on the Excel workbook. Provide a detailed response below each question. Use 12-point font and double spacing. Maintain the existing margins in this document. Your final Word document, including the questions, should not exceed 5 pages. Include a title page in addition to the five pages. Any tables and graphs you choose to include are also excluded from the five-page limit. Name your document as follows: P4_Final_lastname_Report_date. Title Page Name Course and section number Faculty name Submission date 1. Present-value calculations, rather than future-value calculations, are the key to analysis in the field of corporate finance. Why is this the case? Explain the importance for Largo Global Inc. (LGI) of understanding today's value of projected future revenues and/or costs. [insert your answer here] 2. Based on your calculations in Tab 2, Question 8, which offer should LGI accept for the Bowie plant? Explain why. Be sure to include the concepts of risk and potential return as part of your discussion. [insert your answer here] 3. The proposed sale of the Bowie plant is part of the effort to divest the company of underperforming assets. A total of $1.2 billion in assets, with a book value of $650 million, have been identified for potential sale. Assuming that all these sales could be accomplished in 2021, identify the major impacts on the following: a. Balance Sheet, especially these accounts: · Property, plant, and equipment · Accumulated depreciation · Net property, plant, and equipment b. Statement of Cash Flows, especially the Long-Term Investing Activities c. Income Statement Explain the potential impacts, both positive and negative, of these changes for LGI. [insert your answer here] 4. Based on your calculations in Tab 3, Questions 1–4, should LGI proceed with the acquisition of the robotics-based manufacturing equipment? Explain your reasoning. How would the acquisition fit into the efforts to turn the company around? [insert your answer here] 5. In Tab 3, Question 5, did the change in the discount rate make proceeding with the purchase more or less desirable? What do you conclude from this result? Discuss the role of discount rates in LGI's decision-making processes. [insert your answer here] Project 4 Excel Workbook Instructions Instructions Answer all questions in this workbook. Be sure to read the introductory text on tabs 1 and 3 as well as these instructions. Keep in mind that the focus of this project is corporate finance. The information generated by the accounting system is important; but in finance, decisions are driven by an analysis of cash flows rather than profits. Tab 1 contains a series of exercises on the concept of the time value of money. These exercises do not relate directly to the issues facing LGI. Tab 2 focuses on the concept of annuities. The first few questions do not pertain specifically to LGI; the latter questions do. Tab 3 pertains to whether LGI should acquire assets that may enhance the company's productivity and thus improve financial performance. Tab 1 - TVM 1. Briefly explain the meaning of the term "present value" in your own words. 2. Briefly explain the meaning of the term "future value" in your own words. 3. What is the future value in five years of $1,500 invested at an interest rate of 4.75%? 4. What is the future value of a single payment with the following characteristics? PV$950 NPER6years RATE5% 5. What is the present value of $62,000 in six years, if the relevant interest rate is 8.1%? 6. What is the present value of a single payment with the following characteristics? NPER11years RATE7% FV$10,000 7. The present value of a payment is $4000. The future value of that payment in four years will be $4800. What is the annual rate of return? 8. What is the annual rate of return of a single payment with the following characteristics? PV$1,000 NPER12years FV$10,000 Time value of money (TVM) exercises There are five variables in TVM calculations: present value, number of periods, rate of return, regular payments, and future value. If four of the variables are known, then the fifth can be calculated using algebra, a financial calculator, or a computer program such as Excel. Excel functions for the five variables are as follows: PV—present value NPER—number of periods RATE—rate of return PMT—regular payments FV—future value Tab 2 - Annuities 1. How many years would be required to pay off a loan with the following characteristics? PV$11,500 RATE8% PMT$1,600(annual payments) 2. What is the annual payment required to pay off a loan with the following characteristics? PV$14,700 RATE9% NPER10years 3. Why is FV not part of the calculations for either question 1 or question 2? 4. At what annual rate of interest is a loan with the following characteristics? NPER12years PMT$100,000 PV$1,000,000 For questions 5-8, LGI's cost of capital is8.05% 5. LGI projects the following after-tax cash flows from operations from its aging Bowie, Maryland plant (which first went on line in 1953) over the next five years. What is the PV of these cash flows? Projected after-tax cash flows Year(in $ millions) 1(45) 2(45) 3(45) 4(45) 5(45) 6. LGI extended the analysis out for an additional 7 years, and generated the following projections. What is the PV of these cash flows? Projected after-tax cash flows Year(in $ millions) 1(45) 2(45) 3(45) 4(45) 5(45) 6(45) 7(45) 8(45) 9(45) 10(45) 11(45) 12(45) 7. The CFO asked the team to undertake a more detailed analysis of the plant's costs, noting that while it is convenient for making calculations when projections result in data that can be treated like an annuity, this does not always represent the most accurate estimate of future results. What is the PV of these cash flows? Projected after-tax cash flows Year(in $ millions) 1(45) 2(50) 3(55) 4(60) 5(70) 8. LGI received four preliminary offers from potential buyers interested in acquiring the Bowie factory. What is the PV of each offer? Which offer should LGI accept? Offer A$101 million, paid today Offer B$20 million per year, to be paid over the next 8 years Offer C$201 million, to be paid in year 8 Offer D$18 million per year, to be paid over the next 7 years plus a $50 million payment in year 8 Tab 2 - Annuities Tab 3 - Capital Budgeting Table 1 - Data Cost of the new manfactoring equipment (at year=0)$191.1million Corporate income tax rate - Federal21.0% Corporate income tax rate - State of Maryland8.0% Discount rate for the project 6.0% Table 2 - After-tax Cash Flow Timeline (all figures in $ millions) YearProjected Cash Inflows from OperationsProjected Cash Outflows from OperationsDepreciation ExpenseProjected Taxable IncomeProjected Federal Income TaxesProjected State Income TaxesProjected After-tax Cash Flows 0 1850.0840.0 2900.0810.0 3990.0870.0 41,005.0900.0 51,200.01,100.0 61,300.01,150.0 71,350.01,300.0 81,320.01,300.0 Table 3 - Example - Computing Projected After-tax Cash Flows For Year 4(all figures in $ millions) Projected Cash Inflows from Operations1005.0Projected Cash Inflows from Operations1005.0 minusProjected Cash Outflows from Operations(900.0)minusProjected Cash Outflows from Operations(900.0) minusDepreciation Expense(23.9)minusDepreciation Exp - (Depreciation is not a cash flow)0.0 equalsProjected Taxable Income81.1minusProjected Federal Income Taxes(17.0) equalsProjected State Income Taxes(6.5) Projected Taxable Income81.1Projected After-tax Cash Flows 81.5 timesCorporate income tax rate - Federal21.0% equalsProjected Federal Income Taxes17.0 Projected Taxable Income81.1 timesCorporate income tax rate - State8.0% equalsProjected State Income Taxes6.5 1. Complete Table 2. Compute the projected after tax cash flows for each of years 1-8. 2. Compute the total present value (PV) of the projected after tax cash flows for years 1-8. 3. Compute the net present value (NPV) of the projected after tax cash flows for years 0-8. 4. Compute the internal rate of return (IRR) of the project. 5. The CFO believes that it is possible that the next few years will bring a very low interest rate environment. Therefore, she has asked that you repeat the NPV calculation in question 3 showing the case where the discount rate for the project is4.95% Technologically advanced manufacturing equipment proposal LGI has decided to divest itself of some unproductive factory assets. The vice president of production is proposing to acquire robotics-based manufacturing equipment to enable more cost-effective production. The CFO has asked you to evaluate the cash flow projections associated with the equipment purchase proposal and recommend whether the purchase should go forward. Table 2 shows projections of the cash inflows and outflows that would occur during the first eight years using the new equipment. Keep the following in mind: Depreciation. The equipment will be depreciated using the straight-line method over eight years. The projected salvage value is $0. Taxes. The CFO estimates that company operations as a whole will be profitable on an ongoing basis. As a result, any accounting loss on this specific project will provide a tax benefit in the year of the loss. Project 4 Review and Practice Guide