Transcript Rodda, Issacs, Rogers & Bhargava

NCVTS
County Financial Reports
September 26, 2013
Objectives
• Gain information regarding the following NCVTS Reports:
–
–
–
–
County Finance Report
County Reconciliation Report
Net Distribution Report
Pending Refund Report
NCVTS Reports
Background
Reports Background
• General Rules for NCVTS Reports
– Financial reports should include vehicle values, taxes and
interest paid by jurisdiction.
– The financial report will initially be provided by month of
collection. A subsequent version will provide collections by
registration month. Both reports will include a year-to-date
summary page.
– DOT will show gross collections, billing, credit card and debit
card costs, and net revenue.
• Costs will be extracted.
Reports Background
• General Rules for NCVTS Reports
– The levy consists of “bills for registrations/property taxes actually paid
in full through the renewal/issuance process or created through the
LRP process.
– 5% first month interest remains in effect after July 1, 2013.
– DOT Fiscal will provide a monthly Remittance Advice showing:
• Overall collections for your county
• Billing and collection costs
• Credit card and debit card costs
• Net collections after expenses are deducted
County Finance Report
County Finance Report
• Primary purpose is balancing with DOT Remittance Advice
• Secondary role is cost allocation
• Obtained from VTS on a monthly basis
• May be imported as a .csv or .xls file or keyed manually into
your financial system
• County or municipal finance personnel should contact the
County Assessor for direct access to this report
County Finance Report
• Report shows financial data in 13 tables
– One table for each month of the Fiscal year, starting in July and
ending with June
– One final table giving a “Year to Date” summary.
– The amounts in each table are based on payments made in that
Fiscal month.
– Each table breaks out financial data by Tax Jurisdiction.
County Finance Report
• The financial data for each table is divided into four areas:
– Billing Information – provides data for Levies (as originally billed or
adjusted prior to payment) and number of vehicles as well as LRP
(Limited Registration Plates)
– Collections – shows amounts actually paid (after adjustments, if any)
• This is the levy
– Credit /Debit Card Cost Allocation – shows payment data made by
Credit or Debit card
– Total Collected and Total Uncollected – shows data about amounts
paid and amounts unpaid (LRP)
County Finance Report
Billing Information
Jurisdiction
CB1
CH1
KB1
NH1
NH2
WB1
WM1
Totals
Nbr Vehicles
69
0
38
1,918
883
48
880
Renewal/LRP
Renew/Issue Levy
/Issue Net Values
$467,622
$1,098.90
$424,330
$0.00
$373,930
$977.83
$16,751,835
$92,825.42
$7,454,207
$5,889.06
$431,559
$574.05
$8,024,517
$39,892.97
$141,258.23
LRP Levy
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
Net Levy
$1,098.90
$0.00
$977.83
$92,825.42
$5,889.06
$574.05
$39,892.97
$141,258.23
• Valuations and Levy
• The number of vehicles with renewals, issuances or LRPs.
• The net value of registered vehicles (after adjustments made before
payment), issuances and LRPs
• The taxes and fees billed/collected through renewals, issuances and LRPs.
• The taxes billed on vehicles issued an LRP
County Finance Report
• Collections
– The total value of taxes paid
through renewals and issuances
– The total value of taxes collected
through the LRP process
– The aggregate collection rate for
LRP and Renewal collections
Collections
Renew/Issue
Collections
LRP
Collections
Total
Collections
%
Collected
$1,098.90
$0.00
$0.00
$0.00
$977.83
$0.00
$92,335.68
$0.00
$92,335.68
99.47%
$5,874.41
$0.00
$5,874.41
99.75%
$574.05
$0.00
$39,558.59
$0.00
$140,419.46
$1,098.90 100.00%
$0.00
0.00%
$977.83 100.00%
$574.05 100.00%
$39,558.59
99.16%
$0.00 $140,419.46
99.41%
County Finance Report
• Cost Allocation
• 3 Categories of costs
– Billing Costs
– Credit Card Costs
– Debit Card Costs (future)
• Counties should allocate
the lump sum costs
across their taxing
jurisdictions
Credit/Debit Card Cost Allocation
Billing Cost Collections By
%
Credit Card
0.78%
0.00%
0.69%
65.75%
4.18%
0.40%
28.17%
99.97%
$332.24
$0.00
$316.64
$33,685.90
$2,324.74
$212.88
$13,444.61
$50,317.01
Cost %
0.66%
0.00%
0.62%
66.94%
4.62%
0.42%
26.71%
99.97%
Collection by
Debit Card
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
Cost %
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
County Finance Report
• Total Collections/Uncollected
– Provides
• Interest collected for each
jurisdiction
• Total collections (Levy +
Interest) per jurisdiction
• Total LRP uncollected
Total Collections/ Uncollected
Interest Collected
Total Collected
LRP Uncollected
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
$1,098.90
$0.00
$977.83
$92,335.68
$5,874.41
$574.05
$39,558.59
$140,419.46
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
County Finance Report
• Cost allocation percentages are based on the contribution of
each individual levy to the total of all levies.
– Counties/municipalities may choose other methods for cost
allocation
– Counties no longer have the statutory authority to charge
municipalities and other jurisdictions up to 1.5% of collections to
offset collection costs for vehicles billed under the Tag and Tax
Together program
County Finance Report
• DOT Fiscal will provide a monthly Remittance Report
showing:
– Overall collections for your county
– Billing and collection costs
– Credit card and debit card costs
– Net collections after expenses are deducted
County Finance Report
County Finance Report
County Finance Report
County Finance Report
County Reconciliation
Report
County Reconciliation Report
• This report provides detailed county payment and reversal data in
VTS for the purpose of reconciling against distribution reports
from STARS for a given date range.
– Originally intended for testing purposes
– Only looks at payments
• The parameters for this report are:
– Start date (inclusive)
– End date (inclusive)
County Reconciliation Report
County Reconciliation Report
Dataset: Reconciliation by “Year For” and Tax District
Pos
1
2
3
Field Name
Year_for
Tax_District
Vehicle_Taxes
Length
4
20
19
Type
int
Char
money
4
Vehicle_Fees
19
money
5
6
7
8
9
10
11
12
13
14
Taxes_and_Fees
Interest
Holds_Plus
Total_Payments
Reversed_Taxes
Reversed_Fees
Total_Reversed_Taxes_and_Fees
Reversed_Interest
Holds_Minus
Total_Reversals
19
19
19
19
19
19
19
19
19
19
money
money
money
money
money
money
money
money
money
money
15
Credit_Card
19
money
Description
Tax district – using county financial import data
Payments where Levy_type = tax, excluding reversals and
interest
Payments where levy type = vehicle fee, excludes reversals and
interest
Sum of Vehicle taxes and vehicle fees
All interest on vehicle fees and taxes, excluding reversals
Funds not assigned (usually seen as an overpayment)
Sum of Taxes and Fees plus interest plus Holds_plus
Reversals of levy type taxes, excludes reversals of interest
Reversals of levy type vehicle fees excludes interest
Sum of reversed vehicle fees and reversed vehicle taxes
All reversed interest
Reversed holds (reversal of overpayment refunds)
Sum of Total reversed taxes and fees plus reversed interest plus
Holds_minus
Payments associated to a credit card – includes all taxes, fees,
reversals, Holds Plus, Holds_minus and interest
County Reconciliation Report
Dataset: Reconciliation by “Year For” and Tax District cont.
Pos
Field Name
Length
Type
Description
16
Debit_Card
19
money
Sum_Vehicle_Taxes
Sum_Vehicle_Fees
Sum_Taxes_and_Fees
Sum_Interest
Sum_Holds_plus
19
19
19
19
19
money
money
money
money
money
Payments associated to a debit card – includes all taxes, fees,
reversals, Holds Plus, Holds_minus and interest
Sum of all Vehicle taxes in report
Sum of all Vehicle_Fees in report
Sum of all Taxes_and_Fees in report
Sum of all Interest in report
Sum of all Holds_plus in report
17
18
19
20
21
22
Sum_Total_Payments
19
money
Sum of all Total_Payments in report
23
Sum_Reversed_Taxes
19
money
Sum of all Reversed_Taxes in report
24
Sum_Reversed_Fees
19
money
Sum of all Reversed_Fees in report
25
Sum_Total_Reversed_Taxes_and_Fees
19
money
Sum of all Total_Reversed_Taxes_and_Fees in report
26
Sum_Reversed_Interest
19
money
Sum of all Reversed_Interest in report
27
Sum_Holds_Minus
19
money
Sum of all Holds_Minus in report
28
Sum_Total_Reversals
19
money
Sum of all Total_Reversals in report
29
Sum_Credit_Card
19
money
Sum of all Credit_Card in report
30
Sum_Debit_Card
19
money
Sum of all Debit_Card in report
Net Distribution Report
Net Distribution Report
• The Net Distribution Report is a modification of the Daily
Distribution Report and includes total levies collected by tax
district and net adjustments including releases and refunds.
Net Distribution Report
Net Distribution Report
• Recent updates:
– Levy column only includes transactions from STARS
• This will match County Finance Report and County Reconciliation Report
– Net Adjustment column added
• Includes changes to levy amounts due to adjustments and prorations that
occur after the payment is made
– Net Adjustment Interest column added
• Includes changes to interest amounts due to adjustments and prorations after
the payment is made
– Total column renamed to Net Levy
Pending Refund Report
Pending Refund Report
• This report shows prorated refund data by:
– Tax District
– Levy Type
• Vehicle fee – only prorated in the case of full release or bill voidance
• Tax
– Interest for a given time period
Pending Refund Report
• The parameters for the Report are:
– Fiscal Year – in the form “2013-14”.
– Time Period – a specific Month, Quarter or Year
– Refund Types:
• Proration
• Adjustment >=100
• Adjustment <100”
• Over payments are excluded from the report
– Refund Status:
• Approved
• Pending
Pending Refund Report
• This report pulls from three datasets:
– Dataset 1 shows a breakdown by tax district and levy type
– Dataset 2 is a summary of refunds by tax district (prorated)
– Dataset 3 is a list of the parameters used to filter the data and the run
date of the report.
Pending Refund Report
Pending Refund Report
• Dataset 1: Refunds w/ Tax District and Levy Types Prorated
Pos
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Field Name
Name
Adddress_1
Address_2
Address_3
Refund_Type
Bill_Num
Status
Transaction_Num
Refund_Description
Refund_Reason
Create_Date
Tax_District
Levy_Type
Refund_Amt
Length
100
50
50
50
20
10
20
10
200
20
10
20
10
14
Type
Char
Char
Char
Char
Char
Char
Char
int
Char
Char
Date
Char
Char
money
15
Subtotal_Refund_Amt
19
money
16
Total_Refund_Amt
19
money
Description
Tax payer name
Taxpayer Address
Additional Tax Payer address
Taxpayer City, state, zip
Proration or adjustment
Bill Base number
Pending or Authorizied
Refund Number
Generic description
Reason for refund
Date Refund created
Relevant tax district
Tax , Vehicle Fee, or Interest
Amount to be refunded by Tax district and Levy
Type
Amount to be refunded to a taxpayer in a single
refund transaction
Total amount of refunds in the report
Pending Refund Report
• Dataset 2: Summary of Refunds by Tax District
Pos
1
2
3
Field Name
Tax_District_Summary
District_Type_Summary
Refund_Amt_Summary
4
Sum_refund_Amt_Summary
Length
20
20
19
19
Type
Char
Char
money
money
Description
Tax district
District type – City, County,Fire,Special
Amount refunded to tax district
(prorated)
Total refund amount
• Dataset 3: Report Parameters
Pos
1
2
3
4
5
6
Field Name
County
Refund_Time_Period
Refund_Fiscal_Year
Refund_Types
Refund_Status
Report_Run_Date
Length
50
15
11
100
100
21
Type
char
Char
Char
Char
Char
Datetime
Description
Name of County report is for
Name of Month, Quarter or Year
Fiscal year of data
List of Refund Types in report
List of statuses contained in report
Date and Time that the Report was created –
mm/dd/yyyy hr:min
Report Matching
Report Matching
The following three reports should match with each other
(some adjustments may be necessary for reversals)
County
Reconciliation
County
Finance
Net
Distribution
(STARS
Payment)
Report Matching
The following two reports should match with each other
Net
Distribution
(Non STARS
Payment)
Pending
Refund