SPREAD SHEET 1
Excel has the ability to import data from the Internet. This week’s individual work gives you an opportunity to use Web Queries to retrieve stock data from the Internet.
For this week’s individual work, please complete the following:
Lab 3: Stock Club Investment Analysis Problem: Several years ago, you and a large group of friends started a stock club. Each year every member invests more money per month. You have decided to create a portfolio worksheet ( Figure 2– 80) that summarizes the club’s current stock holdings so that you can share the information with your group of friends. The club’s portfolio is summarized in Table 2– 8. Table 2– 8 also shows the general layout of the worksheet to be created.
Instructions: Perform the following tasks:
1. Start Excel. Enter the worksheet titles Sock- It- Away Stock Club in cell A1 and Summary of Investments in cell A2. 2. Enter the column titles and data in Table 2– 8 beginning in row 3.
3. Change the column widths and row heights as follows: column A — 11.78; column C — 10.00; columns E and G — 7.44; columns F, H, and I — 13.00; column J — 8.22; row 3 — 56.25 points; row 14 — 27.00 points.
4. Enter the following formulas in row 4 and then copy them down through row 12: a. Enter Formula A in cell F4: Initial Cost = Shares × Initial Price per Share b. Enter Formula B in cell H4: Current Value = Shares × Current Price Per Share c. Enter Formula C in cell I4: Gain/ Loss = Current Value – Initial Cost d. Enter Formula D in cell J4: Percent Gain/ Loss = Gain/ Loss / Initial Cost
5. Compute the totals for initial cost, current value, gain/ loss, and percent gain loss. For the percent gain/ loss in cell J13, copy cell J12 to J13 using the fi ll handle.
6. In cells D14, D15, and D16, enter Formulas E, F, and G using the AVERAGE, MAX, and MIN functions. Copy the three functions across through the range J14: J16. Delete the invalid formula in cell J14. 7. Format the worksheet as follows:
a. Apply the Trek theme to the worksheet.
b. Format the worksheet title with Title cell style. Merge and center across columns A through J.
c. Format the worksheet subtitle with Franklin Gothic Book font, 16 point font size, Black, Text 1 theme font color. Merge and Center across columns A through J.
d. Format the worksheet title and subtitle background with Orange, Accent 1, Lighter 60% theme color and a thick box border.
e. Format row 3 with the Heading 3 cell style and row 13 with the Total cell style.
f. Format the data in rows 4 through 12: center data in column B; format dates in column C to the mm/ dd/ yy date format; range E4: I4 — Accounting number format style with fi xed dollar sign; range E5: I12 — Comma style; range J4: J13 — Percent style with two decimal places; cells F13, H13, and I13 — Accounting Number format with fi xed dollar sign.
g. Format E14: I16 — Currency format with fl oating decimal places; J15: J16 — Percent style with two decimal places.
h. Format J4: J12 — apply conditional formatting so that if a cell in range is less than 0, then cell appears with a pink background color. 8. Spell check the worksheet. Change the name of the sheet tab to Summary of Investments and apply the Orange, Accent 1, Darker 25% theme color to the sheet tab. Update the document properties, and save the workbook using the fi le name, Lab 2- 3 Sock- It- Away Stock Club Summary of Investments.
SPREAD SHEET 2
use Excel’s sorting capabilities to analyze data.
For this week’s lab work, please complete the following:
Lab 2: Sorting, Finding, and Advanced Filtering Problem: Cornelli’s Inc. has many stores across the nation. Depending on the store classifi cation, there are different departments available at the location; however, they do not have to have all the same de-partments. The company uses a table ( Figure 5 – 69) that shows what departments are at each location as well as the store classifi cation. The CEO, Juniper Alvarez, has asked you to sort, query, and determine some statistics from the table. Carefully label each required printout by using the part number and step. If a step results in multiple printouts, label them a, b, c, and so on. Instructions
Part 1: Start Excel and perform the following tasks.
1. Open the workbook Lab 5- 2 Cornelli’s Department Availability Table from the Data Files for Students. See the inside back cover of this book for instructions for downloading the Data Files for Students or see your instructor for information on accessing the fi les required in this book. Do not save the workbook in this part.
2. Complete the following tasks:
a. Sort the records in the table into ascending sequence by location. Austin, TX should appear fi rst in the table. Seattle, WA should appear last. Print the table. Undo the sort.
b. Sort the records in the table by Classifi cation within Location. Select descending sequence for the Classifi cation and ascending sequence for Location. Austin, TX should be the fi rst record. Print the table. Undo the sort.
c. Sort the table by Home within Furniture within Bath within Auto. Apply sort descending for all four fi elds. Sort the table fi rst on Home, then Furniture, then Bath, and fi nally Auto. Those locations with all four departments will rise to the top of the table. Orlando, FL should be the first record.
Instructions Part 2:
Open the workbook Lab 5- 2 Cornelli’s Department Availability Table from the Data Files for Students. Do not save the workbook in this part. Select a cell within the table. If the column heading arrows do not appear, then click the Filter button ( Data tab | Sort & Filter group).
Use the column heading arrows to fi nd the records that meet the criteria in items 1 through 4 below. Use the Show All command on the Filter submenu before starting items 2, 3, and 4. Print the table for each query. You should end up with the following number of records for items 1 through 4: item 1 should have 7; item 2 should have 3; item 3 should have 4; and item 4 should have 1. Close the workbook without saving the changes. Submit the assignment as requested by your instructor.
1. Find all records that have a pharmacy and a grocery department.
2. Find all records that represent locations with more than 7 departments that have a pharmacy and a clothing department.
3. Find all records that have a location in TX using search.
4. Find all records that have a location in CA using search and that are drugstores.
Instructions Part 3:
Open the workbook Lab 5- 2 Cornelli’s Department Availability Table from the Data Files for Students and then save the workbook using the fi le name, Lab 5- 2 Cornelli’s Department Availability Table Final. Perform the following tasks:
1. Add a criteria range by copying the table title and fi eld names ( range A9: M10) to the range A2: M3 ( Figure 5 – 70 on the following page). Change cell A2 to Criteria Area and then color the title area as shown in Figure 5– 70. Use the Name box in the formula bar to name the criteria range ( A3: M4) Criteria.
2. Add an extract range by copying the table title and fi eld names ( range A9: M10) to the range A34: M35 ( Figure 5 – 71). Change cell A34 to Extract Area and then color the title area as shown in Figure 5 – 71. Use the Name box in the formula bar to name the extract range ( range A35: M35) Extract.
3. With the table active, use the Advanced button ( Data tab | Sort & Filter group) to extract records that pass the tests listed below in a through d. Print the worksheet in landscape orientation using the ‘ Fit Sheet on One Page’ option for each extract.
a. Extract the records that represent locations that have all of the departments. You should extract two records.
b. Extract the records that represent superstores that have a pharmacy but not an auto department. You should extract one record.
c. Extract the records that represent locations that have an auto department and have less than 7 departments total. The fi eld Count in column M uses the COUNTIF function to count the number of Ys in a record. A count of 4 means the record represents a location with four departments. You should extract seven records.
d. Extract the records that represent locations with less than three departments. You should extract 4 records.
Why Work with Us
Top Quality and Well-Researched Papers
We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.
Professional and Experienced Academic Writers
We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.
Free Unlimited Revisions
If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.
Prompt Delivery and 100% Money-Back-Guarantee
All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.
Original & Confidential
We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.
24/7 Customer Support
Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.
No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.
Admission Essays & Business Writing Help
An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.
Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.
If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.