Struggling with Daily MIS Reporting for Manufacturing: How Can I Streamline My Excel Process?

Dear seniors,

Greetings of the day,

This is the third time I am posting my question to my seniors.

I am working in a leather chemicals manufacturing company in Kanpur. I want to prepare an MIS format for reporting on a daily basis to the directors. I want to report daily regarding the following:

- Raw material inward (there are 300 items)
- Raw material issued to a particular production division (as per the production chart, there are 4-5 different divisions)
- Raw material received back from the production division
- Total raw material issued to the production unit
- Total finished goods received in the checking/packing department from the manufacturing unit
- Total finished goods packed
- Total finished goods dispatched to distributors

I want everything on one Excel sheet where there should be automatic calculation of raw material stock per item, where I will insert opening stock plus inward less issue, then finished goods received from the production unit (model-wise/color-wise) less total dispatched finished goods.

Expecting help from all of you.

Thanks and regards,
Priya

Dear Priya,

Do you have the ERP system installed in your organization? If it is installed, you may ask your service provider to set up an email trigger within the ERP for the following request, which will automatically send emails to everyone you wish to notify.

If it is not installed, please install one and proceed with the setup. This is necessary as the first step to collect data from all departments and prepare the MIS. Even with a small time investment in your factory, this can be achieved.

The steps involved are:

1. Firstly, prepare an overall consolidated sheet that you wish to use as a front page to present to your superiors.
2. In the item list of the consolidated sheet, determine the number of items you need to display and create that many pages in an Excel workbook.
3. Rename the worksheets according to the items listed in the consolidated sheet.
4. Create the format and formulas in each sheet.
5. Share the document with the relevant departments and request them to input the data.
6. Implement the formula on the consolidated sheet with hyperlinks to the other worksheets in the document.
7. Generate the report daily and send emails to the superiors.

I hope this enlightens you on the complexity of implementing this with just a single Excel sheet. Try applying this method in your organization and maintain it. Others may follow a different approach that may not align with your organization's needs.

Try it yourself, and if you need assistance in preparing the sheets, please forward your Excel sheets to [Login to view], and I will provide suggestions.