Mining financial model XLS: Excel layout and checks

A mining financial model XLS workbook should connect feed tons, saleable output, realized price and costs to a cash balance that reconciles. Build the physical production schedule first, then apply the right unit cost to each stage. The layout below is for a US mining finance team preparing an operating plan, with formulas you can enter in your own workbook.

Give each worksheet one job

Use a common calendar and scenario name across the workbook. A monthly layout helps expose cash shortages; the annual example below keeps the calculations easy to inspect. Keep historical actuals separate from assumptions and record who supplied each planning input.

Keep a short-ton label beside every physical quantity in this example. A metal operation may require grade, recovery and payable-metal schedules instead. Do not reuse this aggregate-style saleable-yield calculation as a substitute for those schedules. The broader mining financial model guide covers the operating structure; this page concentrates on worksheet cells and checks.

For outside context, USGS Mineral Commodity Summaries provides commodity statistics, including production, reserves and resources. Use those publications to investigate whether an assumption needs review. Your site's contracts and operating records still supply this workbook's inputs; a national statistic is not a realized selling price for one mine.

Enter this operating layout

Consider a fictional US industrial-minerals operation planning one year. All feed processed in this example becomes either saleable product or rejected material, and all saleable product ships in the same year. There is no change in finished-product inventory.

Put row labels in column A and values or formulas in column B. Enter percentages as percentages, quantities as numbers and money as USD. The table shows full-dollar results without rounding intermediate calculations.

The important distinction is the cost driver. Extraction and processing apply to feed, including material that will not become saleable product. Shipping applies to the product shipped. Multiplying every cost by saleable tons would understate the cost of low-yield material.

Microsoft documents that SUM adds all numbers in a range of cells. The total-cost formula names only cost rows because a continuous range would also include unit-cost inputs. Covirage is not affiliated with Microsoft.

Add the cash bridge

Operating surplus is not cash available at the bank. Add these rows below the operating schedule. The working capital increase represents additional cash tied up in operating balances; it is entered as a positive use of cash. Cash taxes are a supplied planning amount, not a rate applied to this simplified surplus.

This example has no debt interest, borrowing, principal payments or distributions. Add separate rows for them when they exist. Keep new equipment purchases separate from operating costs, and do not subtract depreciation again in this cash bridge.

The project finance workbook layout is the nearby guide when debt payments and coverage need their own schedules. A cash-positive mining year can still have a funding gap within the year, so phase the same rows by payment date before deciding that financing is unnecessary.

Change yield without changing the other assumptions

Copy the assumptions into a second scenario and reduce saleable yield to 75%. Keep feed, prices, unit costs, fixed spending, capital spending, working capital and cash taxes unchanged. These are deliberate scenario assumptions, rather than predictions about how taxes or working capital must behave.

Lower yield removes $150,000 of revenue and saves $10,000 of shipping costs. Operating surplus and closing cash therefore fall by $140,000. The result isolates yield because nothing else changes. For another production business with a different seasonal pattern, see the agriculture workbook layout.

Build checks before reviewing the result

The production check should reconcile opening saleable inventory plus production minus shipments to closing inventory. In the example, production equals shipments, so the inventory movement is zero. Separately reconcile feed to saleable output plus rejected material; use measured rejection records when checking actual results.

Tie revenue to the sales register, costs to the selected ledger accounts and capital spending to the equipment schedule. A zero difference proves agreement between schedules, not that the assumptions are reasonable. Keep the assumption owner and supporting document visible for that review. FP&A analysis starts with those reconciled operating and financial files.

Where it goes wrong

• Unit mismatches. Short tons, metric tons and contained-metal units require explicit conversions before multiplying quantities by prices.
• Production treated as sales. Inventory changes break the assumption that every ton produced generates current-period revenue.
• Costs attached to the wrong stage. Feed-based processing costs continue even when saleable yield falls.
• Missing future obligations. Include the approved timing of reclamation, closure and other cash obligations in the cash schedule rather than assuming today's operating surplus covers them.
• Annual totals hiding payment dates. A positive closing balance can conceal an earlier shortage; review monthly receipts and disbursements.

Check your mining workbook in Covirage

Bring your scenario workbook and the production, sales and cost exports behind it. Covirage maps the columns, and its tools compute and check the supplied figures; the external AI model explains the differences with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.

See Covirage for finance teams for the supporting finance workflow.