HOW TO: PAYMENT LOG / SALES TAX for Excel Spreadsheet


What's the point of collecting and recording all of this info?
It's called SALES TAX and the State of Illinois collects, sales tax monthly. In order to pay our sales tax, we need to keep track of how much we collect.

What Items are taxed?
All items are taxed. Services items like session fees, design fees and installation fees are exempt.

Entering Payments into the Payment Log
All payments are to be entered into the physical payment log at the time they occur every day. When I client pays for their order, the order is to be written in the payment log binder. This binder is located in the front room near the credit card machine.

What is a "transaction"?
A transaction is any exchange of payment for services or goods. In other words, anytime a client receives a service or product, the transaction must be recorded.

Examples payment types include: Credit Card, Check, Cash, and Gift Card

When is Sales Tax due?
Sales tax needs to be completed several days prior to the 20th of the month. The accountant needs are info by about the 15th of the month.

Sales tax is a month behind. So if you are in the month of August for example. Sales tax will be due on August 15th for the month of July.

Questions & Answers:

Q: How do I find out the payment type, if is not recorded in the Payment Log? Show picture example (Feb 1, 2016)
A: It depends on the transaction type.

-Credit card transactions: Look in the back of the plastic sheet for a receipt, or look in the back for a sheet receipt from AUTH.
-Online Transaction: American Express transactions that are "online transactions" should be recorded as an "Online Transaction (AUTH)" and a receipt should be found in the back of the plastic sleeve. Other forms of payment through online should also be found as a reciept in the back of the plastic sleeve.
-Checks: There should be a photographed copy in the back of the plastic sleeve. If it is not found here, it will be in finder.
-Cash: There should also be a copy of the picture of the cash in the back of the plastic sleeve. If not found here it will be in finder.

For example this transaction was both "American Express" and an "online transaction". 

Reconciling the Payment Log
Every day needs to be carefully reconciled. This is to be done several ways. There are a series of checks and balances.

If you have a question and the answer is not on this web page, please add the question, as well as the answer, add visuals to make it as easy to understand as possible.




How to Fill out the Excel Spreadsheet

  1. Go to "Shared Files -->  Payment Log --> Master Payment Log --> Master Sales Tax Template.xlsx", then "option copy" the file, so that you create a copy of the file. Never work off of the Master file unless you need to change it for good.
  2. Rename to copy that you are working on. Open the excel and Select the first sheet. Change MONTH to the correct month and change the tab name Master Sales Tax Template to MONTH Sales Tax.
  3. Change all the z's to the correct month number (i.e. January is 1, June is 6, November is 11) You have to do this first because if you enter anyone's name with a z, that z will be replaced with the month number.
    Click on Edit (In the upper left hand corner of the screen)
    Click on Replace...
    Type z in Find What: 
    Type month number (i.e. January is 1, June is 6, November is 11) in Replace With:
    Drop downs should be: Within: "Sheet" & Search "By Columns"
    Click on Replace All
    If you need further help on this step, here is a screenshot of what it should look like.
  4. Now start entering the Payment Log data into the excel. For columns B-G follow the log straight across. 
  5. For "Card" column, write what the code in "" for each payment type.

    Payment Type - Code: Visa - "Visa", Mastercard - "MC", American Express - "AMEX", Discover - "Disc", Cash - "CASH", Check - "CHECK"


    For SWIPE/# column, follow what's in that column: YES, NO, #
    For TYPE column: If written under Yellow - Visa, MasterCard & Discover, type "CCM"
                                   If written under Orange - Online Transactions, type "AUTH"
                                   If written under Blue - American Express, type "AMEX"
                                   If written under Pink - Check, type "CHECK"
                                   If written under Green - Cash, type "CASH"
                                   If written under Grey - NTP Gift

    Certificates/Discounts, try to place it under a different TYPE, highlight in yellow, and make a note
  6. For the K column NOTES, look at the notes in the payment log. In the tab called Product Line, there are a list of different products. Find the one that matched the most with what's in the payment log and copy and paste it. If there is nothing similar, make one and highlight it yellow. Follow the format of OVERALL PRODUCT / Details on Product. 
  7. For columns L-N, follow as written in the payment log. If there is nothing written in the OOS column (OOS stands for Out Of State), look up the client in address book and check to see if they live in Illinois. If they DO live in Illinois, write "NO" for OOS. If they don't live in Illinois, write "YES"
  8. If there were no transactions on a day, write "NO TRANSACTIONS" in the FIRST NAME column. 
    If you need further help on this step, here is a screenshot of what it should look like.
  9. If there are less transactions than the three transactions spaces given,
    Highlight the row(s) number(s) all the way on the left (1, 2, 3, etc)
    Right click on the highlighted row numbers and click on Delete
  10. If you need more transaction rows,
    Click on the row(s) number(s) of the totals row and drag down until the number of rows you need to add is written in the small box that pops up (should say #R which stands for #ofrows that will be added). Once this shows the correct number.
    Right click the highlighted row numbers and select Insert. 
    Copy and paste the line above from the DATE to the OOS column into the inserted row so the formatting is the same.
    Fill in the rows and move on.
  11. On the grey line with all the totals, enter the number of transactions where the is a 0 under the card column.
  12. Down towards the bottom of the spreadsheet there is a peach line with MONTH MONTHLY TOTAL $## $## $##
    Here the totals of those columns should show, and the last $ amount should be the total SALES TOTALS. There are two stacked numbers there. The first is the total summed across the BEFORE TAX, TAX, and SHIP totals. This number should match the one below it which is the SALES TOTAL's summed vertically. If these numbers match, Yay! Double check that all the numbers you entered are the same ones in the log book and you can check with Nicole on any Yellow highlighted sections and be done. If not, keep going for troubleshooting problems. 
  13. Troubleshooting
    1. If the horizontal total is larger than the vertical total, check to make sure the vertical total is summing all of the necessary numbers by double-clicking on the vertical total cell and scrolling through the excel to make sure all of the cells that need to be summed have a colored border around them. To add a cell to be summed click on it. If it gives you the #NAME error then click on that cell, type "=SUM(" and manually select each cell it should add together.
    2. If the vertical sum is larger than the horizontal sum, check that each vertical total is correct (before-tax total, tax, shipping). You do this in the same way that is described in the trouble-shoot 1.
    3. If neither of these works. Double check that each total in the spreadsheet of each day is summing all of the payments for the day. This is also done in the same way as trouble-shoot 1.

How to Do Backwards Tax
This will tell you how much the tax was on a specific dollar amount.

Use this formula to figure out how much tax was paid on a specific dollar amount. For example, if you knew that a client paid $550.00 for their order, but you didn't know how much of that amount was sales tax. Use this formula to figure it out. (Keep in mind if they had non-taxed items this wouldn't work).

Example: Total payment is $550.00
You want to figure out how much tax was paid?

Take $550.00 and divide by 1.0975. . You get $501.138 (round up to $501.14)
(Round to the nearest hundredth [.00]. If the third decimal place is 0-4 keep the second decimal the same. 5-9 round the second decimal up.)

Where did we get 1.0975? 
Take the total payment and divide it by 1.0975 (Since there is 9.75% tax it is 1.097. For example if somehow sales tax got to 12.25% tax, you'd divide by 1.1225) The number you get is the before tax figure you are looking for.

Subtract the "before tax" amount from the total payment. The number you get form this is the tax.

$501.14 is the before-tax amount
$550 - $501.14 = $48.86                                            

$48.86 is the tax

Check your work
$501.14 x .0975 (9.75% tax) = $48.86|  Tax checks out.
$501.14 + $48.86 = $550|  Total Payment Checks out.