Shelly Cashman Excel 2019 | Module 7: End of Module Project 1 Wang Marketing IMPORT DATA AND INSERT SMARTART, IMAGES, AND CHARTS   GETTING STARTED Open the file SC_EX19_EOM7-1_FirstLastName_1

Subject:MS ExcelPrice:19.99 Bought4

Share With

Shelly Cashman Excel 2019 | Module 7: End of Module Project 1

Wang Marketing

IMPORT DATA AND INSERT SMARTART, IMAGES, AND CHARTS

 

• GETTING STARTED

• Open the file SC_EX19_EOM7-1_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SC_EX19_EOM7-1_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program 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_EX19_EOM7-1_Chicago.accdb
  • Support_EX19_EOM7-1_Logo.png
  • Support_EX19_EOM7-1_LosAngeles.html
  • Support_EX19_EOM7-1_NewYork.csv
• With the file SC_EX19_EOM7-1_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.
• When downloading Access support files, your browser may display a warning message similar to "This type of file can harm your computer." SAM Project files are always considered safe, so you can disregard this message.

• PROJECT STEPS

1. Abdul Khoury is an assistant sales manager at Wang Marketing, a full-service agency in New York City that provides advertising, public relations, and marketing for clients around the world. Abdul is preparing a workbook to summarize the agency's revenue and expenses in the past month. He will submit similar workbooks to the agency's bankers once a month as part of their loan agreement with the bank. Abdul asks for your help in importing data and adding other content to the workbook.
   Go to the Advertising worksheet. Insert the picture Support_EX19_EOM7-1_Logo.png to add a picture of the agency's logo to the worksheet. Move and resize the picture so its upper-left corner is in cell A1 and its lower-right corner is in cell G7. Add the alt text Wang Marketing logo for people using screen readers.
2. Abdul needs to import data from the New York, Chicago, and Los Angeles offices. The New York office provided its monthly data in a text file. Import the data from the file Support_EX19_EOM7-1_NewYork.csv as a table in the Advertising worksheet beginning in cell A10.
3. The Chicago office provided its monthly data as an Access database file. Select the Chicago table in Support_EX19_EOM7-1_Chicago.accdb for importing. Use the Power Query Editor to transform the data by removing the first column, and then load the data as a table in the Advertising worksheet beginning in cell A16.
4. The Los Angeles office provided its monthly data as a webpage file. Import the data from the file Support_EX19_EOM7-1_LosAngeles.html, specifying a path such as c:\users\username\documents\projects\ Support_EX19_EOM7-1_LosAngeles.html. Select the Wang Marketing: Los Angeles Office 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 Advertising worksheet beginning in cell A22.
5. Apply Dark Red, Table Style Light 9 to each of the three tables to coordinate with the rest of the workbook. Remove the filter buttons from the header rows. Format the $ Revenue and $ Expense data in each table using the Comma number style with no decimal places.
6. Abdul needs to calculate the profit from type of ad at the three office locations. Insert a column in each table to the right of the $ Expense column. Use $ Profit as the name of each new column. In cell D11, enter a formula without using a function that uses cell references to subtract the expense amount in cell C11 from the revenue amount in cell B11. Copy the formula in cell D11 to the range D12:D14 (if necessary), the range D17:D20, and the range D23:D26.
7. Abdul wants to display the profit from each office for each type of ad. In cell G10, enter a formula using the SUM function that totals the Print profit values (cells D11, D17, and D23). Fill the range G11:G13 with the formula in the cell G10.
8. Abdul also wants to display the total profit from the four types of ads the agency created in the past month. In cell G14, use the Quick Analysis tools to insert the total project amount from the range G10:G13.
9. Abdul wants to include a bar chart that compares the profit totals for the four types of ads the agency creates. Insert a Clustered Bar chart based on the data in the range F9:G13. Move and resize the bar chart so it covers the range A27:G42.
10. Modify the bar chart to make it stand out. Format the bars by applying the Colored Fill – Dark Red, Accent 1 shape style and the Offset: Bottom shape effect from the Outer section of the Shadow gallery.
11. Abdul wants to call attention to the revenue from Web ads. Insert a Text Box from the Basic Shapes section of the Shapes gallery. Type Revenue from Web ads is increasing in the text box. Resize the text box to a height of 0.5" and a width of 1.75", and then move the text box so its upper-left corner is in cell G26 and its lower-right corner is in cell H28. Apply the Colored Outline – Dark Red, Accent 1 shape style to the text box to highlight it on the worksheet.
12. Go to the Organization Chart worksheet, which includes a worksheet title formatted as WordArt. Change the text fill color of the WordArt to Dark Red, Accent 1 to use one of the colors from the agency logo.
13. The SmartArt on the Organization Chart worksheet shows the managers in the agency and their relationship to one another. Abdul does not have photos of all the managers, so he wants to change the SmartArt layout to one that does not use pictures. Change the layout to Organization Chart. Change the colors to Colored Fill - Accent 1 to coordinate with the worksheet title.
14. Abdul needs to update the SmartArt so it accurately reflects the agency's organization. Promote the shape containing the text "Raquel Orozco Sales Manager" so it appears in the same row as the other manager shapes. Add a shape below the "Melissa Romanoff Marketing Manager" shape. Type Jorge Cruz Marketing Asst in the new shape.

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 SAM website to submit your completed project.