Sue has opened a bakery specializing in cupcakes. Her budget must account for fixed expenses, such as her facility lease, utilities, and credit card equipment fees. In addition, she accounts for variable costs including cost of goods sold and credit card processing fees. You will use Goal Seek to determine how many cupcakes she must sell to earn a net profit of $3,500. Then you will use Scenario Manager to evaluate several possible situations. This exercise follows the same set of skills as used in Hands-On Exercise 2 in the chapter. Refer to Figure 6.32 as you complete this exercise.

a. Open e06p2Bakery and save it as e06p2Bakery_LastFirst .

b. Enter the following formulas:

• Cell B14 : =B16-B16*B8 to calculate the projected cash sales amount of the total sales.

• Cell B15: =B16-B14 to calculate the amount of income from credit card transactions.

• Cell B25: =B5*B6 to calculate the cost of goods sold, which is the product of the units sold and unit cost.

• Cell B26 : =(B10*B15)+(B9*B11) to calculate credit card processing fees, which are currently 1.25% of credit card amounts and $0.15 cents per transaction.

• Cell B29 : =B16-B22-B27 to calculate the net profit. c. Click the DATA tab , click What-If Analysis in the Data Tools group, and then select Goal Seek .

d. Complete the Goal Seek by doing the following:

• Click cell B29 to add the cell reference to the Set cell box.

• Click in the To value box and type 3500 .

• Click in the By changing cell box and click cell B5 .

• Click OK in the Goal Seek dialog box and click OK in the Goal Seek Status dialog box. How many cupcakes must Sue sell to reach her net profit goal of $3,500? Answer: 1,260.

Sue has opened a bakery specializing in cupcakes. Her budget must account for fixed expenses, such as her facility lease, utilities, and credit card equipment fees. In addition, she accounts for variable costs including cost of goods sold and credit card processing fees. You will use Goal Seek to determine how many cupcakes she must sell to earn a net profit of $3,500. Then you will use Scenario Manager to evaluate several possible situations. This exercise follows the same set of skills as used in Hands-On Exercise 2 in the chapter. Refer to Figure 6.32 as you complete this exercise.

a. Open e06p2Bakery and save it as e06p2Bakery_LastFirst .

b. Enter the following formulas:

• Cell B14 : =B16-B16*B8 to calculate the projected cash sales amount of the total sales.

• Cell B15: =B16-B14 to calculate the amount of income from credit card transactions.

• Cell B25: =B5*B6 to calculate the cost of goods sold, which is the product of the units sold and unit cost.

• Cell B26 : =(B10*B15)+(B9*B11) to calculate credit card processing fees, which are currently 1.25% of credit card amounts and $0.15 cents per transaction.

• Cell B29 : =B16-B22-B27 to calculate the net profit. c. Click the DATA tab , click What-If Analysis in the Data Tools group, and then select Goal Seek .

d. Complete the Goal Seek by doing the following:

• Click cell B29 to add the cell reference to the Set cell box.

• Click in the To value box and type 3500 .

• Click in the By changing cell box and click cell B5 .

• Click OK in the Goal Seek dialog box and click OK in the Goal Seek Status dialog box. How many cupcakes must Sue sell to reach her net profit goal of $3,500? Answer: 1,260.

Sue has opened a bakery specializing in cupcakes. Her budget must account for fixed expenses, such as her facility lease, utilities, and credit card equipment fees. In addition, she accounts for variable costs including cost of goods sold and credit card processing fees. You will use Goal Seek to determine how many cupcakes she must sell to earn a net profit of $3,500. Then you will use Scenario Manager to evaluate several possible situations. This exercise follows the same set of skills as used in Hands-On Exercise 2 in the chapter. Refer to Figure 6.32 as you complete this exercise.

a. Open e06p2Bakery and save it as e06p2Bakery_LastFirst .

b. Enter the following formulas:

• Cell B14 : =B16-B16*B8 to calculate the projected cash sales amount of the total sales.

• Cell B15: =B16-B14 to calculate the amount of income from credit card transactions.

• Cell B25: =B5*B6 to calculate the cost of goods sold, which is the product of the units sold and unit cost.

• Cell B26 : =(B10*B15)+(B9*B11) to calculate credit card processing fees, which are currently 1.25% of credit card amounts and $0.15 cents per transaction.

• Cell B29 : =B16-B22-B27 to calculate the net profit. c. Click the DATA tab , click What-If Analysis in the Data Tools group, and then select Goal Seek .

d. Complete the Goal Seek by doing the following:

• Click cell B29 to add the cell reference to the Set cell box.

• Click in the To value box and type 3500 .

• Click in the By changing cell box and click cell B5 .

• Click OK in the Goal Seek dialog box and click OK in the Goal Seek Status dialog box. How many cupcakes must Sue sell to reach her net profit goal of $3,500? Answer: 1,260.

a. Open e06p2Bakery and save it as e06p2Bakery_LastFirst .

b. Enter the following formulas:

• Cell B14 : =B16-B16*B8 to calculate the projected cash sales amount of the total sales.

• Cell B15: =B16-B14 to calculate the amount of income from credit card transactions.

d. Complete the Goal Seek by doing the following:

• Click cell B29 to add the cell reference to the Set cell box.

• Click in the To value box and type 3500 .

• Click in the By changing cell box and click cell B5 .

a. Open e06p2Bakery and save it as e06p2Bakery_LastFirst .

b. Enter the following formulas:

• Cell B14 : =B16-B16*B8 to calculate the projected cash sales amount of the total sales.

• Cell B15: =B16-B14 to calculate the amount of income from credit card transactions.

d. Complete the Goal Seek by doing the following:

• Click cell B29 to add the cell reference to the Set cell box.

• Click in the To value box and type 3500 .

• Click in the By changing cell box and click cell B5 .

a. Open e06p2Bakery and save it as e06p2Bakery_LastFirst .

b. Enter the following formulas:

• Cell B14 : =B16-B16*B8 to calculate the projected cash sales amount of the total sales.

• Cell B15: =B16-B14 to calculate the amount of income from credit card transactions.

d. Complete the Goal Seek by doing the following:

• Click cell B29 to add the cell reference to the Set cell box.

• Click in the To value box and type 3500 .

• Click in the By changing cell box and click cell B5 .