Optimisation of production based on price of energy

[LauriT]

Hi,
can you take a look at the attachment?

My question is.
What is the optimal order to produce these products, so that ENERGY COST TOTAL is minimal?
I assume we have 120 possibilities in this case.
A,B,C,D,E
A,B,C,E,D
A,B,D,C,E
A,B,D,E,C
etc,
We don't want to split products hourly. The same products should be produced in a row.

Can anybody help me to optimise this?
Macro, visual basic code?
Any help, I don't want to optimise manually everyday ;-)

Thanks, Lauri.

[Alf]

Perhaps you could use the excel add-in solver?

I also think you need to add a bit more explanation to your work sheet. I assume that the different products consume different levels of energy and during the day the energy cost varies based on availability and demand but what is mwh_h and e_mwh?

Alf

[LauriT]

Thanks Alf.

Excel add-in solver looks promising, it might be usable?
But how to utilise it, I don't have experience with it?

You guessed the problem right, more explanations:

mwh_h = MWh/h (Megawatt Hours per hour)
and e_mwh = €/MWh (euros per Megawatt Hour)

When you multiply those, you will get €/h (euros per hour) as unit.

[Alf]

I’ve set up a solver model based on your information. It’s not the model you wish for as one of your demands was that a product should be produced in “one go”.

This model splits production up in smaller chunks. Your problem is interesting so I’ll keep on working with it to see if I can model your final request i.e. when production starts of a product the process should run until all is produced.

As I’m off for a two week holiday it may take a while before I may have an answer that is if I succeed. If not I’ll post a message telling you I’ve given up. So let’s hope some other forum member will help you as well.

Logically you problem is not that difficult. To get the lowest production costs you run the processes that consume the most energy when energy cost is as low as possible.

So you should start by running production of E for 5 hrs, then 5 hrs of C, then 3 hrs of D, then 4 hrs of A and finally 3 hrs of B. Using this approach you get a total energy cost of 24590 euro. The solver solution stops at 24366 euro but not with the product pattern you wish for.

Alf

[LauriT]

Thanks Alf,
this formula really works. :-)

But in real life I want to optimise with more variables (more products, longer time) this function has the limit of 200 variables.
From solver.com I can buy a product which has bigger capacity, until 8000 variables. (With less than 2000$)
It might be worth of that also in this case?

"So you should start by running production of E for 5 hrs, then 5 hrs of C, then 3 hrs of D, then 4 hrs of A and finally 3 hrs of B. Using this approach you get a total energy cost of 24590 euro."
- This is just the way I want to optimise. Because in practise there are losses with every change in production.
- I think also optimisation is easier with this idea, less variables?

Let's see if we can find a solution utilising this?

[Alf]

this formula really works

It’s not a formula really but the result of a solver run (see solver-energy1 & 2 pgn files) but you could of course use the binary grid (range C52:g71) with a manual input to find the most optical / practical production pattern.

As for buying one of the more advanced solver models I think you still have the option to download a model and test it free of charge for 14 days to see if this is what you need.

I also got the impression that during this testing period the staff at solver is most helpful with getting your model up and running. After all they do wish to sell their product.

Building your solver model could be a major problem depending on the complexity of your process. One should also be vary of putting to much limitation in a model as this will decrease solver's flexibility. You mentioned losses with every change in productions a thing that should be analyzed. Is the savings shown by solver greater than the losses caused by production changes then it may make sense to change production pattern.

The major problem with solver and LP models is that time is not a constraint. In a solver/LP model all thing happens at once. I worked in a refinery where we used a LP model for the monthly refinery planning. So we knew total production and blending pattern for the next month but not exactly on what day we could produce a specific product. For that we needed other tools to “translate” the LP solution to a weekly/daily practical operating plan.

Still Solver is an interesting tool to work with and a most powerfull one as well. Best of luck with modeling while I loaf around in Vienna enjoying the resturants and the wine bars for the next 10 days.

Alf