Need this in given time ...................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................

New Perspectives Excel 2016 | Module 6: SAM Project 1a Springfield Sharks MANAGING MULTIPLE WORKSHEETS AND WORKBOOKS GETTING STARTED Open the file NP_EX16_6a_FirstLastName_1.xlsx, available for download from the SAM website. Save the file as NP_EX16_6a_FirstLastName_2.xlsx by changing the \"1\" to a \"2\". o 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: o Support_NP_EX16_6a_1819VendorSales.xlsx o Support_NP_EX16_6a_VendorPrices1920.docx With the file NP_EX16_6a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. o If cell B6 does not display your name, delete the file and download a new copy from the SAM website. PROJECT STEP 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. New Perspectives Excel 2016 | Module 6: SAM Project 1a 3. 4. 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. 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: 8. 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. 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. New Perspectives Excel 2016 | Module 6: SAM Project 1a 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. Close the Support_NP_EX16_6a_1819VendorSales.xlsx workbook. 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. New Perspectives Excel 2016 | Module 6: SAM Project 1a Final Figure 1: Vendor Information Worksheet Final Figure 2: North Worksheet Final Figure 3: East Worksheet New Perspectives Excel 2016 | Module 6: SAM Project 1a Final Figure 4: South Worksheet Final Figure 5: West Worksheet Final Figure 6: Consolidated Sales Worksheet Author: 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. New Perspectives Excel 2016 | Module 6: SAM Project 1a Springfield Sharks MANAGING MULTIPLE WORKSHEETS AND WORKBOOKS Meshal TAHA ot edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. Springfield Sharks North East South West Stand Manager Loki Mylosky Sara Ryons Jacob Caron Kevin Staszowski Icehouse Arena Stand Name Fins' Wake Final Bite Shark Tooth Feeding Frenzy Extension X642 X643 X678 x694 Springfield Sharks Fins' Wake Shark Dog Nacho Wave Pepperoni Bite Popcorn Pizza Soda Total October 45750 24,775 31,195 20,983 29,970 17,160 November 30960 32,490 67,014 61,809 29,448 22,032 December 36045 40,740 30,447 81,180 42,156 17,460 January 60435 37,620 41,922 74,778 26,172 22,032 eld Sharks Wake February 52470 40,830 44,931 28,050 48,420 23,346 March 36270 34,050 50,286 80,355 42,876 10,836 April 43913 19,275 24,608 51,425 15,060 33,765 $ Total 305843 229,780 290,403 398,580 234,102 146,631 - Springfield Sharks Final Bite Shark Dog Nacho Wave Pepperoni Bites Popcorn Pizza Soda Total October 54637.5 36,125 40,078 41,718 22,830 24,345 November 47280 51,885 16,218 27,456 65,880 13,650 December 21750 54,495 37,944 35,880 33,480 4,980 January 34560 22,410 7,599 17,355 37,440 42,480 eld Sharks al Bite February 85470 19,800 60,435 17,667 26,520 22,500 March 24030 52,335 75,327 18,837 85,140 37,380 April 45750 30,550 31,153 27,005 36,780 21,585 $ Total 313477.5 267,600 268,753 185,918 308,070 166,920 - Springfield Sharks Shark Tooth Shark Dog Nacho Wave Pepperoni Bites Popcorn Pizza Soda Total October 41625 24,625 31,535 16,995 32,460 34,860 November 94170 46,800 55,335 37,050 79,620 4,170 December 88860 30,015 14,382 4,719 81,060 39,420 January 53700 15,750 67,881 33,852 8,160 26,250 eld Sharks k Tooth February 93270 35,865 45,645 46,917 47,940 29,400 March 40290 26,550 74,256 38,025 61,800 7,020 April 51562.5 16,150 61,370 45,678 40,110 19,380 $ Total 463477.5 195,755 350,404 223,236 351,150 160,500 - Springfield Sharks 2018-2020 Consolidated Sales October Total $ November - $ December - $ January - $ - eld Sharks nsolidated Sales February March April Total $ $ - $ - $ - $ 2018-2019 Totals - $ - Springfield Sharks 2018-2019 Consolidated Sales Shark Dog Nacho Wave Pepperoni Bite Popcorn Pizza Soda Total October November December January February $ 176,096 $ 215,513 $ 183,319 $ 185,869 $ 289,013 107,762 163,969 156,563 94,725 120,619 133,650 173,209 103,466 146,753 188,764 102,010 157,894 152,224 157,481 115,793 109,985 218,685 195,870 89,715 153,600 97,747 49,815 77,325 113,453 94,058 $ 727,249 $ 979,084 $ 868,766 $ 787,995 $ 961,845 rks March-April $ 302,269 $ 223,638 396,249 326,656 352,208 162,458 $ 1,763,476 $ Total 1,352,077 867,274 1,142,090 1,012,056 1,120,063 594,855 6,088,415 Springfield Sharks Vendor Prices 2019 - 2020 Shark Dog Nacho Wave Shark Bites Popcorn Pizza Frenzy Soda Fins' Wake $5.99 $4.99 $6.50 $3.99 $5.99 $4.50 Final Bite $6.50 $4.50 $5.99 $4.99 $6.50 $4.99 Shark Tooth $5.99 $5.50 $5.99 $4.50 $4.99 $4.50 Feeding Frenzy $6.50 $4.50 $6.50 $3.50 $4.50 $5.25