New Perspectives Excel 2013 Tutorial 6: SAM Project 1b Abdulrahman Almurbati 1

Subject:MS ExcelPrice:19.87 Bought3

Share With

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.