Complicated

just2cusmile

New Member
Joined
Dec 9, 2005
Messages
4
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?
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
" 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,534
Messages
5,572,738
Members
412,482
Latest member
arooshrana2
Top