Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals

Profit - Margins - Sales

Sage 50 general help forum - Free help and support for all general issues
Post Reply
AndrewP
User
User
Posts: 5
Joined: 24 Aug 2017, 17:01
Sage Version: v2017 23 UK/Europe/Africa

Profit - Margins - Sales

Post by AndrewP » 24 Aug 2017, 17:39

Hello All!

I have spent all day trying to extract some data from Sage - Current and historical back to July 2017 by month.

Long story short all I am after is historical data by Stock Category T/O and Profit excluding certain customers. I managed to do this using invoices. So spent the afternoon running the reports by months. Took ages as Sage is slow working each one out.

I got to July 2016 and a little alarm rang, it should have rang earlier but all the margins were really similar once I got to over 3 months and past a price update. I then discovered that the invoicing is not Frozen in time? Sales price is but cost is todays last cost price even looking at an invoice from 2016. It really surprised me? I really thought that once updated all the data from that should be literally frozen. Even running a report in a month could get corrupted if you have had a lump of popular stock arrive and it has gone up or down dramatically.

Anyway I feel sure product profit run at the end of the month would be more accurate, or at least closer as it looks like it uses average cost price. I cannot see away to exclude certain Account Ref?

Is anyone else surprised invoiced profit is not frozen at point of clicking update? You would think it was relatively simple exercise.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Profit - Margins - Sales

Post by brucedenney » 25 Aug 2017, 11:39

Hi Andrew

The issue is that the cost prices are stored in the Stock transaction tables, it sounds like the report or whatever that you used is picking up the last cost price from the stock table.

What you need to do is to work out margins on the products using the info from the stock transaction table.

That is not much use if you want to know by cusotmer so you need to either

Link the invoice to the stock transaction so you can find the cost price for that particular transaction
or
Link the stock transaction to the invoice table to find the customer.

Hope this helps.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

AndrewP
User
User
Posts: 5
Joined: 24 Aug 2017, 17:01
Sage Version: v2017 23 UK/Europe/Africa

Re: Profit - Margins - Sales

Post by AndrewP » 28 Aug 2017, 14:50

Not sure how to that to be honest!!

I have tried editing "Product Period Profit" (By Transaction)as this report is using the cost at the time of the transaction. SAge even go to the trouble of informing you in the description that the historical data will be accurate.

In the editor I go to Report --> Filter and that opens "Expression Editor" By trying to isolate my main customers Nominal Account of 4100 The customers I want to exclude each have their own Nom Sales Number (For P&L Purpose) but because I work on small margins for these they skew just about every other report in Sage! I have searched the Sage website and found it really hard to find any instructions on using the Expressions. So this is what I pieced together and it still didn't work. The only bit I added was in bold. If its a report you could write happy to pay for it!

STOCK_TRAN.TYPE LIKE "GO" OR STOCK_TRAN.TYPE LIKE "GR" OR NOMINAL_LEDGER.ACCOUNT_REF LIKE "4100"

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Profit - Margins - Sales

Post by brucedenney » 28 Aug 2017, 15:22

I think you need AND not OR and some brackets
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

AndrewP
User
User
Posts: 5
Joined: 24 Aug 2017, 17:01
Sage Version: v2017 23 UK/Europe/Africa

Re: Profit - Margins - Sales

Post by AndrewP » 01 Sep 2017, 09:45

Thanks Bruce - I gave up trying to find instruction online from Sage and called them so in case anyone searches and find this thread. Here is the Sage expression operators To exclude certain things from a report. Guess now I know this can use in other reports. The thing added to the end of the standard Filter is in Bold. So right click on the reort you want to change -- Edit --- Report at the Top --- Filter --- ADD to Expression --- Report --- Properties - Change Name and save as.

STOCK_TRAN.TYPE LIKE "GO" OR STOCK_TRAN.TYPE LIKE "GR" AND NOMINAL_LEDGER.ACCOUNT_REF not in ("4130", "4110","4160")

I was close but no cigar!

If you are reading this Bruce, do you do or could you write anything to import a POP or SOP via Excel just really Product code and quantity? I upgraded to 365 just to get the email about the automatic back up confirming it went through and no data errors and was hoping integration meant a bit more than some basic auto reports in Excel.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Profit - Margins - Sales

Post by brucedenney » 01 Sep 2017, 10:55

STOCK_TRAN.TYPE LIKE "GO" OR STOCK_TRAN.TYPE LIKE "GR" AND NOMINAL_LEDGER.ACCOUNT_REF not in ("4130", "4110","4160")

Looks slightly ambiguous to me. I would add brackets to make it clearer.

(STOCK_TRAN.TYPE LIKE "GO" OR STOCK_TRAN.TYPE LIKE "GR") AND NOMINAL_LEDGER.ACCOUNT_REF not in ("4130", "4110","4160")

For your second question, yes, we can import most sorts of transactions from all sorts of sources. Give me a ring on 03333 445950 and we can chat.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

AndrewP
User
User
Posts: 5
Joined: 24 Aug 2017, 17:01
Sage Version: v2017 23 UK/Europe/Africa

Re: Profit - Margins - Sales

Post by AndrewP » 01 Sep 2017, 17:41

Hi,
Been out all day came back and ran the report it didn't work! HA Good old Sage Support. Will try yours and give you call.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Profit - Margins - Sales

Post by brucedenney » 02 Sep 2017, 11:05

Have you used pivot tables in excel?

If so, I would pull the data from invoicing and stock trans into one big table in excel then do the selections and exclusions using a pivot table to get the bits you want.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

Post Reply

Who is online

Users browsing this forum: No registered users and 13 guests