Anyone Solve This - Production Scheduling Challenge- Solver?

[GorD]

Anyone up for a challenge - this appears so simple, but I have found it to be anything but in reality.

A production line makes one main product made up of two manufactured piece parts. You can sell both the main product and the piece parts(spares), but the sale of spares is limited to less than that of the main product. There are 3 machines each with a set up and run time. The pink colour is one machine and the green another. The question is what is the best utilisation of the machines to maximise profit. You start with £1500, the challenge runs for 2 weeks. Raw material for each line costs £10. You have fixed costs of £2500 weekly. Which you must be able to pay after one week (5 days, 8 hrs per week)

Can anyone think how to model this - Can this be solved with solver, I can't define it properly.

There is a small program that allows you to run your line (like a small game) which I can send if anyone is interested.

Sorry I haven't expalained it very well
See diagram below, Product flows from left to right (arrows didn't show)

S=set up in mins
R=run time in mins

Book1
	C	D	E	F	G	H	I	J	K	L
1	Production		Scheduling		Challenge
2	StationA									SparePart1
3									sell	40
4
5	s=240						StationE
6	r=28						s=180			MainProduct
7							r=25		sell	60
8
9	StationB		StationC		StationD
10									sell	SparePart1
11										30
12	s=120		s=360		s=360
13	r=10		r=6		r=
14
15
Sheet1

 

[GorD]

Re: Anyone Solve This - Production Scheduling Challenge- Sol

bump

 

[Scottcmu]

Re: Anyone Solve This - Production Scheduling Challenge- Sol

I took an entire class on this in college - it was called Models and Methods for Optimization. I'm sure solver can do this if you set up the right boundaries. I guarantee that there is some literature out there that will show you how to use Excel to do what you want. My suggestion is go to the library or purchase a product off the Internet.

 

[Andrew Fergus]

Re: Anyone Solve This - Production Scheduling Challenge- Sol

Hi GorD,

I'm a little confused by the information to accurately answer your question. The $10 costs per line, does that mean the second spare part incurs 3 x $10 costs (for B, C & D) or does it incur 2 x $10 (pink & green)? What is the running time for D? Also, the run time (r), is this the time to produce one unit through the line / process?

Whether you want to make money or product then it is best to do the scheduling at the constraint. The flow of product through the constraint will determine how much product (and money) you can make.

What is the constraint? It is the weakest link, the slowest process, the process with the least capacity, the process with most work piled up in front of it or the rate-limiting process (take your pick). If you can correctly schedule the constraint, then the rest is (relatively) easy. You want to put the most products through the constraint with the highest margin (per constraint unit - usually minutes).

It looks to me like the pink machine is a constraint (given it is used in both parts) - although I could be wrong. If you calculate the margin per minute through the pink machine, part 1 = $30 margin per 28 minutes (assuming r is 28 minutes / item) = $1.07 / minute (please excuse the lack of the pound symbol), Part 2 = $10 margin per 6 minutes (assuming 2 x $10 costs, 3 x $10 is nonsensical) = $1.67 / minute, main product = $20 margin ($60 - $10 for part 1 and $20 for part 2 and $10 for process E) per 34 minutes = $0.59 / minute.

My gut feel on this is to sell none of part 1, and you should be flat out making as much of part 2 as possible. You should sell equal volumes of part 2 and the main product (really you want to sell all of part 2 but given parts sales can not exceed the main product sales you should split it 50:50). Given station C is slower than station B, then the pink machine downtime can be used to make part 1 to allow assembly of the main product. This is the bit you need to schedule - balance the product flowing through stations A and C on the pink machine with a ratio of 1 : 2 (one part 1 for the main product and two part 2 - one for sale and the other for assembly).

I think you have your answer in theory - now to put it into practice! Although you might want to test these calculations / this theory on the green machine.

HTH, Andrew.

 

[GorD]

Re: Anyone Solve This - Production Scheduling Challenge- Sol

Hi Andrew, and thanks for the reply. I concur with almost everything you said. The best solution I/we have been able to come up with does indeed sell 87 main product and 87 of spare part 2. I think we have a decent solution done manuallly by trial and error. i was wondering if it can be modelled.

To answer your other questions

The £10 is the cost of the raw material that is needed to make either of the piece parts. So, to run stationA you need to buy material at £10 per part and the same for station B, the other stations just do further processing of the same material.

The run time for D is 12mins (don't know why that was missing) and it is indeed the time to produce one unit through that machine once it has been set up.

Again thanks for taking some time on this. It is a bit of a game that is doing the rounds at our work, which shows how difficult it is to schedule a line producing one product and spare parts. The best scores are around £1850 cash at the end of two weeks, but no one knows the best possible. i.e is £1850 near an optimal soloution or not.

 

[Andrew Fergus]

Re: Anyone Solve This - Production Scheduling Challenge- Sol

Hi GorD, pleased to be of assistance.

I haven't tried modelling this and I'm not sure if it can be modelled without using trial and error (as you have done) given the variables involved (although someone please correct me if I am wrong). I think the best solution, in the absence of a model, is to propose a theory and then test it. Given my first theory appears to be correct (to sell equal quantities of part 2 and the main product), following is a second theory to maximise the profit.

Given process C is slower than process B then you want to start by producing as much part 1 in Process A as possible while at the same time you let fly with Process B (otherwise process C will be constrained by Process B and will only run at 10 mins per unit instead of the rated 6 mins per unit). Once you have enough part 1 to produce the main product (say 88 or 89 or more if you want to beat $1,850), then change the pink machine to continually process part 2 in Process C. From there it is a matter of juggling processes B and D on the green machine to produce all of the product in time (you might try this with a change from B to D to clear some of the workload and then changing back to B with one last change back to D - see if this helps or hinders the total output) whilst at the same time never letting the pink machine stand idle. Process E should take the first half of the total planned production run that comes off Process D and the second half should be sold as parts.

To make more than $1,850 you need to push more product through the pink machine (if that is possible, forget about the efficiences of B and D - they have to be subservient to the pink machine & your overall objective). However, you might find that 87 is the optimal figure.

HTH, Andrew