If you are looking for some easy templates or procedures to make a daily production report in Excel, you’ve come to the right place. In Microsoft Excel, there are many ways to make daily production reports depending on the demand of the industry or product. In this article, we’ll discuss every step of this method to make a Daily Production Report in Excel.
What Is a Daily Production Report?
A manufacturer or the manager of a factory or production unit has to make a record of daily inputs and outputs and the targets. Inputs can be resources, equipment, and services, and outputs of the production are the completed products. So, it is very necessary to make a daily report on production. It will provide detailed information about what products have been and have not been done and why.
Essential Parts of a Daily Production Report
A daily production report must contain some elements. These are shown below:
- The list of products, order ID, number of orders, and delivery or a completion date of each step.
- Cumulative progress of each step and percentage of completion of total orders.
- And there can be other things depending on the process of the production.
A daily production report should be a complete overview of that day’s production and it will also show how much closer it is, to the completion of the target demand.
How to Make a Daily Production Report in Excel
In the following section, we will provide extensive details on every step of making a daily sales report in Excel.
Steps to Make a Daily Production Report in Excel
Suppose you are a production manager in the garments manufacturing industry. You have an order to produce certain products of a certain amount. You have some key points to cover. These are:
- You have a fixed order number, product name, color, order quantity, and the last date of Shipment.
- Generally, you have to produce 1% more product than the order quantity.
- You have fours steps in the production:
- So, in the daily production report, you have to show the progress. For this, you will add the total number and percentage of completion in each step.
- Also, you will need to show the remaining shipment days, so you can prioritize the products which have less time remaining.
So, these are the prime targets of the daily production report. And to make this, I am showing the steps with proper illustrations.
Step 1: Create a Blank Report First
- First, make a data table by using the steps and procedures of the production.
Step 2: Convert to Table
- Now, convert the cells into an Excel Table. Select all the cells needed and go to the top ribbon.
- Then, click on the Insert tab > Table option. There will open a window.
- After that mark the box saying “My table has headers”. And Press OK.
- Now, you will see that there will be a sorting option under each header.
Step 3: Input Product Data
- Then, input that data of product description, order quantity, and last date of shipment. These data are fixed for the full production period.
Step 4: Make a New Worksheet
- Now, copy and paste the worksheet. And name the first one as “MainPage” and rename the second one as per the date.
Step 5: Insert Formulas for Total Values
Here, in the table these formulas will be used:
- Total Cutting = Today Cutting + Previous Day Cutting
- Total Stitching = Today Stitching + Previous Day Stitching
- Total Packing = Today Packing + Previous Day Packing
- Total Shipment = Today Shipment + Previous Day Shipment
- So, on the page named 05-05-22, we have to use this formula for Total Cutting Column
=[@[Today Cutting]]+Table1[@[Total Cutting]]
- To write this formula manually, write ‘ = ’ and press on the cell F5 and write ‘+’ then go to Main Page worksheet and select G5 cell. Then you will see the same command written above.
- This command will simply sum the Column Today Cutting from this worksheet and Total Cutting Column from the Main Page worksheet.
Similarly input these commands in the Total Stitching, Total Packing, and Total Shipment column consecutively.
=[@[Today Stitching]]+Table1[@[Total stitching]]
=[@[Today Packing]]+Table1[@[Total Packing]]
=[@[Today Shipment]]+Table1[@[Total Shipment]]
Step 6: Calculate Cutting Percentage
Now, to calculate the Cutting Percentage, the following formula will be used:
- To execute the formula, go to the first cell of the column in the table and write this formula.
=[@[Total Cutting]]/[@[Plan QTY]]
- Or, you can simply select cells to do division. Write ‘ = ‘ and select cell E5 and write ‘ / ‘ and select cell G5.
Step 7: Calculate the Balance Columns
- Stitching Balance: It is the difference between Total Cutting and Total Stitching. To execute this formula, press on the first cell of the column, and write this formula:
=[@[Total Cutting]]-[@[Total stitching]]
- Packing Balance: It is the value that interprets the remaining amount of unpacked products. It will be equal to Total Stitching – Total packing. To execute this formula, press on the first cell of the column, and write this formula:
=[@[Total stitching]]-[@[Total Packing]]
Step 8: Add Remaining Shipment Days Column
- You can add another column to calculate the remaining shipment days.
- Then, after making the column, select the cells and go to the top ribbon and select the General Format.
- Now, use this formula to calculate the remaining shipment days:
=[@[Last Shipment Date]]-$D$3
- You can manually write this formula, Write “ = Q5 – $D$3 ” and press enter.
Step 9: Save the Workbook
- Now, your daily production sheet is ready. You have to input the daily data in the blank columns as per the headings. After inputting the data in columns, the table will be as shown:
- Now, save the file to the destined location and give the name mentioning the date. To save, go to File > Save As and select the location then press Save.
Step 10: Open and Rename the Worksheet for the Next Day
- But, you have to make a production report daily. So on the next day, you will use this file to make a new one. Now open this file again.
- Rename the Worksheet as per the date. To rename, do right-click on the worksheet name and select Rename.
Step 11: Save the Workbook for the Next Day and Copy the Total Columns
- Now, input the day’s date in the date cell. And save the file as a new name mentioning the new date.
- Now, Copy the cells of Columns named Total Cutting, Total stitching, Total Packing, and Total Shipment.
Step 12: Paste the Total Columns to the Main Page
- Then, go to the MainPage worksheet. And paste the cells there as per column respectively by using Ctrl + V.
Step 13: Delete the Cells of Today’s Columns
- Then, Come to today’s worksheet named 06-05-22. And delete the cells in the columns named Today Cutting, Today stitching, Today Packing, and Today Shipment.
Final Step: Input New Data
- Then, input today’s data in those deleted cells. Thus, today’s production report is made.
- Repeat these steps to make the report for the next day.
Use Conditional Formatting to Highlight Remaining Shipment Days
You use Excel to make your daily work easy. And daily production report is also to ease your work. If you use Conditional Formatting in the Remaining Shipment Column then it will clearly show you the nearest deadline for a product shipment.
- To do this, select the cells of the column and go to the top ribbon, and press on the Conditional Formatting option.
- There will open a drop-down menu then select Color Scales.
- There will see some options. Select the first one.
You will see here that the lowest values are in red color and medium values are in orange color and the large ones are in green color. It will help you to identify the nearest last shipment day of any product.
Things to Remember
- After clicking on the top right corner where the cell name shows, you will see a drop-down menu. Here Table 1 is mentioning the table on the Main Page and Table 15 is mentioning the table on the second page. You may learn about Excel tables from here.
- You will use this file daily. Just save it daily as a new file by renaming it as per the date and use it again and follow from step-11 till last.
- You can customize this file as per your demand, this article and workbook will help to have an idea about the daily production reports, and use this template and file to make your own.
Download Free Template
You can download the Excel free template for a daily production report from the following button.
That’s all for today’s article. I strongly believe that you may be able to make a daily production report in Excel after reading this article yourself. If you have any queries or recommendations, please share them in the comments section below. Keep learning new methods and keep growing!