Pivot Tables for Analyzing Data and Making Decisions

CMIS 351 Management Information Systems

Task

This assignment gives you an opportunity to practice the use of pivot tables for analyzing data and making decisions. These tables are particularly important for managers because they provide easily accessible business intelligence tools, right at the manager’s desk. If you need help with pivot tables, refer to “(Knowledge Extension) KE4-9: What are Pivot Tables and How Do I Use Them” in the textbook. Microsoft Office also provides Pivot Table support.

Instructions:

1. Download the Excel file: xlmd_travel.xls. It contains information about locations, travel agents, cruise lines, pricing, and commission paid to agents. Descriptions of the fields are as follows:

Column	Name	Description
A	Location ID	unique number assigned to each location
B	Travel Agent ID	unique number assigned to each travel agent
C	Cruise line	name of cruise line
D	Total package price	total price charged for travel
E	Commission	commission charged

3. Add one additional column to calculate the revenue after commission is deducted from total package price.

4. Create pivot tables to help you answer the following questions:

a. Design a table that shows total revenue generated by each destination. Which destination brings in the most revenue?
b. Design a table that shows the total revenue generated by each travel agent. Who is the best employee (travel agent)?

c. Design a table that shows which cruise line is the most valuable for the travel agency.
d. Design a table that calculates the total revenue by travel agent, location, and cruise line. Which location on which line bring in the most revenue, and which agent is involved in that business?
e. Provide a table that shows which agent earns the highest average commission for Bahama lines.

5. Create a 3- to 5-page report to management that provides answers to each of the questions in Question 3 above. The report should include, for each question, an explanation of how your analysis of the pivot table you created enabled you to answer the question, along with implications for running the business (what would the report mean? for example, how would management use each result to make decisions? what types of decisions could be made with the information?). Use tables, graphs, etc., to represent your observations and recommendations based on these analyses. Make sure your report has an introduction and a conclusion. For formatting guidelines, see Writing An Analytical Report.

For this assignment, you must submit: