12 Replies Latest reply on May 11, 2018 2:20 PM by Edward Carrillo

    Funding vs. Spending Burndown Chart

    Edward Carrillo

      Hello,

       

      I want to create a chart that shows how much I've spent out of my total budget I am allowed to spend. I have my data in two different Excel sheets - actual spending per document and budget per document (I've joined the data sources with an inner join, and I keep budget as an attribute so that it doesn't sum up - I'm not sure if there is a better way to do this). The goal is to show forecast spending that essentially shows how long it will take to burn through all of our funding based on our average spending per month thus far. Example: I have $100,000 of budget on Document D and I've spent $49,950 (about half) in 7 months. So it should take about 7 more months to spend through the rest of the $100k. The "Forecast" option is grayed out under the analytics, and I'm not sure how to approach forecasting with Tableau. Any guidance/solution that shows our cumulative actual spending so far as well as a burndown forecast would be much appreciated. Thank you!

       

      -Eddie

        • 1. Re: Funding vs. Spending Burndown Chart
          Jennifer VonHagel

          Hi Edward,

           

          I believe forecast won't work with other table calcs, but you can use table calculations to determine how many months it will take to spend your remaining budget at the average monthly spend rate.

           

          You could chart just the months of actual spent vs budget, and then show text information for number of expected months left in the budget (as opposed to what the forecast would do - plot months into the future). Here's an example. It shows the cumulative spent by month in red bars, and the remaining budget by month in blue bars. The cells above the chart give information on the avg monthly spend rate and number of months you have left at this rate until the budget run out.

           

           

          These are the calculations in the view:

          I created an explicit running total of Actual Spent: Spent Running: RUNNING_SUM(SUM([Actual Spent]))

          Budget Remaining: ATTR([Budget]) - [Spent Running]

          Month Budget Runs Out:

          'Budget runs out after ' +

          STR(ROUND([Budget Remaining ]/attr([Avg Monthly Spend per Doc]))) +

          ' months (during ' +

          STR(DATE(DATEADD('month',

              ROUND([Budget Remaining ]/attr([Avg Monthly Spend per Doc]))+1,

              attr({ FIXED : MAX([month])}))))+

          ')'

           

          Original Budget Text: 'Original Budget: $' + STR(ATTR([Budget]))

          Avg Monthly Spend Text: 'Avg Monthly Spend: $' + STR(ROUND([Avg Monthly Spend per Doc]))

          Remaining Budget Text: 'Remaining Budget: $' + STR([Final Budget Remaining ])

           

          Avg Monthly Spend per Doc: { FIXED [Funding Document] : AVG({ FIXED [Funding Document], DATETRUNC('month',[Date]) : SUM([Actual Spent]) }) }

          Final Budget Remaining: LOOKUP([Budget Remaining],LAST())

           

          To get the Spent Running bar to flip backward onto the Budget Remaining Bar, click "Reversed" under Edit Axis for Spent Running:

           

          This is the same information, but the bar chart format is more in line with your original charts . The Budget is shown as a Reference Line rather than a second measure.  I put the Budget on Detail so it would be available in the Add a Reference Line dialog box.

          Hope this helps,

          Jennifer

           

          P.S., I think that if we added a third data source - a table of dates that go into the future, that we could use the dates from this table to create extra rows in the charts shown, and then use table calculations to project the average monthly spend burning down over the future months. If this sounds like an option you'd like to try, let me know and I can see if I can trick it out.

          1 of 1 people found this helpful
          • 2. Re: Funding vs. Spending Burndown Chart
            Jennifer VonHagel

            I keep playing with this .

             

            You could also add in the individual time period's actual spend, to get a very quick sense of whether the average monthly is reflective of actual monthly spent. For example Document D is currently trending at a much lower monthly spend than original, so the budget may last longer than expected.

             

            1 of 1 people found this helpful
            • 3. Re: Funding vs. Spending Burndown Chart
              Edward Carrillo

              This is a great idea and a great thing to point out - thanks Jennifer!

              • 4. Re: Funding vs. Spending Burndown Chart
                Edward Carrillo

                Jennifer,

                 

                Thank you so much. I've spent the last hour or so replicating what you've done so I can learn from it - you've taught me so much already.

                 

                And yes, that would awesome to visually see a projection based on average spending so far (even though I love what you've added to the chart with the text above it). So I would need to add a third sheet with a list of future dates, by month, and we'd be able to join all 3? How would I approach creating that visual forecast with calculations? 

                 

                Again, thank you - I really appreciate your help!

                 

                Best,

                Eddie

                • 5. Re: Funding vs. Spending Burndown Chart
                  Jennifer VonHagel

                  You're welcome!

                   

                  So, this will get you started. I started this out creating just months into the future - you'll see the results of that below. when I start trying to use these calculations in charts rather than just the table, I ran into issues. To make this work properly, you'd be best off making your "Months" tab a combination of all Documents (A, B, C, D) AND all Months, then join sheet1 to this document on both Months and Document. The null document problem will then go away, and I believe the issues I am running into with the charting would also be fixed.

                   

                  I don't have time to flesh this out more at the moment, but hope this gets you going in the right direction.

                   

                  -----------------------------------------------

                   

                  For simplicity, I created a sheet showing months into the future. Your data source appears to have individual dates, so you may want your date table to show days rather than months - if you'll ever want to project days or weeks into the future.

                   

                  I added a "Months" sheet to your original Excel file. It starts at your data's first month and goes through 2022.

                   

                  I arranged your data like this. I brought Months in first, then did a left join to the date on Sheet1, because we want all dates in Months, even those that do not match months in Sheet1. Since Months has dates at the Month level, and Sheet1 has dates at the day level, I created a join calculation so that we could join at the month level in Sheet1.

                   

                  Also, I had to do a left join between sheet1 and sheet2 for the "Months" left join to hold.

                   

                  Ok, so I created a quick calculation to categorize months into Actual vs Projected:

                  • 6. Re: Funding vs. Spending Burndown Chart
                    Jennifer VonHagel

                    Psht, my message got cut off.  Here is the table with calculation definitions.

                     

                    Budget Running: IF ISNULL(ATTR([Budget])) THEN LOOKUP(ATTR([Budget]),FIRST()) ELSE ATTR([Budget]) END

                    Actual & Projected Spend:

                    IF NOT ISNULL(sum([Actual Spent]))

                    THEN SUM([Actual Spent])

                    ELSE WINDOW_AVG(SUM([Actual Spent]))

                    END

                     

                    Actual & Projected Running: RUNNING_SUM([Actual & Projected Spend])

                    Budget Remaining Actual & Projected: [Budget (Running)] - [Actual & Projected Running]

                     

                    When you first set up the joins and a worksheet like this, you'll find a NULL document column:

                     

                    Hide this column, don't filter it out. (But better yet, create a lookup sheet as described at the beginning of my last reply that contains every combination of document and month or date).

                     

                    Hope this helps,

                    Jennifer

                    • 7. Re: Funding vs. Spending Burndown Chart
                      Jennifer VonHagel

                      Oh I had a brainstorm that makes everything easier.

                       

                      Go ahead and set up a Months sheet in your workbook as originally described.

                       

                      Now we will use a full outer join in Tableau to create a lookup table that has every combination of month and document type - even those months into the future.

                       

                      Put Months and Sheet2 (your list of documents with their budget) in the view, choose a full outer join. They have no fields in common, so just create a join calculation of 1. This will join every record in months to every record in Sheet2. You can see the result is that there are as many months in the table as documents.  And this goes into the future - the future months in your Months sheet. So now we have created a lookup table here that shows every combination of month and document.

                      Now we will add in Sheet1 - your spend data.  We want all the records already in the table (that has future months), so we will choose a left outer join.

                      Join Sheet1 to Months on Funding Document and Month Date:

                       

                      And now the data is very much easier to work with. Here's the table with basic calcs:

                      Actual/Projected: IF ISNULL([Date]) THEN 'Projected' ELSE 'Actual' END

                      Actual & Projected Spend: IF NOT ISNULL(sum([Actual Spent]))  THEN SUM([Actual Spent]) ELSE WINDOW_AVG(SUM([Actual Spent])) END

                      Actual & Projected Running: RUNNING_SUM([Actual & Projected Spend])

                      Budget Remaining Actual & Projected: ATTR([Budget]) - [Actual & Projected Running]

                       

                      And this now works fine with Charts:

                       

                      Hope this helps,

                      Jennifer

                      1 of 1 people found this helpful
                      • 8. Re: Funding vs. Spending Burndown Chart
                        Edward Carrillo

                        Jennifer,

                         

                        You are a Tableau wizard. Thank you so much for the help!

                         

                        I have been playing with it a lot and I've learned a lot. I color formatted so that the bars turn red once our spending is greater than our budget. The last thing I am trying to do is to have the bars no longer display after the first month we've spent more than the budget. If you have any ideas, that would be great, but you've already helped so much, so no need unless you want to.

                         

                        Thanks again, I really appreciate it!

                         

                        -Eddie

                        • 9. Re: Funding vs. Spending Burndown Chart
                          Jennifer VonHagel

                          You're welcome!

                           

                          This should do it .  Filter Months:

                          ATTR([Month Date]) <=

                          window_max(DATE(DATEADD('month',

                              INT([Budget Remaining Actual]/attr([Avg Monthly Spend per Doc]))+1,

                              attr({ FIXED : MAX([month])}))))

                           

                          Also, The "Month Budget Runs Out" Calculation: It originally had ROUND() where it now shows INT(). You should have INT() there instead. Round() has the potential to round up to the next nearest integer, giving an incorrect result. In this case, we always want to truncate the decimals off and use only the result's integer.

                          'Budget runs out after ' +

                          STR(INT([Budget Remaining Actual]/attr([Avg Monthly Spend per Doc]))) +

                          ' months (during ' +

                          STR(DATE(DATEADD('month',

                              INT([Budget Remaining Actual]/attr([Avg Monthly Spend per Doc]))+1,

                              attr({ FIXED : MAX([month])}))))+

                          ')'

                           

                          Best,

                          Jennifer

                          • 10. Re: Funding vs. Spending Burndown Chart
                            Edward Carrillo

                            Hi Jennifer,

                             

                            I've tried to implement what you've given me, but it isn't working. I noticed that in my workbook, from the formulas you originally gave, I have "Budget Remaining" and you are using "Budget Remaining Actual". Have you added a different formula? Also, for some reason, the "Month Budget Runs Out" text no longer works on mine. I noticed this formula also includes "Budget Remaining Actual". Do you have any idea what the issue is?

                             

                            I attached my workbook again. There are a lot of tabs because I've been experiencing different ways to show the data, but the one that aligns with the way we've been working is called "Vertical by Doc".

                             

                            Again, thank you for the help!

                             

                            -Eddie

                            • 11. Re: Funding vs. Spending Burndown Chart
                              Jennifer VonHagel

                              Hi Eddie,

                               

                              Yes, I must have forgotten to write out Budget Remaining Actual. This is the most recent Budget Remaining value where the Budget Remaining is for Actual Spend. (Budget Remaining for the max month in the data that has Actual Spend). This should replace "Final Budget Remaining". I have added the calculation in the attached workbook, but here it is:

                              ATTR([Budget]) -

                              WINDOW_SUM(SUM(IF [Actual/Projected] = 'Actual' THEN [Actual Spent] END))

                               

                              For "Filter Months":

                              1. I added "Budget Remaining Actual"

                              2. When it is in the filter card, hit the drop down box, and choose Compute Using > Table Down.

                              3. Your Actual Spend dates are individual dates rather than months. So I also changed the calculation from finding max(date) to finding the max (month of date).

                               

                              The filter appears to be working now.  There have been a lot of iterations of formulas, and I can't remember which all I created , so in the attached workbook I put all the calculations that I think I created that are still relevant in a folder called "keep". Budget Remaining and Final Budget Remaining are calcs I think you can delete.

                               

                              As for the Month Budget Runs Out, yes it needs to use Budget Remaining Actual. I also updated the formula to use the month of "Date" rather than "Date". And then of course when you put this field on the view, you have to manually set it to say Compute Using > Table Down.

                               

                              Hope this works,

                              Jennifer

                              1 of 1 people found this helpful
                              • 12. Re: Funding vs. Spending Burndown Chart
                                Edward Carrillo

                                Jennifer,

                                 

                                This worked perfectly. Thank you. Have a great weekend!

                                 

                                Best,

                                Eddie