Calculate the inventory at the end of all months of the year. For missing months, inventory level will be inventory of the previous month. Inventory = Previous month’s inventory + Incoming Qty – Outgoing Qty For Jan month, Previous month’s inventory = 0
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 463
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Calculate Monthly Inventories with Power Query
Power Query solution 1 for Calculate Monthly Inventories, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Month = Table.TransformColumns( Source ,{"Month" ,each Date.Month( Date.FromText( "1"& _&"24")) }),
Net = Table.AddColumn(Month, "Net", each [Incoming Qty]-[Outgoing Qty]),
ToTable = Table.FromList({1..12}, Splitter.SplitByNothing(),{"Month"}),
Inv = Table.AddColumn(ToTable, "Inventory", each
let a = Table.AddColumn( ToTable,"zz", each try Net[Net] {List.PositionOf(Net[Month],[Month])} otherwise 0)[zz],
b = List.Accumulate(List.Range(a,0,[Month]),0,(X,Y)=> X +Y) in b),
Ans = Table.TransformColumns( Inv ,{"Month" , each Text.Start( Date.MonthName( hashtag#date(2024,_,1)),3 )})
in
Ans
Power Query solution 2 for Calculate Monthly Inventories, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Generate = List.Generate(
() => [a = 0, I = 0],
each [a] <= 12,
each [
a = [a] + 1,
b = Date.ToText(Date.From(a * 28), "MMM"),
c = Source{[Month = b]}? ?? [],
d = c[Incoming Qty]? - c[Outgoing Qty]?,
I = [I] + (d ?? 0)
],
each [Month = [b], Inventory = [I]]
),
Return = Table.FromRecords(List.Skip(Generate))
in
ReturnPower Query solution 3 for Calculate Monthly Inventories, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Month = List.Transform({1..12}, each Date.ToText(hashtag#date(2024, _, 1), "MMM", "en-US")),
Comp = List.Transform(Month, each try Table.ToRows(Source){List.PositionOf(Source[Month],_)} otherwise {_,0,0}),
Calc = List.Transform(Comp, each _{1}-_{2}),
Total = List.Skip(List.Accumulate(Calc, {0}, (s,c)=> s&{List.Last(s)+c})),
Sol = Table.FromColumns({Month, Total}, {"Month","Inventory"})
in
Sol
Power Query solution 4 for Calculate Monthly Inventories, proposed by Venkata Rajesh:
let
Source = Data,
Months = Table.FromColumns({List.Transform({1..12}, each Text.Start(Date.MonthName(hashtag#date(2024,_,1)),3))}, {"Month"}),
Inv = Table.AddColumn(Months, "Inv", each
[x = Source{[Month = [Month]]}[Incoming Qty],
y = Source{[Month = [Month]]}[Outgoing Qty],
z = try x-y otherwise null][z]),
Output = Table.AddColumn(Inv, "Inventory", each List.Sum(List.FirstN(Inv[Inv], Table.PositionOf(Inv,_)+1)))[[Month],[Inventory]]
in
Output
Solving the challenge of Calculate Monthly Inventories with Excel
Excel solution 1 for Calculate Monthly Inventories, proposed by Bo Rydobon 🇹🇭:
=LET(
m,
TEXT(
SEQUENCE(
12
)*29,
"mmm"
),
HSTACK(
m,
SCAN(
,
XLOOKUP(
m,
A2:A6,
B2:B6-C2:C6,
0
),
SUM
)
)
)Excel solution 2 for Calculate Monthly Inventories, proposed by Bo Rydobon 🇹🇭:
=LET(
s,
ROW(
1:12
),
m,
TEXT(
s*29,
"mmm"
),
n,
MMULT(
N(
m=TRANSPOSE(
A2:A6
)
),
B2:B6-C2:C6
),
t,
SUM(
n
),
IF(
{1,
0},
m,
PROB(
s,
n/t,
1,
s
)*t
)
)Excel solution 3 for Calculate Monthly Inventories, proposed by Rick Rothstein:
=SCAN(
0,
TEXT(
28*SEQUENCE(
12
),
"mmm"
),
LAMBDA(
a,
x,
IFNA(
a+INDEX(
B2:B6-C2:C6,
MATCH(
x,
A2:A6,
0
)
),
a
)
)
)Excel solution 4 for Calculate Monthly Inventories, proposed by John V.:
=LET(
m,
TEXT(
29*ROW(
1:12
),
"mmm"
),
HSTACK(
m,
SCAN(
,
XLOOKUP(
m,
A2:A6,
B2:B6-C2:C6,
0
),
SUM
)
)
)Excel solution 5 for Calculate Monthly Inventories, proposed by محمد حلمي:
=LET(m,
TEXT(
SEQUENCE(
12
)*29,
"mmm"
),
HSTACK(m,
LOOKUP(--(1&m),
--(1&A2:A6),
SCAN(
0,
B2:B6-C2:C6,
LAMBDA(
a,
v,
a+v
)
))))Excel solution 6 for Calculate Monthly Inventories, proposed by 🇰🇷 Taeyong Shin:
=LET(
m,
TEXT(
SEQUENCE(
12
)&-1,
"mmm"
),
HSTACK(
m,
SCAN(
0,
m,
LAMBDA(
a,
v,
a+XLOOKUP(
v,
A2:A6,
B2:B6-C2:C6,
0
)
)
)
)
)
=LET(
m,
TEXT(
SEQUENCE(
12
)&-1,
"mmm"
),
HSTACK(
m,
SCAN(
0,
m,
LAMBDA(
a,
v,
a+FILTER(
B2:B6-C2:C6,
A2:A6=v,
0
)
)
)
)
)Excel solution 7 for Calculate Monthly Inventories, proposed by Kris Jaganah:
=LET(
a,
TEXT(
SEQUENCE(
12,
,
,
31
),
"mmm"
),
HSTACK(
a,
SCAN(
,
XLOOKUP(
a,
A2:A6,
B2:B6-C2:C6
),
LAMBDA(
x,
y,
IF(
ISNA(
y
),
x,
x+y
)
)
)
)
)Excel solution 8 for Calculate Monthly Inventories, proposed by Julian Poeltl:
=LET(
T,
A2:C6,
M,
TAKE(
T,
,
1
),
Q,
SCAN(
0,
CHOOSECOLS(
T,
2
)-TAKE(
T,
,
-1
),
LAMBDA(
A,
B,
A+B
)
),
SY,
SEQUENCE(
12
),
Y,
TEXT(
DATE(
,
SY,
1
),
"MMM"
),
X,
XMATCH(
M,
Y
),
VSTACK(
HSTACK(
"Month",
"Inventory"
),
HSTACK(
Y,
XLOOKUP(
SY,
X,
Q,
,
-1
)
)
)
)Excel solution 9 for Calculate Monthly Inventories, proposed by Timothée BLIOT:
=LET(
M,
TEXT(
DATE(
1900,
SEQUENCE(
12
),
1
),
"mmm"
),
VSTACK(
{"Month",
"Inventory"},
HSTACK(
M,
SCAN(
0,
M,
LAMBDA(
a,
v,
a+XLOOKUP(
v,
A2:A6,
B2:B6-C2:C6,
0
)
)
)
)
)
)Excel solution 10 for Calculate Monthly Inventories, proposed by Sunny Baggu:
=LET(
m,
TEXT(
DATE(
2023,
SEQUENCE(
12
),
1
),
"mmm"
),
v,
XLOOKUP(
m,
A2:A6,
B2:B6 - C2:C6,
0
),
HSTACK(
m,
SCAN(
0,
v,
LAMBDA(
a,
v,
IF(
v,
a + v,
a
)
)
)
)
)Excel solution 11 for Calculate Monthly Inventories, proposed by Abdallah Ally:
=LET(
a,
TEXT(
1&-SEQUENCE(
12
),
"mmm"
),
b,
XLOOKUP(
a,
A2:A6,
B2:B6-C2:C6,
0
),
HSTACK(
a,
SCAN(
0,
b,
LAMBDA(
x,
y,
x+y
)
)
)
)Excel solution 12 for Calculate Monthly Inventories, proposed by Abdallah Ally:
= b.Month
)
SELECTMonth
,SUM(Result) OVER (ORDER BY MonthNumber)Excel solution 13 for Calculate Monthly Inventories, proposed by 🇵🇪 Ned Navarrete C.:
=HSTACK(
TEXT(
29*ROW(
1:12
),
"mmm"
),
LOOKUP(
ROW(
1:12
),
MONTH(
1&A2:A6
),
SCAN(
,
B2:B6-C2:C6,
LAMBDA(
c,
v,
c+v
)
)
)
)Excel solution 14 for Calculate Monthly Inventories, proposed by Asheesh Pahwa:
=LET(
dt,
TEXT(
DATE(
2024,
SEQUENCE(
12
),
1
),
"mmm"
),
m,
A2:A6,
iq,
B2:B6,
oq,
C2:C6,
s,
SCAN(
0,
iq-oq,
LAMBDA(
x,
y,
x+y
)
),
x,
SCAN(
0,
XLOOKUP(
dt,
m,
s,
""
),
LAMBDA(
x,
y,
IF(
y="",
x,
y
)
)
),
HSTACK(
dt,
x
)
)Excel solution 15 for Calculate Monthly Inventories, proposed by Andy Heybruch:
=LET(
_m,
TEXT(
ROW(
1:12
)*28,
"mmm"
),
_i,
SCAN(
0,
_m,
LAMBDA(
a,
v,
a+XLOOKUP(
v,
A2:A6,
B2:B6-C2:C6,
0,
0
)
)
),
HSTACK(
_m,
_i
)
)Excel solution 16 for Calculate Monthly Inventories, proposed by Bilal Mahmoud kh.:
=MAP(
MONTH(
"1-"&E3:E14&"-2024"
),
LAMBDA(
x,
SUM(
FILTER(
B2:B6,
MONTH(
"1-"&A2:A6&"-2024"
)<=x
)
)-SUM(
FILTER(
C2:C6,
MONTH(
"1-"&A2:A6&"-2024"
)<=x
)
)
)
)Excel solution 17 for Calculate Monthly Inventories, proposed by Mey Tithveasna:
=LET(
t,
TEXT(
29*SEQUENCE(
12
),
"mmm"
),
HSTACK(
t,
SCAN(
,
XLOOKUP(
t,
A2:A6,
B2:B6-C2:C6,
0
),
LAMBDA(
a,
b,
IF(
ISNA(
b
),
a,
a+b
)
)
)
)
)Excel solution 18 for Calculate Monthly Inventories, proposed by Milan Shrimali:
=IF(L3#="Jan",
B2-C2,
MAP(
L3#,
LAMBDA(
y,
MAP(
y,
LAMBDA(
x,
IF(
ISERROR(
FILTER(
$A$2:$A$6,
$A$2:$A$6=x
),
OFFSET(
x,
-1,
1
),
OFFSET(
x,
-1,
1
)
+TAKE(
FILTER(
$B$2:$C$6,
$A$2:$A$6=x
),
,
1
)
-TAKE(
FILTER(
$B$2:$C$6,
$A$2:$A$6=x
),
,
-1
)
)
)
)
)
)
)Excel solution 19 for Calculate Monthly Inventories, proposed by Peter Tholstrup:
=LET(
month,
TEXT(
DATE(
,
SEQUENCE(
12
),
1
),
"mmm"
),
&
qty,
XLOOKUP(
month,
A2:A6,
B2:B6 - C2:C6,
0
),
calc,
SCAN(
0,
qty,
LAMBDA(
a,
b,
a + b
)
),
VSTACK(
{"Month",
"Inventory"},
HSTACK(
month,
calc
)
)
)Excel solution 20 for Calculate Monthly Inventories, proposed by Nicolas Micot:
=SI(
E3="Jan";
0;
F2
)+SOMME.SI(
$A$2:$A$6;
E3;
$B$2:$B$6
)-SOMME.SI(
$A$2:$A$6;
E3;
$C$2:$C$6
)Excel solution 21 for Calculate Monthly Inventories, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(m,
TEXT(
ROW(
1:12
)&"-24",
"MMM"
),
i,
MAP(E3:E14,
LAMBDA(i,
SUM(B2:C6*(A2:A6=i)*{1,
-1}))),
HSTACK(
m,
SCAN(
,
i,
SUM
)
))Excel solution 22 for Calculate Monthly Inventories, proposed by Ernesto Vega Castillo:
=LET(
s,
TEXT(
SEQUENCE(
12
)*30,
"mmm"
),
p,
SCAN(
0,
s,
LAMBDA(
a,
b,
a+SUM(
XLOOKUP(
b,
A2:A6,
B2:B6-C2:C6,
0
)
)
)
),
HSTACK(
s,
p
)
)Excel solution 23 for Calculate Monthly Inventories, proposed by Tyler Cameron:
=LET(
a,
TEXT(
DATE(
1,
SEQUENCE(
12
),
1
),
"mmm"
),
HSTACK(
a,
SCAN(
0,
a,
LAMBDA(
x,
y,
x+XLOOKUP(
y,
A2:A6,
B2:B6-C2:C6,
0
)
)
)
)
)
Original
=LET(
a,
TEXT(
DATE(
1,
SEQUENCE(
12
),
1
),
"mmm"
),
b,
A2:A6,
HSTACK(
a,
SCAN(
0,
a,
LAMBDA(
x,
y,
x+XLOOKUP(
y,
b,
B2:B6,
0
)-XLOOKUP(
y,
b,
C2:C6,
0
)
)
)
)
)Excel solution 24 for Calculate Monthly Inventories, proposed by Patrick RAHERINJATOVO:
=IFERROR(SUM(INDEX($A$2:$C$6,MATCH(E3,$A$2:$A$6,0),2),-INDEX($A$2:$C$6,MATCH(E3,$A$2:$A$6,0),3),F2),F2)Excel solution 25 for Calculate Monthly Inventories, proposed by Ben Gutscher:
=LET(
n,
SEQUENCE(
12
),
m,
TEXT(
DATE(
2014,
n,
1
),
"mmm"
),
VSTACK(
HSTACK(
"Month",
"Inventory"
),
HSTACK(
m,
MAP(
n,
LAMBDA(
r,
SUM(
XLOOKUP(
TAKE(
m,
r
),
$A$2:$A$6,
$B$2:$B$6,
0
)
)
)
)-MAP(
n,
LAMBDA(
r,
SUM(
XLOOKUP(
TAKE(
m,
r
),
$A$2:$A$6,
$C$2:$C$6,
0
)
)
)
)
)
)
)Excel solution 26 for Calculate Monthly Inventories, proposed by Muthukumar R.:
=if(f2<>"Jan",ifna(vlookup(f2,a:c,2,0)-vlookup(f2,a:c,3,0)+h1,h1),ifna(vlookup(f2,a:c,2,0)-vlookup(f2,a:c,3,0),h1))Solving the challenge of Calculate Monthly Inventories with Python
Python solution 1 for Calculate Monthly Inventories, proposed by Konrad Gryczan, PhD:
import pandas as pd
from itertools import accumulate
input = pd.read_excel("463 Inventory Calculation.xlsx", usecols="A:C", nrows = 6)
test = pd.read_excel("463 Inventory Calculation.xlsx", usecols="E:F", skiprows = 1, nrows = 13)
months = pd.DataFrame(
{
'abbs': ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
'months': range(1, 13)
}
)
result = months.merge(input, left_on='abbs', right_on='Month', how='left').fillna(0)
result['inventory'] = list(accumulate(result['Incoming Qty'] - result['Outgoing Qty'], initial=0))[1:]
result['inventory'] = result['inventory'].astype("int64")
result = result[['abbs','inventory']]
result.columns = test.columns
print(result.equals(test)) # True
Solving the challenge of Calculate Monthly Inventories with Python in Excel
Python in Excel solution 1 for Calculate Monthly Inventories, proposed by Abdallah Ally:
import pandas as pd
from datetime import datetime
file_path = 'Excel_Challenge_463 - Inventory Calculation.xlsx'
df1 = pd.read_excel(file_path, usecols='A:C', nrows=5)
# Perform data wrangling
months = [datetime(2023, x, 1).strftime('%b') for x in range(1, 13)]
df2 = pd.DataFrame(months, columns=['Month'])
df = df2.merge(df1, how='left')
df['Inventory'] = (df.iloc[:, 1] - df.iloc[:, 2]).fillna(0).cumsum().map(int)
df = df.iloc[:, [0, 3]]
df
Solving the challenge of Calculate Monthly Inventories with R
R solution 1 for Calculate Monthly Inventories, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(padr)
input = read_excel("Excel/463 Inventory Calculation.xlsx", range = "A1:C6") %>% janitor::clean_names()
test = read_excel("Excel/463 Inventory Calculation.xlsx", range = "E2:F14") %>% janitor::clean_names()
months = tibble(abbs = month.abb, month = 1:12)
result = months %>%
left_join(input, by = c("abbs" = "month")) %>%
replace_na(list(incoming_qty = 0, outgoing_qty = 0)) %>%
mutate(inventory = accumulate2(incoming_qty, outgoing_qty, .init = 0,
.f = ~ ..1 + ..2 - ..3)[-1]) %>%
select(month = abbs, inventory)
identical(result, test)
# [1] TRUE
R solution 2 for Calculate Monthly Inventories, proposed by Anil Kumar Goyal:
library(readxl)
library(tidyverse)
inventory <- read_excel("Excel/Excel_Challenge_463 - Inventory Calculation.xlsx",
range = cell_cols(LETTERS[1:3]))
inventory |>
mutate(Month = factor(Month, levels = month.abb, ordered = TRUE)) |>
complete(Month, fill = list(`Incoming Qty` = 0, `Outgoing Qty` = 0)) |>
mutate(Inventory = accumulate2(`Incoming Qty`, `Outgoing Qty`, .init = 0,
.f = ~ ..1 + ..2 - ..3)[-1])
&&