Profit and loss report disparity between item ledger

[Gavin_Smith]

It is very hard for me to extract the number of each line item and the value of those items from quickfile. The sales item report does not show the actual value of each item sold. It is incredibly important that we know which of our products are popular/profitable etc.

To extract these I have downloaded the quickfile back up and used the “Sales_Invoice_Items”, filtered it for the financial year and categorised these in an excel document taking about 2.5 hours for 1800 lines. The result is that each item is now categorised. However, I see the total of this is more than the profit and loss account ‘general sales’. Why would this be?

[QFSteve]

Hello @Gavin_Smith

Has anything else been posted to the general sales code (journal/direct tagged bank transaction)?

Use the search option in the general sales code to filter by category.

Does the “Sales_Invoice_Items” file match your profit and loss account ‘general sales’ figure prior to the edits/formatting?

[Gavin_Smith]

The general sales in profit and loss is about 25% less than the 'Sales_Invoice_Items".

When I try to search the leger as outlined, the only result is for Sales invoice. All other searches have 0 results.

The “Sales_Invoice_Items” file does not match my profit and loss account ‘general sales’ figure prior to the edits/formatting

[Gavin_Smith]

Another 2 hours investigating this; it seems that “Sales_Invoice_Items” includes estimates. Why is that?

Removing the estimates means the “Sales_Invoice_Items” and the “Sales_Invoice” now have the same sub total for the 23-24 year.

[QFSteve]

Hello @Gavin_Smith

All the sales items are stored in the same place

The Sales_Invoice.csv file also includes all estimates and recurring files

The IDs in the items file matches up to the IDs in the invoices file.

Do you use separate numbering sequences for quotes and invoices?

Can you please provide the company name and account number so I can take a look.

But just to set your expectations, QuickFile was never designed to be inventory software and offers no inventory capabilities.

[Gavin_Smith]

Hi Steve,

Yes quickfile’s attitude to the tracking sales is rather disappointing given that knowing which of your products are profitable and how many are sold is fundamental to businesses.

You are incorrect, their is some inventory capabilities. It is just extremely sub par compared to the competition.

I can’t fathom why estimates would be lumped with sales invoices.

On the sales items list the only way i could separate them is that the ‘invoice number’ were extremely different.

[QFMathew]

Hi @Gavin_Smith

Thanks for your feedback.

There is a way of doing what you want, but it requires a little bit of spreadsheet magic. I’ve outlined some steps below for you.

1. Open the Sales_Invoices.csv file in spreadsheet software (e.g. Excel, Calc, or Google Sheets)
2. Open the Sales_Invoice_Lines.csv file and copy the data into a new tab in the Sales_Invoice.csv file, so you end up with 2 tabs, similar to this -
   
3. In the Sales_Invoice_Lines tab, add a column to the end, let’s call it Type, and copy and paste this formula into the first cell with values (in my case, this is L2):
   =VLOOKUP(A2,Sales_Invoices!A:Q,2,false)

This should now display the type of document it relates to - Invoice, Estimate or Recurring.

From there, you can then apply a filter to the column, too, using the standard functions in Excel.

Hope that helps, but if you need anything else, please let us know.