Scribd
Upload
45 views2 pages

LP Using Excel Solver

This document provides a tutorial on using Excel Solver for linear programming, specifically for maximizing revenue from selling Apple and Pear pies. It outlines the objective function, constraints, and step-by-step instructions for setting up changing cells, the objective function cell, and constraints in Excel. Additionally, it explains how to configure the Solver tool to find the optimal solution and suggests further reading for practice.

Uploaded by

Thy Pho
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
45 views2 pages

LP Using Excel Solver

This document provides a tutorial on using Excel Solver for linear programming, specifically for maximizing revenue from selling Apple and Pear pies. It outlines the objective function, constraints, and step-by-step instructions for setting up changing cells, the objective function cell, and constraints in Excel. Additionally, it explains how to configure the Solver tool to find the optimal solution and suggests further reading for practice.

Uploaded by

Thy Pho
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

Tutorial 4 Liner Programming using Excel Solver Supplementary Note

Question 1 as an example
Objective function:
Z = $1.50A + $1.20P
Constraints:

Sugar 1.50A + 2.0P ≤ 1,200


Flour 3.0A + 3.0P ≤ 2,100
Time 6.0A + 3.0P ≤ 3,600
1. Define Changing Cells
Set B1 as the location for Apple pie A, and C1 for Pear pie P
Set Changing Cells values B2 0 and C2 0 (The initial value can be set at any value, 0 is
used here).

2. Set Objective Function Cell


Calculate the total revenue. The objective is to maximise the revenue.
Set the cells B3 $1.50 and C3 $1.20
Multiple the per unit revenue by the number of units of each pie type, and the equation
is: B2*B3 + C2*C3, which is calculated in Objective Function cell D4.

3. Set up Constraints
Set up row 6 with the two pie types A, P and mix
Set up three rows 7, 8 and 9 for constrains
In row 7, enter 1.50 for A and 1.20 for P, set cell D7 as = B2*B7 + C2*C7
In row 8, enter 3.0 for A and 3.0 for P, set cell D8 as = B2*B8+ C2*C8
In row 9, enter 6.0 for A and 3.0 for P, set cell D9 as = B2*B9 + C2*C9

In cells F7 to F9, enter the amount of constraints consistent with the constraint equations
In cells E7 to E9, enter the ≤ or ≥ symbols consistent with the constraint equations.
4. Set up Solver
Set the Target Cell: in D4,
Equal to: Min
By Changing Cells: B2: C2
Subject to Constraints: Click ‘Add’
D7 ≤ F7, D8 ≥ F8,
D9 ≤ F9
Click OK
Select a Solving Method: Simplex. Click ‘Solve’ as shown in the Screen shot 1 on Page2
Report: Hold down Shift key, and click ‘Answer, Sensitivity and Limits’, click OK as Screen
shot 2 on Page 2.

For further practice, please refer to pages 838 – 844 of the prescribed Textbook.

1
2

You might also like