Why are items and expense dates treated differently in reports

Cathi P
Cathi P Member Posts: 19
edited January 2020 in Reckon Accounts (Desktop)
I run a report on my fuel use to export and calculate the diesel rebate for my BAS.
Thinking it would be easier to export to excel, I changed the way I entered my Bills from using Expenses to using Items (but with the item coded back to the same expense account). This enabled me to enter a Quantity for each line, so less information to split from the description/memo column when I exported.

The problem is that while it pulls data entered directly as an Expense by the date it was paid, it is not doing the same for Items. For Items, it is pulling them by the date of the bill, not the date paid. I have been running the report on a cash basis, by the quarter, as I want to report on what is paid in that quarter, regardless of when it was purchased, but tried switching to accrual (just in case) and still have the Expenses and Items treated differently.

Can anyone explain why it does this? And more importantly, how can I make it bring the information to the report based on the date it was paid, not the date on the bill.

While it is annoying that there isn't a simple way to enter this information that allows Reckon to offer a fuel report for my BAS, it is even worse that I can't pull the data in a way that is easy to export and use in Excel without a pile of fiddling around once in excel. I would just sort it all by class and date, however the dates don't all cross in the same format - with some crossing in US format (and so reversed/misread) and some crossing as text which may or may not convert correctly in excel. This is doing my head in!

Comments

  • Charley
    Charley Member Posts: 541 ✭✭✭
    edited January 2020
    Cathi, I wouldn't bother, I'd just set up a spreadsheet and enter fuel amounts and whatever else needed; I know it seems to be double entering but you'll probably find that it's heaps quicker than trying to manipulate reports each quarter. I take it that there is a fair amount of fuel bought.
  • Graham Boast
    Graham Boast Accredited Partner Posts: 331 Accredited Partner Accredited Partner
    edited October 2018
    Hi Cathi

    Below is a Bill that I have entered to replicate your item setup.  I have dated this bill in November, but paid it in December.
    image

    To work out your diesel fuel rebate, modify a report as follows:

    Reports > Purchases > Purchases by Item Summary
    Modify Report as follows:
    Display Tab
    Date = Last Financial Quarter
    Report Basis = Cash
    Display Columns by Class

    image

    Filters Tab
    Item = Diesel

    image

    Header/Footer Tab
    Report Title = Fuel Tax Credit Purchases

    Now memorise the report so you can run it each BAS

    The report looks like this

    image

    If you double-click on the total amount, you can see that it picks up the November Bill against it's December payment date.


    image

    No need to go anywhere near Excel!

    Hope you find this useful.


    Graham Boast
    Reckon Accredited Consultant
    graham@reckonhelp.com.au
    www.reckonhelp.com.au/remotesupport.htm


    Graham Boast | 0409 317366 | graham@reckonhelp.com.au

  • John G
    John G Reckon Staff Posts: 1,570 Reckon Staff
    edited October 2018
    Hi Cathi,

    Thanks for asking the Reckon Community.

    Is your item an "inventory part", "non-inventory part" or "service"?

    When you purchase an "inventory part" it is placed in the Inventory Asset account on the Balance Sheet.  When you sell that "inventory part" the cost is transferred from the Inventory Asset account to Cost of Goods Sold Account on the Profit and Loss Report.  This act allows you to determine the Gross Profit of sales.

    The funds flow is a different matter.  Your payment of "inventory parts" is independent of your Sales.  That is, you acquire the  "inventory part" on one date (the date of the bill), pay it on another day (Date of Payment) and sell it on other days (a range of dates).  

    When the cost of your "inventory part" is recognised depends on your Accounting basis.  Accrual basis recognises the cost when the liability is incurred (date of bill).  Cost basis recognises the cost when the bill is paid (date of payment).  When the "inventory item" is sold does not figure in this consideration.  

    "Non-inventory parts" and "services" are recognised when incurred, depending on whether Accrual or Cost basis is used.  

    Accounts Business stores information on an Accrual basis.  Cost basis reports need to modify the reporting output for date paid and not the date of the transaction.  This is why cost based reports take longer to display than accrual based reports.

    I hope this explanation helps you find the appropriate solution for your needs.

    In relation to the Excel problem of dates, that is a known issue with Excel's integration with 3rd party programmes.  It is not a problem that affects only Accounts Business.  We do have a workaround for this issue in our KB:  Why does the date format change when I click on a date field in the Excel version of a report?  How can I keep the dd/mm/yyyy format?


    regards,
    John






  • Cathi P
    Cathi P Member Posts: 19
    edited September 2014
    After a good 15-20 minutes wondering where the Purchases section of Reports was hiding, I edited my preferences to allow purchase orders/inventory and suddenly it was there! Thanks for your suggestion, and the great walk through on method.

    This is a great solution long-term once I sort out the dates issue. Unfortunately on my system, it is still picking up the dates incorrectly. So for a Bill issued in one quarter and paid in another, it is actually showing up in both quarters. Previously I thought it was just showing up on the Bill issue date, but checking the next quarter shows that information in there as well. Since this info will be used to submit a claim to the ATO, it's pretty damn important that I am not duplicating (and incorrectly increasing) my claim.

    Is this sounding like something I should refer to tech help? Or are there things I could have done in setting up the items (or something else) that is creating these duplicate lines and causing it to report in the wrong time frames?
  • Cathi P
    Cathi P Member Posts: 19
    edited September 2014
    Previously we were able to pull from a monthly report from the fuel company and enter into a basic spreadsheet - but that is not available to us any longer. It was not too lengthy as I was only grabbing beginning and end data and totals, after verifying entries.

    Now I'd be looking at having to enter much the same info as I'm already inputting to Accounts anywhere up to 100 entries for a 3 month period. Doesn't sound much, but as the sole "business admin" I'm loath to add any more work to what I already do.

    It should be possible to do from Accounts - and hopefully once I can find out why there are issues with the dates, Graham's solution looks like it will be just what I need. (But I do take your suggestion to bear the time it is taking me in mind)
  • Cathi P
    Cathi P Member Posts: 19
    edited September 2014
    Hi John,

    I'm entering it as a non-inventory part, so I would think that it should be bringing up the date paid when I run a Cost basis report. As I mentioned in my reply to Graham, I can now see that it is bringing up the Billed date in the earlier quarter (where it was purchased), and also showing in the next quarter (where it was paid).

    I don't understand why it is doing this, though it is possible I've set the items up in a way that is causing errors. Why would it be bringing both dates into reporting?

    Thanks for the link regarding date issues with excel. I'll make sure that I don't export direct to excel, but using csv ion the future. At least that is 1 issue solved!