Deliver a target-versus-achievement MIS — Smart Study Module 4

Produce a January performance report that a branch manager can act on.

Outcomes

• Create branch-month summaries
• Use weighted achievement ratios
• Write a control-checked management note

Understand

Aggregate payments at branch-month grain. Targets are a separate branch-month table; joining each target to every payment and summing it multiplies the target. Summarize first or keep separate measures.

January Pune collection is ₹27,000 against ₹30,000; Nashik is ₹21,000 against ₹30,000. Overall achievement is 48,000/60,000 = 80%, not the sum of percentages. With unequal targets, an unweighted average also fails.

Build a PivotTable with branch in rows and amount in values, filter January and independently compare the grand total. A horizontal bar chart with actual/target is enough; label the period and currency.

Follow along

1. Complete the Excel MIS project checkpoint in Projects below.
2. Create a January branch summary and separate target summary.
3. Add achievement and shortfall columns; reconcile to ₹48,000.
4. Write a three-sentence note naming Nashik, ₹9,000 shortfall and a follow-up owner.

Worked example

=IF(Target=0,"Not available",Actual/Target)
Overall achievement = SUM(actuals)/SUM(targets)

Pune 90%; Nashik 70%; combined 80%; combined shortfall ₹12,000.

Practice

Enter overall January achievement as a percentage number without the % sign.

Create a free personalized study plan · Download project instructions

Interactive practice runs in your browser.