Jan 11 2023 10:30 AM
Good afternoon to the entire community.
I have a problem and I need your valuable help.
Monthly I have to calculate the sales goal of a certain group of stores in total numbers depending on the amount of inventory they have (easy).
The problem is that I also have to reach the goal per product, considering the availability of each store and without going over the store goal.
Can you think of a formula to achieve this? (To fill in the blanks in the 2nd table)
Thank you very much in advance.
PD. I uploaded an example; the green cells are the ones I do manually.
Store | Total Stock | Product 001 | Product 002 | Product 003 | Product 004 | Product 005 | |
Store 001 | 4 | 0 | 0 | 2 | 2 | 0 | |
Store 002 | 20 | 1 | 2 | 6 | 10 | 1 | |
Store 003 | 3 | 2 | 1 | 0 | 0 | 0 | |
Store 004 | 1 | 0 | 0 | 1 | 0 | 0 | |
Store 005 | 3 | 0 | 0 | 0 | 3 | 0 | |
Store 006 | 27 | 2 | 1 | 2 | 21 | 1 | |
Store 007 | 3 | 0 | 0 | 0 | 3 | 0 | |
Store 008 | 0 | 0 | 0 | 0 | 0 | 0 | |
Store 009 | 6 | 0 | 0 | 0 | 6 | 0 | |
Store 010 | 24 | 1 | 5 | 3 | 12 | 3 | |
Store 011 | 13 | 3 | 0 | 2 | 5 | 3 | |
TOTAL | 104 | 9 | 9 | 16 | 62 | 8 | |
Store | Sales Objective | Product 001 | Product 002 | Product 003 | Product 004 | Product 005 | |
Store 001 | 3 | ||||||
Store 002 | 9 | ||||||
Store 003 | 3 | ||||||
Store 004 | 1 | ||||||
Store 005 | 2 | ||||||
Store 006 | 14 | ||||||
Store 007 | 2 | ||||||
Store 008 | 0 | ||||||
Store 009 | 3 | ||||||
Store 010 | 12 | ||||||
Store 011 | 8 | ||||||
TOTAL | 57 | 7 | 6 | 9 | 29 | 5 | <Product Objective |
Jan 11 2023 11:05 AM
Monthly I have to calculate the sales goal of a certain group of stores in total numbers depending on the amount of inventory they have (easy).
You say "easy" but that's because you know the situation. It no doubt IS easy, but can you put into words why it's easy; what it actually amounts to. Is it simply a matter of
Inventory = NN, therefore Sales goal = NN
The problem is that I also have to reach the goal per product, considering the availability of each store and without going over the store goal.
Or is THIS why it's not so easy after all? And what exactly does it mean?
Can you think of a formula to achieve this?
I'm sure the ultimate formula is going to be fairly easy, but you can help me (and others) help you if you put into words (NOT formulas), plain ol' English words, what you go through to get those numbers in the green. You think it's easy/obvious because it is to you; you're so close to the situation, have done it many times....rather than ask us BOTH to reverse engineer the numbers AND to come up with the formula, please talk us through the way you get from table 1 to table 2, using several of the cells to explain. Don't need to do them all, but a representative sample would be helpful.
Jan 11 2023 12:04 PM
Jan 11 2023 01:58 PM
Both from what you've written now, as description, and from looking more deeply at the example you've provided, it seems to me that there's not an easily established formula of hard and fast rules, or even consistent conditionals, to achieve your objective.
The whole premise seems strange, as far as that's concerned.
That aside, though, the individual entries in the green cells are just too arbitrary to be the outcome of any single formula.
It may be that it makes sense to you, but I don't see a meaningful way to do this via formula.