Shelly CashmanExcel 2016 | Module 5: SAM Project 1b Gulf Coast Kayak WORKING WITH MULTIPLE WORKSHEETS AND WORKBOOKS GETTING STARTED Open the FleSC_EX16_5b_FirstLastName_1

Subject:MS ExcelPrice:19.87 Bought3

Share With

Shelly CashmanExcel 2016 | Module 5: SAM Project 1b

Gulf Coast Kayak

WORKING WITH MULTIPLE WORKSHEETS AND WORKBOOKS

GETTING STARTED

Open the FleSC_EX16_5b_FirstLastName_1.xlsx, available for downloadfrom the SAM website.

Save the Fle asSC_EX16_5b_FirstLastName_2.xlsxby changing the “1” to a“2”.oIf you do not see the.xlsxFle extension in the Save As dialog box, do nottype it. The program will add the Fle extension for you automatically.

To complete this SAM Project, you will also need to download and save thefollowing data Fles from the SAM website onto your computer:oSupport_SC_EX16_5b_2019VestSurvey.xlsx

With the FleSC_EX16_5b_FirstLastName_2.xlsxstill open, ensure that yourFrst 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 newcopy from the SAM website.oWhen opening your Fle or the Graded Summary Report, you may beprompted to Update the links. SelectDon't Updatein the dialog box.

PROJECT STEP

1.Shawn Meyers is the owner of Gulf Coast Kayak. Each of his location managerssent him their 2019 school enrollment and revenue data. To make the dataeasier to understand, Shawn wants to consolidate and standardize the format ofeach location’s data.Apply theOfcetheme to the workbook.

2.Group theNaples, Sarasota,andTampaworksheets. With all three worksheetsselected, make the following formatting changes to the merged range A1:N1.

a.Change the font toArial.

b.Change the font size to18 pt.

c.Change the font color toBlue, Accent 5, Darker 25%(9thcolumn, 5throw of the Theme Colors palette).Do not ungroup the worksheets.

3.With theNaples, Sarasota,andTampaworksheets still grouped together, makethe following updates:

a.Apply theHeading 1cell style to the merged range A2:N2.

b.Apply theAccountingnumber format withzerodecimal places and$asthe symbol to the range B16:N16. (Hint: Depending on how you completethis action, the number format may appear as Custom.)c.Apply theCommanumber format withzerodecimal places to the rangeB17:N21. (Hint: Depending on how you complete this action, the numberformat may appear as Custom.)Do not ungroup the worksheets.

4.Shawn notices two typos in the worksheets and wants to Fx the issue.With theNaples, Sarasota,andTampaworksheets still grouped together, makethe following updates:

a.In cell A8, edit the cell text to readOcean(instead of Ocen).

b.In cell A9, edit the cell text to readRough-water(instead ofRoughwater).Ungroup the worksheets.

5.Now that a consistent format is applied to each location’s worksheet, Shawnwants to create a template worksheet. He’ll use a copy of this template for anynew location that he opens, rather than formatting a new worksheet.Select theTampaworksheet and create a copy of it between theTampaand theAll Locationsworksheets. Update the worksheet as described below:

a.Rename the new worksheet usingNew Locationas the name.

b.Clear the contents (but not the formatting) in the merged range A2:N2.

c.Clear the contents (but not the formatting) in the range B7:M12.6.Go to theAll Locationsworksheet. In cell B3, enter a formula using theTODAYfunction to display the current date.

7.In cell B7, enter a formula using theSUMfunction,3-D references, andgrouped worksheets to total the values in cellB7on theNaples:Tampaworksheets.Copy the formula you created in cell B7 to the range B7:M12 without copyingany cell formatting. (Hint:Use the Paste Gallery or Auto ±ill Option.)

8.Shawn created a 2-D pie chart showing how enrollment in each coursecontributed to his total revenue in 2019. Now he wants to format it to make theimportant information stand out better.Resize and reposition the 2-D pie chart (with the title “2019 Total Revenue”) sothat the upper-left corner is located within in cell C24 and the lower-right corneris located within cell L44.

9.Explode the slice of the 2-D pie chart representing the revenue from the OceanSchool by20%.10.Modify the data labels of the 2-D pie chart as described below:

a.Update the data labels to contain only theCategory Nameand thePercentagevalues.

b.Change the data label’s position toCenter.

c.Update the data label’s number format to display using thePercentagenumber format with1decimal place.

11.Add a header to theAll Locationsworksheet using the textRevenue Summaryto the center header section.

12.Using Header & Footer Elements, add a footer that displays theCurrent Datein the left footer section and theSheet Namein the center footer section.

13.Change the margins of theAll Locationsworksheet so that the left and rightmargins are set to0.3. Do not change the top or bottom margins.

14.Shawn is very concerned about safety and has conducted a study to determinehow many life vests were lost at each location last year. He wants to include thesurvey results in this spreadsheet.Switch to theVests Per Locationworksheet.Open the Support_SC_EX16_5b_2019VestSurvey.xlsx workbook, and then switchback to theVests Per Locationworksheet. Link the data to theVests PerLocationworksheet as described below:

a.In cell C3 of theVests Per Locationworksheet, create a formula withoutusing a function that contains arelativereference to cellD2of the2019Lost Vests Surveyworksheet in theSupport_SC_EX16_5b_2019VestSurvey.xlsxworkbook.

b.Copy the formula you just created in cell C3 to the range C4:C5 withoutcopying the formatting.c.Close the Support_SC_EX16_5b_2019VestSurvey.xlsx workbook and switchback to theVests Per Locationworksheet.

15.Shawn now wishes to calculate how many vests should be available at eachlocation, accounting for the percentage of lost vests from each location. Shawnwill need to use the ROUND function in his calculations, since his customerswon’t accept a fraction of a vest.In cell E3, enter a formula using theROUNDfunction to calculate the number ofVests Per Location. The formula should multiply cellB3(the max daily students)by cellD3(the total vests required), and be rounded to0decimal places. Copythe 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 yourchanges, close the document, and then exit Excel. Follow the directions on the SAMwebsite 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