Goal Seek command in Excel

Lets now examine the Lemonade stand example from a slightly different point of view. Given a specific price for a glass, how many glasses your stand would need to sell in order to break even? The Goal Seek feature in Excel allows us to answer this question quickly.

Open lemonade_gs_clean.xlsx file.

Lets set some initial values first:

Fixed cost (B4) = $45,000

unit cost (B3) = $0.45

Price (B1) = $3

Revenue (B5) = B1*B2

Variable cost (B6) = B3*B2

Profit (B7) = B5-B4-B6

Now, lets go to Data -> under the Forecast -> What-If Analysis -> Goal Seek, do the following:

For the Set cell option select cell B7 (profit)

The To value should be set to 0 (i.e., break even point)

By changing cell option should point to B2 as we want to find the quantity that will allow us to break even given a price.

After clicking OK, we should find the result we sought:

Homework: From the sensitivity analysis, determine how many CDs must be sold to break even.