Excel 2016 Module 9 case Problem 4

Granite.xlsx

i need help with this case problem. please help thank you.

case problem 3

case problem 3

swip side to side to see the whole picture. or hold down the picture to opent he picture.

Case Problem 3 Data File needed for this Case Problem: Granite.xlsx Hawthorne Granite Anita Garcia is a project analyst at Hawthorne Granite, a mining company in central Michigan. The company is considering investing in a granite quarry near the town of Mount Pleasant, Michigan. Anita wants you to examine the current value of the project assuming that the quarry will have an initial startup cost of $7.7 million with growing revenue through its first 10 years of existence and declining revenue for the next 15 years as the granite deposits become more difficult and less economical to extract. The company will also have to clean up the site after 25 years of operation to restore it to its original condition. The current cleanup cost is $8.2 million, but Anita wants the Year 25 value, assuming a 3.2 percent annual inflation rate. You will use the financial data supplied by Anita to estimate the profitability of the project. Complete the following: 1. Open the Granite workbook located in the Excel9 > Case3 folder included with your Data Files, and then save the workbook as Granite Quarry in the location specified by your instructor. 2. In the Documentation worksheet, enter your name and the date. 3. In the Project Analysis worksheet, enter the following initial assumptions for the project: In cell B5, enter $7.70 as the startup costs for the project (in millions). In cell B6, enter 31.0% as the operational costs percentage. In cell B7, enter $8.20 as the cleanup cost in current dollars (in millions). In cell B8, enter 25 as the years of operation. In cell B9, enter 3.2% as the annual inflation rate. Explore 4. In cell B12, use the FV function to calculate the final cleanup cost in 25 years, using the inflation rate in cell B9, the number of years in cell B8, a payment value of 0, and the present value of the cleanup cost in cell B7. Change the sign of the result so it appears as a positive value 5. In cell G6, enter the startup cost of the mine using the value in cell B5. 6. Enter the following projected annual income values that the quarry will generate: a. In cell E7, enter $0.50 million as the projected earnings for Year 1. b. In cell E16, enter $12 million as the projected earnings for Year 10. c. In cell E26, enter $2 million as the projected earnings for Year 20. d. In cell E31, enter $1 million as the projected earnings for Year 25. 7. Do the following to fill in the missing income values: a. Interpolate the rising income values between E7 and E16 assuming a growth trend. b. Interpolate the declining income values between E16 and E26 assuming a growth trend. c. Interpolate the declining income values between E26 and E31 assuming a linear trend. 8. In the range F7:F31, calculate the annual cost of goods by multiplying the income value for each year by the operational cost percentage in cell B6. PODO 2. In the Documentation worksheet, enter your name and the date. 3. In the Project Analysis worksheet, enter the following initial assumptions for the project: In cell B5, enter $7.70 as the startup costs for the project in millions). In cell B6, enter 31.0% as the operational costs percentage. In cell B7, enter $8.20 as the cleanup cost in current dollars (in millions). In cell B8, enter 25 as the years of operation. In cell B9, enter 3.2% as the annual inflation rate. Explore 4. In cell B12, use the FV function to calculate the final cleanup cost in 25 years, using the inflation rate in cell B9, the number of years in cell B8, a payment value of 0, and the present value of the cleanup cost in cell B7. Change the sign of the result so it appears as a positive value. 5. In cell G6, enter the startup cost of the mine using the value in cell B5 6. Enter the following projected annual income values that the quarry will generate: a. In cell E7, enter $0.50 million as the projected earnings for Year 1. b. In cell E16, enter $12 million as the projected earnings for Year 10. c. In cell E26, enter $2 million as the projected earnings for Year 20. d. In cell E31, enter $1 million as the projected earnings for Year 25. 7. Do the following to fill in the missing income values: a. Interpolate the rising income values between E7 and E16 assuming a growth trend. b. Interpolate the declining income values between E16 and E26 assuming a growth trend. c. Interpolate the declining income values between E26 and E31 assuming a linear trend. 8. In the range F7:F31, calculate the annual cost of goods by multiplying the income value for each year by the operational cost percentage in cell B6. 9. Anita estimates the quarry will have $1.2 million in fixed costs in Year 1. Enter this value in cell G7. 10. Anita projects that fixed costs will grow at a rate of 4 percent per year. Extrapolate the Year 1 fixed cost value through Year 20 in the range G8:G26. 11. From Year 21 to Year 25, Anita projects that fixed costs will decline by 10 percent per year (so that each year's fixed cost is 90 percent of the previous year). Extrapolate the Year 21 fixed-cost values through Year 25 in the range G27:G31. 12. In cell G32, enter the cleanup cost using the value you calculated in cell B12. 13. In the range H6:H32, calculate the quarry's gross profit by subtracting the sum of the annual cost of goods and fixed costs from the quarry's annual income. 14. In the range 16:132, calculate the cumulative gross profit for each year by adding the sum of the previous year's gross profit values. Case Problem 3 Data File needed for this Case Problem: Granite.xlsx Hawthorne Granite Anita Garcia is a project analyst at Hawthorne Granite, a mining company in central Michigan. The company is considering investing in a granite quarry near the town of Mount Pleasant, Michigan. Anita wants you to examine the current value of the project assuming that the quarry will have an initial startup cost of $7.7 million with growing revenue through its first 10 years of existence and declining revenue for the next 15 years as the granite deposits become more difficult and less economical to extract. The company will also have to clean up the site after 25 years of operation to restore it to its original condition. The current cleanup cost is $8.2 million, but Anita wants the Year 25 value, assuming a 3.2 percent annual inflation rate. You will use the financial data supplied by Anita to estimate the profitability of the project. Complete the following: 1. Open the Granite workbook located in the Excel9 > Case3 folder included with your Data Files, and then save the workbook as Granite Quarry in the location specified by your instructor. 2. In the Documentation worksheet, enter your name and the date. 3. In the Project Analysis worksheet, enter the following initial assumptions for the project: In cell B5, enter $7.70 as the startup costs for the project (in millions). In cell B6, enter 31.0% as the operational costs percentage. In cell B7, enter $8.20 as the cleanup cost in current dollars (in millions). In cell B8, enter 25 as the years of operation. In cell B9, enter 3.2% as the annual inflation rate. Explore 4. In cell B12, use the FV function to calculate the final cleanup cost in 25 years, using the inflation rate in cell B9, the number of years in cell B8, a payment value of 0, and the present value of the cleanup cost in cell B7. Change the sign of the result so it appears as a positive value 5. In cell G6, enter the startup cost of the mine using the value in cell B5. 6. Enter the following projected annual income values that the quarry will generate: a. In cell E7, enter $0.50 million as the projected earnings for Year 1. b. In cell E16, enter $12 million as the projected earnings for Year 10. c. In cell E26, enter $2 million as the projected earnings for Year 20. d. In cell E31, enter $1 million as the projected earnings for Year 25. 7. Do the following to fill in the missing income values: a. Interpolate the rising income values between E7 and E16 assuming a growth trend. b. Interpolate the declining income values between E16 and E26 assuming a growth trend. c. Interpolate the declining income values between E26 and E31 assuming a linear trend. 8. In the range F7:F31, calculate the annual cost of goods by multiplying the income value for each year by the operational cost percentage in cell B6. PODO 2. In the Documentation worksheet, enter your name and the date. 3. In the Project Analysis worksheet, enter the following initial assumptions for the project: In cell B5, enter $7.70 as the startup costs for the project in millions). In cell B6, enter 31.0% as the operational costs percentage. In cell B7, enter $8.20 as the cleanup cost in current dollars (in millions). In cell B8, enter 25 as the years of operation. In cell B9, enter 3.2% as the annual inflation rate. Explore 4. In cell B12, use the FV function to calculate the final cleanup cost in 25 years, using the inflation rate in cell B9, the number of years in cell B8, a payment value of 0, and the present value of the cleanup cost in cell B7. Change the sign of the result so it appears as a positive value. 5. In cell G6, enter the startup cost of the mine using the value in cell B5 6. Enter the following projected annual income values that the quarry will generate: a. In cell E7, enter $0.50 million as the projected earnings for Year 1. b. In cell E16, enter $12 million as the projected earnings for Year 10. c. In cell E26, enter $2 million as the projected earnings for Year 20. d. In cell E31, enter $1 million as the projected earnings for Year 25. 7. Do the following to fill in the missing income values: a. Interpolate the rising income values between E7 and E16 assuming a growth trend. b. Interpolate the declining income values between E16 and E26 assuming a growth trend. c. Interpolate the declining income values between E26 and E31 assuming a linear trend. 8. In the range F7:F31, calculate the annual cost of goods by multiplying the income value for each year by the operational cost percentage in cell B6. 9. Anita estimates the quarry will have $1.2 million in fixed costs in Year 1. Enter this value in cell G7. 10. Anita projects that fixed costs will grow at a rate of 4 percent per year. Extrapolate the Year 1 fixed cost value through Year 20 in the range G8:G26. 11. From Year 21 to Year 25, Anita projects that fixed costs will decline by 10 percent per year (so that each year's fixed cost is 90 percent of the previous year). Extrapolate the Year 21 fixed-cost values through Year 25 in the range G27:G31. 12. In cell G32, enter the cleanup cost using the value you calculated in cell B12. 13. In the range H6:H32, calculate the quarry's gross profit by subtracting the sum of the annual cost of goods and fixed costs from the quarry's annual income. 14. In the range 16:132, calculate the cumulative gross profit for each year by adding the sum of the previous year's gross profit values.