Excel Modify 3

SC_EX16_5b_JesusMojica_Report_2.xlsx

Home >Information Systems homework help >Excel Modify 3

Graded Summary Report

		Shelly Cashman Excel 2016 Module 5: SAM Project 1b
		Jesus Mojica
		SUBMISSION #2 \| SCORE IS: 55 OUT OF 100	GE ver. 6.39
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.		6/6
		Change the workbook theme.
2.	Group the Naples, Sarasota, and Tampa worksheets. With all three worksheets selected, make the following formatting changes to the merged range A1:N1. a. Change the font to Arial. b. Change the font size to 18 pt. c. Change the font color to Blue, Accent 5, Darker 25% (9th column, 5th row of the Theme Colors palette). Do not ungroup the worksheets.		0/6
		Change the font of cell contents.
		In the Naples, Sarasota, and Tampa worksheets, the range A1:N1 should be formatted using the Arial font.
		Change the font size of a range of cells.
		In the Naples, Sarasota, and Tampa worksheets, the range A1:N1 should be formatted using an 18 pt. font size.
		Change the font color of cell contents.
		In the Naples, Sarasota, and Tampa worksheets, the range A1:N1 should be formatted using Blue, Accent 5, Darker 25% font color.
3.	With the Naples, Sarasota, and Tampa worksheets still grouped together, make the following updates: a. Apply the Heading 1 cell style to the merged range A2:N2. b. 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.) c. 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.		6/6
		Apply a cell style to a range of cells.
		Set the number format for a range of cells.
		Set the number format for a range of cells.
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: a. In cell A8, edit the cell text to read Ocean (instead of Ocen). b. In cell A9, edit the cell text to read Rough-water (instead of Roughwater). Ungroup the worksheets.		6/6
		Update the text in a cell.
		Update the text in a cell.
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: a. Rename the new worksheet New Location. 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/6
		Copy a worksheet.
		Clear cell contents.
		Clear cell contents.
6.	Go to the All Locations worksheet. In cell B3, enter a formula using the TODAY function to display the current date.		7/7
		Create a formula using a function.
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.)		4/7
		Create a formula using a function.
		Copy a formula into a range.
		In the All Locations worksheet, the formatting from cell B7 should not be copied to the range B7:M12.
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.		7/7
		Resize and reposition a chart.
9.	Explode the slice of the 2-D pie chart representing the revenue from the Ocean School by 20%.		0/7
		Explode a data point in a chart.
		In the All Locations worksheet, the data point should be exploded by 20%.
10.	Modify the data labels of the 2-D pie chart as described below: a. Update the data labels to contain only the Category Name and the Percentage values. b. Change the data label's position to Center. c. Update the data label's number format to display using the Percentage number format with 1 decimal place.		5/7
		Change the data label options.
		Change the position of the data labels.
		In the All Locations worksheet, the position of the 2-D pie chart's data labels should be set to Center.
		Change the number format of a data label.
11.	Add a header to the All Locations worksheet using the text Revenue Summary to the center header section.		0/7
		Add a header to a worksheet.
		The All Locations worksheet should contain a header that displays the text "Revenue Summary" in the center section.
12.	Using Header and Footer Elements, add a footer that displays the Current Date in the left footer section and the Sheet Name in the center footer section.		0/7
		Add a footer to a worksheet.
		The All Locations worksheet should contain a footer that uses footer elements to display the current date in the left section.
		Add a footer to a worksheet.
		The All Locations worksheet should contain a footer that uses footer elements to display the worksheet name in the center 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.		0/7
		Set custom margins for a worksheet.
		The left and right margins of the All Locations worksheet should be set to 0.3.
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: a. 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. b. Copy the formula you just created in cell C3 to the range C4:C5 without copying the formatting.		4/7
		Create a formula.
		Copy a formula into a range.
		In the Vests Per Location worksheet, the formatting from cell C3 should not be copied to the range C4:C5.
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.)		4/7
		Create a formula.
		Copy a formula into a range.
		In the Vests Per Location worksheet, the formatting from cell E3 should not be copied to the range E4:E5.

Documentation

	Shelly Cashman Excel 2016 \| Module 5: SAM Project 1b
	Gulf Coast Kayak
	WORKING WITH MULTIPLE WORKSHEETS AND WORKBOOKS
Author:	Jesus Mojica
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.

Naples

Gulf Coast Kayak Grading Engine: Grading Error: Step 2: In the Naples, Sarasota, and Tampa worksheets, the range A1:N1 should be formatted using the Arial font. Step 2: In the Naples, Sarasota, and Tampa worksheets, the range A1:N1 should be formatted using an 18 pt. font size. Step 2: In the Naples, Sarasota, and Tampa worksheets, the range A1:N1 should be formatted using Blue, Accent 5, Darker 25% font color.
Naples
2019 Monthly Enrollment
School	January	February	March	April	May	June	July	August	September	October	November	December	Total
Beginner	8	9	19	20	22	20	19	9	18	8	7	9	168
Ocean	9	5	15	25	25	25	15	5	25	9	5	5	168
Rough-water	11	6	26	31	20	31	26	6	20	11	6	6	200
Kids	7	3	30	25	27	25	30	3	23	7	3	9	192
Performance	5	8	29	15	15	15	29	8	19	5	8	11	167
Private	9	11	19	18	17	18	19	11	18	9	11	10	170
2019 Monthly Revenue
School	January	February	March	April	May	June	July	August	September	October	November	December	Total
Beginner	$ 440	$ 495	$ 1,045	$ 1,100	$ 1,210	$ 1,100	$ 1,045	$ 495	$ 990	$ 440	$ 385	$ 495	$ 9,240
Ocean	495	275	825	1,375	1,375	1,375	825	275	1,375	495	275	275	9,240
Rough-water	605	330	1,430	1,705	1,100	1,705	1,430	330	1,100	605	330	330	11,000
Kids	245	105	1,050	875	945	875	1,050	105	805	245	105	315	6,720
Performance	300	480	1,740	900	900	900	1,740	480	1,140	300	480	660	10,020
Private	675	825	1,425	1,350	1,275	1,350	1,425	825	1,350	675	825	750	12,750
Total	$ 2,760	$ 2,510	$ 7,515	$ 7,305	$ 6,805	$ 7,305	$ 7,515	$ 2,510	$ 6,760	$ 2,760	$ 2,400	$ 2,825	$ 58,970

Sarasota

Gulf Coast Kayak
Sarasota
2019 Monthly Enrollment
School	January	February	March	April	May	June	July	August	September	October	November	December	Total
Beginner	5	18	19	25	22	18	19	14	18	8	7	5	178
Ocean	7	15	12	25	25	25	15	5	25	9	5	8	176
Rough-water	9	8	26	31	15	31	26	6	20	11	6	10	199
Kids	11	3	30	25	27	25	30	3	23	7	7	8	199
Performance	9	8	29	19	15	15	29	8	19	13	8	12	184
Private	2	11	19	18	17	18	19	11	18	9	11	10	163
2019 Monthly Revenue
School	January	February	March	April	May	June	July	August	September	October	November	December	Total
Beginner	$ 275	$ 990	$ 1,045	$ 1,375	$ 1,210	$ 990	$ 1,045	$ 770	$ 990	$ 440	$ 385	$ 275	$ 9,790
Ocean	385	825	660	1,375	1,375	1,375	825	275	1,375	495	275	440	9,680
Rough-water	495	440	1,430	1,705	825	1,705	1,430	330	1,100	605	330	550	10,945
Kids	385	105	1,050	875	945	875	1,050	105	805	245	245	280	6,965
Performance	540	480	1,740	1,140	900	900	1,740	480	1,140	780	480	720	11,040
Private	150	825	1,425	1,350	1,275	1,350	1,425	825	1,350	675	825	750	12,225
Total	$ 2,230	$ 3,665	$ 7,350	$ 7,820	$ 6,530	$ 7,195	$ 7,515	$ 2,785	$ 6,760	$ 3,240	$ 2,540	$ 3,015	$ 60,645

Tampa

Gulf Coast Kayak
Tampa
2019 Monthly Enrollment
School	January	February	March	April	May	June	July	August	September	October	November	December	Total
Beginner	10	12	19	25	22	20	19	9	18	8	11	14	187
Ocean	9	5	15	25	25	25	15	5	19	9	5	13	170
Rough-water	11	6	26	31	20	27	26	8	20	11	6	6	198
Kids	7	10	28	25	18	25	30	3	23	7	3	9	188
Performance	5	8	29	15	15	15	29	8	19	5	8	11	167
Private	9	11	19	23	17	18	19	11	18	9	11	10	175
2019 Monthly Revenue
School	January	February	March	April	May	June	July	August	September	October	November	December	Total
Beginner	$ 550	$ 660	$ 1,045	$ 1,375	$ 1,210	$ 1,100	$ 1,045	$ 495	$ 990	$ 440	$ 605	$ 770	$ 10,285
Ocean	495	275	825	1,375	1,375	1,375	825	275	1,045	495	275	715	9,350
Rough-water	605	330	1,430	1,705	1,100	1,485	1,430	440	1,100	605	330	330	10,890
Kids	245	350	980	875	630	875	1,050	105	805	245	105	315	6,580
Performance	300	480	1,740	900	900	900	1,740	480	1,140	300	480	660	10,020
Private	675	825	1,425	1,725	1,275	1,350	1,425	825	1,350	675	825	750	13,125
Total	$ 2,870	$ 2,920	$ 7,445	$ 7,955	$ 6,490	$ 7,085	$ 7,515	$ 2,620	$ 6,430	$ 2,760	$ 2,620	$ 3,540	$ 60,250

New Location

Gulf Coast Kayak
2019 Monthly Enrollment
School	January	February	March	April	May	June	July	August	September	October	November	December	Total
													0
													0
													0
													0
													0
													0
2019 Monthly Revenue
School	January	February	March	April	May	June	July	August	September	October	November	December	Total
Beginner	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
Ocean	-	-	-	-	-	-	-	-	-	-	-	-	-
Rough-water	-	-	-	-	-	-	-	-	-	-	-	-	-
Kids	-	-	-	-	-	-	-	-	-	-	-	-	-
Performance	-	-	-	-	-	-	-	-	-	-	-	-	-
Private	-	-	-	-	-	-	-	-	-	-	-	-	-
Total	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0

All Locations

Gulf Coast Kayak
All Locations
Date Generated	4/2/18
2019 Monthly Enrollment
School	January	February	March	April	May	June	July	August	September	October	November	December	Total
Beginner	23	39	57	70	66	58	57	32	54	24	25	28	533
Ocean	25 Grading Engine: Grading Error: Step 7: In the All Locations worksheet, the formatting from cell B7 should not be copied to the range B7:M12.	25	42	75	75	75	45	15	69	27	15	26	514
Rough-water	31	20	78	93	55	89	78	20	60	33	18	22	597
Kids	25	16	88	75	72	75	90	9	69	21	13	26	579
Performance	19	24	87	49	45	45	87	24	57	23	24	34	518
Private	20	33	57	59	51	54	57	33	54	27	33	30	508
2019 Monthly Revenue
School	January	February	March	April	May	June	July	August	September	October	November	December	Total
Beginner	$ 1,265	$ 2,145	$ 3,135	$ 3,850	$ 3,630	$ 3,190	$ 3,135	$ 1,760	$ 2,970	$ 1,320	$ 1,375	$ 1,540	$ 29,315
Ocean	1,375	1,375	2,310	4,125	4,125	4,125	2,475	825	3,795	1,485	825	1,430	28,270
Rough-water	1,705	1,100	4,290	5,115	3,025	4,895	4,290	1,100	3,300	1,815	990	1,210	32,835
Kids	875	560	3,080	2,625	2,520	2,625	3,150	315	2,415	735	455	910	20,265
Performance	1,140	1,440	5,220	2,940	2,700	2,700	5,220	1,440	3,420	1,380	1,440	2,040	31,080
Private	1,500	2,475	4,275	4,425	3,825	4,050	4,275	2,475	4,050	2,025	2,475	2,250	38,100
Total	$ 7,860	$ 9,095	$ 22,310	$ 23,080	$ 19,825	$ 21,585	$ 22,545	$ 7,915	$ 19,950	$ 8,760	$ 7,560	$ 9,380	$ 179,865
											Grading Engine: Grading Error: Step 9: In the All Locations worksheet, the data point should be exploded by 20%. Step 10: In the All Locations worksheet, the position of the 2-D pie chart's data labels should be set to Center.
Grading Engine: Grading Error: Step 11: The All Locations worksheet should contain a header that displays the text "Revenue Summary" in the center section. Step 12: The All Locations worksheet should contain a footer that uses footer elements to display the current date in the left section. Step 12: The All Locations worksheet should contain a footer that uses footer elements to display the worksheet name in the center section. Step 13: The left and right margins of the All Locations worksheet should be set to 0.3.	Grading Engine: Grading Error: Step 7: In the All Locations worksheet, the formatting from cell B7 should not be copied to the range B7:M12.

2019 Total Revenue

Beginner Ocean Rough-water Kids Performance Private 29315 28270 32835 20265 31080 38100

Vests Per Location

Gulf Coast Kayak
Location	Max Daily Students	Lost Vests (%)	Total Vests Required (%)	Vests Per Location
Naples	138	11%	111%	152
Sarasota	143	9% Grading Engine: Grading Error: Step 14: In the Vests Per Location worksheet, the formatting from cell C3 should not be copied to the range C4:C5.	109%	156 Grading Engine: Grading Error: Step 15: In the Vests Per Location worksheet, the formatting from cell E3 should not be copied to the range E4:E5.
Tampa	144	9% Grading Engine: Grading Error: Step 14: In the Vests Per Location worksheet, the formatting from cell C3 should not be copied to the range C4:C5.		Grading Engine: Grading Error: Step 15: In the Vests Per Location worksheet, the formatting from cell E3 should not be copied to the range E4:E5.	109%	156 Grading Engine: Grading Error: Step 15: In the Vests Per Location worksheet, the formatting from cell E3 should not be copied to the range E4:E5.
Ocean
Rough-water