Let's now examine the Lemonade stand example from a slightly different point of view. Given a...

 

Transcribed Image Text:

Let's 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. 1. Open lemonade_gs_clean.xlsx file. 2. Let's set some initial values first: a. Fixed cost (B4) $45,000 b. unit cost (B3)=$0.45 c. Price (B1) = $3 d. Revenue (B5) = B1*82 e. Variable cost (B6) = 83*B2 f. Profit (B7) 85-84-86 A 1 price $ 3.00 2 demand 3 unit cost $ 0.45 4 fixed cost $ 45,000.00 5 revenue 6 variable cost $ $ 7 profit $(45,000.00) 3. Now, let's go to Data -> under the Forecast -> What-If Analysis -> Goal Seek, do the following: a. For the Set cell option select cell B7 (profit) b. The To value should be set to 0 (i.e., break even point) c. 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. Goal Seek Set cell: To value: SBS7 0 ? By changing cell: Sesa OK Cancel 4. After clicking OK, we should find the result we sought: 1 price $ 3.00 2 demand unit cost 17647.05882 $ 0.45 fixed cost revenue $ 45,000.00 $52,941.18 variable cost $7,941.18 profit $ (0.00) Homework: From the sensitivity analysis, determine how many CDs must be sold to break even. Let's 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. 1. Open lemonade_gs_clean.xlsx file. 2. Let's set some initial values first: a. Fixed cost (B4) $45,000 b. unit cost (B3)=$0.45 c. Price (B1) = $3 d. Revenue (B5) = B1*82 e. Variable cost (B6) = 83*B2 f. Profit (B7) 85-84-86 A 1 price $ 3.00 2 demand 3 unit cost $ 0.45 4 fixed cost $ 45,000.00 5 revenue 6 variable cost $ $ 7 profit $(45,000.00) 3. Now, let's go to Data -> under the Forecast -> What-If Analysis -> Goal Seek, do the following: a. For the Set cell option select cell B7 (profit) b. The To value should be set to 0 (i.e., break even point) c. 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. Goal Seek Set cell: To value: SBS7 0 ? By changing cell: Sesa OK Cancel 4. After clicking OK, we should find the result we sought: 1 price $ 3.00 2 demand unit cost 17647.05882 $ 0.45 fixed cost revenue $ 45,000.00 $52,941.18 variable cost $7,941.18 profit $ (0.00) Homework: From the sensitivity analysis, determine how many CDs must be sold to break even.

Answer rating: 100% (QA)

To determine the number of glasses of lemonade that must be sold to break even we can use the Goal S... View the full answer