Homework answers / question archive / New Perspectives Excel 2013 Tutorial 6: SAM Project 1a Miranda Holcomb 1

New Perspectives Excel 2013 Tutorial 6: SAM Project 1a Miranda Holcomb 1

MS Excel

Share With

---

New Perspectives Excel 2013 Tutorial 6: SAM Project 1a

Miranda Holcomb

1. 

Open the support file support_NP_E13_T6_P1a_employees.xlsx. Switch

back to the NP_Excel2013_T6_P1a_FirstLastName_2.xlsx workbook and go

to the Products worksheet.

Create external references in the Products worksheet to the contact

information found in the support_NP_E13_T6_P1a_employees.xlsx

workbook as described below:

a. Using external cell references, link cell F5 in the Products worksheet to

cell E5 in the SalesDept worksheet (in the

support_NP_E13_T6_P1a_employees.xlsx workbook).

b. Using external cell references, link cell F12 in the Products worksheet

to cell E6 in the SalesDept worksheet (in the

support_NP_E13_T6_P1a_employees.xlsx workbook.)

c. Using external cell references, link cell F19 in the Products worksheet

to cell E7 in the SalesDept worksheet (in the

support_NP_E13_T6_P1a_employees.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_P1a_employees.xlsx.

2. Open the support file support_NP_E13_T6_P1a_sales.xlsx Switch back to

the NP_Excel2013_T6_P1a_FirstLastName_2.xlsx workbook and go to the

Revenue to Date worksheet.

Create external references in the Revenue to Date worksheet to the sales

information found in the support_NP_E13_T6_P1a_sales.xlsx workbook as

described below:

a. Using external cell references, link cell C5 in the Revenue to Date

worksheet to cell D4 in the Sales worksheet (in the

support_NP_E13_T6_P1a_sales.xlsx workbook).

b. Using external cell references, link cell C6 in the Revenue to Date

worksheet to cell D5 in the Sales worksheet (in the

support_NP_E13_T6_P1a_sales.xlsx workbook.)

c. Using external cell references, link cell C7 in the Revenue to Date

worksheet to cell D6 in the Sales worksheet (in the

support_NP_E13_T6_P1a_sales.xlsx workbook).

d. Using external cell references, link cell C8 in the Revenue to Date

worksheet to cell D7 in the Sales worksheet (in the

support_NP_E13_T6_P1a_sales.xlsx workbook).

e. Do not break the links.

Close the support file support_NP_E13_T6_P1a_sales.xlsx.

3. 

Group the Cambridge, Framingham, Nashua, and Waltham worksheets

and make the following formatting updates:

a. Merge and center the range B2:F2

b. Bold the merged range B2:F2

c. Change the font size of the merged range B2:F2 to 18 point.

d. Change the font color of the merged range to White, Background 1 and

change the fill color of the merged range to Orange, Accent 2, Darker 25%

(6th column, 5th row in the Theme Colors Palette).

4. 

With the Cambridge, Framingham, Nashua, and Waltham worksheets still

grouped, update the text in the merged cell B3:F3 to read Projected

5. 

With the Cambridge, Framingham, Nashua, and Waltham worksheets still

grouped, enter a formula in cell C9 that uses the SUM function to

calculates the total projected revenues for 2014 (which appears in the

range C5:C8). Use the fill handle to copy the formula from cell C9 into the

range D9:F9.

6. 

With the Cambridge, Framingham, Nashua, and Waltham worksheets still

grouped, format the range C5:F9 using the Accounting Number format

with 0 decimal places. Ungroup the worksheets.

7. 

Go to the Consolidated by Office worksheet and insert worksheet

references as described below:

a. In cell C5, insert a formula that references cell C9 (the total projected

revenue in 2014) in the Cambridge worksheet.

b. In cell C6, insert a formula that references cell C9 (the total projected

revenue in 2014) in the Framingham worksheet.

c. In cell C7, insert a formula that references cell C9 (the total projected

revenue in 2014) in the Nashua worksheet.

d. In cell C8, insert a formula that references cell C9 (the total projected

revenue in 2014) in the Waltham worksheet.

e. Copy the formulas in the range C5:C8 to the range D5:E8.

8. Insert a hyperlink in cell B11 that links to cell A1 in the Consolidated by

Product worksheet. (Tip: In the Insert Hyperlink option, select the Place

in This Document option to link to the Consolidated by Product

worksheet.) The hyperlink should display the text Click here for projected

revenues by product (without a period.)

9. Go to the Consolidated by Product worksheet. In cell C5, insert a formula

using the SUM function and 3-D references that totals the values of C5 in

the Cambridge, Framingham, Nashua, and Waltham worksheet. Fill the

formula in cell C5 to the range C6:C8 without copying the formatting.

Then copy the formulas in the range C5:C8 to the range D5:E8.

10. 

In cell B13, add ScreenTip to the existing hyperlink that reads Revenue to

date by product (without a period.)

11. 

Group the Cambridge, Framingham, Nashua, Waltham, Consolidated by

Office, and Consolidated by Product worksheets. Add a header to the

grouped worksheets that uses a Header and Footer element to display

the sheet name in the center header section.

12.

With the Cambridge, Framingham, Nashua, Waltham, Consolidated by

Office, and Consolidated by Product worksheets still grouped, insert a

footer that displays the custom text Confidential-For Internal Distribution

Only in the center footer section. Ungroup the worksheets.

13.

Spring Software is opening an office in Manchester, NH. You need to

create a new worksheet for this office that looks like the other regional

worksheets.

a. Create a copy of the Cambridge worksheet. Position the new worksheet

after the Framingham worksheet.

b. Name the new worksheet Manchester.

c. Edit the text in the merged cell B2:F2 to be Spring Software-

Manchester Office.

d. Clear the contents from the range C5:E8.