Shelly Cashman Excel 365 | Module 7: End of Module Project 1 Karam & Lee

GETTING STARTED

1. Save the file SC_EX365_EOM7-1_FirstLastName_1.xlsx as SC_EX365_EOM7-1_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_EOM7-1_Chan.csv

Support_EX365_EOM7-1_Estrada.accdb

Support_EX365_EOM7-1_Karam.jpg

Support_EX365_EOM7-1_Nasir.html

Support_EX365_EOM7-1_Stohlman.docx

3. With the file SC_EX365_EOM7-1_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. Jayden Reeves is an assistant analyst at Karam & Lee, a financial services company that offers financial planning and analysis services in Chicago, Illinois. Jayden is preparing a workbook to summarize the company's billings in the past month. He asks for your help in importing data and adding other content to the workbook to represent the company's managers. Go to the Billings worksheet. Jayden asks you to make the worksheet title more noticeable. Format the "Combined Billings" text box as WordArt using the Fill: Gray, Accent color 1; Shadow style. Bold the text and then apply the Orange, Accent 6 (10th column, 1st row of the Theme Colors palette) Text Fill color.

2. Jayden wants you to import data from four regional managers. Ming Chan provided her monthly data in a text file. Import the data from the file Support_EX365_EOM7-1_Chan.csv. Use the Power Query Editor to transform the data by removing the first row, and then use the new first row as headers. Load the data as a table in the Billings worksheet beginning in cell A4.

3. Vera Estrada provided her monthly data as an Access database file. Select the Estrada table in Support_EX365_EOM7-1_Estrada.accdb for importing, and then load the data as a table in the Billings worksheet beginning in cell A11.

4. Jamil Nasir provided his monthly data as a webpage file. Import the data from the file Support_EX365_EOM7-1_Nasir.html, specifying a path such as c:\users\username\documents\projects\Support_EX365_EOM7-1_Nasir.html. Select the Karam & Lee: Nasir Clients data. Use the Power Query Editor to transform the data by removing the first column, and then load the data as a table in the Billings worksheet beginning in cell A18.

5. Bryce Stohlman provided his monthly data as a Word document. Open the Word document Support_EX365_EOM7-1_Stohlman.docx, copy the table of data, and then paste it while matching destination formatting in the Billings worksheet beginning in cell A33. Transform and format the Stohlman data by copying the range A33:F36, and then pasting the data beginning in cell A25, transposing the data as you paste.

6. Format the range A25:D30 as a table with headers using Orange, Table Style Medium 7. Resize column D to its best fit, and then clear the contents of the range A33:F36.

7. Jayden asks you to format the imported data to make it easier to use. Remove the filter buttons from the header rows. Format the Contract, Expenses, and Total data in the Chan, Estrada, and Nasir tables using the Comma number style with no decimal places, and then format the entire tables using a 12-pt. font size.

8. Jayden also wants to display the total billings for the five types of financial services the company provides, and then compare the billings for each service. In cell G9, use the Quick Analysis tools to insert the total project amount from the range G4:G8.

9. Use the Quick Analysis tools to create a Clustered Bar chart based on the data in the range F3:G8. Move and resize the bar chart so it covers the range F11:K23.

10. Modify the bar chart to make it stand out. Format the bars by applying the Colored Fill – Orange, Accent 6 shape style and the Offset: Bottom Right shape effect from the Outer section of the Shadow gallery.

11. Jayden wants you to call attention to the revenue from retirement services. Insert a Text Box from the Basic Shapes section of the Shapes gallery. Type Retirement services are best sellers in the text box. Resize the text box to a height of 0.5" and a width of 1.7", and then move the text box so its upper-left corner is in cell J10 and its lower-right corner is in cell L11. Apply the Colored Outline – Orange, Accent 6 shape style to the text box to highlight it on the worksheet.

12. Go to the Managers worksheet, which includes SmartArt showing the regional managers in the company and their relationship to one another. In the top shape for Ali Karam, insert the missing picture from the file Support_EX365_EOM7-1_Karam.jpg.

13. Jayden asks you to update the SmartArt so it accurately reflects the company's organization. Because Tricia Cortez is Bryce Stohlman's assistant, demote her shape so it appears below the Bryce Stohlman shape.

14. Create a similar diagram for the company's financial managers. Insert the Organization Chart SmartArt from the Hierarchy category. Move and resize the SmartArt so it covers the range C26:I37.

15. Type Karl Lee in the top SmartArt shape. Delete the shape immediately below the Karl Lee shape since he does not have an assistant. Type Ana Cruz in the lower-left shape, type Eli Mann in the lower-middle shape, and type Reva Flores in the lower-right shape to represent the three managers that Karl Lee supervises.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.

Final Figure 1: Billings Worksheet

Final Figure 2: Managers Worksheet