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 LGl; the latter questions do. Tab 3 pertains to whether LGI should acquire new assets that may enhance the company's productivity and thus improve financial performance. 22 AutoSave Oof G C Home Insert Draw Page Layout Formulas Data Review View Automate @ Tell me Instructions Tab 1 - TVM Tab 2 - Annuities Tab 3 - Capital Budgeting AutoSave off Project4ExcelWorkbook Home Insert Draw Page Layout Formulas Data Review View Automate @ Tell me A 80 Calibri (Body) Paste B I U 12AA ab Wrap Text General ^fx 8. As part of a larger plan to sell off underperforming assets, LGI is considering selling the Bowie property A B C D E G \begin{tabular}{l|l|l|l} H & I & J & K \end{tabular} LM NO 0p Q R S T U V W X Y Z 1. How many years would be required to pay off a loan with the following characteristics? \begin{tabular}{|l|r|l|l|} \hline PV & $11,500 & & \\ \hline RATE & 10.6% & & \\ \hline PMT & $1,600 & (annual payments) \\ \hline \end{tabular} 2. What is the annual payment required to pay off a loan with the following characteristics? \begin{tabular}{|l|r|l|} \hline PV & $14,700 & \\ \hline RATE & 10.0% & \\ \hline NPER & 10 & years \\ \hline \end{tabular} 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? \begin{tabular}{|l|r|l|} \hline NPER & 17 & years \\ \hline PMT & $100,000 & \\ \hline PV & $1,000,000 & \\ \hline \end{tabular} For questions 5-8, LGI's cost of capital is 8.11% 5. LGI projects the following after-tax cash flows from operations from its aging Bowie, Maryland distribution facility (which first went on line in 1953) over the next five years. What is the PV of these cash flows? \begin{tabular}{|r|r|r|} \hline & \multicolumn{2}{|l|}{ Projected after-tax cash flows } \\ \hline Year & (in \$ millions) & \\ \hline 1 & (40) & \\ \hline 2 & (40) \\ \hline 3 & (40) \\ \hline 4 & (40) \\ \hline 5 & (40) \\ \hline & & \\ \hline \end{tabular} Instructions Tab 1 - TVM Tab 2 - Annuities Tab 3 - Capital Budgeting Ready ix Accessibility: Investigate 6. LGI extended the analysis out for an additional 7 years, and generated the following projections. What is the PV of these cash flows? Yeal an? 7. The CFO asked you 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? AutoSave Oof G C Home Insert Draw Page Layout Formulas Data Review View Automate @ Tell me Paste B I U A 69 fx Project4ExcelWorkbook Tell me B C D D Table 3 - Example - Computing Projected After-tax Cash Flows 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 08. 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 is 5.02% Instructions Tab 1 - TVM Tab 2 - Annuities Tab 3 - Capital Budgeting Ready ix Accessibility: Investigate AutoSave off Home Insert Draw Page Layout Formulas Data Review View Automate @ Tell me Paste of Calibri (Body) B IU A69 fx Project4ExcelWorkbook Tell me Comments Share B C D F G J Robotics-based equipment proposal If the Bowie plant is sold, those operations will need to shift to the main Largo facility. The CEO is proposing to acquire robotics-based sorting and distribution equipment to facilitate more cost-effective operations (and be able to handle the increased workload) at Largo. 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. Instructions Tab 1 - TVM Tab 2 - Annuities Tab 3 - Capital Budgeting Ready ix Accessibility: Investigate 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 LGl; the latter questions do. Tab 3 pertains to whether LGI should acquire new assets that may enhance the company's productivity and thus improve financial performance. 22 AutoSave Oof G C Home Insert Draw Page Layout Formulas Data Review View Automate @ Tell me Instructions Tab 1 - TVM Tab 2 - Annuities Tab 3 - Capital Budgeting AutoSave off Project4ExcelWorkbook Home Insert Draw Page Layout Formulas Data Review View Automate @ Tell me A 80 Calibri (Body) Paste B I U 12AA ab Wrap Text General ^fx 8. As part of a larger plan to sell off underperforming assets, LGI is considering selling the Bowie property A B C D E G \begin{tabular}{l|l|l|l} H & I & J & K \end{tabular} LM NO 0p Q R S T U V W X Y Z 1. How many years would be required to pay off a loan with the following characteristics? \begin{tabular}{|l|r|l|l|} \hline PV & $11,500 & & \\ \hline RATE & 10.6% & & \\ \hline PMT & $1,600 & (annual payments) \\ \hline \end{tabular} 2. What is the annual payment required to pay off a loan with the following characteristics? \begin{tabular}{|l|r|l|} \hline PV & $14,700 & \\ \hline RATE & 10.0% & \\ \hline NPER & 10 & years \\ \hline \end{tabular} 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? \begin{tabular}{|l|r|l|} \hline NPER & 17 & years \\ \hline PMT & $100,000 & \\ \hline PV & $1,000,000 & \\ \hline \end{tabular} For questions 5-8, LGI's cost of capital is 8.11% 5. LGI projects the following after-tax cash flows from operations from its aging Bowie, Maryland distribution facility (which first went on line in 1953) over the next five years. What is the PV of these cash flows? \begin{tabular}{|r|r|r|} \hline & \multicolumn{2}{|l|}{ Projected after-tax cash flows } \\ \hline Year & (in \$ millions) & \\ \hline 1 & (40) & \\ \hline 2 & (40) \\ \hline 3 & (40) \\ \hline 4 & (40) \\ \hline 5 & (40) \\ \hline & & \\ \hline \end{tabular} Instructions Tab 1 - TVM Tab 2 - Annuities Tab 3 - Capital Budgeting Ready ix Accessibility: Investigate 6. LGI extended the analysis out for an additional 7 years, and generated the following projections. What is the PV of these cash flows? Yeal an? 7. The CFO asked you 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? AutoSave Oof G C Home Insert Draw Page Layout Formulas Data Review View Automate @ Tell me Paste B I U A 69 fx Project4ExcelWorkbook Tell me B C D D Table 3 - Example - Computing Projected After-tax Cash Flows 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 08. 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 is 5.02% Instructions Tab 1 - TVM Tab 2 - Annuities Tab 3 - Capital Budgeting Ready ix Accessibility: Investigate AutoSave off Home Insert Draw Page Layout Formulas Data Review View Automate @ Tell me Paste of Calibri (Body) B IU A69 fx Project4ExcelWorkbook Tell me Comments Share B C D F G J Robotics-based equipment proposal If the Bowie plant is sold, those operations will need to shift to the main Largo facility. The CEO is proposing to acquire robotics-based sorting and distribution equipment to facilitate more cost-effective operations (and be able to handle the increased workload) at Largo. 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. Instructions Tab 1 - TVM Tab 2 - Annuities Tab 3 - Capital Budgeting Ready ix Accessibility: Investigate