HomeMy WebLinkAboutEx 05 06 and 07 Email TRS to J Hawn Audit Method ExcelPrelimRenton.pdfNate Malone
From:TRS <crisp@trs-integrity.com>
Sent:Friday, June 30, 2017 3:41 PM
To:Jan Hawn
Subject:TracFone Schedules
Attachments:ExcelPrelimRenton Tracfone.xls; Renton TracFone Methodology.doc
Hi Jan,
Hope you are doing well and enjoying the sunshine.
Here is a copy of the TracFone tax schedules and the methodology of how we did the audit.
Below is the explanation that will help you navigate how, what and why we have done them this way:
So much of this audit had to be estimated; the information we needed was not available, so we estimated the
data we needed by using data they actually had collected. If you have questions on the schedules, since they are
much more complicated than typical ones, the Methodology sheet should help.
To read the schedules:
1. Notice that the Original Tax Reported Sheet is blank. No tax was reported. That part is easy!
2. The Unreported Revenue Sheet has columns defined on the Methodology sheet, but to simplify:
-The Non -Direct Sales column D is calculated by multiplying all the Direct Sales (sum of next 4 columns)
times proportions given to us by TracFone.
-Page Plus Air and Page Plus don't show up until 2014 so those columns are empty.
-The actual Billing Platform Sales and APP Direct Sales values that were given to us from TracFone are
listed in columns U and V.
-Columns U and V are read into columns D and H and reduced by the factors explained on the chart that is
found in columns J through Z.
3. The rest of how the charts work you are familiar with.
Also, this audit stops in May of 2013, and TracFone wanted us to stay within the original audit period. The
revenues have been increasing greatly over the years, so if we open another audit to recover the rest of it, the
second audit would probably be about the same amount as this one if not more.
Also, have you had a chance to review our proposal of conducting an audit on
do believe that this audit would be a very good audit for the City of Renton.
Let us know if you have any questions.
Thanks Jan.
Have a great weekend.
Mike
1
000634
Nate Malone
From:TRS <crisp@trs-integrity.com>
Sent:Friday, June 30, 2017 3:41 PM
To:Jan Hawn
Subject:TracFone Schedules
Attachments:ExcelPrelimRenton Tracfone.xls; Renton TracFone Methodology.doc
Hi Jan,
Hope you are doing well and enjoying the sunshine.
Here is a copy of the TracFone tax schedules and the methodology of how we did the audit.
Below is the explanation that will help you navigate how, what and why we have done them this way:
So much of this audit had to be estimated; the information we needed was not available, so we estimated the
data we needed by using data they actually had collected. If you have questions on the schedules, since they are
much more complicated than typical ones, the Methodology sheet should help.
To read the schedules:
1. Notice that the Original Tax Reported Sheet is blank. No tax was reported. That part is easy!
2. The Unreported Revenue Sheet has columns defined on the Methodology sheet, but to simplify:
-The Non -Direct Sales column D is calculated by multiplying all the Direct Sales (sum of next 4 columns)
times proportions given to us by TracFone.
-Page Plus Air and Page Plus don't show up until 2014 so those columns are empty.
-The actual Billing Platform Sales and APP Direct Sales values that were given to us from TracFone are
listed in columns U and V.
-Columns U and V are read into columns D and H and reduced by the factors explained on the chart that is
found in columns J through Z.
3. The rest of how the charts work you are familiar with.
Also, this audit stops in May of 2013, and TracFone wanted us to stay within the original audit period. The
revenues have been increasing greatly over the years, so if we open another audit to recover the rest of it, the
second audit would probably be about the same amount as this one if not more.
Also, have you had a chance to review our proposal of conducting an audit on
do believe that this audit would be a very good audit for the City of Renton.
Let us know if you have any questions.
Thanks Jan.
Have a great weekend.
Mike
1
000634
This email and any files attached to it are the property of Tax Recovery Services, LLC. The contents may be confidential and
proprietary, and any use or distribution of the information contained herein may be prohibited by law. If you are not a named
recipient, or if you have received this email in error, please notify the person who sent it, and immediately delete it.
2
000635
This email and any files attached to it are the property of Tax Recovery Services, LLC. The contents may be confidential and
proprietary, and any use or distribution of the information contained herein may be prohibited by law. If you are not a named
recipient, or if you have received this email in error, please notify the person who sent it, and immediately delete it.
2
000635
TracFone Audit Methodology
Methodology
(Note: For definitions of terms used, see Definition section below)
Usual data is not collected by TracFone:
We have found that typically, for prepaid telephone UUT taxes, a company collects usage
information for the entire state, and applies a percentage ratio found by comparing the number of
customers from a specific city to Washington State usage in order to calculate the total owed to the
specific city. But TracFone only collects information on sales, not on usage, and they don't track
the number of customers they have in each municipality.
Why sales and not usage is used in the estimate:
While it is true that taxation of this prepaid phone activity is properly owed on usage, not sales,
usage figures are not available from TracFone, so an estimate must be made via sales, which
approximates usage. We would like to stress here that taxes are not being levied on sales, but rather
sales are being used as a method to estimate usage.
How the taxable usage was estimated for direct sales (Billing Platform Sales and APP Sales):
TracFone doesn't collect sales information for particular cities, either. What they do collect is sales
information for zip codes. So our first step was to collect and sum up the direct -sales information
for the city's zip codes, then we reduced the zip code sales sum by a percentage ratio found by
comparing population of the city to the sum of the zip codes populations. Direct sales were further
reduced by percentages that removed data sales and interstate and international calls from the total,
resulting in amounts that would represent a good estimate of direct -sales -related taxable utility
usage.
However, this only gave us an idea of city taxable revenues for direct sales to TracFone, and the
larger proportion of TracFone customers buy indirectly from TracFone, via outside carriers such as
Walmart.
Estimation of non -direct sales (sold through outside carriers):
In order to estimate the amount of non -direct sales (and thus usage) that the company collects from
city customers, it was necessary for us to turn to information from TracFone company -wide sales
figures (more localized data was unavailable). We checked that the data for company -wide direct
and non -direct sales included basically the same types of items, and came up with a ratio to
estimate how much direct vs. non -direct sales were occurring (and thereby estimate usage from the
non -direct sales).
Summing direct sales (these numbers were figured using the direct -sales amounts, which were
already reduced by percentages that removed data, interstate and international usage), the non-
direct/direct revenues ratio was then applied to produce the taxable non -direct sales usage estimate.
Finding total taxable revenue:
Total taxable revenue was found 1) by summing up direct sales from internet sales and billing
platform sales (reduced by percentages that removed data, interstate and international usage), and
000636
TracFone Audit Methodology
Methodology
(Note: For definitions of terms used, see Definition section below)
Usual data is not collected by TracFone:
We have found that typically, for prepaid telephone UUT taxes, a company collects usage
information for the entire state, and applies a percentage ratio found by comparing the number of
customers from a specific city to Washington State usage in order to calculate the total owed to the
specific city. But TracFone only collects information on sales, not on usage, and they don't track
the number of customers they have in each municipality.
Why sales and not usage is used in the estimate:
While it is true that taxation of this prepaid phone activity is properly owed on usage, not sales,
usage figures are not available from TracFone, so an estimate must be made via sales, which
approximates usage. We would like to stress here that taxes are not being levied on sales, but rather
sales are being used as a method to estimate usage.
How the taxable usage was estimated for direct sales (Billing Platform Sales and APP Sales):
TracFone doesn't collect sales information for particular cities, either. What they do collect is sales
information for zip codes. So our first step was to collect and sum up the direct -sales information
for the city's zip codes, then we reduced the zip code sales sum by a percentage ratio found by
comparing population of the city to the sum of the zip codes populations. Direct sales were further
reduced by percentages that removed data sales and interstate and international calls from the total,
resulting in amounts that would represent a good estimate of direct -sales -related taxable utility
usage.
However, this only gave us an idea of city taxable revenues for direct sales to TracFone, and the
larger proportion of TracFone customers buy indirectly from TracFone, via outside carriers such as
Walmart.
Estimation of non -direct sales (sold through outside carriers):
In order to estimate the amount of non -direct sales (and thus usage) that the company collects from
city customers, it was necessary for us to turn to information from TracFone company -wide sales
figures (more localized data was unavailable). We checked that the data for company -wide direct
and non -direct sales included basically the same types of items, and came up with a ratio to
estimate how much direct vs. non -direct sales were occurring (and thereby estimate usage from the
non -direct sales).
Summing direct sales (these numbers were figured using the direct -sales amounts, which were
already reduced by percentages that removed data, interstate and international usage), the non-
direct/direct revenues ratio was then applied to produce the taxable non -direct sales usage estimate.
Finding total taxable revenue:
Total taxable revenue was found 1) by summing up direct sales from internet sales and billing
platform sales (reduced by percentages that removed data, interstate and international usage), and
000636
2) by adding in non -direct sales found by summing taxable direct usage estimates and applying the
percentage of company -wide non -direct vs. direct sales.
Finding population proportion:
Population percentage is the percentage which compares city population and zip code
populations, which is used to reduce the sales figures for the zip codes down to approximate
city -only sales. City populations are available yearly, but we needed to use census years in order
to get the zip code populations. Because of this, we used the most recent census, 2010, to
calculate the population percentages.
While this was reasonably accurate post -annexation, the large 2010 Renton annexation would
impact the proportion, overestimating the proportion for years previous to the annexation.
So for years before 2010, we calculated zip code change for one year by using change since
2000 divided by 10, in order to get a zip population sum for 2009 to compare with the 2009 city
population. This new proportion was then applied previous to 2010.
Note: For the years previous to given information, estimates were used as follows:
-For data, percent values were increasing drastically every year, increasing nearly 36% in four
years, starting low in 2010 at 3.96%. Because of this, data percentage was presumed to be zero
previous to 2010.
-For intrastate percentage, the percentages were rather constant throughout the audit, showing a
slight downward trend, dropping only 4 percentage points in 4 years, so the earliest year value
available of 84.07% was used previous to 2009.
Available information
Zip code specific sales data for direct sales only:
APP sales by zip code for each month
Billing Platform sales by zip code for each month
Accessory sales by zip code for each month
Company -wide data showing non -direct and direct sales amounts and proportions
Percent of sales representing data for years 2010 to 2014
Percent of sales representing intra-state calling from years 2009 to 2014
Missing information
Usage data at any level
Local data showing non -direct sales or usage
City -specific sales or usage figures
Definitions
Direct sales:
The Billing Platform, Internet Sales, and Telephone Sales shown on the excel format reports
000637
2) by adding in non -direct sales found by summing taxable direct usage estimates and applying the
percentage of company -wide non -direct vs. direct sales.
Finding population proportion:
Population percentage is the percentage which compares city population and zip code
populations, which is used to reduce the sales figures for the zip codes down to approximate
city -only sales. City populations are available yearly, but we needed to use census years in order
to get the zip code populations. Because of this, we used the most recent census, 2010, to
calculate the population percentages.
While this was reasonably accurate post -annexation, the large 2010 Renton annexation would
impact the proportion, overestimating the proportion for years previous to the annexation.
So for years before 2010, we calculated zip code change for one year by using change since
2000 divided by 10, in order to get a zip population sum for 2009 to compare with the 2009 city
population. This new proportion was then applied previous to 2010.
Note: For the years previous to given information, estimates were used as follows:
-For data, percent values were increasing drastically every year, increasing nearly 36% in four
years, starting low in 2010 at 3.96%. Because of this, data percentage was presumed to be zero
previous to 2010.
-For intrastate percentage, the percentages were rather constant throughout the audit, showing a
slight downward trend, dropping only 4 percentage points in 4 years, so the earliest year value
available of 84.07% was used previous to 2009.
Available information
Zip code specific sales data for direct sales only:
APP sales by zip code for each month
Billing Platform sales by zip code for each month
Accessory sales by zip code for each month
Company -wide data showing non -direct and direct sales amounts and proportions
Percent of sales representing data for years 2010 to 2014
Percent of sales representing intra-state calling from years 2009 to 2014
Missing information
Usage data at any level
Local data showing non -direct sales or usage
City -specific sales or usage figures
Definitions
Direct sales:
The Billing Platform, Internet Sales, and Telephone Sales shown on the excel format reports
000637
submitted to TRS, also called by TracFone "retail sales" amounts. These amounts are from
customer purchases made via the internet (i.e., the TracFone's website) and over the phone. Refills
of airtime that a customer can purchase via the internet (i.e., the TracFone's website), and over the
phone are included in the Billing Platform and Internet Sales retail reports.
Non -Direct sales:
TracFone refers to these sales as "wholesale," as they are sales made through outside carriers such
as Walmart. On the company -wide data chart TRS received from TracFone, these were referred to
as "net airtime sales."
Internet sales, APP sales:
The Alternative Payment Plan ("APP") is TracFone's credit card transaction interface for retail
sales. Under the APP, customers engage in one-time credit card based transactions via the internet
or phone. APP based customer transactions are consummated predominantly via the internet.
Furthermore, any TracFone plan program which is not available to be set up for an automated-
recurring charge gets processed through APP. Note: Although internet sales are not taxable, for
this audit, internet sales are simply used to estimate usage, which is taxable.
Billing Platform:
The Billing Platform ("BP") is a supplement to the Alternative Payment Plan ("APP") to provide a
system for automated -recurring charges. Customers can make a one-time purchase or enroll into
programs available for automated or recurring charges, most of which include airtime on a
monthly (30 day) basis.
Accessories:
Mobile phone accessories include any hardware or software that is not integral to the operation of a
mobile phone as designed by the manufacturer. Note: Accessories are not taxable under the city
utility tax laws, and were not included in taxable amounts for this audit.
Data usage:
The transmission of data via cellphone. Also called "mobile data" and a separate service from
voice calling, cellular data is used for e-mail, Web pages, media and app downloads and software
updates. Text messaging (SMS) is also a different service. Note: Data usage is not taxable under
the city utility tax laws, and was removed from taxable amounts for this audit.
Example of estimating Non -direct Sales from Direct Sales using percent data from TracFone 2010:
Proportion from TracFone: Non -direct Sales = .83 x all sales ;Direct Sales = .17 x all sales
Thus:
Therefore:
Non -direct Sales = .83 x all sales ;Non -direct Sales = .83 ;
Direct Sales .17 x all sales Direct Sales .17
Non -direct Sales = Direct Sales x .83/.17
000638
submitted to TRS, also called by TracFone "retail sales" amounts. These amounts are from
customer purchases made via the internet (i.e., the TracFone's website) and over the phone. Refills
of airtime that a customer can purchase via the internet (i.e., the TracFone's website), and over the
phone are included in the Billing Platform and Internet Sales retail reports.
Non -Direct sales:
TracFone refers to these sales as "wholesale," as they are sales made through outside carriers such
as Walmart. On the company -wide data chart TRS received from TracFone, these were referred to
as "net airtime sales."
Internet sales, APP sales:
The Alternative Payment Plan ("APP") is TracFone's credit card transaction interface for retail
sales. Under the APP, customers engage in one-time credit card based transactions via the internet
or phone. APP based customer transactions are consummated predominantly via the internet.
Furthermore, any TracFone plan program which is not available to be set up for an automated-
recurring charge gets processed through APP. Note: Although internet sales are not taxable, for
this audit, internet sales are simply used to estimate usage, which is taxable.
Billing Platform:
The Billing Platform ("BP") is a supplement to the Alternative Payment Plan ("APP") to provide a
system for automated -recurring charges. Customers can make a one-time purchase or enroll into
programs available for automated or recurring charges, most of which include airtime on a
monthly (30 day) basis.
Accessories:
Mobile phone accessories include any hardware or software that is not integral to the operation of a
mobile phone as designed by the manufacturer. Note: Accessories are not taxable under the city
utility tax laws, and were not included in taxable amounts for this audit.
Data usage:
The transmission of data via cellphone. Also called "mobile data" and a separate service from
voice calling, cellular data is used for e-mail, Web pages, media and app downloads and software
updates. Text messaging (SMS) is also a different service. Note: Data usage is not taxable under
the city utility tax laws, and was removed from taxable amounts for this audit.
Example of estimating Non -direct Sales from Direct Sales using percent data from TracFone 2010:
Proportion from TracFone: Non -direct Sales = .83 x all sales ;Direct Sales = .17 x all sales
Thus:
Therefore:
Non -direct Sales = .83 x all sales ;Non -direct Sales = .83 ;
Direct Sales .17 x all sales Direct Sales .17
Non -direct Sales = Direct Sales x .83/.17
000638
Period of Adjustments:
Firm Name:
Business Address:
City of Renton, Washington
Department of Finance
Field Auditor's Report
SUMMARY SHEET
January 01, 2007 through May 31, 2013
Tracfone Wireless
Tax Department
9700 NW 112th Avenue
Miami, FL 33178
ATTN:Nicholas Ford, Sr. Tax Manager
Registration
Classes 2007 2008 2009 2010 2011 2012 2013 2014 2015 TOTAL
UTILITY TAX 0
Rounding
Corrected Tax 0
Reported Tax 0
DEBIT/(CRE 0 0 0 0 0 0 0 0 0 0 0
Date:Audit submitted by: Michael J. Crisp
REMARKS:YEARLY
YEAR INTEREST DR/(CR)
2007
2008
2009
2010
2011
2012
2013
0
Total Interest Dr(Cr):
Total Tax (Shown Above) DR/(CR)
Total Penalty (Per Calc Sheet) Dr./(Cr,)
Total Due
Extensions Dr. Noted Date
Reviewer Cr. Noted Date
Code Classif.Hours
TOTALS
0
0
0
000639
Period of Adjustments:
Firm Name:
Business Address:
City of Renton, Washington
Department of Finance
Field Auditor's Report
SUMMARY SHEET
January 01, 2007 through May 31, 2013
Tracfone Wireless
Tax Department
9700 NW 112th Avenue
Miami, FL 33178
ATTN:Nicholas Ford, Sr. Tax Manager
Registration
Classes 2007 2008 2009 2010 2011 2012 2013 2014 2015 TOTAL
UTILITY TAX 0
Rounding
Corrected Tax 0
Reported Tax 0
DEBIT/(CRE 0 0 0 0 0 0 0 0 0 0 0
Date:Audit submitted by: Michael J. Crisp
REMARKS:YEARLY
YEAR INTEREST DR/(CR)
2007
2008
2009
2010
2011
2012
2013
0
Total Interest Dr(Cr):
Total Tax (Shown Above) DR/(CR)
Total Penalty (Per Calc Sheet) Dr./(Cr,)
Total Due
Extensions Dr. Noted Date
Reviewer Cr. Noted Date
Code Classif.Hours
TOTALS
0
0
0
000639
Ij
;
I
ti
is
I
I
:
I
II
is
I
Ij
;
I
ti
is
I
I
:
I
II
is
I
flinliMIIIMIME:(111::1 1101111111.811111,tailinn.....i
.,.iPiioqiMirtlin
*}; Inl;,!i;!Mill!i;',1!liniiii:.;iliM11,;i1Tiiiii!iiiiiiiill;.a
,IF
I i
II WI-
a It%I !
I :1 4i1 i 1 ..._
I I .4 ,tli ! I
.11
111 :11:."111 1111,4 II,I, miii .1
VII INlill
PIIIIMIMMIllildllithhilgillitlinalgirt
111...,:;I:aminpalinomoimionilammt"
Illemosem'llimillionsiosimollomunillismimill
flinliMIIIMIME:(111::1 1101111111.811111,tailinn.....i
.,.iPiioqiMirtlin
*}; Inl;,!i;!Mill!i;',1!liniiii:.;iliM11,;i1Tiiiii!iiiiiiiill;.a
,IF
I i
II WI-
a It%I !
I :1 4i1 i 1 ..._
I I .4 ,tli ! I
.11
111 :11:."111 1111,4 II,I, miii .1
VII INlill
PIIIIMIMMIllildllithhilgillitlinalgirt
111...,:;I:aminpalinomoimionilammt"
Illemosem'llimillionsiosimollomunillismimill
.1::--
',It,41,ifflio In
.1::--
',It,41,ifflio In