Final Capstone Project
This is the largest project of Module 26 and combines the major Excel skills learned throughout the course.
Objective
Build a complete Management Information System (MIS) for a business.
Workbook Structure
01_Data
02_Product_Master
03_Customer_Master
04_Employee_Master
05_Clean_Data
06_Analysis
07_PivotTables
08_Dashboard
09_MIS_Report
Data Areas
Sales
- Order ID
- Date
- Customer
- Product
- Category
- Region
- Employee
- Quantity
- Sales
- Discount
- Profit
Products
- Product ID
- Product Name
- Category
- Cost
- Selling Price
- Stock
Employees
- Employee ID
- Name
- Department
- Region
- Target
- Sales
Customers
- Customer ID
- Customer Name
- City
- State
- Segment
MIS KPIs
Create KPI cards for:
- Total Sales
- Total Profit
- Total Orders
- Total Quantity
- Average Order Value
- Total Customers
- Total Products
- Profit Margin %
- Target Achievement %
Profit Margin
=Total_Profit/Total_Sales
Target Achievement
=Actual_Sales/Target_Sales
MIS Dashboard Sections
- Sales Overview
- Monthly Sales
- Monthly Profit
- Sales Growth
- Product Analysis
- Top Products
- Category Sales
- Product Profitability
- Employee Analysis
- Employee Sales
- Target Achievement
- Employee Ranking
- Customer Analysis
- Top Customers
- Customer Segments
- City-wise Sales
- Regional Analysis
- Region Sales
- Region Profit
- Regional Performance
- Inventory Analysis
- Current Stock
- Stock Value
- Low Stock Products
COMPLETE MIS WORKFLOW
Students should complete the project using the following professional workflow:
Raw Business Data
↓
Data Cleaning
↓
Power Query
↓
Excel Tables
↓
Formulas & Functions
↓
PivotTables
↓
PivotCharts
↓
Slicers & Timeline
↓
KPIs
↓
Dashboard
↓
MIS Report
↓
Management Insights
Advanced Challenge
Students who have completed the earlier modules can additionally use:
- Power Query
- Power Pivot
- DAX
- VBA
- Office Scripts
- Power Automate
- SQL
- Power BI
to convert the MIS into a more automated reporting system.
FINAL PRACTICE
Create one complete Business MIS workbook from raw data.
Minimum Requirements
- At least 100 sales records
- 10+ products
- 20+ customers
- 10+ employees
- 4+ regions
- 6+ months of sales data
Must Include
☑ Data Cleaning
☑ Excel Table
☑ Data Validation
☑ Formulas
☑ Conditional Formatting
☑ XLOOKUP
☑ SUMIFS/COUNTIFS
☑ PivotTables
☑ PivotCharts
☑ Slicers
☑ Timeline
☑ KPI Cards
☑ Interactive Dashboard
☑ MIS Summary
☑ Management Insights