Custom Report P&L profit and loss

Hello, i have already established everything. things are working great.

the owners asked for a P & L report. so i installed the custom report module. and i dont know how to do it…
i have a server SQL database . can it be done through tags ? or it cant be done at all ?

the P&L report they want to show
Item name , Sold items , participation sales , Sales Value , COGS , COGS% , Profit Margin , Profit Value , Profit % Cogs.

please find the attached photo

Thank You So much

I don’t think this is actually a P&L report as P&L would typically show overheads/non COG expenses etc. to show your overall bottom line but either way.

Something like this should be achievable but don’t expect it to be a simple one line report tag.

where do i begin ? :slight_smile: im trying my best but im not a pro. and thank you for your reply

I don’t think V4 has Inventory Report Tags, does it? If not, you won’t be able to do this in V4 at all.

how about V5 ?? any luck with it ?

Yes, with V5 it should be doable.

1 Like

I can prepare this report if someone can explain me how to calculate these values.

2 Likes

Looking at the scan it looks like

PLU (not required for samba)
Product Name (self explanatory) = Menu Item Name
QTY Sold (self explanatory) = Total Product QTY
Participation Sales = % of Qty Sold (looks like qty sold % not value) = (Total Product QTY / Total Orders QTY)
Sales Value (self explanatory) = Product Sales Total
Cost of Goods (self explanatory) = Product Cost Total
Cost of Goods % = (Product Cost Total / Product Sales Total) x 100
Profit Margin % = ((Product Sales Total - Product Cost Total) / Product Sales Total) x 100
Profit Value = Product Profit Total ( Product Sales Total - Product Cost Total )
Profit % Cost of Goods = Return on Investment ( Profit / Product Cost Total ) x 100

OK, think that’s about right, the number formatting threw me a little for a minute but that should be right.

2 Likes

lol :slight_smile: i guess so… i was trying to figure it out too :slight_smile: . im just a computer geek :slight_smile: … and thanx for your effort . ur a star :slight_smile: .
i guess you got it right…

doable on V4 ? custom report ? or you mean V5 ?

I would expect me means V5 as V4 isnt getting any more updates and if QMcKay is right about inventory report tags defiantly.

We can configure it to show anything as long as we know how we are getting the numbers.

I mean a PNL is subbjective and depends on how the owner wants it calculated. Explain what should be included for each column and what should be calculated to get it. If you want to include expenses them we need to know how they are being recorded etc.

I would imagine we need v5 for this.

Thank you so much…
Would that be enough for you ? Or you need me to dig up more info. I can ask the Accountant.
Product Name (self explanatory) = Menu Item Name
QTY Sold (self explanatory) = Total Product QTY
Participation Sales = % of Qty Sold (looks like qty sold % not value) = (Total Product QTY / Total Orders QTY)
Sales Value (self explanatory) = Product Sales Total
Cost of Goods (self explanatory) = Product Cost Total
Cost of Goods % = (Product Cost Total / Product Sales Total) x 100
Profit Margin % = ((Product Sales Total - Product Cost Total) / Product Sales Total) x 100
Profit Value = Product Profit Total ( Product Sales Total - Product Cost Total )
Profit % Cost of Goods = Return on Investment ( Profit / Product Cost Total ) x 100

Thanks in advance. I don’t understand much of accounting. So forgive me.

That’s fairly simple yes that should be doable with current tags in v5. I have not tried yet but I don’t see why that’s not possible now.

The profit part might need some SQL to temporarily store some values but either way what you showed should be possible.

1 Like

I just took a look at a V4 installation and I do not see any Inventory Report Tags. They were introduced in V5. You need the Inventory Tags to be able to track Consumption and Cost and then Profit Rate.

It could be doable using SQL in V4, but I am not going to attempt that. What we have already available in V5 works very well.

1 Like

I don’t understand participation sales but for the rest I’ve prepared something with V5.

[PL Report:5, 3, 3, 3, 3, 3, 3, 3]
>Name|Qty|Sales|Cost|Cost%|PM%|PM|P%
{REPORT COST DETAILS:[C.Name]+'.'+[C.PortionName],
C.Quantity.Sum,
[([C.AvgPrice]*[C.Quantity]).Sum],
[([C.Cost]*[C.Quantity]).Sum],
[($4/$3)];%##.00;-%##.00,
[($3-$4)/$3];%##.00;-%##.00,
[($3-$4)];#.00,
[(($3-$4)/$4)];%##.00;-%##.00}`
3 Likes

thank you so much… thanx a million. i will show this to the owners and will buy V5 deff…
but the Q is… will i be able to import my database SQL server V4 to V5 ? with no problems ??

Thank You again.

I am the Q.

The one and only :stuck_out_tongue_winking_eye:

No problem.

it don’t show database module… for some reason… maybe cause i have it as SQL ?? i have a server and 2 terminals, and i connect to SQL string… ?

I believe it is the % of units sold of a product from total qty of all products sold.
Similar to the % value for product group on work period but by qty rather than value.
Ie 100 x £1 coffees would give higher % than 4 x £25 steaks.
In an example if sales for a period were;
100 x Coffee £1 = Total Product Sales @ £100
4x Steak £25 = Total Product Sales @ £100
50 x Beer £4 = Total Product Sales @ £200
The sales % as on work period would show;
Coffee = £100 = 25%
Steak = £100 = 25%
Beer = £200 = 50%
Total Sales = £400 (100%)
The participation sales would be based on QTY giving these results;
Coffee = x100 = 64.9%
Steak = x4 = 2.6%
Beer = x50 = 32.5%
Total QTY= x154 (100%)