BUSN 420 project

User Generated

xenpxpyvss

Business Finance

Description

Major Project

Attached Files:

Technology plays an important role in supporting decision making. This minor project will focus on supporting decision-making by exploring some of the features within Microsoft Excel including the basic filter, custom filter, conditional formatting, as well as pivot tables.

Let’s assume that you work for a small business and have been asked to perform the following tasks to aid in various decisions:

1. Show all information for only customers who live in the North region.

2. Show all information for only customers who (a) live in the North region, (b) own their homes, and (c) have only one household member.


3. Show all information for customers who have at least 4 household members.


4. Show all information for customers who (a) have spent less than $20 or (b) more than $100.


5. Show all information for all customers highlighting those customers who have spent more than $100.


6. Provide a two-dimensional table that counts the number of customers by the categories of REGION and RENT VS. OWN.


7. Provide a two-dimensional table that both (a) counts the number of customers and (b) sums the TOTAL PURCHASES of customers by the categories of REGION and RENT VS. OWN.


8. Provide a three-dimensional table that counts the number of customers by the categories of REGION, RENT VS. OWN, and NUM HOUSEHOLD.

First, read the attached Extended Learning Module D: Decision Analysis with Spreadsheet Software document. Then, create the requested worksheet summaries listed above. You will need to use Microsoft Excel in order to complete this assignment. Microsoft Excel is available at no cost to Regent students via the Office 365 application listed in the Course Resources section of our Blackboard course. The attached XLMD_Customer.xlsx data file should be used to complete the assignment.

Once you have completed each request, please submit one Excel document including 8 worksheets illustrating each exercise.

Unformatted Attachment Preview

BUSN 420 Major Project Assignment Instructions Technology plays an important role in supporting decision making. This minor project will focus on supporting decision-making by exploring some of the features within Microsoft Excel including the basic filter, custom filter, conditional formatting, as well as pivot tables. Let’s assume that you work for a small business and have been asked to perform the following tasks to aid in various decisions: 1. Show all information for only customers who live in the North region. 2. Show all information for only customers who (a) live in the North region, (b) own their homes, and (c) have only one household member. 3. Show all information for customers who have at least 4 household members. 4. Show all information for customers who (a) have spent less than $20 or (b) more than $100. 5. Show all information for all customers highlighting those customers who have spent more than $100. 6. Provide a two-dimensional table that counts the number of customers by the categories of REGION and RENT VS. OWN. 7. Provide a two-dimensional table that both (a) counts the number of customers and (b) sums the TOTAL PURCHASES of customers by the categories of REGION and RENT VS. OWN. 8. Provide a three-dimensional table that counts the number of customers by the categories of REGION, RENT VS. OWN, and NUM HOUSEHOLD. First, read the attached Extended Learning Module D: Decision Analysis with Spreadsheet Software document. Then, create the requested worksheet summaries listed above. You will need to use Microsoft Excel in order to complete this assignment. Microsoft Excel is available at no cost to Regent students via the Office 365 application listed in the Course Resources section of our Blackboard course. The attached XLMD_Customer.xlsx data file should be used to complete the assignment. Once you have completed each request, please submit one Excel document including 8 worksheets illustrating each exercise. Confirming Pages EX TENDED L E ARN IN G MOD UL E D D E C I S I O N A N A LY S I S W I T H S P R E A D S H E E T S O F T WA R E Student Learning Outcomes 1. Define a list and a list definition table within the context of spreadsheet software and describe the importance of each. 2. Compare and contrast the Filter function and Custom Filter function in spreadsheet software. 3. Describe the purpose of using conditional formatting. 4. Define a pivot table and describe how you can use it to view summarized information by dimension. haa7685x_modD_D.1-D.20.indd 1 12/15/11 8:10 PM Confirming Pages Introduction D.2 Introduction As you read in Chapter 4, technology can and does play a vitally important role in both supporting decision making and, in some instances, actually making decisions or recommendations. In this module, we’ll focus on decision-making support by exploring many of the advanced and productive features of Microsoft Excel. Microsoft Excel is spreadsheet software that allows you to work with any kind of information, with each individual piece of information located in a cell. A cell is the intersection of a row and column and is uniquely identified by its column character and row number. In Figure D.1 you can see two workbooks. The first (the one in the background) shows all the detailed information for a group of customers. The second shows the number of customers by region (North, South, East, and West) and by rent versus own. There are a total of 487 customers (cell D9 in the workbook in the foreground), of which 262 own a home (cell B9) and 225 rent (cell C9). Within this workbook, you can easily see some interesting information. For example, there are 148 customers in the East region while only 98 live in the South region. By region and ownership status, 82 own a home in the East region while only 47 rent in the South region. Figure D.1 Number of Customers by Region and by Rent versus Own Customers by region Customers by rent versus own haa7685x_modD_D.1-D.20.indd 2 Total number of customers 12/15/11 8:10 PM Confirming Pages D.3 Extended Learning Module D Of course, now the question becomes, How is the information in Figure D.1 helpful? Well, it depends on the nature of your decision-making task. If you believe that home owners spend more money than those who rent and want to target advertising to the largest region, the information might be helpful. Then again, it might not be. It could very well be that home owners actually spend less than customers who rent. And perhaps you generate more sales in regions with a lower number of customers. Let’s see how spreadsheet software can help you make better decisions. As we do, we’ll introduce you to some spreadsheet features including Filter, conditional formatting, and pivot tables. Our goal here is not to provide in great detail how each of these work, but rather what’s most important about each one of them in supporting your decision-making tasks. After completing this module, you’ll definitely be able to use all features in their basic forms. We recommend that you continue to explore them in detail. LEARNING OUTCOME 1 Lists What we showed in Figure D.1 was a pivot table. A pivot table is a spreadsheet function that summarizes information by category. In our case, it summarized information by region (the rows) and rent versus own (the column). To create a pivot table (and use many of the other features we’ll discuss in this module), you have to first build a list. You should work along with us on this. Connect to the Web site that supports this text (www.mhhe.com/haag and select XLM/D). There, you can download the file called XLMD_Customer.xls. A list is a collection of information arranged in columns and rows in which each column displays one particular type of information. In spreadsheet software, a list possesses the following characteristics: 1. 2. 3. 4. Each column has only one type of information. The first row in the list contains the labels or column headings. The list does not contain any blank rows. The list is bordered on all four sides by blank rows and blank columns (it may not have a blank line above it, if it starts in the first row). Take a look at the workbook in Figure D.2. It contains detailed information about our customers. In fact, we used this very list to generate the pivot table in Figure D.1. First, notice that each column contains only one type of information: column A contains CUST ID, column B contains REGION, and so on. Second, notice that the first row (row 1) contains the labels or column headings. Third, if you scroll down completely through the list, you’ll notice that there are 487 customers and there are no blank rows. Finally, notice that the list is bordered on all sides (except the top) by blank rows and columns. So, this is a list according to the four characteristics we listed. We’re going to be working extensively with this list throughout this module, so let’s take a little time to explore the information in it. The columns of information include A. CUST ID—A unique ID for each customer B. REGION—The region in which the customer lives (North, South, East, or West) C. RENT VS. OWN—Whether the customer rents or owns a home D. NUM HOUSEHOLD—Number of family members in the household E. ANNUAL INCOME—Total combined annual income of all family members haa7685x_modD_D.1-D.20.indd 3 12/15/11 8:10 PM Confirming Pages Lists D.4 Labels or column headings Figure D.2 Each column has only one type of information The Complete List of Customers F. TOTAL PURCHASES—Dollar total of all purchases made by the customer within the last six months G. NUM PURCHASES—Count of all purchases made by the customer within the last six months What we listed above is called a list definition table, a description of a list by column. List definition tables are important. If you can create one just as we did, then you can create a list in a workbook with the appropriate characteristics. If you can’t, then you may not be able to use many of the features we’re about to show you. With the good solid list in place, you’re now ready to start exploring many of the decision support features in Excel. Let’s assume that you work for our hypothetical retail company and have been asked to perform the following tasks to aid in various decisions: 1. Show all information for only customers who live in the North region. 2. Show all information for only customers who (a) live in the North region, (b) own their homes, and (c) have only one household member. 3. Show all information for customers who have at least 4 household members. 4. Show all information for customers who (a) have spent less than $20 or (b) more than $100. 5. Show all information for all customers highlighting those customers who have spent more than $100. 6. Provide a two-dimensional table that counts the number of customers by the categories of REGION and RENT VS. OWN. 7. Provide a two-dimensional table that both (a) counts the number of customers and (b) sums the TOTAL PURCHASES of customers by the categories of REGION and RENT VS. OWN. 8. Provide a three-dimensional table that counts the number of customers by the categories of REGION, RENT VS. OWN, and NUM HOUSEHOLD. We will use Excel to perform all of these tasks throughout the remainder of this module. haa7685x_modD_D.1-D.20.indd 4 12/15/11 8:10 PM Confirming Pages D.5 Extended Learning Module D LEARNING OUTCOME 2 Basic Filter Working with small lists that can be displayed in their entirety on a screen is seldom a problem. With a small list you can see the entire domain of information without scrolling up or down. But our list is much larger, containing 487 customers. So, you have to scroll through it to see all the information. If you were looking for specific information, such as all the customers in the North region (your first task in the list on the previous page), you could sort using the REGION column but you still get all the information (not to mention that customers in the North would come after the customers in the East region, alphabetically). To quickly create smaller lists out of a much larger list, you can use the Filter function. The Filter function filters a list and allows you to hide all the rows in a list except those that match criteria you specify. To filter a list with the Filter function, perform the following steps (see Figure D.3): Figure D.3 Using Basic Filter to See Customers in the North Region 1. Click in any cell within the list 2. From the menu bar, click on Data and then click on Filter When you click on Data and Filter, Excel places list arrow boxes by each column heading The default filtering is all values Shows only customers in the North REGION Deselect all regions except North and click on OK haa7685x_modD_D.1-D.20.indd 5 12/15/11 8:10 PM Confirming Pages Basic Filter D.6 Customers in the North REGION who own a home with only 1 household member Figure D.4 A List Generated with Three Filters Once you complete those two steps, Excel will place list box arrows next to each label or column heading. Now, all you have to do is click on the appropriate list box arrow and select the type of filtering you want. In Figure D.3, you can see that we clicked on the REGION list arrow box. Excel then presented us with a number of options for filtering within REGION. We deselected all regions except North and then clicked on the OK button. Excel then presented us with a filtered list of only those customers in the North region. Our list is still quite long, but it does show only customers in the North. To turn off the Filter function, from the menu bar, click on Data, then click on Filter. When using the Filter function, you’re not limited to working with just one column. In Figure D.3, we filtered using the REGION column. Now, what if you want a filtered list of those customers in the North who own a home and have only one household member (your second task in the list on page D.4)? That’s easy. Click in the RENT VS. OWN list arrow box and choose only Own. Then, click in the NUM HOUSEHOLD list arrow box and choose only 1. That will show you the complete list (4 to be exact) of customers in the North who own a home and have only one household family member (see Figure D.4). It’s important to be clear what Excel is doing to your list when using the Filter function. No matter how you use the Filter function, Excel is simply hiding the rows you don’t want to see. It is not eliminating or deleting them. You can see this in Figure D.4 by looking at the row numbers. Notice that they present the physical row number of each record of information according to where it is located in the original unfiltered list. To un-Filter a list, you can do either of the following: 1. Turn off the Filter function by clicking on Data in the menu bar and then clicking on Filter. 2. Turn off selected column filtering by clicking on the appropriate list arrow box and clicking on Clear Filter from “columnname” where columnname is the name of the column. haa7685x_modD_D.1-D.20.indd 6 12/15/11 8:10 PM Confirming Pages D.7 Extended Learning Module D Custom Filter The basic Filter function allows you to create sublists using exact match criteria: REGION must be North, NUM HOUSEHOLD must be 1, and so forth. But what if you want to know all those customers who have at least four people in their households (your third task in the list on page D.4)? In that case, you can’t use the basic Filter function—you need to use the Custom Filter function. The Custom Filter function allows you to hide all the rows in a list except those that match criteria, besides “is equal to,” you specify. Let’s see how to use the Custom Filter function. Given that you want to see a list of all customers who have at least four people in their households, perform the following steps: Figure D.5 1. 2. 3. 4. 5. 6. Make sure you can see the entire list with the Filter function turned on Click on the NUM HOUSEHOLD list arrow box Select Number Filters Click on Greater Than from the list of Boolean operations Type the number 3 in the upper-right box of the Custom AutoFilter dialog box Click on OK Using a Custom Filter To use Custom Filter, click on Number Filters and then choose the appropriate Boolean operater haa7685x_modD_D.1-D.20.indd 7 Only customers with more than 3 people in their households 12/15/11 8:10 PM Confirming Pages Custom Filter D.8 Excel does the rest and shows you the appropriate list of customers with at least four people in their households. You should notice in Figure D.5 that the Custom Filter box allows you to enter two criteria for creating a filtered list. So, you can easily create a Custom Filter that answers the following question: What customers have spent less than $20 or more than $100 in the past six months (your fourth task in the list on page D.4)? In Figure D.6, we’ve shown you how to do that along with the result. To create this type of filtering, click on Custom Filter within Number Filters and then Select/enter the appropriate values and boolean operators. Only customers who have spent less than $20 or more than $100 Figure D.6 Using a Custom Filter with Multiple Criteria haa7685x_modD_D.1-D.20.indd 8 12/15/11 8:10 PM Confirming Pages D.9 Extended Learning Module D LEARNING OUTCOME 3 Conditional Formatting When you use Filter (either basic or custom), in a way you’re highlighting information you want to see by basically hiding the other information you don’t. As an alternative, you might want to highlight certain information while still being able to see all the other information. If so, you can use conditional formatting. Conditional formatting highlights the information in a cell that meets some criteria you specify. For example, what if you still wanted to be able to scroll through the entire list of customers but also wanted to have all TOTAL PURCHASES greater than $100 highlighted (your fifth task in the list on page D.4). This is a simple process in Excel. To do that, perform the following steps (see Figure D.7): 1. Select the entire TOTAL PURCHASES column (move the pointer over the F column identifier and click once) 2. From the menu bar, click on Home and then Conditional Formatting within Styles 3. Select Highlight Cells Rules 4. Click on Greater Than 5. Type the number 100 in the left box of the Greater Than dialog box 6. Click on OK Figure D.7 The Steps in Applying Conditional Formatting to Highlight Information haa7685x_modD_D.1-D.20.indd 9 12/15/11 8:10 PM Confirming Pages Conditional Formatting D.10 Figure D.8 Only customers whose TOTAL PURCHASES exceed $100 are highlighted in red The Result of Applying Conditional Formatting As you can see in Figure D.8, Excel left the list intact and highlighted all cells in the TOTAL PURCHASES column in which the value exceeded $100. The default “highlighting” is dark red text with a light red fill. You can change this in the Greater Than dialog box by clicking on the pull-down arrow for the right box. You can remove conditional formatting in a couple of different ways: • Option #1 1. 2. 3. 4. Click anywhere in the list Click on Conditional Formatting Select Clear Rules Click on Clear Rules from Entire Sheet • Option #2 1. 2. 3. 4. haa7685x_modD_D.1-D.20.indd 10 Select the entire column in which conditional formatting appears Click on Conditional Formatting Select Clear Rules Click on Clear Rules from Selected Cells 12/15/11 8:10 PM Confirming Pages D.11 Extended Learning Module D LEARNING OUTCOME 4 Pivot Tables Now, let’s return to our original pivot table in Figure D.1 on page D.2. Formally defined, a pivot table enables you to group and summarize information. That’s just what we did in Figure D.1. We created a two-dimensional pivot table that displayed a count of customers by REGION and by RENT VS. OWN (your sixth task in the list on page D.4). Of all the Excel decision-support features we demonstrate in this module, pivot tables take the most steps to create, but they also tend to yield highly valuable information. To create any two-dimensional pivot table, follow the two steps below (and see Figure D.9): 1. From the menu bar, click on Insert and then PivotTable 2. Click on OK in the Create PivotTable dialog box The first step above simply specifies that you want to insert a pivot table. In the second step, we accepted the default pivot table settings by clicking on OK. While in the PivotTable dialog box, there are other options you should consider exploring on your own. • Selecting the information for the pivot table—the default is the entire table. You can select another table or choose to highlight a subset of the current table. • Getting information from another source—you can choose to use an external data source, such as a Word or Access table. • Location of the pivot table—the default is to place the pivot table in a new worksheet. You can choose to place it in the current worksheet if you wish. After you complete the two steps above, you will then see the skeletal structure of a pivot table as shown in Figure D.10. Figure D.9 The First Steps in Creating a TwoDimensional Pivot Table haa7685x_modD_D.1-D.20.indd 11 12/15/11 8:10 PM Confirming Pages Pivot Tables D.12 Figure D.10 Pivot table PivotTable Field List Summary of the pivot table The Skeletal Structure of a Pivot Table The screen in Figure D.10 has many elements to it. In the upper-left corner of the worksheet is the actual pivot table into which we will drop fields of information. The upper-right area contains the PivotTable Field List, the list of fields in the original table that we can work with. The lower-right area contains a sort of summary of the pivot table as you build and use it. To add fields of information to the pivot table, you drag and drop field names (i.e., column headings or labels) from the PivotTable Field List and drop them into one of two places. First, you can drop them into the summary in the bottom-right portion of the screen. Second, you can drop them into the appropriate locations within the pivot table itself. It really doesn’t matter which you choose; both achieve the same result. For our demonstration, we’ll be dropping field names into the actual pivot table. haa7685x_modD_D.1-D.20.indd 12 12/15/11 8:10 PM Confirming Pages D.13 Extended Learning Module D Drag and drop RENT VS. OWN here Drag and drop CUST ID here Drag and drop REGION here Figure D.11 Creating a Pivot Table by Dragging and Dropping Information Recall that we are attempting to build a two-dimensional table that looks like the one in Figure D.1 on page D.2. So, you know that the row information is by REGION. To achieve this, drag the REGION field name from the PivotTable Field List to the pivot table and drop it into the area marked “Drop Row Fields Here” (see Figure D.11). You also know that the column information is by RENT VS. OWN. So, drag that field name from the PivotTable Field List and drop it into the area marked “Drop Column Fields Here.” Finally, you need to place something in the main area of the pivot table that will enable you to count customers by region and rent versus own. The simplest way to achieve this is to drag CUST ID from the PivotTable Field List and drop it into the area marked “Drop Data Items Here.” What you will then have is a pivot table that looks like the screen in Figure D.11, which is not at all what we want. Why? haa7685x_modD_D.1-D.20.indd 13 12/15/11 8:10 PM Confirming Pages Pivot Tables D.14 Now, the pivot table counts the number of customers. Figure D.12 The Pivot Table with the Information You Want When you drop information into the main area of a pivot table, the default aggregation or summarization is by summation. You don’t want to sum customer IDs—that doesn’t make any sense. What you want to do is count them. To change this, perform the following steps (see Figure D.12): 1. 2. 3. 4. Click on any cell in the main area of the pivot table Click on the Field Settings button in the menu area Click on Count in the Summarize by tab of the Value Field Settings dialog box Click on OK in the same dialog box The final screen in Figure D.12 shows the correct information. Also, take a look at the lower-right portion of the screen which contains the summary of the pivot table. It shows the columns as RENT VS. OWN, the rows as REGION, and the values (the main area of the pivot table) as Count of CUST ID. We now have a pivot table that shows a count of customers by REGION and RENT VS. OWN. But depending on what decision you’re trying to make, that may not be enough information. What else might be helpful? Again, depending on the decision, it might be helpful to also know the total of all purchases by REGION and RENT VS. haa7685x_modD_D.1-D.20.indd 14 12/15/11 8:10 PM Confirming Pages D.15 Extended Learning Module D Figure D.13 An Added Field of Information to a Pivot Table Drag and drop TOTAL PURCHASES here to obtain a summary of another dimension of information OWN (your seventh task in the list on page D.4). If so, you don’t need to create another pivot table. You can simply add the field TOTAL PURCHASES to the main area of the pivot table. To do so, drag that label from the PivotTable Field List and drop it into the main area of the pivot table. Figure D.13 shows the result. Is the information helpful? Again, it depends on the decision you’re trying to make. But adding another piece of information to the main area of a pivot table is easy, and it does illustrate the true productivity of spreadsheet software. Your final task in the list on page D.4 is to create a three-dimensional pivot table that counts the number of customers by the categories of REGION, RENT VS. OWN, and NUM HOUSEHOLD. The result will look similar to the two-dimensional pivot table in Figure D.13 with two exceptions. First, you will not include the sum of TOTAL haa7685x_modD_D.1-D.20.indd 15 12/15/11 8:10 PM Confirming Pages Pivot Tables The current depth dimension is for “all” customers, regardless of how many members are in a household. D.16 Figure D.14 Creating a ThreeDimensional Pivot Table PURCHASES in the main area of the pivot table. Second, you will add depth to the pivot table, making it a three-dimensional pivot table. In short, you do this by dragging the NUM HOUSEHOLD label from the PivotTable Field List to the pivot table and dropping it into the area marked “Drop Page Fields Here.” In Figure D.14, you can see in the upper-left screen that we created a two-dimensional pivot table showing a count of customers by REGION and RENT VS. OWN. This is the same two-dimensional pivot table we created in Figure D.12 on page D.14. To add depth to the pivot table, we dragged the NUM HOUSEHOLD label from the PivotTable Field List to the pivot table and dropped it into the area marked “Drop Page Fields Here.” Notice that the new pivot table (the lower right screen in Figure D.14) still looks like a two-dimensional pivot table and provides the same information in the main area of the pivot table. That’s because the default display for a three-dimensional pivot table is to show all summarized information for the depth. You can tell this because to the right of NUM HOUSEHOLD in cell A1 is the word “All.” haa7685x_modD_D.1-D.20.indd 16 12/15/11 8:11 PM Confirming Pages D.17 Extended Learning Module D Click on the NUM HOUSEHOLD pulldown arrow to see your depth options. Figure D.15 Viewing Different Depths in a Three-Dimensional Pivot Table By selecting 4 in the NUM HOUSEHOLD depth dimension, you see a summary of customers for only that criteria. To view the count of your customers by specific values for NUM HOUSEHOLD, simply click on the list box arrow immediately to the right of the word “All” as we did in the top screen in Figure D.15. You then click on the value you want in NUM HOUSEHOLD for displaying the count of customers for that value by REGION and RENT VS. OWN. We clicked on 4 and then OK. The bottom screen in Figure D.15 shows the result. It shows some interesting information that might help you make a decision. For example, in the West region there are 20 customers who own their homes and only 11 who rent their homes. Furthermore, across all regions there are 66 customers who own their homes and only 40 who rent. Again, is this helpful information? That depends on the decision you’re trying to make. The three-dimensional pivot table feature in Excel is a powerful one. If you recall our discussions of data warehouses in Chapter 3, you can actually build a data warehouse with rows, columns, and layers by simply creating a three-dimensional pivot table in Excel. By selecting different values for the page (depth) field, you are actually bringing layers of information to the front. haa7685x_modD_D.1-D.20.indd 17 12/15/11 8:11 PM Confirming Pages Back to Decision Support D.18 Back to Decision Support Let’s take a break from the computer for a moment and discuss what we’ve just demonstrated within the context of decision support. After all, you don’t need to learn the various tools of spreadsheet software just because “they are there.” You should learn them because they will be beneficial to you. In general, the spreadsheet features we’ve just shown you give you the ability to look at vast amounts of information quickly. Indeed, our customer workbook contained information on 487 customers. Can you imagine trying to summarize or aggregate information on 48,700 customers without the use of spreadsheet software? Even creating simple totals and subtotals would be a daunting task. FILTER The Filter function (either basic or custom) helps you quickly create a view of a partial list of information. The basic Filter function creates a partial list based on exact match criteria, while the custom Filter function allows you to specify ranges (e.g., greater than, less than, and so on). The purpose of the Filter function is really to help you quickly focus on only the information that’s important to you by “hiding” the information that isn’t. It’s rather like having a very good search engine that gives you only a list of useful articles to help you write a term paper. CO NDITIONAL FORM ATTI NG Conditional formatting maintains the view of the entire list of information but highlights key pieces of information that you may be looking for. This gives you the ability to see the entire list of information but quickly draws your attention to specific information. PIVOT TABLE A pivot table (either two- or three-dimensional) helps you quickly aggregate or summarize information by dimension. This gives you a nice overview of the information without bogging you down in any of the details. Further, a pivot table can help you see relationships among the information. If you look back at the pivot table in Figure D.13 on page D.15, you can see the relationship between the number of customers and their total purchases within REGION and RENT VS. OWN. These types of relationships can certainly be insightful. Can any of these tools or functions make a decision for you? Definitely not. But they can help you make that decision. That’s why spreadsheet software is often called decision support software. haa7685x_modD_D.1-D.20.indd 18 12/15/11 8:11 PM Confirming Pages D.19 Extended Learning Module D SUMMARY: STUDENT LEARNING OUTCOMES REVISITED 1. Define a list and a list definition table within the context of spreadsheet software and describe the importance of each. A list is a collection of information arranged in columns and rows in which each column displays one particular type of information. A list definition table is a description of a list by column. Lists are important within the context of spreadsheet software because they enable you to use such spreadsheet features as AutoFilter, conditional formatting, and pivot tables. Creating a list definition table is important because it requires you to adhere to the necessary rules for creating a list. 2. Compare and contrast the Filter function and Custom Filter function in spreadsheet software. The Filter function filters a list and allows you to hide all the rows in a list except those that match specific criteria you specify. The Custom Filter function allows you to hide all the rows in a list except those that match criteria, besides “is equal to,” you specify. So, the basic Filter function makes use of “is equal to” as the criteria, while the Custom Filter function allows you to use other criteria such as greater than, less than, and so on. 3. Describe the purpose of using conditional formatting. Conditional formatting highlights the information in a cell that meets some criteria you specify. So, conditional formatting allows you to view the entire list while having certain information called to your attention. 4. Define a pivot table and describe how you can use it to view summarized information by dimension. A pivot table enables you to group and summarize information. When creating a pivot table, you create dimensions of information by specifying how information is to be summarized by dimension. You define the dimensions by dragging and dropping information labels or column headings into the row, column, and page areas of a pivot table. KEY TERMS AND CONCEPTS Conditional formatting, D.9 Custom Filter function, D.7 Filter function, D.5 List, D.3 List definition table, Pivot table, D.11 D.4 ASSIGNMENTS AND EXERCISES 1. WHAT PRODUCTION PROBLEMS DO YOU HAVE? For this assignment use XLMD_ Production.xls. Its list definition table is as follows: A. BATCH—A unique number that identifies each batch or group of products produced B. PRODUCT—A unique number that identifies each product C. MACHINE—A unique number that identifies each machine on which products are produced D. EMPLOYEE—A unique number that identifies each employee producing products haa7685x_modD_D.1-D.20.indd 19 E. BATCH SIZE—The number of products produced in a given batch F. NUM DEFECTIVE—The number of defective products produced in a given batch It seems you have some real problems. There are an unacceptable number of defective products being produced. Your task is to use some combination of Filter, conditional formatting, and pivot tables to illustrate where the problems seem to be concentrated, perhaps by product, by employee, by machine, or even by batch size. Based on your analysis, recommend how to correct the problems. 12/15/11 8:11 PM Confirming Pages Assignments and Exercises 2. EVALUATING TOTAL PURCHASES AND ANNUAL INCOME Using XLMD_Customer.xls, create a pivot table that illustrates the relationship between TOTAL PURCHASES and ANNUAL INCOME. What trends do you see in the information? Suppose your task is to concentrate marketing efforts and resources. On which annual income level would you concentrate? Why? If you were the marketing manager, what additional information would be helpful as you make your decision? Where would you be able to obtain such information? 3. FINDING OUT INFORMATION ABOUT YOUR EMPLOYEES Suppose you own a small business and have a workbook with the following list: A. ID—Unique employee’s identification number B. First Name—Employee’s first name C. Last Name—Employee’s last name D. Department—Employee’s department E. Title—Employee’s job title F. Salary—Employee’s annual salary G. Hire Date—Date employee was hired H. Birth Date—Employee’s birthday I. Gender—Female (F) or Male (M) J. Clearance—N (none), C (confidential), S (secret), or TS (top secret) You can obtain this workbook from the Web site that supports this text (www.mhhe.com/ haag and select XLM/D). Its filename is XLMD_ Employee.xls. Perform the following tasks: a. Create a pivot table that shows average salary by gender within department. b. Create a pivot table that shows the number of employees by clearance. c. Use conditional formatting to highlight those employees in the Engineering department. d. Use conditional formatting to highlight those employees who have no clearance (none). e. Use basic Filter to show only those employees who have top secret clearance (TS). f. Use Custom Filter to show only those employees who earn more than $50,000. 4. EXPLORING INFORMATION AT B&B TRAVEL Benjamin Travis and Brady Austin are haa7685x_modD_D.1-D.20.indd 20 D.20 co-owners of B&B Travel Consultants, a medium-size business in Seattle with several branch offices. B&B specializes in selling cruise packages. Ben and Brady maintain a workbook that contains the following list for each cruise package sale: A. LOCATION #—A unique number that identifies which office location recorded the sale B. TRAVEL AGENT #—A unique number that identifies which travel consultant recorded the sale C. CRUISE LINE—The name of the cruise line for which the package was sold D. TOTAL PACKAGE PRICE—The price charged to the customer for the package E. COMMISSION—The amount of money B&B made from the sale of the package Ben and Brady have decided to scale back their operations. So, they’re looking to you for help. The workbook name is XLMD_Travel.xls and you can find it on the Web site that supports this text at www.mhhe.com/haag (select XLM/D). Using Filter, conditional formatting, and pivot tables, prepare a short report that answers each of the following questions and illustrates the justification for your answers. a. Which, if any, location should be closed? b. Which, if any, travel consultants should be downsized? c. On which cruise lines should B&B focus its sales efforts? 5. CREATE A LIST FOR A BOOKSTORE Suppose that you’re the manager for your school’s bookstore. Your task is to create a list in a workbook that contains information about the textbooks it sells. In addition to tracking price, first author name, and publisher, identify five other pieces of information for each textbook. For this list, first provide a list definition table. Second, enter some information for 20 textbooks. Third, illustrate the use of the basic Filter function, the Custom Filter function, conditional formatting, and pivot tables. Finally, address how your bookstore might be able to use this information to support its decision-making tasks. 12/15/11 8:11 PM CUST ID REGION RENT VS. OWN NUM HOUSEHOLD ANNUAL INCOME 1 North Own 1 10K-25K 2 South Own 4 100K+ 3 North Rent 3 25K-50K 4 West Own 5 25K-50K 5 East Own 6 50K-100K 6 South Own 2 10K-25K 7 East Own 3 100K+ 8 West Rent 4 25K-50K 9 North Own 5 50K-100K 10 West Rent 7 25K-50K 11 North Own 1 50K-100K 12 East Own 2 100K+ 13 South Own 5 10K-25K 14 South Own 4 25K-50K 15 South Rent 3 50K-100K 16 West Own 5 25K-50K 17 North Own 6 50K-100K 18 East Rent 2 10K-25K 19 West Own 3 25K-50K 20 North Rent 1 50K-100K 21 East Own 4 50K-100K 22 South Own 3 50K-100K 23 West Rent 5 25K-50K 24 North Rent 6 10K-25K 25 East Rent 7 100K+ 26 South Own 3 10K-25K 27 West Rent 2 25K-50K 28 North Own 4 50K-100K 29 East Own 5 50K-100K 30 South Rent 4 50K-100K 31 West Own 2 25K-50K 32 East Rent 3 25K-50K 33 East Own 1 50K-100K 34 North Own 6 100K+ 35 North Own 5 10K-25K 36 West Rent 4 100K+ 37 East Rent 1 25K-50K 38 South Rent 5 50K-100K 39 East Rent 3 50K-100K 40 West Own 6 50K-100K 41 North Own 7 25K-50K 42 East Own 3 50K-100K 43 South Rent 2 50K-100K 44 West Rent 4 25K-50K 45 East Own 5 25K-50K 46 South Own 3 10K-25K 47 West Own 7 25K-50K 48 East Rent 3 100K+ 49 South Rent 2 10K-25K 50 North Own 6 25K-50K 51 South Own 5 50K-100K TOTAL PURCHASES $ 38 $ 30 $ 19 $ 21 $ 35 $ 27 $ 26 $ 25 $ 30 $ 26 $ 21 $ 29 $ 20 $ 27 $ 19 $ 19 $ 129 $ 99 $ 99 $ 119 $ 149 $ 165 $ 119 $ 129 $ 165 $ 99 $ 155 $ 115 $ 129 $ 99 $ 169 $ 40 $ 36 $ 45 $ 29 $ 28 $ 46 $ 50 $ 22 $ 20 $ 19 $ 31 $ 30 $ 18 $ 31 $ 35 $ 28 $ 29 $ 35 $ 30 $ 38 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 West North East South West North East West East South East East South West East North North North South West West North East North East South North East West North East South North West East South West East North North West South East South West West West East North South West East Own Rent Rent Own Rent Rent Rent Rent Rent Own Own Own Rent Own Own Rent Rent Own Rent Rent Rent Own Own Own Rent Own Rent Own Own Rent Own Rent Rent Own Rent Rent Own Own Rent Own Rent Rent Own Rent Rent Rent Rent Rent Own Own Rent Own 3 1 5 6 3 4 5 7 2 3 1 4 2 3 5 4 6 5 7 4 5 3 6 5 4 2 3 4 6 5 4 3 2 4 6 4 1 2 3 2 3 7 6 5 4 5 6 3 4 2 1 3 50K-100K 25K-50K 25K-50K 25K-50K 10K-25K 100K+ 100K+ 10K-25K 25K-50K 25K-50K 25K-50K 50K-100K 50K-100K 25K-50K 50K-100K 50K-100K 100K+ 100K+ 100K+ 25K-50K 50K-100K 50K-100K 100K+ 10K-25K 100K+ 25K-50K 50K-100K 10K-25K 50K-100K 25K-50K 25K-50K 25K-50K 50K-100K 100K+ 50K-100K 25K-50K 50K-100K 50K-100K 100K+ 100K+ 100K+ 25K-50K 50K-100K 50K-100K 100K+ 10K-25K 100K+ 25K-50K 50K-100K 10K-25K 50K-100K 10K-25K $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 20 40 30 40 62 22 25 27 22 30 31 22 25 24 36 123 19 23 19 25 129 119 109 179 145 119 129 189 155 78 61 97 63 39 95 86 85 59 84 144 26 31 19 30 29 25 24 36 29 99 169 179 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 North South West East North South West North North East North East North West South East West South East North East North South West North East North West South East North East East North South West East North South West East North East West North East North East West North South East Rent Own Rent Own Rent Rent Rent Own Own Own Rent Rent Rent Own Rent Rent Rent Rent Rent Rent Own Rent Rent Rent Own Own Rent Own Own Rent Own Rent Own Own Rent Rent Rent Rent Own Own Rent Rent Own Own Rent Rent Own Own Own Own Rent Own 4 5 4 6 3 4 5 7 2 3 5 3 2 4 2 1 3 2 4 5 6 3 4 6 2 4 6 3 4 7 4 5 6 2 3 4 5 1 2 3 4 5 4 3 6 2 4 3 3 5 4 6 100K+ 25K-50K 50K-100K 10K-25K 50K-100K 25K-50K 25K-50K 25K-50K 50K-100K 100K+ 50K-100K 25K-50K 50K-100K 50K-100K 100K+ 100K+ 100K+ 25K-50K 50K-100K 50K-100K 100K+ 10K-25K 50K-100K 50K-100K 25K-50K 25K-50K 50K-100K 100K+ 10K-25K 100K+ 25K-50K 50K-100K 50K-100K 50K-100K 25K-50K 50K-100K 50K-100K 25K-50K 25K-50K 10K-25K 25K-50K 100K+ 100K+ 25K-50K 50K-100K 10K-25K 50K-100K 25K-50K 25K-50K 25K-50K 50K-100K 100K+ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 119 99 179 185 179 145 119 99 155 119 149 56 50 86 45 95 86 97 40 50 59 75 68 63 56 86 45 78 68 95 95 39 56 31 29 41 62 51 37 19 38 56 41 51 19 29 41 19 30 19 21 23 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 North West East South West East South North East West South East North East South West West East South West East North East South East North South West East West North East North East West North South East North West East South West East South North East West South East North East Own Own Own Own Rent Own Own Own Rent Rent Own Own Own Own Rent Rent Rent Own Rent Own Rent Own Own Own Rent Rent Own Rent Own Own Own Rent Own Rent Own Own Own Own Rent Own Own Rent Own Rent Own Own Rent Rent Rent Own Rent Own 7 2 3 1 2 4 2 4 5 6 4 2 4 3 5 4 6 4 3 1 2 4 7 4 6 3 7 2 1 4 5 3 2 4 5 2 1 4 2 5 3 6 5 7 4 5 6 3 2 4 5 3 50K-100K 25K-50K 50K-100K 50K-100K 100K+ 100K+ 100K+ 25K-50K 50K-100K 50K-100K 100K+ 10K-25K 50K-100K 50K-100K 25K-50K 25K-50K 50K-100K 100K+ 50K-100K 100K+ 100K+ 100K+ 25K-50K 50K-100K 50K-100K 100K+ 10K-25K 100K+ 25K-50K 50K-100K 10K-25K 50K-100K 25K-50K 25K-50K 25K-50K 50K-100K 100K+ 50K-100K 25K-50K 50K-100K 50K-100K 100K+ 100K+ 100K+ 25K-50K 50K-100K 50K-100K 100K+ 10K-25K 100K+ 25K-50K 25K-50K $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 29 18 26 20 76 40 41 24 26 25 26 23 27 25 34 24 21 40 19 30 35 24 26 15 18 28 24 30 30 23 28 21 28 22 28 30 28 25 29 28 30 19 30 149 155 115 119 99 119 149 145 119 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 South North East South West West East South West East North East South East North South West East West North East North East West North South East North West South South West North East West North East South West North East South West North East South West East East North North West Own Rent Own Rent Own Own Own Rent Rent Rent Rent Own Own Own Rent Rent Own Own Own Rent Rent Own Own Own Rent Rent Own Rent Rent Rent Rent Rent Own Own Own Rent Own Own Rent Rent Own Rent Rent Rent Own Own Own Rent Own Rent Own Own 5 4 6 4 5 6 7 1 3 2 5 4 6 7 3 2 4 5 4 3 4 6 5 4 2 3 1 7 3 4 2 5 3 4 5 6 4 5 6 7 4 3 2 1 5 4 6 3 2 1 5 4 50K-100K 50K-100K 50K-100K 100K+ 10K-25K 50K-100K 50K-100K 25K-50K 25K-50K 50K-100K 10K-25K 100K+ 100K+ 10K-25K 100K+ 50K-100K 50K-100K 25K-50K 25K-50K 10K-25K 50K-100K 25K-50K 50K-100K 100K+ 50K-100K 50K-100K 50K-100K 50K-100K 50K-100K 25K-50K 25K-50K 100K+ 10K-25K 100K+ 100K+ 100K+ 25K-50K 25K-50K 50K-100K 50K-100K 100K+ 25K-50K 50K-100K 10K-25K 50K-100K 25K-50K 25K-50K 25K-50K 50K-100K 100K+ 50K-100K 25K-50K $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 129 99 119 185 119 99 109 179 169 179 145 119 165 129 159 37 55 63 56 67 60 50 49 56 60 59 59 60 68 84 69 97 61 75 76 98 95 59 44 97 68 145 119 129 99 119 185 119 99 60 50 49 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 West East North East South East North South West East West North East North East West North South East North West South South South East North West East South West East South North East West South East North East South North East South West West East South West East North East South Rent Own Rent Rent Own Rent Rent Own Own Rent Own Rent Rent Own Rent Rent Rent Rent Rent Own Own Rent Own Rent Own Rent Own Rent Rent Rent Own Own Own Rent Rent Rent Own Rent Rent Rent Rent Rent Rent Own Rent Rent Rent Own Own Rent Own Own 6 7 3 2 4 5 3 2 4 3 4 3 4 5 4 6 5 7 1 2 3 4 3 5 3 3 2 4 5 6 5 4 3 5 4 6 4 6 1 2 3 4 7 4 3 1 4 2 5 3 6 4 50K-100K 50K-100K 100K+ 100K+ 100K+ 25K-50K 50K-100K 50K-100K 100K+ 10K-25K 50K-100K 50K-100K 25K-50K 25K-50K 50K-100K 100K+ 50K-100K 100K+ 100K+ 100K+ 25K-50K 50K-100K 50K-100K 100K+ 10K-25K 100K+ 25K-50K 50K-100K 10K-25K 50K-100K 25K-50K 25K-50K 25K-50K 50K-100K 100K+ 50K-100K 25K-50K 50K-100K 50K-100K 100K+ 100K+ 100K+ 25K-50K 50K-100K 50K-100K 100K+ 10K-25K 100K+ 25K-50K 25K-50K 50K-100K 50K-100K $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 56 60 59 59 60 68 84 69 97 61 75 76 98 95 59 44 97 68 145 85 59 84 28 26 31 19 30 29 25 24 36 29 99 169 179 119 99 56 50 86 45 95 86 97 40 50 59 75 68 26 15 18 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 East North South West East West North East North East West North South East North West South South West North East West North East South West North East South West North East South West East East North North West West East North East South East North South West East West North East Rent Own Rent Own Own Rent Rent Rent Rent Own Own Rent Rent Own Own Rent Rent Own Own Own Own Rent Own Own Own Own Own Rent Own Own Own Rent Rent Own Own Own Own Rent Rent Rent Own Rent Own Own Rent Own Own Own Rent Rent Own Own 7 3 2 4 5 3 1 4 3 5 6 4 2 5 3 5 6 4 3 5 6 4 6 4 7 4 3 1 2 5 3 4 6 5 3 2 1 6 7 2 1 2 3 2 1 3 4 6 5 4 3 5 50K-100K 100K+ 10K-25K 50K-100K 50K-100K 25K-50K 25K-50K 50K-100K 10K-25K 100K+ 100K+ 10K-25K 100K+ 50K-100K 50K-100K 25K-50K 25K-50K 10K-25K 50K-100K 25K-50K 50K-100K 100K+ 50K-100K 50K-100K 50K-100K 50K-100K 50K-100K 25K-50K 25K-50K 100K+ 10K-25K 100K+ 100K+ 100K+ 25K-50K 25K-50K 50K-100K 50K-100K 50K-100K 50K-100K 10K-25K 100K+ 10K-25K 100K+ 50K-100K 50K-100K 25K-50K 25K-50K 10K-25K 50K-100K 25K-50K 50K-100K $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 28 24 30 30 23 28 21 28 22 28 30 28 25 29 28 30 19 30 149 155 115 39 95 86 85 59 84 28 26 31 19 30 29 25 24 36 29 99 169 179 119 99 179 185 179 145 119 99 155 119 149 56 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 North East North South North West East South East West North West North East South South South West North East West North East South West North South West East East North North West West East North East South East North South West East West North East North East North South North West Own Own Rent Rent Rent Own Rent Own Rent Own Own Own Rent Rent Own Rent Own Own Own Rent Own Rent Own Own Own Own Rent Own Own Rent Own Rent Own Own Rent Rent Rent Own Rent Own Own Rent Own Rent Own Own Rent Rent Own Own Own Own 6 7 5 6 3 4 5 2 3 6 2 5 4 1 4 3 5 6 4 2 5 3 5 6 4 3 5 6 4 6 4 7 4 3 1 2 5 3 4 6 5 3 2 1 6 7 2 1 2 3 2 1 100K+ 50K-100K 50K-100K 50K-100K 50K-100K 50K-100K 25K-50K 25K-50K 100K+ 10K-25K 100K+ 100K+ 100K+ 25K-50K 25K-50K 50K-100K 10K-25K 25K-50K 50K-100K 50K-100K 25K-50K 25K-50K 25K-50K 10K-25K 100K+ 100K+ 10K-25K 25K-50K 25K-50K 25K-50K 50K-100K 50K-100K 25K-50K 50K-100K 50K-100K 100K+ 25K-50K 25K-50K 10K-25K 25K-50K 100K+ 50K-100K 50K-100K 25K-50K 25K-50K 50K-100K 50K-100K 25K-50K 50K-100K 50K-100K 25K-50K 50K-100K $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 50 86 45 95 86 97 23 29 18 26 20 15 40 41 24 26 25 26 23 27 25 18 24 21 40 19 30 35 24 26 15 18 28 24 30 30 23 28 21 28 22 28 30 28 25 29 28 30 19 30 149 155 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 East South East West North West North East West North East South West North East South West North East South West East East North North West West East North East South East North South West East South North West East South East West North West North East West North East South West Rent Own Own Own Own Own Rent Own Own Own Rent Rent Own Own Own Own Rent Rent Rent Own Rent Own Own Rent Own Own Own Rent Rent Own Own Own Own Rent Rent Rent Own Rent Own Rent Own Own Own Rent Rent Own Rent Own Own Own Rent Own 3 4 6 5 4 3 5 6 7 5 6 3 4 5 2 3 5 4 6 7 2 3 1 3 5 4 6 7 2 3 4 5 3 4 1 3 4 5 6 7 6 7 4 3 5 2 3 4 6 4 5 3 10K-25K 100K+ 50K-100K 50K-100K 25K-50K 25K-50K 25K-50K 10K-25K 100K+ 100K+ 10K-25K 25K-50K 25K-50K 25K-50K 50K-100K 50K-100K 25K-50K 50K-100K 50K-100K 100K+ 25K-50K 25K-50K 10K-25K 25K-50K 25K-50K 25K-50K 50K-100K 50K-100K 50K-100K 25K-50K 10K-25K 25K-50K 100K+ 10K-25K 25K-50K 50K-100K 50K-100K 25K-50K 25K-50K 50K-100K 10K-25K 100K+ 100K+ 100K+ 50K-100K 50K-100K 100K+ 10K-25K 100K+ 25K-50K 50K-100K 10K-25K $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 115 119 99 119 149 145 119 129 99 119 185 119 99 109 179 169 179 145 119 165 129 159 37 55 63 56 67 60 50 49 56 60 59 59 60 68 84 69 97 61 75 76 98 95 59 44 97 68 145 119 129 99 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 North North East West South East West South East North East North West East West South North East East West Rent Own Own Own Own Rent Rent Own Own Own Own Rent Rent Rent Own Rent Own Rent Own Own 2 6 3 1 4 3 5 6 3 2 4 5 7 2 1 2 1 3 6 4 50K-100K 10K-25K 100K+ 25K-50K 50K-100K 10K-25K 50K-100K 25K-50K 25K-50K 25K-50K 50K-100K 100K+ 50K-100K 25K-50K 50K-100K 50K-100K 25K-50K 50K-100K 50K-100K 50K-100K $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 119 185 119 99 60 50 49 56 60 59 59 60 68 84 69 97 61 75 76 98 NUM PURCHASES 1 4 3 6 7 5 3 6 8 2 4 9 7 10 2 6 5 7 4 3 2 8 6 7 5 3 6 8 1 8 6 4 7 3 1 4 1 4 6 9 3 1 10 3 7 6 5 8 6 4 7 3 5 4 8 7 9 1 4 7 4 3 8 9 3 2 11 2 6 8 6 8 9 3 6 5 7 2 4 6 4 5 7 2 3 1 7 5 7 9 10 1 1 1 1 11 2 6 5 7 9 3 1 7 4 7 6 8 9 3 2 5 4 7 5 6 3 2 6 8 11 8 5 3 4 3 1 10 2 6 8 7 6 4 7 8 9 6 7 4 3 2 6 5 7 5 8 4 3 5 6 7 8 3 2 5 2 5 1 11 4 3 7 5 8 6 8 5 8 10 7 9 8 1 11 10 1 4 1 3 1 4 5 4 7 5 6 4 2 6 8 9 10 3 6 5 4 6 8 7 4 5 7 8 9 11 8 5 7 8 9 5 2 6 8 6 9 7 6 3 2 11 7 5 7 8 6 8 9 3 4 7 2 8 5 7 9 2 5 4 6 11 8 5 7 4 3 6 10 3 6 8 6 8 10 3 6 5 8 4 7 3 2 6 5 8 9 3 2 6 5 7 9 4 6 8 7 1 10 3 7 5 8 9 3 2 11 6 3 6 7 9 8 3 5 4 6 11 10 3 2 10 11 7 5 9 4 3 2 6 8 6 8 9 10 3 2 6 5 8 7 9 4 6 3 5 3 6 5 7 9 4 6 8 7 1 10 3 7 5 8 9 3 2 11 6 3 6 7 9 8 3 5 4 6 11 10 3 2 10 11 7 5 9 4 3 2 6 8 6 8 9 10 3 2 6 5 8 7 9 4 6 7 5 3 6 8 2 4 9 7 10 2 6 5 7 4 3 2 8 6 7 5 3 6 8 1 8 6 4 7 3 1 4 1 4 6 5 3 7 6 8 9 10 2 6 4 7 5 8 9 2 5 4 3 11 7 4 6 5 9 2 5 3 6 8 4 7 5 1 10 11 11 11 3 6 4 7 9 3 2 6 5 8 7 2 4 3 6 9 10 11 4 2 1 5 4 7 8 9 1 7 4 6 5 9 2 BUSN 420 Major Project Grading Rubric Basic Filter Show all information for only customers who live in the North region. Show all information for only customers who (a) live in the North region, (b) own their homes, and (c) have only one household member. Comments: Custom Filter Show all information for customers who have at least 4 household members. Show all information for customers who (a) have spent less than $20 or (b) more than $100. Comments: Conditional Formatting Show all information for all customers highlighting those customers who have spent more than $100. Comments: Pivot Tables Provide a two-dimensional table that counts the number of customers by the categories of REGION and RENT vs. OWN. Provide a two-dimensional table that both (a) counts the number of customers and (b) sums the TOTAL PURCHASES of customers by the categories of REGION and RENT vs. OWN. Provide a three-dimensional table that counts the number of customers by the categories of REGION, RENT vs. OWN, and NUM HOUSEHOLD. Comments: Late Deductions Total Score Overall Comments Possible Points Actual Points 5 10 15 Possible Points Actual Points 10 15 25 Possible Points Actual Points 10 10 Possible Points Actual Points 15 15 20 50 100
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Similar Content

Related Tags