10 Replies Latest reply on Sep 16, 2015 11:25 PM by Junjappa M

    Cash flow simulation on Tableau

    Fabio Annovazzi

      Hi,

       

      I am new to tableau.

       

      I am trying to understand if it is possible/makes sense to use Tableau for financial/cash flow modeling. I have profit and loss data for the past 5 years and forecast for the next two. I have the balance sheet and cash flow data for the past. I need to build the balance sheet simulation for the next two years. Do I need to do it in Excel or is there a way to do it in Tableau. I thought about using some calculation that says, for instance, that future accounts receivable must be certain % of future sales. But then this will be a different data element vs the "account receivable' data element while in excel the would be just the "following cell" of a spreadsheet.

       

      thanks

       

      fabio

        • 1. Re: Cash flow simulation on Tableau

          Fabio,

           

          This is an awesome question and I think that this would be a really useful analysis.  Would you mind posting the data you have (or at least a sample) so we can understand the current structure of the data?

           

          Thanks,

          Brandon

          • 2. Re: Cash flow simulation on Tableau
            Ben Jones

            Hi Fabio - in your Accounts Receivable example, you could create a calculated field that merges historical (actual) AR figures with future (projected) AR based on an existing "Future Sales" forecast like this:

             

            IF ISNULL(Accounts Receivable) THEN (Future Sales)*(Pct Multiplier for AR) ELSE (Accounts Receivable) END

             

            This calculated field (call it "AR_display" or something) could be included in the table and would show both numbers in the same row of the balance sheet.

             

            You could also create another calcualted field called "Type" that indicates whether the figure is actual or forecasted as follows:

             

            IF ISNULL(Accounts Receivable) THEN "Forecast" ELSE "Actual" END

             

            You could use this field to change the color of the table to make it easy to spot what type of figure it is.

             

            Echoing Brandon - this is an intriguing use of Tableau.  What would be even better is if someone in finance out there used Tableau to visualize the typical income statement / balance sheet / cash flow tables in graphs instead of tables.

             

            I recently posted a waterfall chart for Facebook's income statement that attempted to do this: http://dataremixed.com/2012/02/a-facebook-waterfall/

             

            Thanks,

            Ben

            • 3. Re: Cash flow simulation on Tableau
              Fabio Annovazzi

              Hi Ben, Hi Brandbon,

               

              I read your blog yesterday. Waterfall chart work great in Tableau.

               

              I think it makes sense to use Tableau rather than Excel because financial simulation is about modeling (for instance "putting together" one business with another, or splitting them) and unless you know from the start (which you rarely do) how you want to slice and dice your data, an Excel model (which is inherently "mono-dimensional") risks becoming very complex very quickly while you try to modify it to accommodate the different scenarios that pop up. I remember this as a nightmare.

               

              So this time I decide to build up an excel "table" with all the data of the different companies, on record on top of another, and then see if I could build up the model in Tableau.

               

              I am attaching the structure of the Excel. All numbers are random and the names of the divisions are also invented.

               

              On the right side of the sheet you have the PL and BS entries. (of course BS entries do not balance since they are random). On the right of the sheet I put a formula that "creates" different "measures (revenues, costs, assets,...) that I need in Tableau.

               

              For instance in Tableau I need a table that has the years on the columns, and revenues, direct costs, operating margin, indirect costs, Ebitda, eccetera on the rows. Operating margin must be revenues - direct costs. I did not know how to do that without a separate measures.

               

              And then I have the issue of how to project all this into the future...

               

              Thanks again Ben for the answer, will try it out!

               

              all the best

               

              fabio

              • 4. Re: Cash flow simulation on Tableau
                Fabio Annovazzi

                Ben,

                 

                I tried to apply your technique, and it looks good.

                 

                One question - when you do modeling of a company you are trying to sell or buy you often want to "slice and dice" your model by business, division, or whatever. You might want to see which of the divisions generates (or requires) more cash, and so forth.

                 

                Doing this in Excel is not easy, and quickly leads to a complex model. It should work much better or Tableau

                 

                Your idea assumed "one" Accounts Payable tied via some parameter to "one" stream of revenues. Would it be possible/make sense build the Balance Sheet in Excel as a sum of many Accounts Payable elements (each calculated as the product of the revenues of that division X the specific parameter of that division)?

                 

                Final question. In modeling it is very common to assume a "different" future. For instance to assume that the ratio between revenues and accounts receivable goes up because customers will pay you before eccetera. Is it possible to tie your function to different constants tied to time?

                 

                thanks again

                 

                fabio

                • 5. Re: Cash flow simulation on Tableau
                  Ben Jones

                  Hi Fabio,

                   

                  I'm pretty sure both of your ideas are possible - you can sum up multiple revenue entries for your AR estimation, and you can create different scenarios for the AR calculation using parameters and calculated fields.  The attached example uses a simple slider to allow you to change the AR multiplier for all years, and you could even take it a step further and have different multipliers for different years, etc.

                   

                  http://public.tableausoftware.com/views/AREstimator/Dashboard1?:embed=y

                   

                  http://public.tableausoftware.com/static/images/AR/AREstimator/Dashboard1/1.png

                  Hope this helps!

                  Ben

                  • 6. Re: Cash flow simulation on Tableau
                    Fabio Annovazzi

                    Wow, amazing!

                     

                    Thanks so much for this

                     

                    Fabio

                    • 7. Re: Cash flow simulation on Tableau
                      John Rodat

                      Good question and nice responses which I'm going to use to think about something I'm working on. My problem has to do with cash available to a local government. 

                       

                      I've already done a basic dashboard with weekly snapshots of cash balances back several years. It provides some interactivity, such as a parameter based "danger zone" and the quick filter to choose past years for comparisons with current.

                       

                      The past couple of years, the cash generated organically from operations and taxes has been supplemented by intra-fiscal year borrowing to smooth out cash flow. That's important to assess current position, but can also misleading in thinking about even near term futures.

                       

                      The ability to do some projections will be very useful. So thanks!

                       

                      Will post mine when it's done. I've just returned from four days of Tableau training so give me another week or two.

                       

                      Best regards

                       

                      John

                      • 8. Re: Cash flow simulation on Tableau
                        Ben Jones

                        Fabio: No problem - happy to help.

                        John: Interesting, & have fun putting your training to use!

                         

                        Ben

                        • 9. Re: Cash flow simulation on Tableau
                          taylor.johnson

                          Can someone provide the underlying data or the twbx for this?  I have similar data to that display above but I am having a hard time creating a graph like this.

                          • 10. Re: Cash flow simulation on Tableau
                            Junjappa M

                            @ben jones this is a very interesting concept to visualize could either of you share more details or step by step guide for other tableau users, thank you guys for such an amazing post