Turning a Supplier Price List Into a Quoting System

A commercial furniture dealer came to us wanting something reasonable. Their sales staff should be able to click the items a customer wants and have an estimate or invoice come out the other end.

They already had the prices. Their distributor supplied a full price list, and the business had been quoting from it for years.

So the job looked like building a form on top of a price list that already existed.

It wasn’t. The price list had to be rebuilt first, and that turned out to be most of the work.

A supplier price list is written for people, not for formulas

Open a distributor price list and it makes immediate sense to a human. A product has a name, a description, sizes, a set of finishes, maybe a warranty that differs by range. A salesperson reads across the page, finds what they need, and writes it down.

Now ask a spreadsheet to do the same thing.

It cannot. Not because the data is missing, but because of where the data sits. One product occupies a block of the page rather than a row. The finishes are listed alongside the description. Sizes appear as a note. The warranty applies to a family of products rather than to the thing being sold.

For a person, that is efficient. For a lookup, there is nothing reliable to look up. You cannot ask a formula for this desk, in this finish, at this width when the desk is one row and the finishes are text in a neighbouring cell.

This is the part that surprises people. They expect the hard bit to be the automation. The hard bit is nearly always the shape of the data underneath it.

One row for every version of every product

The fix is simple to describe and tedious to do. Every row in the product database defines one product and one variation of it.

A desk in four widths and three finishes stops being one row with options described in text. It becomes twelve rows, each one a thing a customer can actually buy, each with its own price.

That sounds like more work, and to build it, it is. But it is the only structure a quoting tool can use. Once each sellable variation has its own row, a salesperson can pick an item, pick a finish, set a quantity, and the system knows exactly what that costs without anyone interpreting a catalogue page.

It also fixes a problem the business hadn’t raised. When variations live in prose, two salespeople can read the same page and quote different prices. When each variation is a row with a price attached, they can’t.

Everything else followed from that

With the data in the right shape, the rest of the build was straightforward.

A pricing rule instead of a mental calculation. The business bought at distributor cost and sold at a fixed multiplier of it. That rule lives in the system now, applied the same way on every line, rather than being done in someone’s head or on a calculator.

A tabular form for the sales team. Pick an item, pick the finish, set a quantity. Repeat for each line. Freight and installation get their own fields, because they are real costs that don’t come from the catalogue.

One button, two documents. With the lines entered, generate. The system produces the estimate or the invoice as its own file, and a PDF to send.

A record of every line sold. Each generated document also writes its lines into a master file: document number, item, finish, quantity, sale price, and which salesperson made the sale. A dashboard over that file summarises what has been sold. Nobody enters this data. It accumulates because quoting happens.

Customer details captured once. Contact information entered on an estimate is saved to its own file rather than living only inside that document.

Stock on hand where it matters. The business orders most items in as customers buy them, but holds some. Those get a quantity so the team can see what is actually in the building.

How the requirements were pinned down

Worth describing, because it is why the restructure was caught early rather than halfway through the build.

Before quoting the work, we wrote the open questions into a shared document and asked the client to answer them there. They replied inline. We responded underneath. The scope, then the price and timeline, went into the same document, and the approval happened in it too.

Nothing about that is clever. It just means that by the time anyone wrote a formula, both sides had the same picture of what the system had to do, in writing, in one place.

That is also where the catalogue problem surfaced. Looking at the real price list and the real invoices in order to answer those questions is what made it obvious that the product data would have to be rebuilt.

What the business got

Quoting stopped being a retrieval task. Staff select from a list rather than finding a page in a catalogue, reading across it, and retyping the result somewhere else.

Prices stopped depending on who was quoting. One row, one price, one markup rule.

Sales reporting arrived without anyone maintaining it. Because every document writes its own lines to the master file, the sales history builds itself. That was not in the original request. It was possible only because the data had been restructured, and it cost almost nothing to add once it had been.

It grows by adding rows. A new product, or a new finish on an existing product, is a new row. No formulas to rewrite.

The client came to us on a referral from another client, and moved straight on to further work after this was delivered.

One thing we couldn’t do

The obvious next step was placing orders with the distributors automatically. We looked into it, and neither of their suppliers offered a public way for an outside system to submit orders.

So we said so, and noted it was outside the quote rather than leaving it as an implied maybe. Orders still get placed the way they always were, and the system stops where the data stops.

If you quote from a supplier price list

The industry here was office furniture, but the shape of the problem belongs to anyone who sells or installs products from someone else’s price list.

Roofers quoting shingles by colour and grade. Builders quoting doors by size and finish. Flooring, glazing, equipment dealers, anyone whose supplier issues a catalogue with variants in it.

If your staff are reading a PDF and retyping numbers into a quote, the reason automation hasn’t happened is usually not that nobody has built the tool. It is that the price list underneath it isn’t in a shape a tool can use.

That is fixable. It is mostly a matter of deciding what a row means.

If this sounds familiar

We build custom quoting and invoicing systems in Google Sheets and Excel, including the unglamorous work of getting supplier price data into a shape that can drive them.

Get in touch and we will look at your price list and tell you what it would take. No obligation. If your data is already in decent shape and a template would do, we will say so.