1 2 Previous Next 19 Replies Latest reply on Jul 29, 2016 1:03 PM by Mark Worthen

    Time to Break Even

    Mark Worthen

      I am fairly new to Tableau and have been asked to do a Time to Break Even analysis.  For a prototype I mocked up some data and brought it into Tableau.  Graphing the data and being able to see the break-even point is quite easy.  However, what the user wants is to know the exact quarter in which our investment goes from negative to positive and the number of quarters it takes to go from negative to positive.

       

      Easy to see on the chart and to count but how to I say Break Even occurs in X Quarter and it will take Y Quarters to Break Even?

       

      See the Revenue vs. Break Even chart in the attached file.

       

      Thank you for your help.

        • 1. Re: Time to Break Even
          Rahul Upadhye

          Hey Mark,

          You are on a right track.

          Visually it would be more appealing if u add colors.

          • 2. Re: Time to Break Even
            Mark Worthen

            I agree colors would make it more appealing and will likely do that if they choose to have visual input as well but my primary concern is not the graph, but how to I ascertain that break even comes in FY18-Q4 and how to I determine the number of quarters required to get to the break even point?  Right now the request is to know how many quarters to BE and in which quarter BE will occur.

             

            Considering some projects may not start for two or three quarters, I cannot use anything in regard to the current date, but from when the project will actually start.

             

            Thanks.

            • 3. Re: Time to Break Even
              Mark Worthen

              Of course, being a beginner, I'm not sure how to add color like you did so nicely.  Care to share? 

               

              Thanks.

              • 4. Re: Time to Break Even
                Rahul Upadhye

                Hey,

                Color can be added using the colors pill & they can be customized.

                See the new calculation "BE' and place that on the Labels to display when BE occurred.

                The problem is, in ur data Project quarter is a string, if u convert this column to be date type M/D/YYYY, then u could bring the min(Project quarter) to display exactly when BE occurred.

                See the workbook, its ver. 9.3

                • 5. Re: Time to Break Even
                  Mark Worthen

                  Thanks Rahul,

                   

                  I am on v9.2 (company standard at this time) and so cannot open the file.  Is there any way you can save it in 9.2 format?

                   

                  Thanks.

                  • 6. Re: Time to Break Even
                    Rahul Upadhye

                    Hey Mark,

                     

                    I just downgraded to 9.2 here.

                    1 of 1 people found this helpful
                    • 7. Re: Time to Break Even
                      Rahul Upadhye

                      Try this it might be helpful:

                      Data + Science

                      1 of 1 people found this helpful
                      • 8. Re: Time to Break Even
                        Mark Worthen

                        Thank you again Rahul.

                         

                        What I am still after is how to identify the specific quarter in which BE occurs and the number of quarters it takes to reach BE.

                         

                        These need to be values I can display on a dashboard.  Any thoughts there.  Graph looks much nicer now and you've helped me learn more how to utilize the tool.

                         

                        Thanks.

                        • 9. Re: Time to Break Even
                          Rahul Upadhye

                          Hey Mark,

                           

                          BE occurs when previous value was negative & current value is positive.

                          Essentially u need to convert this logic into IF statement.

                           

                          IF

                          (LOOKUP(SUM([Projected Amt]),-1)<0)

                          AND

                          (LOOKUP(SUM([Projected Amt]),0)>0)

                          THEN 'BE'

                          end

                           

                          first condition checks whether previous value was negative

                          second condition checks whether current value is positive

                          both condition means its a BE

                           

                          This is the first part of solution.

                           

                          For the second part: number of quarters for BE to occur, this needs counting quarters.

                          Quarters can be counted when its date format.... strings cant be used to calculate counting.

                          Pls convert ur Projected QTR into date type and let me know.

                          1 of 1 people found this helpful
                          • 10. Re: Time to Break Even
                            Mark Worthen

                            Thanks again Rahul.

                             

                            I've been working with the data and I can easily see when the BE occurs but being able to put on a dashboard/report that it will take X quarters and break even in Y quarter is a different issue.  The visualization is great, extracting the data from the visualization is what I'm struggling with.

                             

                            In the attached file I've got the Revenue vs. Break Even (revenue showing the discounted cash flow by quarter and BE showing the cumulative discounted cash flow - when does the cumulative cash flow turn positive).  Using that it is easy to see that BE occurs FY18-Q4 and it takes 8 quarters to BE.  However, how to I extract those two data items which are so easily seen?

                             

                            I have somewhat of a stab at that in the BE QTR & Num QTRs sheet which has columns for the projected amount (discounted cash flow), cumulative projected amount, and also ranks the quarters in order and "highlights" the quarter in which BE occurs.

                             

                            It is still essentially a visualization and I am still not sure if there is a calculation I can use which will tell me specifically the BE Quarter.  Some of these items require going into advanced to make sure the table calculation is correct and so if anything changes about the table/chart, I end of having to redo those.  I find that frustrating.

                            1 of 1 people found this helpful
                            • 11. Re: Time to Break Even
                              Rahul Upadhye

                              Hey, i've just modified the BE logic as shown here:

                               

                              Now based on this I see a visual cue on ur dashboard:

                               

                               

                              Now u wanna extract those values? What do u mean by that? do u want to see the underlying data like this:

                               

                              Often I leverage most of the complex logic in the  data , Tableau is not good at cross tabs and u especially if u want to extract an outlier based on a logic it has to include all the data to execute that computation which is eventually passed on to your Underlying Data Tab.

                              1 of 1 people found this helpful
                              • 12. Re: Time to Break Even
                                Mark Worthen

                                Another nice touch.  That helps. 

                                 

                                I guess what I had envisioned was to be able to have a summary line on a dashboard which would say for Project X the BE Quarter is Y and it will take Z quarters to reach BE.

                                 

                                Rather than a line, it could be a table simply showing the project, is current stage and the number of quarters required for BE and the actual quarter in which that project will BE.

                                 

                                Thus, my desire to be able to "extract" the number of quarters and the BE quarter.

                                 

                                You've been very helpful in helping me understand Tableau and how to do some nice things. 

                                • 13. Re: Time to Break Even
                                  Rahul Upadhye

                                  Number of Quarter comes through a Date Comparison.

                                  for ex: A - B = delta

                                  so if A is ur BE quarter then what is B ? is it current quarter? or is it an input from the user like a parameter in tableau?    

                                  • 14. Re: Time to Break Even
                                    Mark Worthen

                                    B is the quarter in which a project starts.  Once there is cash flow associated with a project, it has begun. 

                                     

                                    The question of number of quarters to break even is "How many quarters will it take for this project to break even" as compared with "How many MORE quarters will it take for this project to break even".  The second scenario would use system date or a user input date, etc. to determine but at this point, the only question is from the start of the project, how many quarters will it take to break even.

                                     

                                    After playing with this, I'm thinking I'm probably better off doing some pre-aggregation in SQL rather than trying to do everything in Tableau.

                                    1 of 1 people found this helpful
                                    1 2 Previous Next