Would you consider the investment made in Project 4 optimally financed considering the proportion of debt that is bearable by LGI?

BELOW IS THE PROJECT 4 EXCEL SHEETS TO ANSWER THE QUESTION ABOVE

P4_Final_Hollis_Calculation_date Qw Search Sheet Home Insert Draw Page Layout Formulas Data Review View Acrobat + Share Insert = A- A - So Wrap Text Currency Calibri (Body) - 12 I U WO 49: Q Ex Delete Paste B Merge & Center $ - % % ) . .00 .00 2.0 Conditional Format Cell Formatting as Table Styles i Format Sort & Filter Find & Select Create and Share Adobe PDF A29 fx =FV(0.0475,5,0,1500) A B D E F G G . 1 1 J K L N N 0 o P Q 0 R s T U U v w X Y z AA 20 1. Briefly explain the meaning of the term "present value in your own words. 21 Present value is basically the amount of currency today is worth more than the currency in the future. 22 22 23 23 24 2. Briefly explain the meaning of the term "future value in your own words. 25 Future value is what the sum of money invested today will become over time at a rate of interest. 26 27 2 28 3. What is the future value in five years of $1,500 invested at an interest rate of 4.75%? 29 ($1,891.74) SU 30 31 S 32 4. What is the future value of a single payment with the following characteristics? 33 PV $950 34 NPER 6 years $1,273.09 35 RATE 5% 36 fv=pv(1+rate)"nper 37 38 39 5. What is the present value of $62,000 in six years, if the relevant interest rate is 8.1%? 40 ($38,854.16) 41 42 43 6. What is the present value of a single payment with the following characteristics? a 44 NPER 11 years 45 RATE 7% 46 FV $10,000 $ 47 ($4,750.93) 48 49 50 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? 51 4.66% 52 53 54 8. What is the annual rate of return of a single payment with the following characteristics? 55 PV $1,000 56 NPER 12 years 57 FV $10,000 58 21.15% 59 60 Instructions Tab 1 - TVM Tab 2 - Annuities Tab 3 - Capital Budgeting + Ready @ a + 100% P4_Final_Hollis_Calculation_date Qw Search Sheet Home Insert Draw Page Layout Formulas Data Review View Acrobat + Share Insert Calibri (Body) - 12 = A A- So Wrap Text General 49 X WOO Q Paste B I U A A Merge & Center $ % % ) . .00 .00 2.0 Delete Format Conditional Format Cell Formatting as Table Styles i Sort & Filter Find & Select Create and Share Adobe PDF E72 fx =SUM(C72:C76) B D E F G H 1 j L M M N 0 R S T U v w X Y Z Z 7 8 4 1. How many years would be required to pay off a loan with the following characteristics? 5 5 PV $11,5001 6 RATE 8% FV=PV(1+RATE)ANPER PMT $1,600 (annual payments) 11.118172 9 10 11 2. What is the annual payment required to pay off a loan with the following characteristics? 12 PV $14,700 ($2,290.56) 13 RATE 9% 14 NPER 10 years 15 16 17 18 3. Why is FV not part of the calculations for either question 1 or question 2? 1 2? 19 It was not needed to calculate the equation. 20 21 4. At what annual rate of interest is a loan with the following characteristics? 23 24 NPER 12 years 25 PMT $100,000 2.92% 26 PV $1,000,000 27 28 29 30 For questions 5-8, LGI's cost of capital is 8.05% 31 32 5. LGI projects the following after-tax cash flows from operations from 33 its aging Bowie, Maryland plant (which first went on line in 1953) 34 over the next five years. What is the PV of these cash flows? 35 20 36 Projected after-tax cash flows 37 Year (in $ millions) 38 1 (45) ($41.65) pv=c"[(1-(1+i)n-n/i] 20 39 2 (45)| $38.54 40 3 2 ( (45) $35.67 ($179.44) 41 4 4 (45) $33.02 5 (45) $30.56 43 42 44 42 Instructions Tab 1 - TVM Tab 2 - Annuities Tab 3 - Capital Budgeting + Ready @ a + 100% P4_Final_Hollis_Calculation_date Qw Search Sheet Home Insert Draw Page Layout Formulas Data Review View Acrobat + Share Insert Calibri (Body) - 12 = A A- So Wrap Text General WE 29 Q Ex Delete X Paste B I U A Merge & Center $ % ) . .00 .00 2.0 Conditional Format Cell Formatting as Table Styles i Format Sort & Filter Find & Select Create and Share Adobe PDF E72 fx =SUM(C72:C76) G K L M M N 0 R S U V V w X Y Z Z bb A B D E F H 1 46 6. LGI extended the analysis out for an additional 7 years, and generated the 47 following projections. What is the PV of these cash flows? 48 49 Projected after-tax cash flows 50 Year in $ millions) 51 1 (45) 41.6474 52 2 (45) 38.5445 53 3 (45) 35.6729 54 4 (45) 33.0152 e 55 5 (45) 30.5554 56 6 (45) 28.279) 57 7 (45)| 26.1721 . 58 8 (45) 24.2222 59 9 (45) 22.4176 60 10 (45)| 20.7475 61 11 (45) 19.2017 62 12 (45)| 17.7711 63 64 65 co 7. The CFO asked the team to undertake a more detailed analysis of the plant's costs, noting that while 67 it is convenient for making calculations when projections result in data that can be treated like an annuity, 68 this does not always represent the most accurate estimate of future results. What is the PV of these cash flows? 69 70 Projected after-tax cash flows 71 Year in $ millions) 20 72 1 (45) 41.6474 219.6261 73 2 2 2 (50) 42.8273 74 3 (55) 43.6002 75 4 4 (60)| 44.0202 76 5 5 (70) 47.5307 77 78 79 8. LGI received four preliminary offers from potential buyers interested in acquiring 81 the Bowie factory. What is the PV of each offer? Which offer should LGI accept? 82 83 Offer A $101 million, paid today 101 84 Offer B $20 million per year, to be paid over the next 8 years $ $114.72 85 Offer C $201 million, to be paid in year 8 $108.19 86 Offer D |$18 million per year, to be paid over the next 7 years 87 plus a $50 million payment in year 8 Instructions Tab 1 - TVM Tab 2 - Annuities Tab 3 - Capital Budgeting Ready 80 $120.47 + @ a + 100% P4_Final_Hollis_Calculation_date Qw Search Sheet Home Insert Draw Page Layout Formulas Data Review View Acrobat + Share Insert = Calibri (Body) - 11 A- A So Wrap Text Percentage WO A9 Q - X Delete Paste B I U A Merge & Center $ % ) -.0 .00 .00 1.0 Conditional Format Cell Formatting as Table Styles Ti Format Sort & Filter Find & Select Create and Share Adobe PDF G62 fx fx IRR(128:136) : A B D E F F G H 1 J K L M N O P Q PV #NAME? 13.2 63.0 77.4 64.5 58.2 80.0 28.2 13.3 19 Table 1 - Data 20 Cost of the new manfactoring equipment (at year=0) 191.1 million 21 Corporate income tax rate - Federal 21.0% 22 Corporate income tax rate - State of Maryland 8.0% 23 Discount rate for the project 6.0% 24 25 Table 2 - After-tax Cash Flow Timeline 26 (all figures in $ millions) Projected Cash Projected Cash Inflows from Outflows from Depreciation Projected Taxable Projected Federal Projected State Projected After-tax 27 Year Operations Operations Expense Income Income Taxes Income Taxes Cash Flows 28 0 23.9 (191.1) 29 1 850.0 840.0 23.9 (13.9) (2.92) (1.1) 14.03 30 2 900.0 810.0 23.9 66.1 13.88 5.3 70.83 31 3 990.0 870.0 23.9 96.1 20.18 7.7 92.13 . 32 4 1,005.0 900.0 23.9 81.1 17.03 6.5 81.48 33 5 1,200,0 1,100.0 23.9 76.1 15.98 6.1 77.93 34 6 1,300.0 1,150.0 23.9 126.1 26.48 10.1 113.43 35 7 1,350.0 1,300.0 23.9 26.1 5.48 2.1 42.43 36 8 8 1,320.0 1,300.0 23.9 (3.9) (0.82) (0.3) 21.13 37 20 30 Table 3 - Example - Computing Projected After-tax Cash Flows 33 39 For Year 4 (all figures in $ millions) 40 Projected Cash Inflows from Operations 1005.0 Projected Cash Inflows from Operations 41 minus Projected Cash Outflows from Operations (900.0) Projected Cash Outflows from Operations 42 minus Depreciation Expense (23.9) minus Depreciation Exp - (Depreciation is not a cash flow) Projected Taxable income 81.1 minus Projected Federal Income Taxes 44 Projected State Income Taxes 45 Projected Taxable income 81.1 Projected After-tax Cash Flows 46 times Corporate income tax rate - Federal 21.0% Projected Federal Income Taxes 17.0 48 49 Projected Taxable income 81.1 50 times Corporate income tax rate - State 8.0% Projected State Income Taxes 6.5 52 24 53 1. Complete Table 2. Compute the projected after tax cash flows for each of years 1-8. 54 3 55 56 2. Compute the total present value (PV) of the projected after tax cash flows for years 1-8. pv= $397.83 57 minus 1005.0 (900.0) (23.9) (17.0) ( (6.5) 57.6 43 equals equals 47 equals 51 equals Instructions Tab 1 - TVM Tab 2 - Annuities Tab 3 - Capital Budgeting + Ready @ a + 100% P4_Final_Hollis_Calculation_date Q Search Sheet Home Insert Draw Page Layout Formulas Data Review View Acrobat + Share Insert = Calibri (Body) - 11 A- A So Wrap Text Percentage A9 Q WOO Paste B I U + Merge & Center $ % ) 2.0 .00 .00 .0 Conditional Format Cell Formatting as Table Styles - X Delete I Format Sort & Filter Find & Select Create and Share Adobe PDF G62 fx =IRR(128:136) A B D E F G H 1 K L M N O P $206.73 27% $415.22 $224.12 58 59 3. Compute the net present value (NPV) of the projected after tax cash flows for years 0-8. npv= 60 NPV=129/(1.06)-A29 61 62 4. Compute the internal rate of return (IRR) of the project. Irra 63 64 65 5. The CFO believes that it is possible that the next few years will bring a very low interest rate environment. 66 Therefore, she has asked that you repeat the NPV calculation in question 3 showing the case where the 67 discount rate for the project is 4.95% 68 69 pv 70 npv 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 Instructions Tab 1 - TVM Tab 2 - Annuities Tab 3 - Capital Budgeting Ready + @ a + 100% P4_Final_Hollis_Calculation_date Qw Search Sheet Home Insert Draw Page Layout Formulas Data Review View Acrobat + Share Insert = A- A - So Wrap Text Currency Calibri (Body) - 12 I U WO 49: Q Ex Delete Paste B Merge & Center $ - % % ) . .00 .00 2.0 Conditional Format Cell Formatting as Table Styles i Format Sort & Filter Find & Select Create and Share Adobe PDF A29 fx =FV(0.0475,5,0,1500) A B D E F G G . 1 1 J K L N N 0 o P Q 0 R s T U U v w X Y z AA 20 1. Briefly explain the meaning of the term "present value in your own words. 21 Present value is basically the amount of currency today is worth more than the currency in the future. 22 22 23 23 24 2. Briefly explain the meaning of the term "future value in your own words. 25 Future value is what the sum of money invested today will become over time at a rate of interest. 26 27 2 28 3. What is the future value in five years of $1,500 invested at an interest rate of 4.75%? 29 ($1,891.74) SU 30 31 S 32 4. What is the future value of a single payment with the following characteristics? 33 PV $950 34 NPER 6 years $1,273.09 35 RATE 5% 36 fv=pv(1+rate)"nper 37 38 39 5. What is the present value of $62,000 in six years, if the relevant interest rate is 8.1%? 40 ($38,854.16) 41 42 43 6. What is the present value of a single payment with the following characteristics? a 44 NPER 11 years 45 RATE 7% 46 FV $10,000 $ 47 ($4,750.93) 48 49 50 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? 51 4.66% 52 53 54 8. What is the annual rate of return of a single payment with the following characteristics? 55 PV $1,000 56 NPER 12 years 57 FV $10,000 58 21.15% 59 60 Instructions Tab 1 - TVM Tab 2 - Annuities Tab 3 - Capital Budgeting + Ready @ a + 100% P4_Final_Hollis_Calculation_date Qw Search Sheet Home Insert Draw Page Layout Formulas Data Review View Acrobat + Share Insert Calibri (Body) - 12 = A A- So Wrap Text General 49 X WOO Q Paste B I U A A Merge & Center $ % % ) . .00 .00 2.0 Delete Format Conditional Format Cell Formatting as Table Styles i Sort & Filter Find & Select Create and Share Adobe PDF E72 fx =SUM(C72:C76) B D E F G H 1 j L M M N 0 R S T U v w X Y Z Z 7 8 4 1. How many years would be required to pay off a loan with the following characteristics? 5 5 PV $11,5001 6 RATE 8% FV=PV(1+RATE)ANPER PMT $1,600 (annual payments) 11.118172 9 10 11 2. What is the annual payment required to pay off a loan with the following characteristics? 12 PV $14,700 ($2,290.56) 13 RATE 9% 14 NPER 10 years 15 16 17 18 3. Why is FV not part of the calculations for either question 1 or question 2? 1 2? 19 It was not needed to calculate the equation. 20 21 4. At what annual rate of interest is a loan with the following characteristics? 23 24 NPER 12 years 25 PMT $100,000 2.92% 26 PV $1,000,000 27 28 29 30 For questions 5-8, LGI's cost of capital is 8.05% 31 32 5. LGI projects the following after-tax cash flows from operations from 33 its aging Bowie, Maryland plant (which first went on line in 1953) 34 over the next five years. What is the PV of these cash flows? 35 20 36 Projected after-tax cash flows 37 Year (in $ millions) 38 1 (45) ($41.65) pv=c"[(1-(1+i)n-n/i] 20 39 2 (45)| $38.54 40 3 2 ( (45) $35.67 ($179.44) 41 4 4 (45) $33.02 5 (45) $30.56 43 42 44 42 Instructions Tab 1 - TVM Tab 2 - Annuities Tab 3 - Capital Budgeting + Ready @ a + 100% P4_Final_Hollis_Calculation_date Qw Search Sheet Home Insert Draw Page Layout Formulas Data Review View Acrobat + Share Insert Calibri (Body) - 12 = A A- So Wrap Text General WE 29 Q Ex Delete X Paste B I U A Merge & Center $ % ) . .00 .00 2.0 Conditional Format Cell Formatting as Table Styles i Format Sort & Filter Find & Select Create and Share Adobe PDF E72 fx =SUM(C72:C76) G K L M M N 0 R S U V V w X Y Z Z bb A B D E F H 1 46 6. LGI extended the analysis out for an additional 7 years, and generated the 47 following projections. What is the PV of these cash flows? 48 49 Projected after-tax cash flows 50 Year in $ millions) 51 1 (45) 41.6474 52 2 (45) 38.5445 53 3 (45) 35.6729 54 4 (45) 33.0152 e 55 5 (45) 30.5554 56 6 (45) 28.279) 57 7 (45)| 26.1721 . 58 8 (45) 24.2222 59 9 (45) 22.4176 60 10 (45)| 20.7475 61 11 (45) 19.2017 62 12 (45)| 17.7711 63 64 65 co 7. The CFO asked the team to undertake a more detailed analysis of the plant's costs, noting that while 67 it is convenient for making calculations when projections result in data that can be treated like an annuity, 68 this does not always represent the most accurate estimate of future results. What is the PV of these cash flows? 69 70 Projected after-tax cash flows 71 Year in $ millions) 20 72 1 (45) 41.6474 219.6261 73 2 2 2 (50) 42.8273 74 3 (55) 43.6002 75 4 4 (60)| 44.0202 76 5 5 (70) 47.5307 77 78 79 8. LGI received four preliminary offers from potential buyers interested in acquiring 81 the Bowie factory. What is the PV of each offer? Which offer should LGI accept? 82 83 Offer A $101 million, paid today 101 84 Offer B $20 million per year, to be paid over the next 8 years $ $114.72 85 Offer C $201 million, to be paid in year 8 $108.19 86 Offer D |$18 million per year, to be paid over the next 7 years 87 plus a $50 million payment in year 8 Instructions Tab 1 - TVM Tab 2 - Annuities Tab 3 - Capital Budgeting Ready 80 $120.47 + @ a + 100% P4_Final_Hollis_Calculation_date Qw Search Sheet Home Insert Draw Page Layout Formulas Data Review View Acrobat + Share Insert = Calibri (Body) - 11 A- A So Wrap Text Percentage WO A9 Q - X Delete Paste B I U A Merge & Center $ % ) -.0 .00 .00 1.0 Conditional Format Cell Formatting as Table Styles Ti Format Sort & Filter Find & Select Create and Share Adobe PDF G62 fx fx IRR(128:136) : A B D E F F G H 1 J K L M N O P Q PV #NAME? 13.2 63.0 77.4 64.5 58.2 80.0 28.2 13.3 19 Table 1 - Data 20 Cost of the new manfactoring equipment (at year=0) 191.1 million 21 Corporate income tax rate - Federal 21.0% 22 Corporate income tax rate - State of Maryland 8.0% 23 Discount rate for the project 6.0% 24 25 Table 2 - After-tax Cash Flow Timeline 26 (all figures in $ millions) Projected Cash Projected Cash Inflows from Outflows from Depreciation Projected Taxable Projected Federal Projected State Projected After-tax 27 Year Operations Operations Expense Income Income Taxes Income Taxes Cash Flows 28 0 23.9 (191.1) 29 1 850.0 840.0 23.9 (13.9) (2.92) (1.1) 14.03 30 2 900.0 810.0 23.9 66.1 13.88 5.3 70.83 31 3 990.0 870.0 23.9 96.1 20.18 7.7 92.13 . 32 4 1,005.0 900.0 23.9 81.1 17.03 6.5 81.48 33 5 1,200,0 1,100.0 23.9 76.1 15.98 6.1 77.93 34 6 1,300.0 1,150.0 23.9 126.1 26.48 10.1 113.43 35 7 1,350.0 1,300.0 23.9 26.1 5.48 2.1 42.43 36 8 8 1,320.0 1,300.0 23.9 (3.9) (0.82) (0.3) 21.13 37 20 30 Table 3 - Example - Computing Projected After-tax Cash Flows 33 39 For Year 4 (all figures in $ millions) 40 Projected Cash Inflows from Operations 1005.0 Projected Cash Inflows from Operations 41 minus Projected Cash Outflows from Operations (900.0) Projected Cash Outflows from Operations 42 minus Depreciation Expense (23.9) minus Depreciation Exp - (Depreciation is not a cash flow) Projected Taxable income 81.1 minus Projected Federal Income Taxes 44 Projected State Income Taxes 45 Projected Taxable income 81.1 Projected After-tax Cash Flows 46 times Corporate income tax rate - Federal 21.0% Projected Federal Income Taxes 17.0 48 49 Projected Taxable income 81.1 50 times Corporate income tax rate - State 8.0% Projected State Income Taxes 6.5 52 24 53 1. Complete Table 2. Compute the projected after tax cash flows for each of years 1-8. 54 3 55 56 2. Compute the total present value (PV) of the projected after tax cash flows for years 1-8. pv= $397.83 57 minus 1005.0 (900.0) (23.9) (17.0) ( (6.5) 57.6 43 equals equals 47 equals 51 equals Instructions Tab 1 - TVM Tab 2 - Annuities Tab 3 - Capital Budgeting + Ready @ a + 100% P4_Final_Hollis_Calculation_date Q Search Sheet Home Insert Draw Page Layout Formulas Data Review View Acrobat + Share Insert = Calibri (Body) - 11 A- A So Wrap Text Percentage A9 Q WOO Paste B I U + Merge & Center $ % ) 2.0 .00 .00 .0 Conditional Format Cell Formatting as Table Styles - X Delete I Format Sort & Filter Find & Select Create and Share Adobe PDF G62 fx =IRR(128:136) A B D E F G H 1 K L M N O P $206.73 27% $415.22 $224.12 58 59 3. Compute the net present value (NPV) of the projected after tax cash flows for years 0-8. npv= 60 NPV=129/(1.06)-A29 61 62 4. Compute the internal rate of return (IRR) of the project. Irra 63 64 65 5. The CFO believes that it is possible that the next few years will bring a very low interest rate environment. 66 Therefore, she has asked that you repeat the NPV calculation in question 3 showing the case where the 67 discount rate for the project is 4.95% 68 69 pv 70 npv 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 Instructions Tab 1 - TVM Tab 2 - Annuities Tab 3 - Capital Budgeting Ready + @ a + 100%