CHEP Pallets Report Using Excel PivotTables

I used Excel PivotTables and excel charts to render similar chart to CHEP PALLETS REPORT (flickerbook.co.uk) monthly pallet transfers by supplier. I did two versions of this chart.

• In first version I created 6 pivot tables to group data for 6 months
• In second version I added an additional column to “Transactions report” file that contains month and year value.

Excel file that was used to render these charts can be found here.

Version 1 chart

My first attempt to create a monthly pallet transfers by supplier is time consuming, however I still decided to complete it. These steps were taken to render chart:

• Create 6 Excel PivotTables that each contains one month worth of data (every day of a month was marked manually in the filter).
• Copy each months data and past it into a new excel sheet: this means that data is ordered by month.
• Manually add month and year value to the pasted data.
• Remove repetitive suppliers name from pasted data.
• Add zero values if a supplier haven’t delivered any pallets for a specific month.

Once these steps were completed I had data organised in this manner:

In the picture above orange colour indicates data that has been added manually. I have done this in order to draw a chart that is visually easier to investigate, as this way I ensured that each supplier has the same amount of columns on the chart. Using this data I was able to render this chart:

Using this method I managed to create an almost identical chart to CHEP PALLETS REPORT (flickerbook.co.uk) monthly pallet transfers by supplier chart, however this method requires me to do a lot of things manually (mark dates in pivot tables filter, copy and paste data, add yyy-mm values manually next to copied data, insert 0 pallet quantities when a supplier did not transfer any pallets) which leaves a lot of room for error. 

The finished excel file for this chart can be found here.

Version 2 Chart

In this version I created I new column named “Year Month” inside the “Transaction Report” file, copied data from “Shipment Date” column, pasted it into the new column and changed it’s date format to “mmm-yy”.

The addition of this column allowed me to group transfers by month and supplier, so using this method there is no more need to create six PivotTables as I get the desired results with just one pivot table:

Using data from this PivotTable I created this chart:

This way of creating a monthly pallet transfers by supplier chart is a lot faster and less error prone, as it doesn’t require to do a lot of work manually. The only disadvantage compared to version 1 is that it does not display months, where supplier has not delivered any pallets.

Excel file for this chart can be found here.

Conclusion

Excel pivot tables and charts can be used to search for unusual CHEP pallets transfers. Using pivot tables I was able to produce similar chart to CHEP PALLETS REPORT (flickerbook.co.uk) monthly pallet transfers by suppliers chart. However the benefits of using the CHEP PALLETS REPORT (flickerbook.co.uk) are these:

• Automation: all that needs to be done is the upload of the transaction report file.
• If supplier hasn’t delivered pallet for a specific month, it is represented in a chart.
• Chart titles display time period for which the chart is generated (it can be used to check if a full months data is being used).
• Colours are used to distinguish suppliers in the chart.

What I learned

For me the major benefit of this exercise was working with excel pivot tables and excel charts, as I feel I need more practise with excel and must come up with more ideas on how to do it. Especially it was helpful that I was working with data I used previously, since I knew what results to expect from a chart.

My blog post where I showcase more details on how I created these charts can be found here.