Project #15728 - excel project

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.

Subject Mathematics
Due By (Pacific Time) 11/01/2013 01:33 pm
Report DMCA
TutorRating
pallavi

Chat Now!

out of 1971 reviews
More..
amosmm

Chat Now!

out of 766 reviews
More..
PhyzKyd

Chat Now!

out of 1164 reviews
More..
rajdeep77

Chat Now!

out of 721 reviews
More..
sctys

Chat Now!

out of 1600 reviews
More..
sharadgreen

Chat Now!

out of 770 reviews
More..
topnotcher

Chat Now!

out of 766 reviews
More..
XXXIAO

Chat Now!

out of 680 reviews
More..
All Rights Reserved. Copyright by AceMyHW.com - Copyright Policy