Identifying Form 470 Category 1 Service Option Errors:
Last week’s newsletter dated November 19th noted that Form 470s for FY 2019 are still reflecting confusion on the part of some applicants regarding the proper use of the Category 1 pulldown menu options for requesting Internet and/or transmission services. Although Form 470 problems are ultimately the individual applicants’ responsibility, it may be helpful for state and regional E-rate coordinators to identify likely Form 470 errors and to encourage applicants to refile corrected Form 470s.
The most common problem we’re seeing is among smaller applicants that are requesting Internet access, typically via cable modem services. Too many Form 470s are requesting “Internet Access: ISP Service Only (No Transport Circuit included)” without any requests covering the associated circuit costs themselves. With the growing number of filings for FY 2019, finding and reviewing Form 470s requesting only ISP service is not a trivial exercise for state or regional coordinators.
Here’s one way to manipulate USAC’s database to highlight applicants requesting only ISP service:
- Download the state’s list of Form 470s posted for FY 2019 using USAC’s Download 470 Information (FY2016 and later). To focus only on potential Internet posting problems, constrain the download (in Excel) to Category 1 “Data Transmission and/or Internet Access” service types.
- The download file contains a wealth of Form 470 data with one row per “Function” request and multiple columns. For ease of use — reflected in the examples below — you may want to “Hide” unneccesary columns.
- Sort the file by Billed Entity Name. If an entity has filed more than one Form 470, the sort will group the entity’s Form 470s together.
- Insert a column (the new Col. S in the following example) and add the following Excel formula to each data cell in that column:
=COUNTIF(E$2:E$335,E2)
Note that the first argument in this formula covers the full fixed range of applicant names from Row 2 down to the end of the rows (in this case Row 335). The formula will count the number of rows (i.e., the number of “Function” requests made by each applicant). Applicant 10661, for example, has two requests.
- To easily find applicants only requesting “Internet Access: ISP Service Only (No Transport Circuit included),” filter the spreadsheet for:
- Entities with only one request (as shown in Col. S); and
- Requests for ISP Service Only (as shown in Col. U).
- Resulting in the following list of applicants with potentially misleading Form 470s:
State and regional E-rate coordinators may wish to reach out to these applicants to review their full Internet requirements and to make sure that their Form 470s are properly posted.