spread sheet

1

WEEK # 6 - EXERCISES CHAPTER # 7

Exercise Ch7-1: Level 1 – Sports Wear Retailers Barbara asks you to organize the data for sportswear retailers into an Excel table. The data is currently stored in a text file and is organized by state. You will begin by creating an Excel workbook and then pasting the data from the text file into a worksheet. Complete the following: 1. Use Notepad (or another text editor) to open the text file named Sportswr.txt attached in the assignment and examine the data. Close the file, and then close Notepad. 2. Create a new workbook named 7-1-Sportswear-Retailers-YourName.xlsx and save the file in the Chapter 7 folder. Import the data from the text file Sportswr.txt into a worksheet named Imported Data, starting in cell A1. (If the Creation Date of the property file is not the current date, contact the instructor) 3. In the Column B, create a list of comma-separated values from the data you pasted by concatenating the company name, street address, city, state, ZIP code, and phone number. Separate the name, street address, city, state, ZIP code, and phone number information with commas. Trim each text string in the concatenated value to remove all spaces except for spaces between words. 4. Use the appropriate functions to identify the rows of data that hold the concatenated values in the correct order. 5. Copy and paste using the option paste value all the information of the Imported Data to a new Tab and name it 1-Step. 6. In the 1-Step sort and delete all invalid rows and columns that do not contain the comma- separated values. 6. Copy the result to a new Tab 2-Step and convert the comma-separated values into a structured list with the data for the company, street address, city, state, ZIP code, and phone number appearing in different cells of the worksheet. (Hint: Run the Convert Text to Columns Wizard twice to convert all of the data, being careful not to overwrite any existing data in the process. You can use this process to separate the state from the ZIP code. Be certain to remove any extra spaces remaining at the beginning or end of the data.) 7. Correct any errors in the data that were introduced during the conversion. Remember that the converted data will be in the Tab 2-Step 8. Copy the result to a new tab 3-Step and add a header row with labels to describe the data in each column. 9. Sort the data by state, then by city, and then by company in ascending order. 10. Filter the results to display only those companies in Florida. 11. Add your name and Current Date at the end of the workbook. 12. Save and close the 7-1-Sportswear-Retailers-YourName.xlsx workbook.

2

WEEK # 6 - EXERCISES CHAPTER # 7

Exercise Ch7-2: Level 2- Analyzing Manager Performance at Home Station.

You are a regional manager for Home Station, a national chain of home renovation stores. You are analyzing the weekly sales data for one of the retail stores located in Houston, Texas. The sales data is reported by department and manager. The Houston store manager wants to rotate the department managers in each of the store’s departments so each manager becomes more familiar with the entire store’s operations. You have been assigned the task of determining the impact of rotating the managers on store sales. You will import the sales data from a database into Excel and then create a PivotTable report to summarize the quarterly sales by department and by manager. Complete the following: 1. Create a new Excel workbook and save it as 7-2-Home Station-Houston-YourName.xlsx attached with the data of the assignment. (If the Creation Date of the property file is not the current date, contact the instructor) 2. Import the information from the Sales table in the Sales.accdb database provided into a worksheet named Sales. (If have problem converting the Access Database use the Sales.xlsx File) 3. Create a PivotTable report using the data in the Sales worksheet and place the PivotTable report in a new worksheet named Houston-1 to analyze the sales by department and change the number format of the sales data to currency. 4. Create a new PivotTable named Houston-2 to analyze department sales by quarter. Which department had the highest quarterly sales, and in which quarter did it occur? (Hint: Use the Date field to summarize the dates by quarter.) 6. Create a new PivotTable named Houston-3 and use the Manager Field to analyze department sales by managers. What is the manager that has more sales? 7. Add your name and date at the end of the exercise 8. Save and close the 7-2-Home Station-Houston-YourName.xlsx workbook.