Using the sales transactions from the provided table and the account balance as of 31/08/2024, calculate the Daily Sales Outstanding (DSO) for each customer. To calculate the DSO for customer C, since the sum of the last three transactions matches the balance amount, the DSO is the weighted average (based on Sales) of duration between 03/04/2024 and 31/08/2024.Note: Sample calculation for Customer A is provided in the next table.
📌 Challenge Details and Links
Challenge Number: 118
Challenge Difficulty: ⭐⭐⭐
Designed by: Mahmoud Bani Asadi
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Dso! with Power Query
Power Query solution 1 for Dso!, proposed by Zoran Milokanović:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
S = Table.Sort(
Table.Group(
Table.Join(Source("Table1"), "Customer", Source("Table2"), "Customer"),
{"Customer", "Balance"},
{
"DSO",
each
let
s = List.Select(
List.Accumulate(
List.Reverse(Table.ToRows(_)),
{},
(b, n) =>
let
i = n{3} - {List.Sum(List.Zip(b){4}), 0}{Byte.From(b = {})}
in
b & {n & {if i >= n{2} then n{2} else if i > 0 then i else 0}}
),
each _{4} > 0
)
in
List.Sum(
List.Transform(s, each Number.From(#datetime(2024, 8, 31, 0, 0, 0) - _{0}) * _{4})
)
/ List.Sum(List.Zip(s){4})
}
),
"Customer"
)
in
SPower Query solution 2 for Dso!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Sol = Table.Sort(Table.Group(Source, {"Customer"}, {{"DSO Day", (x)=>
let
a = Table.ReverseRows(x),
b = {
hashtag
#date(2024,8,31)}&List.Transform(a[Date], Date.From),
c = List.Transform({1..List.Count(b)-1}, each Number.From(b{0}-b{_})),
d = List.Transform({1..List.Count(c)}, each List.Sum(List.FirstN(a[Sales],_))),
e = Table.SelectRows(Tbl2, each [Customer]=x[Customer]{0})[Balance]{0},
f = List.Select(d, each _<=e),
g = List.FirstN(a[Sales], List.Count(f)) & {e-List.Last(f)},
h = List.Sum(List.Transform(List.Zip({g,c}), each _{0}*_{1})/e)
in h}}), "Customer")
in
SolPower Query solution 3 for Dso!, proposed by Kris Jaganah:
let
A = (x) => Excel.CurrentWorkbook(){[Name = x]}[Content],
B = Table.AddColumn(
A("Table2"),
"DSO (day)",
each
let
a = Table.Sort(Table.SelectRows(A("Table1"), (y) => y[Customer] = [Customer]), {"Date", 1}),
b = Table.InsertRows(
a,
0,
{[Date = #datetime(2024, 8, 31, 0, 0, 0), Customer = [Customer], Sales = - [Balance]]}
),
c = Table.AddIndexColumn(b, "Id", 1),
d = Table.AddColumn(c, "Sum", each List.Sum(List.FirstN(c[Sales], [Id]))),
e = Table.RemoveFirstN(d, 1),
f = Table.AddColumn(e, "Co", each Number.From([Sum] > 0)),
g = Table.AddColumn(
f,
"Ans",
each
let
p = List.Sum(List.FirstN(f[Co], [Id] - 1)),
q = if p = 1 then [Sales] - [Sum] else if p > 1 then 0 else [Sales],
r = Number.From(((b[Date]{0} - [Date]) * q) / - b[Sales]{0})
in
r
),
h = Table.Group(g, {"Customer"}, {"Ans", each Number.Round(List.Sum([Ans]), 2)})[Ans]{0}
in
h
)
in
BSolving the challenge of Dso! with Excel
Excel solution 1 for Dso!, proposed by Bo Rydobon 🇹🇭:
=MAP(F3:F5,
G3:G5,
LAMBDA(a,
b,
LET(c,
SORT(
FILTER(
B3:D25,
C3:C25=a
),
,
-1
),
s,
TAKE(
c,
,
-1
),
t,
s+SCAN(
b,
-s,
SUM
),
SUM(IF(s>t,
(t>0)*t,
s)*("2024/8/31"-TAKE(
c,
,
1
)))/b)))Excel solution 2 for Dso!, proposed by Oscar Mendez Roca Farell:
=MAP(F3:F5,
G3:G5,
LAMBDA(a,
b,
LET(f,
SORT(
FILTER(
B3:D25,
C3:C25=a
),
,
-1
),
s,
DROP(
f,
,
2
),
d,
DROP(
BYROW(
HSTACK(
s,
b-VSTACK(
0,
SCAN(
,
s,
SUM
)
)
),
LAMBDA(
r,
MAX(
,
MIN(
r
)
)
)
),
-1
),
SUM(d*(45535-TAKE(
f,
,
1
))/b))))Excel solution 3 for Dso!, proposed by Julian Poeltl:
=MAP(
F3:F5,
G3:G5,
LAMBDA(
A,
B,
LET(
T,
SORT(
B3:D25,
,
-1
),
D,
DATE(
2024,
8,
31
)-TAKE(
T,
,
1
),
C,
CHOOSECOLS(
T,
2
),
S,
TAKE(
T,
,
-1
),
F,
FILTER(
S,
C=A
),
R,
B-SCAN(
0,
F,
SUM
),
I,
IF(
R>0,
F,
0
),
X,
IFNA(
XMATCH(
0,
I
),
ROWS(
I
)
),
V,
VSTACK(
TAKE(
I,
X-1
),
INDEX(
R,
X-1
)
),
SUM(
V*TAKE(
FILTER(
D,
C=A
),
X
)
)/B
)
)
)Excel solution 4 for Dso!, proposed by Kris Jaganah:
=MAP(F3:F5,
G3:G5,
LAMBDA(x,
y,
LET(a,
GROUPBY(
B3:B25,
D3:D25,
SUM,
,
0,
-1,
C3:C25=x
),
b,
DROP(
a,
,
1
),
c,
TAKE(
a,
,
1
),
d,
SCAN(
-y,
b,
SUM
),
e,
SCAN(
,
N(
d>0
),
SUM
),
f,
IFS(
e=1,
b-d,
e>1,
0,
1,
b
),
SUM(((DATE(
2024,
8,
31
)-c)*f)/y))))Excel solution 5 for Dso!, proposed by Mahmoud Bani Asadi:
=MAP(
G3:G5,
F3:F5,
LAMBDA(
x,
y, LET(
a,
SORT(
FILTER(
B3:D25,
C3:C25=y
),
1,
-1
),
b,
INDEX(
a,
,
3
),
c,
SCAN(
0,
b,
SUM
),
d,
DROP(
VSTACK(
0,
c
),
-1
),
e,
IF(
cExcel solution 6 for Dso!, proposed by Sunny Baggu:
=MAP(
F3:F5, G3:G5, LAMBDA(x,
y, LET(
f,
45535, _a,
SORT(FILTER(B3:D25,
(C3:C25 = x) * (B3:B25 < f)),
,
-1), _l,
TAKE(
_a,
,
-1
), _d,
f - TAKE(
_a,
,
1
), _rs,
SCAN(
0,
_l,
LAMBDA(
a,
v,
a + v
)
), _b,
y - _rs, _c,
VSTACK(
FILTER(
_l,
_b > 0
),
TAKE(
FILTER(
_b,
_b > 0
),
-1
)
), SUMPRODUCT(
_c * TAKE(
_d,
ROWS(
_c
)
)
) / y
)
)
)Excel solution 7 for Dso!, proposed by Eddy Wijaya:
=LET( I,
SORT(
B3:D25,
1,
-1
), dat,
"8/31/24", MAP(
UNIQUE(
CHOOSECOLS(
I,
2
)
),
LAMBDA(
m,
LET(
f_a,
FILTER(
I,
CHOOSECOLS(
I,
2
)=m
),
limit,
XLOOKUP(
m,
F3:F5,
G3:G5
),
calc,
SCAN(
0,
DROP(
f_a,
,
2
),
LAMBDA(
a,
v,
a+v
)
),
res,
IF(
calc0,
res,
0
),
day_col,
BYROW(
TAKE(
f_a,
,
1
),
LAMBDA(
r,
--dat-r
)
),
SUMPRODUCT(
day_col,
adj_res
)/limit
)
)
)
) Excel solution 8 for Dso!, proposed by Hussein SATOUR:
=MAP(
F3:F5,
G3:G5,
LAMBDA(
x,
y,
LET(
a,
SORT(
FILTER(
B3:D25,
C3:C25=x
),
1,
-1
),
v,
INDEX(
a,
,
3
),
RT,
SCAN(
0,
v,
SUM
),
b,
FILTER(
v,
RT<=y
),
Part,
VSTACK(
b,
y-SUM(
b
)
),
Days,
"31/8"-INDEX(
a,
,
1
),
SUM(
IFNA(
Part*Days,
0
)
)/y
)
)
)Solving the challenge of Dso! with Python
Python solution 1 for Dso!, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "CH-118 DSO.xlsx"
input1 = pd.read_excel(path, usecols="B:D", skiprows=1, nrows=23)
input2 = pd.read_excel(path, usecols="F:G", skiprows=1, nrows=3)
input2.columns = input2.columns.str.replace('.1', '')
test = pd.read_excel(path, usecols="H:H", skiprows=1, nrows=3)
input1 = input1.sort_values(by=['Customer', 'Date'], ascending=[True, False])
r2 = input2.merge(input1, on='Customer', how='left')
r2['days'] = pd.to_datetime('2024-08-31') - pd.to_datetime(r2['Date'])
r2['cumsum'] = r2.groupby('Customer')['Sales'].cumsum()
r2['balance_cover'] = r2.apply(lambda row: row['Sales'] if row['cumsum'] <= row['Balance'] else row['Balance'] - (row['cumsum'] - row['Sales']), axis=1)
r2 = r2[r2['balance_cover'] > 0]
r2['weighted_days'] = r2['days'].dt.days * r2['balance_cover']
result = r2.groupby('Customer').apply(lambda x: x['weighted_days'].sum() / x['balance_cover'].sum()).reset_index(name='weighted_days')
print(result["weighted_days"].equals(test["DSO (day)"])) # TrueSolving the challenge of Dso! with Python in Excel
Python in Excel solution 1 for Dso!, proposed by Alejandro Campos:
df_c = pd.DataFrame(data)
df_c['Weighted Days'] = df_c['Sales'] * df_c['Days']
df_c.loc['Total'] = df_c[['Sales', 'Weighted Days']].sum()
df_c.loc['Total', 'DSO'] = df_c.loc['Total', 'Weighted Days'] / df_c.loc['Total', 'Sales']
df_c.fillna('')Python in Excel solution 2 for Dso!, proposed by Alejandro Campos:
df1 = xl("B2:D25", headers=True)
df2 = xl("F2:G5", headers=True)
df1 = df1.sort_values(by=['Customer', 'Date'], ascending=[True, False])
merg2 = df2.merge(df1, on='Customer', how='left')
merg2['days'] = pd.to_datetime('2024-08-31') - pd.to_datetime(merg2['Date'])
def calculate_dso(group, balance):
group['cumsum'] = group['Sales'].cumsum()
group['balance_cover'] = group.apply(lambda row: row['Sales'] if row['cumsum'] <= balance else balance - (row['cumsum'] - row['Sales']), axis=1)
group = group[group['balance_cover'] > 0]
group['DSO (day)'] = group['days'].dt.days * group['balance_cover']
return group['DSO (day)'].sum() / group['balance_cover'].sum()
result = merg2.groupby('Customer').apply(lambda x: calculate_dso(x, df2[df2['Customer'] == x.name]['Balance'].values[0])).reset_index(name='DSO (day)')
resultSolving the challenge of Dso! with R
R solution 1 for Dso!, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/CH-118 DSO.xlsx"
input1 = read_excel(path, range = "B2:D25")
input2 = read_excel(path, range = "F2:G5")
test = read_excel(path, range = "H2:H5")
r1 = input1 %>%
arrange(Customer, desc(Date))
r2 = input2 %>%
left_join(r1, by = "Customer") %>%
mutate(days = as.Date("2024/08/31") - as.Date(Date)) %>%
mutate(cumsum = cumsum(Sales), .by = Customer) %>%
mutate(balance_cover = ifelse(cumsum <= Balance, Sales, Balance - (cumsum - Sales))) %>%
filter(balance_cover > 0) %>%
mutate(weighted_days = days * balance_cover) %>%
summarise(weighted_days = as.numeric(sum(weighted_days) / sum(balance_cover)), .by = Customer)
all.equal(r2$weighted_days, test$`DSO (day)`, check.attributes = FALSE)
#> [1] TRUESolving the challenge of Dso! with Google Sheets
Google Sheets solution 1 for Dso!, proposed by Peter Krkos:
PowerQuery Solution:
https://docs.google.com/spreadsheets/d/1zR5IZLz8OT76vhaPEHfsPrw8-RDKnLyyqS49IJjdhFk/edit?gid=2037537045#gid=2037537045