Thursday, 6 March 2014

Create your own Order Tracking Excel

Here is a simplified version of my order tracking application. I’
Start out with columns…
  • Order Number – you increment this … it’s there for sorting
  • Date of Order – The date you placed the order (and paid)
  • Category – An arbitrary category used for column filtering
  • Item – A short description of the item
  • Description – A more detailed description of the item
  • Cost – The total cost for the order
  • # Items – the number of pieces
  • Cost Each – a calculated value of the cost per item
  • Vendor – the name of the vendor (sorting and filtering)
  • Sent Date – the date that the vendor tells you the order was sent
  • TTS Days – Time To Send days (calculated)
  • ETA – The date that the vendor estimates for delivery
  • ETA Days – the estimated number of days to deliver (calculated)
  • Arrived Date – the date that the item actually arrived
  • ETA/ATA Difference – the difference between estimated and actual (calculated)
  • Travel Time – the time that the order was in transit (calculated)
Now to put in some calculations … This is where the value is added. I’m using Excel 2010, so these formulae are using functions that I know are there in this version … don’t blame me if your spread-sheet doesn’t have the DATEDIF function!
Cell Reference Formula Purpose
H2 =F2/G2 calculates the cost per item
K2 =IF(J2<>"",DATEDIF(B2,J2,"D"),"") Calculates the number of days to send the order
M2 =IF(L2<>"",DATEDIF(B2,L2,"D"),"") Calculates the estimated number of days to arrive
O2 =IF(N2<>"",IF(N2>L2,DATEDIF(L2,N2,"D"),DATEDIF(N2,L2,"D")),"") Calculates the difference between the Estimated and Actual time to arrive days
P2 =IF(N2 <> "", IF(N2>J2,DATEDIF(J2,N2,"D"),DATEDIF(N2,J2,"D")),"") Calculates the number of days for the order to arrive
Copy the formulae down the columns in the spread-sheet and you should be good to go.
You can turn on Filtering in the spread-sheet so that you can track individual vendors and item categories.
When you don’t enter an ordered date, the item is, effectively, a wish-list item.
Well … that’s my piece for today. Hopefully, I’ll have some free time on the weekend to carry on with my LED bedside light.

Here is a copy of the spread-sheet for personal use.

No comments:

Post a Comment

Paypal Donations

Donations to help me to keep up the lunacy are greatly appreciated, but NOT mandatory.