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 :-
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
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 PAYMENTSMONETIZABLE SUBBASE VS BP.xls
MONETISATION FOR THIS PURPOSE = ARPU x SUBBASE x 12

BP TO RISE IN PROPORTION TO THE INCREASED CONNECTIVITY (DECLARED)