Excel problem 2

New Perspectives Excel 2013

Tutorial 6: SAM Project 1a

Spring Software

MANAGING MULTIPLE WORKSHEETS AND WORKBOOKS

PROJECT DESCRIPTION Noah Lang works in the sales department of Spring Software, a software development company in Cambridge, Massachusetts. He is creating a workbook that summarizes the company’s projected revenues by product line and location. He wants you to complete the workbook by finalizing data entry and applying consistent formatting across the worksheets. You will also insert links to other worksheets and workbooks, and create a new worksheet based on the existing worksheet structure and formats. GETTING STARTED

Download the following file from the SAM website:
- NP_Excel2013_T6_P1a_FirstLastName_1.xlsx
Open the file you just downloaded and save it with the name:
- NP_Excel2013_T6_P1a_FirstLastName_2.xlsx
- Hint: If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel 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
- support_NP_E13_T6_P1a_employees.xlsx
- support_NP_E13_T6_P1a_sales.xlsx
With the file NP_Excel2013_T6_P1a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

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:

1. 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).
2. 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.)
3. 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).
4. 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.

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:

1. 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).
2. 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.)
3. 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).
4. 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).
5. Do not break the links.

Close the support file support_NP_E13_T6_P1a_sales.xlsx.

Group the Cambridge, Framingham, Nashua, and Waltham worksheets and make the following formatting updates:
1. Merge and center the range B2:F2
2. Bold the merged range B2:F2
3. Change the font size of the merged range B2:F2 to 18 point.
4. 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).
With the Cambridge, Framingham, Nashua, and Waltham worksheets still grouped, update the text in the merged cell B3:F3 to read Projected Revenues.
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.
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.
Go to the Consolidated by Office worksheet and insert worksheet references as described below:
1. In cell C5, insert a formula that references cell C9 (the total projected revenue in 2014) in the Cambridge worksheet.
2. In cell C6, insert a formula that references cell C9 (the total projected revenue in 2014) in the Framingham worksheet.
3. In cell C7, insert a formula that references cell C9 (the total projected revenue in 2014) in the Nashua worksheet.
4. In cell C8, insert a formula that references cell C9 (the total projected revenue in 2014) in the Waltham worksheet.
5. Copy the formulas in the range C5:C8 to the range D5:E8.
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.)
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.
In cell B13, add ScreenTip to the existing hyperlink that reads Revenue to date by product (without a period.)
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 & Footer element to display the sheet name in the center header section.
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.
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.
1. Create a copy of the Cambridge worksheet. Position the new worksheet after the Framingham worksheet.
2. Name the new worksheet Manchester.
3. Edit the text in the merged cell B2:F2 to be Spring Software-Manchester Office.
4. Clear the contents from the range C5:E8.

Your workbook should look like the Final Figures on the following pages. The Framingham, Nashua, and Waltham worksheets are not shown in the final figures, as the formatting should match the Cambridge worksheet. Save your changes, close the document, and exit Excel. Follow the directions on the SAM website to submit your completed project.

Note: When you open your graded solution file for this project, you may be given a warning regarding the external links in your document. Click the Don’t Update option in the dialog window.

Final Figure 1: Products Worksheet

Excel problem 2 1