Loading...
Municipal Resource Consultants Southern California �pURCF 32107 W.Lindero Canyon Road Suite 233 MUNICIPAL RESOURCE CONSULTANTS Westlake Village,CA 91361 A partnership of John T. Austin, Inc. &Allen W. Charkow, Inc. (818)991-5220 Sy�y Central California • (209)432-6039 Northern California (415)838-1115 November 2 , 1990 Mr. Mark A. Rohloff Deputy City Manager City of Rancho Palos Verdes 30940 Hawthorne Blvd. Rancho Palos Verdes, CA 90274 Re: Sales Tax Enhancement & Information Services Dear Mr. Rohloff: Thank you for recommending Municipal Resource Consultants (MRC) to provide the City and Redevelopment Agency of Rancho Palos Verdes with sales tax audit, information and • consultation services. • If approved by Council, we plan to conduct the initial audit within the next 90 days. In this regard, we need to receive the City's sales tax distribution reports for the current and previous eight quarters by November 19, 1990. With regard to the sales tax information service, we plan to deliver the first set of STARS reports within 45 days after receiving the City's sales tax distribution reports for the current and previous eight quarters. In order to work as smoothly as possible with the City, we have prepared the following orientation and procedural guidelines to familiarize you and your colleagues with the staff and functional division of responsibilities between our three office locations. We have also identified those areas where we need the City's assistance in procuring certain information. Sales Tax Audit & Information Schedule & Progress Chart (Preliminary) City: Contract Date: DESCRIPTION Month Month Month Month Month Month Month Month Month Month Month Month Month 1 2 3 4 5 6 7 8 9 10 11 12 13 Contract Authorization X • Request Permit Listing & Quarterly Dis. Reports XX Input Permit Lising & Quarterly Dist. Reports ` Oa Prepare, Deliver & Review w/City initial Stars Reports X Inventory & Analyze All Business In City X" Develop Target List of Potential Misallocations I XXX Orientation Meeting X • Contact Each Business On Target List XX X Prepare, Deliver & Review w/City Initial Report Identifying Misalloat'ed Accounts X Coordinate w/Taxpayer & SBE To Correct Misallocated Accts. — ---` -- -- ------_ Receive, Process, Monitor & Analyze Quarterly Dist. Reports — -------__—--_ Ongoing Identification & Correcting of Misallocated Accts. — --�—_----_ __ = Deliver Ongoing Audit & Stars Reports — ---__=--"—— __ _---_--- Quarterly Progress Reports =_=• • X Full Time Effort = Part Time Effort As Required • Mr. Mark A. Rohloff City of Rancho Palos Verdes November 2, 1990 Page 3 ADMINISTRATION, AUDITING & DATA PROCESSING & CONTRACTING ANALYSIS WESTLAKE VILLAGE OFFICE FRESNO/MADERA OFFICE Municipal Resource Consultants Municipal Resource Consultants 32107 W. Lindero Canyon Road 10174 N. Highway 41 Suite 225 Madera, CA 93638 Westlake Village, CA 91361 (209) 432-6039 (818) 991-5220 Staff: Staff: Carol Broughton - Analyst John Austin - Partner Allen Charkow - Partner Carol Cortrite - Manager Pearl Charkow - Manager Beverly DeMoss - Manager Mary Conboy - Data Technician Darleen Gardner - Auditor Russell Douglas - Cartographer Pamela Kevan - Administrator Scott Eckman - Cartographer Douglas Kitchen - Consultant Mary Flynn - MIS Manager Greg Landrum - Auditor Randy Ramos - Analyst Susan Minton - Administrator Sandy Schwartz - Data Technician Chris Ward - Administrator Toni Scott - Data Technician Nelly Sotelo - Data Technician Functions: James H. Tilkes - MIS Director Revenue Enhancement Audits Functions: o Interfacing with Taxpayer Sales Tax Information (STARS) o Interfacing with Inter- mediaries o Data Collection o Data Entry Contract Administration o Data Processing o Data Monitoring & Analysis o Proposals o GIS Reports o Contract Negotiation o Progress Reports Revenue Enhancement Consulting o Accounting (billings & Business License Reports accounts receivable) Economic Development Reports SAN RAMON OFFICE Municipal Resource Consultants 12 Crow Canyon Court, #200 San Ramon, CA 94583 (415) 838-1115 Staff: Hunter Austin - Manager Deborah Blake - Auditor Lowell Smith - Consultant Janis Varney - Auditor Cathy Young - Auditor Mr. Mark A. Rohloff City of Rancho Palos Verdes November 2 , 1990 Page 4 MRC needs the City's assistance in procuring the information summarized as follows and further discussed below: ACTION SUMMARY o Complete information request sheet (Exhibit A) o Send nine quarters of distribution reports (QDR's* for current plus previous eight quarters) to. MRC's Fresno/Madera office o Send future QDR's* immediately upon receipt from the State Board of Equalization (SBE) to MRC's Fresno/Madera office o If the City has not done so already, request QDR payment data from the SBE on : - High density, 1.2 MEG floppy diskette (5.25") if less than 10, 000 permits are registered to the City; or - 9-track tape, 1600-BPI magnetic tape, if more than 10, 000 permits are registered to the City. The SBE contact for ordering QDR data is: Mr. Lawrence Micheli Supervisor Local Tax Unit State of California Board of Equalization 2014 "T" Street, Suite 220 Sacramento, CA 95814 (916) 739-3334 o Map showing city boundaries and non-residential areas (e.g. light and heavy industrial, office buildings, regional malls, major retail, etc. ) o If the City has a business license tax, please send an address order printout of the City's business licenses to our Fresno/Madera office. The run is used to help clarify some of the owner/business name discrepancies on State Board of Equalization files. SALES TAX AUDIT SERVICE An information sheet for the sales tax audit service is attached as Exhibit A. The information requested will facilitate MRC's interaction with the City by identifying key personnel whom we can contact. The data requested greatly facilitates the audit process. Please complete the sheet and return it to our Westlake Village office at your earliest convenience. When the field audit work begins, MRC will need to utilize one or two tables or desks with telephones, ideally in the same work area. This area can be located anywhere within the City facilitites. * Printed copy, microfiche, floppy diskette or computer tape. Mr. Mark A. Rohloff City of Rancho Palos Verdes November 2, 1990 Page 5 SALES TAX INFORMATION SERVICE By standardizing and refining data in the preparation of STARS (Sales Tax Analysis and Reporting System) , we are able to provide the City with vital information in quarterly reports, including computer prepared analysis of the key changes between benchmark years and quarters. The STARS reports enable MRC and the City to monitor that portion of the City's business community which generates the City's sales tax revenue. Up until recently, QDR's have only been available on. microfiche or in printed form. However, the SBE is now providing the QDR's on computer tape or floppy diskette (see Exhibit B for additional discussion) . QDR payment data on electronic media has enabled us to enhance our quarterly information service without raising prices. Only those accounts which in aggregate generate 90% of the City's sales tax are tracked when sales tax payments are processed from the paper printouts provided by the State, as these payments must be hand entered into the computer. Once MRC receives the City's sales tax payment data on tape or diskette, STARS is upgraded to report on all permits. The best way to provide the QDR data to MRC is by high density (1.2 MEG) floppy diskette (5. 25") , as there is no cost to the City for the diskettes. For those of our clients with more than 10, 000 permits registered to the city, the 9-track tape will likely be required and there will be a charge by the State of about $12 per month for the tape. Only payment data should be requested as MRC currently receives all master file data via 9-track tape from the SBE for all of our clients. Master file data is the information that is currently printed -"-- on the "yellow cards" sent by the SBE. When the City receives the floppy diskette or 9-track tape, it should forward same to MRC's Fresno/Madera office for processing. MRC will produce the printed QDR in the same format the State has provided in the past should the City so desire. For the initial set-up, please send the State Board of Equalization's quarterly distribution reports to the MRC's Fresno/Madera office for the current and previous eight quarters. • Mr: Mark A. Rohloff City of Rancho Palo Verdes November 2 , 1990 Page 6 All payment data is allocated to the quarter in which the sales tax was generated by each firm, as opposed to when it was collected by the State. MRC has upgraded its service and now begins the STARS service with nine full quarters of payment data, instead of six quarters. The upgrade was initiated due to MRC's new computer prepared analysis of sales tax growth/decline, which requires two full years of data for comparison. Nine quarters are requried (instead of eight because the most current quarter is never utilized for benchmark comparisons due to varying proportions of late payments by firms each quarter, which show up as zeros in the most current quarter's data. The missing late payments would distort comparisons with previous sales tax results. MRC will also be installing the sales tax data on a PC at the City using special MRC programs to allow the City additional utility in examining and using sales tax information. Besides having the ability to see the full historical payment data on one screen, the City can access sales tax data by business tax ID numbers and can create their own geo area reports on-site. STARS service also includes access to MRC legal staff, workshops with City personnel regarding business retention/attraction, as well as other informational services as required by the City. A report summarizing changes in sales tax permit registration data is also provided to the City for business license personnel to utilize in their compliance efforts. It is important to send payment disks/tapes received from the Board of Equalization to MRC's Fresno/Madera office as soon as they are received by the City. This helps insure timely reports to the City and early data monitoring by MRC for audit purposes. Questions or corrections on reports from the STARS system should be directed to our Fresno/Madera office. City staff are naturally more familiar with local business names and the nature of their City businesses. Initial data provided by the Board may give parent corporation or local owner names instead of the business names used locally. Often the State misclassifies firms under the wrong business code as well. While MRC sanitizes much of this data, some modifications may still be desired by the City. As these changes are discovered, MRC will make the necessary corrections. We are pleased to have the City of Rancho Palos Verdes as our client and look forward to providing sales tax audit and information services that will produce valuable results to the City on an initial and ongoing basis. Mr.- Mark A. Rohloff City of Rancho Palo: Verdes November 2 , 1990 Page 7 We hope that this guide proves helpful and welcome any suggestions you may have to improve our service. Sincerely, hn T. Aus in 007WP/cw cc: Mary Flynn Enclosures • EXHIBIT A INFORMATION REQUESTED Please complete the following and return to MRC's Westlake Village office: 1. Name, title, address, and phone number of principal contact in the City that will act as the general liaison for this contract. 2 . Name, title, address, phone number of principal contact in the Planning Department for annexation, street address, or City boundary information. • 3 . Name, title, address, phone number-and function of any other key personnel assigned to assist with the contract. Page 2 INFORMATION REQUESTED • 4 . Does the City maintain address data for those properties contained within the City limits such as an address range file or directory? If yes, please attach a copy. 5. Has the City annexed any land in the last five years that either had commercial, office or industrial uses existing, or have such uses been added since annexation? If yes, please attach a copy of the annexation map(s) that shows the boundaries of each annexation. 6. Does the City or Chamber of Commerce or other local entity provide a local map of the City? If yes, please attach. MRC would prefer a map that indicates street number data for key intersections. 7 . Is the City aware of any postal overlap zones within the City boundaries? Postal overlap zones are areas in the City that have a different City name for mail delivery and are served by a neighboring city's post office location. If yes, please indicate area on map provided in item 6 above. 8. If there are any unusual situations which the City would like MRC to look into, please attach a brief description. EXHIBIT B MUNICIPAL RESOURCE CONSULTANTS REPORT ON SALES TAX DATA PROCESSING FOR CITIES USING ELECTRONIC DATA TRANSFER BACKGROUND As most of you know, the receipt of QDR data on electronic media, (i.e. , tape or floppy diskette) does not make the processing of the data simplified or automatic. The fact still remains that the SBE's data files contain many non- standard fields of information that must be cleaned up in order to produce a clean set of sales . tax reports. The lack of same requires time-consuming effort, if done manually, to correct the problems and to do so by computer requires a carefully thought-out and sophisticated set of programs. Trying to work with the raw data, without cleanup, can be a frustrating and time consuming exercise, and prone to error. For example, comparing sales tax data to business tax data with a non-standard address format can result in businesses being contacted needlessly or businesses missed because the address is in an obscure part of the run, or the business name is not the same as used locally, etc. What follows is a- brief description of the problems with SBE sales tax data and Municipal Resource Consultants' (MRC's) approaches in dealing with the problems. STATE BOARD OF EQUALIZATION NON-STANDARDIZATION PROBLEMS The most troublesome data fields are the "address" field, the "business name" field, the "industrial classification" field, and the "reporting period" field. The SBE currently uses a single field to enter an address and the format varies by as many people as enter this data. For example, some people put the suite number first, then the rest of the address. Abbreviations of street names or shopping center names vary tremendously. MRC has seen the same street spelled or abbreviated as many as 31 different ways. This causes an address listing produced from the raw data provided by the SBE to be very difficult to use, especially when trying to use the data for comparison purposes, such as against the business tax printouts. - 2 - Street addresses for a particular street can be anywhere in a run, depending on the first character in the address field. For example, if 1234 Apple Street, Suite 4 is entered by the SBE as Suite 4, Apple St. , the address -will be in the "S" street area of the run. This can be frustrating and time consuming when trying to use the data. The business name is also non-standard, and it requires a constant effort to maintain this data. Many businesses are entered by the SBE using the owner's name or the parent corporation name. Therefore, many firms cannot be recognized until the local name is identified, and entered in the system. The industrial classification field is the field containing the business codes that are assigned by the SBE to classify businesses by type. There is a great deal of inconsistency in the assignment of business codes by the SBE. A good example was Gemco. The SBE classified this business as a code 34, supermarket, because the company was owned by Lucky Markets and used their account number. When Gemco closed, the many cities that had Gemco locations in their cities experienced a large decline in sales tax output from this category. Clearly, Gemco should have been classified as a department store, code 7, and the misclassification by the SBE plays havoc in trying to apply or analyze economic factors for projection or analysis of sales tax trends. The reporting period field is located on the QDR's and is used to identify which' period a payment belongs to, i.e. , which month or quarter. Some of the complicating factors are as follows. The codes for those taxpayers that pay monthly are 51 through 62, with 51 being January through 62, which is December. The same numbers are repeated each year and if a late payment comes through from an audit adjustment, for example, and is coded "51", it may be for a prior year. The computer program must be able to distinguish monthly payments by the year to which it belongs. Codes assigned to businesses that report quarterly are fairly straight forward and are designated by a code number that uses first the quarter's number and second the last number for a particular year. For example, the first quarter of 1988 is coded "18" . There are a number of other codes however, that range from "FT" to codes for annual reporting businesses and sometimes the reporting period field is blank. The computer must be able to deal with all of these quirks if the data is to be processed smoothly. Otherwise, a great deal of time will be spent on tracking down the exceptions manually. - 3 - One of the other significant problems is identification of the closed status of a particular business. There is no code sent by the SBE to identify a closed business. Identification of a closed business requires a special process to be performed. The same is true for any other changes to the master file data, such as a new owner, new address, etc. None of these changes are flagged by the SBE. The payment data will still be aggregated by master account number for businesses with multiple locations in the City, without identifying the individual amounts for each sub-number location. This will continue to present a problem to those cities wishing to break out geo-code areas within the City to track areas such as downtown, redevelopment project areas, shopping centers, etc. With only a grand total for sales tax for an individual firm that may be located in more than one geo-area, some method will have to be devised to break out separate amounts for each location. MUNICIPAL RESOURCE CONSULTANTS NON-STANDARD DATA FILE AND OTHER PROBLEM SOLUTIONS Over the years, MRC has developed a highly sophisticated logic-based system to deal with the non-standard address field. Even with this powerful tool, there are still some address formats that defy logic, and must be cleaned up manually. Computer-generated exception reports streamline this manual process. The business name field is a constant maintenance problem which will grow now that all accounts can be included in the STARS system. Currently some of this cleanup is processed on computer by MRC-developed logic-based routines. The industrial classification is also a manual cleanup process but does contain one complication. Changes in the codes assigned by the SBE for more proper classification still have to be matched up to payment data when researching an account and a secondary set of codes needs to be maintained to cross reference any code changes. MRC has also computerized routines to place payments in the proper reporting periods and flag unusual items such as FT's. MRC has also developed computerized routines to identify closed businesses and to flag other changes that occur in the master file for each sales tax account. Using these routines, the latest status of an account is included in the STARS reports. - 4 - Geo-code area reporting is presently being enhanced to provide 100% reporting for all firms located in these areas. The STARS system presently can break out amounts for firms with --- locations in each area on a standard percentage basis or on a customized percentage basis. The standard percentage is the total sales tax for a firm divided by the number of sub- numbers. The customized percentage allows whatever percent breakout between sub-numbers that is desired. Neither method is a perfect substitute for having the actual figures for each sub-number each quarter. This breakout may come in a future enhancement by the SBE. The present methodology does provide a separate figure for each location, and for most businesses it provides a good approximation of the sales tax generated by each location. Many of the computerized routines are also complemented by MRC efforts to identify missing data through its field work. For example, missing suite numbers, corner locations, local business names, misclassified business codes, etc. The result is a clean set of sales tax data which can be utilized by the City for a variety of purposes. This data can be transferred to the City, if desired, on 9-track tape or floppy diskette.