Material unit cost: 6 steps for a weighted average

Why one unit cost is hard to set

To average a material's unit cost for a bid, divide total purchase cost by total quantity bought. The takeoff calls for a certain quantity, and the estimate multiplies that quantity by a single unit cost. Your purchase records rarely hold a single price, though. The first delivery arrived at one price, a rush order came in higher, and a bulk buy came in lower. Each purchase is a true cost you paid, but none of them alone is necessarily the cost you should bid.

The tempting shortcut is the simple average: add up the unit prices and divide by the number of purchases. That treats a small delivery the same as a large one, which distorts the result. A weighted average fixes this by letting each price count in proportion to how much you bought at that price. This post walks through that method, shows how Microsoft Business Central applies the same logic, and gives you a practical sequence to follow before you put the number into a bid.

The two values you need

Every purchase gives you two values: how many units arrived, and what each unit cost. Multiply them and you have the dollars that purchase cost you. Add up the dollars across all the purchases you are including, then divide by the total units. That gives you one unit cost:

Unit cost = (total dollars spent on the material) ÷ (total units bought)

A price with no quantity tells you nothing about how much weight it deserves. A quantity with no price tells you nothing about cost. Keep both on the same line of your record for every purchase, so you never have to guess which price went with which amount.

How Business Central does it

Microsoft's Business Central documentation describes how an inventory system calculates average cost over time. Its design notes on average cost tie each purchase to a period. One line reads: "The average cost of entry number 3 is calculated in the average cost period for January." Another reads: "The average cost for entries 4 and 6 is calculated in the average cost period for February." The average is worked out for each period, so the purchases of one period are grouped together before the figure is computed.

The February example on that page shows the arithmetic. In its words, "To get the average cost for February, Business Central adds the average cost of the item received in inventory (100.00) to the average cost at the beginning of the period (30.00)." The page then says, "The sum (130.00) is then divided by the total quantity in inventory (2)." Its result: "This calculation gives the resulting average cost of the item in the February period (65.00)."

Read those lines for the method, not the specific figures. The February example adds the two average costs, then divides their sum by the total quantity in inventory. Your own spreadsheet will have its own numbers, but the shape of the calculation is the same. The final step matters most for you: the page also notes that the average cost is assigned to the inventory decreases in the period, which in that example are entries 4 and 6. In other words, the average is the price charged out when material leaves stock.

That is the cost an estimator should usually be borrowing. The average is the price the material is carrying in your records. It is not the price of whichever delivery happened to arrive last, and it is not the highest quote anyone sent you last spring.

A step-by-step method for a bid

Work through these steps in order every time you need a unit cost for a material. Doing it the same way each time makes the result easier to check and easier to defend if a client asks where the number came from.

Pick the material and the unit. Confirm that every purchase is measured in the same unit. Feet and pieces, or pounds and hundredweight, do not average together. Convert first, then calculate.
List every purchase in your window. For each one, write down the date, the quantity, and the unit price. Include entries that came from credits or returns, handled as described below.
Multiply each line. For every purchase, multiply quantity by unit price, and keep the result to full precision.
Add the dollars and the quantities separately. Sum the dollar amounts from step 3. Sum the quantities from step 2.
Divide. Divide the dollar total by the quantity total. Round only at the end, to the precision your estimate uses.
Compare the result. Set the weighted average beside the most recent purchase price and beside the simple average. A large gap usually means one purchase carries a lot of weight.

The comparison in step 6 is a sanity check, not a rule.

Choosing the purchase window

The average depends on which purchases you include. The Business Central example works in periods: the average for January is calculated separately from the average for February. You do not have to use calendar months, but you do have to choose a window on purpose and write it down.

A window that is too long can hide a recent price change. Suppose the supplier raised prices partway through a long period. A single average across the whole period blends old and new prices, and it will understate what the next load costs. A window that is too short has the opposite problem: it may rest on one delivery, and one delivery is a thin basis for a bid.

A workable starting point is to take every purchase since the last known price change. If the supplier has changed prices more than once, start from the most recent change. If there have been no changes, a window of recent purchases that covers a normal buying cycle is a reasonable choice. Whatever you pick, write it on the estimate so the next person can see it.

Freight, taxes, and other landed costs

The unit price on a supplier invoice is often not the full cost of getting material to the job. Freight, handling, and taxes may appear as separate charges. Before you average, decide whether your number will use the invoice price only or include freight and handling. Then apply that same rule to every purchase.

Mixing the two is a common source of error. If some purchases carry freight and others do not, the average will tilt toward whichever purchases happen to include it. The fix is simple. If freight is billed for each delivery, spread it across the units in that delivery, add the allocated amount to each unit price, and then run the weighted average. Every purchase is then measured on the same basis.

Keep a note of what you decided. A line in the estimate such as "invoice price plus allocated freight, purchases from the last price change onward" tells a reviewer exactly how the number was built.

Returns, credits, and partial shipments

A return or a credit reduces both the quantity and the dollars. Enter it as a negative purchase, with a negative quantity and a negative amount, rather than as a note beside the average. Then the arithmetic in steps 3 and 4 handles it without special cases. If you simply subtract a return from a total, you may end up with the right dollars and the wrong quantity, and the unit cost will be off.

A partial shipment is just a smaller delivery. Keep it. It still has a price, and it still belongs in the window. Small deliveries carry little weight, which is exactly the behavior you want. Dropping a small delivery because it looks unimportant changes the average in a way that is hard to explain later.

Keep the average separate from the bid price

The weighted average describes what you have paid. The bid describes what you expect to pay while you do the work. Those are different questions, and they can have different answers. If prices are moving up, the historical average will lag behind the market. If a supplier has quoted a price for a future delivery, that quote is usually a better guide to the bid than a backward-looking average.

Use the average as the base. Then decide whether to adjust it, and record the reason for any adjustment. An estimator who writes "average plus a known increase from the supplier's new quote" can defend the number. An estimator who quietly types in a different figure cannot.

The same logic applies when you bid in a busy season. Many projects are priced at once, and prices can change quickly while you wait. Before you lock the number, check whether the supplier's current quote still matches your records. If it does not, the difference is information you should use, not noise to average away.

How the average changes as purchases arrive

The average is not fixed. Each new purchase changes it, and the change depends on how much that purchase weighs. A large new delivery at a different price will move the average more than a small one. That is the reason to recalculate from the full window, rather than nudging the old number by a guess.

The Business Central example also shows that averages can be carried from one period into the next. Its February calculation starts from an average at the beginning of the period, combined with what was received during the period. The lesson for your own work is that a carried-forward average must still be combined with quantities. You cannot simply average two averages together and expect the result to match the true weighted figure. If you roll an average forward from one window to the next, keep the quantity on hand alongside the unit cost, so the next calculation has both values.

Common mistakes to avoid

Most errors in this kind of calculation are small and avoidable. These are the ones that come up most often:

Using a simple average. It is quick, and it ignores quantity. Use the weighted version whenever purchase sizes differ.
Averaging averages without quantities. Two monthly averages cannot be combined by averaging them unless you weight each one by the quantity behind it.
Mixing units. A foot price averaged with a piece price produces a number that means nothing. Convert before you start.
Mixing cost bases. Some purchases with freight and some without will skew the result. Pick one basis and apply it everywhere.
Forgetting returns. A credit recorded only in the dollars, not the quantity, distorts the unit cost.
Rounding too early. Round at the final step. Rounding each purchase first can add up to a visible difference across a large quantity.
Leaving the window undocumented. If nobody can tell which purchases were included, nobody can check the number.
Forgetting to update. A new purchase that arrives after you built the estimate changes the average. If the bid is still open, rerun the calculation before you submit.

A short pre-bid checklist

Before you put a material unit cost into a bid, confirm each of these:

All purchases are in the same unit.
The purchase window is chosen and written down.
Returns and credits are entered as negative purchases.
Freight and taxes are treated the same way on every purchase.
The weighted average has been compared with the latest price and the simple average.
Any adjustment away from the average has a recorded reason.
The calculation used the latest purchases, not a version from before the most recent delivery.

Putting it together

The method is simple, and that is why it is worth doing carefully. Each step is easy to check, so a reviewer can follow the number from the purchases to the bid line. That traceability is often worth more than a few cents of precision.