Excel wants to calculate cost from sales price

[Bitto]

have example

Cost---- SG-A----- comm15%---- Price--- GM%
C-------- D---------- E--------------F----------G
$4768.19 $ 6038.68------ --20% ------$9350.00 49%

wants to calculate from price keeping the same GM 49% and sales price drop to 8750.

donot know the cost, wants to add the formula what cost we need to buy to get GM 49% and sales price $8750.00 and pay the 15% comm also

see attached the file

can some one please help with this formula

really appreciate it

thanks in advance

[Richard Buttrey]

Hi,

Does

=Sales Price * (1-49%) give you what you want?

[JohnTopley]

Try

=(Sales Price*((1-49%)-15%))*(1-21%)

See Row 6 of attached

[Bitto]

thanks John for Replying

its not giving the same result what I am looking for

I have 15%comm Column E------- F Column Price------- GM %
21.70% ---------------$ 8000.00---------------50%


I want to force the E column to comm to change to from 21.70 % to 15% and GM from 50% to 49% if I change the Price and it calculate the cost for me and show what the
cost should be added 21% SG&A

wants to 15% comm and GM 49% able to adj the sales price get the cost after adding 21% SG&A

when I added the formula (sales*((1-49%)-15%))*(1-21%) on Cost column, ii will not change the comm %, don't know why

added the formula on C6 and when tried to change the price on F6 , it did not work


see attached the file

[JohnTopley]

The calculation is based on the assumption the commission is 15% and GM is 49%.

I don't understand you logic for % commission : you include the Sg&A value in the % Commission calculation which in itself has a 15% calculation.


Confused!!!

Can you explain the calculations starting with Cost

Cost * A4 = Sg&A

Commission % : is it 15%of Sg&A ?

Should GM not include consideration of Sg&A and Commission?

Based on your calculation, Richard's formula (#2) would be correct.

[Bitto]

hi John
Calculation is per below
Cost $ 4000.00
SG &A $ 5063.29 Formula ( 4000/(1-21%) gives me $ 5063.29
comm (4000-(5063.29+(15%*8000)))/8000= 21.70 % gives me comm net 21.70%
Sales price $ 8000.00
GM (8000-4000)/8000= 50% GM


what I am looking for in the formula to if I change the Sales price get Commission 15%, what the cost should be

instead of cost 4000.00 is there any formula I can add in the cost cell if I change the sales price it can calculate the cost with 15% comm and SG&A rate 21%


can you help with formula

right now comm showing 21.70 %

would like comm to drop to 15%

really appreciate all the help

thanks again

[JohnTopley]

Sorry but you include a 15% calculation already: so what is this 15% if not commission %?

comm (4000-(5063.29+(15%*8000)))/8000= 21.70 %

[Bitto]

hi John

you are right

I already have 15% comm calculation, when the calculation done its gives me of 21.70% comm instead of 15% net comm when I have 15% comm in the calculation

that's what I want to fix it , how to reach the goal of net 15% comm ineadd of 21.70% comm when I change the sales price , what formula I have to change
and how to get cost from sales price .

want to make comm 15% net and calculates from sales price the cost including SG& A rate at 21%

how to get reach this goal.

can you please help.

[JohnTopley]

My original solution did this!!!

Please look again at this .....the blue cell is the commission % in E7 (15%) based on the commission amount in E6.

C6 is the required Cost to give your Sales price.


=(Sales Price*((1-49%)-15%))*(1-21%)

[Bitto]

John

its giving me the cost with this formula, but the comm % is not changing , if I change the Sales price , the way our comm is calculated its

(Sales-(Sg&A+(Comm*Sales)/Sales) gives comm net %


would ilke the formula in E6 if the change the Sales price in E6 comm % shows only 15% ,

right now if I change the sales price , it does not do anything to E6 cell .
we want to force the comm % to 15% net

I know I am asking a lot, what you guys are genius , if you can help, that will be great
some kind od formula, if sales

in E6 we have to have formula
(Sales-(Sg&A+(Comm*Sales)/Sales) gives comm net % to get Net comm .

hope you can help with this

E6 formula, almost there

thanks again

[JohnTopley]

The formula in E6 is (in my view) wrong! You cannot include Commission*Sales in that formula as you are going round in circles.

Forget your formula:

Is Commission Amount (not %)/Sales Price = 15% ?