Decision Support via Excel Pivot Tables
Due: beginning of class on July 31

The purpose of this assignment is for each student gain experience using an IT tool to help make basic decisions. Students should complete this individually, not in groups.

Excel 2007 format data file (.xlsx)
Excel 97-2003 format data file (.xls)

Part One - Basic Pivot Tables

Using the excel spreadsheet found above, create a series of Pivot Tables (or Pivot Charts) to answer the following questions.

  1. What are the averages for purchases in each region?
  2. What form of payment is most common?
  3. Do our customers shop at work (most likely between 8:00 and 17:00) or at home?
Turn in a printout of the pivot tables you used to answer each question.

Note: To answer number 3, I created a new column in the spreadsheet labeled "Hour of Day" and set it to just the hour instead of hour and minutes. In other words =hour(G8). The students who are excel power users should use an "if" formula to create a value of either day (time between 8:00 and 17:00) or night (not between 8:00 and 17:00).

Part Two - Analysis

Now let's test your ability to think critically. This is also a small writing assignment.

Our company's approach has been to divide advertising efforts evenly between web banner ads and email ads, and to give each region equal levels of resources. What are the weaknesses to this approach?

Also, what changes would you suggest to our advertising plan? For example, should we stop all advertising in the North? Concentrate on web banner ads of books in the South? Stop using Paypall? yadda yadda

Write your answer in paragraph form. Be sure to include all pivot tables or other charts that you used to help make this decision.

Grade for Part Two of this homework will be based on: correctly identifying the problem, identifying any assumptions, quality supporting data, clarity of conclusions. Grade data from this assignment is used as part of the MBA program's formal assessment of student learning. Hence, they require me to give you a writing assignment and require me to grade it using a "Critical Thinking" rubric.