Forum Discussion
Production Allocation
You are right, I have attached an excel sheet which explains the problem and what I am trying to do.
That spreadsheet is a good start, but I personally would still need to know a little more. As I said, the scope of this, even the mental procedure is clear in YOUR mind. If I were there with you in person, I'd want you to walk me through a bit of how you solve this now.
But, all that said, it's pretty clear that you would benefit from a specific Excel Add-in called "Solver" Here's an screen image of what comes up if you search in Excel's Help function. It's entirely possible that you could resolve your own situation simply by loading Solver and following its procedures.
And here's a screen grab of the dialog box that opens up when you activate the Solver Add-In. Just looking at it tells me that I'd need to know a bit more, probably experiment with just a few of the production units and machines to make sure I was using the right "engine"...and then too, are we talking getting all of this done in one day, one week? Just as soon as possible?
I am trying to use solver but it is not giving me the right answer. So there are different SKUs listed from GT-1 to GT-36. The number of units with each SKU is written with their numbers. I want to allocate each item to different machines which are written as machine-1 , 2, 3 etc. The only constraint is the capacity of the machines. I want to allocate the production units in a way that capacity of the machines is satisfied.
I haven't used Solver in a couple decades myself. I have it, but haven't had occasion to use it. Most of my active spreadsheets deal with investing and finance.... [and I'm retired from the corporate world]
Anyway, in the "Problem" spreadsheet you posted, I see the item numbers and a number underneath each. For example, under GT-1 I read 73.666667. Is that both a SKU and an indication of numbers to be produced {I think the answer is "no," but that's an illustration of what I've been saying---your situation is clear to you, but not to your audience here). I'd be happy to try to apply Solver to help, but need to know a lot more that you DO know.
Have you tried using "solver" with just two machines and two units, for example? You write that Solver "is not giving me the right answer" which suggests you have some idea--from prior experience or whatever--as to what "the right answer" should be or should look like. I can't possibly know whether I'm getting a right answer, given that I still don't know what part of all the data is the SKU, what is the volume of each unit desired, what kind of time constraint (if any), etc. etc. I apologize for keeping repeating that, but at this point you are clearly the most knowledgeable on your situation and what parts of your data might go into what spots in Solver's dialog box.
Am I right, for example, that you'd like Excel (Solver, within Excel) to consider multiple alternative combinations of various units with various machines in order to find the optimum combination? Running through various scenarios would take time, if that's the case.... but you need to spell it out more fully.