Gross Margin Analysis
The script below illustrates how a gross margin analysis dashboard can be composed with Envision.
read "/sample/Lokad_Items.tsv" as Items with
Id : text
Name : text
read "/sample/Lokad_Orders.tsv" as Orders expect [Id, Date] with
Id : text
Date : date
Quantity : number
NetAmount : number
Currency : text
read "/sample/Lokad_PurchaseOrders.tsv" as PO expect [Id, Date] with
Id : text
Date : date
Quantity : number
NetAmount : number
Currency : text
show label "Gross margin analysis (LIFO inventory cost)" a1f1 tomato
oend = max(Orders.Date)
lastMonday = monday(oend)
// Default currency, and currency conversions
where date >= oend - 365
currency = mode(Orders.Currency)
Orders.NetAmount = forex(Orders.NetAmount, Orders.Currency, currency, Orders.Date)
PO.NetAmount = forex(PO.NetAmount, \
PO.Currency, currency, PO.Date)
// LIFO inventory value
PO.UnitPrice = PO.NetAmount / max(1, PO.Quantity)
Orders.CostAmount = latest(PO.UnitPrice) * Orders.Quantity
Orders.Margin = Orders.NetAmount - Orders.CostAmount
// Top row of KPIs
show label "Current year" a2b2
where date >= oend - 365
show scalar "Gross margin" c2 unit:" \{currency}" with sum(Orders.Margin)
show scalar "Gross margin" d2 unit:"%" with sum(Orders.Margin) / sum(Orders.NetAmount)
show scalar "Orders lines" e2 with sum(Orders.1)
show scalar "Margin per unit" f2 unit:" \{currency}" with
sum(Orders.Margin) / sum(Orders.Quantity)
show label "Previous year" a3b3
where date >= oend - 2 * 365 and date < oend - 365
show scalar "Gross margin" c3 unit:" \{currency}" with sum(Orders.Margin)
show scalar "Gross margin" d3 unit:"%" with sum(Orders.Margin) / max(1, sum(Orders.NetAmount))
show scalar "Orders lines" e3 with sum(Orders.1)
show scalar "Margin per unit" f3 unit:" \{currency}" with
sum(Orders.Margin) / max(1, sum(Orders.Quantity))
// Linechart
Week.margin = sum(Orders.Margin)
where date >= lastMonday - 52 * 7 and date < lastMonday
show linechart "Weekly gross margin" a4f6 tomato unit:" \{currency}" with
Week.margin as "Current year"
Week.margin[-52] as "Previous year"
// Split by category and supplier
where date >= oend - 365
show barchart "Margin per category (1 year)" a7c9 tomato unit:" \{currency}" with
sum(Orders.Margin)
group by Category
show barchart "Margin per supplier (1 year)" d7f9 tomato unit:" \{currency}" with
sum(Orders.Margin)
group by Supplier
// Tops and flops
where date >= oend - 365
where sum(Orders.NetAmount) > 0
show table "Most profitable items (1 year)" a10c12 with
Id
Name
sum(Orders.Margin) as "Margin" unit:" \{currency}"
sum(Orders.Margin) / sum(Orders.NetAmount) as "Margin" unit:"%"
group into Orders
order by sum(Orders.Margin) desc
R = rankd(sum(Orders.Margin))
where R <= 0.2 * max(R) by 1
show table "Least profitable items (1 year, among top 20%)" d10f12 with
Id
Name
sum(Orders.Margin) as "Margin" unit:" \{currency}"
sum(Orders.Margin) / sum(Orders.NetAmount) as "Margin" unit:"%"
group into Orders
order by sum(Orders.NetAmount) / sum(Orders.Margin) desc