The question table shows inventory levels for materials required to produce products, with specific combinations (1 A, 2 B, 3 C per product). Inventory should maintain this ratio. Daily efficiency is calculated as the ratio of materials forming complete products to total materials available. For instance, on 29/1/2024, due to limited C, only 6 products can be made, requiring 6 A, 12 B, and 18 C (total 36), and efficiency is 36/61 (61= total material)
📌 Challenge Details and Links
Challenge Number: 10
Challenge Difficulty: ⭐⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Inventory Efficiency!!! with Power Query
Power Query solution 1 for Inventory Efficiency!!!, proposed by Eric Laforce:
let
PQty = [A = 1, B = 2, C = 3],
Source = Excel.CurrentWorkbook(){[Name = "tData"]}[Content],
Add_QtyPerProduct = Table.AddColumn(Source, "QPP", each Record.Field(PQty, [Material])),
Add_NbProduct = Table.AddColumn(
Add_QtyPerProduct,
"NP",
each Number.RoundDown([Inventory] / [QPP])
),
Group = Table.Group(
Add_NbProduct,
"Date",
{
"Efficiency Rate",
each
let
NP = List.Min([NP])
in
List.Sum(List.Transform([QPP], each _ * NP)) / List.Sum([Inventory])
}
)
in
GroupPower Query solution 2 for Inventory Efficiency!!!, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = Table.AddColumn(S,"C", each if [Material]="A" then 1 else if [Material]="B" then 2 else 3),
b = Table.Group(a,{"Date"},{{"G", each [[Date],[Inventory],[C]]}})[[G]],
c = Table.TransformColumns(b,{"G", each Table.AddColumn(_,"P", each Number.RoundDown([Inventory]/[C]))}),
d = Table.AddIndexColumn(c,"I"),
e = Table.TransformColumns(d,{"G", each let
f = Table.AddColumn(_,"M",(x)=>List.Min(Table.ToColumns(_){3})),
g = Table.AddColumn(f,"N", each [C]*[M])
in g}),
h = Table.TransformColumns(e,{"G", each let
i = Table.AddColumn(_,"T",(x)=>List.Sum(Table.ToColumns(_){5})),
j = Table.AddColumn(i,"RT",(x)=>List.Sum(Table.ToColumns(_){1}))
in j}),
k = Table.TransformColumns(h,{"G", each Table.AddColumn(_,"Efficiency Rate", each Number.Round([T]/[RT],2))})[[G]],
l = Table.TransformColumns(k,{"G", each _{0}}),
Sol = Table.ExpandRecordColumn(l,"G",{"Date","Efficiency Rate"},{"Date","Efficiency Rate"})
in
SolPower Query solution 3 for Inventory Efficiency!!!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Sol = Table.Group(
Origen,
{"Date"},
{
{
"Efficiency Rate",
(x) =>
let
a = List.Sum(x[Inventory]),
b = List.Min(List.Transform({0 .. 2}, each Number.RoundDown(x[Inventory]{_} / (_ + 1))))
* 6,
c = Number.ToText(b / a, "##%")
in
c
}
}
)
in
SolPower Query solution 4 for Inventory Efficiency!!!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A1 = Table.AddColumn(
S,
"Custom",
each
if [Material] = "A" then
[Inventory]
else if [Material] = "B" then
Number.IntegerDivide([Inventory], 2)
else if [Material] = "C" then
Number.IntegerDivide([Inventory], 3)
else
null
),
A2 = Table.TransformColumnTypes(A1, {{"Date", type date}}),
A3 = Table.Group(
A2,
{"Date"},
{
{"Total", each List.Sum([Inventory]), type number},
{"Min", each List.Min([Custom]), type number}
}
),
Sol = Table.AddColumn(
A3,
"Efficeincy Rate",
each Number.RoundDown([Min] * 6 / [Total], 2),
Percentage.Type
)
in
SolPower Query solution 5 for Inventory Efficiency!!!, proposed by An Nguyen:
let
Dataset = Excel.CurrentWorkbook(){[Name = "Dataset"]}[Content],
GroupByDate = Table.Group(
Dataset,
"Date",
{
"Efficeincy Rate",
each
let
l = _[Inventory],
Quantity = List.Min({l{0}, Number.IntegerDivide(l{1}, 2), Number.IntegerDivide(l{2}, 3)}),
Rate = Number.Round(Quantity * 6 / List.Sum({l{0}, l{1}, l{2}}), 2)
in
Rate
}
)
in
GroupByDatePower Query solution 6 for Inventory Efficiency!!!, proposed by Glyn Willis:
let
qty = Table.Buffer(#table({"p", "q"}, {{"A", 1}, {"B", 2}, {"C", 3}})),
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Date", type date}, {"Material", type text}, {"Inventory", Int64.Type}}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"q",
each Number.RoundDown([Inventory] / Table.SelectRows(qty, (x) => x[p] = [Material])[q]{0}, 0),
Int64.Type
),
#"Grouped Rows" = Table.Group(
#"Added Custom",
{"Date"},
{
{
"efficiency Rate",
each
let
a = List.Min([q])
in
List.Sum(Table.AddColumn(qty, "i", (x) => x[q] * a)[i]) / List.Sum([Inventory]),
Percentage.Type
}
}
)
in
#"Grouped Rows"Solving the challenge of Inventory Efficiency!!! with Excel
Excel solution 1 for Inventory Efficiency!!!, proposed by Bo Rydobon 🇹🇭:
=LET(
d,
B3:B17,
u,
UNIQUE(
d
),
HSTACK(
u,
MAP(
u,
LAMBDA(
v,
LET(
m,
FILTER(
D3:D17,
d=v
),
INT(
MIN(
m/{1;2;3}
)
)*6/SUM(
m
)
)
)
)
)
)Excel solution 2 for Inventory Efficiency!!!, proposed by محمد حلمي:
=LET(u,
UNIQUE(
B3:B17
),
HSTACK(u,MAP(u,
LAMBDA(a,
LET(i,
FILTER(
D3:D17,
B3:B17=a
),v,
TAKE(
i,
-1
)*2,
(v-MOD(
v,
12
))/SUM(
i
))))))Excel solution 3 for Inventory Efficiency!!!, proposed by 🇵🇪 Ned Navarrete C.:
=LET(
d,
B3:B17,
u,
UNIQUE(
d
),
i,
{1;2;3},
HSTACK(
u,
MAP(
u,
LAMBDA(
v,
LET(
f,
FILTER(
D3:D17,
d=v
),
r,
i*MIN(
INT(
f/i
)
),
SUM(
r
)/SUM(
f
)
)
)
)
)
)Excel solution 4 for Inventory Efficiency!!!, proposed by Kris Jaganah:
=LET(a,
B3:B17,
b,
C3:C17,
c,
D3:D17,
d,
XMATCH(
b,
b
),
e,
INT(
c/d
),
f,
MAP(a,
LAMBDA(x,
MIN(IF(a=x,
(a=x)*e,
""))))*d,
g,
UNIQUE(
a
),
HSTACK(g,
MAP(g,
LAMBDA(z,
SUM((z=a)*f)/SUM((a=z)*c)))))Excel solution 5 for Inventory Efficiency!!!, proposed by John Jairo Vergara Domínguez:
=GROUPBY(
B3:B17,
D3:D17,
LAMBDA(
g,
6*INT(
MIN(
g/{1;2;3}
)
)/SUM(
g
)
),
,
0
)Excel solution 6 for Inventory Efficiency!!!, proposed by Andy Heybruch:
=LET(
inv,
WRAPROWS(
D3:D17,
3
), prod,
BYROW(
inv,
LAMBDA(
_a,
MIN(
ROUNDDOWN(
_a/SEQUENCE(
,
3
),
0
)
)*6/SUM(
_a
)
)
), HSTACK(
UNIQUE(
B3:B17
),
prod
)
)Excel solution 7 for Inventory Efficiency!!!, proposed by Pieter de B.:
=HSTACK(
UNIQUE(
B3:B17
),
BYROW(
WRAPROWS(
D3:D17,
3
),
LAMBDA(
b,
SUM(
MIN(
INT(
b/{1,
2,
3}
)
)*6
)/SUM(
b
)
)
)
)Excel solution 8 for Inventory Efficiency!!!, proposed by Surendra Reddy:
=LET(
x,
B3:B17,
y,
D3:D17,
u,
UNIQUE(
x
),
e,
MAP(
u,
LAMBDA(
z,
LET(
s,
SEQUENCE(
3
),
f,
FILTER(
y,
x=z
),
q,
INT(
MIN(
f/s
)
),
SUM(
q*s
)/SUM(
f
)
)
)
),
HSTACK(
u,
e
)
)Excel solution 9 for Inventory Efficiency!!!, proposed by Tyler Cameron:
=MAP(
UNIQUE(
B3:B17
),
LAMBDA(
x,
LET(
a,
FILTER(
D3:D17,
B3:B17=x
),
b,
MIN(
BYROW(
a,
LAMBDA(
u,
ROUNDDOWN(
u/XMATCH(
u,
a
),
0
)
)
)
),
b*6/SUM(
a
)
)
)
)Solving the challenge of Inventory Efficiency!!! with R
R solution 1 for Inventory Efficiency!!!, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("files/CH-0010.xlsx", range = "B2:D17")
test = read_excel("files/CH-0010.xlsx", range = "G2:H7")
result = input %>%
mutate(requirements = case_when(
Material == "A" ~ 1,
Material == "B" ~ 2,
Material == "C" ~ 3
), product_available = Inventory%/%requirements) %>%
group_by(Date) %>%
mutate(min_available = min(product_available),
usage = min_available*requirements) %>%
summarise(usage = sum(usage),
inventory = sum(Inventory),
Efficiency_rate = usage/inventory)
https://github.com/kgryczan/omids_excel_challenges