Sam J. (job poster) hired Jonathan T. (Tasker) to help with this task. Got a similar task you need done?
Open
Assigned
Completed

Excel lent excel guru

Posted by Sam J.

Location

Melbourne VIC, Australia

View map
Due date
Friday, 21st Aug 2015
Details
This task has three components to it and two sheets:

Background / Current status
We are looking to create a reporting tool using excel that captures data from approximately 16 people in a foolproof manner.

They will input this data to excel - Master Sheet - and save it to a network drive.
Each sales person will only be able to input their own data no other data should be visible to other team members only a master administrator. It will sit within a web environment as a spreadsheet.
Each time they add or delete rows the formulas become corrupted

The data we captured are then manually currently totalled and transcribed to a seperate spreadsheet - group sales spreadsheet.

We want this simplified, the formulas locked and the transcription from separate sales spreadsheets into a dashboard detailing the below metrics. Can you help?

Desired future status Core Tasks
1. Individual KPI spreadsheet for sales people to complete that is dummy proof has locked formula’s and once completed will automatically total to task 2 (Group sales master sheet)
Reference workbook - Master Sales KPI Template

Challenges:
1. In each sales persons master sheet when they add or remove lines our formulas move and no longer function correctly.
Add approx 20 Lines aditional lines btw 27 - 33 . When I do this it corrupts the formulas
Lock the sheet so only yellow cells can be completed and blue drop downs work -

2. Dashboard for management that gives insights
Reference workbook - Groups Sales KPI Brief Master

Brief / Dashboard reporting metrics
Average Sale Value
Average calls per meeting
Average meetings per sale
Based on monthly forecast
Annual forecast versus target

Once this is complete we will create a future task to ready the sheet and process for a web environment so that each individual has a unique log in can submit data that feeds into the management insights.
Assignee
Jonathan T.
(1)
100%Completion rate

The percentage of accepted tasks this Tasker has completed.

I have used Excel for many years and perform this type of work all the time.
I can create macro's and results with graphics as need be.

kind regards
Jonathan
Questions(17)
Sam J.poster
First fix the sheet.
... don't take my word for it, get an Excel guru and play with it, if you work it out - fine and I am happy for you by all means!

If you can't work it out in Excel, you can assign the task to me and look at it as done, Regards
.... btw the Password Log on / Log off / ability to change passwords / your ability to retrieve your workers password / blocking one user from another users data while allowing your password to all the data, etc. etc. etc.

Forecasting, Reporting, Averaging, etc. etc.

we looking at around $2000

and if you want to network it, up to 5 users it is $1000 extra more then 5 users could be that a new server will be required (service pack 2)

Just let you know before assigning to me :)
Hi Sam,

You are almost there 99% completed - well done!!

Now that was the good news, the bad news is that you are going to stay at that stage for as long as you are using Excel

You have to re-write the whole program in a Relational Database Management System

Also you want it ''foolproof'', ''genius proof'' and ''crash proof''

I can develop it for you in a RDMS

My bid is to meet you in your office in Melbourne, accurately analyse your requirements and provide with a quote :)
Have a think about how you'd like that to work, but you can't just simply 'add rows'.

Your KPI's are simple enough, just need to think carefully through how thee are calculated keeping in mind that as you add rows the formulas's for those need to be kept static too.

cheers
Jonathan
0 new tasks