How To Prepare Competitive Price Quotes in Excel While Staying Profitable

Pricing your tenders intelligently will allow your company to answer the critical question during price negotiations:

Should we submit a quote for this order?

This article outlines things to consider before providing a price quote. You’ll find useful tricks to understand what you can and can’t do when evaluating the countless cost down requests.

Clue: Consider your average profits for whole order instead of individual items.

Wholesale orders or tenders including high volumes often requires competitive discounts and low prices. That’s why most small companies are reluctant to go into price competition in order not to make a loss at the end of the day even though volumes are high.

Retail pricing (or list prices) is nice and secure. You make sure that you make at least X% of profits from each item you sell.

However, big buyers demand better prices because they have the power to skyrocket your sales volumes. And they know that!

Good news is you can be competitive and profitable at the same time when offering price quotes for mass orders. The secret is keeping an eye on the average profitability of the whole order and not getting distracted with individual low prices. You can also use a repricing software to check your competitors prices and accordingly adjust yours. If you are selling on Amazon, you can check this excellent article on Commerce Caffeine about the best Amazon repricers on the market.

Clue: Calculate the Weighted Average Profitability of your quotation.

Don’t lose your attention when you see this type of technical jargon. It is actually easier than it sounds. Let’s get into it.

Whether you are selling products or services, every item in your list has a cost, right? So let’s get started with putting down your list prices and costs. For the sake of simplicity, we will use COGS (Cost of Goods Sold)*.

*Cost of Goods Sold: 

- It is basically how much money you pay in order to buy/produce 1 more of that item. The difference between the revenue and COGS is gross profits.

- You can include logistics, handling or other variable costs in this. But fixed costs such as rent, utility bills, overheads do not belong to Gross Profit calculation.

Let’s say you are selling computer parts and you have a price list similar to this:

It looks like you make nice gross profits* for most of the items except Power Cable and Cooling Fan. Probably, these items are either too competitive in terms of pricing or we don’t purchase them with advantageous prices. Either way, we barely make money for these items.

*Gross Profit:  

This is the most basic and main profitability figure in the trade business. Often confused with markup. Here is a nice representation of margin and markup:

Sounds like a familiar scenario?

Normally, you sell around 20-30 pieces of each of them per month. That’s good enough to cover the costs and earn a living.

Now, there is this customer who wants to buy a high volume of these parts. And they only accept one supplier to cover them all. Let’s see what they want:

1200 pieces in one order! That’s huge but whenever you see those very cheap prices for some items it quickly turns your off. And when they ask if you want to prepare a price quote for this total order, you will say:

NO WAY!

Sorry but that would be a terribly wrong move. Because, buddy, this order has a hefty profit!

Now comes the nice part of our friend: Weighted Average Profitability

Let’s see how it adds up:

You are right. The requested price for those two items is very cheap and make you lose money. But the other two items are profitable enough to cover the losses.

The mathematical concept here is very simple: you just multiply the quantities with costs and net prices separately and calculate total profitability with the totals.

In Excel you can make this calculation with SUMPRODUCT function easily:

FINAL THOUGHTS

Of course, we are exaggerating the situation here for the sake of presentation. Nobody is fool and most businessmen are probably aware of this approach (they even do this intuitively).

But if you frequently deal with quotes, sometimes you need to be fast and make these calculations quickly while talking on the phone with the customer.

For these cases, we have built a Wholesale Price Calculator which you can download and check out.

Another extremely powerful tool to define your prices is Someka’s Product Pricing Calculator in Excel. 

Hope this helps!