Hello!
I'm trying to brainstorm ways to create a way/formula on excel to trigger when I need to start moving production up going into the busy months. Something to break production down by week (like "you need to make this much of item 11111 this week"). I've watched a few videos on how to create something like this using Solver on excel, but none are working how I need.
This is an example of what I'm working with:
Item: is the specific item #
On Hand Qty: is the amount I already have - (do not want to completely deplete this)
Work Room: is the room in which the item can be created in
Time: is the time it takes to make 1 batch of the item. 1 batch = 1,000 lbs.
Yearly Requirement: is the total amount of the item I will need for the year
CP+1: is the Current Period plus one month (so essentially next month)
CP+2: is the Current Period plus two months (two months from now)
etc.
Any ideas/feedback would be greatly appreciated. Thank you!
I'm trying to brainstorm ways to create a way/formula on excel to trigger when I need to start moving production up going into the busy months. Something to break production down by week (like "you need to make this much of item 11111 this week"). I've watched a few videos on how to create something like this using Solver on excel, but none are working how I need.
This is an example of what I'm working with:
| Item | On Hand Qty | Work Room | Time to Make 1 Batch (minutes) | Yearly Requirement (lbs) | CP +1 | CP +2 | CP +3 | CP +4 | CP +5 | CP +6 | CP +7 | CP +8 | CP +9 | CP +10 | CP +11 | CP +12 |
11111 | 55,000 | 1 | 29 | 659,000 | 66,000 | 55,000 | 70,000 | 9,000 | 27,000 | 38,000 | 54,000 | 66,000 | 76,000 | 63,000 | 72,000 | 63,000 |
21111 | 106,000 | 3 | 18 | 569,000 | 87,000 | 38,000 | 54,000 | 7,000 | 11,000 | 28,000 | 44,000 | 59,000 | 67,000 | 52,000 | 58,000 | 64,000 |
31111 | 66,000 | 3 | 26 | 321,000 | 28,000 | 22,000 | 40,000 | 1,000 | 7,000 | 11,000 | 26,000 | 39,000 | 45,000 | 36,000 | 32,000 | 34,000 |
41111 | 39,000 | 1 | 19 | 300,000 | 37,000 | 26,000 | 23,000 | 4,000 | 7,000 | 15,000 | 21,000 | 29,000 | 37,000 | 32,000 | 31,000 | 38,000 |
51111 | 70,000 | 2 | 13 | 279,500 | 37,000 | 15,000 | 28,000 | 500 | 2,000 | 12,000 | 21,000 | 28,000 | 35,000 | 32,000 | 33,000 | 36,000 |
61111 | 59,000 | 3 | 12 | 201,500 | 25,000 | 12,000 | 22,000 | 500 | 7,000 | 10,000 | 13,000 | 20,000 | 26,000 | 20,000 | 22,000 | 24,000 |
71111 | 52,000 | 3 | 13 | 203,000 | 26,000 | 17,000 | 24,000 | 1,000 | 2,000 | 6,000 | 12,000 | 22,000 | 26,000 | 21,000 | 21,000 | 25,000 |
81111 | 57,000 | 1 | 16 | 201,000 | 19,000 | 13,000 | 17,000 | 1,000 | 2,000 | 4,000 | 14,000 | 22,000 | 30,000 | 28,000 | 26,000 | 25,000 |
91111 | 38,000 | 3 | 20 | 181,500 | 21,000 | 14,000 | 24,000 | 500 | 2,000 | 6,000 | 13,000 | 19,000 | 23,000 | 18,000 | 19,000 | 22,000 |
10111 | 49,000 | 2 | 17 | 181,100 | 26,000 | 9,000 | 16,000 | 100 | 1,000 | 5,000 | 13,000 | 20,000 | 25,000 | 22,000 | 19,000 | 25,000 |
On Hand Qty: is the amount I already have - (do not want to completely deplete this)
Work Room: is the room in which the item can be created in
Time: is the time it takes to make 1 batch of the item. 1 batch = 1,000 lbs.
Yearly Requirement: is the total amount of the item I will need for the year
CP+1: is the Current Period plus one month (so essentially next month)
CP+2: is the Current Period plus two months (two months from now)
etc.
Any ideas/feedback would be greatly appreciated. Thank you!
