Spreadsheet: 55197d1255367010-multiple-lookup-and-iserror-help-sample
Sheet: Sales Reps multi-input

 | • Please copy and paste the values from the individual sales representatives summary sheets (make sure that you copy from the first entry line and copy and paste in the first entry line in this sheet).   • The sheet will automatically calculate the average of the sales representatives and feed it into a summary page. Further in the sheet you will be able to compare individual sales representatives against each other on a specific activity.  • It will also give you the standard deviation of the values from average to show the volatility of the values. A high value might be worth having in mind or even remove the extreme and handle that seperately.  • Please note that the number of activities and time at activity input is further to the right
 | AS IS |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1
 |  |  | AL |  | IS |  | KG |  | KF |  | LI |  | MC |  | MP |  | TV |  | VN |  | VP |  | Average |  |  |  |  | Sales Rep 1 |  | [NAME] |  | [NAME] 3 |  | [NAME] |  | Sales Rep 5 |  | Sales Rep 6 |  | Sales Rep 7 |  | Sales Rep 8 |  | Sales Rep 9 |  | Sales Rep 10 |  | Sales Rep 11 |  | Sales Rep 12 |  | Average
 | Customer contact - new business (needs' investigation, solution presentation, price negotiation, follow up of bid, deal closing) |  | 0.15 |  | 0.1 |  | 0.1 |  | 0.1 |  | 0.2 |  | 0.2 |  | 0.12 |  | 0.1 |  | 0.15 |  | 0.1 |  | 0.132 |  |  | Number of Leads Generated
 | Contact with Customer as part of contact strategy, as health check or for renewal negotiations |  | 0.13 |  | 0.16 |  | 0.15 |  | 0.05 |  | 0.15 |  | 0.2 |  | 0.1 |  | 0.15 |  | 0.05 |  | 0.15 |  | 0.129 |  |  | Average Lead Generation Time (Mins)
 | Office activities for new business (pricing strategy, bid creation, order building & processing) |  | 0.09 |  | 0.12 |  | 0.1 |  | 0.05 |  | 0.11 |  | 0.02 |  | 0.05 |  | 0.01 |  | 0.15 |  | 0.1 |  | 0.08
 | Office activities for renewals (communications with Cust.Care, contracts creation for special deals etc) |  | 0.07 |  | 0.15 |  | 0.2 |  | 0.05 |  | 0.1 |  | 0.02 |  | 0.07 |  | 0.16 |  | 0.1 |  | 0.15 |  | 0.107 |  |  | Number of Initial Pricings
 | Update info in CRM & SFA tools (HVC application) |  | 0.05 |  | 0.02 |  | 0.05 |  | 0.05 |  | 0.06 |  | 0.04 |  | 0.09 |  | 0.05 |  | 0.05 |  | 0.03 |  | 0.049 |  |  | Average time to create initial pricing
 | Extract info from CRM / SFA for customer meetings of the day |  | 0.05 |  | 0.01 |  | 0.01 |  | 0.05 |  | 0.02 |  | 0.02 |  | 0.05 |  | 0.05 |  | 0.05 |  | 0.01 |  | 0.032
 | Critical customers' info exchange with Customer Care CSR |  | 0.02 |  | 0.01 |  | 0 |  | 0.1 |  | 0.05 |  | 0.1 |  | 0.03 |  | 0.05 |  | 0.05 |  | 0.01 |  | 0.042 |  |  | Number of lead qualification meetings
 | Activities with Internal VF organisation to solve all isuues, non related to account manager's sales duties (*) |  | 0.09 |  | 0.2 |  | 0.2 |  | 0.1 |  | 0.04 |  | 0.2 |  | 0.16 |  | 0.04 |  | 0.1 |  | 0.2 |  | 0.133 |  |  | Average time per lead qualification meeting (incl. Travel)
 | Customer Queries - Provide Info to customer for all areas |  | 0.08 |  | 0.04 |  | 0.04 |  | 0.05 |  | 0.05 |  | 0.05 |  | 0.05 |  | 0.05 |  | 0.05 |  | 0.05 |  | 0.051
 | One-to-one coaching with supervisor |  | 0.04 |  | 0.01 |  | 0.04 |  | 0.05 |  | 0.02 |  | 0.02 |  | 0.03 |  | 0.05 |  | 0.05 |  | 0.01 |  | 0.032 |  |  | Number of Credit Checks
 | Customers' lists management for events, gifts, promos, business club etc (create list with prospects, call or visit to inform customers, inform admin for final list) |  | 0.03 |  | 0.02 |  | 0.01 |  | 0.05 |  | 0.02 |  | 0.02 |  | 0.05 |  | 0.01 |  | 0.02 |  | 0.02 |  | 0.025 |  |  | Average time to arrange a credit check
 | Internal Sales Meetings |  | 0.05 |  | 0.04 |  | 0.04 |  | 0.05 |  | 0.03 |  | 0.01 |  | 0.05 |  | 0.05 |  | 0.05 |  | 0.05 |  | 0.042
 | Results & Achievements'  comparison vs actual sales orders |  | 0.02 |  | 0.01 |  | 0.01 |  | 0.05 |  | 0.01 |  | 0.01 |  | 0.03 |  | 0.03 |  | 0.02 |  | 0.01 |  | 0.02 |  |  | Number of customer who need requirements testing
 | Account Review & Analysis |  | 0.04 |  | 0.04 |  | 0.01 |  | 0.05 |  | 0.05 |  | 0.02 |  | 0.05 |  | 0.05 |  | 0.03 |  | 0.04 |  | 0.03799999999999999 |  |  | Average time to test requirements
 | Product & Skills Training |  | 0.02 |  | 0.02 |  | 0.01 |  | 0.05 |  | 0.02 |  | 0.02 |  | 0.02 |  | 0.05 |  | 0.03 |  | 0.02 |  | 0.026
 | Travel (from and to customers) |  | 0.07 |  | 0.05 |  | 0.03 |  | 0.1 |  | 0.07 |  | 0.05 |  | 0.05 |  | 0.1 |  | 0.05 |  | 0.05 |  | 0.06200000000000001 |  |  | Number of bids
 | Sales activities |  | 0.56 |  | 0.5700000000000001 |  | 0.6100000000000001 |  | 0.45 |  | 0.69 |  | 0.6 |  | 0.51 |  | 0.5700000000000001 |  | 0.6 |  | 0.55 |  | 0.5710000000000001 |  |  | Average time to create contract needing amendments
 | Non-Sales Activities |  | 0.44 |  | 0.43 |  | 0.39 |  | 0.5499999999999999 |  | 0.3100000000000001 |  | 0.4000000000000001 |  | 0.49 |  | 0.43 |  | 0.4000000000000001 |  | 0.45 |  | 0.429
 | Total |  | 1 |  | 1 |  | 1 |  | 0.9999999999999999 |  | 1 |  | 1 |  | 1 |  | 1 |  | 1 |  | 1 |  | 1 |  |  | Number of sign-offs required
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Average time to obtain sign off
 | DESIRED
 |  |  | AL |  | KG |  | Average |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Number of times to update post sales info
 | Customer contact - new business (needs' investigation, solution presentation, price negotiation, follow up of bid, deal closing) |  | 0.19 |  | 0.2 |  | 0.195 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Average time to update info
 | Contact with Customer as part of contact strategy, as health check or for renewal negotiations |  | 0.17 |  | 0.3 |  | 0.235
 | Office activities for new business (pricing strategy, bid creation, order building & processing) |  | 0.05 |  | 0.1 |  | 0.07500000000000001 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Number of Complaints
 | Office activities for renewals (communications with Cust.Care, contracts creation for special deals etc) |  | 0.03 |  | 0.1 |  | 0.065 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Average handling time of complaint
 | Update info in CRM & SFA tools (HVC application) |  | 0.05 |  | 0.05 |  | 0.05
 | Extract info from CRM / SFA for customer meetings of the day |  | 0.08 |  | 0.01 |  | 0.045 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Number of Billing/Tariff Queries
 | Critical customers' info exchange with Customer Care CSR |  | 0.1 |  | 0.01 |  | 0.055 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Average time to handle Billing/Tariff Query
 | Activities with Internal VF organisation to solve all isuues, non related to account manager's sales duties (*) |  | 0.02 |  | 0.07 |  | 0.04500000000000001 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Number of service issues
 | Customer Queries - Provide Info to customer for all areas |  | 0.03 |  | 0.02 |  | 0.025 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Average time to handle service issue query
 | One-to-one coaching with supervisor |  | 0.07 |  | 0.02 |  | 0.04500000000000001
 | Customers' lists management for events, gifts, promos, business club etc (create list with prospects, call or visit to inform customers, inform admin for final list) |  | 0.03 |  | 0.01 |  | 0.02 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Number of Customer details queries
 | Internal Sales Meetings |  | 0.05 |  | 0.04 |  | 0.045 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Average time to handle customer details query
 | Results & Achievements'  comparison vs actual sales orders |  | 0 |  | 0.02 |  | 0.01
 | Account Review & Analysis |  | 0.06 |  | 0.01 |  | 0.035 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Number of Contract Re-newals
 | Product & Skills Training |  | 0.05 |  | 0.01 |  | 0.03 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Average time to [NAME] contract
 | Travel (from and to customers) |  | 0.02 |  | 0.03 |  | 0.025
 | Sales activities |  | 0.6699999999999999 |  | 0.77 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Number of change requests
 | Non-Sales Activities |  | 0.33 |  | 0.23 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Average time to handle change request
 | Total |  | 1 |  | 1
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Number of internal meetings
 | AS IS VS DESIRED |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Average time per internal meetings
 |  |  | As Is |  | Desired |  | Delta
Customer contact - new business | Customer contact - new business (needs' investigation, solution presentation, price negotiation, follow up of bid, deal closing) |  | 0.132 |  | 0.195 |  | 0.06300000000000003 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Number of forecasting times
Contact strategy - renewal negotiations | Contact with Customer as part of contact strategy, as health check or for renewal negotiations |  | 0.129 |  | 0.235 |  | 0.106 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Average time to forecast
Office activities - new business | Office activities for new business (pricing strategy, bid creation, order building & processing) |  | 0.08 |  | 0.07500000000000001 |  | -0.004999999999999991
Office activities - renewals | Office activities for renewals (communications with Cust.Care, contracts creation for special deals etc) |  | 0.107 |  | 0.065 |  | -0.04200000000000001 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Number of reporting times
Update info - CRM & SFA tools | Update info in CRM & SFA tools (HVC application) |  | 0.049 |  | 0.05 |  | 0.001000000000000001 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Average time to report
Extract info - CRM & SFA tools | Extract info from CRM / SFA for customer meetings of the day |  | 0.032 |  | 0.045 |  | 0.013
Customer info exchange | Critical customers' info exchange with Customer Care CSR |  | 0.042 |  | 0.055 |  | 0.013 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Number of times to perform Account Analysis
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Average time to analyse account
Internal activities - issues, etc. | Activities with Internal VF organisation to solve all isuues, non related to account manager's sales duties (*) |  | 0.133 |  | 0.04500000000000001 |  | -0.088
Customer queries | Customer Queries - Provide Info to customer for all areas |  | 0.051 |  | 0.025 |  | -0.026 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Number of system activities
One to one coaching | One-to-one coaching with supervisor |  | 0.032 |  | 0.04500000000000001 |  | 0.013 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Average time to perform system activitiy
Customer list management | Customers' lists management for events, gifts, promos, business club etc (create list with prospects, call or visit to inform customers, inform admin for final list) |  | 0.025 |  | 0.02 |  | -0.005000000000000001
Internal sales meetings | Internal Sales Meetings |  | 0.042 |  | 0.045 |  | 0.003000000000000003 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Number of Sales Skills Training Modules
Performance reviews | Results & Achievements'  comparison vs actual sales orders |  | 0.02 |  | 0.01 |  | -0.009999999999999997 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Average per sales skills training module
Account reviews and analysis | Account Review & Analysis |  | 0.03799999999999999 |  | 0.035 |  | -0.002999999999999996
Product and skills training | Product & Skills Training |  | 0.026 |  | 0.03 |  | 0.004 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Number of Product Specific Training Modules
Travel | Travel (from and to customers) |  | 0.06200000000000001 |  | 0.025 |  | -0.03700000000000001 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Average per Product Specific training module
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Number of travel times
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Average travel time