Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Harvey city comprehensive case solutions

28/12/2020 Client: saad24vbs Deadline: 10 Days

Shelly Cashman Excel 2019 | Module 7: SAM Project 1a


Ballantyne Investments


Shelly Cashman Excel 2019 | Module 7: SAM Project 1a


IMPORT DATA AND INSERT SMARTART, IMAGES, AND CHARTS


GETTING STARTED

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


Save the file as SC_EX19_7a_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_7a_Disclaimer.pptx


Support_EX19_7a_Group.jpg


Support_EX19_7a_Sample.xlsx


Support_EX19_7a_Team.html


Support_EX19_7a_Tools.docx


With the file SC_EX19_7a_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.


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 Turquoise, Accent Color 1, Shadow WordArt Style to the title.


Copy the formatting from cell A4 to cell D4.


The worksheet should list information about the asset management team, which is contained in a webpage. Copy data from the webpage as follows:


Open the webpage Support_EX19_7a_Team.html in a browser.


In the Asset Management worksheet, import the data from the Support_EX19_Team webpage in cell G12.


Format the imported data in the range G12:M19 as a table using Turquoise, Table Style Light 9. Remove the external connection if prompted.


In the imported 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.


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


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 CONCATENATE function that displays the first name shown in cell H13 followed by a space (" ") and then the last name shown in cell I13.


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


Incorporate the imported data in the range B5:E10 as follows:


Copy the Position data from the range J13:J18 and paste only the values in the range B5:B10.


In cell C5, enter a formula using the PROPER function to capitalize the first letter in each word in the Specialty text in cell K13.


Fill the range C6:C10 with the formula in cell C5 to list the specialties of the remaining team members.


In cell D5, enter a formula using the LEFT function to insert the first 2 characters on the left of cell L13. Copy the formula in cell D5 to the range D6:D10.


In cell E5, enter a formula using the RIGHT function to insert the last 2 characters on the right of cell M13. Copy the formula in cell E5 to the range E6:E10.


Resize columns A:D to their best fit, resize columns E:F to 7.00, and size column G to 21.00.


Hide rows 12 to 18 so that the worksheet does not display duplicated data.


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 range G5:H8. Go to the Performance Summary worksheet. Copy the data in the range A1:D2, and then transpose the rows and columns as you paste the data on the Asset Management worksheet starting in cell G5.


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:


Insert the picture Support_EX19_7a_Group.jpg.


Move and resize the picture proportionally so that the upper-left corner is in cell G19 and the lower-right corner is in cell J36.


Add a border to the picture using Teal, Accent 6 as the border color to coordinate the picture with the rest of the worksheet.


Apply the Offset: Center picture effect from the Outer section of the Shadow gallery.


Add a caption to identify the picture as follows:


In cell H37, insert a Text Box from the Basic Shapes section of the Shapes gallery.


Enter Asset management team at work in the text box.


Resize the text box to display the entire caption and move the text box so that it is centered below the picture in the range G37:J38.


Elena also wants to provide an organization chart to show the hierarchy of the asset management team. Insert an organization chart as follows:


Insert the Organization Chart SmartArt from the Hierarchy section of the SmartArt gallery.


Move and resize the SmartArt so that the upper-left corner is in cell A19 and the lower-right corner is in cell D36.


Add text to the SmartArt as follows, using Figure 1 as a guide:


Enter Frye in the top shape.


Enter Montgomery in the second shape.


Add a shape after the "Montgomery" shape so that it appears to the right of the "Montgomery" shape.


Enter Garcia in the new shape.


In the bottom row, enter Lattimer in the left shape.


Enter Gao in the middle shape.


Enter Krause in the right shape.


Figure 1: SmartArt Text


Add a caption to identify the SmartArt as follows:


In cell B37, insert a Text Box from the Basic Shapes section of the Shapes gallery.


Enter Org chart in the text box.


Resize the text box to display the entire caption and align the top of the "Org chart" text box with the top of the "Asset management team at work" text box in row 37.


Hide the gridlines on the worksheet to increase its visual appeal.


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:


Use PowerPoint to open the presentation Support_EX19_7a_Disclaimer.pptx.


In Excel, use the Screen Clipping tool to paste a screenshot of only the slogan and disclaimer into the Asset Management worksheet.


Position the upper-left corner of the screenshot image in cell B40.


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:


In cell B29, use the Quick Analysis tools to insert the total investment amount from the range B21:B28, which should match the total in cell D28.


Use the Quick Analysis tool to create a conditional formatting rule that adds Solid Fill Blue Data Bars to the range F21:F28 to help clients visualize the data.


Break the link to the Support_EX19_7a_Sample.xlsx workbook because Elena no longer needs to update the date.


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:


Based on the nonadjacent data in the Date (range A20:A28), Total Value (range C20:C28), and Total Invested (range D20:D28) columns, insert the first type of chart that Excel recommends, which is a Clustered Column – Line chart.


Move and resize the chart so that its upper-left corner is in cell A5 and its lower-right corner is in cell F19.


Remove the chart title because the legend identifies the data clearly.


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:


In cell E30, insert a Callout: Line shape from the Callouts section of the Shapes gallery.


Move the callout line so that it points to the bottom of the "Total Invested" column.


Type Sample data only in the callout shape.


Apply the Subtle Effect – Turquoise, Accent 1 shape style to the callout shape.


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:


Switch the rows and columns so that the chart compares the total value and the total invested by months 1 to 8.


Apply the Ice Blue, Background 2 shape fill color to the plot area of the chart.


Apply the Offset: Bottom Right shape effect from the Outer section of the Shadow gallery to the legend.


Elena wants to add one more element of visual interest on the worksheet.


Insert an online picture using investments as the search text. Select the first picture in the search results. Delete the textbox underneath with the attribution.


Reposition and resize the picture so that the upper-left corner is in cell G1 and the bottom-right corner is in cell K13.


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.


Final Figure 1: Asset Management Worksheet


© Epifantsev/Shutterstock.com, © Monkey Business Images/Shutterstock.com


Final Figure 2: Client Investment Tracker Worksheet




2




Applied Sciences

Architecture and Design

Biology

Business & Finance

Chemistry

Computer Science

Geography

Geology

Education

Engineering

English

Environmental science

Spanish

Government

History

Human Resource Management

Information Systems

Law

Literature

Mathematics

Nursing

Physics

Political Science

Psychology

Reading

Science

Social Science

Home

Blog

Archive

Contact

google+twitterfacebook

Copyright © 2019 HomeworkMarket.com

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

Helping Hand
Homework Guru
University Coursework Help
Top Essay Tutor
Writer Writer Name Offer Chat
Helping Hand

ONLINE

Helping Hand

I am an Academic writer with 10 years of experience. As an Academic writer, my aim is to generate unique content without Plagiarism as per the client’s requirements.

$230 Chat With Writer
Homework Guru

ONLINE

Homework Guru

Hi dear, I am ready to do your homework in a reasonable price and in a timely manner.

$232 Chat With Writer
University Coursework Help

ONLINE

University Coursework Help

Hi dear, I am ready to do your homework in a reasonable price.

$232 Chat With Writer
Top Essay Tutor

ONLINE

Top Essay Tutor

I have more than 12 years of experience in managing online classes, exams, and quizzes on different websites like; Connect, McGraw-Hill, and Blackboard. I always provide a guarantee to my clients for their grades.

$235 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

Brisbane evidence based language test - Assignment 2 - Ray charles innovation in soul music was the combination of - John knowles writing style - Steps of test construction in psychology - Assignment - Physiology and Pharmacology (Due 24 hours) - Is c6h12o6 organic or inorganic - An aptitude for a job implies a - 2013 leaving cert english paper 1 sample answers - Periodic table trends chart - Valery shemetov - Organizational Change and Ethical-Legal Influences in Advanced Practice Nursing - Child development and education 7th edition - Year 6 sentence openers - Britten pears baroque orchestra - University of phoenix mth 217 - Tanglewood case 5 disparate impact analysis - Acute effects of exercise on inspiration - Is sharp el 531wh programmable - Rockingham parcel locker rockingham wa 6168 - Over the years juvenile courts have increasingly come to resemble - Gcu rn to bsn curriculum - 1776 by david mccullough chapter 1 summary - Deism/Naturalism Paper - Colace epocrates - Example of a very poorly implemented database - Handbook of human performance technology principles practices and potential - The case for contamination appiah summary - Handwriting analysis forgery and counterfeiting worksheet - Fire extinguisher signage height requirements - The standard unqualified audit report of a public company - Tryst with destiny speech transcript - Mcnemar chi square calculator - Of mice and men past questions - Nissan banzai veracruz rafael cuervo - Common Descent - 5.9 8 explore network communications - Jarden company has credit sales of - Foul smelling urine icd 10 - British gas home appliance insurance - What is the impact of rapid environmental changes on organizations - Dodecahedron net with tabs - All the ever afters pdf - Use case diagram for grocery store - Video Clip Discussion - 74 turriell point road - Audience analysis memo - Fronius inverter change wifi password - Emperor k - A.I "What is Artificial Intelligence?" - A small good thing - Two Discussion Responses Needed 100 words each 200 words - Routing and switching basics pdf - International brand management los angeles - Great ryburgh parish council - Case Study - Assignment 2 block business letter - Texas a&m toefl requirement - Order # 9580 - Who does macbeth frame for the death of duncan - Psy4500/ Week 3 - Assignment: Analyze the Influence of Genius and Zeitgeist in Groundbreaking Psychological Studies - Dumb shell arts telugu movie names - Avaya site administration 6.0 service pack 14 download - Threethorne cattery for sale - Wk 5, IOP 480: Organizational Readiness Summary - Organizational Week 2 Assignment: Essay 2 - Career development interventions 5th edition apa citation - Keg king font fan - Act government school jobs - Beer lambert law ppt - Wrestling with weight loss case study answers - Writing a speech outline template - RC004 Advocacy and Research. - Bubble diagram interior design - Brisbane city council end of trip facilities - 1 milliradian at 1000 meters - Is sin even or odd - Monster island buddies face reveal - Www clouddeakin - Aa bb cc abc - Zinc and hydrochloric acid lab - Spanish subject pronouns exercises - Assignment: Homeland Security Threats, Risks, and Vulnerabilities - Under 10km speeding fine nsw - Topic 3 DQ1 - Discussion - Ciro model evaluation - The molding department of kennett company - How did erp help improve business operations at shell - Solidworks life cycle assessment - Four central concepts of professional nursing - Research paper on blockchain - PICOT Question Paper - The form of the chant alleluia vidimus stellam is - Sacred time in indigenous religions is - Ethical - Colored periodic table with key - The manager of a store selling tea - Patient centered approaches to nursing