Scaling Discounts with a twist

[Berandon]

I do jobs ranging from $200 - $200,000. I am trying to figure out a way to scale a discount appropriately, but can't seem to find the right formula. I asked AI and it brought up a base discount and scale factor, but it isn't working. Nothing beats real people who have solved the problem before. With my formula, I want to offer a 5% discount as a base, no matter the size, just to show something. But as the job gets bigger, I can afford to give more discount, but i have found that 25% discount seems to be my cap until I hit the $100k mark and then i do a discount based on time it will take and every one of those jobs is unique. So i am trying to appeal to the masses. That said, back to the 5% base, I have normally done:
$0-$5k is 5%
$5k-$10k is 10%
$10k-$15k is 15%
$15k-$25k is 25%
$25k+ is per job basis.
However, the issue i am running into is that when someone is barely at $9800 and they add a small thing, the discount jumps and they get it cheaper than without the thing. which isn't the end of the world. But it tends to annoy customers that it is cheaper to buy more and they seem put off by it. So i want to come up with a scale that makes the discount never fall below the previous brackets max discount.
So, for example:
Someone who spends $14,999 would be discounted to $12,749.15
But someone who spends $15,001 would be discounted to $11,250.75
and that doesn't make sense to me or my customer. I find myself juggling a whole bunch of numbers making sure everything "seems" right and it is tedious and exhausting.

I am over simplifying my formulas right now to check the answers, they will be condensed in the final version. But i will post an example to show where I am at on this iteration.

Other Formulas I have tried and Didn't seem to work, Unless I messed something up
=MAX(F2, MIN(H2, SUM(FILTER(B$2:B2, LTE(A$2:A2, A2)))))
=MAX( B2 * (1 - (F2 + G2 * MAX(0, A2 - 1))), IF(A2 > 1, OFFSET(C2, -1, 0), B2))

Both of these were AI generated and didn't give me the result I wanted. Also, the spreadsheet was smaller with only Quantity, Original Price, Discount Price as columns. So the formulas wont make sense in my current run as i changed it so i could visualize each stage.

I really hope I made sense and someone out there can help me figure this out.

Thank you for your time and any help you can give will be much appreciated.

Retail Price	Base Discount	Scaling Discount	Total Discount	Discounted Price	Discount Percentage		Base Discount	Scale Factor	Max Discount
							.5%	001	25%
2000	=sum(A3*$H$2)	=Sum(B3*$I$2)	=sum(B3:C3)	=A3-D3	=sum(D3/A3)
4900
5001

 

[Micron]

IMO, discounting like that sends a message that you're not really worth what you would be asking without the discount. I'm sure you are, but that's a potential perception. I doubt I can help with formulas you may need, but I can propose an approach that could eliminate the odd scaling effects.

What about once a job is tallied and discounted, that's it? Add-ons would not be discounted, or if they are, discount them as if the add-on was the only job.
For second option:
If you must, discount 14,999 by 15% but discount 100 extra by 5, so
a) 14,999 - 15% = 12,749.15
b) 100 - 5% = 95.00
a+b= 12,844.15

 

[Berandon]

I think the solution might work. I'll see if i can make a formula that implements this.

As Far as the discounts go. I agree and we didn't used to show discounts. I have the numbers of what it costs per day (as well as average percentage over the year) and to be open and how many jobs we can complete per day. But it was a struggle, Customers would ask why we didn't show a discount like other companies and we would try and explain it to them that it wasn't our system. But our numbers show that months we show customers a discount, they are more likely to buy vs hiding the discount we give as part of the overall bid. I have a retail number that covers my overhead at the average low end contract amount. On a $3000 job, we need about 55% margins to cover the overhead of the office and staff. But on a $20k job, we only need about 38% margins to cover us. On special projects, we switch to a per day dollar amount and add in commission and profit and taxes and CC fees etc as line items to make sure we are covered.

I have toyed with the idea of not pre marking up pricing and have the salesmen mark up to what we need instead of discounting. but I don't want to change stuff around on them too much and they feel more confident telling a customer, "you get a __% discount" because of the contract amount or size of project. It also lets us run promotion during holidays and its one more touch point to reach out to customers from the past to extend a current promotion offer as a possible win back scenario.

Thanks for your time.

Ill reply again if i can get a formula to do what you laid out above

 

[Berandon]

So, I spent all day playing with different scaling tactics and I may look for something else, But i simplified it but just dividing the contract amount by 100000 and multiplied by the contract to get the discount dollars and added an if statement to cap it at 25%.

It will work for my situation for now. I am open to other solutions as well if others have ideas.

 

[Micron]

You could also use a vba function call as a formula. Instead of
=If(formula here) you'd have =myFunctionName(cell reference such as C2)
and drag that down. Function can return "" for when C row is empty and you've dragged it down that far (or perhaps simply exit). This would allow you to write any number of decision blocks and return the discounted amounts. However, I think you'd need a way to separate add ons to do what I suggested.