New Perspectives Excel 365 | Module 5: SAM Project B Clarion University

GETTING STARTED

1. Save the file NP_EX365_5B_FirstLastName_1.xlsx as NP_EX365_5B_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. To complete this Project, you will also need the following files:

Support_EX365_5B_Descriptions.docx

Support_EX365_5B_Expenses.xlsx

3. With the file NP_EX365_5B_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Dev Joshi is a career counselor for Clarion University, a school with campuses in Pennsylvania and Delaware. He is using an Excel workbook to plan the career fairs the school holds in the two states and asks you to complete the plan. Go to the Career Fairs worksheet. Dev asks you to correct one hyperlink and insert others to provide access to external information. In cell D7, edit the hyperlink to use Pitt Convention Center as the display text to show the full name of the location.

2. In cell F6, create a hyperlink to an email address as follows:

a. Insert a link to the following email address: kboone@example.cengage.com

b. Use kboone@example.cengage.com as the text to display.

3. In cell B10, create a hyperlink to a Word document containing descriptions of the trade shows as follows:

a. Link to the file Support_EX365_5B_Descriptions.docx.

b. Use Career fair descriptions as the text to display.

c. Use Review career fairs as the ScreenTip text.

4. In cell B11, create a hyperlink to another worksheet as follows:

a. Link to cell A1 in the PA Fairs worksheet of the current workbook.

b. Use Total expenses as the display text.

5. Go to the Harrisburg worksheet. Dev wants you to make the following changes in the three Pennsylvania campus worksheets, which have the same structure:

a. Group the Harrisburg, Pittsburgh, and Williamsport worksheets so you can format them at the same time.

b. In cell B2, enter Career Fair Expenses as the worksheet title.

c. In cell B2, apply the shading color Brown, Accent 5, Lighter 80% (9th column, 2nd row in the Theme Colors palette).

d. In the range C15:E15, apply the Currency number format with 2 decimal places and $ as the symbol.

6. With the Harrisburg, Pittsburgh, and Williamsport worksheets still grouped, add formulas to the worksheets as follows:

a. In cell C11, enter a formula using the SUM function to calculate the total expenses for Room 1 (the range C5:C10).

b. Fill the range D11:F11 with the formula in cell C11 to calculate total expenses for Rooms 2 and 3 and the total for the location.

c. In cell C15, enter a formula without using a function to divide the total expenses for Room 1 (cell C11) by the square footage (cell C14).

d. Fill the range D15:E15 with the formula in cell C15 to calculate the cost per square foot for Room 2 and 3.

e. Ungroup the worksheets and then check to confirm that all three worksheets contain the formatting and formulas from Steps 5 and 6.

7. Dev asks you to create a copy of the formatted Williamsport worksheet as follows to use for the expenses of the Dover, Delaware career fair:

a. Create a copy of the Williamsport worksheet and use Dover as the name of the new worksheet.

b. Move the Dover worksheet so it appears between the Williamsport and PA Fairs worksheets.

c. In cell B3, edit the text to read Dover as the campus.

d. Clear the contents of the range C5:E10.

8. Go to the PA Fairs worksheet. Dev wants you to consolidate the data from each of the Pennsylvania career fairs as follows:

a. In cell B5, enter a formula without using a function that references cell B5 in the Harrisburg worksheet.

b. Fill the range B6:B10 with the formula in cell B5, filling without formatting.

c. In cell C5, enter a formula using the SUM function, 3-D references, and grouped worksheets that totals the values from cell C5 in the Harrisburg:Williamsport worksheets.

d. Fill the range C6:C10 with the formula in cell C5, filling without formatting.

e. Fill the range D5:E10 with the formulas and the formatting in the range C5:C10.

9. Dev has a workbook containing total budget amounts for each career fair expense. He asks you to add the budget data to the range G5:G10 by creating external references as follows:

a. Open the file Support_EX365_5B_Expenses.xlsx, and then go to the PA Fairs worksheet in the project workbook.

b. Link cell G5 in the PA Fairs worksheet to cell G5 in the Career Fair Expenses worksheet in the Support_EX365_5B_Expenses.xlsx workbook.

c. Link each cell in the range G6:G10 in the PA Fairs worksheet to the corresponding cell in the Career Fair Expenses worksheet in the Support_EX365_5B_Expenses.xlsx workbook.

d. Close the Support_EX365_5B_Expenses.xlsx workbook.

10. Dev started to create named ranges in the worksheet and asks you to complete the work. Create a defined name for the range C5:E5 using Design as the range name.

11. Based on the range B6:E10, create names for the range C6:E10 using the values shown in the left column.

12. Apply the defined names Room_1_Total, Room_2_Total, and Room_3_Total to the formulas in the range C11:E11.

13. In cell F11, edit the defined name to use Total_Rooms as the range name, which is a more descriptive name.

14. In cell F15, enter a formula without using a function that divides the total room expenses (the defined range Total_Rooms) by the total square footage (cell F14). (Hint: If an error symbol appears with the message "The formula in this cell differs from the formulas in this area of the spreadsheet.", ignore it.)

15. Break the external link to the Support_EX365_5B_Expenses.xlsx workbook to replace the formulas in the range G5:G10 of the PA Fairs worksheet with static values.

Your workbook should look like the Final Figures on the following pages. When opening your Graded Summary Report, you may be prompted to update the links. Select Don't Update in the dialog box. Save your changes, close the workbook, and then exit Excel. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.

Final Figure 1: Career Fairs Worksheet

Final Figure 2: Harrisburg Worksheet

Final Figure 3: Pittsburgh Worksheet

Final Figure 4: Williamsport Worksheet

Final Figure 5: Dover Worksheet

Final Figure 6: PA Fairs Worksheet