Pivot Table as a data collection tool

[John Lutes]

We recently sent pivot tables to our reps with three year history of several
hundred products (SKU's) and several hundred customers.

We asked that they insert a forecast in the Pivot table source data
worksheet so we could then rebuild the table to display the history and the
forecast.

The request confused most of our sales reps who are admittedly not
particularly EXCEL SAVVY. They insisted on putting there input in the
available cell NEXT to the pivot table data making capturing their forecast
data back to the source data labor intensive.

Is there a solution to this problem? Is there an MS office product more
suited for this task? What is the true meaning of life?

 

[smartin]

We recently sent pivot tables to our reps with three year history of several
hundred products (SKU's) and several hundred customers.

We asked that they insert a forecast in the Pivot table source data
worksheet so we could then rebuild the table to display the history and the
forecast.

The request confused most of our sales reps who are admittedly not
particularly EXCEL SAVVY. They insisted on putting there input in the
available cell NEXT to the pivot table data making capturing their forecast
data back to the source data labor intensive.

Is there a solution to this problem? Is there an MS office product more
suited for this task? What is the true meaning of life?

Well the first lesson, I should think, is, "sales reps are not usually
good data heads." Knowing this group is not particularly acute in that
regard, I suggest you spell out your expectations.

You might do well to give them a template sheet on which to enter their
data, so their numbers are not intermingled with yours. True, this will
separate their data from the PT source as well, but it gives some
control over what gets entered and where. If you structure the data
entry sheet in such a way that it can easily be copy/pasted into the PT
source you can minimize the headache.

 

[John Lutes]

Thanks smartin. It would seem by your overview that maybe we are using the
wrong tool for data collection. Are you aware of a tool better suited for
collecting data from our less than savvy sales reps. The data is vital, and
it needs to be collected quickly and accurately. I sure appreciate your
response.

 

[smartin]

Hi John,

I wouldn't go that far; Excel may be perfectly suited to what you are
trying to do. If your concern is the ability of your associates to enter
data correctly, a fundamental principle should be to make it as easy as
possible for them to succeed.

Excel does have tools to facilitate data entry (e.g., validation, error
checking) so I would not rule it out right away. It might be as simple
as providing a template (as I mentioned earlier) showing an example or
two of what a correct record looks like and asking your reps to follow
the form.

Another option might be to have the reps forward the relevant info and
have a competent person you trust do the parsing and data entry. As
critical as the data is, it might not be a bad idea to have such a
person look over the results if nothing else.

Yet another option might be to build a database application (e.g.,
Access), where stricter controls can be put into play, but this can
involve steeper development efforts that might be costly in terms of
money and time, and may not have a suitable ROI.

I do hope I did not offend you (or anyone else) with my generalization
about sales reps!