Neil from Labetts is a subscriber to our Excel helpdesk, or Excel consultancy service, as I like to call it, so he had the immediate attention of our helpdesk to help him with his challenge: how to streamline the order processing system in their business.
The advantage of using Excel to automate your tasks is that using Macros you really don’t have limits as to what can be done – if he wanted his CD tray on his computer to open, you can do that with a macro, so that gives you an idea of the scope you have when automating tasks using Excel.
The process started once an order form had been filled out, and then the following procedure was to be followed:
1) Save an Excel version of the Purchase Order – this meant saving an individual sheet from a Workbook that contains other sheets he doesn’t want to send to dispatch.
2) Save a PDF version of that sheet as well
3) Print a copy of that sheet
3) Create an address an email and attach the two files saved previously and send it to dispatch to process
The process was timed and took 1 minute 47 seconds. One thing we didn’t include in this timing is that the sheet saved as a new file also needed to be unprotected and formulas replaced with their values. So you had a password problem here as well. But that would have added on another 30 seconds or so – maybe more – so let’s say round off at 2 minutes 20 seconds.
When we had finished setting up the macro for him, at a click of a button he could do all of this in 8 seconds.
There is a side effect as well – they also get a reduction in errors, as a macro is a string of commands that do the same thing over and over again – doesn’t matter if it’s Monday or Friday – 6am or 6pm, a macro works the same way. A great advantage of business automation.
They process 3 or 4 orders of this type per day – so let’s say 900 per year. We saved them 2 minutes 12 seconds per order, which is a neat little saving of 33 hours per year – about 4 working days per year. I don’t know what a fair hourly cost to the company would be, but when you consider holidays, sick leave, bonus, perks, cars, food etc – it’s probably way over USD30.00 an hour – but at that rate – this one macro has saved the company nearly USD1000.00 in costs – if they’re making a 10% net profit, that’s like a USD10.000,00 sale!
Check your systems today and look for any repetitive tasks that you think could be automated. Something you may be able to do yourself – are there calculations done manually that could be done with a formula?
If you’re a subscriber use our Microsoft Office FAQ section or search through our back issues of our Microsoft Office Newsletters. You could also download our eBook on automating your quotes and get some ideas on there – finally, if you have something that will require a macro, why not invest a small amount of money today, get a subscription to our Microsoft Office consultancy services and get your Excel spreadsheets automated.
Article by Matthew Steele – LinkedIn Profile
