• GETTING STARTED
• Open the file SC_EX365_2021_CT7c_FirstLastName_1.xlsx, available for
• download.
•  Save the file SC_EX365_2021_CT7c_FirstLastName_1.xlsx as
• SC_EX365_2021_CT7c_FirstLastName_2.xlsx
•  Edit the file name by changing “1” to “2”.
•  If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.
•  To complete this Project, you will also need the following files:
•  Support_EX365_2021_CT7c_Disclaimer.pptx
•  Support_EX365_2021_CT7c_Group.jpg
•  Support_EX365_2021_CT7c_Sample.xlsx
•  Support_EX365_2021_CT7c_Team.html
•  With the file SC_EX365_2021_CT7c_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
•  If cell B6 does not display your name, delete the file and download a new copy.
•  When opening your start file or the Graded Summary Report, you may be prompted to update the links. Select Don’t Update in the dialog box.

PROJECT STEPS

1. 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 with the rest of the worksheet:
   a. Apply the Fill: Turquoise, Accent Color 1; Shadow WordArt Style to the title.
   b. Copy the formatting from the “Name” heading to the “Years Experience”
   heading.
2. The worksheet should list information about the asset management team, which
   is contained in a webpage. Copy data from the webpage as follows:
3. a. Open the webpage Support_EX365_2021_CT7c_Team.html in a
4. browser.
5. b. In the Asset Management worksheet, import the data from the
6. Support_EX365_2021_CT7c_Team.html webpage in cell G12.
7. c. Format the imported data as a table using Turquoise, Table Style Light 9.
8. Remove the external connection, if prompted.
9. In the imported table, change some data to reflect updates in the company:
   a. Delete the row for Employee 520 because Chad Johnson is no longer with
   Ballantyne Investments.
   b. Find the text “Financial advisor” and replace it with Investments to use a
   position name the company recently updated.
10. Elena wants to list the team information in the shaded range on the left. The
    webpage table separated the first and last names, but Elena wants to list full
    names on the Asset Management worksheet. List the first and last names of
    each team member in a single cell as follows:
    a. For the first employee’s name in the shaded range, use a function to
    concatenate the first name followed by a space and then the last name for
    the first employee from the imported data.
    b. Fill the remainder of the Name column with the new formula to list the full
    names of the remaining team members.
11. Use the imported data to fill in the rest of the table as follows:
    a. Copy the Position data from the imported data and paste only the values
    in the Position column of the shaded range.
    b. For the first employee’s Specialty data in the shaded range, use a function
    to capitalize the first letter in each word in the Specialty text for the first
    employee from the imported data. Fill the remainder of the Specialty
    column with the new formula to list the specialties of the remaining team
    members.
    c. For the first employee’s Years Experience data in the shaded range, use a
    function to insert the first two characters from the Experience text for the
    first employee from the imported data. Fill the remainder of the Years
    Experience column with the new formula to list the years of experience of
    the remaining team members.
    d. For the first employee’s From data, use a function to insert the last two
    characters from the BirthPlace text for the first employee from the
    imported data.
    e. Resize the first four columns to their best fit, resize the next two columns
    to 7.00, and size column G to 21.00. [Mac Hint: Size the first three
    columns to 20.00, size the fourth column to 15.00, resize the next two
    columns to 7.00, and size column G to 21.00.]
    f. Hide the rows that contain the imported data so that the worksheet does
    not display duplicated data.
12. Elena already imported data summarizing the performance of the asset
    management team on the Performance Summary worksheet but wants to
    display the data in the range G5:H8 of the Asset Management worksheet. She
    asks you to switch the rows and columns when you insert the data to fit in the
    Performance Summary table.
    Go to the Performance Summary worksheet. Copy the data in the shaded range man Excel 365/2021 | Module 7: SAM Critical Thinking Project 1c and then transpose the rows and columns as you paste the data on the Asset
13. Management worksheet below the “Performance Summary” heading.
14. In the Asset Management worksheet, insert and format a picture of the asset
    management team as follows so that shareholders can identify members of the
    team:
    a. Insert the picture Support_EX365_2021_CT7c_Group.jpg.
    b. Move and resize the picture proportionally so that it covers the range
    G19:J36.
    c. Add a border to the picture using Teal, Accent 6 as the border color to
    coordinate the picture with the rest of the worksheet.
    d. Apply the Offset: Center picture effect from the Shadow gallery.
15. Add a caption to identify the picture as follows:
    a. In cell H37, insert a text box to create the caption.
    b. Enter Asset management team at work in the text box.
    c. Display the entire caption and move the text box so that it is centered
    below the picture in the range G37:J38.
16. Elena also wants to provide an organization chart to show the hierarchy of the
    asset management team. Insert an organization chart as follows:
    a. Insert an organization chart from the SmartArt gallery.
    b. Move and resize the SmartArt so that the upper-left corner is in cell A19
    and the lower-right corner is in cell D36.
17. Add text to the SmartArt as follows, using Figure 1 as a guide:
    a. Enter Frye in the top shape.
    b. Add a shape after the second shape so that it appears to the right of the
    second shape.
    c. In the second row, enter the following names from left to right, one name
    per shape: Montgomery, Garcia.
    d. In the bottom row, enter the following names from left to right, one name
    per shape: Lattimer, Gao, Krause.
    Figure 1: SmartArt Text
18. Add a caption to identify the SmartArt as follows:
    a. In cell B37, insert a text box to create the caption.
    b. Enter Org chart in the text box.
    c. Resize the text box to display the entire caption. Align the top of both
    captions in row 37.
19. Hide the gridlines on the worksheet to increase its visual appeal.
20. Elena wants to include the company slogan and a standard disclaimer about
    Ballantyne Investments’ services on the Asset Management worksheet. She has
    this information stored in a PowerPoint presentation. Include the information as
    follows:
    a. Use PowerPoint to open the presentation
    Support_EX365_2021_CT7c_Disclaimer.pptx.
    b. In Excel, use the Screen Clipping tool to paste a screenshot of only the
    slogan and disclaimer into the Asset Management worksheet.
    c. Position the upper-left corner of the screenshot image in cell B40.
21. Go to the Client Investment Tracker worksheet, which Elena asks you to finish.
    The worksheet shows sample data for a fictional client to illustrate the type of
    information Ballantyne Investments provides to its clients.
    Elena inserted the data in the range A20:C28 as a link to another worksheet.
    Complete the sample data and break the link as follows:
    a. Use the Quick Analysis tool to calculate the total investment amount
    below the values in the Investment column. This total should match the
    total at the bottom of the Total Invested column. [Mac Hint: Instead of
    using the Quick Analysis tool, select the appropriate cells and use the
    tools on the Home tab of the Ribbon.]
    b. To help clients visualize the data, use the Quick Analysis tool to create a
    conditional formatting rule that adds solid fill blue data bars to the values
    in the Total % Gain (Loss) column to help clients visualize the data. [Mac
    Hint: Instead of using the Quick Analysis tool, select the appropriate cells
    and use the tools on the Home tab of the Ribbon.]
    c. Break the link to the Support_EX365_2021_CT7c_Sample.xlsx
    workbook because Elena no longer needs to update the date.
22. Elena wants to show the total amount invested compared to the total value
    during the eight months of investments. Insert a chart in the Client Investment
    Tracker worksheet as follows to show this information:
    a. Based on the headings and data in the Date, Total Value, and Total
    Invested columns, insert the first type of chart that Excel recommends,
    which is a Clustered Column – Line chart.
    b. Move and resize the chart so that it covers the range A5:F19.
    c. Remove the chart title because the legend identifies the data clearly.
23. Elena wants to make sure that people reviewing the worksheet understand it
    displays sample data. Add a shape to the worksheet as follows to provide this
    information:
    a. In cell E30, insert a Callout: Line shape from the Callouts section of the
    Shapes gallery.
    b. Move the callout line so that it points to the bottom of the Total Invested
    column.
    c. Type Sample data only in the callout shape.
    d. Apply the Subtle Effect – Turquoise, Accent 1 shape style to the callout
    shape.
24. Elena wants to format the column chart in the range G20:M33 to call attention
    to it. She also wants to change the layout of the chart so that it provides
    another way to compare the total amounts by month. Modify the column chart
    as follows:
    a. Switch the rows and columns so that the chart compares the total value
    and the total invested by months 1 to 8.
    b. Apply the Ice Blue, Background 2 shape fill color to the plot area of the
    chart.
    c. Apply the Offset: Bottom Right shadow shape effect to the legend.
25. Elena wants to add one more element of visual interest on the worksheet.
    a. Insert an online picture using investments as the search text. Select the
    first picture in the search results. Delete the text box underneath that
    contains the attribution. (Hint: The picture may differ from the image in
    the Final Figure.)
    b. Reposition and resize the picture so that it covers the range G1:K13.