Okay so I need to create a spreadsheet for pipeline management for three different regions of sales people.
The spreadsheet’s primary purpose is to show individual productivity so anyone can easily see who needs additional help.
I have the individual reps, their scattered appointments, locations, what they did and what the outcome was.
I have to organize it in a way where I can show each rep’s monthly activity easily.
Can someone help me figure this out?
Re: Excel is driving me crazyyyyyyyyyyyyyyy
Are you going to use it to track people's progress over time, i.e. do you need to keep last month's data?
If not, you could just make columns for each attribute (A1 would be "Name", B1 would be "Region" etc) and add filters for the "Outcome" column. You simply sort by whichever "Outcome" label corresponds to someone needing more help.
Re: Excel is driving me crazyyyyyyyyyyyyyyy
Yes, Ghostie...I need to track progress over time. Weeding out people who are not really doing much but aren't getting noticed for it either. My issue right now is...each rep has multiple appointments during the month...so A1 would be the Rep B1 would be Region and so on. Now here's the tricky part: the nature of the appointments needs to be separated as well. If it was just the former few things...it wasn't a big deal. But I have to show each rep is meeting their minimum metrics by their name appearing in three different categories...three different types of marketing they should be doing.
Re: Excel is driving me crazyyyyyyyyyyyyyyy
Way out of my league. But can performance be measured by the amount of business each person has generated in
1) last month
2) last 3 months
3) 6 months
4) last 12 months
5) last 2 years
6) last 3 years.
This allows short term and long term performance comparison. So if someone is having lean patch but has done well long term it would be obvious.
What Robert Schiller would call p/e based on last 10 years earnings adjusted for inflation.
Re: Excel is driving me crazyyyyyyyyyyyyyyy
On the outset, it sounds like there's a lot of repetitive data for each employee record so a spreadsheet may not be the optimal means of doing your analysis. Some sort of a database application may be better. That being said, if a database solution isn't possible, you can probably use Excel - it just won't be as efficient though.
Can you provide a concrete example of say a couple of employees and the types of data fields pertaining to their performance? Hopefully we can give you a good-enough solution based on your requirements and limitations. I dabble with analytics and dashboards, so I can take a stab at this myself.
Re: Excel is driving me crazyyyyyyyyyyyyyyy
Yes, Ghostie...I need to track progress over time. Weeding out people who are not really doing much but aren't getting noticed for it either. My issue right now is...each rep has multiple appointments during the month...so A1 would be the Rep B1 would be Region and so on. Now here's the tricky part: the nature of the appointments needs to be separated as well. If it was just the former few things...it wasn't a big deal. But I have to show each rep is meeting their minimum metrics by their name appearing in three different categories...three different types of marketing they should be doing.
Like Lucid said, a database would probably be best. A sample spreadsheet would help to see what you need exactly. Do you have Microsoft Access?
Otherwise you could have a separate spreadsheet for each person that tabulates their data, and then a master spreadsheet that pulls relevant data from the individual spreadsheets. This would require the use of VLOOKUP or similar functions.
Re: Excel is driving me crazyyyyyyyyyyyyyyy
Okay so there are three different regions of employees. Each needs their own spreadsheet that will show the following categories:
Rep Name
Date
Name of the event they attended (fundraising dinners, etc)
City and state of the event
Approximate attendance
Nature of the visit (seminar, sponsorship, presentation, etc)
Approximate # of handouts (I don't think this is necessary but they want it in there so I have to have it)
Status (this will be where the rep tells us what came out of this marketing attempt)
Right now, I have an extreeeeemely simple table...but its exactly as LC said...extremely repetitive with not much coming out of it.
Re: Excel is driving me crazyyyyyyyyyyyyyyy
Put yourself in the shoes of a sales manager and think what u want to see and then achieve from this spread sheet . Talk to a SM in your org. He b the judge. Buy a sales management book from Amazon for 99 cents and read on what to track .