Roy2U
I created a new model to first produce ACV metrics, and from these derive revenue. The model compounds bi-annually, so it shows the compounding rate in both annual and bi-annual terms, although calculations use the bi-annual rate.
My recent focus has been to make my spreadsheet work. I do not build spreadsheets frequently, so it takes me time to get the results I want. I'll work on what reasonable ACV growth rates should be use for future half years. The growth rates used now were rammed in without much thought, mainly to ensure the logic worked.
NA's ACV growth rate for the 12 months ending June 2021 was stated to be 57%, Y-on-Y. By my model on a bi-annual basis, FY21 grew 48.54% in H1, and 60.73% in H2, which suggests a high bi-annual rate for FY22H2, so the near-future growth rate should be high. I plugged in 60% for H2, but I may have reduced that too rapidly in subsequent halves, or maybe not.
I have not given ANZ much thought yet, but I think that the new initiatives (AI, etc) should give the ACV a nudge in the near future. I am sure there are yet glitches in my model that I'll find in future.
My spreadsheet did not paste well into HC, so to start, I converted some notes to text – namely:
NEARMAP growth model – prototype
Do not force new ACV % growth rate if the ACV dollars are manually inserted.
Forced growth rate is only used in outyears when ACV forecast is projected via the growth rates manually inserted.
Model alters yearly growth rate to compound half-yearly, so insert desired growth via the desired annual effect.
Model converts annual compounding growth (A) to bi-annual (B) via B = (A+1)^(1/2) -1
The reason for bi-annual ACV metrics is that estimated revenue uses them to recognise time lag.
This attempts to keep a nexus between ACV and revenue, but it does not account for lumpy ACF changes
The art is to select reasonable ACV growth rates for future periods, which is easier said than done.
8 Estimate-Reported difference are caused by timing of ACV events, and tempo of acceleration.
The rest posted reasonably well after I reduced wordy text in cells.
| Column 1 | Column 2 | Column 3 | Column 4 | Column 5 | Column 6 | Column 7 | Column 8 | Column 9 | Column 10 | Column 11 | Column 12 | Column 13 | Column 14 | Column 15 |
---|
1 | $US million | 17H1 | 17H2 | 18H1 | 18H2 | 19H1 | 19H2 | 20H1 | 20H2 | 21H1 | 21H2 | 22H1 | 22H2 | 23H1 | 23H2 |
---|
2 | NA ACV | | | | | | | | | | | | | | |
---|
3 | ACV inserted | 3.1 | 5.3 | 8.5 | 12.9 | 17.6 | 22.7 | 24.9 | 28.8 | 35.1 | 44.5 | | | | |
---|
4 | ACV used | 3.1 | 5.3 | 8.5 | 12.9 | 17.6 | 22.7 | 24.9 | 28.8 | 35.1 | 44.5 | 56.29 | 68.94 | 81.57 | 96.51 |
---|
5 | Dollar growth | | 2.2 | 3.2 | 4.4 | 4.7 | 5.1 | 2.2 | 3.9 | 6.3 | 9.4 | 11.79 | 12.65 | 12.63 | 14.94 |
---|
6 | % bi-annual | | 70.97% | 60.38% | 51.76% | 36.43% | 28.98% | 9.69% | 15.66% | 21.88% | 26.78% | 26.49% | 22.47% | 18.32% | 14.02% |
---|
7 | % Yrly force | See Note 1 | | | | | | | | | | 60.00% | 50.00% | 40.00% | 30.00% |
---|
8 | Yrly % effect | | 192.30% | 157.21% | 130.33% | 86.14% | 66.35% | 20.32% | 33.78% | 48.54% | 60.73% | 60.00% | 50.00% | 40.00% | 30.00% |
---|
9 | 1.38 | ◄ Xchange | | | | | | | | | | | | | |
---|
10 | Australian $ | 4.278 | 7.314 | 11.73 | 17.802 | 24.288 | 31.326 | 34.362 | 39.744 | 48.438 | 61.41 | 77.6802 | 95.1372 | 112.5666 | 133.1838 |
---|
11 | | | | | | | | | | | | | | | |
---|
12 | $AU million | 17H1 | 17H2 | 18H1 | 18H2 | 19H1 | 19H2 | 20H1 | 20H2 | 21H1 | 21H2 | 22H1 | 22H2 | 23H1 | 23H2 |
---|
13 | ANZ ACV | | | | | | | | | | | | | | |
---|
14 | ACV inserted | 37 | 40 | 43.3 | 48.8 | 53.3 | 57.9 | 61 | 64.5 | 66.6 | 69.1 | | | | |
---|
15 | ACV used | 37 | 40 | 43.3 | 48.8 | 53.3 | 57.9 | 61 | 64.5 | 66.6 | 69.1 | 72.14 | 75.32 | 78.64 | 82.1 |
---|
16 | Dollar growth | | 3 | 3.3 | 5.5 | 4.5 | 4.6 | 3.1 | 3.5 | 2.1 | 2.5 | 3.04 | 3.18 | 3.32 | 3.46 |
---|
17 | % bi-annual | | 8.11% | 8.25% | 12.70% | 9.22% | 8.63% | 5.35% | 5.74% | 3.26% | 4.40% | 4.40% | 4.40% | 4.40% | 4.40% |
---|
18 | % Yrly force | See Note 1 | | | | | | | | | 9.00% | 9.00% | 9.00% | 9.00% | 9.00% |
---|
19 | Yrly % effect | | 16.87% | 17.18% | 27.02% | 19.29% | 18.01% | 10.99% | 11.80% | 6.62% | 9.00% | 9.00% | 9.00% | 9.00% | 9.00% |
---|
20 | | | | | | | | | | | | | | | |
---|
21 | ACV | | | | | | | | | | | | | | |
---|
22 | | 17H1 | 17H2 | 18H1 | 18H2 | 19H1 | 19H2 | 20H1 | 20H2 | 21H1 | 21H2 | 22H1 | 22H2 | 23H1 | 23H2 |
---|
23 | AN & ANZ | 41.28 | 47.31 | 55.03 | 66.6 | 77.59 | 89.23 | 95.36 | 104.24 | 115.04 | 130.51 | 149.82 | 170.46 | 191.21 | 215.28 |
---|
24 | | | | | | | Note 8► | reported ► | 106.437 | reported ► | 128.21 | | | | |
---|
25 | Revenue | | 44.295 | 51.17 | 60.815 | 72.095 | 83.41 | 92.3 | 99.8 | 109.64 | 122.78 | 140.17 | 160.14 | 180.84 | 203.25 |
---|
26 | | | | | | | | reported ► | 96.714 | reported ► | 113.431 | | | | |
---|