0 Replies Latest reply: Dec 7, 2012 1:27 PM by Michael Ellerbeck RSS

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

    Michael Ellerbeck

      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!