HOW TO: PAYMENT LOG / SALES TAX FOR EXCEL SPREADSHEET

HOW TO: SALES TAX

Questions & Answers:

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.

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 the 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.

What is an example of a transaction type?
Credit Card, Check, Cash, Gift Card or Trade

When is Sales Tax due?
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. Sales tax needs to be completed several days prior to the 20th of the month. The accountant needs our info by about the 15th of the month.

How do I do Sales Tax?
Every day all monetary transactions are recorded in a binder that lives in the front room desk. The binder is called the "Payment Log". The "Payment Log" is used to figure out the sales tax.

What is the current tax rate for Glenview, Illinois?
As of 05/16/18 the tax rate is 9.75%.

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

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

RECONCILE 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 Format Cells

For organization of the spreadsheet the format each time needs to be correct. This includes, text size, font, cells/borders, and currency.

1. Select the cell that is correctly formatted.
2. Locate the "Format" button and click. This will create a hiighted border around it.
3. Next, located the cell or cells that you want to format correctly and select all of them.
4. Let go and it should have formatted the cell or cells.

You can format anything-text size, font, borders and etc. I used the currency as a visual example because it was easiest
See screenshot below for visual steps.

Screen Shot 2018-05-21 at 2.11.37 PM.png

 

HOW TO FILL OUT THE EXCEL SPREADSHEET

Save every 5 minutes!

Not kidding-excel has a mind of it's own and quits unexpectedly often.

FIND THE MASTER EXCEL & RENAME
Go to "Shared Files --> Payment Log --> Sales Tax --> Sales Tax Template" folder then --> Master Sales Tax Template YYYY "option copy" Master Sales Tax Template yyyy" file, so that you are working off of the copy. Never work off of the Master file unless you need to change it for good. See image below.

How To Get To Sales Tax.png
 
Screen Shot 2018-05-18 at 10.09.34 AM.png

RENAME THE FILE WITH THE CORRECT MONTH AND YEAR
Rename the copy that you are working on. When you rename the spreadsheet it should be named as "MMYY Sales Tax".

Open the Excel and Select the first sheet or tab at the bottom. Change MONTH header to the correct month and year. See example below. and change the tab name Master Sales Tax Template to "MMYY Sales Tax". See example to your left..

Note: If you are in the "Page Layout View" you will be able to read the title of the page if you are in the "Normal Page View" you will not see the title of the spreadsheet, so switch to the "Page Layout View" and you can easily edit it.


 

 

 

Screen Shot 2018-05-18 at 10.25.00 AM.png
Enter Month and Year on Spreadsheet.png
 

CHANGE THE DATE IN THE "DATE" COLUMN TO THE MONTH YOU ARE WORKING ON
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.

  1. Click on Edit (In the upper left hand corner of the screen) Scroll down to Find
     
  2. Click on Replace...
     
  3. Type z in Find What: 
     
  4. Type month number (i.e. January is 1, June is 6, November is 11) in Replace With:
    If you are changing the year.
    In Find What: Type the Year the spreadsheet current is in Replace With: the correct year.

     
  5. Drop downs should be: Within: "Sheet" & Search "By Columns"
     
  6. Click on Replace All
     
  7. If you need further help on this step, here is a screenshot of what it should look like.


START TO ENTER PAYMENT LOG DATA INTO THE SPREADSHEET

Now start entering the Payment Log data into the spreadsheet.

  1. Look to see how many transactions were on the day.
  2. If there were no transactions on a day, write "NO TRANSACTIONS" in the FIRST NAME column.
  3. If you need further help on this step, here is a screenshot of what it should look like.
  4. If there are less transactions than the three transactions spaces given, delete the unused spaces. Follow steps 4 and 5.
  5. Highlight the row(s) number(s) all the way on the left (1, 2, 3, etc)
  6. Right click on the highlighted row numbers and click on DeleteSee screenshot here.
  7. If you need more transaction rows,
  8. 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.
  9. Right click the highlighted row numbers and select Insert. 
  10. Copy and paste the line above from the DATE to the OOS column into the inserted row so the formatting is the same.
  11. Move on and add the date from the payment book to the correct columns.

CLIENTS NAME First and Last:

For column B enter the clients First Name

For column C enter the clients Last Name

Make sure the spelling is correct. 
If you are not sure about the spelling look up the client in contacts.

There are many obvious reasons why we want the clients name to be spelled correctly but for sales tax purposes we need the client's name to be correct in case we need to search their payment later.

If in the client name has a specific note DO NOT leave it out.

For example: Gregg Shutan CBRE

Keep CBRE at the end of his last name.

This makes it much easier when we have to bill corporate offices that send employees to our studio.
Other examples are Courage To Connect "CTC", Trammell Crow, and Moller Financial.

ADDING PAYMENT DATA on each day:
COLUMN NAMES and description:

Column D BEFORE TAX

Column E TAX

Column F SHIP

Column G SALES TOTAL

1. Column D-G: Enter currency information as written in the book.
Double check that the payment receipts are in the back of the slip and all of them are written down. Take them all out if needed.
This ensures that all payments are recorded and the correct amount has been paid


ADDING PAYMENT INFORMATION

CREDIT CARD TYPES
Under Card, column H write in the credit card type the client paid with. If it is an online transaction write “Y”. This is because we do not know which card the client uses so we substitute the type with “Y”.

Visa "VISA"

Mastercard "MC"

American Express "AMEX"

Discover "DISC"

Cash "CA"

Check  "CHECK"
See also image of payment types below.

SWIPE/# column I:
Add information as is from the payment log.

Under swipe write “Y” for yes and “N” for no. This will tell us if the card has been swiped or not.

If the payment was paid with a check, substitute “N” for the check number.

PAYMENT TYPES transactions:
Under Type column J write one of the below descriptions.
 

CCM = Credit Card Machine Transactions
Click here for CCM example.

AUTH = Authorize.net (Shoot Proof Transactions)
Click here for AUTH example.

SS = Square Space (Square Space Transactions)
Click here for SS example.

VT = Virtual Terminal (Transactions made in Payment Gateway)
Click here for VT example.
See also image of payment types below.

 

 

 
Screen Shot 2018-05-16 at 12.18.06 PM.png
 

1. Column K

NOTES, follow 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. Follow the format of OVERALL PRODUCT / Details on Product..

2. Column L-N follow as written in the payment log.

  • Column L PIF paid in full:
  • If column L says “N” highlight it yellow. This is so that we can easily tell if someone has not paid in full
  • Column M INT initials:
  • As written in the payment log enter the initials of the person who took the information down.
  • Column N OOS out of state:
    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 "N" for OOS. If they don't live in Illinois, write "Y" for OOS. 


Columns O and R

Column O TYPE:
Here, type in what kind of session it was or the best fit description of the session. This may not say it clearly in the payment log so you will need to find it.

Different ways you can find what the session was.

  • Client Order
  • Look in the back of the day that the order was purchased and see if it says on the receipt.
  • Contacts
  • Look in the contacts app and search their name- you may find it in the below notes of the contact information.
  • Calendar
  • Open the calendar app up and search the clients name and find the day of the shoot. The session type may be within the contacts session time under the notes.
  • Client Folder
    Look in their client folder (most likely the year of the purchase) and look in their ProSelect folder or even through a few images.
  1. On the grey line with all the totals, enter the number of transactions where there is a 0 under the card column.
  2. Down towards the bottom of the spreadsheet there is a peach line with MONTH MONTHLY TOTAL $## $## $##
  3. 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. 

HOW TO ENTER VOIDS

All voids will be written in the sales tax spreadsheet. Do NOT included voids in daily total transactions.
If the void is outside of 24 hours we will need another transaction.
If the void is within 24 hours we do not need another transaction.


1. Enter client information.
2. When adding the currency as a void enter the total amount as a negative. Once you add the negative amount it will become red.
Example: -275 Click here to see example of a void. All negative amounts will end up in red.
3. Double check the ending total under the peach line to make sure everything adding up.
4. The original transaction that has to be voided must say in the NOTES column K,  Original Transaction.
5. The original transaction that has to be voided must say in the NOTES column R,  Voided Transaction. Highlight yellow!

If you have any questions, please ask Nicole. 

 Write here…
 
Screen Shot 2018-05-18 at 5.05.22 PM.png
 
How To Check Monthly Formula.png

TROUBLESHOOTING IF YOUR NUMBERS DON'T MATCH
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 (CELL #" and manually select each cell it should add together. Once you have selected the last cell you want " use ) and press tab" This will give you the total number.

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.

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.