Production planning - semi automated

[Tomcma]

I would like to replace the manually replenished production plan with (semi) automatic planning
production plan is in (working) days
the production process has 7 stages one after the other
two stages: forging and grinding can overlap, i.e. the last day of forging can be the 1st day of grinding
each stage has a limited number of positions
for each project the length of the stages may be different
it must be possible to delay the start of the entire task or one of the stages, also for already started tasks (then the released resources should be used by other tasks)


information provided at the start:
duration of individual stages
start date (if there is no date, it should start as soon as resources are available, otherwise it will start looking for available resources after the provided sart date)

This is desired look/outcome

At the moment I calculate each step separately - for every single day I have 7 cells with formulas to calculate each step progress than 1 cell to combine/collect results
Each "day/step" formula calculates:

• if today is working day and project have "ready to start" status and previous task was completed
• if resources are free (count "up", how many times resource was used and compare with available resources)
• if task was already done (count "left", how many times this stage was "done")

it's something like on screen below

so calculations range of each formula is bigger than previous, for each task there's 54000 formulas (360 days * 150 tasks) - it take ages to change/calculate anything

Preferred solution should base on formulas but at the moment I would appreciate any suggestion/help how to speed up simplify the process
Thanks in advance for any help

 

[Kaper]

This sounds like a kind of linear problem, so probably with cumulative approach to formulas you could limit range they refer to. And then 50+ k formulas would not be a big deal to excel.

Moreover, at given moment of time, you probably do not need to look at the whole year, so you could prepare smaller sheets/workbookjs may be in quarterly or even monthly basis (including last few days of previous period and simplify your calculations.