Excel estimating: where it works, where it strains

Excel or estimating software: what is the difference?

One cost engineer and a spreadsheet they have built up themselves over the years: a tab per customer, columns for material, hours and rate, one cell for the mark-up. That is how estimating runs in plenty of machine shops, and it runs well for as long as that one cost engineer is there.

This piece is written by Subduxion, the company behind Blake. The table sets a spreadsheet beside Blake, so it also carries a column for the work Excel remains the better tool for. The rows are six questions that come back on every estimate.

Which formula gives a cost per part in Excel?

A cost per part adds up five items: material, finishing, setup, programming and machining. Setup and programming happen once per batch, so you divide them by the quantity. Cycle time counts for every part. A full estimate also adds tooling and a risk allowance.

With named cells the formula reads: =Material+Finishing+(Setup+Programming)/60*Rate/Quantity+CycleTime/60*Rate. Times are in minutes, the rate per hour. Building up that rate is covered in how to calculate a machine hour rate.

The result is a cost price, not a selling price. Margin goes on top as a separate decision, per customer or per order. Keep the two apart in the cost build-up, or nobody can later tell what making the part cost and what the price agreement was.

How do two cost engineers end up with two prices?

Each cost engineer keeps a private copy. One prices setup time per setup, the other as a fixed block per order. One folds finishing and its mark-up into the material column, the other keeps it as a line of its own. Both sheets are right for the person who built them, and the same drawing comes out at two prices.

The sheet shows the outcome of those choices, not the choices themselves. Why a cycle time is set where it is, or why the material carries a mark-up, stays in the author's head. In our view that is the heart of it, well beyond Excel: CAD, CAM and ERP can share data without the reason behind an experienced manufacturing engineer's choice of route being written down anywhere.

How reliable is a large spreadsheet?

Research on spreadsheet errors keeps arriving at the same place. A single cell is seldom wrong, but across a large spreadsheet the odds are high that some final figure is. Such mistakes are hard to track down, and whoever built the sheet tends to trust it more than it deserves (Panko, 2016).

By 2008, the only approach with evidence behind it was going through the sheet cell by cell (Panko, 2008). For estimating, that means a price can be defended when someone else can check each estimate line on its own. That only works with the line inputs beside it: the time, rate and quantity it calculates with.

In a guide for audit committees in UK government, the National Audit Office (NAO) sets out what any model should document at a minimum, a simple spreadsheet included: a log of its versions, a record of its data and assumptions, a guide for the people who use it and a plan for succession. A model nobody else has reviewed is more likely to contain errors (NAO, 2023).

What happens on a new drawing revision?

A customer sends revision C of a drawing with one tolerance tightened. In a spreadsheet you copy the sheet, edit the milling line and save it under a new name. From then on, the only record of which quote belonged to which revision is a file name. After that, the same change still has to travel by hand through everything built on that sheet: the quote, the material order, the routing. Each step is a chance for one of them to be missed.

A quote refers to the exact drawing revision it is based on. That needs an estimate tied to one revision, and a revised quote saved as its own quote version, with the estimate of that moment behind it. A copied sheet manages that for as long as everyone follows the same naming, and no longer.

What did this repeat part cost last time?

A repeat order arrives after eighteen months. The old sheet is somewhere in a folder, with the material price and hourly rate of the day. Whoever finds it still has to guess what has changed since: the material, the route, the rates or only the quantity.

The ERP does not solve that. It keeps what was invoiced, not why the price stood where it did: which route, which cycle time, which mark-up. Copy the old price and you quote last time's material price and hourly rate again, and every increase since then comes off the margin. Raise it on instinct and you cannot tell the customer why.

What helps is a repeat-part comparison: the new lines beside the last priced ones, so you can read line by line what moved before you quote again. In Excel you build that with a second tab and discipline. It works until the old file can no longer be found.

How does a new colleague take over a spreadsheet?

An estimating sheet is software one person wrote, without a manual. A new colleague spends weeks beside the author before the hidden columns and fixed mark-ups make sense. When the author leaves, the method leaves too.

What hands over well is an estimate in which every line shows its inputs and every correction its reason. The correction history then becomes the induction file: who changed what, when and why. A new colleague reads the reasoning back instead of guessing at it. How to capture that know-how is covered in keeping a cost engineer's know-how.

When is Excel enough?

Excel is enough when one person does the estimating, the work varies little and the method is settled. A shop with one regular customer, a handful of repeat parts and the same finishing every time gains little beyond a well-kept sheet.

Alongside estimating software, Excel stays useful: for a first exploration, for a pivot table across a year of orders and for further work on an export of the quote. The questions to ask before you switch are in what to check in estimating software.