Excel Solver Exercises

Exercise 01 Minimize Shipping Cost:

Locations L.A. St. Louis Boston
Denver $58 $47 $108
Houston $87 $46 $100
Atlanta $121 $30 $57
Miami $149 $66 $83
Seattle $62 $115 $164
Detroit $128 $28 $38

No. to ship from...

Store Number Needed
L.A. St. Louis
Denver 150
Houston 225
Atlanta 100
Miami 250
Seattle 120
Detroit 150
Total 995 0 0

Warehouse Inventory
Starting Inventory: 400 350
No. Remaining: 400 350

Shipping Costs: $ - $ -

Exercise 02 Minimize Production Cost:

Process Regular Exclusive Super
1 0.00 0 0
2 0.00 0 0.00
3 0.00 0 0.00
Steel Produced 0.00 0 0.00
 ≥ ≥ ≥
Req. Production (Tons) 100 150 150
Price $220.00 $200.00 $300.00
Minimum Rating 75 75 89

0 0 0
Linearized Rating ≥ ≥ ≥
0 0 0

Exercise 03 Maximize Profit of Products:

Products Units Profit/Unit Profit
Product A 25 $13 $325
Product B 25 $18 $450
Product C 25 $22 $550
Product D 5 $17 $85
Product E 5 $3 $15
Product F 5 $9 $45
Product G 5 $2 $10
Product H 5 $3 $13
Product I 5 $11 $55
Product J 4 $2 $8
Product K 4 $12 $48
Product L 3 $3 $9
Product M 2 $12 $24
Product N 5 $6 $30
Product O 23 $5 $115
Product P 3 $13 $39
Product Q 2 $11 $22
Total 151 $1,843

Exercise 04 Marketing Budget Allocation:

Current
Source Clicks CPC Total Cost
 Google 6000 15.37 $92,220
Facebook 9000 16 $144,000
MSN 4000 15.9 $63,600
WSJ 5000 12 $60,000
Yahoo 4000 17 $68,000
Twitter 7000 15.8 $110,600
Reddit 3500 14.3 $50,050
Instagram 1700 15.2 $25,840
WhatsApp 1900 10.5 $19,950
Snapchat 2100 11.8 $24,780
TikTok 4530 12.5 $56,625
Discord 700 7 $4,900
Total 49430 $720,565

Exercise 05 Schedule Optimization:

Number of Employees Monday Tuesday
0 Monday 1 1
0 Tuesday 0 1
0 Wednesday 0 0
0 Thursday 1 0
0 Friday 1 1
0 Saturday 1 1
0 Sunday 1 1

Total Number 0 0
>= >=
Number Needed 17 13

Objective 0

Exercise 06 Project Selection:

Objective $70,000,000
By Changing Cells Project No NPV Year 1
1 Project 1 $ 14,000,000.00 $ 12,000,000.00
0 Project 2 $ 17,000,000.00 $ 54,000,000.00
1 Project 3 $ 17,000,000.00 $ 6,000,000.00
1 Project 4 $ 15,000,000.00 $ 6,000,000.00
0 Project 5 $ 40,000,000.00 $ 32,000,000.00
1 Project 6 $ 12,000,000.00 $ 6,000,000.00
0 Project 7 $ 14,000,000.00 $ 48,000,000.00
0 Project 8 $ 10,000,000.00 $ 36,000,000.00
1 Project 9 $ 12,000,000.00 $ 18,000,000.00

Needed $ 48,000,000.00 $ 20,000,000.00

Available $ 50,000,000.00 $ 20,000,000.00

Exercise 07 Investment Portfolio Optimization Based on Total Yield:

Portfolio Amount: $5,000,000

Investment Pct Yield Amount Invested Yield

New Car Loans 6.90% $1,000,000.00 $69,000.00
Used Car Loans 8.25% $1,000,000.00 $82,500.00
Real Estate Loans 8.90% $1,000,000.00 $89,000.00
Unsecured Loans 13.00% $1,000,000.00 $130,000.00
Bank CDs 4.60% $1,000,000.00 $46,000.00
TOTAL $5,000,000.00 $416,500.00

Total Yield: 8.33% Auto Loans

Exercise 08 Assign Department:

Qualification
Worker Department 1 2
1 9 8
2 10 0
3 5 8
4 4 0
5 9 10
6 5 2
7 8 3
8 2 7
9 8 0
10 7 1
11 8 7
12 0 0
13 9 2
14 9 3
15 1 6
16 9 0
17 8 7
18 6 4
19 6 9
20 3 4
21 4 5
22 8 0
23 7 1
24 8 7
25 5 4
26 2 7
27 8 1
28 8 2
29 3 1
30 3 4
31 1 0
32 6 1
33 1 8
34 6 7
35 6 7
36 1 1
37 6 4
38 5 6
39 5 4
40 8 9
ises

Exercise 01 Minimize Shipping Cost: A company has wareho

Angeles, and Boston. Six retail outlets are situated all over t
retail outlets take orders from customers. The company the
of the warehouses. The company aims to supply all six retai
products they require from stock that is currently on hand in
shipping products to outlets, the company wants to keep sh
ip from... possible.
No. to be Shipped The first table shows the shipping costs from one port to an
Boston
table shows the product requirement for each port. After th
0 contains the information about the warehouse inventory. Yo
using the solver that will minimize the shipping costs from t
0 outlets.
0 Exercise 02 Minimize Production Cost: Mix different raw m
different types of steel, such as regular, exclusive, and super
0 about the availability and cost of these raw materials, as we
0 Additionally, establish the required amount of production, t
different classes of steels, and their minimum rating. The co
0 dataset.
0 0

y
500
500

$ - $ -

Usage Availability (Tons) Raw Cost Raw Rating

0.00 ≤ 130 $200.00 70
0.00 ≤ 200 $180.00 76
0.00 ≤ 180 $250.00 90
 Revenue $0.00
Production Cost $0.00
Profit $0.00

Exercise 03 Maximize Profit of Products: Profit per unit is given for 17 products. Your tas
find the maximum profit using the solver.
The combined production capacity is 300 units per day.
The company needs 50 units of Product A to fill an existing order.
The company needs 40 units of Product B to fill an expected order.
The market for Product C is relatively limited. So the company is not interested in produc
more than 40 units of this product per day. Additionally, the Product D to Q should be mo
equal to 15.
Exercise 04 Marketing Budget Allocation: Here, the current stats on the left, and the por
where you are going to use the solver is on the right. Your task is to maximize the convers
(value of cell J83). The budget should be higher or equal to sixty thousands and the total
budget and the total cost should be equal.

Solver
Conversion Rate Conversions Solver Budget Clicks Conversions
 2.46% 148 0 0 0.000
2.17% 195 0 0 0.000
2.13% 85 0 0 0.000
2.10% 105 0 0 0.000
2.57% 103 0 0 0.000
2.33% 163 0 0 0.000
1.99% 70 0 0 0.000
2.17% 37 0 0 0.000
2.20% 42 0 0 0.000
2.10% 44 0 0 0.000
2.01% 91 0 0 0.000
1.98% 14 0 0 0.000
1096 0 0 0.000

Wednesday Thursday Friday Saturday Sunday

1 1 1 0 0
1 1 1 1 0
1 1 1 1 1
0 1 1 1 1
0 0 1 1 1
1 0 0 1 1
1 1 0 0 1

0 0 0 0 0
>= >= >= >= >=
16 17 9 9 12
 Year 2
$ 3,000,000.00
$ 7,000,000.00
$ 6,000,000.00
$ 2,000,000.00
$ 35,000,000.00
$ 6,000,000.00
$ 4,000,000.00
$ 3,000,000.00
$ 3,000,000.00

Exercise 07 Investment Portfolio Optimization Based on Total

going to invest $5 million in various sectors. Find how this amo
the following conditions.
Firstly, the amount that the credit union will invest in new-car
times the amount that the credit union will invest in used-car l
used car loans are riskier investments.
Secondly, car loans should make up at least 15% of the portfoli
Thirdly, unsecured loans should make up no more than 25% of
Pct. of Portfolio Then, at least 10% of the portfolio should be in bank CDs.
Afterward, the total amount invested is $5,000,000.
20.00% Finally, all investments should be positive or zero.
Exercise 08 Assign Department: Need to assign 40 employees
20.00% head of each department has rated each employee’s competen
20.00% equals most competent). Each employee has rated his satisfacti
department (again on a 0 to 10 scale). Data is recorded in the P
20.00% going to assign between 8 to 12 people to each department. T
assign employees to workgroups to maximize total satisfaction
20.00% division has the required number of employees.
100.00%

40.00%

Qualification Satisfaction
3 4 1 2 3
6 8 9 2 7
5 6 9 6 7
10 5 7 7 7
5 2 9 1 0
4 5 9 8 8
7 2 9 1 0
1 5 9 8 8
9 3 2 8 1
6 2 7 8 2
1 2 8 3 1
6 3 4 3 4
1 8 4 1 5
6 6 9 0 9
1 2 5 2 3
5 4 7 0 7
2 7 1 1 2
8 4 8 8 6
3 5 5 7 8
3 0 5 7 2
3 2 9 7 1
8 3 9 4 9
4 6 8 5 7
6 3 2 1 4
7 5 8 9 2
9 0 5 7 6
3 4 2 7 0
3 0 8 4 7
1 1 9 8 8
1 10 5 3 8
9 0 8 3 10
6 2 6 6 1
7 6 8 6 5
0 1 1 1 9
2 6 9 8 6
3 1 5 7 4
4 2 5 7 5
6 9 10 4 9
3 10 4 5 4
4 5 8 0 7
8 0 7 1 4
 ost: A company has warehouses in St. Louis, Los
utlets are situated all over the United States. These
stomers. The company then ships products from one
y aims to supply all six retail locations with the
that is currently on hand in the warehouses. While
company wants to keep shipping charges as low as
g costs from one port to another. Then, the second
ment for each port. After that, there is a table that
he warehouse inventory. Your task is to find the values
ze the shipping costs from the warehouses to the
n Cost: Mix different raw materials to produce
egular, exclusive, and super quality steel. There is data
these raw materials, as well as their quality rating.
ed amount of production, the price per ton of
eir minimum rating. The conditions are given in the
en for 17 products. Your task is to

der.
rder.
is not interested in producing
roduct D to Q should be more or
ats on the left, and the portion
k is to maximize the conversions
ty thousands and the total solver
Exercise 05 Schedule Optimization: The number of workers
needed for a bank is provided. For example, 17 workers are
needed on Monday, 13 workers are needed on Tuesday, 15
workers are needed on Wednesday, and so on. All bank
employees work five consecutive days. What is the
minimum number of employees that this bank can have to
meet its labor requirement?
The condition is, Total Number of Employee >= Number
Needed is given in row 96 and 98.
Exercise 06 Project Selection: Use an excel solver to
determine which projects should be undertaken in excel.
Firstly, find the maximum value of NPV.
Secondly, the year 1 value should be <=50,000,000 and year
2 value should be <=20,000,000.
Thirdly, selecting a project means 1 and discarding means 0.
So use this.
ptimization Based on Total Yield: The credit union is
sectors. Find how this amount will be allocated using
nion will invest in new-car loans must be at least three
nion will invest in used-car loans. The reason is: that
nts.
at least 15% of the portfolio.
ke up no more than 25% of the portfolio.
hould be in bank CDs.
ed is $5,000,000.
ositive or zero.
ed to assign 40 employees to four Departments. The
each employee’s competence on a 0 to 10 scale (10
loyee has rated his satisfaction with each job
e). Data is recorded in the Problem worksheet. You are
ople to each department. The problem is how you can
maximize total satisfaction and ensure that each
f employees.

4 Qualification Satisfaction
7 #N/A #N/A Department Assigned
4 #N/A #N/A 1 0 1
3 #N/A #N/A 2 0 1
3 #N/A #N/A 3 0 1
3 #N/A #N/A 4 0 1
3 #N/A #N/A
3 #N/A #N/A Total Satisfaction #N/A
5 #N/A #N/A
2 #N/A #N/A
6 #N/A #N/A
1 #N/A #N/A
4 #N/A #N/A
3 #N/A #N/A
1 #N/A #N/A
8 #N/A #N/A
10 #N/A #N/A
8 #N/A #N/A
8 #N/A #N/A
4 #N/A #N/A
1 #N/A #N/A
8 #N/A #N/A
5 #N/A #N/A
9 #N/A #N/A
5 #N/A #N/A
5 #N/A #N/A
2 #N/A #N/A
8 #N/A #N/A
4 #N/A #N/A
5 #N/A #N/A
9 #N/A #N/A
1 #N/A #N/A
9 #N/A #N/A
5 #N/A #N/A
1 #N/A #N/A
5 #N/A #N/A
10 #N/A #N/A
8 #N/A #N/A
8 #N/A #N/A
8 #N/A #N/A
8 #N/A #N/A
Load These Settings to Solver

Exercise 01
0
0
1
1
32767
0
Exercise 02
0
0
1
1
1
32767
0
Exercise 03
0
0
FALSE
FALSE
TRUE
0
FALSE
32767
0
Exercise 04
0
0
0
TRUE
32767
0
Exercise 05
0
0
1
1
32767
0
Exercise 06
0
0
1
1
32767
0

Exercise 07
0
0
TRUE
TRUE
TRUE
FALSE
FALSE
32767
0

Exercise 08
0
0
1
1
0
1
32767
0
 Excel Solver Exercises

Exercise 01 Minimize Shipping Cost:

Locations L.A. St. Louis Boston
Denver $58 $47 $108
Houston $87 $46 $100
Atlanta $121 $30 $57
Miami $149 $66 $83
Seattle $62 $115 $164
Detroit $128 $28 $38

No. to ship from...

Store Number Needed
L.A. St. Louis
Denver 150 150 0
Houston 225 0 225
Atlanta 100 0 100
Miami 250 0 25
Seattle 120 120 0
Detroit 150 0 0
Total 995 270 350

Warehouse Inventory
Starting Inventory: 400 350
No. Remaining: 130 0

Shipping Costs: $ 16,140 $ 15,000

Exercise 02 Minimize Production Cost:

Process Regular Exclusive Super
1 30.00 25 0
2 64.29 125 10.71
3 5.71 0 139.29
Steel Produced 100.00 150 150.00
 ≥ ≥ ≥
Req. Production (Tons) 100 150 150
Price $220.00 $200.00 $300.00
Minimum Rating 75 75 89

7500 11250 13350

Linearized Rating ≥ ≥ ≥
7500 11250 13350

Exercise 03 Maximize Profit of Products:

Products Units Profit/Unit Profit
Product A 50 $13 $650
Product B 100 $18 $1,800
Product C 40 $22 $880
Product D 15 $17 $255
Product E 15 $3 $45
Product F 15 $9 $135
Product G 15 $2 $30
Product H 15 $3 $38
Product I 15 $11 $165
Product J 15 $2 $30
Product K 15 $12 $180
Product L 15 $3 $45
Product M 15 $12 $180
Product N 15 $6 $90
Product O 15 $5 $75
Product P 15 $13 $195
Product Q 15 $11 $165
Total 400 $4,958

Exercise 04 Marketing Budget Allocation:

Current
Source Clicks CPC Total Cost
 Google 6000 15.37 $92,220
Facebook 9000 16 $144,000
MSN 4000 15.9 $63,600
WSJ 5000 12 $60,000
Yahoo 4000 17 $68,000
Twitter 7000 15.8 $110,600
Reddit 3500 14.3 $50,050
Instagram 1700 15.2 $25,840
WhatsApp 1900 10.5 $19,950
Snapchat 2100 11.8 $24,780
TikTok 4530 12.5 $56,625
Discord 700 7 $4,900
Total 49430 $720,565

Exercise 05 Schedule Optimization:

Number of Employees Monday Tuesday
5 Monday 1 1
3 Tuesday 0 1
0 Wednesday 0 0
4 Thursday 1 0
0 Friday 1 1
3 Saturday 1 1
5 Sunday 1 1

Total Number 17 16
>= >=
Number Needed 17 13

Objective 20

Exercise 06 Project Selection:

Objective $70,000,000
 By Changing Cells Project No NPV Year 1
1 Project 1 $ 14,000,000.00 $ 12,000,000.00
0 Project 2 $ 17,000,000.00 $ 54,000,000.00
1 Project 3 $ 17,000,000.00 $ 6,000,000.00
1 Project 4 $ 15,000,000.00 $ 6,000,000.00
0 Project 5 $ 40,000,000.00 $ 32,000,000.00
1 Project 6 $ 12,000,000.00 $ 6,000,000.00
0 Project 7 $ 14,000,000.00 $ 48,000,000.00
0 Project 8 $ 10,000,000.00 $ 36,000,000.00
1 Project 9 $ 12,000,000.00 $ 18,000,000.00

Needed $ 48,000,000.00 $ 20,000,000.00

Available $ 50,000,000.00 $ 20,000,000.00

Exercise 07 Investment Portfolio Optimization Based on Total Yield:

Portfolio Amount: $5,000,000

Investment Pct Yield Amount Invested Yield

New Car Loans 6.90% $173,076.93 $11,942.31
Used Car Loans 8.25% $576,923.07 $47,596.15
Real Estate Loans 8.90% $2,500,000.05 $222,500.00
Unsecured Loans 13.00% $1,249,999.96 $162,499.99
Bank CDs 4.60% $499,999.99 $23,000.00
TOTAL $5,000,000.00 $467,538.46

Total Yield: 9.35% Auto Loans

Exercise 08 Assign Department:

Qualification
Worker Department 1 2
1 1 9 8
2 1 10 0
3 2 5 8
4 1 4 0
5 1 9 10
6 1 5 2
7 1 8 3
8 2 2 7
9 2 8 0
10 1 7 1
11 3 8 7
12 3 0 0
13 3 9 2
14 1 9 3
15 4 1 6
16 4 9 0
17 4 8 7
18 3 6 4
19 2 6 9
20 1 3 4
21 3 4 5
22 1 8 0
23 4 7 1
24 2 8 7
25 2 5 4
26 2 2 7
27 1 8 1
28 2 8 2
29 3 3 1
30 3 3 4
31 2 1 0
32 4 6 1
33 3 1 8
34 1 6 7
35 2 6 7
36 4 1 1
37 3 6 4
38 4 5 6
39 4 5 4
40 4 8 9
 Exercise 01 Minimize Shipping Cost: A company has wareh
situated all over the United States. These retail outlets take
one of the warehouses. The company aims to supply all six
currently on hand in the warehouses. While shipping produ
low as possible.
The first table shows the shipping costs from one port to an
each port. After that, there is a table that contains the infor
values using the solver that will minimize the shipping costs
Solution: Solver Parameters are as follows
Set Objective: G27
hip from... No. to be To: Min
Shipped By Changing Variable Cells: D15:F20
Boston Subject to the Constraints: C15:C20=G15:G20, D25:F25>=0
0 150 Select a Solving Method: Simplex LP
Exercise 02 Minimize Production Cost: Mix different raw m
0 225 exclusive, and super quality steel. There is data about the a
0 100 rating. Additionally, establish the required amount of produ
minimum rating. The conditions are given in the dataset.
225 250 Solution: Solver Parameters are as follows
Set Objective: I38
0 120 To: Min
150 150 By Changing Variable Cells: C32:E34
Subject to the Constraints: C35:E35>=C37:E37, C37:E37>=C
375 995 Select a Solving Method: Simplex LP

500
125

$ 24,375 $55,515

Usage Availability (Tons) Raw Cost Raw Rating

55.00 ≤ 130 $200.00 70
200.00 ≤ 200 $180.00 76
145.00 ≤ 180 $250.00 90
 Revenue $97,000.00
Production Cost $83,250.00
Profit $13,750.00

Exercise 03 Maximize Profit of Products: Profit

using the solver.
The combined production capacity is 300 units
The company needs 50 units of Product A to fill
The company needs 40 units of Product B to fill
The market for Product C is relatively limited. S
product per day. Additionally, the Product D to
Solution: Solver Parameters are as follows
Set Objective: E65
To: Max
By Changing Variable Cells: C48:C64
Subject to the Constraints: C48<=50, C49>=40,
Select a Solving Method: Simplex LP
Exercise 04 Marketing Budget Allocation: Here
the solver is on the right. Your task is to maximi
to sixty thousands and the total solver budget a
Solution: Solver Parameters are as follows
Set Objective: J83
To: Max
By Changing Variable Cells: H71:H82
Subject to the Constraints: H71:H82>=60000, H
Select a Solving Method: GRG Nonlinear

Solver
Conversion Rate Conversions Solver Budget Clicks Conversions
 2.46% 148 60000 3903.7085231 96.031
2.17% 195 60000 3750 81.375
2.13% 85 60000 3773.58490566 80.377
2.10% 105 60000 5000 105.000
2.57% 103 60000 3529.41176471 90.706
2.33% 163 60000 3797.46835443 88.481
1.99% 70 60000 4195.8041958 83.497
2.17% 37 60000 3947.36842105 85.658
2.20% 42 60000 5714.28571429 125.714
2.10% 44 60000 5084.74576271 106.780
2.01% 91 60000 4800 96.480
1.98% 14 60565.0000007275 8652.14285725 171.312
1096 720565.000000728 56148.520499 1211.411

Wednesday Thursday Friday Saturday Sunday

1 1 1 0 0
1 1 1 1 0
Exercis
1 1 1 1 1 needed
0 1 1 1 1 employ
labor re
0 0 1 1 1 The con
Solutio
1 0 0 1 1 Set Obj
1 1 0 0 1 To: Min
By Cha
Subject
Select a
16 17 12 10 12 Exercis
>= >= >= >= >= Firstly,
Second
16 17 9 9 12 Thirdly,
Solutio
Set Obj
To: Ma
By Cha
Subject
Select a
 Year 2
$ 3,000,000.00
$ 7,000,000.00
$ 6,000,000.00
$ 2,000,000.00
$ 35,000,000.00
$ 6,000,000.00
$ 4,000,000.00
$ 3,000,000.00 Exercise 07 Investment Portfolio Optimizati
$ 3,000,000.00 allocated using the following conditions.
Firstly, the amount that the credit union will
reason is: that used car loans are riskier inve
Secondly, car loans should make up at least 1
Thirdly, unsecured loans should make up no
Then, at least 10% of the portfolio should be
Afterward, the total amount invested is $5,0
Finally, all investments should be positive or
Solution: Solver Parameters are as follows
Set Objective: C132
To: Max
By Changing Variable Cells: D125:D129
Subject to the Constraints: D125>=D126*3, D
Select a Solving Method: GRG Nonlinear
Exercise 08 Assign Department: Need to ass
Pct. of Portfolio 10 scale (10 equals most competent). Each e
3.46% worksheet. You are going to assign between
satisfaction and ensure that each division ha
11.54% Solution: Solver Parameters are as follows
Set Objective: P146
50.00% To: Max
25.00% By Changing Variable Cells: C140:C179
Subject to the Constraints: C140:C179<=4, C
10.00% Select a Solving Method: Evolutionary
100.00%

15.00%

Qualification Satisfaction
3 4 1 2 3 4
6 8 9 2 7 7
5 6 9 6 7 4
10 5 7 7 7 3
5 2 9 1 0 3
4 5 9 8 8 3
7 2 9 1 0 3
1 5 9 8 8 3
9 3 2 8 1 5
6 2 7 8 2 2
1 2 8 3 1 6
6 3 4 3 4 1
1 8 4 1 5 4
6 6 9 0 9 3
1 2 5 2 3 1
5 4 7 0 7 8
2 7 1 1 2 10
8 4 8 8 6 8
3 5 5 7 8 8
3 0 5 7 2 4
3 2 9 7 1 1
8 3 9 4 9 8
4 6 8 5 7 5
6 3 2 1 4 9
7 5 8 9 2 5
9 0 5 7 6 5
3 4 2 7 0 2
3 0 8 4 7 8
1 1 9 8 8 4
1 10 5 3 8 5
9 0 8 3 10 9
6 2 6 6 1 1
7 6 8 6 5 9
0 1 1 1 9 5
2 6 9 8 6 1
3 1 5 7 4 5
4 2 5 7 5 10
6 9 10 4 9 8
3 10 4 5 4 8
4 5 8 0 7 8
8 0 7 1 4 8
t: A company has warehouses in St. Louis, Los Angeles, and Boston. Six retail outlets are
These retail outlets take orders from customers. The company then ships products from
ny aims to supply all six retail locations with the products they require from stock that is
es. While shipping products to outlets, the company wants to keep shipping charges as
osts from one port to another. Then, the second table shows the product requirement for
e that contains the information about the warehouse inventory. Your task is to find the
nimize the shipping costs from the warehouses to the outlets.
follows

0
=G15:G20, D25:F25>=0
P
ost: Mix different raw materials to produce different types of steel, such as regular,
here is data about the availability and cost of these raw materials, as well as their quality
quired amount of production, the price per ton of different classes of steels, and their
e given in the dataset.
follows

4
>=C37:E37, C37:E37>=C43:E43,F32:F34<=H32:H34
P
Profit of Products: Profit per unit is given for 17 products. Your task is to find the maximum profit
on capacity is 300 units per day.
units of Product A to fill an existing order.
units of Product B to fill an expected order.
C is relatively limited. So the company is not interested in producing more than 40 units of this
onally, the Product D to Q should be more or equal to 15.
eters are as follows

ells: C48:C64
nts: C48<=50, C49>=40, C50<=40, C51:C64>=15,C65=400
d: Simplex LP
Budget Allocation: Here, the current stats on the left, and the portion where you are going to use
ht. Your task is to maximize the conversions (value of cell J83). The budget should be higher or equal
the total solver budget and the total cost should be equal.
eters are as follows

ells: H71:H82
nts: H71:H82>=60000, H83=E83
d: GRG Nonlinear
Exercise 05 Schedule Optimization: The number of workers needed for a bank is provided. For example, 17 wo
needed on Monday, 13 workers are needed on Tuesday, 15 workers are needed on Wednesday, and so on. All
employees work five consecutive days. What is the minimum number of employees that this bank can have to
labor requirement?
The condition is, Total Number of Employee >= Number Needed is given in row 96 and 98.
Solution: Solver Parameters are as follows
Set Objective: C100
To: Min
By Changing Variable Cells: B88:B94
Subject to the Constraints: B88:B94=integer, D96:J96>=D98:J98
Select a Solving Method: Simplex LP
Exercise 06 Project Selection: Use an excel solver to determine which projects should be undertaken in excel.
Firstly, find the maximum value of NPV.
Secondly, the year 1 value should be <=50,000,000 and year 2 value should be <=20,000,000.
Thirdly, selecting a project means 1 and discarding means 0. So use this.
Solution: Solver Parameters are as follows
Set Objective: C104
To: Max
By Changing Variable Cells: B107:B115
Subject to the Constraints: B107:B115=binary, C117:D117<=C118:D118
Select a Solving Method: Simplex LP
 ent Portfolio Optimization Based on Total Yield: The credit union is going to invest $5 million in various sectors. Find how this a
ollowing conditions.
hat the credit union will invest in new-car loans must be at least three times the amount that the credit union will invest in used
car loans are riskier investments.
should make up at least 15% of the portfolio.
oans should make up no more than 25% of the portfolio.
f the portfolio should be in bank CDs.
amount invested is $5,000,000.
nts should be positive or zero.
ameters are as follows

e Cells: D125:D129
raints: D125>=D126*3, D130=C122, F128<=0.25, F129>=0.1, F132>=0.15
hod: GRG Nonlinear
Department: Need to assign 40 employees to four Departments. The head of each department has rated each employee’s comp
most competent). Each employee has rated his satisfaction with each job department (again on a 0 to 10 scale). Data is recorded
going to assign between 8 to 12 people to each department. The problem is how you can assign employees to workgroups to m
ure that each division has the required number of employees.
ameters are as follows

e Cells: C140:C179
raints: C140:C179<=4, C140:C179=integer, C140:C179>=1, Q141:Q144=0
hod: Evolutionary

Qualification Satisfaction
9 9 Department Assigned
10 9 1 12 0
8 7 2 10 0
4 9 3 9 0
9 9 4 9 0
5 9
8 9 Total Satisfaction 324
7 8
0 8
7 8
6 4
1 5
6 9
9 5
4 8
7 10
4 8
3 8
9 7
3 9
8 9
8 8
3 9
7 9
4 7
7 7
8 8
2 8
1 8
9 10
0 6
6 9
0 9
6 9
7 7
2 10
6 9
10 8
5 8
0 8
provided. For example, 17 workers are
n Wednesday, and so on. All bank
es that this bank can have to meet its
6 and 98.

ould be undertaken in excel.

20,000,000.
 rious sectors. Find how this amount will be

redit union will invest in used-car loans. The

rated each employee’s competence on a 0 to

to 10 scale). Data is recorded in the Problem
mployees to workgroups to maximize total