Capital Budgeting Assignment in Excel (Vardan Inc.)
Question # 00061583
Posted By:
Updated on: 04/16/2015 12:26 PM Due on: 04/17/2015
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.
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.
-
Rating:
/5
Solution: Capital Budgeting Assignment in Excel (Vardan Inc.)