I am stumped when it comes to creating a formula for the following scenario: I have forecasted demand for the time frame in question. What I am attempting to do is to determine the optimal mix of 4 of our different inventory items (Products A, B, C, & D) that are variable depending on demand that are needed to make a the sale. For instance, I forecast that account # 12347 will service 693 customers from 9/1-9/15. I need to know how many of each products that I need to send them for this period. For this example, I would send that account location 1 of Product A (supplies for 400 of the 693 customers) and 1 of Product B (supplies for an additional 300 customers). They would have enough supplies for 700 customers (7 more than they are projected to need). With thousands of accounts, I need to come up with a formula that would give me the optimal mix of Products A-D to service their projected amount of customers for 9/1-9/15. Your help would be a huge time saver!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
- Product A: Has enough supplies to service 400 customers <o></o>
- Product B: Has enough supplies to service 300 customers <o></o>
- Product C: Has enough supplies to service 200 customers <o></o>
- Product D: Has enough supplies to service 100 customers