A What-if scenario changing one figure to work out another
 

I have been trying to work out what gross sales are needed in order to
generate a profit after tax of $1,000,000. i think i will need to use
something like Goal Seek or Solver but i don't have a formula set up so i
can't use them yet.

Currently, this is what the financial data looks like: (assumptions in
brackets)
Gross Sales $2,105,500.00
Net Sales $2,085,500.00 (Gross sales - 3%)

MM&G $417,100.00 (20% of Net Sales)
Labour $325,000.00
Materials $600,000.00
Variable Overheads $277,500.00 (30% of Labour + Overheads)
Fixed Overheads $70,000.00

Cost of Goods Sold $1,272,500.00 (Labour+Materials+Overheads)

Gross Profit $813,000.00 (Net Sales - Cost of Goods Sold)
Profit Before Tax $395,900.00 (Gross Profit - MM&G)
Tax $138,565.00 (35% of Profit Before Tax)

Profit $257,335.00 (This is the figure i hope to reach
$1,000,000 on by changing the gross sales which has a flow-on effect with
some of the other figures).

A What-if scenario changing one figure to work out another
 

Yes, you need to use Goal Seek
Enter the following in Col A (rows 1-12)
Gross Sales 2105500
Net Sales =B1*0.990501069
MM&G =B2*0.2
Labour 325000
Materials 600000
Variable Overheads =(B4+B5)*0.3
Fixed Overheads 70000
Cost of Goods Sold =B4+B5+B6+B7
Gross Profit =B2-B8
Profit before Tax =B9-B3
Tax =B10*0.35
Profit =B10-B11

(btw some of the values you gave were not correct)

Now go to Tools-Goal Seek
Set Cell to $B$12
To Value to 1000000
By changing Cell to $B$12

You will get the following result;
Gross Sales $3,547,398.42
Net Sales $3,513,701.92
MM&G $702,740.38
Labour $325,000.00
Materials $600,000.00
Variable Overheads $277,500.00
Fixed Overheads $70,000.00
Cost of Goods Sold $1,272,500.00
Gross Profit $2,241,201.92
Profit before Tax $1,538,461.54
Tax $538,461.54
Profit $1,000,000.00



"ExcelNovice1" wrote:

I have been trying to work out what gross sales are needed in order to
generate a profit after tax of $1,000,000. i think i will need to use
something like Goal Seek or Solver but i don't have a formula set up so i
can't use them yet.

Currently, this is what the financial data looks like: (assumptions in
brackets)
Gross Sales $2,105,500.00
Net Sales $2,085,500.00 (Gross sales - 3%)

MM&G $417,100.00 (20% of Net Sales)
Labour $325,000.00
Materials $600,000.00
Variable Overheads $277,500.00 (30% of Labour + Overheads)
Fixed Overheads $70,000.00

Cost of Goods Sold $1,272,500.00 (Labour+Materials+Overheads)

Gross Profit $813,000.00 (Net Sales - Cost of Goods Sold)
Profit Before Tax $395,900.00 (Gross Profit - MM&G)
Tax $138,565.00 (35% of Profit Before Tax)

Profit $257,335.00 (This is the figure i hope to reach
$1,000,000 on by changing the gross sales which has a flow-on effect with
some of the other figures).

A What-if scenario changing one figure to work out another
 

The first half of what you recommended was really helpful, Thankyou!
Unfortunately, when it comes time to use the goal seek function following
the steps you gave on goal seek, i keep getting an error returned that says
"Cell must contain a value". are you sure i should select $B$12 both
times(for the 'Set cell' and 'By changing cell' sections)?

"Sheeloo" wrote:

Yes, you need to use Goal Seek
Enter the following in Col A (rows 1-12)
Gross Sales 2105500
Net Sales =B1*0.990501069
MM&G =B2*0.2
Labour 325000
Materials 600000
Variable Overheads =(B4+B5)*0.3
Fixed Overheads 70000
Cost of Goods Sold =B4+B5+B6+B7
Gross Profit =B2-B8
Profit before Tax =B9-B3
Tax =B10*0.35
Profit =B10-B11

(btw some of the values you gave were not correct)

Now go to Tools-Goal Seek
Set Cell to $B$12
To Value to 1000000
By changing Cell to $B$12

You will get the following result;
Gross Sales $3,547,398.42
Net Sales $3,513,701.92
MM&G $702,740.38
Labour $325,000.00
Materials $600,000.00
Variable Overheads $277,500.00
Fixed Overheads $70,000.00
Cost of Goods Sold $1,272,500.00
Gross Profit $2,241,201.92
Profit before Tax $1,538,461.54
Tax $538,461.54
Profit $1,000,000.00



"ExcelNovice1" wrote:

I have been trying to work out what gross sales are needed in order to
generate a profit after tax of $1,000,000. i think i will need to use
something like Goal Seek or Solver but i don't have a formula set up so i
can't use them yet.

Currently, this is what the financial data looks like: (assumptions in
brackets)
Gross Sales $2,105,500.00
Net Sales $2,085,500.00 (Gross sales - 3%)

MM&G $417,100.00 (20% of Net Sales)
Labour $325,000.00
Materials $600,000.00
Variable Overheads $277,500.00 (30% of Labour + Overheads)
Fixed Overheads $70,000.00

Cost of Goods Sold $1,272,500.00 (Labour+Materials+Overheads)

Gross Profit $813,000.00 (Net Sales - Cost of Goods Sold)
Profit Before Tax $395,900.00 (Gross Profit - MM&G)
Tax $138,565.00 (35% of Profit Before Tax)

Profit $257,335.00 (This is the figure i hope to reach
$1,000,000 on by changing the gross sales which has a flow-on effect with
some of the other figures).

A What-if scenario changing one figure to work out another
 

I am sorry for wrong instructions - that was a typo.
By changing Cell to should be $B$1 (Gross Sales)

Glad I could help a little.

Let me know how it goes...

"ExcelNovice1" wrote:

The first half of what you recommended was really helpful, Thankyou!
Unfortunately, when it comes time to use the goal seek function following
the steps you gave on goal seek, i keep getting an error returned that says
"Cell must contain a value". are you sure i should select $B$12 both
times(for the 'Set cell' and 'By changing cell' sections)?

"Sheeloo" wrote:

Yes, you need to use Goal Seek
Enter the following in Col A (rows 1-12)
Gross Sales 2105500
Net Sales =B1*0.990501069
MM&G =B2*0.2
Labour 325000
Materials 600000
Variable Overheads =(B4+B5)*0.3
Fixed Overheads 70000
Cost of Goods Sold =B4+B5+B6+B7
Gross Profit =B2-B8
Profit before Tax =B9-B3
Tax =B10*0.35
Profit =B10-B11

(btw some of the values you gave were not correct)

Now go to Tools-Goal Seek
Set Cell to $B$12
To Value to 1000000
By changing Cell to $B$12

You will get the following result;
Gross Sales $3,547,398.42
Net Sales $3,513,701.92
MM&G $702,740.38
Labour $325,000.00
Materials $600,000.00
Variable Overheads $277,500.00
Fixed Overheads $70,000.00
Cost of Goods Sold $1,272,500.00
Gross Profit $2,241,201.92
Profit before Tax $1,538,461.54
Tax $538,461.54
Profit $1,000,000.00



"ExcelNovice1" wrote:

I have been trying to work out what gross sales are needed in order to
generate a profit after tax of $1,000,000. i think i will need to use
something like Goal Seek or Solver but i don't have a formula set up so i
can't use them yet.

Currently, this is what the financial data looks like: (assumptions in
brackets)
Gross Sales $2,105,500.00
Net Sales $2,085,500.00 (Gross sales - 3%)

MM&G $417,100.00 (20% of Net Sales)
Labour $325,000.00
Materials $600,000.00
Variable Overheads $277,500.00 (30% of Labour + Overheads)
Fixed Overheads $70,000.00

Cost of Goods Sold $1,272,500.00 (Labour+Materials+Overheads)

Gross Profit $813,000.00 (Net Sales - Cost of Goods Sold)
Profit Before Tax $395,900.00 (Gross Profit - MM&G)
Tax $138,565.00 (35% of Profit Before Tax)

Profit $257,335.00 (This is the figure i hope to reach
$1,000,000 on by changing the gross sales which has a flow-on effect with
some of the other figures).

A What-if scenario changing one figure to work out another
 

Thanyou very much, your solution worked well. Your help was much appreciated.