DSO Table

[HaFpOwer]

Hellow,

I am trying to calculate monthly DSO based on trial balance data that is updated every month, basically DSO calculated based on two tables,

Table  1
Trial Balance
	Jan	Jan	Feb	Feb
Group TB.Class	Period Balance	End Bal Amt	Period Balance	End Bal Amt
1.AR Rec	336,344.08	4,595,880.91	269,075.27	3,676,704.73
2.AR Prov	0.00	-843,767.44	0.00	-675,013.95
3.AR Clearing	-30,461.02	-19,846.54	-24,368.82	-15,877.24
4.AR Deposit	0.00	-105,496.78	0.00	-84,397.42
5.AR UnApp	-125.50	-5,731.29	-100.40	-4,585.03
6.AR On Acc	-23,136.14	-189,230.09	-18,508.91	-151,384.07
8.Rev Ops 1	-2,976.00	-14,182.00	-2,380.80	-11,345.60
8.Rev Ops 2	-986,208.25	-4,872,281.73	-788,966.60	-3,897,825.39
8.Rev Ops 3	-670,044.09	-4,274,803.44	-536,035.27	-3,419,842.75
8.Rev Ops 4	-41,142.54	-218,343.23	-32,914.03	-174,674.58
8.Rev Ops 5	-248,763.93	-1,537,963.16	-199,011.14	-1,230,370.52
8.Rev Ops 6	-300.00	-1,800.00	-240.00	-1,440.00
8.Rev Ops 7	-20,196.44	-98,937.48	-16,157.15	-79,149.98
8.Rev Ops 8	-680.51	-7,901.97	-544.41	-6,321.57
Total Receivable Balance	3,431,808.77		2,745,447.02
	Sum from line 1 to 6

note that below DSO table based on above and is going to be calculated every month. 

2018	Jan	Feb	Mar
Period Revenue *(Sum from line 7 to end)	1,970,312	1,576,249
Total Sales Annualised	23,643,741	21,279,367
Average Receivable Balance	3,431,809	3,088,628
Receivable Turnover	6.9	6.9
DSO	53	53
Number of Days	365	365

Pleaes advise how i can create usch calculation/ table and generate it every time i use new data. 

Thank you

[HaFpOwer · Accepted solution]

Thank you @parry2k for your reply

please find example in excel format in below link

https://1drv.ms/u/s!AuDSNniB2nu8kRZVUw9SVxrvBfyJ?e=fwsYry

Thank you

[HaFpOwer · Accepted solution]

Hi,

let me try to make it more clear i am trying to create table that include below lines for each month, ,

1. To have a Total Receivable Balance by creating a formula or table to calculate values of first table based on group TB class, following below formula

•    Total Receivable Balance = 1.AR Rece + 2.AR Prov. + 3.AR Clearing + 4.AR Deposit + 5.AR Unapp + 6.AR On Acc

1. In table 2, each row is calculated based on below formula,

• a-Period Revenue = Sum(8.Rev Ops 1 + 8.Rev Ops 2 + .....+8.Rev Ops 😎
• b-Total Sales Annualised = (Total Period Revenue / Number of periods)*12
• c-Average Receivable Balance = Total Receivable Balance / number of periods)
• d-Receivable Turnover = Total Sales Annualised / Average Receivable Balance
• e-DSO = 365 / Receivable Turnover

Hope you can support me in my query.

Thank you

[parry2k]

@HaFpOwer the table you shown here is that how your raw data is? You mentioned two table but here it is only one.

I would recommend to put sample raw data in excel file and share the calculation so that solution can be provided based on that.

[HaFpOwer · Accepted solution]

Thank you @parry2k for your reply

please find example in excel format in below link

https://1drv.ms/u/s!AuDSNniB2nu8kRZVUw9SVxrvBfyJ?e=fwsYry

Thank you