How to Make Daily Production Report in Excel (Download Free Template)

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.


Download Free Template

You can download the Excel free template for a daily production report from the following button.


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:
    1. Cutting
    2. Stitching
    3. Packing
    4. Shipment
  • 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.

How to Make Daily Production Report in Excel

Read More: How to Make Production Report in Excel (2 Common Variants)


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. 

How to Make Daily Production Report in Excel

  • Now, you will see that there will be a sorting option under each header.

Read More: How to Automate Excel Reports Using Macros (3 Easy Ways)


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.

How to Make Daily Production Report in Excel


Similar Readings


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.

How to Make Daily Production Report in Excel

Read More: How to Generate Reports Using Macros in Excel (with Easy Steps)


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]]

How to Make Daily Production Report in Excel

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

Total Stitching:

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

Total Packing:

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

Total Shipment:

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

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


Step 6: Calculate Cutting Percentage

Now, to calculate the Cutting Percentage, the following formula will be used:

Cutting Percentage = Total Cutting / Plan QTY
  • 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. 
=E5/G5

How to Make Daily Production Report in Excel

Read More: How to Create a Summary Report in Excel (2 Easy Methods)


Similar Readings


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]]

Or,

=G5-J5

How to Make Daily Production Report in Excel

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

Or,

=J5-M5

How to Make Daily Production Report in Excel


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:
Remaining Shipment = Shipment date – Today
=[@[Last Shipment Date]]-$D$3
  • You can manually write this formula, Write “ = Q5 – $D$3 ” and press enter. 

How to Make Daily Production Report in Excel


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:

How to Make Daily Production Report in Excel

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

Steps to Make a Daily Production Report in Excel


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.

Open and Rename the Worksheet for the Next Day


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. 

Save The workbook for the Next day and Copy the Total Columns


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.

Paste the Total Columns to the Main Page


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. 

Delete The Cells of Today's Columns


Final Step: Input New Data

  • Then, input today’s data in those deleted cells. Thus, today’s production report is made.

Production of this Date 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.

Use Conditional Formatting to Highlight Remain Shipment days

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


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 MainPage and Table 15 is mentioning the table on the second page. You may learn about Excel tables from here.

Things to remember

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

Conclusion

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. Don’t forget to check our website ExcelDemy for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

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

Leave a reply

ExcelDemy
Logo