Recent Posts:

  • Point of Sale Journal Entries for QuickBooks
  • PAYROLL MANUAL ENTRIES IN 4 EASY STEPS
  • Understanding the payroll register
  • Understanding payroll journal entries in QuickBooks
  • No such thing as a bad student
  • S-Corps, K-1's, 1040's, Oh My!!
  • W2 an easy guide as seen from a tax professional, Part 2
  • W2 an easy guide as seen from a tax professional



    • Point of Sale Journal Entries for QuickBooks

    Point of Sale Journal Entries Imported to QuickBooks in 4 Not So Easy Steps

    Level: Advanced

    Practical use in the field: Excellent

    Prerequisites:

    1. Knowledge of the restaurant industry
    2. Sales tax rates in New York State/City
    3. Restaurant journal entries
    4. Sales tax journal entries
    5. Advanced knowledge of Excel

    What you will need:

    1. The custom workup template I designed:

    http://www.taxproff.com/excelspreadsheets.php

    2. Your clients Point of Sale register in CSV format for each day of the month, and the monthly summary.

    3. Big Red Consulting or BRC IIF import software:

    The IIF transaction creator and importer take raw Excel data and will import it into QuickBooks at a touch of a button.

    You can import journal entries, deposits, bills, credit card transactions,

    and much, much more! It takes a bit of practice, but once you learn how to use this tool,

    you will wonder how you have managed without it all this time. It is fairly affordable and I will post the product name and link below:

    IIF Transaction Creator & List Importer

    http://bigredconsulting.com/latest-version-info/

    4. Kutools for Excel

    Kutools is a very powerful tool that is very affordable (around $19 for the year).

    Prior to Kutools, you would need to write VBA or Macros formulas to be able to accomplish the same tasks.

    In this article I use Kutools to delete all rows that have zero values in a couple of clicks.

    I will add the link of an excellent article written on how to do just that. I will also post the link for buying or trying out Kutools below:

    http://www.extendoffice.com/documents/excel/815-excel-remove-rows-based-on-cell-value.html

    http://www.extendoffice.com/product/kutools-for-excel.html

    One caveat:

    The spreadsheet I created is already formatted for my clients POS Aloha CSV exports. You may need to make adjustments to the Excel spreadsheet to conform to your clients POS export.

    Once you have accomplished that, you will be able to take it forward and use the same method month after month with great ease and detail.

    I can’t go into great detail as to how to use Kutools or the Importer, as that is something that you will need to refer to the source, or the articles I have indicated above.

    As I mentioned, this is an advanced blog designed for advanced users.

    Understanding:

    You may ask yourself why you would need a daily report as opposed to entering the journal entry in one shot on a monthly basis.    

    1. You keep the detail of everything that occurred for each day
    2. You will be able to run reports for any day of the year
    3. The restaurant industry should have reports run on a 13 period cycle as opposed to on a monthly cycle in order to show the true profit and loss. The article below is generated by

    John Nessel at Restaurant Resource group:

    http://www.rrgconsulting.com/page/RRG/CTGY/QBGUIDE

    I have purchased his QuickBooks guide for the restaurant industry for $79; it is a very good resource that covers the industry from soup to nuts (no pun intended)

    Overview of worksheet by section:

    1. There are multiple tabs, one for each day of the month, or 31 days
    2. There is a Calculation Tab that pulls the data from each tab. You may need to format your spreadsheet, to pull the rows you need using the VLOOKUP function
    3. The last tab is the Journal Entry Tab. This tab pulls data from the Calculations Tab and has the amounts go directly into the journal entry format needed to import into QuickBooks

    Step 1

    Paste the CSV file for each day into each tab of the worksheet. In my particular case, there are multiple cells that start with the same name (like Charged Tips),

    hence, it confuses the Excel LookUp function. My workaround for this is to paste a custom name like Employees Charged Tips, and I highlight it in green in each day.

    The Calculation Tab is obviously the source for copy paste task. The daily tabs should all be uniform and will look like this in any area that has duplicate names:

    Step 2

    Once the pasting into each day is complete, you need to verify that the information reconciles in the Calculation Tab.

    I have already taken great care of that in the worksheet, and one needs to look it over to understand how it works.

    There are rows that zero out (or indicated as Diff? in yellow highlight) in the template. If it does not zero out, you obviously need to tweak your workbook:

    Once that is done, you will use the clients monthly numbers to make sure that everything reconciles on a monthly basis, in column AG and AH of the Calculation Tab:

    Step 3

    Now you need to move to the Journal Entry Tab and change the dates. This is very easy, as all you need to do is change the very first day in cell B5, the rest of the days will change automatically for the month you need.

    The debits and the credits need to equal, some adjustments will be needed to eliminate the pennies for rounding, that you will take care of in the final stage, when you are ready to import your transactions.

    Please note that the format here is already set up for the BRC importing, there will be no need to make any adjustments. You need to try out the importer and get a hang of it before you go ahead obviously.

    Step 4

    Now you have your information ready to import, delete all the zeros using Kutools, and import into QuickBooks. You are done.

    Please study the worksheet, the BRC and Kutools software before attempting this in real time. Once you are very familiar with all of the above, the total time it takes to enter all of this information from start to finish, takes me around 30 minutes. It is set up to have a great deal of information and complexity imported in 30 minutes tops. If you need guidance or help setting up your spreadsheet, I will be more than happy to help you set it up. Feel free to contact me with any questions or comments that you may have.

    Greg Freyman, CPA | 05/10/2013