Data File needed for the Case Problem: Witte.xlsx
Witte Limestone Sheila Dawson is a manager at Witte Limestone, a ?rm specializing
in the excavation and processing of limestone. Sheila is currently working on a proposal
for excavating a new limestone quarry outside of the town of New Berlin, Wisconsin.
According to the most recent estimates, the area has enough limestone to support a
quarry for the next 20 years. There will be a substantial startup cost as well as substantial
costs at the end of the quarry’s useful lifetime. The quarry will be most pro?table in its
early and middle stages of development, but will lose pro?tability in its later stages as it
becomes more dif?cult to extract the remaining limestone deposits. Also, current envi-
ronmental regulations require the company to restore the area to its original condition
when the quarry work is ?nished. Sheila asks you to determine the pro?tability of the
investment in the New Berlin quarry. Complete the following:
1. Open the Witte workbook located in the Excel9\Case3 folder, and then save the
workbook as Witte Limestone in the same folder. In the Documentation worksheet,
enter your name and the date.
2. The initial cost to set up the quarry is $3,800,000. Enter this value as a negative cash
?ow in cell B5 of the Investment Analysis worksheet.
3. Sheila estimates the quarry will generate $170,000 in cash during its ?rst year of
operation, with the yearly cash ?ow increasing to $980,000 by the end of Year 5.
Interpolate the Year 1 through Year 5 cash ?ow values, assuming that the increase in
cash follows a growth trend.
4. From Year 5 to Year 10, Sheila estimates the yearly cash ?ow will increase from
$980,000 to $1,100,000. Interpolate the cash ?ow ?gures, assuming a linear trend in
the increase in cash. From Year 10 to Year 15, Sheila estimates the yearly cash ?ow
will decrease from $1,100,000 to $850,000. Interpolate the yearly cash ?ow ?gures
once again assuming a linear trend in the decline.
5. From Year 15 to Year 20, the yearly cash ?ow from the quarry will decline from
$850,000 to $50,000. Interpolate the yearly cash ?ow ?gures assuming a growth
trend in the decline in net cash.
6. In Year 21, the quarry will close and the company will spend an estimated
$12,000,000 to restore the area to its original pristine condition. Enter this value in
cell B26 as a negative cash ?ow.
7. Calculate the total cash ?ow of the project by entering Total in cell A27 and the sum
of the values in the range B5:B26 in cell B27. Format the range A27:B27 with the
Total cell style. Based on this estimate, will the quarry pay back the cost of the initial
investment and the environmental cleanup?
8. Create a chart of the net cash ?ow values from the range A6:B25 using the Scatter
with Straight Lines chart type. Place the new chart on a chart sheet named Cash
Flow Chart. Remove the legend from the chart. Add the chart title Yearly Cash
Returns from the New Berlin Quarry above the chart. Add the title Year to the hori-
zontal axis and the title Net Cash Flow per Year to the vertical axis.
9. The company wants at least an 11% rate of return on this investment. In the
Investment Analysis worksheet, estimate the internal rate of return for the entire
investment from Year 0 through Year 21 by ?rst inserting guesses on the IRR in cells
D5 and D6. Enter the value 1% in cell D5 and the value 10% in cell D6. In cell E5,
calculate the IRR of the investment, using the guess from cell D5. In cell E6, calcu-
late the IRR of the investment using the guess from cell D6. Format the calculated
IRRs to show two decimal places. Are the two IRR values consistent? Based on their
results, can you con?rm that the investment will have a return rate high enough for
the company to proceed?
10. Calculate the net present value of the quarry project for different discount rates.
Enter the values 1% through 20% in 1% increments into the range D9:D28. In the
range E9:E28, calculate the net present value of the investment assuming the dis-
count rates in column D and assuming that the initial investment in the quarry will
occur immediately. For what discount rates is the NPV positive? For what discount
rates is the NPV negative? Based on your calculations, can you determine whether
the investment will be worthwhile if the desired rate of return is 11%? Compare your
answer with your answer in Step 9. What accounts for the apparent discrepancy
between the two answers?
11. Create a chart of the net present values from the range D9:E28 using the Scatter with
Smooth Lines chart type. Place the new chart on a chart sheet named NPV Chart.
Remove the legend from the chart. Add the chart title Net Present Values above the
chart. Add the title Rate of Return to the horizontal axis and the title Net Present
Value to the vertical axis. Recall that the IRR value is the rate of return in which the
net present value is equal to 0. Does your chart explain your results in Step 9?
12. The startup date for the quarry might be delayed because of local community action
regarding environmental concerns about the quarry. Recalculate the net present
values for each of the discount rates in D9:D28 by inserting new net present value
calculations into the range F9:F28, assuming that the initial investment occurs not
immediately but after a year’s delay. Assuming a discount rate of 11%, how much
will the delay cost the company in current dollars?
13. Save and close the workbook. Submit the ?nished workbook to your instructor,
either in printed or electronic form, as requested.
|Due By (Pacific Time)||11/01/2013 01:33 pm|
out of 1971 reviews
out of 766 reviews
out of 1164 reviews
out of 721 reviews
out of 1600 reviews
out of 770 reviews
out of 766 reviews
out of 680 reviews