Use Pivot Table and Become Smart in Report Making (Download Excel Template)

Report making is one of the daily activities for most of the garment manufacturers in the garment industry. Most of them use a spreadsheet (MS Excel or Open Office) for making a daily production report and other reports.

I assume that you are one of them. You may know how to use a spreadsheet. The question is how are you utilizing excel tools to make your reports?

Take an example of making Daily production report (DPR). You make it daily, right? How do you do it? I hope you follow this step,

Existing Method of Report Making

  • You have DPR template with set formula 
  • You copy last day’s sheet and make new sheet. 
  • You delete previous day’s production quantity and add today’s production
  • Add new rows in the template if need to add more/new style in the report
  • You drag the formula to new rows added in the new sheet
  • You calculate total production by adding today’s production
  • You cross-check and tally production quantity with previous reports and actual report twice.

(If you already made 31 sheets date wise in a file you also need to similar activities)

Finally, you get the DPR but this is only one report with lot of rows and column. You spend lot of time and energy but the final outcome is a clumsy report. If one takes printout of the DPR having more than four sewing lines it is really a torture to the report reader. Are you happy with such report format? (See Figure 1)

Daily production report format
Figure -1: Daily Production Report

If you are asked to make summarized report (e.g. style wise summary report, day-wise production day of a style, or monthly report) or report in a different format, it would be a tough job (time consuming job). In case management asks for report I know this job is more than a mental torture.

Other disadvantages are
  • You need enter same data in multiple sheets/multiple reports
  • You need to make separate file for each month.
  • You need make multiple reports as requested by management 
  • Limited form of reporting

How can you improve your reporting making process?

Use pivot table. All you need to enter data in one sheet and view report as you want. Make one sheet for data entry. Design your data entry sheet with maximum data field required to make all of your desired reports. Design your reports (to be viewed) using pivot table. You will get Pivot table in Inset tab (See Figure 2) in the menu bar. 
Figure -2: Inserting Pivot table 

In this article I am not going to teach you how to use pivot table. You can learn it by reading online. I will show an example that I use making production report.

Making report using pivot table is not difficult. But most of us don’t know how to use Pivot table. I have learned this last year and prepared more than 100 reports for garment manufacturers using pivot table. I have shown here an example (with hypothetical data).

In this report you will get following report (See Figure 3)
  1. Daily production report
  2. WIP Report (Running styles)
  3. Monthly Report
  4. Style Summary Report
  5. Figure-3: List of Reports
  6. Style report (Date wise break-up)

Benefits of using Pivot table.

In this report you
  • Don’t need to make multiple files for making different reports using same data.
  • Don’t need to make day wise DPR (Daily Production Report). 
  • Don’t need to copy the file and edit data and enter new data on the current sheet
  • Don’t need to check your formula daily prior to taking the printout.
You just need to enter data in one sheet (See Figure 4). And all desired reports will be generated just by refreshing data. You can make numerous reports and check data using filter as much variation as you need provided that you have entered all related data in the entry sheet that you want to view in the report. By making multiple reports you can keep your DPR clean (See Figure 5, 6, 7). Data analysis become very easy when you keep all data in one table. 
Figure-4: Data Entry Sheet
Daily Production Report: To view the daily production you need to select date only from the drop down menu. For WIP report you need select running styles.
Figure-5: Daily Production Report (DPR)
Monthly Production Report: You will get monthly report by selecting Month and Year. In the sample report I have not shorted month wise.
Figure 5: Monthly Production Report
Style Report: You can check detailed report for styles date wise past data (See Figure 6).  Report can be viewed for single style and multiple styles.
Figure 6: Style Report
Style Summary Report: You can check production status of all styles for a duration (See Figure 7).
Figure -7: Style Summary Report

P.S. You can make an advanced report using SQL query and pivot table in excel.

Prasanta Sarkar

Prasanta Sarkar is a textile engineer and a postgraduate in fashion technology from NIFT, New Delhi, India. He has authored 6 books in the field of garment manufacturing technology, garment business setup, and industrial engineering. He loves writing how-to guide articles in the fashion industry niche. He has been working in the apparel manufacturing industry since 2006. He has visited garment factories in many countries and implemented process improvement projects in numerous garment units in different continents including Asia, Europe, and South Africa. He is the founder and editor of the Online Clothing Study Blog. He lives in India. linkedin

Post a Comment

Previous Post Next Post

Advertisement

Contact Form