Give me some data and I will try my best to analyse it.
I was given, OK! I FOUND someone’s piece work records for a part of a particular month and asked them if I could create a spreadsheet for it … thankfully they said yes and here we are!
I won’t provide any details except to say that my friend is providing a consumer service in an outlet in the centre of a capital city. The basic data she recorded was:
- number of customers served per day
- total tips received from all customers that day, received in cash and kept by the employee
- total earnings that day, as to be paid at the month end as salary
I set to work and the following table contains the following basic and additional columns:
- Total Pay
- Cumulative Tips
- Cumulative Earnings
- Cumulative Total Pay
- Tips per Customer
- Earnings per Customer
At the bottom of the Cumulative total Pay column I have programmed an annualised taxation warning that shows the employee that at the current rate of earnings they will be liable for income tax during this tax year. This is not a PAYE country!
You can see that the month has not finished yet!
I then prepared a series of graphs from this table: forgive the inconsistent way in which I have presented the six graphs … microscope time:
- graph one shows the days worked and days off … the days off are shows as gaps in the curve
- graph two shows daily earnings and cumulative earnings
- graph three shows daily tips received and cumulative tips received
- graph four shows earnings v tips … the potential relationship between them: being able to predict tips from earnings might be an ambition!
- graph five shows earnings per day and average earnings per customer
- graph six shows tips per day and average tips per customer
For this individual employee this analysis is neither here nor there. In the grand scheme of things it is not that important. If you take a bigger view, however, and consider this information for every one of the 20 employees in this organisation, their tips v earnings, their taxation positions and so on, there is a whole mine full of data just waiting to be analysed!
Take a look at these, for example and ask yourself,
- why is the earnings per customer not a constant 100?
- why has this employee taken more days off than the one day per week?
- can you see a pattern emerging that would help this employee to predict tips from earnings?
I am sure you can find other questions to ask, apart from why did I bother?!
Finally, this worksheet is now a template and I hope to get the chance to keep it updated for at least six months, maybe more.