MIS stands for management information system, but in a BPO the word usually means something humbler: the daily and weekly reports an analyst builds, often in Excel, from data exported from the dialler, the CRM and the quality tool. Let us build one report from nothing, so the functions make sense in context.

The raw data

Imagine an export from a customer-care process in Indore. Each row is one handled call, with columns for date, agent name, agent ID, call ID, handle time in seconds and a disposition code. The export holds a day’s worth: several thousand rows. Nobody reads that. The report we want has one row per agent: calls handled, total handle time, AHT, and a flag if AHT is outside a range.

First, clean the data. Convert the range to an Excel Table (Ctrl+T), so formulas expand when new rows arrive. Check that handle times are numbers, not text, and look for duplicate call IDs; Remove Duplicates or a COUNTIF helper column will find them. Blank agent names and impossible values, like a 40,000-second call, should be investigated, not silently deleted.

Step one: the list of agents

On a second sheet, list each agent once. In recent Excel versions UNIQUE does this from the agent column; in older ones, copy the column and remove duplicates. Better still, keep a master roster sheet that comes from HR or the WFM team, because an agent who took no calls today would not appear in the raw data at all, and missing people are exactly what a report must show.

Step two: count and sum

Next to each agent, calls handled comes from COUNTIFS, matching the agent column to the name. Total handle seconds comes from SUMIFS, summing the handle-time column for the same agent. Use COUNTIFS and SUMIFS rather than filtering and eyeballing, because they update automatically and leave a formula trail someone else can audit. Add the date as a second condition so the same sheet works for a week by changing one cell.

AHT is the total handle seconds divided by the calls. Wrap it in IFERROR so that an agent with zero calls shows a dash or blank in place of a divide-by-zero error.

Take three illustrative agents. Anand handled 120 calls with 36,000 seconds in total, so his AHT is 300 seconds. Bhavna handled 150 calls and 37,500 seconds: 250 seconds. Chitra handled 100 calls and 34,000 seconds: 340 seconds.

Step three: the team figure, and the averaging trap

What is the team’s AHT? Many beginners average the three AHT cells: (300 + 250 + 340) / 3 = 296.7 seconds. This is wrong. The right figure is total handle time over total calls: (36,000 + 37,500 + 34,000) / (120 + 150 + 100) = 107,500 / 370, which is about 290.5 seconds. The difference arises because the agents handled different numbers of calls, so an average of averages gives Chitra’s 100 calls the same weight as Bhavna’s 150.

This applies to every ratio in an MIS: attrition, quality scores, abandon rate, CSAT. Compute the ratio from the summed numerator and summed denominator. Where each agent has a different number of audits or calls, the simple average of percentages misleads. If you must show a roll-up, build it with SUMPRODUCT or from totals.

Step four: bring in other sources

The same agent’s quality score lives in another file. XLOOKUP (or VLOOKUP in older versions) pulls it in by agent ID. Prefer ID over name, since names repeat and get spelled differently. Set the lookup to require an exact match, and test what happens when an ID is missing: you want a visible “not found” you can chase, not a quiet wrong value from an approximate match.

Step five: make it readable

Add an IF flag for AHT above or below the agreed band, but do not leave it at that: a flag with no context makes team leaders defensive. Conditional formatting can colour only the exceptions. Sort so the problem rows are visible. A pivot table is the quicker route for ad hoc questions, such as AHT by disposition or by hour, though a formula-based sheet is easier to schedule and reuse day after day.

Habits that save embarrassment

  • Reconcile your total calls with the source system’s count before sending. If they differ, find out why.
  • Keep one tab for inputs, one for calculations, one for the output page. Do not paste values over formulas.
  • Put the date range and the data source on the report itself.
  • Protect agent-level data: it is personal and performance-related, so share it only with those who need it, and avoid sending it to personal email.
  • Ask who reads the report and what decision it supports. A report nobody acts on is a candidate for deletion.

Once the sheet works, the next step is automation: Power Query to refresh the import, or scheduled reports from the tool itself. But the thinking above, correct denominators, clean keys, reconciliation, stays the same whichever tool you move to.

Figures and names in the examples are illustrative.

Leave a Reply

Trending