Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
mellerbeck
Creator II
Creator II

Weighted Average as well as total average of a 'booking', creating score card

Trying to create a score card that will give a weighted grade of all total bookings as well as a grade per customer

So sales people have a booking, a booking consists of a start date an end date and an amount booked as well as amount sold

Let's say customer A, startdate 1/3/2012, enddate 1/1/2013 and 1000 LB's booked and 500 LB's sold

So pretending that todays date is July 3rd 2012 to make the math come out even. We can calculate the total days in the booking = 364

divide the 1000 by 364 gives us that we should on average sell 2.75 LB's a day.

Then using our pretend date we are 182 days into the booking so multiple that by our 2.75 gives us a 'Target' of 500.

Dividing our amount sold (500) / by our amount sold (500) gives us a 1.00 as a projected usage so right on target.

So now use an interval match to assign a range of projected usages to equal the typical school Grade i.e. if it looks like the booking will oversell by 1.26 times then assign that an .85 or a B+

.85;.65;.7499;

.90;.75;.8499;

.95;.85;.9499;

1;.95;1.0599

.95;1.06;1.1599

.90;1.16;1.2599

.85;1.26;1.3599

So, to get a weighted grade by customer figure out the percent of the total booking for that customer that the booking signifies and multiply it by by the score i.e. if its 33 % of the booking multiplied by .85

Anyways thats what I was trying to do, I'll attach it and if someone wouldn't mind looking it over and see if the math works out I would really appreciate it!

0 Replies