Need a Sales Report that includes deposits (without tax)

I'm trying to find a way to create a report for "Gross Sales" which includes all payments received (whether deposits for orders or layaways, not just cash&carry sales) but pre-tax. Many landlords require this and consider any and all payments received, regardless of when the sale is completed or if it is, as "Gross Sales". Even if there is a workaround to get these numbers. The issue I'm running into is if I simple just combine the sales report in lightspeed with the report of open orders at the end of the month, and I do the same the following month, I would be running into duplicates if the prev. months open order totals turned into completed sales for the next month. I hope this makes sense. As an FYI, I also signed up to lightspeed accounting and quickbooks online. I haven't set it up yet, but if there's a way through the integration to achieve what I need, that could work also. Prev to transitioning to lightspeed, we manually tracked everything in a ledger from an old register.
Tags:
6 comments
Our initial questions are:
Do you have Lightspeed analytics?
Do you have any programming experience?
Adrian Samuel
Software Developer
Lightspeed HQ
@josephmckeown
Hey! I don't have analytics and limited programming experience. What were you thinking in terms of a solution?
I have background and experience with our Product's APIs and thus might be able to put forward some ideas as to how to produce such reports if you have a programming background
Adrian Samuel
Software Developer
Lightspeed HQ
That would be great if @josephmckeown could do that for me. In the meantime Adrian, what were you thinking? Maybe I can put something together myself with your help and my limited ability to program?
Here would be my programmatic method:
Go through the sale endpoint like so:
As you can see in this GET/Read request the saleID is fixed so you'll need to loop through all the sales via their IDs throughout your given time range.
Already we're only receiving completed transactions and not those that our pending.
We need to apply a set of conditions in our application logic:
Adrian Samuel
Software Developer
Lightspeed HQ
Some easy solutions that exist in Retail are:
- Showing all Payments from the Payments report, then subtracting the taxes from the Sales report. (which I think gets you closest to the number that you are being asked to provide)
- Showing Sales only, before and after taxes are applied
- Showing all Payments and Deposits after taxes are applied
Showing Payment Methods pre-tax becomes a problem because of this situation:Say that a customer buys a $100 jacket, on which there is $10 tax. The customer owes $110. They pay with $60 cash, and put the remaining $50 on their Credit Account. Where is the tax being collected?
Now we could possibly come up with a Payments report in Analytics, in which we used Calculations to re-create a pre-taxed value by payment method. There is an illustration of a similar solution in the Products by Payment Method article, for your consideration.
Thanks for the great question Rishi. Please let us know if we can help with anything further.
Lightspeed Analytics and reporting consultant
Lightspeed HQ