Introduction

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

With the default reports inside Sage, you are able to quickly view the Gross profit per item, but it will not show you the nett profit per Item. This dashboards takes the expenditure from the various departments and allocates these accordingly to the items.

By entering settings on both Item UDF's and Account UDF's the dashboard will pick up both to display and use for calculations.

Dashboards

Two dashboards are available for viewing the profitability:

1) Costing overview 

Main dashboard that contains all the logic and calculations to display the profitability per item per segment.

2) Sales detail

Detail dashboard containing the individual invoices for reconciliation purposes. (Note that the sales data is also available in the Costing overview, however it is loaded consolidated to limit the amount of data)

Costing overview

Main dashboard that contains all the logic and calculations to display the profitability per item per segment.

To come to a net profit the following expenditures are included in the calculations:

1) Weighted average unit cost (Coming directly from Sage and is calculated and maintained in Sage)

2) Production expenses (expenditure that is not included in the Weighted average unit of Sage, these costs are booked to GL accounts)

3) Logistics expenses (expenditure that is not included in the Weighted average unit of Sage, these costs are booked to GL accounts)

4) Sales expenses (expenditure that is not included in the Weighted average unit of Sage, these costs are booked to GL accounts)

5) Sales rebates (rebates that are not included in the Weighted average unit of Sage, these costs are booked to GL accounts)

6) Marketing expenses (rebates that are not included in the Weighted average unit of Sage, these costs are booked to GL accounts)

7) IT & Administration expenses (rebates that are not included in the Weighted average unit of Sage, these costs are booked to GL accounts)

8) Discounts on sales  (Differences between the standard selling price and the actuals invoice unit price)

9) Free of charge (FOC) items  (Direct expenses due to FOC items on invoices)

By allocating all these costs a net profit % is calculated in the dashboard.

Sage configurations

In order for the report to pick up items and expenditure the following UDF's need to be filled in Sage

On Account

ulGLAccGroup

Determines in what section of expenditure the account should be included.

Options: Admin & IT; Catering; Logistics; Marketing; Production; Sales; Sales - Rebates

ulGLGroupType

Determines if the accounts is categorized as fixed or variable (Note that are no calculation differences based on this field, just grouping/categorisation.)

Options: Fixed; Variable

ulGLCriteria     

Determines how the expenditure should be allocated to the various segments.

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

ucGLLineSegment

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

Options: TEXT VALUE I.E.: L1-50;L2-50

Note that this field is ignored if the ulGLCriteria  is not direct.

ucGLChannelSegment

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

Options: TEXT VALUE I.E.: KA-100

Note that this field is ignored if the ulGLCriteria  is not direct.

ulGLWarehouse

Determines how the balance for the ulGLCriteria option Litres/packs should be allocated.

Options: 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.

ulGLBudgetCalculationOption

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

Options: Packs; Litres: Bottles; Value

The budgets are stored in the table called _as_RayyanBudgets. This table contains a per month per item budget for the indicated options

On Item

ulIIItemType

Determines they type of the item. If selected either of the 2 options below, the item will be includes in the report and in all calculations

Options: Rayyan; Private label

ufIIPACKLTR

Determines total litres in 1 pack

Options: Float number

uiIIPCSPERPACK

Determines the total pieces per pack

Options: Float number

On Manufactures

ulMANULineSegment

Determines the line for the specific manufacture

Options: Float number

On Invoice

SalesRep.Address3

All invoices are linked to a Salesrep. The Address3 field determines to what segment the sales is considered.

This value is needed to allocate the costs when the option Litre,Pc,Packs

SalesRep.Bank_Account

All invoices are linked to a Salesrep. The Address3 field determines to what segment the sales is considered.

Sage data

All data is loaded via a SQL connection with views/functions to calculate the figure.

GL Balance

The Sage GL balances are calculated by summing all transactions per GL account per financial period. These balances will correspond with an user running a Trial balance from the Sage UI.

Sales data

The Sales is loaded into the report consolidated per day, meaning it is not available per invoice (this to lower the amount of data to compute in the report). This information is however available in the "Sales detail" Dashboard.

Sales consists of archived documents of the type: Case invoice (0), Credit note (1) and Sales Order (4)

Only the lines that contain stock items of the type "Rayyan" and "Private label" are loaded in the data. 

Manufacture data

The manufacture data is loaded per manufacture transaction. Only the manufactures that contain a value on the UDF ulMANULineSegment and which are processed are loaded into the report.

Pages

1) Production expenses

Contains the GL accounts that have the value "Production" filled in the UDF ulGLAccGroup.

This overview will split the expenditure over the various lines. Meaning this overview is not segment based. In report 7 & 8 you will only see this split between Fixed and Variable.

Note that this Expenditure is split over the items based on either "% of total litres sold" or "% of total revenue" (Based on the selected option in the menu on report 7 & 8)

The sum of all the lines (in the columns) will add up to the total of all lines (most bottom right figure). This figure will correspond with the sum of all Fixed and Variable costs allocations per item if you were to export the report to Excel.

2) Logistics expenses

Contains the GL accounts that have the value "Logistics" filled in the UDF ulGLAccGroup.

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.

This overview will split the expenditure over the various segments. 

Note that this Expenditure is split over the items based on either "% of total litres sold" or "% of total revenue" (Based on the selected option in the menu on report 7 & 8)

The sum of all the Segments (in the columns) will add up to the total of all segments (most bottom right figure). This figure will correspond with the sum of all Fixed and Variable costs allocations per item if you were to export the report to Excel.

3 - A) Sales expenses

Contains the GL accounts that have the value "Sales" filled in the UDF ulGLAccGroup.

This overview will split the expenditure over the various segments. 

Note that this Expenditure is split over the items based on either "% of total litres sold" or "% of total revenue" (Based on the selected option in the menu on report 7 & 8)

The sum of all the Segments (in the columns) will add up to the total of all segments (most bottom right figure). This figure will correspond with the sum of all Fixed and Variable costs allocations per item if you were to export the report to Excel.

3 - B) Sales expenses - Rebates

Contains the GL accounts that have the value "Sales - Rebates" filled in the UDF ulGLAccGroup.

This overview will split the expenditure over the various segments. 

Note that this Expenditure is split over the items based on either "% of total litres sold" or "% of total revenue" (Based on the selected option in the menu on report 7 & 8)

The sum of all the Segments (in the columns) will add up to the total of all segments (most bottom right figure). This figure will correspond with the sum of all Fixed and Variable costs allocations per item if you were to export the report to Excel.

4) Marketing expenses

Contains the GL accounts that have the value "Marketing" filled in the UDF ulGLAccGroup.

This overview will split the expenditure over the various segments. 

Note that this Expenditure is split over the items based on either "% of total litres sold" or "% of total revenue" (Based on the selected option in the menu on report 7 & 8)

The sum of all the Segments (in the columns) will add up to the total of all segments (most bottom right figure). This figure will correspond with the sum of all Fixed and Variable costs allocations per item if you were to export the report to Excel.

5) IT & administration

Contains the GL accounts that have the value "Admin & IT" filled in the UDF ulGLAccGroup.

This overview will split the expenditure over the various segments. 

Note that this Expenditure is split over the items based on either "% of total litres sold" or "% of total revenue" (Based on the selected option in the menu on report 7 & 8)

The sum of all the Segments (in the columns) will add up to the total of all segments (most bottom right figure). This figure will correspond with the sum of all Fixed and Variable costs allocations per item if you were to export the report to Excel.

7) Costing and pricing overview

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. 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. 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 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: 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 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

8b) Performance overview - per channel

9) Budget overview

10) Sales & Production overview

11) Stock % per Line

Sales detail

This dashboard is made for reconciliation purposes. While the sales data is loaded group per day in the main dashboard, here it will be loaded per invoice, making it easier to trace inside Sage.

The matrix on the dashboard can be drilled down with the UI tools (right click or by using the drilldown menu) to show the data on invoice level.

Per month the following information is available: Litres sold, Sales, COGS,Discount. 

These figure will correspond with the sales data from the Main dashboard.

By using the filter in the bottom right corner, the figures for FOC can be displayed and viewed (which are used for the FOC section in report 7 and 8)

TO DO

Check PCS on Production vs Packs on Logistics