I am looking for an experienced data processing specialist take shipment data from my fulfillment center, and code a spreadsheet to calculate revenue in each US State and each country outside the US for three years (2015, 2016, and 2017).
I will share a sample of the data with shortlisted candidates. Please note that it is only one small sample of the data to process, and the full spreadsheet contains 177,644 rows of data.
The data export currently shows us the customer's state and country, the product they purchased, and the quantity that they bought. It does not show the price, however the prices are known and included in the second tab in the sample spreadsheet.
To calculate the total revenue for each state and non-US country, we will need to do the following:
- Calculate each shipment's revenue by multiplying the quantity ordered by the price of the product they ordered. For example, if they ordered two $50 products the revenue for this order would be $100.
- Calculate the revenue for each state for each of the three years (2015, 2016, and 2017). For example, we would calculate the Texas revenue for 2015 by adding up revenue from all of the orders to Texas customers in 2015. We would do the same for each state and non-US country for each of the three years.
The final deliverable will be:
- A spreadsheet that shows the correct total revenue for each state and non-US country for 2015, 2016, and 2017
- Include the original data and prices on this spreadsheet, and a brief written explanation of how these revenue numbers were calculated.
This data is needed for a state sales tax audit, to provide supporting third party data for the reports exported by our CRM.
If this project goes well, I have more data processing work that I would like to find a regular person to work with on in the future.
About the recuiterMember since Sep 13, 2017 Gregory P Benso
from Illinois, United States