Optimizing profit

[Duodu]

A company has decided to produce the product in standard shipping batches. To cope with the new product, an adjustment to production needs to be made on the lines that will make it. The company has given you the following information

Product A Product B New Product
Profit (Objective) £500 £750 £1,100
Batches to make ? ? ?

Materials required per pallet type Available Units
Semi-skilled labor time 50 50 100 6000 Hours
Skilled labor time 5 15 10 800 Hours
Materials 500 400 300 31,000 Units

I want to use solver to
Design an optimizer on excel and use solver to find out how many batches of each product to make to maximize profit and
model a reduction in skilled labor hours by 100 to 700
Thanks

[Hydraulics]

Nice (and pretty standard) homework.

How about setting up an excel file, test it, and then come back if something is wrong?

HTH,

Francesco

[PKowalik]

Since it's pretty standard, providing an Excel file is not really necessary.


The objective function in E3 (total profit) is =SUMPRODUCT(B3:D3;B$2:D$2)
Copy it down to E5:E7 to obtain left-hand sides for constraints (total usage of labor and materials).

[AliGW]

@PKowalik

I think you missed the point. If we suspect that a problem is a homework exercise, we need to encourage the OP to have a go themselves first and not hand them a solution on a plate (from which they are likely to learn very little).

[PKowalik]

Perhaps I did. However, I've been teaching stuff like this problem for 30 years and I know how difficult it can be for beginners.

[AliGW]

So do I.