Depositor Reporting

Owner: James

Timing: Quarterly

Source of data: Shopify

Problem Solved: Analyzes amount of deposits collected vs number of deposits owed, and reported to the government

Process:

Download a Shopify report of the quarter in question.

Process:

Pull a Shopify Orders report for the date interval in question corresponding to your due date.

Export the data for the dates in question as a Plain CSV file.

Open the section of the KPI Mother Goose doc called "Quarterly"

Open the file emailed to you and delete all the columns except the columns titled in Row 1 as "Name, Shipping Method, Created At, Line Item Quantity, Line Item Name, Location & Source"

Save the excel file as "(Month) Shopify Sales"

Open the tab within Mother Goose called "333 Shopify Quarterly"

• Click "File"

• Click "Import"

• Upload the Excel file you just saved.

• Choose to replace cells at the first row of the tab.

If it hasn't been done in advance, copy and paste and then shift paste the rows pertaining to the previous period below, to create a section for this new quarter.

Modify the dates in "Quarter Ending" in Column A.

Referencing the unique list of items in Column J, input the name of each Threes item sold in Packaged SKUs in the Quarterly tab.

• Vliet and Logical Conclusion need to be the firs two rows.

• The equation only needs the first word of each item.

• Each column has a unique equation. Copy and paste each of the equations into the first row for the packaged SKUs we are examining.

  • Fill each subsequent cell with the equation to match the format.

  • A number of these will return as NA.

    • After you have proofed the equation, remove the NAs to allow the total number of bits to be calculated in the far-right column.

Enter the total number of Deposits 4pks into cell M9 and Deposit Singles into cell P9

Since we do not count the number of Logical Conclusion and Vliet 4pks and singles sold into this, the equation calculates how many bits of each we sold

The right section of the doc should calculate the total number of bits collected, subtracting the amount of Logical and Vliet we sold. We take that number and subtract the number of deposits that we collected. The first number subtracted against the second number is the total number of deposits we didn't collect.

Open the Beverage container deposits doc

• Go to Beverage Container deposits depositor NYS

  • For the corresponding quarter, input the totals from your Manual Pull (part1) exercise.

  • The equations setup will total how much we need to pay out.

    • Enter the amount of bits relative to each section from M13 and N27 and add to the row for the Quarter Ending we are examining.

      • Drag the equations down from the above row in Columns M, N and O.

      • The amount in Column N will have the amount we need to pay.

  • Go to the NYS website

  • Press login

  • There are multiple logins on hand for the NYS website

    • You want to use the one for ‘3foldnysjustin’ in 1password.

    • Select Other taxes in the Services menu.

    • Select ‘Beverage container web file’ from the drop-down

    • Select the matching quarter we manually pulled in the previous section.

    • Under “Balance in account at the beginning of the quarter ($): Input “0”

    • “Is this an amended filing?” No

    • “Are you permanently discontinuing your business and filing your final return?” No

    • Press ‘Continue’

    • Beverage container deposits credited to account ($): This should match Column M from your work on Beverage Container deposits depositor NYS

      • Interest and dividends credited to account ($): $0*

      • Beverage container deposits paid from account ($): $0*

      • Service charges on account ($): $0

      • Press calculate to pull the 80% figure required to pay. Screenshot figures and press continue

    • Selecting our Bank Account listed as the payment option. Screenshot each step as you go.

    • Screenshot confirmation

    • Click print, save file as PDF. Format file as “YEARMONTO for TIME FRAME” and upload the receipt to a new folder in this one formatted to the ending date in the window in which you’re reporting. Upload figures from the calculation page and also the screenshot from the bank account page.

    • Enter date paid in Column P of Beverage Container deposits depositor NYS