Increase a Number by a Percentage when the New Sale Price Increases a fee

[razzandy]

Hi Guys

So, I need a formula or a VBA method to Increase a Sale Prices by 25% based on cost + 25% but when the sale price goes up a fee price also goes up so I end up with a circular reference!

Lets say:
Cost In E2, Fee in G2 and Postage Cost in i2. In J2 I want to add the costs up and add 25% which is easy

=SUM(E2,G2,I2)*(1+0.25)

the problem is the new Calculated Higher Sale Price now incurs a higher fee which which is not being included if you can understand what I am saying?!?!?!?

P.S. I work the fee out with this formula:

=ROUND(F2*0.138,2)+0.05

Thanks in advance

 

[razzandy]

Is 25% a constant for your Margin?
Also, now that you have iterations on, what Sales Price / Etsy fee are you currently generating for :
Cost @ 1.2 and Postage @ 3.064

Cost for that SKU in row 2 is 1.20, Postage 3.064 and eBay Fee is worked out with this formula:

ROUND(F2*0.138,2)+0.05

 

[Snakehips]

Cost for that SKU in row 2 is 1.20, Postage 3.064 and eBay Fee is worked out with this formula:

ROUND(F2*0.138,2)+0.05

Yes but what is the value you are expecting of getting for the Sale price in F ??
Also. Do you always use 25% as your margin?

 

[razzandy]

Yes but what is the value you are expecting of getting for the Sale price in F ??
Also. Do you always use 25% as your margin?

Arrrrr, Sorry!

I am expecting to get a Margin Increase of 25% (fixed) on top of the costs plus 20% VAT on top. I have to include VAT in the sale price.

I currently have this formula in Z2

=SUM(E2,G2,I2)/(1-AA$1)*(1+0.2)

and in F2 I have:

=z2

AA$1 Contains the markup percentage of 25%

 

[Snakehips]

Ah, Id not considered the inclusion of VAT.

Am I right in thinking that now you have iterations on to prevent the circular reference error, you are are getting your expected result for Sales Price and I can relax on even thinking any more about this?

 

[razzandy]

Ah, Id not considered the inclusion of VAT.

Am I right in thinking that now you have iterations on to prevent the circular reference error, you are are getting your expected result for Sales Price and I can relax on even thinking any more about this?

Yep, you are correct