Regionwise - DSO April 2010

300 270 240 210 180

Debt Class Net Sales Net Sales Net Sales Net Sales Net Sales

JUL 09 to JUL 09 AUG 09 to AUG 09 SEP 09 to SEP 09 OCT 09 to OCT 09 NOV 09 to NOV 09

A 3,749,412 35,008,168 26,940,885 14,794,286 37,056,707

B 30,876,131 37,479,412 35,008,168 26,940,885 14,794,286

Question The objective is to determine day sales outstanding (DSO) per debtor class. DSO would be computed as follows: 1. Starting from the right most cell, determine the cell till where the cumulative revenue is <= Accounts receivable balanceK7, move leftward and stop till where the cumulative revenue <= AR and note the DSO i.e. 240 (from row 3)is 20,61,887 (i.e. 15,27,81,710 - 15,07,19,823). 2. Now determine the daily sales for the next left cell i.e. 3,50,08,168/30=11,66,938. 3. Now determine the DSO for the revenue shortfall i.e. 20,61,887/11,66,939 = 1.76 4. The total DSO now is 240 + 1.76 = 241.76 ~ 242 This has to be done for many rows and doing this manually take s alot of time. I would like to automate the same via some f Solution Kindly refer to my array formula (to be confirmed by Ctrl+Shift+Enter as against the conventioned Enter) in cell M7.

150 120 90 60 30

Net Sales Net Sales Net Sales Net Sales Net Sales

DEC09 to DEC09 Jan10 to Jan10 Feb 10 to Feb 10 Mar 10 to Mar 10 Apr 10 to Apr 10

9,426,784 14,207,574 8,369,065 6,472,646 33,451,876

37,056,707 9,426,784 14,207,574 8,369,064.6 6,472,646.3

sales outstanding (DSO) per debtor class. DSO would be computed as follows:

1. Starting from the right most cell, determine the cell till where the cumulative revenue is <= Accounts receivable balance. So if the AR in cell L7 is Rs. 15,27,81,710, then starting from cell 240 (from row 3). The revenue from D7:K7 is 15,07,19,823 (which is <= AR). The revenue shortfall

This has to be done for many rows and doing this manually take s alot of time. I would like to automate the same via some formula.

(to be confirmed by Ctrl+Shift+Enter as against the conventioned Enter) in cell M7.

AR as ofApril 2010 DSO

152,781,710 241.77

124,603,097 216.29

DSO

Year Month Days Sales AR DSO

2010 OCT 28 130,267 393,276

2010 NOV 28 155,537 414,557

2010 DEC 35 222,938 371,944 61.82

2011 JAN 28 102,258 307,599 60.24

2011 FEB 28 148,733 331,462 68.63

2011 MAR 35 164,781 297,722 60.03

2011 APR 28 177,965 355,186 65.34

2011 MAY 28 162,604 393,276 67.20

2011 JUN 35 203,800 414,557 70.58

2011 JUL 28 188,777 399,683 64.22

2011 AUG 28 193,795 456,652 68.72

2011 SEP 35 222,747 436,271 65.93

2011 OCT 28 182,228 416,872 64.72

2011 NOV 28 205,311 505,650 74.56

2011 DEC 35

2012 JAN 28

2012 FEB 28

2012 MAR 35

2012 APR 28

2012 MAY 28

2012 JUN 35

2012 JUL 28

2012 AUG 28

2012 SEP 35

2012 OCT 28

2012 NOV 28

2012 DEC 35

2013 JAN 28

2013 FEB 28

2013 MAR 35

2013 APR 28

2013 MAY 28

2013 JUN 35

2013 JUL 28

2013 AUG 28

2013 SEP 35

2013 OCT 28

2013 NOV 28

2013 DEC 35

Question The objective is to determine day 1. Starting from the current cell (say cell F10), determine the cell till where the cumulative revenue is <= Accounts receifrom cell D10, move upward and stop till where the cumulative revenue <= AR and add the days i.e. revenue shortfall is 52,707 (i.e. 393,276 2. Now determine the daily sales for the value in cell D8 i.e. 164,781/35= 3. Now determine the DSO for the revenue shortfall i.e. 52,707/4,708 = 4. The total DSO now is 56 + 11.19 = 67.19 in cell F10 This has to be done for many rows and doing this manually take s alot of time. I would like to automate the same via some Solution Kindly refer to my array formula (to be confirmed by Ctrl+Shift+Enter as against the conventioned Enter) in cell F5.

The objective is to determine day sales outstanding (DSO) for each month. .DSO is computed on historical sales DSO would be computed as follows:

1. Starting from the current cell (say cell F10), determine the cell till where the cumulative revenue is <= Accounts receivable balance. So if the AR in cell E10is Rs. 3,93,276 then starting from cell D10, move upward and stop till where the cumulative revenue <= AR and add the days i.e. 28+28 =56 from rcolumn C). The revenue from D9:D10 is 340,569 (which is <= AR). The revenue shortfall is 52,707 (i.e. 393,276- 340,569).

2. Now determine the daily sales for the value in cell D8 i.e. 164,781/35=4,708.03

3. Now determine the DSO for the revenue shortfall i.e. 52,707/4,708 = 11.19

4. The total DSO now is 56 + 11.19 = 67.19 in cell F10

This has to be done for many rows and doing this manually take s alot of time. I would like to automate the same via some formula.

Kindly refer to my array formula (to be confirmed by Ctrl+Shift+Enter as against the conventioned Enter) in cell F5.

DSO would be computed as follows:

le balance. So if the AR in cell E10is Rs. 3,93,276 then starting . The revenue from D9:D10 is 340,569 (which is <= AR). The

DOS

Year Month Days COGS Inv DOS

2010 OCT 28 130,267 393,276 72.87

2010 NOV 28 155,537 414,557 72.88

2010 DEC 35 222,938 371,944 71.80

2011 JAN 28 102,258 307,599 68.02

2011 FEB 28 148,733 331,462 65.82

2011 MAR 35 164,781 297,722 55.92

2011 APR 28 177,965 355,186 58.51

2011 MAY 28 162,604 393,276 66.99

2011 JUN 35 203,800 414,557 66.18

2011 JUL 28 188,777 399,683 58.69

2011 AUG 28 193,795 456,652 69.16

2011 SEP 35 222,747 436,271 67.27

2011 OCT 28 182,228 416,872 66.04

2011 NOV 28 205,311 505,650 99.39

2011 DEC 35 102,258

2012 JAN 28 148,733

2012 FEB 28 164,781

2012 MAR 35 177,965

2012 APR 28 162,604

2012 MAY 28 203,800

2012 JUN 35 188,777

2012 JUL 28 193,795

2012 AUG 28 222,747

2012 SEP 35 182,228

2012 OCT 28 205,311

2012 NOV 28

2012 DEC 35

2013 JAN 28

2013 FEB 28

2013 MAR 35

2013 APR 28

2013 MAY 28

2013 JUN 35

2013 JUL 28

2013 AUG 28

2013 SEP 35

2013 OCT 28

2013 NOV 28

2013 DEC 35

Question The objective is to determine days of Supply(DOS) for each month. .DOS is computed on 1. Starting from the current cell (say cell F10), determine the cell till where the cumulative COGS is <= Inv balance. Sodown and stop till where the cumulative COGS <= Inv and add the days i.e. 393,276- 366,404). 2. Now determine the daily COGS for the value in cell D12i.e. 188,777/28= 3. Now determine the DOS for the COGS shortfall i.e. 26,87246,742= 4. The total DOS now is 63+ 3.98= 66.99 in cell F10 This has to be done for many rows and doing this manually take s alot of time. I would like to automate the same via some Solution Kindly refer to my array formula (to be confirmed by Ctrl+Shift+Enter as against the conventioned Enter) in cell F3.

The objective is to determine days of Supply(DOS) for each month. .DOS is computed on future COGS DOS would be computed as follows:

1. Starting from the current cell (say cell F10), determine the cell till where the cumulative COGS is <= Inv balance. So if the Inv in cell E10is Rs. 3,93,276 then starting from cell D10, move down and stop till where the cumulative COGS <= Inv and add the days i.e. 28+35=63 from rcolumn C). The COGS from D10:D11 is 366,404(which is <= Inv). The COGS shortfall is 26,872 (i.e.

2. Now determine the daily COGS for the value in cell D12i.e. 188,777/28=6,742.04

3. Now determine the DOS for the COGS shortfall i.e. 26,87246,742= 3.98

4. The total DOS now is 63+ 3.98= 66.99 in cell F10

This has to be done for many rows and doing this manually take s alot of time. I would like to automate the same via some formula.

Kindly refer to my array formula (to be confirmed by Ctrl+Shift+Enter as against the conventioned Enter) in cell F3.

the Inv in cell E10is Rs. 3,93,276 then starting from cell D10, move . The COGS from D10:D11 is 366,404(which is <= Inv). The COGS shortfall is 26,872 (i.e.

Days 31.00 30.00

Region Closing Debt 2012.05 2012.06Afghanistan 382,415.14 362,324.87 409,056.98 CIS 354,370.49 282,580.89 230,687.15 Corporate Investigations 967,736.68 643,297.44 569,258.96 Africa 138,075.49 112,494.57 83,775.52 Fraud 199,641.68 133,873.97 169,316.64 Middle East 121,278.54 91,302.65 163,970.60 Western Europe 526,274.03 279,919.43 152,196.20 Crisis and Security Consulting 7,780,556.92 3,247,811.91 1,878,731.82 Africa 932,002.24 473,134.26 489,471.54 Africa Consulting 242,833.35 64,046.06 205,803.19 Africa Protection 39,204.24 18,484.89 47,653.78 Algeria 375,713.94 291,036.01 187,539.91 Crisis Management 357,970.09 95,086.37 30,643.95 Europe Consulting 153,539.66 130,035.30 72,867.92

Europe Embedded Security 54,427.90 82,332.30 15,409.14 Europe Protection 27,661.91 21,146.80 7,297.12 Libya 2,973,385.99 1,405,722.92 7,221.36 Middle East 705,008.55 209,297.44 276,463.98 Nigeria 1,314,321.14 412,764.07 272,448.78 Security Design Solutions 276,650.52 139,946.04- 52,973.99

31.00 30.00 31.00 31.00

2012.07 2012.08 2012.09 Sales 2012.10 DSO409,024.92 417,525.03 400,766.63 366,764.20 32.21 228,670.83 162,196.80 192,287.19 263,113.89 45.71 457,906.92 405,548.23 558,638.16 557,846.78 53.75

64,020.65 93,551.16 63,134.35 79,746.86 59.64 113,526.16 111,466.43 146,064.98 150,388.11 41.45

7,816.40 16,390.80 61,888.60 88,910.24 47.21 272,543.71 184,139.84 287,550.23 238,801.57 61.99

2,384,873.06 2,837,590.15 2,539,935.79 2,803,209.33 87.77 523,777.64 524,368.25 348,061.26 345,165.97 75.66

4,110.48 71,124.23 62,368.98 111,777.41 90.97 159,194.73 80,577.19 3,933.75 27,808.41 64.78 194,099.90 178,780.48 157,929.41 174,988.64 69.18

97,079.40 184,785.50 61,057.52 149,617.78 85.91 69,007.46 128,435.70 98,673.38 66,970.84 58.20

8,059.98 48,742.28 33,048.91 28,092.92 55.70 6,287.93 11,844.85 12,182.09 11,681.02 71.62

724,588.68 786,316.14 1,165,084.45 1,106,557.46 88.77 246,578.90 217,266.27 256,319.93 226,999.41 92.56 191,604.14 382,135.64 223,451.80 350,419.17 141.36

16,966.29 80,628.79 18,839.28 43,015.38 #N/A

Final DSO32.21 45.71 53.75 59.64 41.45 47.21 61.99 87.77 75.66 90.97 64.78 69.18 85.91 58.20

55.70 71.62 88.77 92.56

141.36 Greater than 184