Quarterly+Report+Data+Checking

=Data Checking =

Quarterly Reports | Quarterly Report District-Building Code Tips

REMC staff conduct a thorough audit of each report as it is received. The data in each field is scanned to ensure that it falls within expected parameters, and all calculations are checked. Reports that are questioned may be sent back to vendors for correction. Reports are not considered to have been received until REMC is satisfied that all obvious errors have been corrected. Each late report results in a competitive disadvantage in the next bid cycle. This page outlines the audit process that REMC conducts on each report. We recommend that vendors conduct their own audit before submitting the report to minimize the likelihood that the report will be returned and possibly counted as late.

 Sort by each field to ensure that all data meets the following standards:


 * **Field ** || **Expected Minimum **
 *  Value ** || **Expected Maximum **
 *  Value ** || **Comments ** ||
 * Purchase Order Number || N/A || N/A || Custom to each district ||
 * Order Date || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Beginning of Quarter || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">End of Quarter || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">The minimum date is expected to be the first day of the quarter or after. Occasionally, there could be reasons to report sales from previous quarters. However, any dates prior to the beginning of the quarter should be checked for duplicate reporting or data errors.

<span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;"> The maximum date should never be after the end of the quarter. Post-dated sales should be reported in the quarter in which the order date falls. ||
 * <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">District Code || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">1010 || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">84050 || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Any numbers outside this range are errors and must be corrected. ||
 * <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Building Code || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">0 || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">99999 || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Any numbers outside this range are errors and must be corrected. ||
 * <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">School District (Bill to) || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">N/A || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">N/A || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Free form field. ||
 * <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Building (Ship to) || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">N/A || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">N/A || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Free form field. ||
 * <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">ZIP Code || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">48000 || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">49999 || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Any numbers outside this range are not Michigan ZIP codes and should be checked for error. Sales to institutions outside Michigan are not to be reported and should be deleted. ||
 * <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">REMC Center Number || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">1 || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">22 || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Any numbers outside this range are errors and must be corrected. ||
 * <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">REMC Bid Item Number || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Varies || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Varies || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">For most items, the first one or two digits indicate the year of the contract. For example, for 2012, REMC Item Numbers begin with "12." For multiyear contracts such as computers and software, the first one or two digits indicate the year the contract began and do not change during the contract term. For example, for the 2011-2014 Computer & Network contracts, the REMC Item Numbers begin with "11" indicating a contract beginning in 2011. The report should be sorted by REMC Bid Item Numbers and the minimum and maximum checked for consistency with the products awarded to your company. ||
 * <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Quantity Purchased || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">N/A || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">N/A || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Returns and credits are indicated by marking the Quantity Purchased with a negative sign ("-"). Do not use parentheses. Theoretically, there are no expected minimum or maximum numbers for this field. However, unexpectedly large or small numbers should be checked for error. In addition, all quantities of zero should be checked for error. Generally, lines with quantities that actually are zero should be deleted. Check with REMC Bid staff if you believe that there is a reason that they should be left in a report. ||
 * <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Education List Price || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">$0.01 || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Varies || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">As defined in the ITB, Education List Price is the price that a Michigan K-12 school would pay for one unit of an item, including shipping, if they purchased the item on their own without consortium pricing in place. This is almost always less than MSRP, retail, list or street price. It is important that the correct price be used in this column so that REMC can report actual savings to schools. The price should also match the Education List Price shown in the REMC online catalog. Any changes in Education List Price during the contract term<span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;"> must be submitted via the Vendor Center s<span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">o that the online catalog can be updated.

<span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;"> Education List Price is expected to be a positive number greater than zero, and greater than the REMC price. Returns and credits are indicated by negative numbers in the Quantity Purchased column. An Education List Price of zero should be checked for error.

<span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;"> The Education List Price should be for one unit of the item. In other words, do not multiply the Quantity Purchased by the Education List Price when entering the data. ||
 * <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">REMC Price || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">$0.00 || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Varies || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">REMC price is the actual price that the customer paid for one unit of the item. Do not multiply the Quantity Purchased by the REMC Price when entering the data in this column.

<span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;"> The REMC price is expected to be less than the Education List Price and is always a positive number. Returns and credits are indicated by negative numbers in the Quantity Purchased column. There may be a very few instances where the REMC price equals the Education List Price. REMC prices greater than Education List Price must be corrected. REMC prices equal to zero indicate that the customer did not pay for the item. These should be checked for error. For items with no value (that is, Education List Price and REMC Price equal to zero), the line should be deleted.

<span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;"> To check that all REMC prices are less then or equal to Education List Price, add a column to the right of the REMC price and enter a formula subtracting the REMC price from the Education List Price (usually =K3-L3, assuming that the first row of data is row 3). Sort on the new column. For negative differences, there is an error in one of the prices that must be corrected. If the difference is zero, check for error. It is also advisable to look at the bottom of the report to see if there are very large differences that could indicate an error. Be sure to delete the column that was added.

<span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;"> REMC prices higher than those listed in the REMC online catalog should be checked for error and potential problems with sales data entry. Vendors who charge more than the bid price are in violation of the contract. Conversely, REMC prices lower than the REMC catalog price may indicate that a vendor has not reported a price reduction to REMC. This is also a violation of the contract. ||
 * <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Total Dollar Sales || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Varies || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Varies || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">On every line, this number must equal exactly the Quantity Purchased multiplied by the REMC Price. REMC Staff audit this using the following process:

<span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;"> Add a blank column to the left of Total Dollar Sales. Enter a formula in the first row of data multiplying the Quantity Purchased by the REMC Price (usually =J3*L3, assuming that the first row of data is row 3). Fill the formula down, then sum the column that you just added as well as the Total Dollar Sales column. If the two sums are equal, there are likely no errors in the Total Dollar Sales column.

<span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;"> If the sums are not equal, or if you want to be sure that there are no errors, add a column to the right of Total Dollar sales and enter a formula subtracting the Total Dollar Sales from the other column that you added (usually =M3-N3, assuming that the first row of data is row 3). Fill the formula down, then sort or filter on this new column. Any lines that are not equal to zero indicate an error in the calculation of the Total Dollar Sales. These errors must be corrected.

<span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;"> Once the Total Dollar Sales are correct, the extra formula column(s) should be deleted. The sum at the bottom of the Total Dollar Sales column is the amount on which you will be invoiced for the REMC administrative fee. ||
 * <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Vendor Name || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">N/A || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">N/A || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Enter your company's name is it appears in the REMC online catalog. This is not the manufacturer of the product. ||
 * <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Item/Catalog Discount Description || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">N/A || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">N/A || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">This field is only required for items sold through a catalog discount. Provide a brief description of the item. Generally, the description from your invoicing system would be sufficient. ||
 * <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">SKU || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">N/A || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">N/A || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">This field is only required for items sold through a catalog discount. Provide a unique identification number or code for the item. This can be a manufacturer part number, SKU or model number, or your company's order number. Be consistent in which type of number that you use. REMC uses this field to scan catalog discounts for sales volumes for specific products. Having good match points in this column helps facilitate this process. ||
 * <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Customer Number (Bill to) || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">N/A || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">N/A || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Enter a permanent and unique identification code native to your company's invoicing system for the Bill-to location. ||
 * <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Customer Number (Ship to) || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">N/A || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">N/A || <span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;">Enter a permanent and unique identification code native to your company's invoicing system for the Ship-to location. ||

<span style="font-family: 'Trebuchet MS',Helvetica,sans-serif;"> Quarterly <span style="font-family: Arial,Helvetica,sans-serif;">Reports | <span style="color: #800000; font-family: Arial,Helvetica,sans-serif;">Quarterly Report District-Building Code Tips