Research Support Billing

Overview

The Research Support Group charges a subscription fee for services. Research Groups or individuals may subscribe to either Dedicated or Desktop support. Detailed notes can be found here.

Timing

As of the 2010 CSCF Advisory Committee decision, bills are to be sent out once a term near the beginning of the term. That allows the subscribers to review the proposed bill and make adjustments before the services are delivered, if necessary.

Billing Files

Edocs\rsg\Subscriptions\Billing data

• broken down by term
• each term has a spreadsheet and associated .doc files which act as a mail merge document to print the bills

Review procedure

Prepare documents for review process

1. Copy spreadsheet and .doc files from previous term into a separate folder for the new term
   • eg: from X:\rsg\Subscriptions\Billing data\Billing-2010Fall to X:\rsg\Subscriptions\Billing data\Billing-2011Winter
2. Rename the spreadsheet to reflect the new term (eg: Billingdata-W11.xls)
3. Update the spreadsheet for the new data
   1. Rename the first tab (eg: from F10-Billing to W11-Billing)
   2. Insert a new worksheet to hold the previous term's hours report (eg: F10)
   3. Run the subscription report to get total hours for the previous term
      • Subscriptions: https://www.cs.uwaterloo.ca/cscf/research/subRC/index.php
      • Usage Report -> Sort By: Subscription Type -> By Term: Fall 2010 (pick the appropriate term)
      • Generate Report
      • Copy the table for Dedicated and Desktop subscriptions to the new tab in the spreadsheet
      • remove extraneous lines (compare to other tabs), leave the headings, I like to resize the columns for neatness
   4. Create new historical columns for this past term on the "Subscriptions" tab
      • Insert four new columns ahead of the previous term, eg: (for Fall 2010)- F10/S10/W10 avg, F10 Units, F10 Hours, F10 +/-
      • Copy the current F10 units from column G (the billing units for the previous term) into "F10 Units"
      • Copy the formula from the previous term's "Hours" column to the first cell in F10 Hours, eg: =VLOOKUP(A2,'S10'!$1:$65536,2,0) * new formula for F10 would be: =VLOOKUP(A2,'F10'!$1:$65536,2,0) * Copy that formula all the way to all of the rows in that column with a subscription code
   5. Calculate the F10 +/- based on the number of hours subscribed vs number of hours tracked such that a negative number means they got less hours than they paid for and a positive number indicates they got more hours than they paid for
      • eg: ="F10 Hours"-("F10 Units"*"Hours/Unit)
      • Copy that to the end of that column
   6. Calculate the average +/- over the last 3 terms
      • in the first row of the F10/S10/W10 column, create a formula that adds "F10 +/-", "S10 +/-" and "W10 +/-" (the last three terms) and divide by 3
      • copy that to all of the other rows
   7. Update the current term units column
      • change the column heading from the previous term (eg: "F10") to the current term (eg: "W11")
      • that makes the past term's units the default for this term
   8. Update the formula for the IncDec column (noting an increase or decrease from a previous term)
      • in the first row: ="W11" - "F10 Units" (first row)
      • copy to all rows
   9. Create a new Comments column for the current term. Insert ahead of the previous term. Keeping the old comments can be helpful in future terms.

Review Dedicated subscriptions

• Overview: we're going to look at the average number of hours up or down per term, see it looks like a single-term anomaly or a trend. Typically we're looking for numbers of hours greater than 7 (+/-) and likely closer to 14. 7 hours is roughly a 1/2 unit, 14 is a full unit. For instance, over the past three terms if there was one really high term, but the terms before and after were close, we probably wouldn't raise the subscription. If, however, there were three consecutive terms of 14+ hours over, we would want to propose an increase of one (or more) units. Similarly, if we were typically low by roughly the same amounts, we would want to drop it by a unit. We can consider 1/2 unit increases or decreases, with a 1/2 unit being the minimum subscription amount.

Review Desktop subscriptions

• It can be helpful to look at Debbie's list of grad students in the Grad Office Assignments in the Sharepoint admin documents