CPQ vs spreadsheets: when your Excel quoting tool runs out

Martin works at a mid-sized manufacturer. He prices every quote. He is the estimator: the person who works out what a product will cost. He has been there for twenty-eight years, and his spreadsheet has grown with the business. It has a tab for every product line. Formulas point to other tabs. Here and there, a cell holds a fixed number. Only Martin knows where that number comes from. It works. The quotes are right.

Martin retires in two years. And for the first time, the managing director wonders: what happens to that spreadsheet then?

Why Excel works for so long

A spreadsheet is not a stopgap. It is fast, everyone has it, and it can hold anything. An estimator who knows the product can build something in a few weeks that does exactly what is needed. There is no vendor, no licence and no big project.

So it is no surprise that so many factories make their quotes in a spreadsheet. It grows along with the product. And whoever uses it knows exactly where everything is.

But that last point is also the weak spot.

If you came here for a manufacturing quote template

A good quoting spreadsheet for a manufacturer usually keeps four things apart, each on its own tab:

• Options: every choice per product line, with the values you are allowed to pick
• Rules: what cannot go together, and what must come along with what
• Prices: prices per piece, prices per metre and surcharges, each with a date and an owner
• The quote: a sheet that only reads from the other three, with no numbers typed in by hand

Keeping these apart makes the spreadsheet easier to hand over to someone else. It is also exactly the structure a configurator needs later. If your current file mixes all four on one sheet, fix that first, whatever you decide next.

Signs your spreadsheet has reached its limit

The spreadsheet works, until one of these things happens:

• There are several versions. Inside sales uses last year's file. Field sales uses a copy on a laptop, so prices drift apart.
• One person understands it. When Martin is on holiday, quotes wait, or they go out with a price nobody is sure about.
• Quotes get checked again. Nobody fully trusts the result, so someone checks everything by hand.
• New options no longer fit. Every new version of the product means another tab or an even longer formula.
• Others need to configure. Distributors or customers want to put the product together themselves, and you cannot send a spreadsheet outside the company.
• The quote still has to be typed in again. It goes from the spreadsheet into the quote document, and then again into the ERP system, the software where you track orders and production.

One sign is no reason to stop. Three or four are a reason to think it over.

The rules only Martin knows

Martin's spreadsheet holds more than formulas. It also holds rules that exist nowhere else. A rule says what is and is not allowed when you put a product together. For example: above a certain length, always add an extra support. These two options never go together. This customer always gets a different surcharge. Some rules live in a formula, some in a comment, and a few only in Martin's head.

That is the real reason his retirement is a risk. The risk is not that the spreadsheet disappears. It is that nobody will know why it calculates the way it does. A cell with a fixed number turns into a mystery.

Writing that knowledge down is worth it, whatever you choose next. It means someone else can take over the spreadsheet. And it is exactly what a configurator needs to work.

When Excel is enough

Excel is often enough when:

• one or two experienced people make the quotes
• the product is easy to follow, with few options that depend on each other
• you make a limited number of quotes per week
• customers and distributors do not need to configure products themselves
• the knowledge behind the spreadsheet is written down, or known by more than one person

If that sounds familiar, a well-kept spreadsheet with a clear owner is a sensible choice. Spend your time on writing things down, not on a new system. In CPQ Consulting & Selection that is sometimes the outcome too: no new system, just a better pricing rule in what you already have.

The step in between: writing the rules down

Between "everything in Excel" and "a CPQ system" there is a step that almost always pays off. You take the rules out of the spreadsheet and write them down in plain language. Sit down with Martin and go through the file tab by tab:

1. Which options exist? Per product line, with every possible value.
2. Which rules apply? What cannot go together? What is required when you pick something else?
3. How is the price built? Per piece, per metre, as a surcharge or as a percentage.
4. What exceptions are there? And why do they exist?
5. Where do the fixed numbers come from? Every cell with a number and no formula deserves an explanation.

What you end up with is a document of a few pages that explains the spreadsheet. You can use it to train Martin's successor. And if you pick a configurator later, this is the foundation. A broader approach is described in Product data and rules before CPQ.

What you gain when the system does the maths

CPQ stands for Configure, Price, Quote. A CPQ system follows the same rules as the spreadsheet, but in one place and for everyone at once. It also builds the quote for you. There is more background in What is CPQ?.

How much time that saves is hard to put in one number, and the research is old. Haug, Hvam and Mortensen ran a study in 2011 of 14 engineering-oriented companies. In it, the estimated reduction in quote lead time after introducing a configurator averaged 85.5%. Lead time here means how long it takes from request to quote. Keep in mind: that is an estimate, not a measurement, and from a small group. In 2006, Hvam and Christensen described one supplier of cement plants. There, the lead time for budget quotes went from 2 to 5 weeks down to 1 to 3 days. That is one company.

So read those numbers as a direction, not a promise. What you gain in any case is order. You depend less on one person. There is one version of the truth instead of loose copies, and less retyping. Where the time in a quote really goes is covered in Faster quoting in manufacturing.

Martin still has an important part to play. He is the best person to write the rules down and check the new results. The sooner you start, the more time there is.

Wondering whether your spreadsheet will last a few more years, or whether it is time for the next step? In CPQ Consulting & Selection we first look at how you quote today. Only then do we talk about systems. Get in touch and tell us how your Martin works.