Homework answers / question archive / Illustrated Excel 2016 | Module 13: SAM Project 1a Blue Creek Pottery EXCHANGING Data WITH OTHER PROGRAMS   GETTING STARTED Open the file IL_EX16_13a_FirstLastName_1

Illustrated Excel 2016 | Module 13: SAM Project 1a Blue Creek Pottery EXCHANGING Data WITH OTHER PROGRAMS   GETTING STARTED Open the file IL_EX16_13a_FirstLastName_1

MS Excel

Share With

---

Illustrated Excel 2016 | Module 13: SAM Project 1a

Blue Creek Pottery

EXCHANGING Data WITH OTHER PROGRAMS

 

GETTING STARTED

• Open the file IL_EX16_13a_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as IL_EX16_13a_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.
  • To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
    • Support_IL_EX16_13a_sales.accdb
    • Support_IL_EX16_13a_sales_by_item.txt
    • Support_IL_EX16_13a_cup.jpg
    • With the file IL_EX16_13a_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. Keeley and Devin Ahern own a pottery shop in Vermont. In addition to handcrafting pottery in the workshop, Keeley manages the store’s employees, while Devin monitors the retail sales. Devin has created files using different programs and needs your help to bring the 2020 sales data into an Excel workbook.

The employee sales data for 2020 is in an Access database. Devin asks you to bring this data into the Excel file.

Switch to the Sales worksheet. Import the data from the 2020 Sales table of the Access file Support_IL_EX16_13a_sales.accdb into the worksheet at cell A8. Import the data into your workbook as an Excel table.

1. Apply the Blue, Table Style Light 10 table style to the imported data. (Hint: Depending on your version of Office, the table style may apear as Table Style Light 10 instead.) Change the font color of cells in range A8:F8 to Dark Purple, Text 2 (4^th column, 1^st row in the Theme Colors palette).
2. Sort the records by the Sales field from Smallest to Largest. Format the range D9:F24 using the Currency number format with the $ symbol and 2 decimal places.
3. Devin also needs to calculate the totals for each person.

Add a Total Row to the table. Use AutoFit to increase the width of column F to display the total bonus amount.

1. Use the Total Row to calculate the maximum figure in the Sales column. (column D).
2. Devin wants to analyze the sales data by looking at the sales of each item in the shop.

Import data from the text file Support_IL_EX16_13a_sales_by_item.txt into the Excel workbook at cell J8. The data in the text file has headers and is delimited with tabs.

1. Format the range K9:M18 using the Currency number format with the $ symbol and 2 decimal places.
2. Adjust the width of columns K:M using AutoFit to best fit the column data.
3. Formatthe font of the range J8:M18 using the Dark Purple, Text 2 (4^th column, 1^st row in the Theme Colors palette) font color and bold formatting. Center the heading text in the range J8:M8.
4. Devin asks you to help him add the shop’s logo to the worksheet.

Insert the picture from the file Support_IL_EX16_13a_cup.jpg into the worksheet.

1. Devin asks you to improve the logo by adding a border and picture effects.

Format the image using a Beveled Matte, White picture style (2^nd column, 1^st row). Add a border using Blue, Accent2, Lighter 80% (6^th column, 2^nd row). Adjust the border weight to 3 points.

1. Add an Offset: Right picture effect to the logo (1^st column, 2^nd row from the Outer section of the Shadow gallery). Resize and reposition the image to fit in the range F1:G6.
2. Turn off the gridlines view.

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

Final Figure: Sales Worksheet