“How much money did we make last week?”

Such a simple question, and yet it’s surprisingly difficult to answer if you are a contract staffing firm. This is because you earn your money over time, and often months later than when a consultant is placed.

If you want to run your business quantitatively, in addition to the question above you need to know things like:

  • How concentrated is our contract revenue flow? If we lose this client, will we be ok?
  • Is our total contract revenue declining or increasing?
  • Is our revenue from this client declining or increasing?

All of these questions require a special type of data called a time-enumerated dataset; read on for how to make one for your business.

Step 1: Clean the Data

As with our previous article (How-to: Sales Forecasting) it’s important to start with clean data. Otherwise all of your hard work on Steps 2 and 3 will suffer from garbage in garbage out syndrome.

The most common issues that we see in contract revenue data are:

  • Contract duration. Unknown end date and possible early termination can play havoc with an initial duration estimate.
  • Footing of ATS, timecard software, and financial software. Each data source only has part of the story. Getting them to talk to each other — much less agree — can be a project just by itself.
  • Other influences: varying burden rates, using top-line vs gross margin, etc.

Once you’ve got the data prepared, on to the fun part.

Step 2: Create An Enumerated Dataset

After Step 1, each deal should look something like this:

Placement Client Revenue Start Date End Date
Software Engineer XYZ Corp $3,600 1/2/2011 1/22/2011
Senior Sales Manager Acme Corp $10,200 1/16/2011 2/26/2011

Here’s the problem. Each of those placements is a single entry. You need to transform the data into per-week (or per-day) in order to show it over time.

For example, you could chart the $3,600 as a lump sum on the start date. However that doesn’t help you figure out how much cash you had coming in the last week of January. You need to spread that $3,600 out like peanut butter over the duration of the placement.

In theory this is quite simple; in practice it can be quite frustrating. You need to create an expanded view for the full year that looks like this:

Placement Client Week 1/2/2011 Week 1/9/2011 Week 1/16/2011 Week 1/23/2011 Week 1/30/2011
Software Engineer XYZ Corp $1,200 $1,200 $1,200
Senior Sales Manager Acme Corp $1,700 $1,700 $1,700

Warning: this is possible but quite difficult via Excel. Excel is good at aggregating (e.g. with PivotTables) but not good at expanding data into enumerated tables like this. I wouldn’t recommend it unless you are good at programming.

Still with me? Unfortunately, this kind of manipulation has a lot of “gotchas” which can make it complex:

  • When breaking each placement over time (“spreading the peanut butter”) you probably need to break it by day not week. Otherwise you’ll have issues with contracts that start or end mid-week.
  • As time goes on you’re adding more and more columns for each day or week. That means the table is expanding horizontally as well as vertically. Most databases (Excel included) don’t like it when you expand column-wise over time.
  • While not a large dataset by modern standards, if you do five hundred placements in a year and you’re expanded out daily you are manipulating 500 * 365 = 183k separate data points. You’ll need to aggregate that before charting it.

Step 3: Aggregate and…Update

All of that gets you a chart where each deal has its own series over time. Sounds good right?

Contract Rev by Opportunity

Not Useful! — Unaggregated Form

Contract Rev, Aggregated by Client

Much Better — Aggregated By Client

Not so fast. That’s a lot of series, perhaps hundreds or thousands. Your chart will have more striations than rings in a redwood tree.

You need to aggregate the dataset before you can chart it. I’d recommend aggregating by client but keeping the placement-level data so that you can still drill down if you want to. Excel’s PivotTable function is fine for this aggregation step.

Now here’s another hard part of an over-time chart: updating it over time! If your chart is a weekly view you’ll benefit most from updating it each week. Make sure you set up the data sourcing in Step 1 and the conversion in Step 2 such that you can output easily each week.

When you’re done, you will have powerful tool which:

  • Shows your top-line revenue over time, so you can see if you are trending up or down
  • Shows concentration by client or salesperson, so you can see what is driving your growth
  • Allows you to drill from client down to placement, so you can take action to increase revenue


Recommended Posts