1 2 Previous Next 27 Replies Latest reply on Jan 12, 2016 12:25 AM by Brian Calvanese

    Corporate P&L Allocations Using Tableau

    Sam Foshay

      I'm a financial analyst for a firm that is currently trying to migrate all of it's reporting and metrics for the P&L onto Tableau. As I'm new to Tableau I'm unaware of any advanced functionality regarding table calculations. I'm running into a specific problem around allocating corporate costs in my core data set, and was wondering if Tableau has a feature/capability to automatically "run" these allocations for me so i don't have to do this in my source data. Here is an overview of the situation:

       

      • Data set: Raw general ledger extract from accounting systems and Raw forecasting data by GL Account
      • Data environment: Excel/ Access file with basic mapping and attribution features
      • Company Structure: multiple product specific cost centers with shared "corporate" cost centers that are allocated to the P&L's of the businesses
      • Allocation methodology: % of revenue type (services cost centers allocated on % of services revenue, Product cost centers allocated on % of product revenue etc.)

       

      Problem statement: Can I have Tableau automatically calculate my % of revenue allocations for my corporate cost centers?

       

      I'd prefer to do this in tableau vs. my Excel/Access file as this will avoid that file ballooning to a large state, and it will allow me to apply the same filters that I'm applying to my non allocated cost.

       

      Thanks!
      SF

        • 1. Re: Corporate P&L Allocations Using Tableau
          Brian Calvanese

          Hi Sam -

          Table calc's might help... can you mock up some data for us to play with?

          1 of 1 people found this helpful
          • 2. Re: Corporate P&L Allocations Using Tableau
            Sam Foshay

            Thanks for the quick response Brian- Attached is a very pared down extract of what i'm hoping to achieve.

             

            What I'm looking for is to see a Cross tab with the product 1 P&L, with the corporate costs allocated as a % of Revenue.

             

            As a second layer of complexity, if we could allocate sales, Marketing, R&D and G&A along total revenue while allocating support revenue as a % of services revenue.

             

            Thanks,
            SF

            • 3. Re: Corporate P&L Allocations Using Tableau
              Brian Calvanese

              Hi Sam - thanks for the data!

               

              I mocked up the Product P&L's in the .xls... can you tell me if I have the math right?  I can think of a couple of ways to do this in Tableau... just want to make sure I have the right end goal in mind.

               

               

              1 of 1 people found this helpful
              • 4. Re: Corporate P&L Allocations Using Tableau
                Sam Foshay

                Brian - this is correct. Let me know when you've finished the mockup in Tableau.

                • 5. Re: Corporate P&L Allocations Using Tableau
                  Sam Foshay

                  Brian - I'm working on a deadline, do you have any update on whether this is feasible? Otherwise I'll have to do this offline in my data set.

                   

                  I appreciate the help!

                  • 6. Re: Corporate P&L Allocations Using Tableau
                    Brian Calvanese

                    Yes, I think so... just trying to straighten out an issue with the grand total to show a profit view... when's your deadline?

                    • 7. Re: Corporate P&L Allocations Using Tableau
                      Sam Foshay

                      Hey brian - I'd like to have the feasibility decision made in the next 36 hours. thanks!

                      • 8. Re: Corporate P&L Allocations Using Tableau
                        Brian Calvanese

                        There was a small error in the .xls I had attached above - caught this as I built the view in Tableau:  I had the Total Rev Alloc % hooked into Product Revenue... I re-pointed this to Total Revenue for this update.

                         

                        Instead of Table Calc's, this solution requires heavy use of nested LOD's... I didn't take a lot of time to format the view, the workbook is attached and a rough snapshot is shown below:

                         

                         

                        As you will see, this ties to the corrected .xls (also attached and shown below).

                         

                         

                        This was a very interesting exercise... the notion of doing a fully allocated P&L entirely within Tableau from a very raw and un-manipulated data source has a ton of potential for FP&A folks... I don't see how this would have been possible without LOD's (perhaps with Table Calc's, padding, scaffolding, etc... but, even then I'm not sure it would have worked because of the need for horizontal and vertical Grand Totals).

                         

                        I'll try to post more of a walk through for some of the calc's and techniques a bit later when I have more free time.

                         

                        Hope this helps!!

                        Brian

                        1 of 1 people found this helpful
                        • 9. Re: Corporate P&L Allocations Using Tableau
                          Sam Foshay

                          Brian - this is great but i'm having a hard time ascertaining the order of operations necessary to reverse engineer this process, and how you achieved it. Can you please provide a detailed step by step description of this?

                           

                          I agree completely that this has a lot of potential for FP&A users, which is a userset that has a hard time adopting to the Tableau view of raw data. Can you clarify what LOD means?

                           

                          Thanks!

                          • 10. Re: Corporate P&L Allocations Using Tableau
                            Brian Calvanese

                            Hi Sam - glad you found this helpful!!

                             

                            LOD's are "Level of Detail Calculations"... these are very powerful calculations that let you do math at levels of granularity above or below the level of granularity defined by your view (i.e. the level of granularity defined by the dimensions you've dragged into your worksheet).  This is relatively new functionality to Tableau (introduced about a year ago).

                             

                            For example, let's say you want to compare the average sales for a salesperson to an average of their peers.  Before LOD's, you would have to do some Tableau gymnastics to make this happen.  Now, you can bring salesperson into the view, see their averages, and then use LOD's to bring in an average that 'excludes' salesperson from the level of granularity for comparison purposes.

                             

                            If you're just starting on your Tableau journey, you may want to watch a few youtube video's, and then read a few blog's, and then take a week off from work to just sit and play so you can start to climb the learning curve 

                             

                            Bora Beran's blog post would be a really good place to start:

                            What’s new in Tableau 9.0? Part 2 – Level of Detail Expressions « Bora Beran

                             

                            I'll try to describe some of the main techniques and calc's used in the workbook in separate posts to follow.

                            • 11. Re: Corporate P&L Allocations Using Tableau
                              Brian Calvanese

                              Part 1:  Creating the Allocation Framework

                               

                              For this exercise, I found it very important to keep the end goal in mind...

                               

                              ... we need to create something that looks like this:

                               

                               

                              ... from something that looks like this:

                               

                               

                              I assumed we wanted to do everything in Tableau without reshaping the data.  I used a couple of interim views to help me get there in stages. The rest of this post deals with the first stage of setting up the Allocation Framework.

                               

                              I first noticed that "Level" distinguished between "Corporate", "Product 1", and "Product 2"... so, from this, I created a field that gave me a roll up:

                               

                              [z Level Rollup]

                              split([Level]," - ",1)

                               

                              The "split" function gives us the nth member of a text field separated by a delimiter.  In this case, we want the first term that occurs before the dash.  This gives us a field that has three values:  "Corporate", "Product 1", and "Product 2".

                               

                              The next thing I noticed was that "Level" also has the functions we want to both allocate from and allocate to.  To get us started, I created the following calculated field, incorporating some info from the "HC/Non-HC" and "Account Number" fields:

                               

                              [z Account]

                              if [HC/Non-HC]="Revenue" and contains([Account Number],"services") then "Revenue - Services"

                              elseif [HC/Non-HC]="Revenue" then "Revenue - Product"

                              else split([Level]," - ",2) end

                               

                              Next, I flipped the sign on the expense items to keep everything straight in my head (I think this is optional, but my twisted mind thought this was a good idea at the time):

                               

                              [Values]

                              if contains(attr([z Account]),"Revenue") then 1 else -1 end * sum([Amount])

                               

                              Putting all of this together gave me the following allocation framework:

                               

                               

                              So far, all we've done is create the initial framework.  We have two revenue categories we can use to drive allocations and we can distinguish between the buckets we'd like to allocate from (i.e. "Corporate") vs. the two categories we'd like to allocate to (i.e. "Product 1" and "Product 2").

                               

                              Next, I'll walk through the LOD calc's required to drive the allocations.

                               

                              To be continued...

                              • 12. Re: Corporate P&L Allocations Using Tableau
                                Brian Calvanese

                                Part 2:  Calculating the Allocations

                                 

                                In order to calculate the allocations, we need to know the following for each "z Account" described above:

                                 

                                - The amounts to be allocated - I'm going to call this the "z Natural Amount".

                                - Whether the natural amount will be allocated across Total Revenue ("z Amt to Allocate on Total Revenue") or Service Revenue ("z Amt to Allocate on Service Revenue").

                                - The Allocation %'s applicable to each Product ("z Allocation % - Total Revenue" and "z Allocation % - Services Revenue").

                                - The Allocation Amounts ("z Allocation on Total Revenue" and "z Allocation on Services Revenue") are then a function of the amounts to be allocated and the allocation %'s.

                                 

                                Let's examine these four sets of calc's in more detail...

                                 

                                [z Natural Amount]

                                [Amount]

                                 

                                "z Natural Amount" is nothing more than "Amount" in disguise.  I think this survived because I tried to do something at one point and abandoned that idea.

                                 

                                Here comes our first LOD calc:

                                 

                                [z Amt to Allocate on Total Revenue]*

                                sum({fixed [z Account] :

                                    sum({exclude [z Level Rollup]:sum(iif([z Level Rollup]="Corporate" and [z Account]<>"Support",[Amount],0))})

                                })

                                 

                                I think this one is best explained in pieces.

                                 

                                First, at the row level (this is important), I used the iif() statement to identify Corporate expenses that weren't applicable to Support (since Support is allocated across Services revenue).

                                 

                                Next, wrapping this with {exclude [z Level Rollup]: sum(...) } tells Tableau to ignore what are the columns in the viz so that the Corporate amount is carried across the view. 

                                 

                                Finally, I wrapped this with sum({fixed [z Account]: sum(...)} to preserve the calc's at the level of [z Account] so I can roll things up later.  The services version of this calc works exactly the same way... so, I won't walk through that one again.

                                 

                                Honestly, I don't know how many people would be able to follow how this nested LOD calc works by reading my awful plain English description... this is something you need to experience and suffer through on your own... there's no shame in taking a trial and error approach to build these things out!!

                                 

                                *Full disclosure... as I was writing this up, I noticed there was an extra redundant layer to the calc you'll find in the workbook... so, the calc here is simpler than the one in the posted workbook.

                                 

                                Next, we need to calc the Alloc %'s:

                                 

                                [z Allocation % - Total Revenue]

                                sum({fixed [z Level Rollup] : sum(iif([z Level Rollup Summary]="Revenue",[Amount],0))})

                                /

                                sum({sum(iif([z Level Rollup Summary]="Revenue",[Amount],0))})

                                 

                                The numerator uses an LOD to find the amount of Revenue applicable to each product (i.e. fixed at the level of [z Level Rollup]).  The denominator uses a similar LOD to make "Total Revenue" available to the calc's for each product.  You can think of this as the equivalent of using the dollar sign in Excel to fill formulas across a spreadsheet, locking in the total on the bottom while letting the top float.

                                 

                                Again, the services version is the same, but limited to services revenue.

                                 

                                Finally, bringing these fields together arrives at the allocated amounts for each product:

                                 

                                [z Allocation on Total Revenue]**

                                [z Amt to Allocate on Total Revenue]*[z Allocation % - Total Revenue]

                                 

                                [z Allocation on Services Revenue]

                                [z Amt to Allocate on Services Revenue]*[z Allocation % - Services Revenue]

                                 

                                These calc's apply the Alloc %'s to the amounts that need to be allocated to arrive at the allocations.

                                 

                                **More full disclosure... this is a simpler version of the calc in the workbook posted above... the way the initial version evolved, there was some leftover logic that wasn't needed.

                                 

                                Putting all of this together into a single view gives you a way to validate all of the numbers against the Excel sheet:

                                 

                                 

                                Using LOD calc's in this way will give us the ability to eventually hide the "Corporate" column... more on this later!!

                                • 13. Re: Corporate P&L Allocations Using Tableau
                                  Brian Calvanese

                                  Part 3:  Finishing the View

                                   

                                  Now that all of the allocations are worked out, all that's left is to add everything up.

                                   

                                  The first step here is to create another view of "z Account" that collapses product and service revenue into a single bucket called "Revenue":

                                   

                                  [z Account - Display]

                                  if contains([z Account],"Revenue") then "Revenue" else [z Account] end

                                   

                                  Next, we need a field that adds the natural amounts to the allocated amounts:

                                   

                                  [z total w Allocation]

                                  sum([z Natural Amount])+[z Allocation on Total Revenue]+[z Allocation on Services Revenue]

                                   

                                  Pulling this together and filtering out the "Corporate" bucket/column gives the following:

                                   

                                   

                                  This is pretty close!!  Revenue is now rolled up and the expense line items are now fully allocated.  However, the grand total is adding revenue to expense instead of showing a view of profit.

                                   

                                  While I would probably prefer to show Revenue, Expenses, and Profit in three separate tables - there's no way I know of to alias the "Grand Total" to say "Profit" - there is a way to force the grand total to show a difference if you'd like to see everything in a single cross-tab and don't care about the label:

                                   

                                  [z total w Allocation (profit view)]

                                  if max([z Account])=min([z Account]) then [z total w Allocation]

                                  else sum(iif([z Level Rollup Summary]="Revenue",[z Natural Amount],-[z Natural Amount])) - [z Allocation on Total Revenue] - [z Allocation on Services Revenue]

                                  end

                                   

                                  The test on the first line looks at each aggregated row in the table and tests to see if it's made up of a single member.  By definition, this will always be the case for all but the Grand Total line (which still has all of the different account values sitting behind it).  As a result, each row of the table will show the original "z total w Allocation"... but the Grand Total will show the difference between revenue and expense (i.e. profit).

                                   

                                  Putting this in the table in place of the original calc arrives at the final result:

                                   

                                   

                                  Creating a hierarchy out of "z Account - Display" and "z Account" let's you drill into and out of Revenue to see the split between Product and Services:

                                   

                                   

                                   

                                   

                                  Hope the step-by-step helps explain more about how the view was developed… enjoy!!

                                   

                                  Brian

                                  1 of 1 people found this helpful
                                  • 14. Re: Corporate P&L Allocations Using Tableau
                                    Sam Foshay

                                    Hey Brian - I really appreciate the detailed response. I'm carved out a significant amount of time to work on this today and will get back to you with any questions. My company has 9 products and 4-5 different rev classes to allocate based on, so the level of detail is going to be a bit more complicated than what I provided. That being said, I think this will achieve what we want!

                                    1 2 Previous Next