Illustrated Excel 2016 | Module 2: SAM Project 1b

Beth's Caf

working with formulas and functions

GETTING STARTED

Open the file IL_EX16_2b_FirstLastName_1.xlsx, available for download from the SAM website.

Save the file as IL_EX16_2b_FirstLastName_2.xlsx by changing the "1" to a "2".

oIf you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

With the file IL_EX16_2b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

oIf cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1.Beth owns a caf chain with stores in four different cities. She wants to analyze her sales of and sales taxes associated with her store's most popular items.

Go to the Sales Summary worksheet.

Copy the contents of the range A6:A11 and paste them into the range A15:A20 so that Beth can compare the sales of and the sales taxes for the same products.

2.In cell B11, create a formula using the SUM function to total the values in the range B6:B10.

Using the Fill Handle, copy the formula in cell B11 into the range C11:F11.

3.Copy the contents of cell F5 to cell F14.

4.In cell E15, create a formula that multiplies the value in cell E6 (Latte sales in Baltimore) by the value in cell E14 (sales tax rate in Baltimore). Use a relative reference to cell E6 and an absolute reference to cell E14 in the formula.

Copy the formula you created in cell E15 to the range E16:E19 to determine the sales tax for each item sold in Beth's Baltimore branch.

5.Using the Fill Handle, copy the formula in cell B20 to the range C20:F20.

6.Beth wants to open a new store in a different city, and she wants to estimate what her expenses would be.

In cell A22, change the text to Average tax rate for all cities (without including a period at the end of the phrase).

7.In cell D22, create a formula that uses the AVERAGE function to identify the average tax rate for all cities from the values in the range B14:E14.

8.Beth would like to know which products resulted in her collecting the highest and lowest amounts of sales tax.

In cell A23, enter the text Highest tax paid across products (without including a period at the end of the phrase).

9.In cell D23, create a formula that uses the MAX function to identify the highest value in the range F15:F19.

10.In cell D24, create a formula that uses the MIN function to identify the lowest value in the range F15:F19.

11.Beth is interested in knowing how much revenue she generated from these popular items in total after deducting taxes.

In cell D25 create a formula that subtracts the value in cell F20 (total sales taxes collected) from the value in cell F11 (total sales).

12.Beth would also like the final revenue total to be rounded without decimal places.

In cell D26, create a formula using the ROUND function that rounds the value in cell D25 to zero decimal places.

13.Because Beth already calculated the average tax rate for all cities in cell D22, the range A27:D27 is no longer necessary. Delete the cells in the range A27:D27 and shift the remaining cells up.

14.Cell A3 contains a note indicating this document is incomplete. As the worksheet is now finished, clear the contents of cell A3.

Your workbook should look like the Final Figure on the following page. Save your changes, close the workbook, then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Sales Summary Worksheet

Illustrated Excel 2016 | Module 2: SAM Project 1b Beth's Caf WORKING WITH FORMULAS AND FUNCTIONS GETTING STARTED Open the file IL_EX16_2b_FirstLastName_1.xlsx, available for download from the SAM website. Save the file as IL_EX16_2b_FirstLastName_2.xlsx by changing the \"1\" to a \"2\". o If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically. With the file IL_EX16_2b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. o If cell B6 does not display your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. Beth owns a caf chain with stores in four different cities. She wants to analyze her sales of and sales taxes associated with her store's most popular items. Go to the Sales Summary worksheet. Copy the contents of the range A6:A11 and paste them into the range A15:A20 so that Beth can compare the sales of and the sales taxes for the same products. 2. In cell B11, create a formula using the SUM function to total the values in the range B6:B10. Using the Fill Handle, copy the formula in cell B11 into the range C11:F11. 3. Copy the contents of cell F5 to cell F14. 4. In cell E15, create a formula that multiplies the value in cell E6 (Latte sales in Baltimore) by the value in cell E14 (sales tax rate in Baltimore). Use a relative reference to cell E6 and an absolute reference to cell E14 in the formula. Copy the formula you created in cell E15 to the range E16:E19 to determine the sales tax for each item sold in Beth's Baltimore branch. 5. Using the Fill Handle, copy the formula in cell B20 to the range C20:F20. 6. Beth wants to open a new store in a different city, and she wants to estimate what her expenses would be. In cell A22, change the text to Average tax rate for all cities (without including a period at the end of the phrase). 7. In cell D22, create a formula that uses the AVERAGE function to identify the average tax rate for all cities from the values in the range B14:E14. Illustrated Excel 2016 | Module 2: SAM Project 1b 8. Beth would like to know which products resulted in her collecting the highest and lowest amounts of sales tax. In cell A23, enter the text Highest tax paid across products (without including a period at the end of the phrase). 9. In cell D23, create a formula that uses the MAX function to identify the highest value in the range F15:F19. 10. In cell D24, create a formula that uses the MIN function to identify the lowest value in the range F15:F19. 11. Beth is interested in knowing how much revenue she generated from these popular items in total after deducting taxes. In cell D25 create a formula that subtracts the value in cell F20 (total sales taxes collected) from the value in cell F11 (total sales). 12. Beth would also like the final revenue total to be rounded without decimal places. In cell D26, create a formula using the ROUND function that rounds the value in cell D25 to zero decimal places. 13. Because Beth already calculated the average tax rate for all cities in cell D22, the range A27:D27 is no longer necessary. Delete the cells in the range A27:D27 and shift the remaining cells up. 14. Cell A3 contains a note indicating this document is incomplete. As the worksheet is now finished, clear the contents of cell A3. Your workbook should look like the Final Figure on the following page. Save your changes, close the workbook, then exit Excel. Follow the directions on the SAM website to submit your completed project. Final Figure 1: Sales Summary Worksheet Author: Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. Illustrated Excel 2016 | Module 2: SAM Project 1b Beth's Caf WORKING WITH FORMULAS AND FUNCTIONS Delvin Mines-Allen ot edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. Beth's Caf Sales Summary Sales by Items and Note: Incomplete Document Sales by Items and City Product City Latte Cappuccino Macchiato Espresso Moka Total Baton Rouge 79833.45 90218.27 73281.44 30823.19 21939.23 Arlington 71085.34 94217.65 75872.99 40704.06 25226.64 $120,000 Tampa Baltimore 64648.19 65080.27 102273.69 57185.13 78439.89 54924.46 56870.75 30967.33 28493.69 27458.88 Total 280647.24 343894.73 282518.78 159365.33 103118.43 $80,000 $60,000 $40,000 $20,000 $- Sales Taxes Collected Product $100,000 Tax Rate 9% 7185.01 8119.64 6595.33 2774.09 1974.53 26648.60 8% 5686.83 7537.41 6069.84 3256.32 2018.13 7% 4525.37 7159.16 5490.79 3980.95 1994.56 Average tax rate Lowest tax paid across products Final revenue after deducting taxes Rounded final revenue after deducting taxes Average tax rate TBD Baton Rouge Latte 6% 17397.21 22816.21 18155.96 10011.36 5987.22 Arlington Cappuccino Macchiato E Sales by Items and City Rouge Latte Arlington Cappuccino Macchiato Tampa Espresso Baltimore Moka