Question

i need help with this exce problem. i got everyrhing done but im stuck on G and im noy sure what to do. please if answer do it in excel. than k you

Sue has opened a bakery specializing in Gun Oakery specializing in cupcakes. Herhude must account for fixed expenses, such as her facility lease, utilities, and credit card equipment fees. In addition, she acca costs including cost of goods sold and credit card processing fees. You will use mine how many cupcakes she must sell to earn a net profit of $3.500. You then will Manager to evaluate several possible situations Refer to Fleure 6.43 as wosi complete this exercise equipment fees. In addition, she accounts for variable al card processing fees. You will use Goal Seek to deter- 2 Sue's Bakery Budget 4 Forecasts 5 Cupcakes sold and sold 6 Unt price 8 Percentage of credit card sale 9 Credit Card Swiona per Mont 10 Crede Card Transaction Rate 11 Credit Card Cost per Swipe 16 Total income 18 Fixed Costs 20 Uteties 21 Credit Card Equipment 25 Costs of goods sold 28 Crede Card Processing Fees Excel 2016, Windows 10, Microsoft Corporation • Scenario Summary Ready FIGURE 6.43 a. Open 06p2Bakery and save it as e06p2Bakery Last First 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 cur- rently 1.25% of credit card amounts and S0.15 cents per transaction. . Cell B29: =B16-B22-B27 to calculate the net profit. Click the Data tab, click What If Analysis in the Forecast 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.

Answer 1

Scenario Summary
		Ideal Case	Increased Costs	Low sales
Changing Cells:
Units sold	$B$5	1260	700	500
Units cost	$B$6	$           0.75	$           0.75	$           0.75
Sales price	$B$7	$           4.25	$           4.50	$           4.00
credit card swipes	$B$9	375	275	150
Card transaction rate	$B$10	1.25%	2.49%	1.25%
Result Cells:
Gross sales	$B$16	$    5,355.00	$    3,150.00	$    2,000.00
fixed costs	$B$22	$       790.00	$       790.00	$       790.00
Variable costs	$B$27	$    1,065.00	$    1,065.00	$    1,065.00
profit	$B$29	$    3,500.00	$    1,295.00	$       145.00
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.

1. Go to Data\Whatif analysis\Scenario manager

2. click Add, Enter the scenario name as Ideal case, click OK

3. Enter the values corresponding to Ideal case scenario in the cells boxes

4. Click OK

5. Repeat the process from ADD with the other 2 scenarios

6. Later, click summary

7. In result cells box, enter the said cells(b16,b33,b27,b29) using Ctrl

8. Click OK

Note: if the amounts in result cells are not varying with scenarios, then you probably havent linked the cells properly using formulas (as detailed in the question b)