1 Reply Latest reply on Oct 24, 2014 11:52 AM by EricBrown_1

    Production Calculation

    EricBrown_1

      Title

      Production Calculation

      Post

      Here's my puzzle: Given 3 products and their known sales ratio, calculate the required production quantity to balance the inventory to match the sales ratio?

      Example:

      Product A (comes in 3 qty)

      Size              Sales Ratio    Inventory (Units)

      size 7oz         15 %              20

      size 18oz        55%              30

      size 72oz        30%              25

      Manufacturer makes 1000 oz of new product a and wants to determine how much to assign to each size to bring his new inventory to match sales percentages. 

      My formula: x =  ( volume ratio ( inventory volume + volume produced ) - inventory volume ) / container size

      works when all sizes need to be produced, but does not work if there's an excess of one or more sizes and the formula results in a negative for that(those) sizes. This leads to an amount being applied to the positive values that exceeds the produced volume qty. I need to account for the negative(s) and factor into all sizes.

      Calculation.jpg

        • 1. Re: Production Calculation
          EricBrown_1

          Just to help anyone that might come by this post with a similar problem in the future, I resolved the problem with the following formula, adjusting the referenced fields for each product size:

          If ( Daily Batch | Recipe Variables::vol7oz ≠ 0 ; 
            Let 
              ([
              Eval7oz = ((( SumHardInventoryOZ + QtyToAllocate ) * Daily Batch | Recipe Variables::vol7oz) - ( Inventory7oz * 7 )) ;
              Eval18oz = ((( SumHardInventoryOZ + QtyToAllocate ) * Daily Batch | Recipe Variables::vol18oz) - ( Inventory18oz * 18 )) ;
              Eval72oz = ((( SumHardInventoryOZ + QtyToAllocate ) * Daily Batch | Recipe Variables::vol72oz) - ( Inventory72oz * 72 )) ; 
              Ratio7oz = Daily Batch | Recipe Variables::vol7oz ;
              Ratio18oz = Daily Batch | Recipe Variables::vol18oz ;
              Ratio72oz = Daily Batch | Recipe Variables::vol72oz
              ];
           Case (
              Eval7oz < 0 ; 0 ;
              Eval7oz ≥ 0 and Eval18oz ≥ 0 and Eval72oz ≥ 0 ; ( Eval7oz / c7oz ) + SpecialOrder7ozFilter::sumPack7oz ;
              Eval7oz ≥ 0 and Eval18oz < 0 and Eval72oz < 0 ; ( ( Eval7oz + Eval18oz + Eval72oz ) / c7oz ) + SpecialOrder7ozFilter::sumPack7oz ;
              Eval7oz ≥ 0 and Eval18oz ≥ 0 and Eval72oz < 0 ; ( Eval7oz / c7oz ) + ( Eval72oz * ( Ratio7oz / ( Ratio7oz + Ratio18oz ) ) / c7oz ) + SpecialOrder7ozFilter::sumPack7oz ;
              Eval7oz ≥ 0 and Eval18oz < 0 and Eval72oz ≥ 0 ; ( Eval7oz / c7oz ) + ( Eval18oz * ( Ratio7oz / ( Ratio7oz + Ratio72oz ) ) / c7oz ) + SpecialOrder7ozFilter::sumPack7oz ;
                     )
              )
          ; "" )

          Please note that there are items included not shown on the initial problem like special orders that are taken out of the QtyToAllocate field and then brought back in for final packaging equation. I applied this to the three sizes with the correct fields and it now works correctly. 

          Hope this saves someone else the time it took me to resolve.