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?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,798
Messages
5,833,741
Members
430,228
Latest member
Normano

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top