Shelly Cashman Excel 2016 | Module 5: SAM Project 1b Gulf Coast Kayak Working with Multiple Worksheets and Workbooks GETTING STARTED Open the file SC_EX16_5b_FirstLastName_1

Subject:MS ExcelPrice:19.99 Bought15

Share With

Shelly Cashman Excel 2016 | Module 5: SAM Project 1b

Gulf Coast Kayak

Working with Multiple Worksheets and Workbooks

GETTING STARTED

• Open the file SC_EX16_5b_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SC_EX16_5b_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_SC_EX16_5b_2019VestSurvey.xlsx
• With the file SC_EX16_5b_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.
  • When opening your file or the Graded Summary Report, you may be prompted to Update the links. Select Don't Update in the dialog box.

 

PROJECT STEP

1. Shawn Meyers is the owner of Gulf Coast Kayak. Each of his location managers sent him their 2019 school enrollment and revenue data. To make the data easier to understand, Shawn wants to consolidate and standardize the format of each location’s data.

Apply the Office theme to the workbook.

2. Group the Naples, Sarasota, and Tampa worksheets. With all three worksheets selected, make the following formatting changes to the merged range A1:N1.
   1. Change the font to Arial.
   2. Change the font size to 18 pt.
   3. Change the font color to Blue, Accent 5, Darker 25% (9^th column, 5^th row of the Theme Colors palette).

Do not ungroup the worksheets.

3. With the Naples, Sarasota, and Tampa worksheets still grouped together, make the following updates:
   1. Apply the Heading 1 cell style to the merged range A2:N2.
   2. Apply the Accounting number format with zero decimal places and $ as the symbol to the range B16:N16. (Hint: Depending on how you complete this action, the number format may appear as Custom.)
   3. Apply the Comma number format with zero decimal places to the range B17:N21. (Hint: Depending on how you complete this action, the number format may appear as Custom.)

Do not ungroup the worksheets.

4. Shawn notices two typos in the worksheets and wants to fix the issue.

With the Naples, Sarasota, and Tampa worksheets still grouped together, make the following updates:

1. 1. In cell A8, edit the cell text to read Ocean (instead of Ocen).
   2. In cell A9, edit the cell text to read Rough-water (instead of Roughwater).

Ungroup the worksheets.

5. Now that a consistent format is applied to each location’s worksheet, Shawn wants to create a template worksheet. He’ll use a copy of this template for any new location that he opens, rather than formatting a new worksheet.

Select the Tampa worksheet and create a copy of it between the Tampa and the All Locations worksheets. Update the worksheet as described below:

5. 1. Rename the new worksheet using New Location as the name.
   2. Clear the contents (but not the formatting) in the merged range A2:N2.
   3. Clear the contents (but not the formatting) in the range B7:M12.
6. Go to the All Locations worksheet. In cell B3, enter a formula using the TODAY function to display the current date.
7. In cell B7, enter a formula using the SUM function, 3-D references, and grouped worksheets to total the values in cell B7 on the Naples:Tampa worksheets.

Copy the formula you created in cell B7 to the range B7:M12 without copying any cell formatting. (Hint: Use the Paste Gallery or Auto Fill Option.)

8. Shawn created a 2-D pie chart showing how enrollment in each course contributed to his total revenue in 2019. Now he wants to format it to make the important information stand out better.

Resize and reposition the 2-D pie chart (with the title “2019 Total Revenue”) so that the upper-left corner is located within in cell C24 and the lower-right corner is located within cell L44.

9. Explode the slice of the 2-D pie chart representing the revenue from the Ocean School by 20%.
10. Modify the data labels of the 2-D pie chart as described below:
    1. Update the data labels to contain only the Category Name and the Percentage values.
    2. Change the data label’s position to Center.
    3. Update the data label’s number format to display using the Percentage number format with 1 decimal place.
11. Add a header to the All Locations worksheet using the text Revenue Summary to the center header section.
12. Using Header & Footer Elements, add a footer that displays the Current Date in the left footer section and the Sheet Name in the center footer section.
13. Change the margins of the All Locations worksheet so that the left and right margins are set to 0.3. Do not change the top or bottom margins.
14. Shawn is very concerned about safety and has conducted a study to determine how many life vests were lost at each location last year. He wants to include the survey results in this spreadsheet.

Switch to the Vests Per Location worksheet.

Open the Support_SC_EX16_5b_2019VestSurvey.xlsx workbook, and then switch back to the Vests Per Location worksheet. Link the data to the Vests Per Location worksheet as described below:

14. 1. In cell C3 of the Vests Per Location worksheet, create a formula without using a function that contains a relative reference to cell D2 of the 2019 Lost Vests Survey worksheet in the Support_SC_EX16_5b_2019VestSurvey.xlsx workbook.
    2. Copy the formula you just created in cell C3 to the range C4:C5 without copying the formatting.
    3. Close the Support_SC_EX16_5b_2019VestSurvey.xlsx workbook and switch back to the Vests Per Location worksheet.
15. Shawn now wishes to calculate how many vests should be available at each location, accounting for the percentage of lost vests from each location. Shawn will need to use the ROUND function in his calculations, since his customers won’t accept a fraction of a vest.

In cell E3, enter a formula using the ROUND function to calculate the number of Vests Per Location. The formula should multiply cell B3 (the max daily students) by cell D3 (the total vests required), and be rounded to 0 decimal places. Copy the formula, but not the cell formatting, from cell E3 to the range E4:E5. (Hint: Use the Paste gallery.)

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

Final Figure 1: Naples Worksheet

Final Figure 2: Sarasota Worksheet

Final Figure 3: Tampa Worksheet

Final Figure 4: New Location Worksheet

Final Figure 5: All Locations Worksheet

Final Figure 6: Vests Per Location Worksheet