How to Make MIS Report in Excel for Sales (with Easy Steps)

In this article, we’re going to show you the quick steps of how to make an MIS report in Excel for sales. To demonstrate our method, we’ve picked a dataset with 5 columns:Product Name”, “Type”, “Month”, “Region”, and “Sales”.

Make MIS Sales Report in Excel


Download Practice Workbook


What Is MIS Report?

MIS stands for “Management Information System”. With this, top management can make effective decisions. This also provides the opportunity of communicating information to all stakeholders. Moreover, a company’s marketing and sales departments can analyze sales data using this report. Consequently, this will help the company to maximize profits. Moreover, there are 5 components of an MIS report People, Data, Business Procedures, Hardware, and Software. We’ll use these to create our MIS sales report.


4 Steps to Make MIS Sales Report in Excel

Step-1: Utilizing Excel PivotTable Feature to Make MIS Sales Report

Firstly, we’re going to use Excel PivotTable to organize our dataset. Secondly, we’ll add Charts. Finally, we’ll move those Charts into a new Sheet and insert Slicers to modify our output. Without further ado, let’s jump into the task.

Steps:

  • Firstly, select the cell range B4:F12.
  • Secondly, from the Insert tab >>> select PivotTable.

Utilizing PivotTable to Make MIS Sales Report in Excel

Then, a dialog box will appear.

  • Thirdly, select Existing Worksheet and select cell B2 in our “PivotSheet as the output location.

After that, we’ll see the PivotTable Fields dialog box.

Utilizing PivotTable to Make MIS Sales Report in Excel

  • Then, select all the Fields. This will put everything except the Sales fields into Rows.
  • After that, drag the “Region” field to Columns.

We should get an output similar to this.

Utilizing PivotTable to Make MIS Sales Report in Excel

Read More: How to Make Monthly Report in Excel (with Quick Steps)

Step-2: Inserting Charts in Excel

We’ll insert Charts using the PivotTable Tools in this section. Then, we’ll add PivotCharts. This will help us visualize the dataset. To do this –

  • Firstly, select anywhere on the PivotTable output data.
  • Secondly, from the PivotTable Analyze >>> select PivotChart.

Inserting Charts to Make MIS Sales Report in Excel

Then the Insert Chart dialog box will appear.

  • Thirdly, from Column >>> select Clustered Column.
  • Then, press OK.

We’ll see a column Chart on our Sheet.

Inserting Charts to Make MIS Sales Report in Excel

Graph Breakdown

The management can understand the following sales information from this Graph.

  • iPhone 13 is more profitable in the South region.
  • The demand is higher for iPhone 13 Pro in the East region.
  • For laptops, both models of MacBook are more prevalent in the South region.
  • Therefore, the company should increase the inventory of iPhone 13 for South, iPhone 13 pro for East, and Laptops for the South region.

Then, we’re going to add a Pie Chart.

  • Firstly, from the PivotTable Analyze >>> select PivotChart.
  • Secondly, from Pie >>> select Doughnut and press OK.

A Doughnut type Pie Chart will be shown.

Inserting Charts to Make MIS Sales Report in Excel

Graph Breakdown

The management can learn about these things by looking at the Graph. Moreover, the outer ring is for the South region.

  • If we take only consider the Product Types, then Laptop accounts for 60% and 50% of Total Sales for the South and East region respectively.
  • The higher model of the MacBook is twice as popular as the Base model in the South Region.
  • In the East region, iPhone 13 Pro is 4 times as popular as the iPhone 13.

The management can know the exact amount of profitability and demand for each product for a particular region.

Finally, we’re going to add Line Chart.

  • Firstly, from the PivotTable Analyze >>> select PivotChart.
  • Secondly, from Line >>> select “Line with Markers” and press OK.

A “Line Graph” will appear.

Inserting Charts to Make MIS Sales Report in Excel

Graph Breakdown

  • For Mobiles, the higher model has generated more sales. However, this is not true for Laptops.
  • The East region accumulated more sales with Mobile phones.
  • The South region gained more revenue for the base Laptop model.
  • The company may decide to drop the MacBook Pro 16” model from its product portfolio.

Read More: How to Make Sales Report in Excel (with Easy Steps)


Similar Readings


Step-3: Moving Charts to Another Sheet

In this section, we’ll move the Charts to a new Sheet named “Graphs”. This will help us to display our data in a clean Sheet.

  • Select, any of the three charts.
  • Then, from PivotChart Analyze >>> select Move Chart.

Moving Charts to Excel Sheet while making MIS sales Report

The Move Chart dialog box will appear.

  • From “Object in” select “Graphs”.
  • Then, press OK.

Similarly, move all the Charts to the new Sheet.

Moving Charts to Excel Sheet while making MIS sales Report

Read More: How to Generate Reports from Excel Data (2 Easy Methods)

loying Slicers in Excel to Make MIS Sales Report

In this step, we’ll add Slicers. This will allow us to apply Filters to our Charts.

  • Firstly, select any of the Graphs.
  • Secondly, from the PivotChart Analyze tab >>> select “Insert Slicer”.

Employing Slicers to Make MIS Report

A dialog box will appear.

  • After that, select the first 4 fields then click on OK.

The Slicers will look like this.

Employing Slicers to Make MIS Report

We can click on any of the fields to change our datasets.

  • Select “March” under the Month Slicer.

This will show only the values from the month of March.

Employing Slicers to Make MIS Report

Moreover, our Slicer will modify all the existing Charts. Thus, we’ve created an MIS sales report in Excel.

Read More: How to Make Monthly Sales Report in Excel (with Simple Steps)


Practice Section

We’ve added a practice dataset in the Excel file.

make mis report in excel for sales


Conclusion

We’ve shown you quick steps on how to make an MIS report in Excel for sales. If you face any problems, feel free to comment below. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I am passionate about all things related to data and MS Excel is my favorite application. I want to make people's life easier by writing easy-to-follow and in-depth guides here at Exceldemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo