Our business is trying to back into a price discount total based on forecasts of gross sales, percent of gross sales, and percent of MSRP. The gross sales are forecasted by day while the percent of gross sales and percent of MSRP are forecasted by product status and month. For example, I may forecast that 2.5% of total gross sales today will come from products in the "F" status, and those products will be sold at 53% of MSRP.
Here's a budget for percent of gross sales and percent of MSRP last month:
+----------+-------------+-----------+
| | % of Demand | % of MSRP |
+----------+-------------+-----------+
| Status B | 2.49% | 39.01% |
+----------+-------------+-----------+
| Status I | 9.02% | 53.48% |
+----------+-------------+-----------+
| Status A | 19.58% | 99.00% |
+----------+-------------+-----------+
| Status T | 20.57% | 99.00% |
+----------+-------------+-----------+
| Status N | 32.31% | 99.00% |
+----------+-------------+-----------+
| Status S | 10.00% | 82.49% |
+----------+-------------+-----------+
| Status F | 7.03% | 97.77% |
+----------+-------------+-----------+
If I want to calculate the weighted MSRP, I use this formula: $Weighted MSRP = \sum(Percent of Demand)(Percent of MSRP$)
For the table above I get 92.7%
However, when I include the sales forecast I get something different. The formula I will use is this: $Weighted MSRP = \frac{Gross Sales}{\sum\frac{(Percent of Demand)(Gross Sales)}{Percent of MSRP}}$
This formula makes sense to me because the main denominator should give me Gross MSRP dollars, and dividing Gross Sales by Gross MSRP dollars should give me the Percent of MSRP. However, when I do the calculation it does not match my first. For example, if I use a gross sales total of 44,115.40 with the table of data above I am seeing a Total MSRP Dollars of 52,504.59 and a Weighted MSRP of 84%, which is much different than the 92.7 I calculated above. Why aren't these MSRP percentages matching?