could you please show me how these numbers in the second picture were obtained based on the problem provided in the first pic so I could do it in excel
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. Iron Ore Production (1000T) Selling Price (S/T) 0 400 1000 1000 1000 1000 1000 1000 500 0 85.00 87.13 89.30 91.54 93.82 96.17 98.57 101.04 34,000.00 87.125.00 89,303.13 91,535.70 93.824.10 96,169.70 98,573.94 50.519.14 Gross Revenue (S1000) 0 Royalty 12.5% (S1000) 0 Net Revenue (51000) 0 4.250.00 10.890.63 11,162.89 11,441.96 11.728.01 12.021.21 12.321.74 6,314.89 2 9,750.00 76,234,38 78,140.23 80,093.74 82,096.08 84,148,49 86,252.20 44.204.25 Salvage Value (S1000) 24,180.00 22,487.40 20.913.28 19.449.35 18,087.90 16,821.74 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 (S1000) 0 2 4.000.0061,500.00 63.037.50 64,613.44 66,228.77 67.884.49 69.581.61 35.660.57 Reclamation (S1000) 1200 14.734.38 15.102.73 15,480.30 15,867,31 16,263.99 16.670.59 8,543.68 Profit (EBITDA) (S1000) 0.00 5.750.00 Acquisition (S1000) 4,000.00 4.000.00 Development (51000) 15,000.00 21,000.00 Inventory & Parts (51000) 3,000.00 3,000.00 Equipment (S1000) 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,689.06 31,908.22 31.219.72 20,874.37 7.00% 13.04% 18.88% 24.31% 29.37% 34.07% 38.44% 42.50% 46.28% Escalated Dollars (ROR)%= Constant Dollars (ROR)% - 7.00% 13.51% 19.56% 25.19% 30.43% 35.30% 39.83% 14.04% 47.96%
