Monday, 7 November 2011

Equipment Usage Database

Over the years I have seen a few different methods that golf clubs use to keep track of their equipment usage.  The first and most basic of these is writing it down on a piece of paper after each use.  The more advanced of these systems automatically check the hour meter reading each time the machine enters the shop area and automatically enters this info into your equipment management software.  While both of these systems do work they both have drawbacks.

The first pen and paper system is very labor-intensive.  It requires that after each use the operator must fill out the sheet.  Later on the Mechanic or designated individual needs to interpret this data to make sense of it.  This works good but takes a lot of time.

The automatic system is great but the cost of acquiring such a system is astronomical.  This system also requires that each machine has a hour meter that can communicate wirelessly with your computer.  Another drawback is that this system takes a great deal of time to set up and get running smoothly.

I have created a simply data input and filter using Google Docs that makes it easy to input info from any computer or device with internet access.  It inputs that data into a spreadsheet where you can filter the data for different operators, equipment, areas used as well as date.  The database can keep track of equipment hours, areas cut, circle/clean up cut directions, fuel consumption as well as amount of clippings harvested and whether or not the equipment needs servicing.

This info is then pulled from my equipment maintenance database and notifies me when I need to do scheduled maintenance.  I'll talk about this part of the system in another post.

The best part about this system is it's free and you can customize it however you want quite easily.

The backbone of this system is the spreadsheet which can be found below or here.

Use this template and change the name to whatever you want.

Here is the list of equipment at my course
The first step to using this database it to edit the form.  On the top of the screen select "form" then "edit form".  A window will pop up that will allow you to edit the data that is able to be input into the database. This form ensures that all of the data input into the system is formatted the same and eliminates issues with spelling and the like.

The first field has the equipment operator names.  Add all the names of the people on your crew here starting with the operators that use equipment the most.  As employees come and go you can go in here and add or remove them.


Next is the equipment name field.  Add all the designations for all your equipment that you use on the golf course here.  If you have multiple units of the same type you will need to assign each a different number.

The next is what area of the course the equipment was used on.  Again you can edit these to better suit the terminology that is used on your course.  I can use this information so sort out the total machine time per area at the end of the year.

The next input is the holes which the equipment was used.  I only have 9 holes on my course so I only have 9 options plus my practice areas.  I use this information mainly to let my employees know what was last done with each mower so they know what needs to be done next.  I embed this spreadsheet on my maintenance website so that everyone can see it.  The sheet titled "last use" is a collection of the last use of each machine.  As I only have 1 machine per area of the course this is easy.  You may need to sort this sheet differently.

The next data is the circle cut direction.  Some people call this the clean up cut.  Call it whatever you like but I use this to inform my employees of the direction the last circle cut was done in so that they can alternate the directions each time they cut.

Fuel consumption is here as well in case you want to monitor this.

I use the basket empties field to monitor the growth of the turf on my putting greens.  Read all about this here.

The next field is used to inform the mechanic if the machine needs maintenance.  For the purposes of the basic equipment log this is of no use.  If you want to tie this into a equipment maintenance database later on then keep this here.

The other two fields are for the mechanic to read and are optional.

Now that you have the form filled out and ready save it.  You are now ready to embed this form onto the web.

I created a maintenance website that I will briefly discuss here.  I will throw up a template later if I have the time.

Equipment use section of my Maintenance website
I have created a Google site that is only accessible to my employees. The following picture shows how I have set up the equipment use section of the site.  I have put the "last use" sheet up top so that everyone knows what was most recently done.  Followed is the form that we just made.  Under the "form" selection up top select the "embed form in a website" option.  Copy that link and paste it into your site in html format.  This is easily done on Google sites. (note: to make the form easily view-able on a mobile device edit the width for 100% and add about 50 pixels to the height)

Now whenever your crew comes in after completing a task on the course they just go to the shop computer and enter the info into the form. They can even access the form on their smartphone if you allow them access to you site.

Now that you are collecting data you can go to the "filterdata" page to filter the information however you like.  Under each section option at the top you can select the different data and it will filter it accordingly.  The dates can be changed to only show data for a specific period in time.  I have it set up to default from the beginning of 2012 to tomorrow's date.

The "uniquedata" sheet is there simply to formulate the drop down menus on the "filterdata" sheet.

The "greensgrowth" sheets are for monitoring the growth rates on your course.  Delete these if you don't want to do this.

The last sheet titled "3100-G" can be used as a permanent sheet for each machine.  Simply duplicate this sheet for every piece of equipment you want a permanent record for and rename it accordingly.  To get the equipment data into the sheet just change the name on the Sheet cell A2.

And that's about it.  This can be used to monitor a large number of things on your course.  The true potential of this system really comes out when you link this to an equipment maintenance database.  I have mine linked and it automatically alerts me when machines need scheduled maintenance.  A very cool feature which I will discuss in a later post.  For now see if you can set this up and let me know how it works.  I look forward to receiving feedback.