Quoting and estimating

From 'quotes take a full day' to an hour: automating quotations from your Excel price list

By · Published

Quotes from an Excel price list can be automated by reading the enquiry, matching each requested item to your price file, applying your written pricing rules, and producing a draft quotation for a salesperson to check and send. The routine share of quotes is automated; the judgement stays with the person. Cost is usually £1,500 to £7,500, depending on how tidy the price file and rules are.

At ten to four a builder emails Dave a list for a house extension. It runs to twenty-two lines, typed the way the builder talks: “the usual lintels”, “18mm ply x 30”, “4 bags of the grey”. Dave opens the stock file, an Excel price list he knows better than anyone, and starts at the top. Find the product. Check which price band the customer is on. Dig out the email from last spring where the boss agreed a better rate on timber. Type the line into the quote template. Then the phone rings, and someone is waiting at the trade counter. By half past five he has priced nine lines. The quote will go out tomorrow, behind the four already in the queue.

Dave is made up. The stock file is not.

Key facts

  • A UK Business Forums member described getting quotes “down from what used to take almost a full day to around about an hour now” (UKBF).
  • Quotes are slow for three reasons: looking things up, pricing rules that live in people’s heads, and enquiries that leave out information.
  • Software can draft the routine quotes. The judgement stays with the salesperson, who approves every draft before it goes out.
  • The price file has to be laid out so that software can read it. Tidying it is often the most valuable step on its own.
  • Better Systems Co.’s enquiry-to-quotation work usually costs £1,500 to £7,500, depending on how tidy the price file and the pricing rules are.

Why do quotes take so long?

Because a quote is mostly looking things up, and the things are in different places. For Dave the list price is in the Excel file. The customer’s discount is on another sheet, or in an email from two years ago. Stock and lead times are in Sage or in the warehouse manager’s head. The last quote to the same builder is somewhere in his sent items. One forum member put it plainly: “it takes so long to figure out all the materials needed” (UKBF).

Three problems hide in that afternoon, and each needs a different fix.

  1. Looking things up. Dave finds the product, the price band, the customer’s agreed discount and the carriage charge. It is fetching, and software fetches faster than he can.
  2. Pricing rules in heads. The cut-length surcharge, the minimum order, the rule that one customer gets last year’s price until March. Dave knows them all and nobody has written them down. Software cannot apply a rule that lives only in his memory, and writing them down is work.
  3. Missing information. The enquiry says “200 of the usual brackets” or gives a length without a finish. Dave emails back, waits a day, and the quote goes out two days after the enquiry arrived.

A fourth problem sits underneath. A quote built by hand in Word or Excel is a place for mistakes. As another UKBF member wrote, “every ‘hand’ written entry or unprotected formula is open to human error” (UKBF). A wrong price that goes out on a quote is a wrong price you are held to.

What parts of quoting can be automated, and what cannot?

The routine quotes can be automated. The judgement cannot, and should not be.

Task Can it be automated? Why
Reading the enquiry and listing the items asked for Yes Software can read an emailed, PDF or spreadsheet enquiry and list each line
Matching items to your product codes Mostly Customers have their own names for your products. The system learns “the usual lintels” from past quotes and flags names it does not know
Looking up list price and the customer’s discount Yes, if the price file is tidy One row per product and one column per price band is enough
Applying rules such as minimum order, carriage and cut-length charges Yes, once written down Each rule becomes a plain check the salesperson can read
Deciding to sharpen a price to win a particular job No That call stays with the salesperson
Pricing non-standard or design work No The estimator and their tools stay in charge
Asking the customer for missing information Partly The system drafts a reply listing what is missing. A person sends it
Follow-up reminders on quotes that have gone quiet Yes The system logs the date and raises a reminder

How many of Dave’s quotes are routine? We call that the routine share. It is your number, not a statistic, and you can measure it before spending anything. Take the last three months of quotes and mark each one. Did it need a judgement call, or did Dave look up the price list and apply the standard rules? The second pile is the work automation takes off him.

What does the Excel price list need to look like?

It needs to be a table software can read, which is a stricter test than a table Dave can read. Dave knows the yellow rows are discontinued and that “POA” means ring the supplier. Software does not. In practice the file needs:

  • One row per product, with a code that matches the code in your accounts or stock system.
  • One column per price band or quantity break, with the band rules written in a separate table and not in a column heading.
  • Customer-specific prices on their own sheet: customer account, product code, agreed price, valid from, valid to.
  • No merged cells, no prices in cell comments, no colour coding that carries meaning, no “POA” in a price column.

Most price lists we see fail two or three of these, which is normal. Tidying the file is the first job, and it pays for itself even if you automate nothing else. The sales desk works from one set of prices, and the same file can feed the website and the accounts system. If you also need prices to reach Sage 50 or Sage 200, the Sage 50 systems page sets out the routes.

What are the steps?

  1. Gather a batch of real enquiries and the quotes that answered them. Twenty to fifty pairs from Dave’s sent items is enough to see the patterns and the awkward cases.
  2. Write down the pricing rules. Discounts, minimums, carriage, cut lengths, lead times and the exceptions. Dave holds them in his head, so Dave is the author. We hold the pen.
  3. Tidy the price file into the layout above, or build a small sheet alongside it so the original can keep its layout.
  4. Build the reading and the matching. The system learns each customer’s names for your products from the past quotes.
  5. Test against history. The system drafts quotes for the past enquiries and we compare them line by line with what Dave sent. Each difference is either a rule we missed or a mistake in the original quote. Both are worth finding.
  6. Go live with approval on every quote. Nothing leaves the building unchecked.
  7. Loosen later, with evidence. Once the system has drafted one kind of quote correctly for an agreed period, Dave can approve those in batches.

What does a draft quote with approval look like?

Dave opens a screen with the builder’s email on one side and the draft quote on the other. Each line shows the matched product code, the quantity, the price and where the price came from: list price, quantity break, or the customer’s agreed price with the date it runs out. The system marks every line it was unsure about. That might be an item it could not match, a quantity it could not read, a price older than the limit you agreed, or a margin below the minimum you set. Dave edits what needs editing and approves. The quote goes out in your existing template from his own email address.

If the customer accepts, the approved quote becomes a sales order in your existing system by a route its maker supports, and Dave releases it. If the system cannot read an enquiry at all, it holds it for a person and does not guess, as What happens to an order the software can’t read? explains.

What does a slow quote cost?

It costs Dave’s hours, which you can count today, and lost orders, which you can count only once you start logging.

Start with the hours. Every figure in this paragraph is an assumption, so swap in your own. Say Dave builds 40 quotes a week at 20 minutes each. That is 40 × 20 ÷ 60 × 48 working weeks, or 640 hours a year. At an assumed £20 an hour for what Dave costs to employ, that is £12,800 a year of looking up and typing. The formula is from our post on the cost of manual order entry, and the ROI calculator runs it on your figures. The hours automation hands back are time, not cash. For a sales desk that means more enquiries quoted, and quoted sooner.

The lost orders are harder. We cannot give you a number, and you should be wary of anyone who does without looking at your figures. But the builder who emailed Dave at ten to four probably sent the same list to another merchant. If the merchant down the road quotes within the hour, the builder may have ordered before Dave’s price arrives. These questions measure it.

  • How many enquiries arrived last month, and how many were quoted at all?
  • How many quotes went out more than two working days after the enquiry?
  • Of the quotes you won, how many went out the same day? Of the ones you lost, how many went out late?
  • How many enquiries went unanswered because the one person who can price them was on holiday?

If your sales records or your sent items can answer those, you have a starting point. If they cannot, logging every enquiry with a received date and a quoted date is part of the automation, and three months later you have the figures.

How much does it cost?

The pricing page has the detail. In outline:

Situation Better Systems Co. band Price
You want the routine share measured and the rules written down before deciding Automation audit £500 to £1,250, credited against a build
Tidy price file, enquiries arriving one way, quotes sent as a PDF from a template Quick win £1,500 to £3,000
Customer-specific pricing, rules held in heads, quotes created in Sage or another system Business workflow £3,000 to £7,500
We keep watch, fix it when an enquiry format changes, and add new rules Managed support £150 to £1,500 a month

What drives the cost is how tidy your pricing is. A tidy price file is a quick win. Rules that live in Dave’s head take longer, because writing them down is most of the work.

What does Dave’s afternoon look like afterwards?

Same builder, same list, ten to four. By the time Dave is back from the trade counter, a draft is on his screen. Nineteen of the twenty-two lines are priced, each showing where its price came from. Three are marked. The system does not know which “grey” the builder means, it cannot read one quantity, and the timber rate agreed last spring has run out. Dave rings the builder about the grey, asks the boss about the timber, corrects the lines and approves. The quote goes out before he goes home.

Nineteen out of twenty-two belongs to the story and is not a promise. We measure the real figure on your own quotes before you pay for a build. Whether to sharpen the timber price to win the job is still Dave’s call.

What we would do

We would start with the last three months of enquiries and quotes, tell you in pounds what the looking up costs today, measure the routine share, and write the pricing rules down with your own Dave. If the routine share is small, we would tell you and suggest tidying the price file and logging enquiries instead. If it is large, the enquiry-to-quotation automation service builds the reading, the matching and the approval screen, tests them against your quote history, and goes live with your salespeople approving every draft. The glossary explains any term used here.

Sources

Questions people ask

Our price list is in Excel but customer discounts are agreed verbally. Can that still be automated?

Only once the discounts are written down. The first step of any quotation project is to get the agreed prices and discount rules out of people's heads and into a table that a person can read and a system can apply. That step alone often removes a risk the business did not know it had, because a salesperson leaving no longer takes the pricing with them.

Will customers be able to tell the quote was drafted automatically?

No. The quote goes out in your existing template, from your salesperson's email address, after they have checked it. The only change customers notice is that the quote arrives sooner. Nothing is sent to a customer without a person approving it.

Can the quote become a sales order automatically when the customer accepts?

Yes, with approval. When the customer confirms, the approved quote can be turned into a sales order in your existing system, such as Sage 50, Sage 200 or Xero, by a route the maker of that system supports. The salesperson still releases the order. What disappears is typing the quote in a second time.

What if we quote from drawings or site surveys rather than a product list?

Then the design and take-off work stays with your estimators and their tools. We automate the parts around it: logging the enquiry, chasing missing information, pricing the standard items on the schedule, and following up on quotes that have gone quiet. We will say so if too few of your quotes are routine for it to be worth it.

Related guides

Find out what the typing is costing you

A 20-minute call. You tell us about your Sandra, your Dave or your Phil, and how the job gets done today. We tell you honestly whether automating it would pay, and what it would cost.