GISdevelopment.net ---> GITA 2001 ---> How They Did It - And What's Next

Using GIS for tax asset allocation

Robert A. Popoff
GeoForce
A subsidiary of Lost Time Control West, Inc.
12446 Willingdon Road
Huntersville, NC 28078


Paper

Definitions
Project Definitions
  • Tiger
    The Project Name of the PSNC Energy GIS Project.
  • FLAME
    The Project Name of the PSNC Energy Peoplesoft Project.
Tiger Definitions
  • Engineering
    Used in this paper to represent the department that maintained the manual paper mapping system, and now maintains the TIGER system.
  • Tax District
    A TIGER polygon (Area) feature, which represents a specific Tax District usually within a county.
  • Transmission Main
    A linear feature, which represents a piece of pipe, that transmits high-pressure natural gas to areas where it is regulated and distributed to Distribution Mains.
  • Distribution Main
    A linear feature, which represents a piece of pipe, that transmits lower pressure natural gas to Service Mains.
  • Service Mains
    A linear feature, which represents a piece of pipe, that takes natural gas from the distribution main to the customer's meter.
  • Meters
    A point feature, which represents the regulator and monitoring device, that takes natural gas from the service main to the customer's home or business.
  • Station
    A point feature, which represents the regulating device, that distributes gas from Transmission Mains to Distribution Mains.
  • Right of Way
    A polygon feature, which represents the area of land, that is bought or leased from a landowner who agrees to allow pipe (usually Transmission Pipe) to cross their land.
  • Building
    A polygon feature that represents a structure owned by PSNC Energy.
Flame Definitions
  • Plant Accounting
    The division of Accounting that is responsible for maintaining the assets costs for PSNC Energy.
  • Projects
    The term used by Plant Accounting which group costs together logically based on when assets were installed and for what purpose.
  • Project File and Numbers
    A sequential number which links a Plant Accounting Project with the assets installed by Engineering and maintained by TIGER
Background
For fiscal year 1999, PSNC Energy decided to use TIGER to allocate taxable asset costs across tax districts in its franchise territory, for the purpose of reporting taxable assets to the State of North Carolina. Replacing the old system of manually calculating new installation, retirements, and tax district boundary changes, a TIGER Application was implemented to both streamline and offer a more accurate means of calculating these costs. A project team was developed to coordinate the implementation of the new system. While this application was designed to reduce the number of hours the taxable asset reports for the State of North Carolina took to prepare, both FLAME and TIGER benefited in some surprising ways.

Old System
The old system consisted of communicating throughout the years on all new projects files from Engineering, determining the total cost for the project, and communicating this to Plant Accounting by reporting such items as size, type, length, and date of installation of pipe, and determining these amounts within each tax district. In addition, other departments reported assets to Plant Accounting such as station costs by project file, building and land costs by tax district, and inventory by address. Plant Accounting also maintained a running total of costs for such assets as meters, service mains, and right of ways, not knowing what tax district they actually existed in.

When new projects replaced old projects, it was Engineering's responsibility to report to Plant Accounting the size, type, date installed, and length of pipe that was retired for each tax district. Plant Accounting would then try and update FLAME based on these numbers. When new tax districts were created or boundaries were changed, Engineering would have to determine lengths of pipe by size, type, and date installed that needed to be moved from one tax district to another. This process went on all year, year after year. Then, by February of the following year, the costs would need to be added up based on new tax district boundaries and reported to the state. If there were large changes in asset costs for a specific tax district, explanations in the form of legal documents would need to be submitted to the State of North Carolina. This tedious and stressful process, which was performed by a number of departments throughout the year(s), often was put off until the last moment, and breakdown in communications often occurred.

Recommendations
It was agreed upon that FLAME and TIGER would supply the necessary dataflows into a Microsoft Access Database in order to prepare the annual reports. The Figure below shows what data was used from each system. There are five defined methods of allocating costs by tax district. Based on the geographic information maintained in TIGER and the level of detail costs maintained in FLAME, one of the five methods was selected to allocate costs. Following is an example of each of the five methods of allocation

Method 1. Allocation based on a single point feature in TIGER
In the following illustration, FLAME reported the project cost for project number A100 was $40,000.00. TIGER was able to locate a Station which has the project number as an attribute and determined it to be in Tax District 100. Thus, the $40,000.00 was allocated to Tax District 100. This was the simplest and most straightforward allocation method.



Method 2. Allocation based on a Tax District
For certain features that did not exist in TIGER, FLAME was able to provide the cost and the tax district and this data was loaded into the database. Many buildings and land fit into this allocation method.

Method 3. Allocation based on a specific Linear Feature
Using method 3, a linear feature such as a Transmission Pipe crossed many tax districts. FLAME was able to provide a total cost for the installation of the Transmission Main based on the Transmission Line Number. TIGER was able to determine what percentage of the pipe length existed in each tax district. The total cost from FLAME was then multiplied times the percentage of pipe length in each tax district to determine the allocation cost.



Method 4. Allocation based on many linear features
In the next example, many linear features were used to allocate costs based on a category. FLAME was able to provide the total cost for Distribution Main based on a combination category of size, type (material), and year installed. TIGER then determined the total length of pipe for each category of size, type, and year. It then determined the percentage that existed in each tax district. The total cost for each category of Distribution Main was then multiplied by each percentage of that category in each tax district to determine the allocation cost.

Method 5. Allocation based on other entity costs
In many categories of costs, there were no specific TIGER features, nor any breakdown of the costs in FLAME. For instance, right of way costs were simply maintained as a single running total. Based on other entity total costs as a percentage within each tax district, the total cost was allocated to each tax district. Thus, the total cost for right of ways was allocated based on Transmission Main Costs using the following calculations:
  1. Determine the total feet of each Transmission Line in each tax district
  2. Divide the total feet for each Transmission Line in each tax district resulting in the % of feet for each Transmission Line in each tax district
  3. Multiply the % of feet for each transmission line in each tax district times the cost of the each Transmission Line resulting in the cost of each Transmission Line in each tax district. This was the result of allocation method 2.
  4. Sum the Transmission Line costs for each tax district resulting in the total cost for Transmission Main in each tax district.
  5. Sum the total costs of each tax district resulting in the Total Transmission Main cost.
  6. Divide the Total Transmission Main costs into the total cost for Transmission Main in each tax district resulting in a ratio.
  7. Result is the % of Transmission Main costs in each tax district.
Now, multiply the total costs for right of ways by each tax district % of Transmission Main costs to allocate the right of way costs.

A similar ratio was created for Distribution Main costs for each tax district. These ratios were used on the following entities:



Surprises
As stated in this paper's background, a number of efficiencies were expected. In addition, both the FLAME and TIGER systems also saw improvements. Plus the asset allocation application proved to more than pay for its development.

Table 1. Project Costs in FLAME
Project Number in FLAME Cost Station Number in TIGER
E10355 $43,125.54 547891
C3476 $128,635.82 Missing
W12115 $178,335.23 Missing
W23546 $45,129.20 731298
E2001 $75,836.63 812794
E3006 $93,451.77 Missing

Using Allocation Method 1, all Project Costs maintained in FLAME needed to be linked to a Station Feature in TIGER, on a one-to-one basis. Using Table 1 as an example, initially FLAME was concerned that some of their project numbers did not have a corresponding station in TIGER. As Engineering researched the missing stations in TIGER, it became apparent that over the years, a communication breakdown occurred when certain projects were retired. Nearly $680,000.00 of projects over the years had been retired and not communicated to Plant Accounting, some retired as far back as 1968. Thus FLAME was able to retire these costs in their system and save PSNC Energy reoccurring tax fees for years to come.

In the preparation of the tax report, a number of tax districts did not appear on the report. Again, as Engineering researched the issue in TIGER, Engineering realized certain tax district polygons were missing in TIGER. This was an additional problem because as costs were allocated using methods 3-5, certain tax districts were appeared to not have any assets to report, while others were reporting asset values too high. TIGER reviewed what tax districts were missing and created polygon features for them. Thus, the TIGER system improved its accuracy due to this application as well.

Since TIGER was now maintaining all the pipe lengths and could determine what existed in each tax district, Plant Accounting was able to reduce the number of hours they devoted to the manual process of creating the tax asset report by 900 man-hours. In addition, the number of hours Engineering was devoting to calculating the new and retired projects and communicating these figures to Plant Accounting, was reduced by over 300 man-hours. The increase in the number of hours used to prepare the reports using the new application offset these savings by 400 man-hours, resulting in a net reduction of 800 man-hours.

What's Next?
Both the FLAME and TIGER systems agree long term that more accuracy can be realized as more geographic data is created in TIGER. The following steps are planned:
  • New objects for land, buildings, and right of ways are expected which will eliminate the need for FLAME to maintain the associated tax district.
  • TIGER has undertaken the process of entering and maintaining services and meters during the coming 24 months, to eliminate the use of allocation method 5.
  • Because links now exist between TIGER and FLAME, though they are only logical at this time, Engineering no longer reports to Plant Accounting on the installation and retirement of projects and pipe lengths. Plant Accounting simply maintains the costs on the FLAME system.
  • The preparation of the 2001's asset allocation report is expected to use less than 100 total man-hours to prepare. Tax districts are entered and altered throughout the year in TIGER, costs continue to be maintained in FLAME, and all that needs to be entered into the MS Access Database are the new Transmission Main Costs, Distribution Main costs for each size and type of pipe for year 2000, and any new project costs. Retirements will be deleted from the FLAME system using the Table 1 method above.

    Long term, the asset allocation application needs to be written with a Visual Basic front end, dynamically interfacing both TIGER and FLAME, but that will take place once all objects are entered and maintained in TIGER, and PSNC Energy decides what department's budget will pay for it.
© GISdevelopment.net. All rights reserved.