Using the MS-Excel to develop a solution to a business problem/ MIS Excel Project

Description

MIS Excel Project/Presentation:

Note: Start with the ABC Company Data workbook posted on Moodle

Description

Problem 1

ABC Company wants to analyze certain aspects of its sales performance. Create a pivot table to show total sales by quarter and then month (horizontally) and also by Account IDs in intervals of 100000 (vertically). The data should show only for the South. Based on the pivot table data, answer the following questions:

1. What was the total revenue for the 4th quarter?
2. What was the total revenue for Account IDs 1717423-1817422?
3. Describe the quickest way to obtain the detail records supporting the revenue totals for Account ID ranges.
4. For Account ID range 1817423-1917422, how many detail records support the total revenue figure for all periods?

Also, create an embedded 100% Stacked column chart to show total revenue with Quarter and Month as the series and Account intervals as the categories.

Problem 2

ABC Company also wants to understand its transaction activity. Create a pivot table to show the total number of transactions by Region and then by City (vertically) and also by Sales Period (horizontally). Show only the first five sales periods. The data should be in percentage form with the grand total of all transactions being 100%. Collapse the City data so only Region transactions are showing. Based on the pivot table data, answer the following questions:

1. What percentage of transactions does the South comprise?
2. For the region of Midwest, how many detail records support the total transaction percentage shown?

Also, create a non embedded stacked bar chart to show the number of transactions with Sales Period as the series and Region as the categories.

Each pivot table should be on its own worksheet with the worksheet tab named logically. One chart is to be embedded with its related pivot table, the other in its own worksheet. Any dates used or items derived from dates should be based on service date.

Evaluation Breakdown (20%)

• Accuracy of Excel pivot tables/charts and answers to questions – 5%
• Quality of PowerPoint design – 5%
• Peer evaluation of each team member** - 5%
• Team presentation in Collaborate – 5%
PowerPoint Guidelines

• Approximately 12-15 slides
• Screenshots of tables and charts
• Answers to questions clearly displayed
• Other graphics and animations to enhance delivery

Sample presentation layout for your guidance:

• Title slide
• Overview / Agenda
• General-purpose of pivot tables and charts (how they help your analysis)
• Explanation of problem 1
• Pivot table 1 explanation
• Chart 1
• Answers to problem 1 question(s)
• Explanation of Problem 2
• Pivot table 2 explanation
• Chart 2
• Answers to Problem 2 question(s)
• Conclusion