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.