2 Replies Latest reply on Apr 24, 2018 10:02 AM by Phil Whelan

    Gross Margin and other percentage calculations

    Phil Whelan

      Hi,

       

      I have created a workbook on Tableau which  contains various financial tables - such as P&L, Cash Flow, expenses, revenue breakdown, aged debtors, and so on. The workbook is fed by four different data sets (Grp CF Full, Grp IS Full, INT Func Full Values and Int Rev Mark).

       

      I am however struggling to calculate percentages, i.e. those relating to percentage of sales and working capital metrics.

       

      I have created several calculated fields on Tableau (such as Actuals vs. Budget or Forecast), but I am struggling with creating calculations for the above fields - since they all depend on selecting items within dimension hierarchies, rather than measures. I have browsed through several similar questions on this forum, but can't find an appropriate answer.

       

      I have attached a dummy packaged workbook here in Tableau v10.5 showing my dataset (can't attach the real workbook due to data confidentiality).

       

      Since this is the first time I have asked a query on this forum, please do advise if the workbook has not attached correctly.

       

      Essentially I have three main calculation types I'd like to do:

       

      1.    "Act IS" tab -   I need to create rows for various items as a percentage of sales, so just need to divide each metric by sales - namely:

            -     Gross Margin    (Gross Profit/ Sales)

            -     Direct Labour as % of sales

            -     Factory OHs as % of sales

            -     Materials as % of sales

            -     Other COS as % of sales

            -     Warranties as % of sales

      2.     "Act WC metrics" tab -   I need to create rows for the following working capital metrics;

            -     DSO                (calculation:          Ave Debtors 12 mths/ Rolling Gr Sales 12 mths * 365)

            -     DPO                (calculation:          Ave Creditors 12 mths/ Rolling COS 12 mths * 365)

            -     Stock Turns     (calculation:         Rolling COS 12 mths/ Ave Inventory 12 mths)

      3.    Cash conversion - I need to divide DHOC (in the "Grp CF Full" data set) by DHOP (in the "Grp IS Full" data set). I appreciate this may be tricky as the metrics are in different data sets.

       

      Many thanks in advance for your help with this.