Calculate units needed for price reduction

[Caly]

Hello I have competition price and units sold compared to my current price and units sold

If I have to reduce my price to match competition price I am needing a formula to see the number of units needed to be sold to reviver the margin loss

Example
Cost is $10
Current price is $20 and my units sold is 5 so my margin is 50%, ext sales of $100 and margin dollars is $50

Competitors price is $15 and units sold is 20 so my the margin is 33.33%, sales volume of $300 and $100 margin dollars

If I lower to the $15 price hoping to increase sales I need to determine the number of units needed to sell to still make up the margin dollars

Is there a formula for that?

 

[ExceLoki]

Book1
	A	B	C	D	E
1	cost	current price	current units	competitor price	# of units to make up margin
2	10	20	5	15	10
Sheet1

Cell Formulas
Range	Formula
E2	E2	=(B2-A2)/(D2-A2)*C2

 

[Caly]

Thank you. Have an additional ask, I need to see the units needed to be sold to recover the difference in lowering the price based on the total sales difference. Below is an example with much varying numbers so struggling on this one.

Example
Cost is $207.89
Current price is $299.95, units sold is 10, margin is 30.69%, total sales is $2,999.50 with margin dollars of $920.56

Competitive price is $249.98, units sold is 210, margin is 16.84%, total sales using the current units sold at the current price is $2,499.80 (10 units multiples by the comp price 249.98) and margin dollars is $420.86

The competition price is better and holds the market share at 4.76% (taking current units sold of 10 divided by competition units 210)

The price delta is -$499.70 (2499.80 comp total sales MINUS 2999.95 current total sales or the margin dollars difference using comp margin dollars 420.86 minus current margin dollars 920.56)

Is there a formula to see the number of units needed to make up the 499.70 difference?