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:
o NP_Excel2013_T6_P1a_FirstLastName_1.xlsx
• Open the file you just downloaded and save it with the name:
o NP_Excel2013_T6_P1a_FirstLastName_2.xlsx
o 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
o support_NP_E13_T6_P1a_employees.xlsx
o 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
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.xlsxworkbook 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.xlsxworkbook).
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.xlsxworkbook.)
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.xlsxworkbook).
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.xlsxSwitch 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.xlsxworkbook 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.xlsxworkbook).
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.xlsxworkbook.)
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.xlsxworkbook).
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.xlsxworkbook).
e. Do not break the links.
Close thesupport file support_NP_E13_T6_P1a_sales.xlsx.
3. Group the Cambridge, Framingham, Nashua, and Waltham worksheets andmake 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 worksheetsstill grouped, update the text in the merged cell B3:F3 to read Projected Revenues.
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 worksheetsstill grouped, format the range C5:F9 using the Accounting Number format with 0 decimal places. Ungroup the worksheets.
7. Go to the ConsolidatedbyOffice 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 Productworksheet. (Tip: In the Insert Hyperlink option, select the Place in This Document option to link to the Consolidated by Productworksheet.) 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, addScreenTip 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 & 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. Clearthe contents from the range C5:E8.
Your workbook should look like the Final Figures on the following pages. The Framingham, Nashua, and Walthamworksheets 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.
Attachment:- Attachments.rar