• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Allocation and planning Tool

Shanzil

New Member
I want to allocate campaign products to various stores.
Idea is for example we have product A in stock and we want to allocate this product to No. of stores.

ProductStock
A
50​

We give input like 50 (what we have in stock) than excel make calculation and come up with proposal for allocation. Where allocation proposal based on what we have available in stock and last year sales. Stores with high rank will get more stock then stores with low rank.
Where stores have their own ranking based on their size and sales.

StoresRankingAllocation
1​
2​
10​
2​
1​
3​
3​
2​
5​

We can also allocate stock based on percentage, as we have to allocate 75% of available stock to all locations.
Data input
Excel worksheets containing product data like: (possibility to choose item worksheets like shoes, t-shirts, house holding) etc.
  • Item name
  • Item description
  • Current stock
  • Last year sales
  • Demand
Excel sheets with stores id’s with their respective names and priority ranks.
  • Small stores in size and sales = 1
  • Medium stores in size and sales =2
  • Big stores in size and sales = 3
When the application start comes up user form containing 3 como boxes
  • Product (where products are shoes, t-shirts and house holding) can be selected to allocate particular product
  • Month jan to dec (in which month we want to allocate our stock (based on last year sales in respective months.

  • Quantity (how much we want to allocate. (Input box)

Then hit the allocate button and excel make calculations in back ground based on store rank and allocation rank, demand and last year sales and provide us an allocation proposal for respective item in respective month in sheet allocation shoes in allocation Colum.

User form having button like allocate, reset to rest selected data in user form and close to exit user form.
 

Attachments

vletm

Excel Ninja
Shanzil
If You write something and Your file has something else ... what do You expect to get?
... something else than You wrote or what?
Good luck
 
Top