Sales Goal + Product Goal

Copper Contributor

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.

 

StoreTotal StockProduct 001Product 002Product 003Product 004Product 005 
Store 001400220 
Store 00220126101 
Store 003321000 
Store 004100100 
Store 005300030 
Store 00627212211 
Store 007300030 
Store 008000000 
Store 009600060 
Store 01024153123 
Store 0111330253 
TOTAL1049916628 
        
StoreSales ObjectiveProduct 001Product 002Product 003Product 004Product 005 
Store 0013      
Store 0029      
Store 0033      
Store 0041      
Store 0052      
Store 00614      
Store 0072      
Store 0080      
Store 0093      
Store 01012      
Store 0118      
TOTAL57769295<Product Objective
3 Replies

@mroldan5 

 

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.

 

Thank you for your comments Mathetes. I will try to explain better next time.

The first table is fixed information, it is the available inventory of each store by products.
For the second table, we assume that the sales objective is 55% of the stock of each store (approximately) "Sales Objective" column. To manually make the green section, I must check how many products each store has vs. what is the sales objective vs. the sales objective of each product and three are related.
For example, Store 001 has 4 products in inventory, but their goal is 3 sales, and they only have 2 products in stock, so they will contribute 3 sales for the overall goal and 1 for the Product 003 goal (9) and 2 for the Product 004 Goal (30).
I hope this helps. I will be waiting if more information or clarification is required.

@mroldan5 

 

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.

  • Why would you set as a sales objective only an overall 55% of current stock?
  • You (the organization) would really be satisfied if we end the period with 45% of the starting stock still on the books?
  • Is no new stock being generated?
  • Are we just trying to move existing stuff so that we can go out of business?

 

That aside, though, the individual entries in the green cells are just too arbitrary to be the outcome of any single formula.

  • Why not just go with the column "Sales Objective" for each store and let them achieve it in whatever way they can achieve it?
  • Are you going to penalize Store 001 if they end up selling 2 of product 3 and only 1 of product 4, the reverse of the objectives you've set?
  • Same question for others.
  • Is Store 8 going out of business? Why not transfer some of the various products to them and give them an objective of staying in business by doing some business?

It may be that it makes sense to you, but I don't see a meaningful way to do this via formula.