Breakeven/ Math Question....
 

I know how to determine breakeven, and I know how to solve for a targeted
profit level. What I can't get my head around is how I solve for a targeted
profitability in terms of a percentage of sales........... I can provide an
example, but in the meantime, I thought maybe someone else has been down this
road.

TIA!

Breakeven/ Math Question....
 

There are a lot of variables that go into profitability by percent of sales,
the main being profit margin, you would need to A. have only one item B. do
an average or C. Do a calculation for each sale, but this can all be done,
just need to know how you calculate

-John

"JEFF" wrote:

I know how to determine breakeven, and I know how to solve for a targeted
profit level. What I can't get my head around is how I solve for a targeted
profitability in terms of a percentage of sales........... I can provide an
example, but in the meantime, I thought maybe someone else has been down this
road.

TIA!

Breakeven/ Math Question....
 

Hi John,

Thanks for the reply €” I'll keep it simple:

Net Sales 200,000 (or whatever)
Cost of Sales will always be 30% of Sales
Gross Margin = Sales - Cost of Sales
Operating Expenses fixed in this scenario
Income Before Taxes = Gross Margin - Operating Expenses

** Need to determine level of sales to realize an Income Before Tax of, say,
5%... Obviously, I could use Goal Seek, but really need it as a formula.


Thanks a lot.


"John Bundy" wrote:

There are a lot of variables that go into profitability by percent of sales,
the main being profit margin, you would need to A. have only one item B. do
an average or C. Do a calculation for each sale, but this can all be done,
just need to know how you calculate

-John

"JEFF" wrote:

I know how to determine breakeven, and I know how to solve for a targeted
profit level. What I can't get my head around is how I solve for a targeted
profitability in terms of a percentage of sales........... I can provide an
example, but in the meantime, I thought maybe someone else has been down this
road.

TIA!

Breakeven/ Math Question....
 

Sorry jeff, I gave it a shot but i suck at math, given the calculations I can
do it without any issues. Why is goal seek a no go?

"JEFF" wrote:

Hi John,

Thanks for the reply €” I'll keep it simple:

Net Sales 200,000 (or whatever)
Cost of Sales will always be 30% of Sales
Gross Margin = Sales - Cost of Sales
Operating Expenses fixed in this scenario
Income Before Taxes = Gross Margin - Operating Expenses

** Need to determine level of sales to realize an Income Before Tax of, say,
5%... Obviously, I could use Goal Seek, but really need it as a formula.


Thanks a lot.


"John Bundy" wrote:

There are a lot of variables that go into profitability by percent of sales,
the main being profit margin, you would need to A. have only one item B. do
an average or C. Do a calculation for each sale, but this can all be done,
just need to know how you calculate

-John

"JEFF" wrote:

I know how to determine breakeven, and I know how to solve for a targeted
profit level. What I can't get my head around is how I solve for a targeted
profitability in terms of a percentage of sales........... I can provide an
example, but in the meantime, I thought maybe someone else has been down this
road.

TIA!

Breakeven/ Math Question....
 

I want to give it to novice users, along with a cell reference that allows
them to simply pick a desired Income Before Tax % and have the answer fall
out............

"John Bundy" wrote:

Sorry jeff, I gave it a shot but i suck at math, given the calculations I can
do it without any issues. Why is goal seek a no go?

"JEFF" wrote:

Hi John,

Thanks for the reply €” I'll keep it simple:

Net Sales 200,000 (or whatever)
Cost of Sales will always be 30% of Sales
Gross Margin = Sales - Cost of Sales
Operating Expenses fixed in this scenario
Income Before Taxes = Gross Margin - Operating Expenses

** Need to determine level of sales to realize an Income Before Tax of, say,
5%... Obviously, I could use Goal Seek, but really need it as a formula.


Thanks a lot.


"John Bundy" wrote:

There are a lot of variables that go into profitability by percent of sales,
the main being profit margin, you would need to A. have only one item B. do
an average or C. Do a calculation for each sale, but this can all be done,
just need to know how you calculate

-John

"JEFF" wrote:

I know how to determine breakeven, and I know how to solve for a targeted
profit level. What I can't get my head around is how I solve for a targeted
profitability in terms of a percentage of sales........... I can provide an
example, but in the meantime, I thought maybe someone else has been down this
road.

TIA!

Breakeven/ Math Question....
 

Since the cost of sales is always 30% of sales, gross margin will be 70% of sales.

To calculate the sales needed to have a given Income Before Taxes (as a percentage of sales), try this formula:
Sales = (operating expenses) / (0.7 - IBT) where "IBT" is the desired percentage.

--

"JEFF" wrote in message ...
I want to give it to novice users, along with a cell reference that allows
them to simply pick a desired Income Before Tax % and have the answer fall
out............

"John Bundy" wrote:

Sorry jeff, I gave it a shot but i suck at math, given the calculations I can
do it without any issues. Why is goal seek a no go?

"JEFF" wrote:

Hi John,

Thanks for the reply €” I'll keep it simple:

Net Sales 200,000 (or whatever)
Cost of Sales will always be 30% of Sales
Gross Margin = Sales - Cost of Sales
Operating Expenses fixed in this scenario
Income Before Taxes = Gross Margin - Operating Expenses

** Need to determine level of sales to realize an Income Before Tax of, say,
5%... Obviously, I could use Goal Seek, but really need it as a formula.


Thanks a lot.


"John Bundy" wrote:

There are a lot of variables that go into profitability by percent of sales,
the main being profit margin, you would need to A. have only one item B. do
an average or C. Do a calculation for each sale, but this can all be done,
just need to know how you calculate

-John

"JEFF" wrote:

I know how to determine breakeven, and I know how to solve for a targeted
profit level. What I can't get my head around is how I solve for a targeted
profitability in terms of a percentage of sales........... I can provide an
example, but in the meantime, I thought maybe someone else has been down this
road.

TIA!

Breakeven/ Math Question....
 

JEFF wrote...
....
Net Sales 200,000 (or whatever)
Cost of Sales will always be 30% of Sales
Gross Margin = Sales - Cost of Sales
Operating Expenses fixed in this scenario
Income Before Taxes = Gross Margin - Operating Expenses

** Need to determine level of sales to realize an Income Before Tax of, say,
5%... Obviously, I could use Goal Seek, but really need it as a formula.

OK, the 5% means the ratio of income before taxes to net sales. So net
sales means total proceeds from sales less sales or excise taxes?

Anyway,

IBT = GM - OE

GM = NS - CoS

CoS = 0.3 * NS

so

GM = 0.7 * NS

IBT = 0.7 * NS - OE

Then

5% = 0.05 = IBT / NS = 0.7 - OE / NS

which rearranges to

NS = OE / 0.65

in this particular case. More generally, the formulas work out to

IBT = NS - CoS - OE

and profit margin is

IBT / NS = 1 - (CoS + OE) / NS

This may be solved for a target profit margin, TPM.

TPM = 1 - (CoS + OE) / NS

which rearranges to

NS = (CoS + OE) / (1 - TPM)

Breakeven/ Math Question....
 

Jim,

Thanks, this will work for this vanilla example.... I may be back :)



"Jim Reed" wrote:

Since the cost of sales is always 30% of sales, gross margin will be 70% of sales.

To calculate the sales needed to have a given Income Before Taxes (as a percentage of sales), try this formula:
Sales = (operating expenses) / (0.7 - IBT) where "IBT" is the desired percentage.

--

"JEFF" wrote in message ...
I want to give it to novice users, along with a cell reference that allows
them to simply pick a desired Income Before Tax % and have the answer fall
out............

"John Bundy" wrote:

Sorry jeff, I gave it a shot but i suck at math, given the calculations I can
do it without any issues. Why is goal seek a no go?

"JEFF" wrote:

Hi John,

Thanks for the reply €” I'll keep it simple:

Net Sales 200,000 (or whatever)
Cost of Sales will always be 30% of Sales
Gross Margin = Sales - Cost of Sales
Operating Expenses fixed in this scenario
Income Before Taxes = Gross Margin - Operating Expenses

** Need to determine level of sales to realize an Income Before Tax of, say,
5%... Obviously, I could use Goal Seek, but really need it as a formula.


Thanks a lot.


"John Bundy" wrote:

There are a lot of variables that go into profitability by percent of sales,
the main being profit margin, you would need to A. have only one item B. do
an average or C. Do a calculation for each sale, but this can all be done,
just need to know how you calculate

-John

"JEFF" wrote:

I know how to determine breakeven, and I know how to solve for a targeted
profit level. What I can't get my head around is how I solve for a targeted
profitability in terms of a percentage of sales........... I can provide an
example, but in the meantime, I thought maybe someone else has been down this
road.

TIA!