MIS Collection Dashboard

Build a practical branch-and-employee collection report from raw MIS data, then turn it into a management dashboard.

Intermediate · 45–60 min

Objectives

• Understand an MIS collection dataset
• Calculate achievement %
• Create DPD buckets
• Build a PivotTable and PivotChart
• Explain a management insight

Project dataset

Guided checkpoints

1. Understand the data

   Read the headers and identify which field is the target, which is the actual collection, and which field represents delinquency.

   Map business meaning to Excel columns.

2. Calculate achievement %

   In a new column, calculate Collection ÷ Target for row 2 and format it as a percentage.

   Create a KPI from two business measures.

3. Create the DPD bucket

   Classify DPD into 0–30, 31–60, 61–90 and 91+ using IF/IFS logic.

   Turn a numeric risk measure into a report-friendly category.

4. Build the PivotTable

   Create a PivotTable with Branch in Rows, Collection in Values, and Status in Filters.

   Summarize collection by branch.

5. Analyze branch performance

   Compare branch collection with branch target and identify the KPI management should review first.

   Translate a report into a business question.

6. Create a PivotChart

   Use a column or bar chart to compare branch collection totals.

   Select a chart for category comparison.

7. Build the dashboard

   Create a compact management view with 3 KPIs, one comparison chart and useful filters.

   Assemble a focused dashboard.

8. Management insight

   Review the project data and state which branch needs the closest review based on target vs collection.

   Make a defensible business conclusion.

Download sample project .xlsx · All portfolio projects

Interactive practice runs in your browser.