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

[ExcelNovice1]

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).

 

[Sheeloo]

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]

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]

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]

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