Reporting
Space shortcuts
Page tree

Versions Compared

Old Version 21

changes.mady.by.user Jan van der Hilst

Saved on

compared with

New Version Current

changes.mady.by.user Jan van der Hilst

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

This overview will create a profitability percentage per item while having the flexibility to change the report with the multiple buttons/options.
CalculationExplanation
Standard price per pack

Standard sales price used as starting cost for deducting all the other expenditures.

This price is picked up from Pricelist 1 from Sage.

Cost of material

  • Based on BOM
  • Average Unit cost

The two unit prices are displayed, while only using the Average Unit cost for the calculations.

The Average Unit cost will be used to be deducted from the Standard price.

Sales

  • Qty sold
  • Litres sold
  • Revenue
  • % of current item - XXXX
  • % of total XXX - Segment
  • % of total XXX


FieldExplanation

Qty sold

Number of packs sold for the selected item for the selected period. Calculated based on sales figures.

Litres sold

Number of litres sold for the selected item for the selected period. Calculated based on sales figures. The quantity is multiplied with the UDF ufIIPACKLTR of the stockitem to get a litre sales total.
RevenueRevenue for the the selected item for the selected period. Calculated based on sales figures.

% of current item - XXXX

XXXX = Litres or revenue based on the selected option in the menu.


Show the XXXX % per segment vs the total XXXX for the one item. This is used to allocate the Production expenditure to a per segment amount.

(Summing the percentages within its own items for all the segments, will add up to 100% )

Note that this is not the same as "% of total XXX" as this checks it against the total of all items.

% of total XXXX - Segment

XXXX = Litres or revenue based on the selected option in the menu.


Shows the % XXXX of grand total for the current segment. Note that summing all % from left to right should add up to 100%.

Note that based on this figure the Expenditure of report 1 -5 will be allocated to the item.


% of total XXXX

XXXX = Litres or revenue based on the selected option in the menu.

Shows the % of grand total of the XXXX of all items loaded into the Dashboard. Note that summing all % from left to right should add up to 100%.

Exfactory

  • Variable cost
  • Fixed cost

Contains the amount allocated to the item (or segment underneath if option selected in the dashboard). This amount is split based on variable and fixed UDF (ulGLGroupType).

Example calculation:

WhatValueNote
Item30001
PeriodOct
Get the per line % of total

  Line 1 Qty = 151,613.00 = 20.51%

Line 2 Qty = 33,713.00 = 3.98%

Get these figure from the page called "11) Stock % per line". Note that this is calculated per line.

Image Modified

Note that this percentage is based on bottles sold per line

Get the line totals

Line 1 = 589,692.48

Line 2= 623.300.15

Get this from the page "1) Production expenses". Scroll to the bottom to get the totals for the line.

Image Modified

Note if you want can filter the overview with Fixed and Variable to check them individually.

Multiply the figures

Line 1 = 589,692.48 * 20.51% = 120,945.93

Line 2= 623.300.15 * 3.98% = 24,807.35

Total = 145,753.27


Note the total will be the value for the ALL Segments (or the collapsed version of the matrix with out the segment level)
Get the per segment value

In this case Item 300001 only has sales on DT:

So % of current item - revenue = 100% for DT

145,753.27 * 100 % = 145,753.27

Image Modified

Note that there is a minimal CRN on MEGA, causing the 1.38 value



Logistic

  • Variable cost
  • Fixed cost

Contains the amount allocated to the item (or segment underneath if option selected in the dashboard). This amount is split based on variable and fixed UDF (ulGLGroupType).

Example calculation:


WhatValueNote
Item300119
PeriodOct

Get the per line % of total (for ALL or per segment)


ALL = 15%

DT = 13.22

Horeca = 3.68

KA = 18.50

Mega = 30.08

Based on the selected option this can be either Revenue or Litres based. For this example we are going for revenue based

The percentages are found in the section under Sales in the same Matrix:

Image Added

Note that the percentages are rounded on 2 decimals

logistics expenditure amount per segment and all


DT = 312,927.28

Horeca = 186,937.19

KA = 200,111.16

MEGA = 128,912.54

All = 828,888.17 (Sum)

Note that these calculations contain the values based on the SDW and SDWII calculations (See litres calculations for explanation)
Multiply it
Image Added

Note that I calculated the percentages back based on the amount allocated in the maxtrix. (This for an accurate percentage allocation.)

If you take the percentages from the step above, you will have slight rounding differences.

Sales

  • Variable cost
  • Fixed cost
Same calculation/split as Logistics

Delivery Costs

Total of production + Logistics + Sales

Marketing

  • Variable cost
  • Fixed cost
Same calculation/split as
Sales
Logistics

IT & Admin

  • Variable cost
  • Fixed cost
Same calculation/split as
Sales
Logistics
Total cost

Sum of Delivery Costs + Marketing + IT & Admin

Note that if you are looking at the overview with the option Cost per item, Average Unit cost will contain the value of CoM - Average Unit cost

RebatesSame calculation as Sales

Discounts

Calculated by taking the sum of the difference between the price of the item on the invoice vs the price of the pricelist 1.

Note that you can find this breakdown in the Dashboard/report Sales dashboard. By looking at the sum of the discount columns

FOC

Looks at the FOC equivalent of the Item. Calculated by multiplying the Average unit Cost of the item with the quantity on the invoice.

Note that you can find this breakdown in the Dashboard/report Sales dashboard. By looking at the sum of the COGS columns (First filter the dashboard to only show FOC items (bottom right)

Profit

  • Net price to trade
  • Profit per pack
  • Profit % per pack

Net price to trade =

Pricelist price 1 - (Rebates) - (Discounts) - FOC)

Note this includes the deduction of rebates!

Profit per pack =

Pricelist price 1 - (Average Unit cost) - (Delivery cost) - (Marketing) - (IT & Admin) - (Rebates) - (Discounts) - FOC)


Profit % per pack =

Profit per pack / Net price to trade






8a) Performance overview - per SKU

...