I am an accountant and and learning how to use Shopify software for a new client (remote retailer startup). Thank you for your video, "How to record Shopify sales in QBO". It was one of the best training videos I have ever watched. You are an excellent teacher. I am adopting your process in my work. I appreciate the time you took to produce this video.
I'm so grateful for this tutorial and your incredible teaching style. You don't skip over steps and assume people will know. Thank you so much. Finding this has made my year!
Thank you. Great comprehensive video. So many blogs, websites, and videos just brush over some stuff and then tell you to buy their product or hire them. As a person trying to start a business, keeping fees low is a must. This was so helpful. Thanks again
very good howto. You did a great job and I hope you end up doing this professionally. I have two shopify stores and an accounting company, they are quite confused on all the details in shopify and it's a learning curve. A talented bookkeeper who specializes in automating quickbooks, shopify and the ecommerce solutions should be quite valuable in this economy.
Thank you! I agree with the value/demand and find myself quite happy with my current expertise and niche of working with external e-commerce POS systems with QBO. Thanks for watching and thank you for the comment!
This was incredibly helpful and I just want to say thank you SO MUCH for taking the time to make this video. I was lost and now I feel like I actually understand things clearly.
I really appreciate videos like this! This is exactly how I learn, and I definitely need step by step verbal and visual instructions. Thank you so much!!
Thank you! Using the connector may potenially work for you just fine. I’ve just seen many instances where using integrations mess things up in QBO. It is tough too when the integration is set up fully correctly because then there are a large amount of transactions in QBO to clean up, making it more difficult after the fact. I’d suggest if attempting an integration to first fully figure out the exact debits and credits needed for every case scenario. Then, set up the integration and test and determine the exact debits and credits happening in the background in QBO (looking at Transaction Journals) per each scenario and see if they match. A huge consideration in this too is the timing and amounts of any activity happening to your bank account in QBO during an integration, if the debits don’t match exactly to the actual bank deposits then things can become messy quickly and make bank reconciliations impossible and this is usually where I see duplicate sales start to be entered. I personally like this method because it takes the guesswork and/or research of what is actually happening during the integration and allows for more control and customization.
I love this video. Thank you. When the deposit comes through the bank feed how do we pair it up? Do we just pair up the transfer made from clearing to checking?
Ok I went to the other video about how to enter square sales into QBO to learn about the cash deposits and he ends up explaining it in that video after the 30 minute mark!
You’re welcome! Yes, make sure it’s a transfer from clearing to checking and then yes if you are entering these beforehand, you can then “match” them in the Bank Feed. Or, what I usually like to do is create the transfers themselves from the Bank Feed rather than creating them first and then matching them later, usually I do this with the help of setting up “Bank Rules”. However, either method works and accomplishes the same thing at the end of the day so do what works best for you!
Thank you for finding the timeline in the other video and referencing it here! Also, make note of the other reply I just made in this same thread regarding creating the transfers themselves from the Bank Feed itself which is possible if there is no cash/check split needed. Thanks again!@@haileyguthrie3435
I would so love to be able to watch you do this. Do you have a video that shows the step by step of deposit coming in and how you handle it?@@essentiallyintentional
great video! Although my Financd>Payout report screen does NOT look like the one in your video. Mine does not show fees ? ? ? Is there a way to change columns?
Thank you! Yes, gift card purchases show in sales reports and with this the liability will be increased and gift cards redeemed show in the payments reports and with this the gift card liability will be decreased! The same gift card liability line can be added twice in the journal entry, once in top section to credit for new gift card purchases shown in the shopify sales reports and a second time near the bottom section of the journal entry where the same gift card liability account will be debited (decreased) for gift cards redeemed which will show in the Shopify payments reports
Thank you so much for this - it will save us a lot of time - I have found a Summary Report that seems to put quite a bit of the info together that might be useful. We are in the UK and I think our Quickbooks is set up differently in terms of sale tax though so just trying to figure out that bit. I am wondered how the gift cards fit into this...
You make the best vids! QUESTION: Newbie with hardly any experience (learning for a family biz) they are using Heartland POS system so things are a little different for me but it's the same concept, I think...How would I adopt your process now if I have already reconciled Jan and Feb in QBO? I know I need to fix those months to properly reflect their sales, taxes, fees...etc. Should I un-reconcile, start fresh and follow this process or can I do a bulk JE to show my actual sales and expenses for jan and feb and then do the clearing account starting in March? I would love your input. Thank you!
Thank you! Honestly, it’s hard to say without diving in further. If interested, feel free to schedule a one-on-one session with me (link in video description). At the surface, I can say you to keep the debits in your bank account for Jan / Feb, so a bulk JE without also fixing those will not work. There is a chance of starting in March the clearing account method, but this needs to be handled with care. Again, a one on one could be beneficial as it’s too involved for me to fully explain everything via a message. Thanks again!
Hello! This video was fantastic, easy to understand and easy to follow along to! I have a question about using this method for my client who uses Shopify as her in-store POS. Luckily I am only dealing with Shopify Payments, but I am a little unsure how to add Cash payments into the equation. Any tips? Thank you!
Thank you! Should be the same workflow. Cash payments made will show up in the shopify payments report, in the JE the cash account will be increased. The only difference will be that the CC shopify payments won't be auto deposited ino bank account, you will just need to make sure that when your client is depositing this cash into their bank account the same cash account is being lowered during the deposit entries in QBO, do not duplicate sales here at this stage, I've seen that happen. Another consideration is if checks are involved then be sure to handle that correctly as well during the QBO deposit entry phase
This video was extremely helpful as I have a new small business online and a store front. Can you backtrack doing this each month for 2024? Or would you recommend setting this up and only using it moving forward. I am wondering how it would be beneficial to start from the beginning of the year.
I’m glad I was able to help! Honestly, I would need to know more information about your specific case in order to correctly answer this. If desired, feel free to book a one-on-one Zoom consulting session with me and I’d be glad to go over the pros and cons of going back and redoing or starting now and moving forward for your specific business and case. The link to look at available schedule dates for this is in the video description. Thank you!
I'm understand the Shopify reports and the JE now, thank you for making this video. I can't seem to find all the reports that I need to do the same in eBay. I found the financial statement. I feel I need more than that. Can I just pick up the numbers from the financial statement each month?
Thank you! These help us a lot! Can you help us and dig more about how to record inventory and COGS without inventory apps? and what method are you using, is it perpetual or periodic method?
I'm glad these videos are helping you all! I may need to make a TH-cam video covering Inventory, but I also cover it in my paid course. But, here's a summary of how I personally handle my current clients' Inventory / COGS tracking in the absence of Inventory specific apps: I kind of combine the two methods, perpetual and periodic. I find this to be the best trade-off between simplicity, ease of use and monthly tracking. Straight periodic is the easiest, but I don't like not having COGS numbers until physical counts are completed, even estimated COGS on a monthly basis is better in my opinion to get at least an estimated view of month-to-month Gross Profit. Sticking with Shopify for this example, I would ask my client to the best of their ability enter in the estimated costs for the items being sold in Shopify. This allows me to run a monthly report in Shopify to at least get an estimated COGS per month in real-time. Then, I create a JE debited COGS and credited Inventory for that month to capture at least the estimated COGS. The full purchases of this inventory are just debited as Inventory Asset in QBO and not broken out per Inventory item. So, at this stage all Inventory purchases are just increasing Inventory Asset in QBO for the total and not being tracked per Inv item. Then, when entering these monthly sales, COGS is increased and the Inventory Asset decreased every month based on the estimated amount which is taken via the COGS report in Shopify which tracks the items sold for that month with their associated estimated cost that was entered before. We can at least get an estimated look at COSG and Gross Profit month to month with little effort and no physical counts. Then, finally when the client can, ideally quarterly but at an absolute minimum once at year-end, they will perform a physical Inventory count. Then, once that is completed, I'll take the actual Inventory Asset total on hold, compare it to the Inventory Asset total in QBO and offset it with COGS, this will bring both the Inventory and COGS numbers to actual. Again, I personally love using this customized combo for small business clients because it combines the simplicity and money savings of not having to introduce a perpetual inventory system, but also allows you to at least include estimated COGS in the monthly P&Ls with the ability to bring those numbers to actual whenever you want with a physical inventory count.
Hi! I'm cleaning up shopify store and I don't have experience with Netevia.hq POS. I'm having trouble finding the amount of Shopify payouts and fees in Netevia POS, and I can't reconcile it with the bank. DO you have experience on this? Thank you very much.
This is a VERY helpful video!! Since we are not online only, could you clarify how deposits of cash and checks are accounted for - what do you credit when the deposit is made if it isn't sales (since sales are added in total in this sales journal entry?)
Thanks for the feedback I'm glad it was helpful! You can watch my Square video here: th-cam.com/video/ybCOrxyitbc/w-d-xo.html starting at 30 minutes in where I discuss this topic. The short answer though is if you are separating cash and check clearing accounts on the Shopify Sales journal entry, then you would create a deposit (or journal entry) in QBO and then in the bottom portion of the bank deposit entry separate out the cash and check deposit amount used in the cash and check payment accounts during the sale entry (I explain better in the video). If you are entering the cash and checks collected during the Shopify sales entry JE under a combined Undeposited Funds account then you would just create a transfer in QBO from Undeposited Funds to your bank account to record those deposits.
Great video, but I am really struggling on how to incorporate the Bank Feed and the Journal Entries. Also, with the PayPal Bank feed where deposits are made and the fee comes out there (I know you explained the fee portion well - thank you). Again, its how to clear everything out from teh Bank Feed I am struggling with.
I very much need to make a video on this portion of the workflow as I’ve had many requests for this. Sorry for the delay but that will definitely be my next video made.
Gunnar, Shopify payouts received at the beginning of the month are deposits for the previous month. Therefore, to be accurate I would need to pull fees from those payouts that are for the previous month to get a true amount paid in fees for the month, correct?
Yes, that is correct if attempting a full Accrual-Basis workflow. I wish Shopify had an easier way to determine the month’s fees. I used to do this for clients where I would look at the following month’s payouts to add in those fees to the prior month but stopped for a few reasons: 1. It became more trouble than I thought was worth because at the end of the day the fees were equal anyway. 2. It lead to a potential increase chance in error if you miss some calculations and if you missed how some payouts contain sales amount for two months (one payout containing fees and funds from both the last day of the prior month and the first day of the current month), if any of these are missed at the beginning and ends of any month, then the Shopify Clearing Account will never go back down to $0. 3. (and most importantly), all of these clients were both taxed as and mostly tracking things on a “Cash-Basis”. I started to view the way shown in the video as recording the fees correctly if looked at on a cash-basis. The way I started to view it is if you receive a Payout to your bank account on July 2, but it was from sales on June 30, you could still post those Shopify fees withheld as as expense in July because the fees weren’t technically incurred (again on a cash-basis) until the payout was made and deposited in July. You can certainly click on the individual Payouts and do this math like I used to with no harm, especially if you are needing a full Accrual-Basis workflow. I decided to teach the simpler cash-basis workflow in this video for the 3 reasons stated above. Hope this makes sense and helps clarify things and I appreciate your thoroughness!
You could use a Sales Receipt and positive and negatives to accomplish the same thing but you have to ensure they all equal $0 and know what you're doing when setting this up as to where all of the products/services on the sales receipt are linked to in your Chart of Accounts, I go over this method in some of my other videos, I believe in my "How To Enter Daily Sales into QuickBooks Online" video.
Greetings, so I wouldn't have to touch the entries in the bank statement, I just wait until the end of the month and make the single entry in the journal? Shopify groups several payments into one, would there be a way to reconcile the bank statement for greater accuracy or is it better not to touch them?
No, in order to complete bank reconciliations, you will need to categorize every debit and credit in and out of the bank account for the same exact dollar amounts and dates. You will still only be entering one Journal Entry to record most of your Shopify activity into your books like sales, but will still need to do something with each Shopify to Bank deposit in the Bank Feed. These will mostly be categorized to lower the Shopify Clearing Asset account. Then, you will have all Shopify info entered but also keep the capability of completing the monthly bank reconcilaitions.
Pennies off: Gunnar, almost without fail my journal entry is $.02 cents off in one direction or the other. How should I handle this difference so that I can save the journal entry?
A short potential solution would be to do this: Create an “Other Income” Chart of Account account called “Cash Over / Short” and put the pennies off, whether debited or credit into that account. A more involved solution would be to investigate why it is always a few pennies off within Shopify and then adjust the Journal Entries accordingly. This is assuming that the pennies difference isn’t happening based on the Journal Entry entries themselves but there being a legitimate difference in Shopify of Sales and amount collected within certain months.
Hello, just watched your video and I have a quick question, my total sales figure on the "Sales Over Time Report" does not equal the net payments on the "Payment" report as it did in your example. Is there a way to account for this in the journal entry? I believe it is a timing issue so not sure if I need to add an additional clearing or holding account for the difference.
You definitely can add in a temporary holding account if you'd like, this would be if you are tracking monthly sales on a more accrual-basis. What I'll sometimes do instead of adding a separate holding account is use the total Payment amount and increase/decrease the Income line to match it. The logic I find behind this is for if you are accounting on a cash-basis, then the income matches what you were paid that month, then this will balance out the following month. An example would be a $100 payment timing issue, then in Shopify the month sales report is $100 than the Payment report, then I would put the payment as-is, then lower the sales (based on the sales report) by $100, that way you are not including the $100 in sales that you haven't yet received payment for, hence the cash-basis accounting, Then, the following month that $100 payment should be included in the Payments report and the sales can be reflected in that month's sales. Hope this makes sense and again, either method could work for you.
I’m not sure if this is exactly answering your question, but I like to separate the clearing accounts based on the future avenues of deposits. With some of my clients, within the Shopify sales, they will receive separate bank deposits from Paypal, Shopify, Amazon, Stripe, etc. When this is the case, I like to separate them out into separate clearing accounts so that it is easier to audit the accuracy of the workflow if it’s broken out like this. This also makes it easier to handle things like separating out the merchant fees and everything vs trying to do that if you combine them all. Having said that, if you want to make it all under one clearing account, or if you only have Shopify payments for your business, then you can definitely do that as well no problem at all. I just try to add in as many potential scenarios as possible in these videos in order to cover as many possible scenarios as I can.
In the workflow shown in the video, the fees are not being subtracted out from the sales directly as the sales are entered “Gross” and the fees are also added in separately as an expense. So, on your Profit & Loss Report, your top line sales are all Gross Sales, and then the Shopify fees are listed as an Expense (or COGS) on your Profit & Loss, leaving your Net Income (everything else excluded) as the total gross sales minus the Shopify fees. One of the reasons I prefer this method is because small business tax forms (Schedule C/1120S) ask for Gross Sales instead of Net. If you want though, you could just put in the sales using this workflow/template as net fees, you would just put the net fees amount in the Shopify income line and remove the separate Shopify Fees expense line.
It should be the same, assuming that the sales is entered at the time into Shopify via cash. As long as it shows up on the monthly Shopify reports as cash sale than everything else should be the same workflow as shown!
Hey there. My total sales on Shopify does not match my total payout figure in Shopify because there are timing differences (when the purchase was made and when it was paid out). How do I account for this on QB?
Are you referring to the "Net Payments" total amount in the Shopify "Payments” Report vs Shopify Net Sales, or the net Shopify payouts in the Shopify “Payouts” Report?
@@essentiallyintentional I have the same problem as well, Im using the Shopify Payouts Report. Since there is a delay on when shopify sends payments the sales and what is collected dont match up.
@@essentiallyintentional The payout report you use to get the fees may include fees from sales made before the desired month (e.g. order on 30th April may reflect in 2nd May payout if no payout was made over the weekend) thus the fees total is incorrect for the month of May? Or does this not matter because overtime it may balance out?
@@ethanb931 That is correct, it does though balance out over time. I used to break it down by month by clicking on certain payouts and seeing what month's sales it was associated with, by ended up finding this to simply not be worth the effort (although it can certainly be done no problem). But again, over time I've found that if not doing that then the Shopify Clearing Balance always over time ends up being brought back down to $0.
No, this will not save time vs a direct integration. I prefer the control and flexibility of this method, but if time savings is your number one priority then you can attempt the direct integration route.
It’s different for different workflows and it’s up to you to determine when the fees are taken out at what stage, pre or post deposit. In my experience, when having the Paypal Bank connected to a QBO Bank Feed, you can create deposits that take out the Paypal fees then, that is what I show for that particular workflow taking the Gross Paypal amount. If in your workflow you are not accounting for Paypal Fees at the deposit stage, then you can take net Paypal and add in the expense fees during the sales entry stage with no problem. Hope this helps!
@@essentiallyintentional So what I have ended up doing is going through the monthly statements on PayPal and just adding a line for them on the journal. Unfortunately with paypal, they show all fees in their native currency, so i have to do conversion rates. But it works out in the end. Thank you for the response!
What are the most common errors here? I cant for the life of me figure out why I'm off 24.48 I will say this is my first month of cash sales if that makes any difference?
Sometimes it’s a timing issue vs entry, I would first verify the amounts match (or don’t match) for the “Total sales” in the sales over time report and the “Net Payments” from the payments report for the same date range. If the do match then it’s most likely an entry error. If they don’t match, then first verify is the mismatch is the same dollar amount you listed and then research from there, mainly looking at what is included in the payments amount and whether it should be counted as cash sales for that month and if you deal with any open invoicing or anything within Shopify as well then that could complicate things potentially also
@@essentiallyintentional I am also having the same issue. I can't figure out why I am off by 79.03 in my journal entry (debit has the larger total number). My Net payments and Total Sales are off by that amount in Shopify. Any idea why would that be? I am a small business and do my own bookkeeping, so I am hoping your method will help me stay more on top of my accounts.
@@SimpleMama47 There are many different possibilities that could cause that, that it would be hard for me to say without further investigation. Essentially, however Sho[ufy is calculating the sales number on the one sales report, vs the payment collected total is sometimes a little different (again for many different potential reasons). When I’ve ran into this with clients, I’ve always (because we are tracking sales on a cash-basis), kept the payments as-is and adjusted the sales number if needed to match the payment collected. Doing this has always kept the Shopify clearing account accurate and eventually getting back to $0, and I feel the sales accuracy is correct, again being on a cash-basis, because you are counting sales based on the actual total funds collected (after sale tax and things of course). So, you could either doing some investigating within Shopify to see with your particular workflow and business how they are calculating each number and going from there, or adjust your sales in the JE to $79.03 higher (or do both). When investigating within Shopify, some things to look at are your individual payment methods and collected, and if they all represent dollar for dollar what’s reflected on your sales report (I’ve seen “Manual” payment type in Shopify being used for order errors to reship products, so the numbers was increasing the net payments number but not the sales. Or, if there are ever any open invoices, POs, draft orders, etc. Sometimes these can cause the numbers on the two reports to be different. Hope this helps.
How do you account for deferred revenue? Could you make a video on how to record this? Sometimes the report recorded the sales includes all orders for the date in question and not always it ships in the same month.
If I ever make a video for this I will let you know. The short answer would be figuring out the debits and credits needed for your workflow and either working it into your Monthly Journal Entry Shopify Template, adding in a second Journal Entry Template, or a combination of the two. If at any point you would like to schedule a one-on-one with me so I can help make a specific workflow for you (and ask me anything else QBO-related, you can book a session here: calendly.com/essentially-intentional/training-with-gunnar-1-hour thank you for watching!
I am an accountant and and learning how to use Shopify software for a new client (remote retailer startup). Thank you for your video, "How to record Shopify sales in QBO". It was one of the best training videos I have ever watched. You are an excellent teacher. I am adopting your process in my work. I appreciate the time you took to produce this video.
Thank you again for this comment and thank you for reaching out directly. It is much appreciated.
I'm so grateful for this tutorial and your incredible teaching style. You don't skip over steps and assume people will know. Thank you so much. Finding this has made my year!
Thank you! I make long videos, but try to make sure everything is included and no steps skipped. Glad I could help! Thanks for this feedback
Great video! You highlighted key points that no one else mentioned. Thank you!
Thank you, Thank you, Thank you! for this incredibly comprehensive and thoughtfully done tutorial!
Thanks for watching and appreciate the feedback!
Thank you. Great comprehensive video. So many blogs, websites, and videos just brush over some stuff and then tell you to buy their product or hire them. As a person trying to start a business, keeping fees low is a must. This was so helpful. Thanks again
Thank you for this feedback! Much appreciated, glad I was able to help
very good howto. You did a great job and I hope you end up doing this professionally. I have two shopify stores and an accounting company, they are quite confused on all the details in shopify and it's a learning curve. A talented bookkeeper who specializes in automating quickbooks, shopify and the ecommerce solutions should be quite valuable in this economy.
Thank you! I agree with the value/demand and find myself quite happy with my current expertise and niche of working with external e-commerce POS systems with QBO. Thanks for watching and thank you for the comment!
This was incredibly helpful and I just want to say thank you SO MUCH for taking the time to make this video. I was lost and now I feel like I actually understand things clearly.
Glad I could help, I appreciate the feedback!
So helpful! Best video I've found explaining how to properly do this. Thank you for taking the time to make this in-depth explanation. 10/10
I'm glad I could help. Thank you for this comment!
I really appreciate videos like this! This is exactly how I learn, and I definitely need step by step verbal and visual instructions. Thank you so much!!
Thank you for this feedback! My videos are longer than most, but hopefully work for people with your learning style. Thanks for watching!
I like your video presentation. It is informative too, at the same time. Thanks a lot for your guidance!
Thank you for watching and for this awesome feedback!
Great and thorough walkthrough! Is there a reason why you'd use this method over the Shopify Connector Integration?
Thank you! Using the connector may potenially work for you just fine. I’ve just seen many instances where using integrations mess things up in QBO. It is tough too when the integration is set up fully correctly because then there are a large amount of transactions in QBO to clean up, making it more difficult after the fact. I’d suggest if attempting an integration to first fully figure out the exact debits and credits needed for every case scenario. Then, set up the integration and test and determine the exact debits and credits happening in the background in QBO (looking at Transaction Journals) per each scenario and see if they match. A huge consideration in this too is the timing and amounts of any activity happening to your bank account in QBO during an integration, if the debits don’t match exactly to the actual bank deposits then things can become messy quickly and make bank reconciliations impossible and this is usually where I see duplicate sales start to be entered. I personally like this method because it takes the guesswork and/or research of what is actually happening during the integration and allows for more control and customization.
I love this video. Thank you. When the deposit comes through the bank feed how do we pair it up? Do we just pair up the transfer made from clearing to checking?
Same question!
Ok I went to the other video about how to enter square sales into QBO to learn about the cash deposits and he ends up explaining it in that video after the 30 minute mark!
You’re welcome! Yes, make sure it’s a transfer from clearing to checking and then yes if you are entering these beforehand, you can then “match” them in the Bank Feed. Or, what I usually like to do is create the transfers themselves from the Bank Feed rather than creating them first and then matching them later, usually I do this with the help of setting up “Bank Rules”. However, either method works and accomplishes the same thing at the end of the day so do what works best for you!
Thank you for finding the timeline in the other video and referencing it here! Also, make note of the other reply I just made in this same thread regarding creating the transfers themselves from the Bank Feed itself which is possible if there is no cash/check split needed. Thanks again!@@haileyguthrie3435
I would so love to be able to watch you do this. Do you have a video that shows the step by step of deposit coming in and how you handle it?@@essentiallyintentional
great video! Although my Financd>Payout report screen does NOT look like the one in your video. Mine does not show fees ? ? ? Is there a way to change columns?
This is a great, detailed video! Do you also include processing gift card sales/redemptions in this process?
Thank you! Yes, gift card purchases show in sales reports and with this the liability will be increased and gift cards redeemed show in the payments reports and with this the gift card liability will be decreased! The same gift card liability line can be added twice in the journal entry, once in top section to credit for new gift card purchases shown in the shopify sales reports and a second time near the bottom section of the journal entry where the same gift card liability account will be debited (decreased) for gift cards redeemed which will show in the Shopify payments reports
Thank you so much for this - it will save us a lot of time - I have found a Summary Report that seems to put quite a bit of the info together that might be useful. We are in the UK and I think our Quickbooks is set up differently in terms of sale tax though so just trying to figure out that bit. I am wondered how the gift cards fit into this...
Gift Cards are a liability and credited when purchased and debited when used (redeemed) for a purchase as a payment method!
@@essentiallyintentional thank you
You make the best vids! QUESTION: Newbie with hardly any experience (learning for a family biz) they are using Heartland POS system so things are a little different for me but it's the same concept, I think...How would I adopt your process now if I have already reconciled Jan and Feb in QBO? I know I need to fix those months to properly reflect their sales, taxes, fees...etc. Should I un-reconcile, start fresh and follow this process or can I do a bulk JE to show my actual sales and expenses for jan and feb and then do the clearing account starting in March? I would love your input. Thank you!
Thank you! Honestly, it’s hard to say without diving in further. If interested, feel free to schedule a one-on-one session with me (link in video description). At the surface, I can say you to keep the debits in your bank account for Jan / Feb, so a bulk JE without also fixing those will not work. There is a chance of starting in March the clearing account method, but this needs to be handled with care. Again, a one on one could be beneficial as it’s too involved for me to fully explain everything via a message. Thanks again!
Hello! This video was fantastic, easy to understand and easy to follow along to! I have a question about using this method for my client who uses Shopify as her in-store POS. Luckily I am only dealing with Shopify Payments, but I am a little unsure how to add Cash payments into the equation. Any tips? Thank you!
Just found your Square video, which I believe has the answers to my question! Thank you :)
Thank you! Should be the same workflow. Cash payments made will show up in the shopify payments report, in the JE the cash account will be increased. The only difference will be that the CC shopify payments won't be auto deposited ino bank account, you will just need to make sure that when your client is depositing this cash into their bank account the same cash account is being lowered during the deposit entries in QBO, do not duplicate sales here at this stage, I've seen that happen. Another consideration is if checks are involved then be sure to handle that correctly as well during the QBO deposit entry phase
This video was extremely helpful as I have a new small business online and a store front. Can you backtrack doing this each month for 2024? Or would you recommend setting this up and only using it moving forward. I am wondering how it would be beneficial to start from the beginning of the year.
I’m glad I was able to help! Honestly, I would need to know more information about your specific case in order to correctly answer this. If desired, feel free to book a one-on-one Zoom consulting session with me and I’d be glad to go over the pros and cons of going back and redoing or starting now and moving forward for your specific business and case. The link to look at available schedule dates for this is in the video description. Thank you!
I'm understand the Shopify reports and the JE now, thank you for making this video. I can't seem to find all the reports that I need to do the same in eBay. I found the financial statement. I feel I need more than that. Can I just pick up the numbers from the financial statement each month?
Amazing video. Thanks a lot!
Thank you! These help us a lot! Can you help us and dig more about how to record inventory and COGS without inventory apps? and what method are you using, is it perpetual or periodic method?
I'm glad these videos are helping you all! I may need to make a TH-cam video covering Inventory, but I also cover it in my paid course. But, here's a summary of how I personally handle my current clients' Inventory / COGS tracking in the absence of Inventory specific apps:
I kind of combine the two methods, perpetual and periodic. I find this to be the best trade-off between simplicity, ease of use and monthly tracking. Straight periodic is the easiest, but I don't like not having COGS numbers until physical counts are completed, even estimated COGS on a monthly basis is better in my opinion to get at least an estimated view of month-to-month Gross Profit. Sticking with Shopify for this example, I would ask my client to the best of their ability enter in the estimated costs for the items being sold in Shopify. This allows me to run a monthly report in Shopify to at least get an estimated COGS per month in real-time. Then, I create a JE debited COGS and credited Inventory for that month to capture at least the estimated COGS. The full purchases of this inventory are just debited as Inventory Asset in QBO and not broken out per Inventory item. So, at this stage all Inventory purchases are just increasing Inventory Asset in QBO for the total and not being tracked per Inv item. Then, when entering these monthly sales, COGS is increased and the Inventory Asset decreased every month based on the estimated amount which is taken via the COGS report in Shopify which tracks the items sold for that month with their associated estimated cost that was entered before. We can at least get an estimated look at COSG and Gross Profit month to month with little effort and no physical counts. Then, finally when the client can, ideally quarterly but at an absolute minimum once at year-end, they will perform a physical Inventory count. Then, once that is completed, I'll take the actual Inventory Asset total on hold, compare it to the Inventory Asset total in QBO and offset it with COGS, this will bring both the Inventory and COGS numbers to actual. Again, I personally love using this customized combo for small business clients because it combines the simplicity and money savings of not having to introduce a perpetual inventory system, but also allows you to at least include estimated COGS in the monthly P&Ls with the ability to bring those numbers to actual whenever you want with a physical inventory count.
@@essentiallyintentionalThank you very much!
Hi! I'm cleaning up shopify store and I don't have experience with Netevia.hq POS. I'm having trouble finding the amount of Shopify payouts and fees in Netevia POS, and I can't reconcile it with the bank. DO you have experience on this?
Thank you very much.
I'm new to A2X for recording sales, etc., which I see other bookkeepers using. Do you recommend it?
This is a VERY helpful video!! Since we are not online only, could you clarify how deposits of cash and checks are accounted for - what do you credit when the deposit is made if it isn't sales (since sales are added in total in this sales journal entry?)
Thanks for the feedback I'm glad it was helpful! You can watch my Square video here: th-cam.com/video/ybCOrxyitbc/w-d-xo.html starting at 30 minutes in where I discuss this topic. The short answer though is if you are separating cash and check clearing accounts on the Shopify Sales journal entry, then you would create a deposit (or journal entry) in QBO and then in the bottom portion of the bank deposit entry separate out the cash and check deposit amount used in the cash and check payment accounts during the sale entry (I explain better in the video). If you are entering the cash and checks collected during the Shopify sales entry JE under a combined Undeposited Funds account then you would just create a transfer in QBO from Undeposited Funds to your bank account to record those deposits.
Can’t this process be automated by connecting Shopify to QBO? Why are choosing the harder way?
Great video, but I am really struggling on how to incorporate the Bank Feed and the Journal Entries. Also, with the PayPal Bank feed where deposits are made and the fee comes out there (I know you explained the fee portion well - thank you). Again, its how to clear everything out from teh Bank Feed I am struggling with.
I very much need to make a video on this portion of the workflow as I’ve had many requests for this. Sorry for the delay but that will definitely be my next video made.
Gunnar, Shopify payouts received at the beginning of the month are deposits for the previous month. Therefore, to be accurate I would need to pull fees from those payouts that are for the previous month to get a true amount paid in fees for the month, correct?
Yes, that is correct if attempting a full Accrual-Basis workflow. I wish Shopify had an easier way to determine the month’s fees. I used to do this for clients where I would look at the following month’s payouts to add in those fees to the prior month but stopped for a few reasons: 1. It became more trouble than I thought was worth because at the end of the day the fees were equal anyway. 2. It lead to a potential increase chance in error if you miss some calculations and if you missed how some payouts contain sales amount for two months (one payout containing fees and funds from both the last day of the prior month and the first day of the current month), if any of these are missed at the beginning and ends of any month, then the Shopify Clearing Account will never go back down to $0. 3. (and most importantly), all of these clients were both taxed as and mostly tracking things on a “Cash-Basis”. I started to view the way shown in the video as recording the fees correctly if looked at on a cash-basis. The way I started to view it is if you receive a Payout to your bank account on July 2, but it was from sales on June 30, you could still post those Shopify fees withheld as as expense in July because the fees weren’t technically incurred (again on a cash-basis) until the payout was made and deposited in July. You can certainly click on the individual Payouts and do this math like I used to with no harm, especially if you are needing a full Accrual-Basis workflow. I decided to teach the simpler cash-basis workflow in this video for the 3 reasons stated above. Hope this makes sense and helps clarify things and I appreciate your thoroughness!
Can you key all the separate value in without using the journal entry? I dont have access to that at the moment.
You could use a Sales Receipt and positive and negatives to accomplish the same thing but you have to ensure they all equal $0 and know what you're doing when setting this up as to where all of the products/services on the sales receipt are linked to in your Chart of Accounts, I go over this method in some of my other videos, I believe in my "How To Enter Daily Sales into QuickBooks Online" video.
Greetings, so I wouldn't have to touch the entries in the bank statement, I just wait until the end of the month and make the single entry in the journal? Shopify groups several payments into one, would there be a way to reconcile the bank statement for greater accuracy or is it better not to touch them?
No, in order to complete bank reconciliations, you will need to categorize every debit and credit in and out of the bank account for the same exact dollar amounts and dates. You will still only be entering one Journal Entry to record most of your Shopify activity into your books like sales, but will still need to do something with each Shopify to Bank deposit in the Bank Feed. These will mostly be categorized to lower the Shopify Clearing Asset account. Then, you will have all Shopify info entered but also keep the capability of completing the monthly bank reconcilaitions.
Pennies off: Gunnar, almost without fail my journal entry is $.02 cents off in one direction or the other. How should I handle this difference so that I can save the journal entry?
A short potential solution would be to do this: Create an “Other Income” Chart of Account account called “Cash Over / Short” and put the pennies off, whether debited or credit into that account. A more involved solution would be to investigate why it is always a few pennies off within Shopify and then adjust the Journal Entries accordingly. This is assuming that the pennies difference isn’t happening based on the Journal Entry entries themselves but there being a legitimate difference in Shopify of Sales and amount collected within certain months.
I'm having this problem now too.
Hello, just watched your video and I have a quick question, my total sales figure on the "Sales Over Time Report" does not equal the net payments on the "Payment" report as it did in your example. Is there a way to account for this in the journal entry? I believe it is a timing issue so not sure if I need to add an additional clearing or holding account for the difference.
You definitely can add in a temporary holding account if you'd like, this would be if you are tracking monthly sales on a more accrual-basis. What I'll sometimes do instead of adding a separate holding account is use the total Payment amount and increase/decrease the Income line to match it. The logic I find behind this is for if you are accounting on a cash-basis, then the income matches what you were paid that month, then this will balance out the following month. An example would be a $100 payment timing issue, then in Shopify the month sales report is $100 than the Payment report, then I would put the payment as-is, then lower the sales (based on the sales report) by $100, that way you are not including the $100 in sales that you haven't yet received payment for, hence the cash-basis accounting, Then, the following month that $100 payment should be included in the Payments report and the sales can be reflected in that month's sales. Hope this makes sense and again, either method could work for you.
wat about points redeemed or customers earned then shopify pos braches inventory management in qbo?
How do I get these to show up in my sales on QBO?
Why did you not bundle all payment types (for the holding account) and look at the total at the top? Maybe I missed the reason why in the video.
I’m not sure if this is exactly answering your question, but I like to separate the clearing accounts based on the future avenues of deposits. With some of my clients, within the Shopify sales, they will receive separate bank deposits from Paypal, Shopify, Amazon, Stripe, etc. When this is the case, I like to separate them out into separate clearing accounts so that it is easier to audit the accuracy of the workflow if it’s broken out like this. This also makes it easier to handle things like separating out the merchant fees and everything vs trying to do that if you combine them all. Having said that, if you want to make it all under one clearing account, or if you only have Shopify payments for your business, then you can definitely do that as well no problem at all. I just try to add in as many potential scenarios as possible in these videos in order to cover as many possible scenarios as I can.
How do you subtract out the fees from sales? Seems like the sales/revenue is overstated on the financials by the fees.
In the workflow shown in the video, the fees are not being subtracted out from the sales directly as the sales are entered “Gross” and the fees are also added in separately as an expense. So, on your Profit & Loss Report, your top line sales are all Gross Sales, and then the Shopify fees are listed as an Expense (or COGS) on your Profit & Loss, leaving your Net Income (everything else excluded) as the total gross sales minus the Shopify fees. One of the reasons I prefer this method is because small business tax forms (Schedule C/1120S) ask for Gross Sales instead of Net. If you want though, you could just put in the sales using this workflow/template as net fees, you would just put the net fees amount in the Shopify income line and remove the separate Shopify Fees expense line.
What is the correct way to do this with in-person cash sales through shopify ?
It should be the same, assuming that the sales is entered at the time into Shopify via cash. As long as it shows up on the monthly Shopify reports as cash sale than everything else should be the same workflow as shown!
Hey there. My total sales on Shopify does not match my total payout figure in Shopify because there are timing differences (when the purchase was made and when it was paid out). How do I account for this on QB?
Are you referring to the "Net Payments" total amount in the Shopify "Payments” Report vs Shopify Net Sales, or the net Shopify payouts in the Shopify “Payouts” Report?
@@essentiallyintentional I have the same problem as well, Im using the Shopify Payouts Report. Since there is a delay on when shopify sends payments the sales and what is collected dont match up.
@@essentiallyintentional The payout report you use to get the fees may include fees from sales made before the desired month (e.g. order on 30th April may reflect in 2nd May payout if no payout was made over the weekend) thus the fees total is incorrect for the month of May? Or does this not matter because overtime it may balance out?
@@ethanb931 That is correct, it does though balance out over time. I used to break it down by month by clicking on certain payouts and seeing what month's sales it was associated with, by ended up finding this to simply not be worth the effort (although it can certainly be done no problem). But again, over time I've found that if not doing that then the Shopify Clearing Balance always over time ends up being brought back down to $0.
Does it actually help save time? This seems like extra work
No, this will not save time vs a direct integration. I prefer the control and flexibility of this method, but if time savings is your number one priority then you can attempt the direct integration route.
How come you show gross for paypal, but separate amazon and stripe fees? wouldn't paypal fees be a write off as well?
It’s different for different workflows and it’s up to you to determine when the fees are taken out at what stage, pre or post deposit. In my experience, when having the Paypal Bank connected to a QBO Bank Feed, you can create deposits that take out the Paypal fees then, that is what I show for that particular workflow taking the Gross Paypal amount. If in your workflow you are not accounting for Paypal Fees at the deposit stage, then you can take net Paypal and add in the expense fees during the sales entry stage with no problem. Hope this helps!
@@essentiallyintentional So what I have ended up doing is going through the monthly statements on PayPal and just adding a line for them on the journal. Unfortunately with paypal, they show all fees in their native currency, so i have to do conversion rates. But it works out in the end. Thank you for the response!
Couldn't find anyone on TH-cam who explains this as well as you. Thank you!!!
Thank you for this comment and feedback! Much appreciated. Glad to help.
What are the most common errors here? I cant for the life of me figure out why I'm off 24.48 I will say this is my first month of cash sales if that makes any difference?
Sometimes it’s a timing issue vs entry, I would first verify the amounts match (or don’t match) for the “Total sales” in the sales over time report and the “Net Payments” from the payments report for the same date range. If the do match then it’s most likely an entry error. If they don’t match, then first verify is the mismatch is the same dollar amount you listed and then research from there, mainly looking at what is included in the payments amount and whether it should be counted as cash sales for that month and if you deal with any open invoicing or anything within Shopify as well then that could complicate things potentially also
@@essentiallyintentional I am also having the same issue. I can't figure out why I am off by 79.03 in my journal entry (debit has the larger total number). My Net payments and Total Sales are off by that amount in Shopify. Any idea why would that be?
I am a small business and do my own bookkeeping, so I am hoping your method will help me stay more on top of my accounts.
@@SimpleMama47 There are many different possibilities that could cause that, that it would be hard for me to say without further investigation. Essentially, however Sho[ufy is calculating the sales number on the one sales report, vs the payment collected total is sometimes a little different (again for many different potential reasons). When I’ve ran into this with clients, I’ve always (because we are tracking sales on a cash-basis), kept the payments as-is and adjusted the sales number if needed to match the payment collected. Doing this has always kept the Shopify clearing account accurate and eventually getting back to $0, and I feel the sales accuracy is correct, again being on a cash-basis, because you are counting sales based on the actual total funds collected (after sale tax and things of course). So, you could either doing some investigating within Shopify to see with your particular workflow and business how they are calculating each number and going from there, or adjust your sales in the JE to $79.03 higher (or do both). When investigating within Shopify, some things to look at are your individual payment methods and collected, and if they all represent dollar for dollar what’s reflected on your sales report (I’ve seen “Manual” payment type in Shopify being used for order errors to reship products, so the numbers was increasing the net payments number but not the sales. Or, if there are ever any open invoices, POs, draft orders, etc. Sometimes these can cause the numbers on the two reports to be different. Hope this helps.
How do you account for deferred revenue? Could you make a video on how to record this? Sometimes the report recorded the sales includes all orders for the date in question and not always it ships in the same month.
If I ever make a video for this I will let you know. The short answer would be figuring out the debits and credits needed for your workflow and either working it into your Monthly Journal Entry Shopify Template, adding in a second Journal Entry Template, or a combination of the two. If at any point you would like to schedule a one-on-one with me so I can help make a specific workflow for you (and ask me anything else QBO-related, you can book a session here: calendly.com/essentially-intentional/training-with-gunnar-1-hour thank you for watching!