GETTING STARTED

Open the file SC_EX19_7a_Mac_FirstLastName_1.xlsx, available for download from the SAM website.

• Save the file as SC_EX19_7a_Mac_FirstLastName_2.xlsx by changing the “1” to a “2”.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
• With the file SC_EX19_7a_Mac_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

oIf 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.

oSupport_EX19_7a_Mac_Disclaimer.pptx

oSupport_EX19_7a_Mac_Group.jpg

oSupport_EX19_7a_Mac_Sample.xlsx

oSupport_EX19_7a_Mac_Team.html

oIf cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

• Elena Dorado is an associate investment banker at Ballantyne Investments. She is preparing a draft of a workbook that will be presented to shareholders to describe Ballantyne’s asset management team and client relationships. She asks for your help in importing data and adding other content to the workbook.

  Go to the Asset Management worksheet. Change the formatting of cell D4 and the WordArt containing the worksheet title, “Ballantyne Investments”, as follows to coordinate it with the rest of the worksheet:

  • Apply the Fill: Turquoise, Accent Color 1; Shadow WordArt Style to the title.
• The worksheet should list information about the asset management team, which is contained in a webpage.

  Copy data from the webpage as follows:

  • From the Data tab, use the ‘From HTML’ tool to open the webpage Support_EX19_7a_Mac_Team.html as a table in a new workbook.
  • Copy the table, with the column headers included, from the new worksheet. In the Asset Managementworksheet, paste the table in cell G12. Close the new worksheet without saving.
• In the new table, change some data to reflect updates in the company:
  • Delete the row for Employee 520 because Chad Johnson is no longer with Ballantyne Investments.
• Elena wants to list the team information in the range A5:E10. The webpage table separated the first and last names, but Elena wants to list the full name on the Asset Management worksheet.

  List the first and last names of each team member in a single cell as follows:

  • In cell A5, enter a formula using the CONCAT function that displays the first name shown in cell H13followed by a space (” “) and then the last name shown in cell I13.

b.Copy the formatting from cell A4 to cell D4.

c.Format the pasted data in the range G12:M19 as a table using Turquoise, Table Style Light 9. (Hint:Make sure “My table has headers” is checked.)

b.Find the text “Financial advisor” and replace it with Investments to use a position name the company recently updated.

b.Fill the range A6:A10 with the formula in cell A5 to list the full names of the remaining team members.