Excel & Solver:

Hands-On Modeling Practice

Exercises

EXCEL REVIEW
2001-2002
 The Excel modeling problems here
are similar to problems you’re
likely to encounter in
Fuqua’s Decision Models course.

You can create Excel models on your own

to solve these problems, or use the
notes and tips included here as a guide.

Also see the

accompanying Excel workbook
named MorePractice.xls
available online at this URL:

http://faculty.fuqua.duke.edu/~pecklund/excelreview/ExcelReview.htm
Contents

Page

Blue Ridge Hot Tubs...................................................................... 1

Wood Walker .................................................................................. 5

Electro-Poly Corporation .............................................................. 8

This page intentionally blank.
Blue Ridge Hot Tubs

The Problem
Blue Ridge Hot Tubs, Inc. sells two models of hot tubs: The Aqua-Spa and the
Hydro-Lux. The company purchases prefabricated fiberglass hot tub shells and
installs a common water pump and the appropriate amount of tubing into each
hot tub. The number of hours it takes to install each model, the tubing required,
and the profit for each of the two models is described in the table below.

Hot Tub Installation Tubing Profit

Model Labor Hours Required
Aqua Spa 9 12 $350
Hydro-Lux 6 16 $300

The company expects to have 200 pumps, 1,566 hours of labor, and 2,880 feet of
tubing available during the next production cycle. The company can sell all the
hot tubs it makes.

Create a spreadsheet model to determine the optimal number of Aqua-Spa and

Hydro-Lux hot tubs to produce in order to maximize profits.

Modeling notes and tips follow.

1
Blue Ridge Hot Tub Modeling Notes & Tips

I. Arrange the Data in the Spreadsheet

(See the “Blue Ridge Basic” Worksheet)

Below is an illustration of the data we know from the problem arranged in a logical
way, with:
•= space for the decision variables (number to make; here zeros), and
•= a label for information we want to know (e.g.- maximum total profit).

Note that the constraint information (which is very important) is part of this
worksheet.

Blue Ridge Hot Tubs

Total Profit:

Aqua-Spa Hydro-Lux
Number to make: 0 0
Unit profit: $350 $300

Constraints: Used: Available:

Pumps required: 1 1 200
Labor required: 9 6 1,566
Tubing required: 12 16 2,880

II. Add Formulas to the Model

(See the “Blue Ridge Formulas” Worksheet.)

Remember to use cell references (not typed-in values) when creating formulas in
order to keep the numbers in the model easy-to-change.

Total Profit: =B7*B8+C7*C8

Aqua-Spa Hydro-Lux
Number to make: 0 0
Unit profit: 350 300

Constraints: Used: Available:

Pumps required: 1 1 =$B$7*B11+$C$7*C11 200
Labor required: 9 6 =$B$7*B12+$C$7*C12 1566
Tubing required: 12 16 =$B$7*B13+$C$7*C13 2880
Total Profit

2

 Figure out the total profit formula and put it in place.

 Number to make X unit profit for each model, with the results added
together.

Constraints

 Figure out formulas to represent the constraints.

 These formulas go in the “Used” column, for pumps, labor, & tubing.

 Note: Create the first formula (for pumps) and copy it down the
column (but be sure to use absolute addressing to refer to number-to-
make).

III. Try Solving the Problem Manually

Try entering values for #-to-make, maximizing total profit but not violating
constraints. Not easy!

IV. Use Solver to Find the Best Solution

(See the “Blue Ridge Solver” worksheet.)

Set up the Solver and let it find the maximum Total Profit.

Information required to define the problem in Solver includes:

•= Target call: Total Profit, maximize

•= Changing cells: Decision variables; number to make of each tub model

•= Constraint cells: Available pumps, labor, tubing. That is, each of these values
must be <= the number of these items available. (Note that if these cells are
arranged contiguously on the worksheet, they can be defined all at
once...that is, D11:D13 <=E11:E13, instead of one at a time.) Plus, take into
account what are called “lower bounds” on the decision variables...that is,
that neither number to make value can be less than zero.

•= Under Options: Select “Assume Linear Model”, because this is an Linear

Programming problem (an optimization problem with a linear objective
function and linear constraints). Solver uses a special, efficient algorithm
called the simplex method to solve this kind of problem. Leave other settings
at their defaults. (Note that the “Precision” option determines how much
rounding error is allowed in Solver’s solution... it could come up with
200.0000000906 pumps as an answer, for example.)

3
Blue Ridge Hot Tubs

Total Profit: $66,100

Aqua-Spa Hydro-Lux
Number to make: 122 78
Unit profit: $350 $300

Constraints: Used: Available:

Pumps required: 1 1 200 200
Labor required: 9 6 1,566 1,566
Tubing required: 12 16 2,712 2,880
The worksheet with Solver’s solution.

4
Wood Walker

The Problem
Wood Walker is a self-employed furniture maker. He makes three different
styles of tables: A, B, and C. Each model of table requires a certain amount of
time for the cutting of component parts, for assembling, and for painting. Wood
can sell all the units he makes. Model B may be sold without painting.

Use the data below to formulate a spreadsheet model that will help Wood
determine the product mix that will maximize his profit.

Time per Table (hours)

Model Cutting Assembling Painting Profit per Table
A 1 2 4 $35
B 2 4 4 $40
unpainted B 2 4 0 $20
C 3 7 5 $50
Capacity
(hours/month) 200 300 150

Modeling notes and tips follow.

5
Wood Walker Modeling Notes & Tips

I. The Basic Layout

(See the “Wood Walker Basics” worksheet)

The worksheet illustrated below shows the information given in the problem:
•= Givens: Cutting, assembling, painting time required per table.
•= Also given: Profit per table
•= Constraints: Available capacity, or the amount of time in hours/month for
each activity

The objective, the value to maximize, is Total Profit.

Wood Walker Furniture Model

------------------ Time per Table ------------------

Model # Made Cutting Assembling Painting Profit/Table

A 1 2 4 $35
B 2 4 4 $40
B unptd. 2 4 0 $20
C 3 7 5 $50
Capacity avail: 200 300 150
Capacity used:
Slack capacity:
Total profit:

Also included in this illustration (though the values aren’t yet filled in):
•= Number to make, or the “decision variables” that can be manipulated to try
to maximize total profit. (There is a constraint: “slack capacity” can never be
negative.)
•= Capacity used, which depends on the number of tables of each type made.
•= Slack capacity, calculated based on capacity available and capacity used.

II. Add Formulas to the Worksheet

(See the “Wood Walker Formulas” worksheet)

Capacity Used Formulas

for cutting, assembling, painting

Example: cutting capacity used

model A cutting time X model A number made
+ model B cutting time X model B number made
+ model B/U cutting time X model B/U number made

6
 + model C cutting time X model C number made
= total cutting capacity used

Use this same type of formula for assembling & painting.

Slack Capacity
for cutting, assembling, painting
Subtract capacity used from capacity available.

Total Profits
value to maximize, within constraints

For example:
model A profit/table X model A #made
+ model B profit/table X model B #made
+ model B/U profit/table X model B/U #made
+ model C profit/table X model C #made
= Total Profit

Constraint: Slack capacity numbers must be zero or positive.

III. “Solver Version”

(See the “Wood Walker Solver” worksheet.)

Note:
Set number format for cells B6:B9 and C11:E12 to zero (no decimals).

Solver setup:
Target cell: total profit; maximize.
Changing cells: number to make.
Constraints:
C11:E11 >= 0
B6:B9 >= 0

Solver’s solution: Total profit is $2,438 with these numbers...

Model # Made
A 38
B 0
B unptd. 56
C 0

7
Electro-Poly Corporation

The Problem
The Electro-Poly Corporation is the world’s leading manufacturer of slip rings.
A slip ring is an electrical coupling device that allows current to pass through a
spinning or rotating connection. The company recently received a $750,000 order
for various quantities of three types of slip rings. Each slip ring requires a certain
amount of time to wire and harness. This table summarizes the requirements for
the three models of slip rings:

Model 1 Model 2 Model 3

Number ordered: 3,000 2,000 900
Wiring/Unit (hours): 2 1.5 3
Harnessing/Unit (hours): 1 2 1

Unfortunately, Electro-Poly doesn’t have enough wiring and harnessing capacity

to fill the order by its due date. The company has only 10,000 hours or wiring
capacity and 5,000 hours of harnessing capacity available to devote to this order.
However, the company can subcontract any portion of the order. The unit costs
of producing each model in-house and buying the finished products from a
subcontractor are summarized below:

Model 1 Model 2 Model 3

Cost to make: $50 $83 $130
Cost to buy: $60 $97 $145

Determine the number of slip rings to make and the number to buy in order to
fill the customer order at the least possible cost.

Modeling notes and tips follow.

8
Electro-Poly Modeling Notes & Tips

I. The Basic Model

(See the “Electro-Poly Basic” worksheet.)

To begin constructing the basic model, review the information available.

Consider the decision variables (or changing cells) required:

 Number of model 1 slip rings to make in house.

 Number of model 1 slip rings to subcontract.

 Number of model 2... etc. as above

 Number of model 3... etc. as above

Consider the objective function (or target cell):

 Total cost of filling the order. To be minimized.

 Recall the cost of make vs. buy for each model slip ring.

 The mathematical expression of the cost of filling the order is:

$50*M1 + $83*M2 + $130*M3 + $61*B1 + $97*B2 + $145*B3

where M1=Make Model 1, M2=Make Model 2, etc. and B1=Buy

Model 1, B2=Buy Model 2, etc.

Consider the constraints:

1) The number made in-house can’t exceed available capacity for wiring
and harnessing.
In terms of the labor hour requirements:
For wiring: 2M1 + 1.5M2 + 3M3 <= the 10,000 hours avail.
For harnessing: 1M1 + 2M2 + 1M3 <= the 5,000 hours avail.
where 2M1 means 2 hours for Model 1, etc.

2) We have to come up with enough slip rings to fill the order.

That is:
For Model 1: Make1 + Buy1 = 3,000 (demand for Model 1)
For Model 2: M2 + B2 = 2,000 (demand for Model 2)
for Model 3: M3 + B3 = 900 (demand for Model 3)

3) Non-negativity conditions are also important here:

M1, M2, M3, B1, B2, B3 must all be >= 0.

Note that in this version:

•= The number to make and number to buy are entered as zeros, to start. These
are the changing cells.
•= The cost of make vs. buy is entered. This is given in the problem.
•= The number of each model needed is entered. Also given.
•= The hours required for wiring & harnessing are entered. Also given.

9
 •= The hours available for wiring & harnessing are entered. Given.
•= Space is made available for hours USED... which must be a formula.
•= Space is made available for TOTAL COST... which must be a formula.
•= A row is made available for #AVAILABLE... which must be a formula (and
indicates the sum of #made + #bought).

II. Formulas
(See the “Electro-Poly Formulas” worksheet)

Note: You might fill in some dummy or experimental values for # to make and #
to buy just to make sure your formulas are working properly.

Key cell formulas are:

1) #AVAILABLE row
Model 1 # to Make + Model 1 # to Buy (e.g.: B14 holds =B7+B8)
Same for Models 2 and 3.

2) Under “Hours required”, the “Used” column

Wiring hours used is: sumproduct of wiring hours required for
Models 1-3 multiplied by Number to Make for Models 1-3 .
=SUMPRODUCT(B18:D18,$B$7:$D$7)

Harnessing hours used is similar to the above. Note that if you

use absolute addressing to refer to #-to-Make ($B$7:$D$7) you can
copy the wiring formula for harnessing.

(Note that this sumproduct deals only with # to Make... since we

aren’t concerned with hours required, used, or available for the
ones we buy.)

3) Total Cost
Sumproduct of cost to make & buy for all three models and
number to make & buy for all three models. Or:
=SUMPRODUCT(B11:D12,B7:D8)

Sumproduct here is the equivalent of:

Cost to make M1 * # to make of M1
+ Cost to make M2 * # to make of M2
+ Cost to make M3 * # to make of M3
+ Cost to buy M1 * # to buy of M1
etc. ... through M3

...but Excel’s SUMPRODUCT formula provides a shorthand.

10
III. Using Solver
(See the “Electro-Poly Solver” Worksheet)

Target cell: Total cost; to be minimized (B3).

Changing cells: Number to make and number to buy (B7:D8)
Constraints: 1) Number available = number needed
(B14:D14 = B15:D15)
2) Non-negativity constraint on the number to
make for each model and number to buy for
each model. They must be >= 0
(B7:D8 >= 0)
3) Hours used for wiring and harnessing are <=
hours available
(E18:E19<=F18:F19)

Under Solver Options: Check Assume Linear Model

Note: You might want to format the numbers to make/buy cells with no decimal
places; Solver will otherwise return decimal #s in these cells, which don’t
make sense.

Solver’s solution is that the total cost is $433,000 and:

Number to Model 1 Model 2 Model 3

Make: 3000 2000 900
Buy: 0 0 0

11