I had a question related to calculating weighted average, on data example below given definition :

  • Column C = Product Supply
  • Column D = Product Demand
  • Column E = Supply / Demand
  • Column F = Demand Contribution (demand / total demand) to calculate total weight
  • Column G = SD Ratio * Demand Contribution (E*F)

S/D ratio >1 means that we had too much supply and <1 means that we need to provide more supply. In case below im trying to aggregate the number to get better sense on warehouse A whether we are on overall over / under supply, using weighted average based on demand (we want prioritize warehouse with higher demand and product with higher demand) but i dont understand the approach is correct or not since when im summing the weighted ratio the total is tally to total supply/ total demand (column C13 = Column G12) is this the correct way to do the aggregation?

Case Example

  • $\begingroup$ The question is what you really care about and what you are going to do with it. It makes sense that the two fields in red match up. If you weighted your rows properly then they should definitely match. $\endgroup$ Oct 15, 2020 at 20:37
  • $\begingroup$ Also, you have to consider whether you can really count units of supply. What if it takes 1 hours to start making product A2. But then you can make 10 per 2 minute cycle. That would mean that it will take you 62 minutes to make 10 of them (per demand requirement) or 76 minutes to make 80 (what you got in supply). You shouldn't optimize to demand in this hypothetical because there is a setup time and cost involved. $\endgroup$ Oct 15, 2020 at 20:40
  • $\begingroup$ Hi @AdamSampson Thanks for the answer, in this case supply and demand is aggregated in one hour bucket, what im trying to do is to get a sense of which warehouse/product is actually over/undersupplied by looking into the aggregated numbers, and we want to prioritise it by demand, For example on the case above on one hour, product A1 and C3 is undersupplied i want to put more attention to product C3 since the demand is higher, hence we will take the budget from most oversupplied product in this case A2 and put the budget to vendor C3 so we can provide better supply for this product. $\endgroup$
    – Decile
    Oct 16, 2020 at 3:55


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.