6 Replies Latest reply on Jul 3, 2013 9:42 AM by Ben Price

    What-if analysis and fixed inventories...

    Ben Price

      Hi All,

       

      I'm not sure whether Tableau is the best tool to be doing this in, but if it is, I was wondering if you may be able to help?

       

      I'm looking to do some what-if analyses with the data I have on a selection of websites, this includes:

      • What if we sold all inventory?
      • What if we increased sales price by a %?
      • What if we shifted the ratio of Resold and Sold goods?

       

      I was wondering what the best way to do these three things would be?

       

      I've attached a sample Workbook (site names changed to protect data) that I have set up 3x worksheets, two of which I have a problem with:

      1. Available inventory.
      2. Increased sales price by %
      3. Sold vs. Resold Revenue

       

      My three questions:

      • In the 1st sheet - How do I relate and compare this to the "impressions"? - At present, I get the average inventory but I get a sum of the overall impressions divided by the categories, not per category, there should be a data join that connects them, (There are a few table joins that I've had to create and I'm not quite sure what I'm doing here, so any help would be massively appreciated)
      • In the 2nd sheet - Why does the "Guardian" not show any numbers when there's clearly data in the database?
      • In the 3rd sheet - How would I be able to add a slider to test changing balances of the ratio of sold vs. resold? (Also, Guardian isn't showing up here either...)

       

      I've attached the tableau workbook, extract and main data source,

       

      Any help would be massively appreciated!

       

      Cheers,
      Ben

        • 1. Re: What-if analysis and fixed inventories...
          Ben Price

          Anybody have any thoughts on this?

          • 2. Re: What-if analysis and fixed inventories...
            Dana Withers

            Hi Ben,

             

            I think there is something weird and interesting going on with the links between the data sources. I'm a bit confused by the data you have. Looking at the excel sheet, can you clarify a bit more what is shown in the sheets and how it is related?

            For example how are Sizes and Combi-Tableau related? It cannot be on ref. It cannot be on size alone?

            I'm also confused about the link between Inventory and the rest. For example... BBC has 3 listings in Sizes, but only 1 in Inventory. So how are those two related?

            If I look at size 300x250, how can I tell if this is related to BBC listing or BBC Standard?

            Or should those two be ignored and just go for the combi sheet?

             

            My apologies for starting with just questions, but the key to any report issue is getting a grip on the data, and I'm currently not quite there with you . Love to help though!

             

            Dana

            1 of 1 people found this helpful
            • 3. Re: What-if analysis and fixed inventories...
              Ben Price

              Hi Dana,

               

              Not a problem, it's a bit complex, I'm going about a "quick-fix" with parameters and formulas now, but if I can get this working it would be massively useful.

               

              In terms of the relationships, each site has a certain "placement" that can show several different sizes of ads, the relationship goes:

               

              • Site > Placement > Size > Inventory.

               

              The links are as so:

              Combi-Tableau to Inventory by "Site"

              Inventory to Sizes by "Placement"

               

              As an example:

               

              • BBC has 4 types of placements (Header, Small, Listing and Standard)
              • Each of these placements has a set amount of inventory.
              • This inventory can then be split across the different sized ads that are available to be served through it (e.g. "BBCHeader" has 728x90, 870x66 and 970x250)

               

              I then want to be able to do two things:

              1. Allocate the inventory from each placement across the various sizes as I see fit (either with a slider or a drop down)
              2. Compare these inventories with the current impressions that are being delivered to see what additional inventory we could utilise.
              • 4. Re: Re: What-if analysis and fixed inventories...
                Ben Price

                I just added another Join:

                Sizes to Combi-Tableau by "Size" - That seems to have had a positive effect...

                • 5. Re: What-if analysis and fixed inventories...
                  Dana Withers

                  Hi Ben,

                   

                  I can't see a difference because it is not a packaged workbook. Can you upload a packaged workbook instead of a normal one?

                   

                  What I'm still not quite understanding is that your placements and sizes can't really mix. So I understand that your inventory for - for example - BBC Small should total up to 400.000 which is easy because it is the only option possible. The BBC Standard has two options, but they have nothing in common with BBC Small. If you want a slider to change the allocation, you'd have to take into account what it can actually allocate to at the same time?

                   

                  I'm also still a bit confused from your combi sheet, that if it says BBC and size 300x250 - how can you tell if that is on Listing or Standard without double counting?

                   

                  Dana

                  • 6. Re: Re: What-if analysis and fixed inventories...
                    Ben Price

                    Hi Dana,

                     

                    I've attached a packaged workbook so you can see the results.

                     

                    In terms of the relationships, the individual placements don't relate to each other, the placements relate to the sizes.

                     

                    In this example, the slider would change the available inventory for the various sizes, for example:

                    • BBC Standard has two options: 300x250 and 300x600
                    • I'm aiming to get the inventory of 11,000,000 and split it between the two different sizes (or 3 in the case of BBCHeader) with some sort of slider.

                     

                    In the Combi-sheet, this is what I've attempted to do with the data joins, joining the size to the placement and then back to the inventory.