Question

1. Muna Hsu is an assistant hospitality manager at the Icehouse Arena, home to the Springfield Sharks (the town’s amateur hockey team.) Muna is summarizing two years of sales data from her food vendors. Since each stand supports a different section of the arena, Muna will need to coordinate and consolidate sales data on multiple worksheets. Break the external link that exists in the worksheet, so that the formulas in cells B4, B5, B6, and B7 of the Vendor Information worksheet are replaced with static values. Then switch to the Vendor Information worksheet. 2. Muna wants to update the links in her workbook, so that she’ll be able to quickly pull up the Vendor pricing plans for the 2019-2020 season. In cell B9, create a hyperlink to the Icehouse Arena Vendor Refreshment Price listing for 2019-2020 season as described below: a. The hyperlink should link to the Support_NP_EX16_6a_VendorPrices1920.docx file, available on the SAM website. b. The hyperlink should use 2019-2020 Vendor Refreshment Prices as the text to display. c. The hyperlink should use Click here to view the Vendor Refreshment Price listing for the 2019-2020 season. as the ScreenTip text. 3. Edit the hyperlink Icehouse Arena website in cell B10 as described below: a. The hyperlink should use Icehouse Arena Vendor Website as the display text. b. The hyperlink should use Click here to access the Icehouse Arena Vendor website. as the ScreenTip text. 4. Muna now wishes to give a consistent look and feel to the worksheets submitted by each of the vendor stands. Group the North, East, and South worksheets together and then make the following formatting updates: a. Change the font size in the merged range A1:I1 to 28 pt. b. Apply the Heading 3 cell style to the merged range A2:I2. c. Bold the values in the range A5:A10. d. Apply the Accounting number format with zero decimal places and $ as the symbol to the range B5:I10. (Hint: Depending how you complete this step, the number format for this range may display as Custom.) Do not ungroup the worksheets. 5. With the North, East, and South worksheets still grouped, update the worksheet as described below: a. In cell A7, edit the text to read Shark Bites (instead of Pepperoni Bite). b. In cell A9, edit the text to read Pizza Frenzy (instead of Pizza). Do not ungroup the worksheets. 6. With the North, East, and South worksheets still grouped, select the range B11:H11. Using the AutoSum button, enter a formula using the SUM function that totals the sales for each month of the hockey season (shown in the range B5:H10). Ungroup the worksheets and then check to confirm that the formatting and formulas from steps 4-6 are present in all three worksheets. 7. Muna wants to create a copy of the formatted South worksheet to use for the West section of the arena’s sales data. Create a copy of the South worksheet between the South worksheet and the Consolidated Sales worksheet then update the worksheet as described below: a. Rename the copied worksheet using West as the name. b. In the merged range A2:I2, edit the text to read Feeding Frenzy (instead of Shark Tooth). c. Clear the contents of the range B5:H10. 8. Muna now wishes to consolidate the data from each of the vendor stands. Switch to the Consolidated Sales worksheet. In cell A5, enter a formula without using a function that references cell A5 in the North worksheet. Copy the formula from cell A5 to the range A6:A10 without copying the formatting. 9. In cell B5, enter a formula using the SUM function, 3-D references, and grouped worksheets that totals the values from cell B5 in the North:West worksheets. Copy the formula from cell B5 to the range B6:B10 without copying the formatting. Then copy the formulas and the formatting from the range B5:B10 to the range C5:H10. 10. Muna wants to compare the sales from the 2019-2020 season to the 2018-2019 season and needs to include the missing data. Open the support file Support_NP_EX16_6a_1819VendorSales.xlsx. Switch back to the NP_EX16_6a_FirstLastName_2.xlsx workbook and go to the Consolidated Sales worksheet. Create external references in the Consolidated Sales worksheet to the sales information found in the Support_NP_EX16_6a_1819VendorSales.xlsx workbook as described below: a. Using external cell references, link cell J5 in the Consolidated Sales worksheet to cell H5 in the Consolidated Sales 2018-2019 worksheet in the Support_NP_EX16_6a_1819VendorSales.xlsx workbook. b. Using external cell references, link cell J6 in the Consolidated Sales worksheet to cell H6 in the Consolidated Sales 2018-2019 worksheet in the Support_NP_EX16_6a_1819VendorSales.xlsx workbook. c. Using external cell references, link cell J7 in the Consolidated Sales worksheet to cell H7 in the Consolidated Sales 2018-2019 worksheet in the Support_NP_EX16_6a_1819VendorSales.xlsx workbook. d. Using external cell references, link cell J8 in the Consolidated Sales worksheet to cell H8 in the Consolidated Sales 2018-2019 worksheet in the Support_NP_EX16_6a_1819VendorSales.xlsx workbook. e. Using external cell references, link cell J9 in the Consolidated Sales worksheet to cell H9 in the Consolidated Sales 2018-2019 worksheet in the Support_NP_EX16_6a_1819VendorSales.xlsx workbook. f. Using external cell references, link cell J10 in the Consolidated Sales worksheet to cell H10 in the Consolidated Sales 2018-2019 worksheet in the Support_NP_EX16_6a_1819VendorSales.xlsx workbook. g. Do not break the links.

vendor information

Springfield Sharks
	Stand Manager	Stand Name	Extension
North	Loki Mylosky	Fins' Wake	X642
East	Sara Ryons	Final Bite	X643
South	Jacob Caron	Shark Tooth	X678
West	Kevin Staszowski	Feeding Frenzy	x694

North file

Springfield Sharks
Fins' Wake
	October	November	December	January	February	March	April	Total
Shark Dog	45750	30960	36045	60435	52470	36270	43913	305843
Nacho Wave	24,775	32,490	40,740	37,620	40,830	34,050	19,275	229,780
Pepperoni Bite	31,195	67,014	30,447	41,922	44,931	50,286	24,608	290,403
Popcorn	20,983	61,809	81,180	74,778	28,050	80,355	51,425	398,580
Pizza	29,970	29,448	42,156	26,172	48,420	42,876	15,060	234,102
Soda	17,160	22,032	17,460	22,032	23,346	10,836	33,765	146,631
Total								$                             -

East file

Springfield Sharks
Final Bite
	October	November	December	January	February	March	April	Total
Shark Dog	54637.5	47280	21750	34560	85470	24030	45750	313477.5
Nacho Wave	36,125	51,885	54,495	22,410	19,800	52,335	30,550	267,600
Pepperoni Bites	40,078	16,218	37,944	7,599	60,435	75,327	31,153	268,753
Popcorn	41,718	27,456	35,880	17,355	17,667	18,837	27,005	185,918
Pizza	22,830	65,880	33,480	37,440	26,520	85,140	36,780	308,070
Soda	24,345	13,650	4,980	42,480	22,500	37,380	21,585	166,920
Total								$                             -

South

Springfield Sharks
Shark Tooth
	October	November	December	January	February	March	April	Total
Shark Dog	41625	94170	88860	53700	93270	40290	51562.5	463477.5
Nacho Wave	24,625	46,800	30,015	15,750	35,865	26,550	16,150	195,755
Pepperoni Bites	31,535	55,335	14,382	67,881	45,645	74,256	61,370	350,404
Popcorn	16,995	37,050	4,719	33,852	46,917	38,025	45,678	223,236
Pizza	32,460	79,620	81,060	8,160	47,940	61,800	40,110	351,150
Soda	34,860	4,170	39,420	26,250	29,400	7,020	19,380	160,500
Total								$                             -

Consolidated sales

Springfield Sharks
2018-2020 Consolidated Sales
	October	November	December	January	February	March	April	Total	2018-2019 Totals
								$                          -
								-
								-
								-
								-
								-
Total	$                             -	$                             -	$                             -	$                             -	$                             -	$                             -	$                             -	$                             -	$                              -

Suppoort NPX16 6A 1819VENDORSALES.xls

Consolidated Sales 2018 2019

Springfield Sharks
2018-2019 Consolidated Sales
	October	November	December	January	February	March-April	Total
Shark Dog	$       176,096	$       215,513	$       183,319	$       185,869	$       289,013	$       302,269	$         1,352,077
Nacho Wave	107,762	163,969	156,563	94,725	120,619	223,638	867,274
Pepperoni Bite	133,650	173,209	103,466	146,753	188,764	396,249	1,142,090
Popcorn	102,010	157,894	152,224	157,481	115,793	326,656	1,012,056
Pizza	109,985	218,685	195,870	89,715	153,600	352,208	1,120,063
Soda	97,747	49,815	77,325	113,453	94,058	162,458	594,855
Total	$       727,249	$      979,084	$       868,766	$       787,995	$      961,845	$   1,763,476	$         6,088,415

support NP EX16 vendorprices1920.docx

Springfield Sharks
Vendor Prices 2019 – 2020
	Fins’ Wake		Final Bite			Shark Tooth		Feeding Frenzy
Shark Dog	$5.99		$6.50			$5.99		$6.50
Nacho Wave	$4.99		$4.50			$5.50		$4.50
Shark Bites	$6.50		$5.99			$5.99		$6.50
Popcorn	$3.99		$4.99			$4.50		$3.50
Pizza Frenzy	$5.99		$6.50			$4.99		$4.50
Soda	$4.50		$4.99			$4.50		$5.25

Answer 1

Muna Hsu is an assistant hospitality manager at the Icehouse Arena, home to the Springfield Sharks (the town’s amateur hockey team.) Muna is summarizing two years of sales data from her food vendors. Since each stand supports a different section of the arena, Muna will need to coordinate and consolidate sales data on multiple worksheets. Break the external link that exists in the worksheet, so that the formulas in cells B4, B5, B6, and B7 of the Vendor Information worksheet are replaced with static values. Then switch to the Vendor Information worksheet. 2. Muna wants to update the links in her workbook, so that she’ll be able to quickly pull up the Vendor pricing plans for the 2019-2020 season. In cell B9, create a hyperlink to the Icehouse Arena Vendor Refreshment Price listing for 2019-2020 season as described below: a. The hyperlink should link to the Support_NP_EX16_6a_VendorPrices1920.docx file, available on the SAM website. b. The hyperlink should use 2019-2020 Vendor Refreshment Prices as the text to display. c. The hyperlink should use Click here to view the Vendor Refreshment Price listing for the 2019-2020 season. as the ScreenTip text. 3. Edit the hyperlink Icehouse Arena website in cell B10 as described below: a. The hyperlink should use Icehouse Arena Vendor Website as the display text. b. The hyperlink should use Click here to access the Icehouse Arena Vendor website. as the ScreenTip text. 4. Muna now wishes to give a consistent look and feel to the worksheets submitted by each of the vendor stands. Group the North, East, and South worksheets together and then make the following formatting updates: a. Change the font size in the merged range A1:I1 to 28 pt. b. Apply the Heading 3 cell style to the merged range A2:I2. c. Bold the values in the range A5:A10. d. Apply the Accounting number format with zero decimal places and $ as the symbol to the range B5:I10. (Hint: Depending how you complete this step, the number format for this range may display as Custom.) Do not ungroup the worksheets. 5. With the North, East, and South worksheets still grouped, update the worksheet as described below: a. In cell A7, edit the text to read Shark Bites (instead of Pepperoni Bite). b. In cell A9, edit the text to read Pizza Frenzy (instead of Pizza). Do not ungroup the worksheets. 6. With the North, East, and South worksheets still grouped, select the range B11:H11. Using the AutoSum button, enter a formula using the SUM function that totals the sales for each month of the hockey season (shown in the range B5:H10). Ungroup the worksheets and then check to confirm that the formatting and formulas from steps 4-6 are present in all three worksheets. 7. Muna wants to create a copy of the formatted South worksheet to use for the West section of the arena’s sales data. Create a copy of the South worksheet between the South worksheet and the Consolidated Sales worksheet then update the worksheet as described below: a. Rename the copied worksheet using West as the name. b. In the merged range A2:I2, edit the text to read Feeding Frenzy (instead of Shark Tooth). c. Clear the contents of the range B5:H10. 8. Muna now wishes to consolidate the data from each of the vendor stands. Switch to the Consolidated Sales worksheet. In cell A5, enter a formula without using a function that references cell A5 in the North worksheet. Copy the formula from cell A5 to the range A6:A10 without copying the formatting. 9. In cell B5, enter a formula using the SUM function, 3-D references, and grouped worksheets that totals the values from cell B5 in the North:West worksheets. Copy the formula from cell B5 to the range B6:B10 without copying the formatting. Then copy the formulas and the formatting from the range B5:B10 to the range C5:H10. 10. Muna wants to compare the sales from the 2019-2020 season to the 2018-2019 season and needs to include the missing data. Open the support file Support_NP_EX16_6a_1819VendorSales.xlsx. Switch back to the NP_EX16_6a_FirstLastName_2.xlsx workbook and go to the Consolidated Sales worksheet. Create external references in the Consolidated Sales worksheet to the sales information found in the Support_NP_EX16_6a_1819VendorSales.xlsx workbook as described below: a. Using external cell references, link cell J5 in the Consolidated Sales worksheet to cell H5 in the Consolidated Sales 2018-2019 worksheet in the Support_NP_EX16_6a_1819VendorSales.xlsx workbook. b. Using external cell references, link cell J6 in the Consolidated Sales worksheet to cell H6 in the Consolidated Sales 2018-2019 worksheet in the Support_NP_EX16_6a_1819VendorSales.xlsx workbook. c. Using external cell references, link cell J7 in the Consolidated Sales worksheet to cell H7 in the Consolidated Sales 2018-2019 worksheet in the Support_NP_EX16_6a_1819VendorSales.xlsx workbook. d. Using external cell references, link cell J8 in the Consolidated Sales worksheet to cell H8 in the Consolidated Sales 2018-2019 worksheet in the Support_NP_EX16_6a_1819VendorSales.xlsx workbook. e. Using external cell references, link cell J9 in the Consolidated Sales worksheet to cell H9 in the Consolidated Sales 2018-2019 worksheet in the Support_NP_EX16_6a_1819VendorSales.xlsx workbook. f. Using external cell references, link cell J10 in the Consolidated Sales worksheet to cell H10 in the Consolidated Sales 2018-2019 worksheet in the Support_NP_EX16_6a_1819VendorSales.xlsx workbook. g. Do not break the links.

vendor information

Springfield Sharks
	Stand Manager	Stand Name	Extension
North	Loki Mylosky	Fins' Wake	X642
East	Sara Ryons	Final Bite	X643
South	Jacob Caron	Shark Tooth	X678
West	Kevin Staszowski	Feeding Frenzy	x694

North file

Springfield Sharks
Fins' Wake
	October	November	December	January	February	March	April	Total
Shark Dog	45750	30960	36045	60435	52470	36270	43913	305843
Nacho Wave	24,775	32,490	40,740	37,620	40,830	34,050	19,275	229,780
Pepperoni Bite	31,195	67,014	30,447	41,922	44,931	50,286	24,608	290,403
Popcorn	20,983	61,809	81,180	74,778	28,050	80,355	51,425	398,580
Pizza	29,970	29,448	42,156	26,172	48,420	42,876	15,060	234,102
Soda	17,160	22,032	17,460	22,032	23,346	10,836	33,765	146,631
Total								$                             -

East file

Springfield Sharks
Final Bite
	October	November	December	January	February	March	April	Total
Shark Dog	54637.5	47280	21750	34560	85470	24030	45750	313477.5
Nacho Wave	36,125	51,885	54,495	22,410	19,800	52,335	30,550	267,600
Pepperoni Bites	40,078	16,218	37,944	7,599	60,435	75,327	31,153	268,753
Popcorn	41,718	27,456	35,880	17,355	17,667	18,837	27,005	185,918
Pizza	22,830	65,880	33,480	37,440	26,520	85,140	36,780	308,070
Soda	24,345	13,650	4,980	42,480	22,500	37,380	21,585	166,920
Total								$                             -

South

Springfield Sharks