College of Business Administration (COBA)

Academic Year 2023– 2024

Semester Fall Spring Summer

Course Code - Name MGT 200 Management Information System

Instructor Dr. Mohammad Alnasar

Project and Presentation

Exam
Quiz/Test Assignment Mid Final

Other
Deadline Sunday, June 23, 2024
CLOs:

1. Explain the principles, concepts, theories, and processes of management information systems.
2. Analyze the impact of management information systems on business processes, productivity,
and competitive advantage.
3. Distinguish the role of management information systems in organizational decision-making and
performance evaluation
4. Apply appropriate management information systems tools, techniques, and technologies to
solve business problems through teams.

Student IDs: __________________________________________

Student Names:

Question CLO Max Score Student Score

Task1 1 2
2 3
3 5
4 15

Total 25
 Assessment: Project and Presentation
Project: Creating an Interactive Excel Dashboard
Objective:
Demonstrate proficiency in creating an interactive Excel dashboard using real-world order
data to effectively present insights and facilitate decision-making processes.

Step 1: Data Preparation

Utilize the provided dataset containing the following columns:

- Order Number
- Customer Number
- Sales Representative
- Date of Order
- Priority of the Order
- Stock Keeping Unit
- Quantity
- Sell Price
- Discount
- Shipping Cost
- Mode of Shipment
- Shipment Category
- Date of Shipment

Import Data into Excel:

1. Open Excel.
2. Go to `File > Open` and select the dataset file.
3. Ensure the data is correctly loaded into a worksheet named `Raw Data`.

Step 2: Data Analysis

Conduct a thorough analysis to identify trends, insights, or KPIs relevant to the
business context.

Analysis Questions:
1. Order Analysis:
- Total number of orders and total revenue generated.
- Order quantity variation by customer and sales representative.
- Distribution of order priorities.

2. Sales Performance:
- Sales volume and revenue by sales representative.
- Monthly sales trends.

3. Product Analysis:
- Top-selling SKUs.
- Impact of discounts on sales volume and revenue.

4. Shipping Analysis:
- Usage of different shipment modes.
- Variation in shipping costs across shipment categories.
5. Customer Analysis
- Top customers by order quantity and revenue.
- Order frequency by customer.

Create Pivot Tables:

1. Pivot Table 1: Sales by Representative
- Rows: Sales Representative
- Values: Sum of Sell Price

2. Pivot Table 2: Orders by Customer

 - Rows: Customer Number
- Values: Count of Order Number, Sum of Quantity

3. Pivot Table 3: Quantity by SKU

- Rows: Stock Keeping Unit
- Values: Sum of Quantity

4. Pivot Table 4: Average Order Value

- Rows: Customer Number
- Values: Sum of Sell Price / Count of Order Number

5. Pivot Table 5: Revenue Trends by Month

- Rows: Date of Order (Grouped by Month/Year)
- Values: Sum of Sell Price

6. Pivot Table 6: Profit Margin by SKU

- Rows: Stock Keeping Unit
- Values: Calculated Field (Profit = (Sell Price - Cost) * Quantity)

7. Pivot Table 7: Shipping Duration

- Rows: Shipment Category
- Values: Average of (Date of Shipment - Date of Order)

8. Pivot Table 8: Customer Lifetime Value

- Rows: Customer Number
- Values: Sum of Sell Price

Step 3: Designing Your Dashboard

Create Charts and Graphs:

1. Bar Chart: Total Sales by Representative
- Use Pivot Table 1 data.

2. Pie Chart: Order Priority Distribution

- Summarize order priorities using Pivot Table.

3. Line Graph: Monthly Sales Trends

- Use Pivot Table 5 data.

4. Bar Chart: Average Order Value by Customer

- Use Pivot Table 4 data.

5. Line Graph: Monthly Revenue Trends

- Use Pivot Table 5 data.

6. Pie Chart: Profit Margin by SKU

- Use Pivot Table 6 data.

7. Bar Chart: Shipping Duration by Shipment Category

- Use Pivot Table 7 data.

8. Bar Chart: Customer Lifetime Value

- Use Pivot Table 8 data.

Insert Slicers for Interactivity:

1. Sales Representative
2. Order Priority
3. Shipment Mode
4. Order Date (Month/Year)
5. Customer Number
6. Stock Keeping Unit

Apply Conditional Formatting:

1. Highlight top sales representatives.
2. Emphasize high-priority orders.
3. Identify significant revenue trends.

Design Layout:
1. Create a new worksheet named `Dashboard`.
2. Arrange charts, graphs, and pivot tables in a user-friendly layout.
3. Position slicers prominently for easy access.

Step 4: Interactive Features

Ensure the dashboard is interactive, allowing users to explore and analyze data
dynamically.

1. Adjust Slicers:
- Allow users to filter data based on criteria (e.g., sales representative, order priority).
- Visualizations and metrics update automatically based on slicer selections.

2. Test Interactivity:
- Ensure all slicers and filters work correctly.
- Verify that charts and pivot tables update dynamically.

Step 5: Documentation and Submission

Document the Process:
1. Word Document:
- Title: "Interactive Excel Dashboard Project"
- Sections:
 - Introduction: Objective and dataset overview.
- Data Preparation: Steps to import and prepare data.
- Data Analysis: Key analysis questions and findings.
- Dashboard Design: Explanation of charts, graphs, and layout.
- Interactivity: Description of slicers and conditional formatting.
- Conclusion: Summary of insights and potential business implications.

2. Screenshots:
- Include screenshots of the dashboard, pivot tables, and charts.

Save and Submit:

1. Excel File:
- Save the Excel file with the name format: `ID_Name_ExcelDashboard.xlsx`

2. Word Document:
- Save the Word document with the name format: `ID_Name_ExcelDashboard.docx`

3. Submission:
- Submit both files as per the assignment instructions.

Final Notes
- Ensure the Excel file is clear, organized, and well-documented.
- Ensure the dashboard is visually appealing and provides valuable insights.
- Be creative and think about what would be most useful for a business audience.
- Test the interactivity and functionality thoroughly before submission.

Note: you are required to present your work by the end of the semester.
Therefore, prepare a 10-15 minutes presentation once you complete your
report.
By following these steps, you will create a comprehensive and interactive Excel dashboard
that showcases your ability to transform raw data into actionable business insights. If you
have any further questions or need additional guidance, feel free to ask!

Integrity Statement: Read carefully!

Strict directives have been given to me to follow the University’s policy on cheating. Any
student caught cheating in any form is given zero (0) the first time. (We will not get into
the issue of who copied from whom!!!). If such act reoccurs, the case is sent to the
University Student Discipline Committee.

Due Date:

Please submit your completed assignment by Sunday, June 23, 2024

Good Luck
Dr. Mohmmad Al Nasar