Homework #2 – Excel Charts and Pivot Tables
Overview and Learning Objectives
This homework assignment asks you to use Microsoft Excel to use the VLookup function in a report, and also to generate charts and pivot tables. These are all useful skills. VLookup simplifies spreadsheet logic in many situations, and charts offer a visual perspective that can help people to see trends and data relationships. And once you get the idea of pivot tables, they are extremely useful in summarizing data “on the fly” in a variety of formats. This assignment is related to course objective #4 (“Design and implement Microsoft Excel spreadsheets and Microsoft Access databases to support business decision making”).

Quality Furniture Enterprises, Continued
This homework assignment continues the scenario from Homework #1, about Quality Furniture Enterprises. Caitlin’s uncle (and boss) was pleased with the sales forecast report she created. “I guess I should learn Excel,” he says. “I’ve been using the calculator on my laptop to compute the totals, and then copying and pasting things into a Word document.” Caitlin did her best not to appear smug, but it was nice to be appreciated, especially since this was still her first month on the job. But before she had any time to bask in her success, her uncle said, “And since you did so well on that report, I have another project for you.” So much for taking an early lunch break today. “Sure,” she said. “What do you need?” “First off, remember how I told you we were thinking about changing our commission structure? We’ve decided to vary it depending on the product line. Product lines 1 and 2 are our core business, and people are used to buying those things from us, so we’re only going to offer lower commissions on those products. But product lines 3 and 4 aren’t as well established; we’re going to offer higher commissions on those to encourage the sales force to give those product lines more attention. Here’s a chart showing the new commissions.” He handed Caitlin a sheet of paper with this table: Product Line PL1 PL2 PL3 PL4 Commission % 2.5% 2.5% 3% 3.5%

“Second,” he continued, “the report you created was great, but we also need some reports that summarize the information, instead of showing all the details for each customer. For example, the sales manager wants one that just shows each sales rep’s total forecasts per month. We want so rev. 2014-06-10

see if any of the reps need help finding more potential business. And the product line managers want to see the forecasts by product line, so they know how much potential business they have.” He handed her three sheets of paper. “Here are the reports we need.” Caitlin glanced through the reports. They had similarities, but no two were the same. She thought she knew a way to create them quickly. “And finally …” He paused and sighed. “The Big Bosses went to a furniture industry conference and trade show last week. I hate it when this happens, because they always come back with big ideas that we can’t afford. This time, it’s dashboards and data visualization. They want executive-level displays that they can just glance at to get an idea of what’s going on, but they don’t want to spend any money. Excel can create charts and graphs, right? Just give them a few of those. Maybe a pie chart showing forecasts by product line. And what about a column chart showing forecasts by month and product line? That should be enough for now.” Caitlin knew what her uncle meant. The Big Bosses (better known as her grandparents) were notoriously cheap when it came to spending money on information technology. But an even bigger problem, as she knew from her MIS class, was that a lot of QFE’s business processes would have to be changed to collect and share data more effectively before an executive information system would be a realistic possibility. For now, it looked like she was headed back to Excel, to create some new forecast reports and charts.

Getting Started
The Microsoft Excel Part 4 and 5 units should be especially helpful in this assignment. If you need help with just one or two specific skills or concepts, see the list of Excel Learning Resources in the Microsoft Excel and Access area on Blackboard. If those resources don’t answer your questions and you need individual help, please ask; I’ll be happy to assist you. Download the starting workbook, located in the Homework 2 folder in the Homework area on Blackboard. It has one spreadsheet called "Forecast Data," containing the same data you worked with in Homework #1, but organized differently. Here, instead of having one row per customer forecast, the sheet has one row per customer forecast for each product line. Forecast rows are created for a product line ONLY if the customer’s forecast amount is greater than zero. This means that each customer’s forecast can have anywhere from 1-4 rows in this spreadsheet, instead of just one row as in Homework #1.

Assignment Part A (10 points): VLOOKUP Function
In this part of the homework, you will handle the change in commission structure. 1. Add a “Projected Commission” column to the Forecast Data spreadsheet. Using the VLOOKUP function, calculate the projected commission in dollars for each row in the spreadsheet. Use the commission percentages Caitlin’s uncle provided (see above). 2. Now, revise your commission formula. Add an IF function so that the formula calculates commission ONLY for forecasts with a probability of at least 70% (you may remember that we also calculated commission for “likely orders” only in Homework #1. 3. OPTIONAL for EXTRA CREDIT: Add another forecast column called “Commission 2.” Reproduce the calculations in the Projected Commission column using nested IF functions instead of the VLOOKUP function. Make sure the commission values are the same in both columns. rev. 2014-06-10

2

Assignment Part B (40 points): Pivot Tables
The various types of reports Caitlin’s uncle mentioned offer an example of “slice and dice” data analysis (as discussed in Chapter 2 of the text), where decision makers need to see the data from different perspectives and at different levels of detail. Although you could build each report from scratch as we did with the Homework #1 report, the easiest and most flexible way to create different “views” of the data is to use pivot tables. That’s what you will do in this part of the assignment. 1. (8 points) Create a pivot table from the Forecast Data spreadsheet showing each sales rep’s total forecast per month. Be sure to check the range of cells Excel selects for the report to make sure that your data table for the VLookup function was excluded. Place the pivot table on a new sheet. Change the tab on your pivot table sheet to have the label “Step B1.” (These labels are important; getting them wrong will cost you points.) 2. (8 points) Create a second pivot table that shows the total forecasts for each product line per month. Place this pivot table on a new sheet and label the tab “Step B2.” 3. (15 points) Create a third pivot table showing forecasts for each customer, subdivided by month and, within month, by product line. Place this pivot table on a new sheet and label the tab “Step B3.” 4. (9 points) Format the Step B3 sheet, considering the suggestions in the “Spreadsheet Formatting Tips” article from a previous unit. NOTE: If you have trouble figuring out what the Step B3 sheet should look like, you can obtain a sample showing row and column labels (without the detail data) by emailing me. This hint will cost you five points against your score on the assignment.

Assignment Part C (30 points): Charts
In this part of the homework, you will create the two charts Caitlin’s uncle requested.

1. (9 points) First, you need to create a 3D pie chart of the total forecast, split up by product line. Start by creating a pivot table summarizing the forecasts by product line; i.e., use product line as the row label. Then use that pivot table to create the pie chart. (Some versions of Excel have a “Pivot Chart” facility that lets you combine the pivot table and the chart into a single step.) Here is what it should look like before any format changes (colors may be different)

2. (6 points) Label the tab “Step C2.” Then modify the design, layout, and format as follows: a. Enlarge the chart so it’s easier to see the details. b. Add a descriptive chart title. c. Make sure you have a legend as shown in the example. rev. 2014-06-10

3

d. Add data labels showing the forecast amount for each product line. e. If the data labels do not show that the values are in dollars, fix this (the best way to do this is to go back to the pivot table and format the data values in dollars). f. Enlarge the font used for the data labels and legend so they are easier to read. 3. (9 points) Next, create a column chart summarized by product line and month. Again, it’s easiest to create a pivot table first, with the product lines as row labels and months as column labels. The table should NOT show sales rep or customer information. Then create a 3D column chart with one column for each month, and within the column, separate colors for each product line. You will need to look at the column chart options to figure out which one to use to get this result. Here is what it should look like before any format changes (the colors may be different):

HINT: If your chart comes out with four bars—one for each product line—and the months are in the legend, go to the “Design” tab under “Chart Tools” and click “Switch Row/Column.” 4. (6 points) Label the tab “Step C4.” Then modify the design, layout, and format of your chart as follows: a. Enlarge the chart so it’s easier to see the details. b. Add a descriptive chart title (you may have to reduce the size of the column chart to make room). c. Make sure you have a legend showing the months. d. If the vertical axis does not show that the values are in dollars, fix this (the best way to do this is to go back to the pivot table and format the data values in dollars). e. Enlarge the font used for the legend and axes so they are easier to see.

Submitting Your Work
See the Homework folder (the “General Information and Instructions” item) to learn more about:  Working with a partner  Late penalties Submit your completed work on Blackboard as an Excel workbook. Keep a copy of the workbook handy until you receive your grade; sometimes problems arise when uploading files, and I might need you to submit it again.

rev. 2014-06-10

4

Grading
This assignment is worth 80 points, with points distributed as shown in the instructions. Points will be awarded as follows:    Full credit: Followed instructions and got the correct results. Partial credit: Tried to follow instructions but something went wrong. No credit: Did not follow the instructions, skipped the step, or hard coded results.

rev. 2014-06-10

5