I have a list of prices in Excel of which currently have a set Gross Price Percentage based on the cost and selling price, but what I want to do is now work out a target price for the seller to reduce their prices as necessary.
i.e.
PART01 - Costs=49.98 - Selling=62.48. Gross=20.02%
Ideally I would like to suggest a target price based on a Gross of 30%.
So the forumla's value would be 43.74
Fully appreciate any help anyone can give
If you want to calculate based on 30% gross - then isnt that
COST / ( 1-% )
Cost/0.7 for 30%
so
PART01 - Costs=49.98 - Selling=62.48. Gross=20.02%
PART02 = COST = 49.98 Gross = 30% = 49.98 / (1-0.3) = 71.40
isnt Cost Price set fixed ?, so you change the selling price and to make MORE margin the price has to increase ?
SO you can setup a formula to do that
OR are you trying to keep the same sales price - and change the COST to make more margin ?
to reduce the cost from sales and marging
COST = (1-margin)*revenue
Cost = (1-0.3) * 62.48
=43.74
PART03 - Costs=??? - Selling=62.48. Gross=30%
set up the formula New COST = (1-margin)*revenue
Last edited by etaf; 03-30-2021 at 08:02 AM.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Thank you for replying. What I mean is I would like to work out a new cost price based on the final price being 62.48 at 30%.
So in order to achieve a 30% gross on the selling price of 62.48, the formula would work the selling price is 43.74
It's basically working out a 30% gross in reverse, but using a fixed selling price and therefore, allowing the ability to set target prices based on a desired selling price
just noticed etaf you posted Cost = (1-0.3) * 62.48 and this works perfectly! - thank you for very much
i did edit my answer
however we may have crossed posts
so
OR are you trying to keep the same sales price - and change the COST to make more margin ?
to know the reduced cost price and keep the same sales and change the margin
COST = (1-margin)*revenue
Cost = (1-0.3) * 62.48
=43.74
PART03 - Costs=??? - Selling=62.48. Gross=30%
set up the formula New COST = (1-margin)*revenue
Originally Posted by etaf
Yeah, I'm fixing the selling price as this is the price that's competitive. So therefore its time to go back to the supplier and try to get a newer price with a desired gross of 30%. So to sell at 62.48 with a desired gross of 30%, the target cost price would be 43.74.
With B3 having the fixed selling price, I have utilised your formula as:
=(1-0.3)*B3
I've created a Gross Profit formula to make sure it's working also and it's showing the GP as 30%
you could also use a cell for the GP% and then change as needed
Otherwise its just B3*0.7
Anyway, glad its all working for you now
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks