How to use Excel to calculate units based on capacity and priority by hour

I'm trying to automate Excel to plan units (in columns C and E) that can be produced based on capacity (B and D) and priority by hour (G to J). If possible it would also be based on the time values in columns L to N, so the time window can be turned on/off. How would this be done? The capacities in columns B and D will eventually reference both one another and the values in the plan (columns C and E) so I will have iterative calculation turned on to get around circular references. I've attached an example worksheet.

Thanks in advance for any help on this!

Hi PKowalik, the 1h cake/bread is a different product category from 2h cake/bread. So the 50 cakes and 600 bread at 1h bake would need to be cooked first (assuming no backlog) as they take priority, and once they're all in the ovens any spare capacity can be used on the 2h bake, and so on. There's no real difference between price/quality it's just ranked based on how quickly they need to be finished and ready to go to a customer.

Yes that's correct.

It's possible to bake both in the same time window (that's what we do in the bakery). In the full dashboard (the one attached here is just a snippet) there is a separate formula that calculates cake and bread capacity. The capacity is an either/or, so you can bake a maximum of 100 cakes or 500 bread between 0630-0730. If you entered in 100 cakes into the Cake plan then the bread capacity would drop to 0, if you entered 50 then it would drop to 250, etc.

Yep, priority just means to finish the job as quickly as possible. As it's a small bakery we usually aren't running for the whole day.

Ideally yes we'd use as few time windows as possible. Start and end time doesn't really matter, logic should be to fill up windows to max capacity and stop once all units have been baked. But we'd need to have the ability to turn windows on/off (columns L to N); whether a window can be turned on/off depends on whether someone is available to do the baking but I'd do that manually. Customer orders are cut-off at 8pm the day before so there are no orders dropping in throughout the day, the number of ordered units are fixed.

Re: priorities I apologise if I've made this overly confusing! It should be to fill up the capacity of the earliest open window by using the product with the highest priority first. The only case where it differs is with backlogs where they would ignore windows being open/closed in columns L/M/N and just fill up all the capacity until all units were accounted for.

So for cakes we have a capacity of 100 units at 0630. Let's ignore the backlog of 1000 units and assume the window is turned on. That would mean filling up the 0630 window with all 50 of the 1h bake and then 50 units out of 150 for 2h bake. The 0730 window would then fill up with the remaining 100 units left from 2h bake and 900 from 3h bake, and so on. If the 0630 and 0730 windows were turned off then it would have to start with the 0830 window, and so on. If we include the backlogs then they would ignore windows being turned on/off and take first priority in filling up all the capacity from 0630 onward (logic being that if we have backlogs we open at 0630 and I'd bake them myself if my siblings weren't able to-it's a family bakery). However if a closed window had spare capacity after doing backlogs then I don't want it to fill up with anything else (as backlogs are usually done by someone rushing in to do just those units).