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

Solving the question using Excel. This is a quite technical question so made a bit lengthy in Excel. I am attaching screenshots of Excel. First ones with values, later ones with formulas showing.

Steps for calculation are:-

1) Plot all the items given in question, like maximum amount of units, consumption per unit of items etc.

2) Now calculate items consumed for minimum demand. Find the balance items remaining.

3) Now calculate contribution per unit of different items based on consumption of materials in different items and items' respective contributions.

4) Find the item giving maximum contribution for each material. Now multiply this max contribution per material with maximum units of material remaining. we will get the material giving maximum contribution on whole.

5) We will produce maximum possible units of Item D045 based on this. Maximum units which can be produced will be minimum of units which can be produced with each of the material's maximum amount available. Here it is steel. As steel will be fully utilised, only Item A158 can be produced with remaining materials as Item A158 does not require steel.

6) Find the maximum units of Item A158 which can be produced with remaining materials.

7) multiply the number of units produced with Contribution per unit of all the items actually produced. You will get the total contribution.

8) If no minimum demand is to be met, all the materials will be fully available. Now repeat the above process with full materials available.