Question

Unsure how to set this up properly in excel?

Assignment #6

The Nimble Digits Division of Block C Enterprises manufactures computer furniture and accessories.At the present time 15 different components are being produced.Each product is some combination of steel, plastic, wood, aluminum and formica.The availability of these component materials is 980 pounds of steel alloy, 400 sq ft of plastic, 600 bd ft of wood, 2500 pounds of aluminum, 1800 bd ft of Formica and labor is limited to 1000 hours.Given the data below, determine how many of each item should be produced to maximize the profit.Also determine how the profit would change if the monthly demand for all items with an a through e prefix is eliminated.

Item a158 requires 0.4 sq ft of plastic, 0.7 bd ft of wood, 5.8 pounds of aluminum, 10.9 bd ft of Formica and 3.1 hours of labor.There is no minimum monthly demand and this item's contribution to the profit is $18.79.

Item b179 requires 4 pounds of steel, 0.5 sq ft of plastic, 1.8 bd ft of wood, 10.3 pounds of aluminum, 2.0 bd ft of Formica and 1.0 hours of labor.The minimum monthly demand is 20 and this item's contribution to the profit is $6.31.

Item c023 requires 6 pounds of steel, 1.5 bd ft of wood, 1.1 pounds of aluminum, 2.3 bd ft of Formica and 1.2 hours of labor.The minimum monthly demand is 10 and this item's contribution to the profit is $8.19.

Item d045 requires 10 pounds of steel, 0.4 sq ft of plastic, 2.0 bd ft of wood, and 4.8 hours of labor.The minimum monthly demand is 10 and this item's contribution to the profit is $45.88.

Item e388 requires 12 pounds of steel, 1.2 sq ft of plastic, 1.2 bd ft of wood, 8.1 pounds of aluminum, 4.9 bd ft of Formica and 5.5 hours of labor.There is no minimum monthly demand and this item's contribution to the profit is $63.00.

Answer 1

Rough Sketch
		Item a	Item b	Item c	Item d	Item e
	Profit per unit	$18.79	$6.31	$8.19	$45.88	$63.00
									Total
	Resources	Resources used per unit					Total Used		Available
	Steel		4	6	10	12	32	≤	980
	Plastic	0.4	0.5		0.4	1.2	2.5	≤	400
	Wood	0.7	1.8	1.5	2	1.2	7.2	≤	600
	Aluminium	5.8	10.3	1.1		8.1	25.3	≤	2500
	Formica	10.9	2	2.3		4.9	20.1	≤	1800
	Labor	3.1	1	1.2	4.8	5.5	15.6	≤	1000
									Total Profit
	Number of unit	1	1	1	1	1			$142
Spreadsheet model
		Item a	Item b	Item c	Item d	Item e
	Profit per unit	$18.79	$6.31	$8.19	$45.88	$63.00
									Total
	Resources	Resources used per unit					Total Used		Available
	Steel		4	6	10	12	980	≤	980
	Plastic	0.4	0.5		0.4	1.2	139.6	≤	400
	Wood	0.7	1.8	1.5	2	1.2	239.4	≤	600
	Aluminium	5.8	10.3	1.1		8.1	1468.6	≤	2500
	Formica	10.9	2	2.3		4.9	1795.8	≤	1800
	Labor	3.1	1	1.2	4.8	5.5	828.8	≤	1000
									Total Profit
	Number of unit	132	20	10	12	60			$7,019
If there is no minimum monthly demand
		Item a	Item b	Item c	Item d	Item e
	Profit per unit	$18.79	$6.31	$8.19	$45.88	$63.00
									Total
	Resources	Resources used per unit					Total Used		Available
	Steel		4	6	10	12	980	≤	980
	Plastic	0.4	0.5		0.4	1.2	140.1	≤	400
	Wood	0.7	1.8	1.5	2	1.2	213.8	≤	600
	Aluminium	5.8	10.3	1.1		8.1	1320.9	≤	2500
	Formica	10.9	2	2.3		4.9	1794.3	≤	1800
	Labor	3.1	1	1.2	4.8	5.5	869.4	≤	1000
									Total Profit
	Number of unit	135	1	1	19	65			$7,518
As the most profitable product is item e, the company should produce more units of item e to maximise profit