Chapter 22 | Business Reporting and Decision Support

Management Information System Reports

Learn how to convert operational Excel data into accurate, timely and decision-ready MIS reports for sales, HR, inventory, finance and management review.

Start Learning Build MIS Workbook

Reporting CadenceDesign daily, weekly and monthly information flows.

Decision-Focused KPIsSelect measures that trigger management action.

Exception ReportingHighlight risks, delays and deviations instead of hiding them.

Refreshable ReportingBuild repeatable processes with controls and documentation.

Choose Language

Module 6: Charts and Dashboard Development

Learning Objectives

After This Chapter, You Will Be Able To

Design complete MIS reports that are accurate, relevant, timely, controlled and useful for management decisions.

Define MIS

Understand the purpose, users, inputs, outputs and limitations of management reporting.

Build Architecture

Separate source, mapping, calculation, report and control layers in a maintainable workbook.

Create Functional MIS

Prepare sales, HR, inventory and finance reports using appropriate KPIs and comparisons.

Control Quality

Use reconciliation, exception checks, refresh logs and sign-off controls before distribution.

MIS Foundation

Understand What an MIS Report Must Accomplish

MIS is not merely a collection of Excel tables. It is a disciplined information system that supports monitoring, control and decision-making.

1 Meaning, Purpose and Business Value of MIS

A Management Information System report summarizes operational data into information that managers can understand and act upon. It connects business activity with goals, targets, risks, ownership and timelines.

Definition: An MIS report is a structured, periodic and decision-oriented presentation of business information prepared for monitoring performance, identifying deviations and supporting management action.

The MIS Information Cycle

Capture

Collect transactions, activities and master data from reliable sources.

Validate

Check completeness, format, duplication and business-rule compliance.

Analyse

Calculate KPIs, targets, trends, variances and exceptions.

Communicate

Distribute concise findings with owners and required actions.

Characteristics of a Strong MIS

Characteristic	Meaning	Management Test
Relevant	Contains information linked to business objectives.	Does this measure influence a decision?
Accurate	Matches approved sources and calculation definitions.	Can the number be reconciled?
Timely	Arrives early enough for corrective action.	Is the report still actionable?
Consistent	Uses stable definitions, periods and formats.	Can users compare one report with another?
Concise	Highlights what matters without excessive detail.	Can the reader find the main message quickly?
Actionable	Shows exceptions, ownership and next steps.	What action should follow this information?

Professional Tip: A report becomes management information only when it gives context. A sales value alone is data; sales versus target, prior period and forecast is information.

Practical Experiment 1: Convert Data into Information

Transform a raw monthly sales total into a decision-ready management statement.

Step 1: Record

Write the actual sales value and reporting period.

Step 2: Compare

Add target, prior month, variance and percentage achievement.

Step 3: Recommend

Write one management observation and one action.

Learning Output: A concise KPI statement that explains status, direction and required action.

2 Define Report Users, Decisions and KPI Ownership

Different users require different detail. An executive may need five strategic indicators, while an operations manager may need branch-level exceptions and employee-level follow-up. Start every MIS with a reporting requirement document.

AudienceWho reads, approves and acts on the report?

DecisionWhich decision should the report support?

CadenceHow frequently must the information be available?

OwnershipWho owns each source, KPI and corrective action?

MIS Requirement Questions

What business objective is being monitored?
Which KPIs indicate progress, risk or failure?
What level of detail is required: company, region, branch, product or employee?
What comparison is meaningful: target, budget, prior period or benchmark?
Which exceptions require escalation?
When is the report due, and who approves it?
What confidential information must be protected?

Avoid: Creating reports because “management always asks for them.” Every recurring report should have a documented user, purpose, owner and decision.

Practical Experiment 2: Prepare an MIS Requirement Sheet

Design requirements for a monthly regional sales report.

Step 1: Identify

List users, decisions, frequency and delivery deadline.

Step 2: Define

Write KPI names, formulas, sources and owners.

Step 3: Approve

Add validation and final sign-off responsibilities.

Learning Output: A formal requirement document that prevents scope confusion and calculation disputes.

Workbook Architecture

Build MIS Reports on a Controlled Data Structure

A reliable MIS workbook separates inputs, transformations, calculations, presentation and quality checks.

3 Design the Source-to-Report Workbook Flow

A professional workbook should not mix raw data, manual assumptions, formulas and management outputs on one sheet. Clear layers make refreshes safer and errors easier to investigate.

1InstructionsPurpose, owners, definitions and refresh steps.

2Raw DataProtected source extracts without manual editing.

3MappingsTargets, categories, owners and master-data tables.

4CalculationsHelper fields, KPIs, variances and summaries.

5MIS OutputManagement summary, exceptions and detail.

Recommended Workbook Sheets

Sheet	Purpose	Control
00_Read_Me	Report purpose, period, owner, definitions and instructions.	Version number and update log.
01_Raw_Data	Original transaction or operational data.	No manual formulas or formatting corrections.
02_Master	Employees, products, branches, targets and mapping values.	Unique keys and approved owners.
03_Calculations	Helper columns, KPI logic and reconciliation totals.	Protected formulas and documented assumptions.
04_MIS_Summary	Management KPIs, trends and commentary.	Locked presentation and refresh date.
05_Exceptions	Overdue, below-target, missing or unusual records.	Owner, action and closure status.
06_Control	Source counts, totals, errors and refresh checks.	PASS / FAIL indicators before distribution.

Example control formulas
=ROWS(SalesTable[Invoice_ID])
=SUM(SalesTable[Net_Sales])
=COUNTBLANK(SalesTable[Region])
=COUNTIF(Control_Status,"FAIL")

Data Structure Requirements

One row must represent one business record.
Each column must represent one consistent field.
Use Excel Tables for auto-expanding data ranges.
Keep dates as valid Excel dates and amounts as numeric values.
Use unique transaction or employee identifiers.
Separate targets and mapping tables from transaction records.
Avoid merged cells and manual subtotals in source data.

Practical Experiment 3: Build an MIS Workbook Skeleton

Create a controlled workbook before entering report logic.

Step 1: Create Layers

Add instruction, raw data, master, calculation, output, exception and control sheets.

Step 2: Add Controls

Create source row count, amount total and missing-field checks.

Step 3: Protect

Apply clear input, formula and output formatting conventions.

Learning Output: A maintainable report framework ready for functional MIS development.

Reporting Cadence

Design Daily, Weekly and Monthly MIS for Different Decisions

Frequency should reflect how quickly a measure changes and how soon management can act.

4 Match Report Frequency with Management Action

Daily MIS

Operational status, collections, attendance, production, service issues and urgent exceptions.

Weekly MIS

Performance trends, team productivity, pipeline movement, inventory risks and action closure.

Monthly MIS

Strategic results, budget comparison, profitability, workforce trends and management review.

Cadence	Typical Questions	Suitable Content	Design Guidance
Daily	What requires action today?	Yesterday, today-to-date, pending and urgent exceptions.	Compact, operational and distributed early.
Weekly	Are teams moving toward the monthly goal?	Week-to-date, run rate, trend, pipeline and corrective actions.	Include owner-wise review and open-action ageing.
Monthly	Did the business achieve plan, and why?	Actual, target, budget, prior period, variance, forecast and commentary.	Use a management summary supported by detailed schedules.

Period Calculations

=SUMIFS(SalesTable[Sales],SalesTable[Date],TODAY()-1)

Yesterday’s total for a daily report.

=SUMIFS(SalesTable[Sales],SalesTable[Date],">="&TODAY()-WEEKDAY(TODAY(),2)+1,SalesTable[Date],"<="&TODAY())

Current week-to-date sales.

=SUMIFS(SalesTable[Sales],SalesTable[Date],">="&EOMONTH(ReportDate,-1)+1,SalesTable[Date],"<="&EOMONTH(ReportDate,0))

Selected month total.

=Actual/Target

Achievement percentage; protect against zero targets with IFERROR where required.

Cut-off Discipline: Every report should state the data cut-off date and time. A report sent at 9:00 a.m. may include transactions only up to the previous day.

Practical Experiment 4: Create a Reporting Calendar

Plan the delivery cycle for one department.

Step 1: Classify

Separate daily, weekly and monthly KPIs.

Step 2: Schedule

Set source cut-off, preparation, validation and distribution times.

Step 3: Assign

Identify data owner, report preparer, reviewer and recipient.

Learning Output: A reporting calendar with clear frequency, responsibility and deadline.

Functional MIS

Build Reports for Sales, HR, Inventory and Finance

Each function requires different KPIs, comparisons, exception rules and detail levels.

5 Sales MIS and Customer Performance Reporting

A sales MIS should explain revenue performance, target achievement, growth, product contribution, regional performance, sales pipeline and collection quality. Avoid reporting only invoice value without considering returns, discounts and outstanding amounts.

Net SalesGross sales less returns and adjustments.

AchievementActual divided by target.

GrowthChange versus prior comparable period.

Average OrderSales divided by order count.

CollectionCash collected versus amount due.

Recommended Sales MIS Sections

Executive summary: actual, target, achievement, growth and forecast.
Region, branch, salesperson and product performance.
Top and bottom performers.
New customers, repeat customers and inactive customers.
Order pipeline and conversion stage.
Returns, discounts, overdue collections and credit exposure.
Management commentary and corrective actions.

Achievement % = Actual Sales / Target Sales
Growth % = (Current Period - Prior Period) / Prior Period
Average Order Value = Net Sales / Number of Orders
Collection Efficiency % = Amount Collected / Amount Due

Practical Experiment 5: Prepare a Weekly Sales MIS

Summarize target, actual, pipeline and collections by region.

Step 1: Calculate

Create actual, target, variance, achievement and growth measures.

Step 2: Analyse

Rank regions and identify below-target teams.

Step 3: Communicate

Add three observations and action owners.

Learning Output: A weekly sales performance summary with clear commercial actions.

6 HR and Attendance MIS

HR MIS converts employee records, attendance, leave, hiring, training and attrition data into workforce information. Protect personal data and restrict detailed reports to authorized users.

Area	Example KPI	Decision Supported
Workforce	Opening, additions, exits and closing headcount	Staffing and manpower planning
Attendance	Present days, absence rate, late marks and overtime	Operational discipline and scheduling
Recruitment	Open positions, time to hire and offer acceptance	Hiring capacity and bottleneck review
Attrition	Employee exits divided by average headcount	Retention intervention
Training	Participation, completion and assessment improvement	Capability development
Leave	Leave taken, balance and excessive absence exceptions	Resource planning and policy compliance

Absence Rate % = Absent Days / Scheduled Working Days
Attrition Rate % = Employee Exits / Average Headcount
Average Headcount = (Opening Headcount + Closing Headcount) / 2
Training Completion % = Completed Learners / Enrolled Learners

Confidentiality: Avoid sharing salary, personal identifiers, medical information or individual performance data beyond authorized recipients.

7 Inventory and Procurement MIS

Inventory MIS should help the business maintain service availability without locking excessive funds in stock. It combines quantity, value, movement, ageing, reorder requirements and supplier performance.

Availability

Opening stock, receipts, issues, closing stock and stock-out items.

Movement

Fast-moving, slow-moving, non-moving and obsolete inventory.

Investment

Inventory value, ageing, days on hand and excess stock.

Core Inventory Measures

Closing Stock = Opening + Receipts - Issues ± Adjustments

Quantity reconciliation.

Stock Turnover = Cost of Goods Sold / Average Inventory

How efficiently inventory is used.

Days on Hand = Average Inventory / Daily Usage

Estimated coverage available.

Reorder Alert = IF(Closing Stock<=Reorder Level,"ORDER","OK")

Action-oriented replenishment control.

Reconciliation Rule: Quantity movement and inventory valuation should be independently reconciled. A correct quantity report can still contain incorrect rates or values.

8 Finance, Expense and Cash-Flow MIS

Finance MIS monitors revenue, cost, profitability, budget usage, receivables, payables and liquidity. The report should clearly distinguish accounting actuals, budgets, forecasts and operational estimates.

Report	Key Measures	Management Focus
Profitability	Revenue, gross profit, operating cost, operating profit and margin	Performance and cost control
Budget	Actual, budget, variance and utilization percentage	Overspend and resource allocation
Receivables	Outstanding amount, ageing bucket, overdue percentage and collection forecast	Credit and cash recovery
Payables	Due amount, upcoming payments and overdue suppliers	Cash planning and supplier continuity
Cash Flow	Opening cash, inflows, outflows, closing cash and short-term forecast	Liquidity management

Gross Margin % = (Revenue - Direct Cost) / Revenue
Budget Variance = Actual - Budget
Budget Utilization % = Actual Expense / Approved Budget
Overdue % = Overdue Receivables / Total Receivables

Practical Experiment 6: Build a Functional KPI Dictionary

Create a standard definition sheet for sales, HR, inventory and finance measures.

Step 1: Define

Write KPI name, business meaning and formula.

Step 2: Control

Add source, frequency, unit and owner.

Step 3: Approve

Add effective date and approval status.

Learning Output: A shared KPI dictionary that prevents inconsistent calculations across reports.

Exception Reporting

Direct Management Attention to Deviations and Risks

A strong MIS does not merely describe performance; it identifies what requires intervention.

9 Build Exception Rules, Ageing and Action Tracking

Exception reporting isolates records outside acceptable limits. Rules must be objective, measurable and approved. Each exception should show severity, owner, due date, action and closure status.

Below TargetPerformance lower than approved threshold.

OverdueAction, invoice or task past due date.

MissingRequired field or source record absent.

UnusualValue outside normal range or expected pattern.

Non-CompliantRecord violates policy or control rule.

Example Exception Formulas

=IF(Achievement<80%,"Critical",IF(Achievement<95%,"Watch","On Track"))

Performance severity classification.

=IF(AND(Balance>0,DueDate<TODAY()),TODAY()-DueDate,0)

Overdue days for receivables.

=IF(COUNTBLANK(RequiredFields)>0,"Incomplete","Complete")

Required-field control.

=FILTER(DataTable,DataTable[Status]<>"On Track","No exceptions")

Dynamic exception list for modern Excel.

Exception Register Fields

Field	Purpose
Exception ID	Unique reference for tracking and discussion.
Category and Severity	Type and priority of the issue.
Description	Clear statement of what is wrong.
Impact	Financial, operational, customer or compliance consequence.
Owner	Person responsible for corrective action.
Due Date and Age	Required closure date and elapsed time.
Action and Status	Planned response and current progress.
Closure Evidence	Proof that the issue has been resolved.

Practical Experiment 7: Create an Exception Register

Convert a list of below-target and overdue records into an action tracker.

Step 1: Classify

Define severity rules for critical, watch and on-track status.

Step 2: Assign

Add owner, due date and corrective action.

Step 3: Review

Calculate age and identify overdue open actions.

Learning Output: A management exception register that supports closure rather than passive reporting.

Automation and Governance

Create Repeatable, Controlled and Distribution-Ready MIS

Automation should reduce repetitive work without hiding logic or weakening validation.

10 Refresh, Validate, Distribute and Archive the Report

Use Excel Tables, formulas, PivotTables, Power Query and macros according to the reporting need. Every automated process still requires source checks, reconciliation and approval.

Refresh

Import or paste source data, refresh queries and PivotTables, and update calculation timestamps.

Validate

Check row counts, totals, duplicates, blanks, errors, period filters and report reconciliation.

Distribute

Lock formulas, prepare PDF or controlled workbook, approve, send and archive the version.

Recommended Control Sheet

Control	Expected Result	Status Logic
Source Row Count	Matches source extract or approved record count.	PASS when equal.
Source Amount Total	Matches accounting or operational control total.	PASS within approved tolerance.
Duplicate Keys	Zero unexpected duplicate transaction IDs.	FAIL when count exceeds zero.
Missing Mandatory Fields	Zero blanks in critical columns.	FAIL when incomplete records exist.
Formula Errors	No unhandled Excel errors in output ranges.	FAIL when error count is above zero.
Period Check	Maximum transaction date aligns with report cut-off.	PASS when dates are current.
Approval	Reviewer name, date and status recorded.	Distribution allowed only after approval.

Version and Distribution Discipline

Use a consistent filename such as Sales_MIS_2026-07_v1.0.xlsx.
Display report period, data cut-off, refresh timestamp and preparer on the summary.
Protect calculation sheets and unlock only intended filters or inputs.
Remove hidden sensitive data before sharing externally.
Archive the approved version and avoid overwriting historical reports.
Maintain a change log for formula, definition or source modifications.

Automation Rule: Never automate an unstable process. First standardize the source, definitions, controls and approval workflow; then automate repetitive steps.

Practical Experiment 8: Perform a Mock MIS Refresh

Run the complete reporting cycle using a new period of data.

Step 1: Refresh

Replace or import the new source and update calculations.

Step 2: Validate

Complete all control checks and correct failures.

Step 3: Publish

Record approval, export the output and archive the version.

Learning Output: A documented, repeatable and controlled MIS publication process.

Interactive MIS Planner

Select a Reporting Situation and Receive a Design Recommendation

Use this tool to connect reporting audience, business function and cadence with a practical MIS structure.

Primary Audience Business Function Reporting Frequency

Recommended starting point: Select the audience, function and cadence, then generate your reporting plan.

Real-Time Practical Assignment

Build an Integrated Monthly Management MIS Workbook

Create a professional report that combines sales, HR, inventory and finance information into one controlled management pack.

Prepare Source Tables

Create or import separate Tables for sales, workforce, inventory and expenses.

Create Master Data

Add regions, departments, product groups, owners, targets and approved mappings.

Define KPIs

Prepare a KPI dictionary with formula, unit, source, owner and reporting frequency.

Build Summaries

Create actual, target, variance, trend and exception analysis for each function.

Create Management Page

Present headline KPIs, trends, risks, commentary and action status on one page.

Add Controls

Validate row counts, source totals, blanks, duplicates, errors and period completion.

Prepare Exception Register

List critical deviations with severity, owner, due date and closure status.

Publish and Archive

Complete review, protect formulas, export management output and save the approved version.

Present Findings

Explain five important observations and three priority actions in a short management review.

Expected Deliverable: One refreshable Excel workbook containing instructions, source data, master tables, calculations, functional MIS pages, management summary, exception register, control checks and approval record.

Assessment Standard: The workbook must be accurate, logically structured, clearly formatted, fully reconciled and understandable to another user without verbal explanation.

Practice Worksheet

Complete These MIS Development Tasks

Save each result as evidence of your reporting and management-analysis skills.

Draft an MIS Requirement DocumentDefine audience, purpose, decisions, KPIs, cadence, owners and delivery deadline.

Create a KPI DictionaryDocument at least 15 KPIs from sales, HR, inventory and finance.

Build a Control SheetAdd row count, amount total, blank, duplicate, error and period checks.

Create a Daily MISPrepare a concise operational report with urgent exceptions and owners.

Create a Weekly MISShow week-to-date progress, monthly run rate, team ranking and action closure.

Create a Monthly MISCompare actual, target, prior month, prior year, variance and forecast.

Prepare an Exception RegisterTrack at least ten deviations with severity, owner, due date and status.

Write Management CommentaryPrepare five insights using fact, cause, impact and action structure.

Run a Refresh TestAdd new source records and confirm every report and control updates correctly.

Prepare a Report Handover NoteDocument source paths, refresh process, formulas, controls, recipients and troubleshooting.

AICPE Quality Learning Commitment

AICPE Gurukul focuses on practical, skill-based and career-oriented learning that helps students, institutes and professionals create useful workplace solutions. Learn more at aicpeindia.org and aicpe.online.

Common Mistakes

MIS Reporting Mistakes Students Should Avoid

Small structural and control errors can make an impressive report unreliable.

Wrong Practices

Reporting numbers without targets, trends or definitions.
Mixing raw data, formulas and presentation on one sheet.
Using manually selected ranges that exclude new records.
Changing KPI formulas between months without approval.
Hiding exceptions to make performance appear better.
Distributing before source totals are reconciled.
Sharing confidential data with unauthorized recipients.
Overwriting prior approved report versions.

Professional Practices

Connect each KPI with a decision and owner.
Use layered workbook architecture and Excel Tables.
Maintain a KPI dictionary and reporting calendar.
Show actual, target, variance, trend and explanation.
Maintain a transparent exception and action register.
Complete documented control checks before publishing.
Protect sensitive data and use controlled outputs.
Archive approved reports with version numbers.

Remember: A visually attractive MIS with incorrect or late information can create worse decisions than having no report at all.

Quick Revision

Remember These MIS Reporting Principles

Review the essentials before beginning Power Query and automated data transformation.

Decision First

Define audience, business purpose, decisions and KPI ownership before designing the report.

Layer the Workbook

Separate sources, mappings, calculations, outputs, exceptions and controls.

Match Cadence

Use daily reporting for immediate action, weekly reporting for progress and monthly reporting for strategic review.

Add Context

Show actual, target, variance, trend, benchmark and management commentary.

Report Exceptions

Assign severity, owner, due date, action and closure status to deviations.

Control Publication

Refresh, reconcile, review, approve, protect, distribute and archive every MIS version.

Protected Learning Content