0 Replies Latest reply: Jun 19, 2013 2:34 AM by Marvin Tejada RSS

    Calculating Weeks of Sales

    Marvin Tejada

      To the retail gurus, I need a little bit of help in a certain formula.

      I've been trying to figure out how to do a Weeks of Sales calculation over a straight table chart.

      To illustrate what I'm trying to accomplish, please see the table below.

       

       

      Fiscal PeriodBOPSLS RetailWeeks of Sales
      P014,600,000.001,211,393.00                3.44
      P024,429,066.001,072,099.00                3.29
      P034,584,333.001,766,039.90                6.04
      P044,119,848.001,238,298.00                8.87
      P053,922,274.001,198,110.00              15.20
      P064,341,743.72350,615.56              74.30

       

       

       

      Weeks of Sales is calculated by looping through each SLS Retail row and getting the number of periods/weeks BOP could cover it given the actual sales per period. For example, the P01 BOP of 4,600,000 could be spread over roughly 3.44 periods (total SLS Retail from P01 to P03 = 4,049, 531).

      I've been exploring combining rangesum and chart inter-record functions but I can't figure out how to make it conditional such that it compares BOP with a running sum of SLS Retail.

       

      Any hint would be of great help.

      TIA!