How to Prepare MIS Report? (In Tally & Excel)

“Management Information System” or MIS, as it is generally referred to the world over, is a way for managers and the top-level administration from all divisions to gather and evaluate information related to everyday tasks and production data.

It is a great tool to be able to assess your workforce as well as business operations for inconsistencies and roadblocks.

Get Free Demo: Best GST Software in 2020

 To understand it better, you need to picture MIS as an amalgamation of hardware, software, business processes involved in reporting to management.

Benefits of MIS Reports

As tedious as they may seem, MIS reporting is a field that requires a great deal of experience and knowledge. There are various reasons why MIS reports are an integral part of every business,

• To gain an overview of how the business is performing.
• To know about a bottleneck in the system and get a real-time sense of any hindrances in the system.
• Efficient results warrant remedial action in case the results are not satisfactory.
• By comparing outcomes over a period of time, business strategies can be worked out, and workflow improvements are suggested to the workforce.
• The business is better aligned with customer expectations with the help of forecasts and charts and graphs.
• Data is represented in a better and more understandable manner to the management.
• Once you are familiar with Tally and Excel operations and commands, running an MIS report is not too difficult.

MIS Report and Types

Essentially, MIS reports involve preparing a condensed and indexed list of various aspects and parameters of one or more departments such as orders, revenues, workforce data, attrition details, and other HR-related data to compare it with a predefined set of matrix sets for a particular organization.

How would it help the business? It’s simple – by comparing the existing parameters against the predefined standards, it helps determine any anomalies as well as inadequacies. It provides a sense of direction to focus on business goals and helps organizations adopt best practices. In a nutshell, it gives a clear picture of where the organization is headed.

Types of MIS Reports

1. Financial Statements

Statements such as Funds Statement Report, Cashflow Statement Report, Profit Report, Cost Reports, and Income Statement Report help to understand the current financial position of the organization.

• Funds Statement Report helps you to keep track of the funds and are usually created by the accounting department. Hence, inflow and spending of all funds are reported in this format.
• Cash Flow Statement Report helps track the cash influx and spending. It helps the business operations team to keep a tab on all cash transactions. They are inter-referenced with the Funds Statement as the cash spent usually goes from fund sources.
• Cost Report is one of the frequently prepared ones; it helps report on costs incurred across all departments and gives a clear picture of the costs for an organization.
• Profit Report helps the accounts team to project profit earned by the organization. It draws a comparison between the expected profit and the actual one generated. This is a great report to analyze factors leading to a loss situation.
• Income Statement Report helps report on taxes, income, and balance sheet, thus providing the financial data in totality.

2. Sales Reports

Sales MIS Reports work similarly to the Profit report; that is, they help draw a comparison between the projected sales as well as the actual sales made. This helps provide a deep insight into how products and services are performing in the market.

3. Budget Reports

A budget helps keep expenses in check. Hence, budget reports around expenses, income, cash, as well as production help utilize allotted budgets wisely.

4. Inventory Reports

For organizations dealing with goods, Inventory Reports provide data around the availability of stocks, items that aren’t moving, stocks location-wise, etc. This is helpful to track assets.

5. Outstanding Management for Customers and Suppliers

An MIS report on outstanding payments and invoices from the customer’s perspective helps report on any unaccounted or deficit income.

6. Statutory Reports

Reporting is not always to higher management; certain reports need to be shared with tax and government authorities. These ensure regulatory compliance with rules and laws.

7. Payroll Reports

Reports that provide information around total CTC of the entire workforce or that of specific departments are categorized under Payroll reports.

8. Ratio Analysis

A financial report primarily, the Ratio Analysis report helps to compare the profit earned against the cost incurred.

9. Manufacturing Losses Reports

Manufacturing defects, production failure due to faulty machinery, or any natural calamity hampering the manufacturing would all be reported under this category of MIS report.

10. HR Reports

Finally, anything to do with the workforce from hiring or termination data to performance data, HR MIS reports cover a wide range of employee-specific data.

Steps to Prepare MIS Report in Tally

MIS report can be easily created with the help of Tally ERP. There are four main types of MIS Reports created using Tally,

1. Inventory Reports: To maintain stock of all the inventory (assets).

2. Accounting Reports: To report on the current financial condition of the organization and the operational performance organization-wide.

3. Financial Reports: To report on the financial condition from the shareholders’ perspective.

4. Management Control Reports: Reports on budget, costs, income, etc., to help higher management get a picture of what’s going on.

Let’s go through the creation of some of these MIS reports in Tally,

1. Bill Payable

Payments due for future dates are categorized under the “Payable” type of reports. To navigate to the Payables screen, simply go to the gateway of Tally, then Display, then Statement of Accounts, then Outstanding, and finally to Payables.

Sample report would look like this,

Image Source –  tallyeducation

2. Group Outstandings

All outstanding payables for an organization come under the “Group Outstandings” report. To navigate to the Group Outstandings screen, simply go to the gateway of Tally, then Display, then Statement of Accounts, then Outstanding, and finally to Group.

A sample report would look like this,

Image Source – tallyeducation

You could choose to apply filters of your choice like Period of reporting, group-wise reporting, ledger-wise reports, Ageing analysis, Auto (to report weekly, monthly, fortnightly, yearly wise), or party-wise breakup.

3. Ageing Analysis

Ageing Analysis report gives you a picture of the number of bills left unserviced or unpaid for a specific period of time.

For instance, the Ageing Analysis report for “Payables” can be extracted by navigating to the gateway of Tally, then Display, then Statement of Accounts, then Outstandings, and finally to Bills Receivable.

A sample report for reference,

Image Source – tallyeducation

4. Cash Flow Report

To know the cash movement within your organization, simply navigate to the gateway of Tally, then Display, Cash/Funds Flow, and finally to Cash Flow.

A sample report would look like this,

Image Source – academia

5. Fund Flow Report

Similar to Cash Flow, you could also report on the Funds movement by navigating to the gateway of Tally, then Display, Cash/Funds Flow, and finally to Funds Flow.

A sample report would look like this,

Image Source – academia

6. Cash Flow Projection Report

To access the Cash Flow report, navigate to Gateway of Tally, then Display, Cash/Funds Flow, and finally to Cash Flow Projection.

Sample Report,

Image Source – tallyeducation

7. Ratio Analysis Report

One of the most powerful reports for management reporting to report on the organization’s financial stats like Liquidity, liability to risks, and payables and Receivables, Ratio Analysis report helps make better decisions. Based on these reports, firms can study diversification, plan additional investment, and plan to expand the business among other key benefits.

To generate the Ratio Analysis, navigate to Gateway of Tally and then Ratio Analysis. 

Sample Report,

Image Source – academia

Steps to Prepare MIS Report in Excel

Another great way to create an effective MIS report is by using Excel. The MIS report can be a short one-pager report reporting simple stats or can run into several tabs reporting on different variables. 

Types of MIS Reports in Excel

1. Manufacturing Report: To report on all manufacturing data like goods, manufacturing date, Manufacturing batch, Manufacturing location, Manufacturing Date/Time, etc.

2. Production Report: Similar to Manufacturing, this is to report on all production-related data. The format would differ from company to company based on what they want to make a report.

3. Logistics Report: You can use MIS reports running analytical data reporting on logistics data as well such as Inventory, Transportation, Storage and Packaging, Production Planning, Forecasting of Demand, etc.

4. Sales Report: You can include key elements like Deals closure, Deals in the pipeline, the status of deals in pipeline, Deal Closure date, Sales Cycle duration, etc.

5. Warehouse and Construction: Similar to the reports above, you can report on the various elements around Warehouse and Construction. The format, of course, differs for every company but the key elements can be the same.

So you see, there’s an MIS report for any sector and any industry. 

A simple MIS report reporting consumer product sales across different regions would look like this,

Image Source – excel124

Let’s go through a few examples,

1. Sales Report including Sales Forecast

You need to report to the higher management about current Sales stats and also apprise them of the yearly and quarterly forecast, so you know how the good or service is doing.

Include the details of the good, sales stats of good like quantity, revenue, number of orders placed for the good, total good sales. For the forecast reporting part, you can include the values/revenues and the margin expected for the next month, quarter, or for the year. 

Sample report would look like this,

Image Source- bestcollections

Also, Read: A Complete Guide on How to File GSTR-3B Online

2. Profit and Loss report

Suppose a company wants to report on how many expenses they incurred and the profits they made.

Include month-on-month data on the Sales made and the Marginal Profit %. List down expenses such as Rent, electricity, water (in case it’s a brick-and-mortar set up), the total salary of employees, transportation costs if the company provides transport or an allowance instead, expenses incurred for business travel trips, or any other consumables. Include a separate tab for Sales Profit and Sales Expenses. 

A sample report would look like this,

Image Source – exceltemp

3. Daily Accounts Report

A typical Daily accounts reports maintained by firms would look like,

Image Source – bestcollections

Things to know before creating MIS reports in Excel

• Advanced knowledge of Excel and pivots/formulae is needed to get started on MIS reporting.
• Deep understanding of where the source data is coming from and the relationships between the different variables involved.
• Know who the audience is so you can prepare the report accordingly and include only elements that are relevant. Also, ensure that the report is easy to read and all the stats are delivered clearly in the report and do not require any further processing.
• Perhaps before the previous step, take the requirements in detail from the stakeholders of the report so you know exactly where to start in terms of accessing and collating the data and proceeding with only the relevant reporting data.
• It depends on an efficient data gathering tool that can extract data from diverse data sources like spreadsheets, flat files, RDBMS databases, etc.
• Get a process in place to cleanse the data before use and get rid of the rubbish or unusable data and hindrances like incomplete data, invalid values, or null values. 
• You could consider keeping the raw data in a different sheet so it’s easier to link to the report and also prevent any tampering with it.

Other Types of MIS Reports

1. Real-time Reports

These reports help to monitor reporting data as they happen. Typically useful for immediate supervisors in Call centers or production or similar businesses, where the spike in call volume or sudden interruption or decline in production can be a cause of worry. They are also very useful to make amends in the system with constant monitoring so the process is seamless and smooth.

2. Scheduled Reports

If you don’t wish to see data changes in real-time but need to see them at a specific time of the day, say first thing in the morning or before the end of the day, scheduled reports enable you to set parameters and the report will be generated automatically. It also cuts down on manual intervention and errors due to human errors (say in setting wrong or incomplete parameters or entering incorrect data). 

3. Ad-hoc Reports

Say you don’t wish to see a report every day or real-time, then your best bet is to have the team run the report on an ad-hoc basis. These are typically created only when the need arises. You can extract the report manually using formulae and pivots or use automated tools to extract a report there and then.

Conclusion

The format of generating reports in your company or organization may depend on what kind of departments you work with and also how much resources you utilize to keep the departments running. Good management information systems can help you generate valuable reports that can keep your business competitive and also give you a better insight to make well-educated business decisions in the long run.