Forum Discussion
DSO Table
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
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
Hi,
let me try to make it more clear i am trying to create table that include below lines for each month, ,
- 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
- 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 8)
- 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
3 Replies
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
Hi,
let me try to make it more clear i am trying to create table that include below lines for each month, ,
- 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
- 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 8)
- 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
