sales needed to cover costs that increase as sales do

[donna-LexusWebs]

Hello,
I'm trying to write a funcction that will calculate the amount of
incremental sales I would need to cover my costs if I were start paying a
sales commission.
Example:
If my original sales were 100,000
and I wanted to pay 3% commission, that would cost me:
75% of the sales (that is my cost of sale) PLUS the 3%. The problem is that
if I need to increase sales to cover the 3% commission, I have to pay the
cost of sales (75%) plus the commission on those sales and so on- and so-on.
It seems like a never ending loop. I can do a "solver" but I need a formula
to put into a spreadsheet to give to my sales team. It's more than breakeven
because of the incremental sales and costs associated with those additional
sales.

Any clues?!?! ANY input would be so appreciated!!

 

[Marcelo]

Hi,

did you try go seak?

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"donna-LexusWebs" escreveu:

 

[donna-LexusWebs]

I am not familiar with that function. What would it look like?

 

[tim m]

This may or may not be of any help to you but it might set you on the right
path. (There might be a function for this but if there is I haven't used it.)

I tried to reverse engineer your scenario by doing the following:
In Cell A2 I put $100,000 for sales, in column B2 I put cost of sales
A2*.75, in Column C2 I put Commision A2*.03 in Column D2 I put Profit A2-B2
(I left out commision in the 1st row becasue you want to calculate your
profit without the commision to begin with. That gives a profit of $25,000
on sales of $100,000 with no commisions.

I then copied that row to row 3 and this time I put A3-B3-C3 as the profit
as this time I'd be including the commision.

I then experimented with putting in various amounts in the Sales cell until
I came up with a profit of $25,000 with the 3% commision and .75 cost of
sales included.

I ended up with a figure of $113,635 as the needed sales to give you a
profit of $25,000 and pay out the commisions. (a 13.65% increase in sales)

As an experiement I doubled the sales amount to $227,270 and it gave double
the profits of $50,000

 

[tim m]

It's under 'Tools'....'goal seek'

I am not familiar with that function. What would it look like?

 

[donna-LexusWebs]

Thanks for your help, Tim. It was very helpful. I took it to the next level
and automated a Solver macro to reference the value that I would need since
it would change according to my data (and there's a lot of data to "solve
for").

Thanks again - I love this forum!! It is always so helpful...