Page History

Introduction

The dashboarding will show the profitability per item per segment. Considering the overhead expenditure captured on the GL accounts inside Sage Evolution.

...

Options: Budget; Direct; Litre; Packs; Pcs;

Options

Calculation

Budget

Allocated the full GL balance based on the selected calculation option of ulGLBudgetCalculationOption. The dashboard page "9) Budget overview" contains an overview of the budget table content and the calculations for allocating to Segments per calculation option.

It calculates the % of the grand total of the selection option as a percentage to use for the expenditure allocation.

Note that this option is not available for production/line based expenditure

Direct

Allocated the full GL balance to the indicated Segment. The segment to allocate to is specified in the UDF ucGLChannelSegment (segment relates expenditure) or ucGLLineSegment (production line based expenditure) . Thevalue inside these fields should be build as followed two examples:

Line/Segment	outcome
KA-100	100% of the balance will be allocated to KA segment
L1-50;L2-50	50% of the balance will be allocated to L1 segment and 50% to L2

Litre

Allocated the full GL balance based on the percentage litres sold per segment for the selected period.

Based on the SalesRep.Address3 field Sales is allocated to a specific segment. Based on this field a % of total is calculated per segment. These percentages are available in the page "10) Sales & Production overview"

Example calculation

Expense GL Balance	1,000
Total litres sold	2,500
Sales litres per segment	Qty	Percentage
KA	1,000	40%
DT	200	8%
BPlus	800	32%
Horeca	500	20%
	2,500	100%

Cost per segments:
KA	400
DT	80
BPlus	320
Horeca	200

Note that Logistics requires an additional value in the UDF ulGLWarehouse : SDW;SDWII;Both

Based on the selected option it includes/exclude sales made via the SDW or SDWII in the percentage of total litres/packs sold.

This field corresponds with the value SalesRep.Bank_Account on the invoice.

Packs

Same as litres, however it looks at packs sold instead of Litres sold.

Pcs

Allocated the full GL balance based on the percentage of pcs manufactured per line for the selected period.

Example calculation

Balance on GL expense account	7,500
Total Qty produced for period	5,000
Selected Line. (in UDF)	Should be set to all

Qty per line
L1	1,300.00	26%
L2	600.00	12%
Cup	1,200.00	24%
Glass	1,900.00	38%
	5,000	100%

Cost per line
L1	1950
L2	900
Cup	1800
Glass	2850
	7500

Currently Note only available in Production/line option

ucGLLineSegment

Determines how the balance for the ulGLCriteria option direct should be allocated.

...

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

Calculation

Explanation

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

Field	Explanation
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.
Revenue	Revenue 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:

What	Value	Note
Item	30001
Period	Oct
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:

What	Value	Note
Item	300119
Period	Oct
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 Logistics

IT & Admin

Variable cost
Fixed cost

Same calculation/split as 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

Rebates

Same 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

...

Space shortcuts

Page tree

Versions Compared

Old Version 15

New Version Current

Key

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

8a) Performance overview - per SKU