question archive New Perspectives Excel 2013 Tutorial 6: SAM Project 1a Miranda Holcomb 1
Subject:MS ExcelPrice:19.87 Bought3
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.
Purchased 3 times