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

 

[dreid1011]

It would be helpful if you could provide some sample data and expected results.

 

[razzandy]

Hi @dreid1011 thanks for your response.

Will a screenshot do? As you can see to get to my 25% Margin I have to just repeatedly, manually increase the sale price in f2 to achieve this but I wanted to add an additional formula that could be pasted down my list of products to produce my new sale price based on cost(s) + 25%. The stumbling block is with a new higher sale price comes a higher eBay fee!

 

[dreid1011]

What is the expected result for that sample data?

Also, I see you are using Excel 2007 and I am not very handy with the limited functions available in that version, so hopefully someone else can provide a solution. I do have something I think is working in 365, but without knowing what you expect, I cannot say for sure, and likely does not matter in any case.

 

[razzandy]

I want it to give me the sale price based a 25% Markup based on the costs + 25% but as said before the eBay cost changes based on that sales price.

 

[razzandy]

Do you @Snakehips think you have any ideas on this???

​

 

[Snakehips]

@razzandy Forgive me because I am perhaps not fully understanding.
If you are ncluding the variable Etsy fee within your calculation for price then you will inevitably have a circular reference issue because the Etsy fee is based upon that price.
Is it not possible for you to have the Etsy fee kept separate?

 

[razzandy]

@razzandy Forgive me because I am perhaps not fully understanding.
If you are ncluding the variable Etsy fee within your calculation for price then you will inevitably have a circular reference issue because the Etsy fee is based upon that price.
Is it not possible for you to have the Etsy fee kept separate?

You've hit the nail on the head @Snakehips the eBay fee is exactly as you say variable and the circular reference is where I ended up which resulted in me asking for help on here. I can't think of any way to keep the eBay fee separate because as you have pointed out it depends on the sale price. I am considering a VBA method where it increases the sale price in increments until the margin hits 25%. Maybe this is the only way, what do you think?

Thanks for your input

 

[razzandy]

So, here is what I have up to now:

Formula is in Z2 and AA1 contains the 25% value:

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

in addition to this I added a circular reference formula in F2 (Sale Price)

=Z2

and I followed the help file that pops up with the circular reference error which recommends turning on iterations as below:

Can anybody foresee any issues with this?

 

[Snakehips]

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