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”.
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.
How to Make MIS Sales Report in Excel (With Easy Steps)
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.
Then, a dialog box will appear.
- Thirdly, select Existing Worksheet and select cell B2 in our “Pivot” Sheet as the output location.
After that, we’ll see the PivotTable Fields dialog box.
- 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.
Read More: How to Make Monthly Report in Excel
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.
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.
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.
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.
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
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.
The Move Chart dialog box will appear.
- From “Object in” select “Graphs”.
- Then, press OK.
Similarly, move all the Charts to the new Sheet.
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”.
A dialog box will appear.
- After that, select the first 4 fields then click on OK.
The Slicers will look like this.
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.
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
Practice Section
We’ve added a practice dataset in the Excel file.
Download Practice Workbook
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!
