Management Information System (MIS) – Excel Formula

Management Information System ( MIS )
Management Information System ( MIS)

Management Information System (MIS)

Here we are going to provide you detailed information about what is MIS, where MIS activities can be used, what exactly to be done in the MIS, How to manage MIS Reports and MIS Excecutives / Data Analysts, how to get MIS Executive Job for freshers, experienced people, Qualities of the MIS Executive / Data Analyst and MIS job advantages and disadvantages.

We also discuss some important MIS Daily Reports like Head count report, SLA Report, Procure to pay report ( P2P Report ), AP Dashboard, AR Dashboard, GL Dashboard Invoice Summary Report, Payroll team related.

What is MIS?

MIS is nothing but managing the huge information in a sequential order in order to retrieve the required information. Whenever we require the particular information from the huge database, you should have the proper plan to maintain the data.

Where MIS activities can be used?

MIS activities can be used in the corporate companies, government sectors and many other places where the huge data involves.

What exactly to be done in the MIS?

As discussed in the above, we have to maintain the huge data to retrieve again on need basis. Hence usually if the data is limited we can go head with the MS Excel. And MS Excel is compulsory to use in the MIS. Because calculations purpose MS excel is the excellent application. Usually in the MIS, Information would be stored as reports, dashboards and in the other data formats. In order to store the data in the sequential order, we will maintain in the reports wise. If MS excel is not capable to maintain the data, then we can go ahead with the MS Access to store the data. IF data is more 1 GB in one single file then we have to choose the SQL or other database model.

Excel single file capacity 30 MB. if exceeds more than 30 MB, then we have to choose the MS Access

MS Access single file capacity 1 GB. If exceeds more than that, then we can choose SQL database or any other database oracle based availability.

How to manage MIS reports, Dashboards ?

Managing the MIS Reports:

Usually we prepare the reports for the daily, weekly, monthly, quarterly, half-early or annually in a single file for the respective day/ week/ month etc.. we will keep the important cleaned data in a particular order column header wise to pick the important numbers ( Business drivers) to show the activity related status in a report. These reports to be cascaded to top level management and they take the decisions based on these numbers also they come to know the present situation of the activity.

Managing the MIS Dashboards:

When you create the dashboards, you can keep the data sheets in the hidden mode and show only the display sheet which have important numbers and graphs related sheets. based on the frequency data can be empty and refill the data into the dashboard template.

Here one important thing is we need to have the formulas if we are going to prepare in the MS Excel. Excel formulas would play the key role in the MIS Reports/ Dashboards. This is one excellent advantage with the MS Excel. Any difficult thing can be achieved with formulas. we have the good formatting look in the MS Excel. That’s why management prefers to show the data in the Excel than any other applications.

MIS Executives / Data Analyst :

MIS People are decision makers who can advise the awesome ideas to the top level management. Operation manager must have the MIS skills. MIS executives are also called as Data Analyst.

Qualities of MIS Executives / Data Analyst :

This job role is very crucial for the company. All the confidential information will be there with the MIS Executive. Hence it is very important to hide the information from the non related people. Data Analyst must hide the lot of information from non related people.


Assume that John is the MIS Exective and he is supporting to 4 departments in the same team. Hence he has all the confidential information of 4 departments. In such case one of the departments manager Rock came and asked about the total income for the month for the other department. Then John will be in trouble, he can not reject his request and at the same time he should not provide that information to Rock. Then John should say, “i am yet prepare the report, once it is done, I will let you know”. That’s how john should escape from Rock’s request. Because it is not related to Rock’s department. Due diligence is very important quality for the Data Analyst.

Payroll Team Related MIS Activities:

Pay role team also has the MIS activities. Since this team maintains the salaries of the all employees, it is highly confidential task. Also it is very challenging job to process the salaries on monthly basis. I will provide another example which was happened to my colleague. He has process one guy salary to another guy because of the naming confusion. Two employees have to same name. That’s why he got confused and interchanged their salaries. It has become very big mistake in the Payroll MIS team.

Similar way finance team also has MIS activities. In this team all the reports will deal with the money related. Hence it is very challenging job to do. Every time MIS guy would deal with lot of money related numbers. If any report goes with wrong number, then company would be in trouble. Hence in this department, management would do double check all the MIS activities. Still sometimes errors occurs. whenever error occurs in the finance team, management gets many calls from their higher level like from clients. Hence It is very tough thing to manage, sometimes need to pay the penalty to client by company.


How To Get MIS Executive Job as a fresher or experienced?


  1. Educational qualification of the MIS Executive is a graduate from any bachelors
  2. Hands on experience should be there in the MS Excel mainly and better to have power point presentation skills.
  3. Should be capable to speak English fluently without any MTI ( Mother tongue influence). Means when people speak their mother tongue should not be appeared in the spoken English. Because sometime we may need to handle client calls at that point of time. Client may face problem to understand because most of the clients are from the USA, UK or Australia. If the client is from India that is fine. If we keep practice on daily basis, it is easier to remove the MTI from our spoken English
  4. It is better to have 30 wpm minimum typing speed. Because sometimes there are requirements to type some information on need basis. It is very rare but good to have the typing skills
  5. Better to have the Analytical knowledge, since always MIS executive would work the huge data. Freshers can ignore this point as they will not have any idea at the beginning stage
  6. MIS Executive always plays with the huge data. Hence should be well versed with data analysis knowledge. Means when executive receives the data, need to check the what exactly the information and what exactly to be helped to management and what are the business drivers in the given data as such.
  7. Good to have 7 QC tools (1 Pareto Analysis, 2 Histogram, 3 Fishbone analysis (cause and effect diagram), 4 check sheet, 5 Control chart 6 Scatter diagram, 7 Stratification) knowledge but not compulsory.
  8. Pivot tables preparation is must for MIS Job position. Because most of the time need to handle with the huge data. When we have huge data, it is very difficult to understand the whole data at once. Hence if MIS job holder has capability of the creating pivot tables, It helps him a lot in understanding the whole data in a easy manner. Also helps him in creating the reports and dashboards
  9. Should be capable with Excel dashboard creation, Excel reports, Excel template. When you say dashboards or reports. These are involved the excel formulas and other excel techniques like hide in the excel sheets and all. In order to learn this either you can see the older dashboards or google it with the excel dashboards and get the sample dashboards to understand. Freshers can ignore this point as they just need only excel formulas skills.
  10. Excel Charts or Graphs would play key role in the MIS job. As management only looks these charts when they have no time to see whole information. Based the given data, MIS Executive would select the graphs like bar graph, pie chart or any other which suits to existing data points

The above points are very important for experienced guys and freshers who wants to join as MIS Executive. Few points are not compulsory to fresher as i have already mentioned in those respective points.

MIS Reports / Dashboards:

Here we can learn about some important MIS reports and dashboards with examples.

Head count report:

This report conveys the number of people working in the respective team or in the organisation. HR will have this head count report for entire company as he/ she is responsible to maintain this report. If the team size is bigger more than 50 people approximately. Usually MIS Executive or HR prepares this report on monthly basis as they have to know the number people in the respective team or in the company. Now we can learn how to prepare this report. At first you need to have the all the employee details in the one worksheet. Like First Name, Last Name, Email address, Reporting manager, Joining date etc. As shown in the below

Head count report
Head count report

In the above list, whenever employee leaving the organization or team change, we need to capture. Then we can find the opening balance for the next month, closing balance for the current month and department wise number of employees.

Every month end you have to prepare this report, then you will come to know the exact head count on monthly. When you do this, you also find the attrition percentage. This is confidential report.

Procure to pay report ( P2P Report ):

This report is mainly for vendor payments and invoice ageing status. When we are producing one product, we require some raw materiel to produce. or if we have to provide some service also we require some things in order to provide the service. To provide the service or raw material, we have to pay amount to vendors. At that point of time, this procure to pay concept comes into the place. In order to pay the amount, we will have some time and dead line. we prepare this report to know about the vendor payments mainly. We will have some service level agreement to pay the amount.


when we need to pay some amount to the vendors, we will have some duration to pay that. Hence we must maintain one report for calculating the time on day to day basis. In order to do this process, we will have the invoice copies. These are classified as purchase order, No purchase order, Travel & Expenses as such. In this case No purchase order to process, value of the purchase order payments to be captured. This report will have the below tabs ( Sheets) approximately. Similar way various business models will have their respective tabs.


Summary Dashboard sheet can be shown as in the below manner. This is just an example to understand by yourself. You can prepare the same in your own manner based on your client requirement.

SLA Report sheet has the ageing like  0-3 days, 4-7 days, 8-14 days, >14 days. Using the excel formulas, we can pull the number of days into the above ageing columns as shown in the below

Ageing table example
Ageing table example

Similar way we can show the Purchase order, No Purchase order, Freight, Travel and Expenses summary in different sheets. Once we prepare these sheets, we can show one consolidated worksheet to for all the overall information. Then we have to prepare the payments sheet, to show the payment information for the vendors as on that particular day.

Hope you understand the Procure to pay report in detailed.

MIS Job role Advantages:

  1. You will not get continues flow of work. Actually speaking bit lesser work most of the times. Because 100% Accuracy required in this job. 4 hours of the work would be assigned in the 8 hours of job time.
  2. Typing work would be very lesser comparing to other non voice jobs.
  3. It would be very interesting and enjoyable, when you are dealing the challenges with Excel formulas or helping others to solve their excel template issues
  4. Most of the other team members would respect the MIS Executive, because this job role would support to the team as I explained in the above point.
  5. Once this job gets habituated with all the assigned tasks, it would be cake walk for working in daily.
  6. Managers would rely on the MIS Executive’s report numbers to take their crucial decisions in the business
  7. Many team members would try to catch you all the time to learn excel tricks from MIS Executive
  8. This job role would circulate reports to top level management hence MIS Executive will have the good connectivity with top level management like BU Heads.
  9. This job role will even send the reports to clients. Hence even in client location people also gives the respect to this job role. Since they will also send requests to prepare the Ad hoc reports
  10. By doing all these activities, usually MIS Executive would become an subject matter expert in their respective team activities.


MIS Job role Disadvantages:

  1. MIS guy would rely on production team member’s inputs to prepare the report, dashboards. This would little bit hectic process due to some reason, when they are unable to send the inputs, Report circulation would be delayed. Managers would ask the MIS guy for the report as they would like to know the process current situation.
  2. Whenever clients come with ad hoc reports requirement, it would be an urgent requirement. MIS guy should prepare the daily reports as well as this ad hoc report for the client. That particular time, this MIS Executive would face a problem to work in fast pace mode. In the Workforce management team, MIS Executive has the always tasks in the queue to do.
  3. Sometimes production would update the numbers incorrectly and the same would be taken by MIS guy and getting errors in the report after next day. That time it would be bit difficult to prove that he has done correctly.
  4. Payroll team MIS job also bit difficult to do because it is almost similar to bank job. Every paisa should be accountable while preparing the payroll salary report or any other report in the payroll team.

Based on the above points, you can understand MIS Job would be enjoyable also hectic task sometimes. Hence MIS executive should learn how to play the safe game in the job role while dealing with the production team or any other top level management.




  1. Hello. I have checked your and i see you’ve got some
    excellent content so probably it is the reason that you rank high in google.
    You are going to improve fast.


Please enter your comment!
Please enter your name here