Homework answers / question archive / Illustrated Excel 2016 | Module 2: SAM Project 1a Camile’s Electronics working with formulas and functions   GETTING STARTED Open the file IL_EX16_2a_FirstLastName_1

Illustrated Excel 2016 | Module 2: SAM Project 1a Camile’s Electronics working with formulas and functions   GETTING STARTED Open the file IL_EX16_2a_FirstLastName_1

MS Excel

Share With

---

Illustrated Excel 2016 | Module 2: SAM Project 1a

Camile’s Electronics

working with formulas and functions

 

GETTING STARTED

• Open the file IL_EX16_2a_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as IL_EX16_2a_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • 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_2a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
    • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

 

PROJECT STEPS

1. Camile owns a franchise of electronics stores with locations in four cities. She wants to analyze the sales of and the sales taxes paid for her store’s most popular products.

Go to the Sales Summary worksheet.

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

1. 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.

1. Copy the contents of cell F5 to cell F14.
2. In cell E15, create a formula that multiplies the value in cell E6 (total sales figure for TVs 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 Camile’s Baltimore branch.

1. Using the Fill Handle, copy the formula in cell B20 to the range C20:E20.
2. Camile 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).

1. In cell D22, create a formula using the AVERAGE function to identify the average tax rate for all cities, based on the range B14:E14.
2. Camile 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 amount collected across products (without including a period at the end of the phrase).

1. In cell D23, create a formula using the MAX function to identify the highest amount of sales tax collected, based on the range F15:F19.
2. In cell D24, create a formula using the MIN function to identify the lowest amount of sales tax collected, based on the range F15:F19.
3. Camile is interested in knowing how much revenue she generated from these popular items in total after deducting taxes.

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

1. In keeping with best practices, Camile wants to round her final revenue to the nearest dollar.

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

1. Because Camile 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.
2. Cell A3 contains a note indicating this worksheet 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, and exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Sales Summary Worksheet