EXCEL spreadsheets
A rare job interview
My old computer has what is now an old version of MS Office, which I mainly used just for word processing. I didn't have any use for the other software but I wanted it anyway, knowing that it might come in handy one day. I also have some self-teach books so that I could learn whatever I needed to in a hurry if need be. Judgement day arrived, somewhat belatedly, in July 2009 when Social Care Recruitment invited me to a job interview, although the job itself is just for one day per week. The interview included a 30 minute test on EXCEL.
A hasty preparation
I spent some time over the preceding weekend playing around with EXCEL, aided by the books and the in-built help options. The books covered only the basics, but the help pages showed me that EXCEL has a vast array of options, most of which are for a variety of specialist users. I mainly stuck to what was in the books, but I picked on a few of the more advanced features to make my sessions more interesting. I figured that a 30-minute test wouldn't delve too deeply anyway and my primary goal was to do enough in the test to get by. I made clear to Social Care Recruitment, in a telephone conversation before I was offered the interview, that I hadn't used EXCEL extensively, but I had used it a little and, being an ex-computer programmer, it's the sort of thing that I learn fast. So I turned up for the interview wondering if I would get through the EXCEL test, but it was more important to attend the interview than what the result was. Just seeing what the test involved might be useful, and so it proved.
The test in detail
The EXCEL test simulated their payroll spreadsheets. Two of the real ones are fed into their SAGE accounting system, while the third, containing invoices, is printed. I was supplied with some timesheet data (worker name, client code, care type, start date/times and finish times), which I had to enter into their test spreadsheet. I had to calculate the nightshift and dayshift hours as I entered it, because there are higher pay and charge rates for nightshift. The nightshift was 8pm to 8am. If somebody started at 5pm and finished at 1am the next day, the first three hours were dayshift and the last five were nightshift. Workers don't always start and end on the hour so whoever does the job needs to be good at maths or needs a calculator that works with hours and minutes.
I was also given a separate sheet of paper showing the client code / care type combinations, with the pay and charge rates for day and night shifts. As I entered the data on the main spreadsheet, I had to consult this other list to enter the relevant pay rate. (Of course, whoever does this job eventually remembers the most commonly used values and doesn't need to consult the list very often.) The second spreadsheet is a summary version of the main spreadsheet, to which I had to enter the data. Finally, there is the invoice, which involves entering the same data as the summary spreadsheet except that pay rates are replaced by the equivalent charge rates, so it's back to that separate list showing the different rates.
Judged strictly, I failed the test because I ran out of time, but I impressed Social Care Recruitment by the speed with which I had assimilated EXCEL and I was given a few more minutes to finish the test to familiarise myself with the invoice, which I hadn't started within the 30 minutes.
What might have been
If I had been offered the job, I would have stayed on benefits, but they would have been reduced. I doubt that I would have been any better off financially and might well have been worse off, but I would have been pleased to start work again. The job would have given me experience that I cannot gain at home, in a library or on a training course. I feel that I did well at the interview, but despite the impression that I'd made, Social Care Recruitment decided not appoint me. Their e-mail response indicated that other candidates more closely matched the job requirements but I don't believe that was the reason. I think Social Care Recruitment feared losing me if I was offered a full-time job on the basis of the experience gained. I don't think there would be much chance of that, since the experience gained would be limited and my best chance would lie in finding other part-time jobs to complement the one at Social Care Recruitment.
I'd been told that there were 80 or 90 applicants (this is low compared to the 350 that I think is average these days), so just making the interview shortlist was an achievement in itself, but I believe I came very close to securing the job. Perhaps I should derive confidence from this, but I don't think it improves my prospects of a full-time job. As far as part-time work is concerned, it shows what is possible, but failure to secure this job means that I still have the problem that if I apply for part-time work, an employer will worry about losing me to a full-time employer. Had I secured the job, I could have applied for other part-time jobs with greater confidence. A second part-time job would have taken me over 16 hours per week, so I would be transferred to the tax credits system.
Limitations interest me
None of the tasks in the 30-minute EXCEL test were difficult, my initial slowness being caused by my surprise that it was necessary to do so much manually. Silly me - I didn't expect to have to do calculations just to get EXCEL, which is primarily a calculation program, to do its job. I remember back to my days as a computer programmer when the computer was expected to do everything with minimal data entry. Now, I know that bespoke computer systems are expensive and a small recruitment agency couldn't justify the cost, but let's look at how such a system would work then see what might be possible with EXCEL.
A bespoke system
On a bespoke (i.e. tailor-made) system, the data entry clerk would be given the timesheets and would simply type in the data (worker name, client code, care type, start date/times and finish times) as required. The computer would do everything else, working out how many of the hours are dayshift and how many are nightshift, getting the pay rates and charge rates from an internally held table, doing all the calculations required and formatting all the information for printing the invoice and for feeding into SAGE. (In the old days, bespoke systems did all the accounting performed by SAGE too.) The bespoke system would have a facility, perhaps available only to the manager, to allow the table of pay and charge rates to be updated as necessary, which might be once a year.
So while the bespoke system would be expensive to set up, savings would be made on data entry costs, as it would only be necessary to type in the timesheet data without worrying about all the other stuff. Apart from cost savings, human errors would be reduced, which is a major consideration where payments are involved. Hence major employers may sometimes use bespoke systems, if their data volumes justify the extra start-up cost, where small employers prefer EXCEL. Major employers sometimes use EXCEL too, but are likely to provide support for it from their IT departments.
A missed opportunity
Actually, I think Social Care Recruitment missed out on a potential opportunity, but I missed out on a potential opportunity at the interview. Partly because I didn't want to raise expectations that I might not be able to fulfil, I omitted to suggest that once I got into the swing of things, I would try to find a more efficient way of setting up the spreadsheet, to reduce the data entry requirement. Note that I would have done such research in my own time. I made clear to Social Care Recruitment that if I needed to practise EXCEL, I would do it in my own time, at home or in the library. By implication, that would have included any research I did into improving the spreadsheet.
Because my knowledge of EXCEL is limited, I am not sure how much could be done to this end without using other software. EXCEL will operate in conjunction with a lot of other software (quite apart from SAGE) and it would certainly be possible to reduce the data entry task to just typing in the timesheet data if the right software were available. However, I doubt that it would be possible to do so using EXCEL alone, but some possibilities occurred to me as I reflected on the interview, before I knew the result.
I thought that it might be possible to
- get EXCEL to calculate the dayshift and nightshift hours. Note that entering the data separately for each shift is unacceptable, but in any case it wouldn't save on data entry even though EXCEL could then calculate the hours and minutes from the start and end times.
- set up a table of pay and charge rates within the spreadsheet and get EXCEL to put the correct one in the allotted box in the main data.
- get EXCEL to copy at least some of the data entered on the main spreadsheet to the summary spreadsheet and invoice.
On the basis of a cursory look at my old version of EXCEL, I don't think that splitting the hours is possible within EXCEL unless newer versions offer a way of doing so, which is a pity since that's the most desirable of the three, though I think there is at least a chance with the other two. It is tempting to suggest that the limitations of EXCEL prove that things were better in the old days, but I assume that a programming language such as Visual Basic would be able to interface with EXCEL to produce the desired results. That wouldn't be any use to Social Care Recruitment unless they have or are willing to buy the software, but it's the kind of problem that appeals to me.
Future possibilities
Once I'm pensioned off, I'll be able to afford state-of-the-art versions of all the relevant software (a top-of-the-range version of MS Office plus Visual Studio for programming languages; I doubt that I will need SAGE). Not being distracted by the government's requirements regarding looking for a job, I'll be able to see how they all work together. Eventually, I hope to set up a system that allows me to enter timesheet data as described on this page, with the software doing all the required calculations, fetching the pay and charge rates and formatting the output. I know that I could do this quite easily using Visual Basic or another programming language without EXCEL. The trick is to find a solution using EXCEL. No, I don't want anybody to tell me how to do it. The fun for me lies in finding the solution by my own efforts. In the course of finding the solution to this problem, I'll learn a lot of other things along the way, which will in turn give me ideas for other challenges to set myself.
Really, I would have liked to spend my years since my last redundancy solving this and other computer application problems, and if I'd been able to do that, I might have found paid work. Unfortunately, government policies dictated otherwise. It's not that they actively prevented me from doing what I wanted, but rather that the adversarial atmosphere they created, particularly with threats and intimidation, was not conducive to such efforts. The software is expensive, so buying it requires (among other things) confidence that my income, limited as it may be, is not under any threat. Also, being forced to spend time on New Deal and other useless activities provides a further deterrent. Why save up over a period of many months to buy expensive software that I can't use as and when I want to?
EXCEL is a powerful tool but, like a lot of other software, it requires a lot of knowledge and skill to get the most out of it. Programmers and ex-programmers such as myself will always, in the end, get more out of it than anybody else if they put in the effort, which is why I think that major employers are likely to provide support for it from their IT departments.
Having seen how Social Care Recruitment use EXCEL and being able to see the limitations of their system compared to a bespoke system, I can play around with EXCEL as and when it suits me to see if I can find solutions to the problems I've identified. This may help me with future job applications, though it will be purely for my own purposes. I won't bother passing on my ideas to Social Care Recruitment as they chose not to employ me. I don't expect to give up looking for jobs after I'm pensioned off either. It's just that I won't be under any pressure from the government so I'll be able to pick and choose which jobs interest me.
No comments:
Post a Comment