Essential Parts of a Daily Production Report

• 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.

How to Make a Daily Production Report in Excel

Steps to Make a Daily Production Report in Excel

We’ll create a template for a garment production facility report.

• We have a fixed order number, product name, color, order quantity, and the last date of Shipment.
• Let’s assume the company needs to produce 1% more product than the order quantity.
• You have fours steps in the production:
1. Cutting
2. Stitching
3. Packing
4. Shipment
• We have to show the progress by adding the total number and percentage of completion in each step.
• We also show the remaining shipment days to help prioritize the products which have less time remaining.

Step 1 – Create a Blank Report

• Make a data table by listing the steps and procedures of the production.

Step 2 – Convert to a Table

• Select all the cells needed and go to the top ribbon.
• Go to the Insert tab and choose the Table option. There will open a window.
• Check the box My table has headers.
• Press OK.

• You will get a sorting option under each header.

Step 3 – Input Product Data

• Use sample data or the data from your company to fill in the values you have.

Step 4 – Make a New Worksheet

• Copy and paste the worksheet. We named the first one as MainPage and renamed the second one to the current date.

Step 5 – Insert Formulas for Total Values

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
• On the page with the date for the name, use this formula for the 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 the Main Page worksheet and select the G5 cell. Then you will see the same command written above.
• This command will sum the column Today Cutting from this worksheet and Total Cutting column from the Main Page worksheet.

Input the following commands in the Total Stitching, Total Packing, and Total Shipment columns, respectively.

Total Stitching:

`=[@[Today Stitching]]+Table1[@[Total stitching]]`

Total Packing:

`=[@[Today Packing]]+Table1[@[Total Packing]]`

Total Shipment:

`=[@[Today Shipment]]+Table1[@[Total Shipment]]`

Step 6 – Calculate the Cutting Percentage

The following formula will be used:

Cutting Percentage = Total Cutting / Plan QTY
• Go to the first cell of the column in the table and write this formula.
`=[@[Total Cutting]]/[@[Plan QTY]]`
• You can also insert the following:
`=E5/G5`

Step 7 – Calculate the Balance Columns

• Stitching Balance – difference between Total Cutting and Total Stitching.
• Use one of the following formulas in the column.
`=[@[Total Cutting]]-[@[Total stitching]]`

OR

`=G5-J5`

• Packing Balance: Remaining amount of unpacked products, equal to Total Stitching – Total packing.
• Insert the following formula in the column.
`=[@[Total stitching]]-[@[Total Packing]]`

OR

`=J5-M5`

Step 8 – Add the Remaining Shipment Days Column

• Add another column to calculate the remaining shipment days.
• Select the cells in the column, go to the top ribbon, and select the General Format.
• Use this formula to calculate the remaining shipment days:
Remaining Shipment = Shipment date – Today
`=[@[Last Shipment Date]]-\$D\$3`

Step 9 – Save the Workbook

• Input the daily data in the blank columns as per the headings.

• Go to File, then to Save As, select the location, name the file and press Save.

Step 10 – Open and Rename the Worksheet for the Next Day

• On the next day, you will use this file to make a new one.
• Rename the Worksheet as per the date. Right-click on the worksheet name and select Rename.

Step 11 – Save the Workbook for the Next Day and Copy the Total Columns

• Input the day’s date in the date cell. Save the file with a new name to use the new date.
• 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

• Go to the MainPage worksheet.
• Paste the cells there as per column respectively by using Ctrl + V.

Step 13 – Delete the Cells of Today’s Columns

• Go to today’s worksheet.
• Delete the cells in the columns named Today Cutting, Today stitching, Today Packing, and Today Shipment.

Final Step – Input New Data

• Input today’s data in those deleted cells.

• Repeat these steps to make the report for the next day.

Use Conditional Formatting to Highlight Remaining Shipment Days

• Select the cells of the column and select the Conditional Formatting option in the Home tab.
• Select Color Scales.
• Select the first option on the list.

• The lowest values are in red color, which will help you to identify the closest 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.

• Save the file with a new date as the name daily and repeat Steps 11 and onward to make the changes to the new sheet.
• You can customize this file as per your demand.

Related Articles

<< Go Back to Report in ExcelLearn Excel

Get FREE Advanced Excel Exercises with Solutions!
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

1. WOW!!
This contents is sooooo helpful. Thanks Osman Goni Ridwan, now i can follow these steps to create my production report.