Complicated

[just2cusmile]

Your company makes three products: x, y, and z that sell for $105, $132, and $123 respectively. Producing a unit of product x requires 1.2 hours of casting, 1.7 hours of machining, and 0.3 hours of assembly. Producing a unit of product y requires 3.2 hours of casting, 1.5 hours of machining, and 0.3 hours of assembly. Producing a unit of product z requires 3.0 hours of casting, 1.2 hours of machining, and 0.3 hours of assembly. Product x costs $35 per unit. Product y costs $44 per unit. Product z costs $41 per unit. Your share holders insist that no one product should make up over half of the total production and that each product must make up at least ten percent of the production. You are given a total casting budget of 700 hours, a total machining budget of 500 hours, and a total assembly budget of 100 hours. How many units of each product should you produce in order to gain the most profit?

How do I set this up in excel?

 

[XLGibbs]

" How many units of each product should you produce in order to gain the most profit? "

I think you mean how many would you have to sell to break even. "Most Profit" is pretty open ended, as it is directly related to units sold after the breakeven point.

What do you have so far? it is much easier to get help fixing your what you have tried.

Also, you have not indicated why the production time is relevant...is the cost per unit the fixed cost? If so, what is the variable cost per hour of production time.

If it is merely an issue of maximizing per unit profit based on the limited production and assemply times, then you would produce the unit with the lowest selling price, lowest cost, and lowest production time...from a pure volume stanpoint.

Your formula would have to be:

(700 / casting time) = max units casted
+ (500/machining time) = max units machined
+ (100/assembly time) = max assembled

You would use the max assembled number as these would be the only sellable items. That number of units times the per unit profit of $70,$88,$82 respectively would give you most profitable answer.

But you can produce nearly twice as many unit x for a profit of $70 per unit.....but no matter how many units you produce, the maximum units assembled is always 3000. So it looks like product x is the logical winner.

 

[XLGibbs]

Proof is in the pudding.

Book1
	A	B	C	D	E	F	G	H
1		Cast	Machine	Assembled		Price	Cost	Profit per
2	x	1.2	1.7	0.3		105	35	70
3	y	3.2	1.5	0.3		132	44	88
4	z	3	1.2	0.3		123	41	82
5	Max Units				Max Potential	Max profit
6	x	583	294	333	333		23333	Highest
7	y	219	333	333	219		19250	Mid
8	y	233	417	333	233		19133	Lowest
9
10	Budget	700	500	100
Sheet1