could please write the eqautions that were used to get each number in the solution provided
You are on the honor system. You may use notes, but all work is expected to be your own. Use the posted MS Excel spreadsheet to work the following problem. Type your name in the box above the spreadsheet. Express tons and dollars in terms of 1000 T and $1000, respectively. Use appropriate formatting. Return the completed spreadsheet to me via Canvas by 12:00 pm Wednesday, April 1. Problem Evaluate the economic potential of an iron-ore mine for a mineral-rights acquisition cost of $4.0 million dollars. Mine development will be contracted with costs estimated to be $15 million at time zero, with an additional $6 million charged in year 1 for preproduction stripping. Mining equipment costs will total $26 million and will also be charged at time zero. $3.0 million will be charged at time zero for spare parts and inventory. Iron-ore production is expected to start in year 1, with the mining of 400,000 tons of iron ore. Annual iron-ore production in years 2 through 7 is expected to be 1,000,000 tons. The production in year 8 is estimated to be 500,000 tons, and the reserve will be depleted in year 8. The selling price is $85.00 per ton for year 1 and is expected to escalate by 2.5% over the life of the mine. Royalties are set at 12.5% of gross revenues. Operating costs, which include state and local severance and property taxes, are estimated to be $60.00 per ton of iron ore in year 1, escalating 2.5% per year over the life of the mine. Reclamation costs will be incurred at year 8 and are expected to be $12.0 million in today's dollars, with an annual escalation rate of 3.0% over the next 8 yrs. The salvage value of the equipment will be based on a depreciation rate of 7.0% annually over the 8 years. The inflation rate for the next 8 years is estimated at 3.5% annually. (a) Calculate the ROR, with the before-tax cash flow expressed in escalated dollars. (b) Calculate the ROR, with the before-tax cash flow expressed in constant dollars. NAME: 0 1 2 5 6 7 Year Iron-Ore Production (1000 T) Selling Price (S/T) Gross Revenue ($1000) Royalty @ 12.5% (51000) Net Revenue (S1000) Salvage Value (S1000) Operating Costs (S/T) Operating Costs (S1000) Reclamation (S1000) Profit (EBITDA) ($1000) Acquisition (S1000) Development ($1000) Inventory & Parts (S1000) Equipment ($1000) Escalated Dollars: BTCF Constant Dollars: BTCF Escalated Dollars: ROR (%) - Constant Dollars: ROR (%) = Name S Rama Krishnan Year Iron Ore Production (1000T) Selling Price (S/T) 400 40 $500 1000 1000 1000 1000 $7.133369154 1000 1000 500 500 Price 0 85.00 96.17 98.57 10104 34,000.00 87.125.00 89,303.13 1.535.70 3.824.10 96,169.70 98.573.94 50,319.14 Revue (51000) Royalty 12.5% (S1000) Net Revenue (51000) 0 4,250.00 10,890.63 11,162.89 11,441.96 1.728.01 12,02121 12.321.74 6.314.89 29,750.00 76.234.38 78.140.23 80,093.74 $2.096.08 84.148.49 86.252.20 44.204.25 Salvage Value (51000) 24,180,00 22,487.40 20.913.28 19.449.35 18,087.90 16,82174 15,644 22 14.549.13 13.530.69 Operating Costs (S/T) O 24,000,000,00 61.500.000.00 63.037.500.00 64.613.437.50 66.228.773.44 67.884.492.7769.581.605.09 35.660.572,61 Operating Costs (51000) 0 24,000.00 61,500.00 63.037.50 64.613.44 66,228.77 67884.49 69.58161 35.660.57 Reclamation (51000) 1200 14.734.38 15.102.73 15.480.30 15.867.31 16.263.99 16,670.59 8.543.68 Profit (EBITDA) (51000) 0.00 5.750.00 Acquisition (51000) 4,000.00 4,000.00 Development (51000) 15,000.00 21,000,00 Inventory & Parts (51000) 3.000,00 3.000.00 Equipment (51000) 26,000.00 26,000.00 0.00 2.159.09 34.399.99 33.342.76 32.393.31 31.544.94 30,791.43 30.127.03 20.143.76 Escalated Dollars: BTCF Constant Dollars BTCF 0.00 2,237.40 35,647.66 34.552.09 33.568.20 32.889.06 31.90822 31,219.72 20,874.37 7.00% 13.64% 18.85% 42.50% Escalated Dollars (ROR) % - Constant Dollars (ROR) - 7.00% 13.51% 19.56% 25.19% 30.43% 35.30% 47.0 You are on the honor system. You may use notes, but all work is expected to be your own. Use the posted MS Excel spreadsheet to work the following problem. Type your name in the box above the spreadsheet. Express tons and dollars in terms of 1000 T and $1000, respectively. Use appropriate formatting. Return the completed spreadsheet to me via Canvas by 12:00 pm Wednesday, April 1. Problem Evaluate the economic potential of an iron-ore mine for a mineral-rights acquisition cost of $4.0 million dollars. Mine development will be contracted with costs estimated to be $15 million at time zero, with an additional $6 million charged in year 1 for preproduction stripping. Mining equipment costs will total $26 million and will also be charged at time zero. $3.0 million will be charged at time zero for spare parts and inventory. Iron-ore production is expected to start in year 1, with the mining of 400,000 tons of iron ore. Annual iron-ore production in years 2 through 7 is expected to be 1,000,000 tons. The production in year 8 is estimated to be 500,000 tons, and the reserve will be depleted in year 8. The selling price is $85.00 per ton for year 1 and is expected to escalate by 2.5% over the life of the mine. Royalties are set at 12.5% of gross revenues. Operating costs, which include state and local severance and property taxes, are estimated to be $60.00 per ton of iron ore in year 1, escalating 2.5% per year over the life of the mine. Reclamation costs will be incurred at year 8 and are expected to be $12.0 million in today's dollars, with an annual escalation rate of 3.0% over the next 8 yrs. The salvage value of the equipment will be based on a depreciation rate of 7.0% annually over the 8 years. The inflation rate for the next 8 years is estimated at 3.5% annually. (a) Calculate the ROR, with the before-tax cash flow expressed in escalated dollars. (b) Calculate the ROR, with the before-tax cash flow expressed in constant dollars. NAME: 0 1 2 5 6 7 Year Iron-Ore Production (1000 T) Selling Price (S/T) Gross Revenue ($1000) Royalty @ 12.5% (51000) Net Revenue (S1000) Salvage Value (S1000) Operating Costs (S/T) Operating Costs (S1000) Reclamation (S1000) Profit (EBITDA) ($1000) Acquisition (S1000) Development ($1000) Inventory & Parts (S1000) Equipment ($1000) Escalated Dollars: BTCF Constant Dollars: BTCF Escalated Dollars: ROR (%) - Constant Dollars: ROR (%) = Name S Rama Krishnan Year Iron Ore Production (1000T) Selling Price (S/T) 400 40 $500 1000 1000 1000 1000 $7.133369154 1000 1000 500 500 Price 0 85.00 96.17 98.57 10104 34,000.00 87.125.00 89,303.13 1.535.70 3.824.10 96,169.70 98.573.94 50,319.14 Revue (51000) Royalty 12.5% (S1000) Net Revenue (51000) 0 4,250.00 10,890.63 11,162.89 11,441.96 1.728.01 12,02121 12.321.74 6.314.89 29,750.00 76.234.38 78.140.23 80,093.74 $2.096.08 84.148.49 86.252.20 44.204.25 Salvage Value (51000) 24,180,00 22,487.40 20.913.28 19.449.35 18,087.90 16,82174 15,644 22 14.549.13 13.530.69 Operating Costs (S/T) O 24,000,000,00 61.500.000.00 63.037.500.00 64.613.437.50 66.228.773.44 67.884.492.7769.581.605.09 35.660.572,61 Operating Costs (51000) 0 24,000.00 61,500.00 63.037.50 64.613.44 66,228.77 67884.49 69.58161 35.660.57 Reclamation (51000) 1200 14.734.38 15.102.73 15.480.30 15.867.31 16.263.99 16,670.59 8.543.68 Profit (EBITDA) (51000) 0.00 5.750.00 Acquisition (51000) 4,000.00 4,000.00 Development (51000) 15,000.00 21,000,00 Inventory & Parts (51000) 3.000,00 3.000.00 Equipment (51000) 26,000.00 26,000.00 0.00 2.159.09 34.399.99 33.342.76 32.393.31 31.544.94 30,791.43 30.127.03 20.143.76 Escalated Dollars: BTCF Constant Dollars BTCF 0.00 2,237.40 35,647.66 34.552.09 33.568.20 32.889.06 31.90822 31,219.72 20,874.37 7.00% 13.64% 18.85% 42.50% Escalated Dollars (ROR) % - Constant Dollars (ROR) - 7.00% 13.51% 19.56% 25.19% 30.43% 35.30% 47.0
