Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

BADM-2301 Lab

Class Assignment 3 Instructions

Learning Topics

Quick data summary using Excel functions.

Sorting data in ascending or descending order by a specified column.

Learning how to use VLOOKUP function and Pivot Table.

Background

Britney Pearce is a store manager of DangerWay, a small grocery retailer based in DMV area. Every January Britney receives an annual sales report summarizing the sales revenue from each product in the previous year. In this session, we will learn how to perform sales analyses and data management using advanced Excel functions in a more realistic business scenario.

Preparation

Under “Class Assignments” on Blackboard, locate “CA3 – Class Assignment 3 Excel”, and download the instruction Word/PDF file as well as the starter file “Grocery.xlsx”. Save the file on your local computer as “Grocery_lastname.xlsx”. Double-click on it to open the file. Look at the first sheet, “Grocery” within the file and get a general idea about the contents of the table and the organization of the information. (Imagine Britney will likely have a much longer list for her store. To keep the lab session manageable, we are only looking at 100 of them, but the analysis procedure is similar.)

1. First, you may notice that in the “Sales” column in the Grocery worksheet, many numbers show up in “######” format. This is because the cell content is bigger than the cell. Resizing the column should solve the problem.

Ø To resize, move your mouse cursor over the borderline between columns C and D on the top. When you see the cursor's shape change into a double-arrowed divider double double-click it. This will resize the column C to “just fit” all the numbers. You can do the same to Columns A and B.

2. Next, format the sheet to make it look nicer. For example, use:

a. bold font style for headers,

b. center alignment,

c. bottom cell border, and

d. fill in some color of your choice to make the header row stand out.

3. Since we have quite a few rows on the sheet, it is a good idea to “freeze” the top row.

Ø To freeze the top header row, go to ribbon “View” → “Freeze Panes” → “Freeze Top Row.”

4. Save the file again as you work on it further. Note the directory where you save the file.

Data Summary

It is always helpful to get a general sense of the dataset when there are many records. Britney, for example, wants to know the number of items on the list, the maximum and minimum revenue across items, the average per-item revenue, and the total revenue in the last year. We will create a separate worksheet to summarize this information.

1. Insert a new sheet by right-clicking on the Grocery sheet, then clicking on “Insert”, then clicking on ok (Note that “worksheet” is highlighted in the dialogue box. If not, choose it). Rename  this newly inserted sheet “Sheet1” to “Summary” by double-clicking on “Sheet1.”

2. Input/type the following labels in the first column:

a. “Count” in cell A1,

b. “Maximum” in A2,

c. “Minimum” in A3,

d. “Average” in A4, and

e. “Total” in A5.

Make column A stand out as the header column by changing the font style to bold for the whole column.

3. We will use the COUNT function to count how many items are on the list. In cell B1, type “=COUNT(Grocery!C2:C101)”. Press Enter. You should see the item count is 100.

Ø The expression between parentheses tells Excel that our target cells range from C2 to C101 on the worksheet “Grocery”.

4. We can use the MAX function to find the maximum value from the “sales” column in the Grocery worksheet. In cell B2, type “=MAX(Grocery!C2:C101)”. Press Enter. You should see the maximum per-item revenue.

5. Find the minimal value in the same way in cell B3. The Excel function is MIN.

6. The AVERAGE function calculates selected cells' average (mean) value. Type “=AVERAGE(Grocery!C2:C101)” in cell B4 and press Enter.

7. Follow the same drill to find the total revenue. The function you will is SUM.

Note: Make sure to choose the right column in the Grocery worksheet for this.

8. Another way to get a quick general sense of the data set is to use Data Bars to highlight the value and variation of the cells. To illustrate its use, go to the “Grocery” tab, select the “Sales” column by clicking on the column header C. Click the “Conditional Formatting” button on the Style group of the Home tab, move the mouse cursor to “Data Bars”, then select the “Blue Data Bar”, the first from the pop-up menu. You should see that each column cell is now color-coded by a bar with the size corresponding to the number in it.

The status bar also allows you to find all this information quickly. To do so right click the status bar on the bottom of the Excel window, and make sure the following items are selected from the pop-up menu: “Average”, “Count”, “Minimum”, “Maximum”, “Sum”, and “Numerical Count”. Then select column C on the “Grocery” worksheet; you will see the results on the status bar.

Sorting

A quick way to review the most important items vs. the least in revenue is to sort the list by the “Sales” column. Excel provides convenient and flexible ways to sort data.

1. Make sure “Grocery” is your active worksheet, which means it’s the worksheet you working in. Select Column C. To select a whole column, move your mouse cursor to the top over the column label “C”. When you see its shape change into a downward arrow, click it. Now the whole column is selected.

Ø You can select a whole row similarly, or even the entire spreadsheet.

2. Go to “DATA” tab and click on “Sort” button. Make sure “Expand the selection” is selected, then click on “Sort”.

3. Within the “sort” dialogue box, under “Column” Sort By choose “Sales”, and from “Order” choose “Largest to Smallest”. Then click on OK. Your list should now be sorted with the biggest-sales item on top and smallest one at the bottom.

4. Now sort the table alphabetically by the product name, with A- on the top and Z- on the bottom. Save your work again.

Ø You can also sort by the SKU number in ascending or descending order. Each way gives you a different view of the same dataset depending on your need.

VLOOKUP Function

Excel’s VLOOKUP function is a powerful tool to cross reference multiple fields, to combine worksheets, and to locate and find records dynamically. We will use the following scenario to illustrate its use.

Suppose Britney Pearce wants a by-category revenue list rather than an itemized one. She wants to add a “Category” column to the Grocery worksheet to show each item's category (e.g., meat, dairy, drinks, etc.). Now we have category information stored in the “Category” sheet with SKU being the identifier. How to automate the process and not type one by one?

1. Go back to the “Grocery” worksheet and type “Category” In cell D1. In cell D2, type “=VLOOKUP(B2,Categories!A:B,2,FALSE)”. Press Enter. You should see “Fruits” as the category of “Apples” if the first item on your sheet is “Apples”.

Ø In this expression, B2 is the value to look up for; Categories is the target worksheet, and A: B is the target cell range; 2 means the 2nd column to the right (i.e. the next cell to the right) is the location of the value to return; FALSE means we need an exact match for the value specified (B2).

The function takes 4 arguments:

a) What value to search for? B2 in this case

b) Where to search for the value specified in a)? Categories!A: B is where you go

c) Which column of Categories!A:B to return (from left to right)? Column B is the 2nd column, so we use 2!

d) How to find the match FALSE for “exact” match?

2. Drag-fill to populate the rest of the list by clicking on the lower right corner of the “Fruits” cell and dragging it to the end of the list.

Pivot Table:

1. Open the “Pivot Table (Sales)” sheet in Grocery.xslx.

2. Select the table you want to create a pivot table for by selecting rows A1:D7

3. Go to “Insert” → “Pivot table” → Choose “Existing worksheet”, then click outside on the worksheet below the table. The cell you clicked on is selected. Now go back to the dialogue box and click on OK.

4. You will see a Pivot Table created below the table on the worksheet and on the left you will see a separate area with Pivot Table fields and “filters, “columns”, “rows” and “values” labels below the fields.

5. To create the pivot table now, you can drag and drop fields into these labels, depending on how you desire to organize the pivot table. For instance,

- Drag fields ‘Region’ and ‘Salesman’ onto Row Labels.

- Drag field name ‘Product’ into Column Labels.

- Drag field name Sales into Values.

6. It should look like the following table.

7. Within the Pivot Table, right-click on ‘Sum of Sales”, then choose ”Value Field Settings”. You can summarize value fields by statistics such as Average, Count, Min, Max, etc.

Now you can use the pivot table to answer the following questions:

A. How many salespersons are there for each product?

B. What are the total sales across states and products?

C. Which type of salespersons (by product) generates the highest average sales?

Check the worksheet “Pivot Table (Salary)” for more practice.

Save the file after walking through the tutorial. Note the directory you saved it in. Submit the file as Class Assignment 3.

You will continue working on Lab Assignment 3 based on this file.