12 Replies Latest reply on Dec 23, 2016 1:15 AM by Simon Runc

    How to make financial projection models

    adam.cotton

      I have annual revenues for retail sales (about 5k annually) in box-and-whisker charts. Very nice.

      How can I create a model where I can punch in some figures for 2017, 2018 etc, and see how it affects my long-term overall revenue projections?

      I would like to be able to set the projected median, upper hinge, lower hinge of a projected year.

       

      Any tips and links to tutorials on doing projections appreciated. Cheers!

        • 1. Re: How to make financial projection models
          Tom W

          You could look at the forecasting functionality - http://onlinehelp.tableau.com/current/pro/desktop/en-us/help.html#forecasting.html

           

          You could potentially do some modeling with R, Python or even a SQL source by entering factors into parameters and pushing those back into the datasource as well. But I will say this - Tableau is not a data modeling tool, it's a data viz tool so your options within the tool itself are pretty much limited to what you can achieve with forecasting, calculated fields + parameters or integrations with other tools like R, Python.

          • 2. Re: How to make financial projection models
            adam.cotton

            Thanks much for the reply. That does sort of answer my question -- there isn't really a way to enter data for models, so I'm back to excel for things like this. I'm using a static dump from SQL.

             

            I get a lot of value from doing forecasting of different 'what if' scenarios in spreadsheets, and the idea is that it would be very useful if this was visual: going straight from knob to graph, in the way that one does with filters in Tableau.

             

            An example would be analysis of online advertising:

             

            Cost per click --> click-through rate --> conversion rate --> conversion value --> return on investment.

            Each variable leverages the others, and it would be nice to be able to go from existing, historical data, to tweaking the expected forecast data, and see the effects. I'm doing this now in Excel, but would be nicer in Tableau. Just sayin.

             

            --

            UPDATE: I can see that SAS Visual Analytics does exactly what I want. Not throwing any shade at Tableau, which is fantastic at what it does. But they might want to develop that kind of functionality.
            SAS Visual Analytics | SAS

            • 3. Re: How to make financial projection models
              Tom W

              Those SAS Visual Analytics look very similar to the Tableau Forecast option.

               

              Like I said before though, you can use parameters and calculated fields to do what if analysis like this;

              Workbook: Sales-Forecast[1]

               

              The topic has been covered a lot actually, so there's a bunch of good reading material:

              https://community.tableau.com/search.jspa?q=what+if

              Tableau 201: How to Make a What-If Analysis Using Parameters

              • 4. Re: How to make financial projection models
                Simon Runc

                hi Adam,

                 

                While I'm not the person to ask regarding the intricacies of this blog post...Tableau can be extended (as Tom has said) using R (and very soon Python). Think you might find this useful/interesting

                 

                Multivariate Forecasting in Tableau with R | Bora Beran

                 

                but yes, at it's heart, Tableau is a data viz tool and we (as in my company) tend to run such forecasting/modelling exercises outside Tableau (generally Python) and bring the results into Tableau for analysis (the 2 together can be very powerful)

                • 5. Re: How to make financial projection models
                  adam.cotton

                  Thanks so much, &Tom W. This is exactly what I need. It seems the function that is baked into SAS VA is doable in Tableau with a parameter and a calculated field. I am doing simple stuff, and don't have time to get into R, although the model in that link is beautiful.

                   

                  If anyone wants to weigh in, I'm trying to figure out how to do that calculation so that I can vary just the upper segment of my sales forecast, in the sense of: higher dollar level sales for the top 20% dollar value sales: all else equal.

                  • 6. Re: How to make financial projection models
                    Simon Runc

                    hi Adam,

                     

                    So to take a proper look at this I think we'd need to see some example data, and the expected behaviour (eg. what you want to be able to alter...etc.)

                     

                    Enjoyed the SAS VA video...although the UI does look familiar...I wonder where I've come across it before?

                     

                    Going from that video and the post Tom directed you to (Tableau 201: How to Make a What-If Analysis Using Parameters ). We could adapt the formula there to only affect a part of the sales...

                     

                    So the formula [Sales] * (1+[What-If]/100) could be changed to

                     

                    IF [Souce] = 'Paid Ads' THEN  [Sales] * (1+[What-If]/100) ELSE [Sales] END

                     

                    and we could even parameterise the 'Paid Ads' so you could easily apply the uplift to any source (or have a few parameters and be able to control each source)...

                     

                    If you wanted something a little more complicated....such as picking up the Top 20% and applying a What-If to that, it might be a little more complicated, and the solution would depend on many things (shape of data, are percentiles hard coded into data, is there a "Nieve"/"Neutral" forecast baked into the data...etc.).

                     

                    Hopefully, that helps, but if you want to explore a more detailed solution, I suggest starting a new thread (with some example data/workbook)...happy for you to ping me and I can take a look.

                    • 7. Re: How to make financial projection models
                      adam.cotton

                      Simon thanks;

                      My data looks something like this (just example);

                      Sales (N= 5000) have a median median value of $50. In a box-whisker chart, lower 'hinge' $25, top hinge $75. Let's say the top 33% of sales by dollar value average $75.

                      I want to model that shows the effect of getting the big spenders to spend more; lower levels no change. (80-20 rule, anyone?)

                      I guess I could create buckets(?) to calculate percentile groups. I vaguely remember doing this before.

                       

                      Using Tom's calculation above and your thought on IF statements, I can already get some useful projections using a threshold, for example running the multiplier parameter on values over $75:

                      IF [Subtotal] >75

                      THEN [Subtotal]*(1+[Whatif]/100)

                      ELSE [Subtotal]

                      END

                      Not sure if that's right.

                      All this is very useful. Grateful for the input. I think I can make my boss see the light.

                      • 8. Re: How to make financial projection models
                        Simon Runc

                        hi Adam,

                         

                        So yes that's the kind of thing...in the attached I've used an LoD to create a Dimension which puts Customers (using Superstore) in one of 3 Tercile Buckets...

                         

                        The formula is

                        [Tercile Dim - LoD]

                        IF {FIXED [Customer Name]: SUM([Sales])} > {FIXED: PERCENTILE({FIXED [Customer Name]: SUM([Sales])}, 0.667)}

                            THEN "H"

                        ELSEIF {FIXED [Customer Name]: SUM([Sales])} > {FIXED: PERCENTILE({FIXED [Customer Name]: SUM([Sales])}, 0.333)}

                            THEN "M"

                        ELSE

                            "L"

                        END

                         

                        I have feeling this could be simplified, but when a formula is doing what we want I tend to not ask too many questions!!...I'm also quite excited as I've not been able to get "off canvas" (i.e. not requiring customer in the VizLoD to allow the Table version of Percentile to group customer) percentile using LoDs to work before (I felt there was a way, but never quite nailed it)....so glad I attempted this Question!!

                         

                        The "What Tercile Does" tab shows the effect of this formula

                        Putting our customers into 3 equally sized buckets, based on their total spend

                         

                        I can then use this dimension to control the What-If analysis

                         

                        I created a parameter (from 0% - 100% in increments of 5%)

                         

                        and used the formula from the post (btw I've coded them a between 0-1, and formatted as %age, so don't need the /100)

                        [What If -  Sales]

                        IF [Tercile Dim - LoD] = 'H'

                        THEN [Sales]*(1+[%age Uplift on H Tercile])

                        ELSE [Sales]

                        END

                         

                        I've then Created the "Final" tab so you can see what it's all doing. Of course, you can adapt this and parameterise any part of any of the formulas (eg. if you wanted quartiles...you could parameterise the Tercile formula and could control this too)

                         

                        Hope that helps...and I've learned a little bit here too

                         

                        I think I can make my boss see the light...ah the holy grail for any analyst!!

                        3 of 3 people found this helpful
                        • 9. Re: How to make financial projection models
                          adam.cotton

                          Wow, man, that's simply awesome. I plugged in your formulas and boom, it works and I'm already seeing scary $^%@# .

                           

                          Just amazing. That would have taken me a long time to figure out. Glad you moved forward too!

                           

                          Still figuring out many things -- how to make the what-if formula only affect forecasts, not historical? Where I put the "and [date]>2016"?

                           

                          Yes, and I can literally see the light dawn on my boss' face when I show him what's going on. This stuff is really persuasive.

                          • 10. Re: How to make financial projection models
                            Yuriy Fal

                            Simon, you've actually just built

                            a simpler-to-read (all FIXED) version

                            of RFM-style calculation. Thank you!

                             

                            Yours,

                            Yuri

                            • 11. Re: How to make financial projection models
                              Simon Runc

                              Thanks Yuri...I would say 40% deliberate & 60% luck!! (I found this blog and wondered what would happen if I started switching out the row level [Sales] for LODs). But yes this is going to be very very handy...giving us some sort of ranking/ordering of "things", without having the "thing" in the vizLoD (or "off canvas" as I like to call it).

                               

                              Hi Adam...thanks, I surprised myself here!!...so yes you can add a further (AND) test on [Date] < XXXX to only apply any uplift to future dates. One other thing to consider, is that as we're using FIXED LoDs to generate the Terciles, this is based on the entire dataset (regardless of any "regular" filtering), due to the order of operations in Tableau (image below). So if you want any filters to affect the tercile you'll need to make them "in context" (or higher)

                               

                              Order-of-Operations - JM.png

                               

                              Alternatively, you can "bake" the filtering into the formula...so the below would create 3 groups based on sales in 2015 only

                               

                              [Tercile Dim - LoD]

                              IF {FIXED [Customer Name]: SUM(IIF([Year] = 2015, [Sales], NULL))} > {FIXED: PERCENTILE({FIXED [Customer Name]: SUM(IIF([Year] = 2015, [Sales], NULL))}, 0.667)}

                                  THEN "H"

                              ELSEIF {FIXED [Customer Name]: SUM(IIF([Year] = 2015, [Sales], NULL))} > {FIXED: PERCENTILE({FIXED [Customer Name]: SUM(IIF([Year] = 2015, [Sales], NULL))}, 0.333)}

                                  THEN "M"

                              ELSE

                                  "L"

                              END

                              1 of 1 people found this helpful