How do you manage your Sales Tax preperation?

AshleyAshley Accounting Supervisor

I was curious how other Breweries manage their monthly and quarterly sales tax returns using OBeer? Right now I keep a list in excel of vendors that I don't need to report on, and ones that I do. Then I go through our G/L's and pick out the vendors, based on my excel list, that need to be reported. And then put that info into another spreadsheet.

Ultimately I would like to find a way to handle as much of this as possible in Obeer. I was thinking a possible field in the BP screen, or something along those lines. Then I would either need to build a query to pull AP invoices for these vendors in a certain time period, or better yet, find a pre-built report in Obeer.

We're in CA, so if any other breweries would be willing to chime in I would appreciate it. I would much rather spend my time analyzing the info rather than gathering it! :-)

Thanks in Advance!


  • ZachZach Business Process Manager

    If you go the query route then the FROM tables OPCH and PCH1 should be all you need to run this report. WHERE would be T0.[DocDate] >=[%0]
    AND T0.[DocDate] <=[%1] to give you a monthly or quarterly date range.

    If you post a link to demo report that CA made or description of what needs to be in each column then I'd be willing to take a crack at it.

  • AshleyAshley Accounting Supervisor

    Thanks Zach! I guess for the query to ultimately work I would need to choose a field in the BP screen to "mark" certain BP's to show up on the query. The info I'm pulling out manually from the chart of accounts is BP Code, BP Name, Posting Date, Ref #, Paid?, Total amount due. I look at a variety of accounts, too many to name, to pick out items that we owe sales and used tax on. I have a list of vendors that I know either already charge us tax, or because of the nature of what we purchase we may not owe any tax. So I skim the accounts and look for the vendors that are not on my cheat sheet. It's pretty time consuming, and that's why I would like to find a better method of obtaining the info.

  • ZachZach Business Process Manager

    If you got a cheat sheet then you're half way to codifying the parameters of your SQL query! As for the tax aspect, if you can predefine items as being "sales and used tax" specific then this would provide a way to use WHERE to find only items you need to report on. To do this I'd suggest modifying the Items Master Data by creating an User Defined Field to mark items as taxable or not. I feel like once you identify what items you need to report on by using UDF tags then pulling extra details (vendor names, costs, quantities, etc.) would be relational problem, which SQL queries solve. Setting tags and writing the query might take a good day or so but in the long run this could save you some serious time! Good luck Ashley! Let me know how it goes.

  • AshleyAshley Accounting Supervisor

    Thank you again Zach! I really appreciate you talking this over with me! The problem with your suggestion above is that 99% of the AP invoices I'm searching through are service style, not item style. That's why I was thinking if I could mark the BP then I could see any invoices during a specific time period created to them. I just feel like somebody out there has to have a system for this, that's working! lol I can't be inventing the wheel here.

    On another note, we've had OBeer help us make some UDF's before so I'm familiar with that. But this is part of the fun of solving puzzles hu? hehe

  • ZachZach Business Process Manager

    Omg, it is a puzzle. It is nerdy AF but I serious treat stuff like this as a game. It makes work more entertaining and less of a job.

    Okay, so services is what you're trying to isolate on. Hmmm... Maybe adding an a "Tax Liable" column on your AP invoice by clicking on "Form Settings" on the icon ribbon, Table Format , and then toggle on Tax Liable would do it? You can also add UDF for documents as well. I would suggest going the UDF on the AP document route since you can edit UDF after a document is added! This would be totes useful if you're not always entering AP invoices and someone forgets to change the "Tax Liable" field for each line item on each invoice. You could always go back and tag invoices that need to be used in your query. However, if you mark an invoice with UDF then every line item would be called in a query. That could be a problem if you have 2 line items (I dunno if this could/does happen) where 1 line is being taxed and the other line is not taxed because both items would considered a taxable item in the report by the UDF. (This makes so much sense in my head but I'm not sure if I am explaining it well!)

  • AshleyAshley Accounting Supervisor

    Clearly we're both nerdy! When I try to explain to people why I like being an accountant, most of the time the only thing they get right off the bat is that it's like solving puzzles all day long.

    I've look at the tax liable field before, but don't want it adding tax into the grand total of the invoice. I haven't played around with this enough to find out if you can have one set that doesn't auto generate a tax rate. But now that I'm typing that out, I guess it's possible b/c of the EX option that's defaulted! And then you could set it at the BP level to be a default on documents.

    I didn't realize you could use UDF's in an AP invoice. This is very interesting! You could almost do a weekly audit of the report and compare to the GL's we look at. That would cut down the amount of time later on when we actually have to report. And with training our AP person could learn to mark certain invoices.

    I wonder if there's a way to do something similar to a project code? Or possibly use project codes? We have been using them with our distributors, and have them set at the BP master data screen. Then the AR invoices auto. fill when created based on what's been set. But it doesn't always work, and it doesn't auto fill on CM's.

    I've briefly looked into the Property settings also, but haven't had the extra time to go down that rabbit hole.

Sign In or Register to comment.