|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 'What-If' Tables - Working Capital |
|
|
|
|
|
|
|
|
|
| The
working capital 'what-if' tables (below) assess the impact of incremental
changes in inventories, |
|
| receivables
and payables on monthly cashflows and cash balances. These tables are based
on the |
|
| monthly
assumptions and projections and are updated automatically whenever the model
recalculates. |
|
| To use this
facility, monthly projections must be complete (but not necessarily final). |
|
|
| For
each table, you can choose to phase in the percentage changes over an
interval from one to six months. |
|
| If the
number of months being used for phasing-in is altered, you must recalculate
to update the analyses. |
|
|
| 1
- Changes in Inventory Levels |
|
|
|
|
Number of months over
which % changes will be phased in: |
6 |
<< Enter number between 1 and 6 |
|
|
|
% |
|
Net Cashflow |
|
Year End Cash |
|
Peak Monthly |
|
Peak Monthly |
|
Inventory |
|
|
|
Change |
|
for Year |
|
Surplus (deficit) |
|
Cash Surplus |
|
Cash Deficit |
|
Levels |
|
|
|
|
|
$000 |
|
$000 |
|
$000 |
|
$000 |
|
Days* |
|
|
|
-30% |
|
764.5
|
|
654.5 |
|
667.1 |
|
(232.9) |
|
16 |
|
|
|
-15% |
|
709.3
|
|
599.3 |
|
613.3 |
|
(270.5) |
|
20 |
|
|
|
Base Case |
|
654.2 |
|
544.2 |
|
559.4 |
|
(308.2) |
|
24 |
|
|
|
+15% |
|
599.0
|
|
489.0 |
|
505.5 |
|
(345.9) |
|
27 |
|
|
|
+30% |
|
543.9
|
|
433.9 |
|
451.6 |
|
(383.5) |
|
31 |
|
|
|
'*
Days sales - based on average monthly sales and inventory levels for fourth
quarter of year |
|
|
|
The
following table summarizes the deviations from the base case: |
|
|
|
% |
|
|
Net Cashflow |
|
Year End Cash |
|
Peak Monthly |
|
Peak Monthly |
|
|
|
Change |
|
|
for Year |
|
Surplus (deficit) |
|
Cash Surplus |
|
Cash Deficit |
|
|
|
|
|
|
$000 |
|
$000 |
|
$000 |
|
$000 |
|
|
|
-30% |
|
|
110.3
|
|
110.3
|
|
107.8 |
|
75.3 |
|
|
|
-15% |
|
|
55.1
|
|
55.1
|
|
53.9 |
|
37.7 |
|
|
|
Base Case |
|
|
0 |
|
0 |
|
0 |
|
0 |
|
|
|
+15% |
|
|
(55.1) |
|
(55.1) |
|
(53.9) |
|
(37.7) |
|
|
|
+30% |
|
|
(110.3) |
|
(110.3) |
|
(107.8) |
|
(75.3) |
|
|
|
|
| 2
- Changes in Levels of Receivables |
|
|
|
|
Number of months over
which % changes will be phased in: |
6 |
<< Enter number between 1 and 6 |
|
|
|
% |
|
Net Cashflow |
|
Year End Cash |
|
Peak Monthly |
|
Peak Monthly |
|
Accounts |
|
|
|
Change |
|
for Year |
|
Surplus (deficit) |
|
Cash Surplus |
|
Cash Deficit |
|
Receivable |
|
|
|
|
|
$000 |
|
$000 |
|
$000 |
|
$000 |
|
Days* |
|
|
|
-30% |
|
836.6
|
|
726.6 |
|
732.1 |
|
(201.9) |
|
27 |
|
|
|
-15% |
|
745.4
|
|
635.4 |
|
645.7 |
|
(255.1) |
|
32 |
|
|
|
Base Case |
|
654.2 |
|
544.2 |
|
559.4 |
|
(308.2) |
|
38 |
|
|
|
+15% |
|
563.0
|
|
453.0 |
|
473.0 |
|
(361.3) |
|
44 |
|
|
|
+30% |
|
471.8 |
|
361.8 |
|
386.7 |
|
(414.5) |
|
50 |
|
|
|
'*
Days sales - based on average monthly sales and receivable levels for fourth
quarter of year |
|
|
|
The
following table summarizes the deviations from the base case: |
|
|
|
% |
|
|
Net Cashflow |
|
Year End Cash |
|
Peak Monthly |
|
Peak Monthly |
|
|
|
Change |
|
|
for Year |
|
Surplus (deficit) |
|
Cash Surplus |
|
Cash Deficit |
|
|
|
|
|
|
$000 |
|
$000 |
|
$000 |
|
$000 |
|
|
|
-30% |
|
|
182.4
|
|
182.4
|
|
172.7 |
|
106.3 |
|
|
|
-15% |
|
|
91.2
|
|
91.2
|
|
86.4 |
|
53.1 |
|
|
|
Base Case |
|
|
0 |
|
0 |
|
0 |
|
0 |
|
|
|
+15% |
|
|
(91.2) |
|
(91.2) |
|
(86.4) |
|
(53.1) |
|
|
|
+30% |
|
|
(182.4) |
|
(182.4) |
|
(172.7) |
|
(106.3) |
|
|
|
|
|
| 3
- Changes in Levels of Payables |
|
|
|
|
Number of months over
which % changes will be phased in: |
6 |
<< Enter number between 1 and 6 |
|
|
|
% |
|
Net Cashflow |
|
Year End Cash |
|
Peak Monthly |
|
Peak Monthly |
|
Accounts |
|
|
|
Change |
|
for Year |
|
Surplus (deficit) |
|
Cash Surplus |
|
Cash Deficit |
|
Payable |
|
|
|
|
|
$000 |
|
$000 |
|
$000 |
|
$000 |
|
Days* |
|
|
|
-30% |
|
526.2
|
|
416.2 |
|
436.2 |
|
(388.5) |
|
21 |
|
|
|
-15% |
|
590.2
|
|
480.2 |
|
497.8 |
|
(348.3) |
|
26 |
|
|
|
Base Case |
|
654.2 |
|
544.2 |
|
559.4 |
|
(308.2) |
|
31 |
|
|
|
+15% |
|
718.2
|
|
608.2 |
|
621.0 |
|
(268.1) |
|
35 |
|
|
|
+30% |
|
782.2 |
|
672.2 |
|
682.6 |
|
(227.9) |
|
40 |
|
|
|
'*
Days costs - based on average monthly costs and payable levels for fourth
quarter of year |
|
|
|
The
following table summarizes the deviations from the base case: |
|
|
|
% |
|
|
Net Cashflow |
|
Year End Cash |
|
Peak Monthly |
|
Peak Monthly |
|
|
|
Change |
|
|
for Year |
|
Surplus (deficit) |
|
Cash Surplus |
|
Cash Deficit |
|
|
|
|
|
|
$000 |
|
$000 |
|
$000 |
|
$000 |
|
|
|
-30% |
|
|
(128.0) |
|
(128.0) |
|
(123.2) |
|
(80.3) |
|
|
|
-15% |
|
|
(64.0) |
|
(64.0) |
|
(61.6) |
|
(40.1) |
|
|
|
Base Case |
|
|
0 |
|
0 |
|
0 |
|
0 |
|
|
|
+15% |
|
|
64.0
|
|
64.0
|
|
61.6 |
|
40.1 |
|
|
|
+30% |
|
|
128.0
|
|
128.0
|
|
123.2 |
|
80.3 |
|
|
|
|
|
|
| Calculations
- Not printed with What-Ifs Report |
|
| |
Opening |
Jan |
Feb |
Mar |
Apr |
May |
Jun |
Jul |
Aug |
Sep |
Oct |
Nov |
Dec |
|
| Base
case data: |
|
| Inventory levels |
266.3 |
## |
284.156 |
291.428 |
304.965 |
301.361 |
321.621 |
327.481 |
336.603 |
348.657 |
359.194 |
363.418 |
367.629 |
|
| Receivables |
285 |
## |
403.037 |
390.508 |
441.985 |
425.033 |
480.887 |
488.672 |
520.203 |
561.043 |
575.744 |
586.69 |
607.985 |
|
| Payables |
210 |
## |
342.275 |
310.743 |
345.06 |
321.147 |
372.927 |
365.267 |
387.363 |
407.162 |
410.673 |
416.696 |
426.642 |
|
| Net cashflows |
|
## |
(0.7) |
(25.1) |
(59.6) |
(24.7) |
99.1 |
273.0 |
(11.1) |
261.5 |
245.1 |
(114.8) |
99.6 |
|
| Closing cash balances |
(110.0) |
## |
(198.8) |
(223.9) |
(283.5) |
(308.2) |
(209.1) |
63.9 |
52.8 |
314.3 |
559.4 |
444.6 |
544.2 |
|
| |
|
|
| Inventory
Level Changes |
|
|
| %
for phase-in periods: |
|
|
| -30% |
|
## |
(0.10) |
(0.15) |
(0.20) |
(0.25) |
(0.30) |
(0.30) |
(0.30) |
(0.30) |
(0.30) |
(0.30) |
(0.30) |
|
| -15% |
|
## |
(0.05) |
(0.08) |
(0.10) |
(0.13) |
(0.15) |
(0.15) |
(0.15) |
(0.15) |
(0.15) |
(0.15) |
(0.15) |
|
| Base |
|
|
|
|
| +15% |
|
## |
0.05
|
0.08
|
0.10
|
0.13
|
0.15
|
0.15
|
0.15
|
0.15
|
0.15
|
0.15
|
0.15
|
|
| +30% |
|
## |
0.10
|
0.15
|
0.20
|
0.25
|
0.30
|
0.30
|
0.30
|
0.30
|
0.30
|
0.30
|
0.30
|
|
| Inventory: |
|
| -30% |
266.3 |
## |
255.7
|
247.7
|
244.0
|
226.0
|
225.1
|
229.2
|
235.6
|
244.1
|
251.4
|
254.4
|
257.3
|
|
| -15% |
266.3 |
## |
269.9
|
269.6
|
274.5
|
263.7
|
273.4
|
278.4
|
286.1
|
296.4
|
305.3
|
308.9
|
312.5
|
|
| Base |
266.3 |
## |
284.2
|
291.4
|
305.0
|
301.4
|
321.6
|
327.5
|
336.6
|
348.7
|
359.2
|
363.4
|
367.6
|
|
| +15% |
266.3 |
## |
298.4
|
313.3
|
335.5
|
339.0
|
369.9
|
376.6
|
387.1
|
401.0
|
413.1
|
417.9
|
422.8
|
|
| +30% |
266.3 |
## |
312.6
|
335.1
|
366.0
|
376.7
|
418.1
|
425.7
|
437.6
|
453.3
|
467.0
|
472.4
|
477.9
|
|
| Net cashflows: |
|
| -30% |
|
## |
14.4 |
(9.8) |
(42.3) |
(10.4) |
120.2 |
274.8 |
(8.4) |
265.1 |
248.3 |
(113.5) |
100.8 |
|
| -15% |
|
## |
6.8 |
(17.4) |
(50.9) |
(17.6) |
109.6 |
273.9 |
(9.8) |
263.3 |
246.7 |
(114.1) |
100.2 |
|
| Base |
|
## |
(0.7) |
(25.1) |
(59.6) |
(24.7) |
99.1 |
273.0 |
(11.1) |
261.5 |
245.1 |
(114.8) |
99.6 |
|
| +15% |
|
## |
(8.3) |
(32.7) |
(68.2) |
(31.9) |
88.5 |
272.2 |
(12.5) |
259.7 |
243.5 |
(115.4) |
98.9 |
|
| +30% |
|
## |
(15.8) |
(40.4) |
(76.8) |
(39.1) |
77.9 |
271.3 |
(13.9) |
257.9 |
241.9 |
(116.0) |
98.3 |
|
| Closing
cash balances: |
|
| -30% |
(110.0) |
## |
(170.4) |
(180.2) |
(222.5) |
(232.9) |
(112.7) |
162.1
|
153.7
|
418.9
|
667.1
|
553.6
|
654.5
|
|
| -15% |
(110.0) |
## |
(184.6) |
(202.0) |
(253.0) |
(270.5) |
(160.9) |
113.0
|
103.2
|
366.6
|
613.3
|
499.1
|
599.3
|
|
| Base |
(110.0) |
## |
(198.8) |
(223.9) |
(283.5) |
(308.2) |
(209.1) |
63.9
|
52.8
|
314.3
|
559.4
|
444.6
|
544.2
|
|
| +15% |
(110.0) |
## |
(213.0) |
(245.8) |
(314.0) |
(345.9) |
(257.4) |
14.8
|
2.3
|
262.0
|
505.5
|
390.1
|
489.0
|
|
| +30% |
(110.0) |
## |
(227.2) |
(267.6) |
(344.5) |
(383.5) |
(305.6) |
(34.4) |
(48.2) |
209.7
|
451.6
|
335.6
|
433.9
|
|
| |
| Receivables
Level Changes |
|
|
| %
for phase-in periods: |
|
|
| -30% |
|
## |
(0.10) |
(0.15) |
(0.20) |
(0.25) |
(0.30) |
(0.30) |
(0.30) |
(0.30) |
(0.30) |
(0.30) |
(0.30) |
|
| -15% |
|
## |
(0.05) |
(0.08) |
(0.10) |
(0.13) |
(0.15) |
(0.15) |
(0.15) |
(0.15) |
(0.15) |
(0.15) |
(0.15) |
|
| Base |
|
|
|
|
| +15% |
|
## |
0.05
|
0.08
|
0.10
|
0.13
|
0.15
|
0.15
|
0.15
|
0.15
|
0.15
|
0.15
|
0.15
|
|
| +30% |
|
## |
0.10
|
0.15
|
0.20
|
0.25
|
0.30
|
0.30
|
0.30
|
0.30
|
0.30
|
0.30
|
0.30
|
|
| Receivables: |
|
| -30% |
285 |
## |
362.7
|
331.9
|
353.6
|
318.8
|
336.6
|
342.1
|
364.1
|
392.7
|
403.0
|
410.7
|
425.6
|
|
| -15% |
285 |
## |
382.9
|
361.2
|
397.8
|
371.9
|
408.8
|
415.4
|
442.2
|
476.9
|
489.4
|
498.7
|
516.8
|
|
| Base |
285 |
## |
403.0
|
390.5
|
442.0
|
425.0
|
480.9
|
488.7
|
520.2
|
561.0
|
575.7
|
586.7
|
608.0
|
|
| +15% |
285 |
## |
423.2
|
419.8
|
486.2
|
478.2
|
553.0
|
562.0
|
598.2
|
645.2
|
662.1
|
674.7
|
699.2
|
|
| +30% |
285 |
## |
443.3
|
449.1
|
530.4
|
531.3
|
625.2
|
635.3
|
676.3
|
729.4
|
748.5
|
762.7
|
790.4
|
|
| Net cashflows: |
|
| -30% |
|
## |
20.2 |
(6.8) |
(29.7) |
(6.9) |
137.1 |
275.4 |
(1.7) |
273.8 |
249.5 |
(111.5) |
106.0 |
|
| -15% |
|
## |
9.7 |
(16.0) |
(44.7) |
(15.8) |
118.1 |
274.2 |
(6.4) |
267.6 |
247.3 |
(113.1) |
102.8 |
|
| Base |
|
## |
(0.7) |
(25.1) |
(59.6) |
(24.7) |
99.1 |
273.0 |
(11.1) |
261.5 |
245.1 |
(114.8) |
99.6 |
|
| +15% |
|
## |
(11.2) |
(34.2) |
(74.5) |
(33.7) |
80.1 |
271.9 |
(15.9) |
255.4 |
242.9 |
(116.4) |
96.4 |
|
| +30% |
|
## |
(21.6) |
(43.4) |
(89.4) |
(42.6) |
61.0 |
270.7 |
(20.6) |
249.3 |
240.7 |
(118.0) |
93.2 |
|
| Closing
cash balances: |
|
| -30% |
(110.0) |
## |
(158.5) |
