How Small Data Becomes a Big Cost

Small data is the stuff you keep in Excel or Access, or heaven forbid… a Word table.  It’s on your desktop or a shared file that a few people use.  It’s less than a million records. (A million sounds like a lot huh?  It’s not really.  There are 1.5 million items for sale on eBay right now…. in the Sporting Goods section.) Even if it was a sub- set from a larger system, now its small data.

But you love Excel right? It’s one of your most valuable tools.  All you have to do is get your data in there, and you can manipulate it, contort it, and pivot it in so many different ways it can make your head spin… and eventually, it does.

If you are spending more than a couple hours a week in a particular Excel spreadsheet, managing your data, your solution is costing too much.  For ease, let’s say your time is worth $50 an hour (This is a whole other article, but on average, it is.  This however has little to do with what you are paid.)

Two hours a week is 100 hours a year; times $50 is $5,000 a year.

Is that your only spreadsheet?  How much time do you spend managing data across projects?  Often the people I encounter that spend an incredible amount of time manipulating data in Excel are professionals in a wide range of industries; Chemists, Biologists, Engineers, and most of all… managers.

Notice not one of those professions listed was a technologist?  Imagine how many spreadsheets and how many hours are spent on them throughout your company or project. The costs can be astronomical.

If it is the responsibility of a technologist to manage some data, they realize the benefit to developing a system.  Luckily for them, they can do it themselves.  Even with the small systems that you are only spending a couple hours a week with, there are likely repetitive processes that can be coded to produce your final output with so much automation that your time spent interacting with your data is reduced to a tenth.  Yes, a tenth, and often much of that is time waiting for the code to run.

Microsoft Office doesn’t just provide us with the basic tools we need for our business: a word processor, spreadsheet and database. It provides us the ability, through writing additional code in Visual Basic for Applications, to add functionality a real programmer can give us.  It doesn’t have to be a full software suite; it just has to save us time.  We can replace our data entry time with a custom coded process, or our calculations, or the manipulation of cells to make it readable.  Sometimes just re-organizing the data can be a huge timesaver.  But the decision to bring in an expert to simplify our process is a difficult one.  It’s going to be expensive right?

With these variables:

  • Length of the project in Years = L
  • Current Time spent with System in hours a week = CTs
  • Final Time spent (goal) in hours per week = FTs
  • IT analyst Rate in dollars = ITR
  • Current Administrator Rate in dollars = CAR

The budget for Development cost (Dc) should be: 

Dc = ((((CTs*52)*L)*CAR-(((FTs*52)*L))*.70)*CAR)/ITR  (Always plan for a minimum 30% benefit.)

Added to that should be an investigation time (it) to determine if the budget can be reached.

it = Dc*0.05 (Don’t spend more than 5% of a total budget to determine if it can be done.)

These quick formulas help decide if it’s worth improving a system of managing small data.  It so often is, but a blasé attitude to ‘only a couple hours a week’ leads to significant waste.

For something that takes 2 hours a week on a two year long project, it’s worth sitting down with an Analyst and Microsoft Office expert in VBA coding to see if they can improve it.

In fact, with equal rates, it’s worth giving them 6.3 hours to determine if they can improve your process to a tenth of your time in 126 hours.

Or give them 3.5 hours to see if they can improve it to just an hour a week in 70 hours.

You might be surprised what a pro can do to your spreadsheet in a week.

(Yes the clock pictured works in Excel.  Click here to go to the site to download it.)

Comments

John Quinlin's picture

Love the linked Excel Sheet. Thats pretty amazing.
I’m going to see if I can give your formulas a test run with some of my data management.

Add new comment