New Perspectives Excel 2013 Tutorial 6: SAM Project 1b Abdulrahman Almurbati 1.  Open the support file support_NP_E13_T6_P1b_Contacts.xlsx. Switch back to the NP_Excel2013_T6_P1b_FirstLastName_2.xlsx workbook and go to the Buyers worksheet. Create external references in the Buyers worksheet to the contact information found in the support_NP_E13_T6_P1b_Contacts.xlsx workbook as described below: a. Using external cell references, link cell I6 in the Buyers worksheet to cell D7 in the Contacts worksheet (in the support_NP_E13_T6_P1b_Contacts.xlsx workbook). b. Using external cell references, link cell I13 in the Buyers worksheet to cell D8 in the Contacts worksheet (in the support_NP_E13_T6_P1b_Contacts.xlsx workbook). c. Using external cell references, link cell I20 in the Buyers worksheet to cell D9 in the Contacts worksheet (in the support_NP_E13_T6_P1b_Contacts.xlsx workbook). d. Now that the information is available in the current worksheet, break the links in the workbook to convert the formulas with external references to values. Close the support file support_NP_E13_T6_P1b_Contacts.xlsx. 2.  Open the support file support_NP_E13_T6_P1b_Forecast.xlsx. Switch back to the NP_Excel2013_T6_P1b_FirstLastName_2.xlsx workbook and go to the Projected Sales worksheet. Create external references in the Projected Sales worksheet to the sales information found in the support_NP_E13_T6_P1b_Forecast.xlsx workbook as described below: a. Using external cell references, link cell B5 in the Projected Sales worksheet to cell C6 in the Sales Forecast worksheet (in the support_NP_E13_T6_P1b_Forecast.xlsx workbook). b. Using external cell references, link cell B6 in the Projected Sales worksheet to cell C7 in the Sales Forecast worksheet (in the support_NP_E13_T6_P1b_Forecast.xlsx workbook). c. Using external cell references, link cell B7 in the Projected Sales worksheet to cell C8 in the Sales Forecast worksheet (in the support_NP_E13_T6_P1b_Forecast.xlsx workbook). d. Using external cell references, link cell B8 in the Projected Sales worksheet to cell C9 in the Sales Forecast worksheet (in the support_NP_E13_T6_P1b_Forecast.xlsx workbook). e. Do not break the links. Close the support file support_NP_E13_T6_ P1b_Forecast.xlsx. 3.  Group the Westview, Fontaine, Willow, and Highgate worksheets and make the following formatting updates: a. Merge and center the range A2:F2. b. Bold the merged range A1:F2. c. Change the font size of the merged range A1:F1 to 24 pt. d. Change the font color of the merged range A1:F1 to Aqua, Accent 1, Darker 50% (5th column, bottom row in the Theme Colors palette) and change the fill color of the merged range to White, Background 1, Darker 15% (1st column, 3rd row in the Theme Colors Palette). 4.  With the Westview, Fontaine, Willow, and Highgate worksheets still grouped, update the text in the merged cell A3:F3 to read Quarterly Sales by Category. 5.  With the Westview, Fontaine, Willow, and Highgate worksheets still grouped, enter a formula in cell B11 that uses the SUM function to calculate total Q1 sales (using values from the range B5:B10). Use the fill handle to copy the formula from cell B11 into the range C11:F11. 6. With the Westview, Fontaine, Willow, and Highgate worksheets still grouped, format the range B5:F11 using the Currency Number format with 0 decimal places. Ungroup the worksheets. 7.  Go to the Sales by Store worksheet and insert worksheet references as described below: a. In cell B4, insert a formula that references cell B11 (total Q1 sales) in the Westview worksheet. b. In cell B5, insert a formula that references cell B11 (total Q1 sales) in the Fontaine worksheet. c. In cell B6, insert a formula that references cell B11 (total Q1 sales) in the Willow worksheet. d. In cell B7, insert a formula that references cell B11 (total Q1 sales) in the Highgate worksheet. e. Copy the formulas in the range B4:B7 to the range C4:E7. 8.  Insert a hyperlink in cell A10 that links to cell A1 in the Sales by Category worksheet. (Hint: In the Insert Hyperlink dialog box, select the Place in This Document option to link to the Sales by Category worksheet.) The hyperlink should display the text Click here for sales by category (without a period). 9.  Go to the Sales by Category worksheet. In cell B4, insert a formula using the SUM function and 3-D references that totals the values of cell B5 in the Westview, Fontaine, Willow, and Highgate worksheets. Fill the formula in cell B4 to the range B5:B9 without copying the formatting. Then copy the formulas in the range B4:B9 to the range C4:E9. 10.  In cell A12, add a ScreenTip to the existing hyperlink that reads Projected Sales worksheet (without a period). 11.  Group the Westview, Fontaine, Willow, Highgate, Sales by Store, and Sales by Category worksheets. Add a header to the grouped worksheets that uses a Header and Footer element to display the sheet name in the right header section. 12.  With the Westview, Fontaine, Willow, Highgate, Sales by Store, and Sales by Category worksheets still grouped, insert a footer that displays the custom text Proprietary in the center footer section. Ungroup the worksheets. 13.  Fit Kix Footwear is opening a new store in Concord, NH. You need to create a new worksheet for this store that looks like the worksheets for the other stores. a. Create a copy of the Westview worksheet. Position the new worksheet before the Sales by Store worksheet. b. Name the new worksheet Concord. c. Edit the text in the merged cell A2:F2 to be Concord Store. d. Clear the contents from the range B5:E10.