Using Excel to formulate Equations

[aneeta]

Hello,

Can you suggest an Excel methodology to set up Mathematical Equations for the following Twin Conditions (as mandated by the Regulatory Authority).


Twin Condition I for TV Broadcasters for selling their content to Cable TV Service Providers :-


To Sell their content (TV Channels) to MSOs (viz. Cable TV Service Providers) as a Bouquet (Packet), Broadcasters have to abide by the following Twin Conditions :-

A> Bouquet Rate > = 66% of the Sum of Ala Carte Rates (Standalone) of the TV Channels

B> Average Bouquet Rate > = 33% of Each Ala Carte Rate of the TV Channel in that Bouquet (Details on Page 700, Link -http://www.tdsat.nic.in/New%20Compendium19.11.2008/TD%20SET%20VOL-2-PDF/4th%20October-2007(696-738).pdf)


The implicit principle behind Twin Condition I is that If I buy N different things together as a packet (Bouquet), there will be a definite reduction in price. But, the reduction is in such a way that :-

1> It cannot be below a certain % (66) of the total price AND

2> The average selling price of the TV Channels in the Bouquet will not be below a certain % (33) of their individual prices. The % (2/3 & 1/3) depend on the profit and pricing mechanism

Twin Condition II for Cable TV service Providers for reselling Content to Retail Customers (Cable TV Subscribers) :-

To resell this content to the retail subscriber, MSOs have to abide by the following Twin conditions :-

A> Ala Carte Rates for end consumer (Retail) < = 2 x (Ala Carte Rates provided by the Broadcaster to the MSOs)

B> Ala Carte of a TV Channel < = 3 x Ascribed Value of a TV Channel where Ascribed Value is the Ala Carte Rate rationalised with respect to the Bouquet rate. (Details on page 8, TARIFF ORDER DATED 20TH SEPT, 2013; LINK - http://www.cablequest.org/pdfs/trai/Tariff-amendments-for-notification-english-20sept2013.pdf )


Twin Condition I is the MSO's content purchase and Twin Condition II is the MSO's content sale.

Using Excel to formulate these Twin Conditions into Mathematical equations can help in determining the profit margin which the Regulatory Authority’s Mandate entails for the Cable TV service Provider

Thanks

 

[Somendra Misra]

Hi Aneeta,

Welcome to Chandoo.org forum.

As a new user you must go through below link :
http://chandoo.org/forum/forums/new-users-please-start-here.14/

Now regarding your problem, you must understand that most of the users here are not from TV Channel broadcasting system, which can understand your terminology.

Than I would also suggest you to upload a sample file with the data that is fetched into your system. You should also mention in it what will the inputs on which the equations has to be formed and if possible your required output based on your sample data.

Regards,

 

[aneeta]

Thanks
We can take the Twin Condition I wherein the Implicit Principle for TV Broadcasters for Selling Content to MSOs is :-
1> The Bouquet Rate cannot be below a certain % (66) of the total price (i.e. Sum of Ala Carte rates forming part of that Bouquet)

AND

2> The average selling price of the TV Channels in the Bouquet will not be below a certain % (33) of their individual prices.
The % (2/3 & 1/3) depend on the profit and pricing mechanism
Why 2/3 and 1/3 ((1/3-2/3 conjecture ?)


SAMPLE DATA :-




ABOVE BOUQUET HAS BEEN PRICED @ INR 43.2

AVERAGE BOUQUET RATE = INR 2.88 (PLEASE SEE NOTE BELOW)

AS CAN BE SEEN,THE BOUQUET RATE OF INR 43.2 IS GREATER THAN 66% OF SUM OF ALL ALA CARTE CHANNEL RATES WHICH IS INR 64.88

ALSO, AVERAGE BOUQUET RATE OF INR 2.88 IS GREATER THAN 33% OF EACH ALA CARTE CHANNEL RATE PRESENT IN THIS BOUQUET

THE ABOVE PRINCIPLE EXISTS FOR ALL THE BOUQUETS CREATED BY MEDIA PRO (PFA2 ATTACHED HEREWITH)


NOTE :-

1. A LA CARTE IMPLIES STAND ALONE RATES OF EACH TV CHANNEL AS GIVEN BY THAT BROADCASTER (PAGE 15-16 OF PFA2)

2. AVERAGE BOUQUET PAY CHANNEL RATE = BOUQUET RATE / NUMBER OF PAY CHANNELS IN THAT BOUQUET

 

[aneeta]

PLEASE FIND SAMPLE DATA (PAGE 15, 16, 17 OF PFA 2) AS BELOW :-

TV CHANNEL

A-LA-CARTE / STAND ALONE RATE PER SUBSCRIBER PER MONTH INR)

STAR MOVIES

7.42

STAR WORLD


2.05

FOX CRIME


6.51

NDTV INDIA


3.37

NDTV PROFIT


2.7

NDTV 24x7


3.82

NDTV GOOD TIMES


4.04

ZEE CAFÉ


3.6

ZEE STUDIO


3.15

ZEE BUSINESS


2.16

CARTOON NETWORK

5.62

MOVIES OK


7.14

POGO


5.62

HBO


7.01

CNN


0.67

TOTAL


64.88