Scribd
Upload
8 views4 pages

Solverlpp

The document provides step-by-step instructions for creating an Excel sheet to calculate the maximum profit from two products using Solver. It details how to set up a table with descriptions, units, and formulas for machine hours and profit. Finally, it guides the user on applying Solver to optimize the production quantities of Product X and Product Y for maximum profit.

Uploaded by

Aman Verma
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
8 views4 pages

Solverlpp

The document provides step-by-step instructions for creating an Excel sheet to calculate the maximum profit from two products using Solver. It details how to set up a table with descriptions, units, and formulas for machine hours and profit. Finally, it guides the user on applying Solver to optimize the production quantities of Product X and Product Y for maximum profit.

Uploaded by

Aman Verma
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

PART 1 — What you must type in Excel

(exact cells)
Open a new Excel sheet and follow these exact steps.

STEP 1 — Create this table first


Type exactly this:

Column A
A1 → Description
A2 → Units
A3 → Machine A hours
A4 → Machine B hours
A5 → Hours Available
A6 → Profit per unit

Column B and C
B1 → Product X
C1 → Product Y

B2 → (KEEP EMPTY – Solver will fill)


C2 → (KEEP EMPTY)

B3 → 2
C3 → 1

B4 → 1
C4 → 3

B5 → 60
C5 → 45

B6 → 300
C6 → 500

So your Excel screen should look like this:

A B C
1 Description Product X Product Y
2 Units (blank) (blank)
3 Machine A hrs 2 1
4 Machine B hrs 1 3
5 Hours Available 60 45
6 Profit per unit 300 500
PART 2 — Now create formulas
STEP 2: Machine A total hours used

Go to cell D3
Type:

= B3*$B$2 + C3*$C$2

(This means: 2x + 1y)

STEP 3: Machine B total hours used

Go to cell D4
Type:

= B4*$B$2 + C4*$C$2

(This means: x + 3y)

STEP 4: Profit formula

Go to cell D6
Type:

= B2*$B$6 + C2*$C$6

(This gives: 300x + 500y)

Your Excel should now look like this:

A B C D
1 Description Product X Product Y Formula Output
2 Units (blank) (blank)
3 Machine A hrs 2 1 =2x + 1y
4 Machine B hrs 1 3 =1x + 3y
5 Hours Available 60 45
6 Profit per unit 300 500 =300x + 500y

Where:

 D3 = formula for Machine A usage


 D4 = formula for Machine B usage
 D6 = total profit

PART 3 — Now apply Solver


Go to:

Data → Solver

If Solver is not there:

 File → Options → Add-ins → Excel Add-ins → Go → ✔ Solver Add-in

Now fill Solver like this:


Set Objective:

Select cell D6

→ Choose Max

By Changing Variables:

Select B2 and C2

Add Constraints:

Cell Condition Value


D3 ≤ 60
D4 ≤ 45
B2 ≥ 0
C2 ≥ 0

Solve → Simplex LP → Solve

FINAL ANSWER Excel will give you:


Product X = 27
Product Y = 6
Maximum Profit = 11100

You might also like