decimal value e.g. 1.2 (i.e. 20%) Simple Calculation but can't get right figure

[JosephGourvenec]

Hi all,
I’m having an issue trying to calculate the difference of two values to get a decimal point value and then deduct that value from our margin value to be competitive on price.
I’m not an excel wizard but get the principles of it, so I’m calling on your good people’s expertise to help me out again.

Example excel doc: https://www.dropbox.com/s/7achwotgqmr1u48/Example Calculations.xlsx?dl=0

Here is an example (based on row 3 in the attached example)
Our cost price Ex VAT = 9.45 (Column G)
Our margin = 1.30 = 30% (flexible to meet competitive pricing) (Column Q)
SKU price Ex VAT = 12.26 (Column R)
SKU Margin Ex VAT = 2.81 (Not in Excel sheet as this value)
Delivery fee Ex VAT = 4 (fixed fee) (Column AA)
Commission 18% = 1.18 / 18% (This is a fixed fee) (Column AB)
Our total SKU price Ex VAT = 19.22 (Column U)

Competitor price Ex VAT = 15.61 (Column K)
Difference vs our total price ex Vat = 3.61 (Column W)

What I’m trying to calculate is the reduction in the (Our Margin) value that we would need to reduce by to be competitive even if it is a negative value. “Not that I’d sell something at a loss”
But I need that as a decimal points value e.g. 1.30 but of the difference of the “.30” which is the actual % value e.g. 30%.

One I’ve tried is =SUM(Our total SKU price Ex VAT - Competitor price Ex VAT)/ABS(Competitor price Ex VAT) = 1.07
So then 1.30 – 1.07 = 0.23
- So my final margin is 7% to match that of my nearest competitor
- But that only brings my price to 16.29 not the competitors 15.61 price

What I cannot work out is how I get to that value which has been annoying me for the last couple of days.

I hope one of you lovely excelers can help me solve this soon.

Cheers,
Joe

 

[wideboydixon]

Margin to meet your competitor's price:

=(([Competitor Price]/[Commission])-[Delivery fee])/[Cost Price]

I haven't used the same columns as you (for illustration) but I think this is what you're after:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Cost Price	Margin	SKU price	SKU Margin	Delivery fee	Commission	Total	Competitor Price	Difference	New Margin
2	9.45	1.3	12.285	2.835	4	1.18	19.2163	15.61	3.6063	0.976594
Sheet2

Cell Formulas
Range	Formula
C2	=A2*B2
D2	=A2*(B2-1)
G2	=(C2+E2)*F2
I2	=G2-H2
J2	=(H2/F2-E2)/A2

WBD

 

[JosephGourvenec]

Thank you wideboydixon, the Worksheet formulas worked a treat.

The =(([Competitor Price]/[Commission])-[Delivery fee])/[Cost Price] came out with a different value when I tried it, but that was probably me.

All the best

Cheers,
JosephGourevenc