Average Market

filipsneyers
filipsneyers
edited November 19 in Modeling

Hello,

I have a list of 200 companies with all their P&L numbers in a module. Now I want to sum all the data of the 6 biggest of these companies into one artificial company to have a market average.

How do I sum the 6 companies that I chose into a company called "benchmark Market" and then compare any single company to that "created benchmark company"?

The choice of the companies has to be changeable, preferably with a boolean on the list "companies"

Thank you!

Kind regards,

Filip

Answers

  • billdowling
    billdowling

    Hi Filip, there are a few ways you could do this. You could use RANK to test for the top 6 companies, but if you want the companies to be changeable, I would sort the output and use a Boolean to select the companies you want to include in the benchmark.

    You can then store the benchmark average in a separate module and reference this when comparing a company to benchmark.

    Below are some screenshots of a simple example. You could also dimension this over time to track the benchmark history.

    Bill

    image.png
    image.png
    image.png

  • derektslim
    derektslim

    Hello Filip

    That is a fun and interesting question you have. As Bill suggested, there are a few ways to achieve this and my approach is similar.

    I would set up the module as follows:

    • Check for Comparison? : Boolean, Summary None - Allows user to choose Company to compare
    • Revenue : Number, Summary None - I assume you will have this formulated somehow
    • Comparison Revenue : Number, Summary None - Gets revenue if selected for comparison
      IF Check for Comparison? THEN Revenue ELSE 0
    • Comparison Ranking : Number, Summary Max - Ranks comparison revenue accordingly
      RANK(Comparison Revenue)
    • Top # Revenue : Number, Summary Sum - Gets the top # revenue according to input (user input, see second module), and adds in place a check in case there is lesser companies ticked off
      IF 'MOD2 Selector'.Top # Ranking for Comparison < Comparison Ranking[SELECT: 'L1 Company List'.Total] THEN IF Comparison Ranking > 'MOD2 Selector'.Top # Ranking for Comparison THEN 0 ELSE Comparison Revenue ELSE Comparison Revenue
    • Top # Revenue Average : Number, Summary None (No Dimension)
      IF 'MOD2 Selector'.Top # Ranking for Comparison < Comparison Ranking[SELECT: 'L1 Company List'.Total] THEN Top # Revenue[SELECT: 'L1 Company List'.Total] / 'MOD2 Selector'.Top # Ranking for Comparison ELSE Top # Revenue[SELECT: 'L1 Company List'.Total] / Comparison Ranking[SELECT: 'L1 Company List'.Total]
    • Variance from Top # Revenue Average : Number, Summary None
      IF Check for Comparison? THEN Comparison Revenue - Top # Revenue Average ELSE 0
    image.png
    image.png

    A module that allows user to input the top number to be used for comparison

    image.png

    Hope this helps!

    Derek Lim

  • filipsneyers
    filipsneyers

    Thanks for all the answers, but the next problem is already showing up:

    I now have 2 modules with multiple line items (that are KPI's that are calculated upon all that P&L data per company).

    Module 1 "Cal01 KPI's" with the dimension "company" that already existed, so I have all the KPI's per company in that module. I made a Line item subset of all these KPI's - LIS Finances

    Module 2 "Cal01b KPI's - Benchmark Company" without the dimension "company" , but which contains all the KPI's of the Benchmark company. I also made a Line Item subset of the same KPI's as the module above which is called LIS Finances BM

    To link these 2 line item subsets(because the KPI's are the same) I made a mapping table (sys TEST) between LIS Finances and LIS Finances BM.

    Problem is that I cannot make a module to compare any company of module 1 to the benchmark company of module 2. The problem is that the first module has the "company" dimension" and the 2nd module has NOT.

    Can anyone help me how to have the same KPI's in one module with no subsidiairy views so I can compare any company with the benchmark company and show it on a graph on an app page?

    I will share a view of module 1 and a view of module 2.

    I already made some attempts in the images "solution that does not work". Problem is that the company is ok but that there are no values for the benchmark company (always 0,0%)

    Thanks for the help.

    axa module 1.JPG
    Benchmark company module 2.JPG
    solution that does not work.JPG
    solution that does not work-data.JPG

Categories