2 seperate spread sheets all info is included with files uploaded.

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:

  • In the Lab 3: Stock Club Investment Analysis, EX 132.

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: 

 

  • “In the Lab” Lab 2: Sorting, Finding, and Advanced Filtering, EX 354 – 357.
    • There are 3 parts to this assignment
      • Part 1 – Sorting
      • Part 2 – Finding
      • Part 3 – Extracting
    • Save a separate file for each part of the Lab after completing the final exercise for that section (e.g. Part 2, #4.  Find all the records that have locations in CA and that are drugstores), and use a filename that is descriptive Georgie Hollenbeck_CGS 2510-12 Lab Work 6 Sort.xlsx

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.

 

 

Get 20% Discount on This Paper
Pages (550 words)
Approximate price: -

Try it now!

Get 20% Discount on This Paper

We'll send you the first draft for approval by at
Total price:
$0.00

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.

Our Services

Do My Homework has assembled a team of highly skilled writers with diverse experience in the online writing circles. Our aim is to become a one stop shop for all your Academic/ online writing. Check out below our amazing service!

Essays

Essay Writing Services

At Do My Homework, we prioritize on all aspects that creates a good grade such as impeccable grammar, proper structure, zero-plagiarism, and conformance to guidelines. The principal purpose of essay writing is to present the author's evaluation concerning a singular subject about which they have made. Since Professionalism is the mother of every success, try our team of experienced writers in helping you complete your essays and other assignments.

Admissions

Admission Papers

You have been trying to join that prestigious institution you long yearned for, but the hurdle of an admission essay has become a stumbling block. We have your back, with our proven team that has gained invaluable experience over time, your chance of joining that institution is now! Just let us work on that essay.How do you write an admission essay? How do you begin the essay? For answers, try Quality Custom Writers Now!

Editing

Editing and Proofreading

Regardless of whether you're pleased with your composing abilities, it's never an impractical notion to have a second eye go through your work. The best editing services leaves no mistake untouched. We recognize the stuff needed to polish up a writing; as a component of our editing and proofreading, we'll change and refine your write up to guarantee it's amazing, and blunder free. Our group of expert editors will examine your work, giving an impeccable touch of English while ensuring your punctuation and sentence structures are top-notch.

Coursework

Technical papers

We pride ourselves in having a team of clinical writers. The stringent and rigorous vetting process ensures that only the best persons for job. We hire qualified PhD and MA writers only. We equally offer our team of writers bonuses and incentives to motivate their working spirit in terms of delivering original, unique, and informative content. They are our resources drawn from diverse fields. Therefore your technical paper is in the right hands. Every paper is assessed and only the writers with the technical know-how in that field get to work on it.

Coursework

College Essay Writing

If all along you have been looking for a trustworthy college essay service provider that provides superb academic papers at reasonable prices, then be glad that you search has ended with us. We are your best choice! Get high-quality college essay writing from our magnificent team of knowledgeable and dedicated writers right now!

Coursework

Quality Assignment/Homework Help

We give the students premium quality assignments, without alarming them with plagiarism and referencing issues. We ensure that the assignments stick to the rules given by the tutors. We are specific about the deadlines you give us. We assure you that you will get your papers well in advance, knowing that you will review and return it if there are any changes, which should be incorporated.