The project assumed the cooperation of two entities. Entity A was to be responsible for providing external licensors’ content for entity B, which acted as a cable service provider.
Enterprises settled on the principles of profit share . The financial analysis had to enable negotiations between the companies, taking into account all available models of settlements with licensors. The content was divided into two basic groups, which differed in the model of sharing and settlements with licensors.
The tool for analyzing financial forecasts had to meet the requirements in the form of taking into account separate product lines that distinguish models of settlements with licensors in the form of:
- revenue share settlement for the purchase,
- payments for end user access to content measured in megabytes,
The analysis was to have the functionality of “scheduling” the provision of the offer on set-top boxes.
The financial forecasts required them to operate on “product sheets”, taking into account the possibility of determining:
- dedicated price,
- payment model,
- the settlement rate between the cooperation parties.
The analysis of the sensitivity of the obtained margin to changes in exchange rates was also taken into account, as well as a complete financial analysis of the obtained revenues with a breakdown into content groups, including both sources.
Parameters of the first product line
According to the requirements, in one section the parameters of the particular content of the content are defined, taking into account:
- licensor’s cost,
- settlement model,
- assumed conversions at particular stages of development,
The schedule for the provision of services in the set-top box network is included in a different section of the model, which also allows for financial modeling.
Parameters of the second product line
According to the division, the second section contains the parameters of the content group focused on the use of access to movies, measured in gigabytes.
The business model calculates the possible margin and determines the profitability thresholds. Additionally, it takes into account changes in the margin for the use of services in the form of price thresholds.
Profitability analysis due to fluctuations in EUR exchange rates
Payments for services are made in Polish zlotys, however licensors charge fees in foreign currencies. Currency rates are volatile due to a number of economic and geopolitical factors.
In such a situation, it is necessary to be able to carry out exchange rate sensitivity analyzes in order to determine the critical points for prices and the entire business venture.
In this sheet you can simulate the shape of the course. In addition, you can see changes in the field of:
- revenue projection,
- licensor’s costs,
- operator costs.
Profitability analysis due to changes in USD exchange rates
The analysis covers a particular segment of revenues generated by an account paid in a specific currency. The screenshot shows the possibility of forecasting the US dollar exchange rate.
Excel uses fairly simple VBA solutions and conditional formatting for the convenience and ease of use of Excel.
Financial analysis of the first content line for entity A
Model sheet containing financial analysis of group one services. The view containing the forecasted financial results on clear charts was included in the following approach:
The annual financial forecasts are shown in the table.
All data is calculated dynamically, which means that any changes to products are recalculated in the background and changes to forecasts are visible immediately.