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!
