Capital Budgeting Assignment in Excel Spreadsheet problem based on chapters 5/6 ? Capital Budgeting Vardan Inc. is considering an investment in a new machine which has an estimated useful life of 3 years. The machine will be depreciated according to the MACRS recovery allowances for a 3-year class investment, resulting in depreciations of 33%, 45%, 15%, and 7% respectively in years 1 through 4. The initial cost of the machine is $1,050,000, including shipping and installation. The machine will result in an annual operating savings of $400,000 per year, and will allow a reduction in inventory of $75,000 over the useful life of the machine (this means a reduction of $75,000 at t=0, and then NWC stays at that level until the end of the project). After 3 years, inventory will return to its previous level. It is estimated that the machine can be sold for its salvage value of $250,000 at the end of 3 years. The firm's marginal tax rate is 30%. a) Calculate the project?s IRR and Payback b) Calculate the project?s NPV under the following assumptions: Project?s discount rate = 10%, 11%, 12%, etc,? until 19%, 20% (11 scenarios) c) Display the NPV-profile in a chart (discount rate on x-axis, NPV on y-axis) d) Repeat steps a) and b) under the following assumptions: Initial cost of the machine = $1,600,000 (incl. shipping and installation), annual savings = $600,000, reduction in inventory = $125,000, salvage value = $300,000, marginal tax rate = 20%. Everything else remains unchanged. e) Display the NPV-profile in the same chart like c) f) Calculate the crossover-rate and the corresponding NPV (Hint: use goal seek) Minimum requirements: - Show the setup for the two projects - Show for both projects IRR in % and Payback in years, 2 decimal places. - Show for both projects NPV numbers in $, no decimal places. - Display the NPV-profiles of both projects in the same chart chart. The preferred type of diagram for this problem is a so-called ?XY (scatter)-chart with data points connected by smoothed lines?. The discount rate has to be on the x-axis, the NPVs on the y-axis. - Use goal seek to calculate the crossover rate in %, rounded to two decimal places. Show the solution in a way that makes it clear how you calculated the number. Project assignment: Create a one-page spreadsheet solution that includes all the required parameters, including the NPVprofile. - Convert the excel-file to a pdf-file.

Spreadsheet problem based on chapters 5/6 - Capital Budgeting Vardan Inc. is considering an investment in a new machine which has an estimated useful life of 3 years. The machine will be depreciated according to the MACRS recovery allowances for a 3year class investment, resulting in depreciations of 33%, 45%, 15%, and 7% respectively in years 1 through 4. The initial cost of the machine is $1,050,000, including shipping and installation. The machine will result in an annual operating savings of $400,000 per year, and will allow a reduction in inventory of $75,000 over the useful life of the machine (this means a reduction of $75,000 at t=0, and then NWC stays at that level until the end of the project). After 3 years, inventory will return to its previous level. It is estimated that the machine can be sold for its salvage value of $250,000 at the end of 3 years. The firm's marginal tax rate is 30%. a) Calculate the project's IRR and Payback b) Calculate the project's NPV under the following assumptions: Project's discount rate = 10%, 11%, 12%, etc,... until 19%, 20% (11 scenarios) c) Display the NPV-profile in a chart (discount rate on x-axis, NPV on y-axis) d) Repeat steps a) and b) under the following assumptions: Initial cost of the machine = $1,600,000 (incl. shipping and installation), annual savings = $600,000, reduction in inventory = $125,000, salvage value = $300,000, marginal tax rate = 20%. Everything else remains unchanged. e) Display the NPV-profile in the same chart like c) f) Calculate the crossover-rate and the corresponding NPV (Hint: use goal seek) Minimum requirements: - Show the setup for the two projects - Show for both projects IRR in % and Payback in years, 2 decimal places. - Show for both projects NPV numbers in $, no decimal places. - Display the NPV-profiles of both projects in the same chart chart. The preferred type of diagram for this problem is a so-called \"XY (scatter)-chart with data points connected by smoothed lines\". The discount rate has to be on the x-axis, the NPVs on the y-axis. - Use goal seek to calculate the crossover rate in %, rounded to two decimal places. Show the solution in a way that makes it clear how you calculated the number. Project assignment: Create a one-page spreadsheet solution that includes all the required parameters, including the NPVprofile. - Convert the excel-file to a pdf-file