Costing Problem with Final Sale Price

kierenschneider · #1 12-07-2009, 10:56 AM

Hi all,

I am trying to get an accurate way of costing our products using a spreadshhet where we can change our required margins for different markets.

The only problem is that we have a Paypal fee on all transactions, so that includes postage and all other costs. What I would like to do is include the cost of the transaction fee into the sale price, so that we are not losing our margin. But when you add 3.9% +0.20p to say £10 it becomes £10.59. then if you apply the transaction fee its £10.59*0.039 +0.2=£0.61 not 0.59.

Any help would be really appreciated. I have been wrapping my brain for days trying to do this.

Thanks
Kieren

modytrane · #2 12-07-2009, 11:10 AM

I don't understand your problem.
The way you describe it, you are adding 3.9% +0.20 to 10.00 to get 10.59.
That is correct so far.
Then you are calculating transaction fee of 3.9% +0.20 on 10.59.
Of course, the result will be 0.61.
Between the two calculations, you are changing the base price from 10.00 to 10.59, so you are getting the extra 0.02 [going from 0.59 to 0.61], which is 3.9% of 0.59.

Please provide better examples or clarify your problem.
modytrane.

kierenschneider · #3 12-07-2009, 11:20 AM

thats exactly it. I want an accurate way to calculate the price to sell at to include the transaction fee. So that no money is lost and our margins are correct.

in a nut shell how do you calculate the paypal cost into the sale fee so that it is correct.

Thanks

scaffdog845 · #4 12-07-2009, 11:37 AM

kierenschneider I agree with modytrane, you are adding in the costs associated with the sale to get a new sale price. Pay Pal will charge these costs using the inflate rate so the final bill cannot eaqual the original bill.

Perhaps iploading a small sample workbook with your expected results spelled out would be helpfull.

modytrane · #5 12-07-2009, 04:09 PM

I think I understand the problem now.
Correct me if the following is wrong.
You want to figure out sale price such that when PayPal takes out 3.9% of Sale price for commission and you subtract 0.20 for handling, you would get your original price.
Because of rounding errors, you will not get exact numbers, but it can be close enough.
Look at the attached sample file.
I start out with original price in column D, then calulate Sale Price in column E [That's the formula you want]. To verify the numbers, I calculate the cost based on sale price in column F and subtract it from sale price to see if it matches original price.

Hope it helps.
modytrane.

Huron · #6 12-07-2009, 04:51 PM

I think my attached file will do what you want. You enter the required margin percentage and it calculates the selling price by dividing the calculated Paypal amount on the cost elements by the PayPal rate. I've put in a check column so that you can see it works correctly.

agarwaldvk · #7 12-07-2009, 09:40 PM

Hi Guys

You might want to have a look at this version. The modified spreadsheet is attached herewith. For detailed information, please see the comments in the appropriate cells.

Deepak

modytrane · #8 12-08-2009, 09:57 AM

I think everyone here is coming up with their own version of costing, margin and sell price calculations.
They all have merits and none are wrong, but we haven't heard anything from OP, so we don't know what was originally intended.

Here's my interpretation:
From the very first post, I understood that 10.00 was their sell price. So, that already included their margin.
Then they wanted to add handling costs + Paypal charges to come up with final sell price, such that after paying those charges, they would still wind up with 10.00 [their original sell price]. That way, after paying handling charges, they would still make their margin [built in to 10.00].
Attached example satisfies this scenario.

Let's hear from Kieren, and see what the original intent was.
modytrane.

kierenschneider · #9 12-11-2009, 03:55 AM

Hi all,

thanks for all the help. Sorry I havent been on...

I will review the examples and let you know ASAP.

Thanks again.

kierenschneider · #10 12-11-2009, 05:29 AM

WOW thanks everyone. I have worked through the spreadsheets and they are exactly what I am looking for.

Sell Price = C x (1/(1-y))+0.2) is what I was looking for.

Brilliant!

You guys should be proud, the people on the physics forum couldnt solve it.

Cheers
Regards,
Kieren

Welcome to the Excel Forum

Forum Statistics