To be done in Excel

Jamshedpur Utilities Company plans to buy and install a water-processing machine for massive fresh water production. The equipment costs $1,250,000 with a 6-year life. It is estimated to be worth of $350,000 when the project expires (assume straight-line depreciation). The annual fixed cost of this project is $ 240,000. Starting from next year, 200,000 tons of fresh water will be produced by this machine each year. The price for each ton of water sold to public is $4.00 in the first year and grows at 8% annually. On the other hand, the cost of each ton is stable at $1.40. At the end of the project, the machine will be actually sold at a salvage value of $ 600,000. Assuming the tax rate is 40% and the required rate of return is 9%.

1. Create a table of operating cash flow statement for Jamshedpur Company in an Excel worksheet. Please show Initial Outlay, Annual after-tax cash flows and terminal cash flows. Calculate the NPV of this project.

worst case
below-average case
base case above-average case bestcase

the cost of each ton produced
$2.00
$1.80 $1.40 $1.30 $1.10

the price of each ton at 1st year $2.20 $3.00 $4.00 $5.00 $6.00

probability 10% 20% 40% 20% 10%

2. Based on the information provided in the table above, please calculate the expected NPV, and the standard deviation of the NPVs. Use “Scatter Chart with Smooth line” of Excel to display the Probabilities and the NPVs for each case. In this chart, you should have 5 columns for 5 cases. The horizontal axis stands for NPV and the vertical axis stands for probability

3. Report the project NPV when the Cost of Equipment changes by 30%, 0%, -30%, separately. Repeat the same procedure to get the NPVs in response to the changes of Water Price.

4. Create a sensitivity diagram with percentage change of variable as the horizontal axis and percentage change of NPV as the vertical axis. This diagram should include 2 straight lines for 2 variables. Which variable is more influential to NPV, and how do you draw the conclusion?

The post Capital Budgeting question help appeared first on Essaysholic.

Would you like to get in touh with us?
Contact Us