View attachment MONETIZABLE SUBBASE VS BP.xlsPlease find below a problem which I am trying to find a possible solution in Excel
Background :-
The Indian Government mandate on Cable TV Digitization has ushered in a new Capex cycle
PFA (MONETIZABLE SUBBASE VS BP)
The graphs are representative of :-
1. Growth in Net Revenue (POLYNOMIAL EQUATION :- Y = 0.1304X^2 + 0.1132X + 0.21
2. Growth in Broadcaster payments (Digitization leads to higher declared sub base) (POLYNOMIAL EQUATION :- Y = 0.0554X^2 + 0.2611X + 0.88
ASSUMPTION :-
1. Net Revenue Growth Curve will trail the BP Growth curve till end 2014 (Consolidation Phase :- 18 months)
OTHER INFORMATION :-
o Net Revenue = ARPU x Sub base (Sub base can be assumed)
3. ARPU for 2012 - 2017 have been provided in the table (PFA) and also BELOW :-
Data :-
OBJECTIVE :-
Is it possible to use EXCEL Tools to arrive at values for BP (as a % of Net Revenue) for 2013 - 2017 which will :-
· Align BP Graph with the Monetizable Sub base Graph
a. Sum of BP apportioned over the period 2013 – 2017 (COLUMN F) & BP @ 33% (COLUMN H) be equal (on Time Value of Money basis)
IS IT POSSIBLE TO DO A VALIDATION OF THE ASSUMPTION PREMISE
BP :- BROADCASTER PAYMENTSView attachment MONETIZABLE SUBBASE VS BP.xls
MONETISATION FOR THIS PURPOSE = ARPU x SUBBASE x 12
BP TO RISE IN PROPORTION TO THE INCREASED CONNECTIVITY (DECLARED)
Background :-
The Indian Government mandate on Cable TV Digitization has ushered in a new Capex cycle
PFA (MONETIZABLE SUBBASE VS BP)
The graphs are representative of :-
1. Growth in Net Revenue (POLYNOMIAL EQUATION :- Y = 0.1304X^2 + 0.1132X + 0.21
2. Growth in Broadcaster payments (Digitization leads to higher declared sub base) (POLYNOMIAL EQUATION :- Y = 0.0554X^2 + 0.2611X + 0.88
ASSUMPTION :-
1. Net Revenue Growth Curve will trail the BP Growth curve till end 2014 (Consolidation Phase :- 18 months)
OTHER INFORMATION :-
o Net Revenue = ARPU x Sub base (Sub base can be assumed)
3. ARPU for 2012 - 2017 have been provided in the table (PFA) and also BELOW :-
Data :-
MONETISABLE SUBBASE (x TIMES) A | BP (x TIMES) B | ARPU (INR) C | MONETIZABLE ARPU (D = AxC) | BP DAS (F = B x 56) | BP% (G = F/D) | BP @ 33% OF D (H) | H-F | ||
2012 | 0.8 | 1 | 170 | 136 | 56 | 41 | 45 | -11 | |
2013 | 0.7 | 2.1 | 170 | 119 | 118 | 99 | 39 | -79 | |
2014 | 1 | 2.1 | 170 | 170 | 118 | 69 | 56 | -62 | |
2015 | 3 | 2.1 | 226 | 678 | 118 | 17 | 224 | 106 | |
2016 | 5 | 4.25 | 253 | 1265 | 238 | 19 | 417 | 179 | |
2017 | 5 | 4.25 | 289 | 1445 | 238 | 17 | 477 | 238 | |
886 | 1258 | 372 |
Is it possible to use EXCEL Tools to arrive at values for BP (as a % of Net Revenue) for 2013 - 2017 which will :-
· Align BP Graph with the Monetizable Sub base Graph
a. Sum of BP apportioned over the period 2013 – 2017 (COLUMN F) & BP @ 33% (COLUMN H) be equal (on Time Value of Money basis)
IS IT POSSIBLE TO DO A VALIDATION OF THE ASSUMPTION PREMISE
BP :- BROADCASTER PAYMENTSView attachment MONETIZABLE SUBBASE VS BP.xls
MONETISATION FOR THIS PURPOSE = ARPU x SUBBASE x 12
BP TO RISE IN PROPORTION TO THE INCREASED CONNECTIVITY (DECLARED)