Sales and Use Tax Allocation Overview

Sales and Use Tax Allocation

Allocating state taxes according to known proportions (Wayfair Decision)

Overview

An online learning subscription service, courses2you.com, offers a subscription membership for $9.95 per month. Some users subscribe through the site itself and provide their billing zip code at checkout. Others subscribe via third party distributers on college campuses. For these subscribers, we do not have a billing zip code. The tax team at courses2you.com must calculate the sales and use tax per state for these subscription sales. Given the lack of zip code information for many subscribers, we will approximate our sales distribution by state according to the proportion of known sales.

Objective

The goal of this workflow is to create a table that lists the taxes to be paid in each state for subscriptions occurring in 2018.

There will be 5 different files of subscription information provided, one from each distributor, including the direct distribution from the company site which will have zip codes. You will have to use the dates provided from those files in order to determine the amount each customer was charged for their subscriptions in 2018. If the subscription was active in a month for even a single day, the user will be charged the $9.95 monthly fee. Some subscriptions are/were active for several years including those that have not yet expired. Be sure to account for any month those accounts may have been active during 2018 as well.

Processes

1. Pull in all of the .csv files.

2. Identify the 2018 revenue for each subscription using the number of months that each subscription was active in 2018. If the subscription was active for any day of a month, that month is included. Be sure to remove bad data where the expiration date is before the activation date.

3. Identify the total known sales, the total known sales for each state, and the total unknown sales (no zip code so cannot be allocated to a state).

4. Use the known sales proportions for each state to allocate the unknown sales accordingly, and determine the total sales tax required for each state.

5. Create a formatted table to be output to an Excel file containing the state, its approximate total sales, the state tax rate, and the state taxes to be paid.

Data Dictionary

Subscriptions_College Courses Now.csv

All 5 of the subscription files have the same schema with each record representing a single subscription.

zipcode_by_state_lookup.xlsx

Identifies the state associated with each zip code.

State Tax Rates

This data is included as a Text Input tool included in the starter workflow. There is not an external file.