Managerial Decision Modeling w/ Spreadsheets, 3e (Balakrishnan/Render/Stair)
Chapter 3 Linear Programming Modeling Applications with Computer Analyses in Excel
3.1 Chapter Questions
1) In a multi-period production scheduling application, the decision variables typically designate:
A) the number of units to produce in a given period
B) minimizing inventory and production costs
C) the number of units held in inventory in a given period
D) the number of inventory units to produce in a given period
E) number of units to produce in a given period, along with the number of units to hold in inventory at
the end of each period
2) In a multi-period production scheduling application, which equation describes the relationship
between demand, production, and inventory?
A) productiont + demandt – inventoryt-1 = inventoryt
B) productiont + demandt + inventoryt-1 = inventoryt
C) productiont – demandt-1 + inventoryt-1 = inventoryt
D) productiont – demandt + inventoryt = inventoryt-1
E) productiont – demandt + inventoryt-1 = inventoryt
3) The objective function of a transportation problem is to:
A) maximize transportation cost
B) maximize supply quantities
C) minimize total demand
D) minimize total shipping cost
E) minimize production cost
4) In a multi-period production scheduling application, the objective function is to:
A) minimize production and inventory costs
B) minimize demand over the production horizon
C) maximize capacity over the production horizon
D) minimize capacity over the production horizon
E) maximize inventory
Use this information to answer the following questions.
Consider the following product mix problem and its associated spreadsheet model.
Max 3X1 + 3X2
Subject to:
2X1 + 3X2 ≤ 10 (constraint #1)
3X1 + 2X2 ≤ 20 (constraint #2)
X1 ≥ 5 (constraint #3)
X1, X2 ≥ 0 (non-negativity)
5) Refer to the spreadsheet above. What formula should be entered in cell D3 to compute total
profitability?
A) =SUMPRODUCT(B1:C1,B3:C3)
B) =SUM(B3:C3)
C) =B2*B3 + C2*C3
D) =SUMPRODUCT(B3:C3,E6:E8)
E) =B1*B3 + C1*C3
6) Refer to the spreadsheet above. Which cell(s) specifies the “changing cells” in Solver?
A) B1:C1
B) D6:D8
C) B3:C3
D) D3
E) B2:C2
7) Refer to the spreadsheet above. Which cell(s) designates the objective function as specified in
“Solver”?
A) E4
B) D3
C) D2
D) B1:C1
E) B2:C2
8) Refer to the spreadsheet above. Which equation should be entered in cell D8 to compute the
consumption of resource 3 (i.e., constraint #3)?
A) =SUMPRODUCT(B1:C1,B8:C8)
B) =SUMPRODUCT(B2:C2,B7:C7)
C) =SUMPRODUCT(B2:C2,B8:C8)
D) =SUMPRODUCT(D6:D8,E6:E8)
E) =SUMPRODUCT(B2:C2,E6:E8)
9) Refer to the spreadsheet above. Which equation should be entered in cell F6 to compute the unused
resources of constraint #1?
A) B2 – E6
B) D6 – E6
C) E6 – D6
D) E6 – B2
E) F6 – E6
10) Refer to the spreadsheet above. Which equation should be entered in cell F8 to compute the amount
by which the minimal requirement of constraint #3 has been exceeded?
A) =SUMPRODUCT(B2:C2,D3:E3)
B) D8 – E8
C) E8 – D8
D) F8 – E8
E) E8 – F8
11) The media selection problem can have which of the following objectives?
A) maximizing audience exposure
B) minimizing advertising costs
C) maximizing profit
D) A and B only
E) A, B, and C
12) The marketing research problem has which of the following objectives?
A) maximizing audience exposure
B) minimizing advertising costs
C) minimizing interview costs
D) maximizing profit
E) minimizing the total number surveyed
13) Which of the following statements regarding the Portfolio selection problem is FALSE?
A) Decision variables typically involve the amount to be invested in each investment choice.
B) Minimizing risk is the most common objective.
C) Alternate formulations are possible.
D) Maximizing return on investments subject to a set of risk constraint is a typical problem formulation.
E) Legal constraints can be introduced in addition to risk constraints .
14) Which of the following statements is FALSE regarding employee staffing applications?
A) Employee staffing problems can be formulated as assignment problems.
B) The goal of employee staffing problems is typically to determine how many employees need to start
their work at the different starting times permitted.
C) The employee staffing problem is a extension of the product mix problem.
D) It is possible to have multiple optimal solutions in employee staffing applications.
E) Based on the requirements and constraints, it is possible for an employee staffing problem to become
infeasible.
15) A company produces two products, X1, and X2. The constraint that illustrates the consumption of a
given resource in making the two products is given by: 2X1+3X2 ≤ 10. This relationship implies that
each unit of X1 consumes 3 units of that resource.
16) A company produces two products, X1, and X2. The constraint that illustrates the consumption of a
given resource in making the two products is given by: 3X1+5X2 ≤ 120. This relationship implies that
both products can consume more than 120 units of that resource.
17) Consider the following constraint: X1 ≥ 40. This implies that at most 40 units of product X1 can be
produced.
18) Consider the following constraint: X2 ≤ 30. This implies that at least 30 units of product X2 can be
produced.
19) The “Diet” problem can be classified as an “ingredient blending” linear programming application.
20) In a multi-period production scheduling application, the decision choices in later periods are directly
dependent on the decision made in earlier periods.
21) Production, inventory, and demand are typical decision variables in a production scheduling, multi-
period linear programming application.
22) The “Sinking Fund” problem can be classified as a multi-period linear programming application.
23) In a “Portfolio Selection” application, the objective function is always to minimize risk, given a set
of legal and policy constraints.
24) The objective function of the “Diet” problem is usually to maximize the food ingredient combination
to satisfy a stated nutritional requirement.
25) The “Transportation” problem involves determining the amount of goods to be transported from a
number of origins to a number of destinations.
26) Alternate optimal solutions are not common in linear programming applications.
27) The objective of transportation problems is typically to either minimize total shipping costs or
distances.
28) Diet and feed mix problems are special classes of the general product mix problem.
1) An investment company currently has 1 million dollars available for investment in five different
stocks. The company wants to maximize the interest earned over the next year. The five investment
possibilities along with the expected interest earned are shown below. To manage risk, the investment
firm wishes to have at least 35% of the investment in stocks A and B. Furthermore, no more than 15%
of the investment may be in stock E. Model and solve this problem in Excel.
Investment Expected Interest Earned (%)
Stock A 7
Stock B 9
Stock C 8
Stock D 10
Stock E 11
Use this information to answer the following questions.
The following table contains information on the cost to run four jobs (1, 2, 3, and 4) on four available
machines (A, B, C, and D).
Machine
A B C D
1 $12 $16 $14 $10
Job 2 $9 $8 $13 $7
3 $15 $12 $9 $11
4 $10 $13 $15 $16
2) Refer to the table above. Use Excel to model and solve this problem to determine the least costly job-
machine assignment.
3) Refer to the table above. Assume that job number 4 was canceled and will no longer be processed.
How will this affect the job-machine assignment? Use Excel to model and solve this problem to
determine the least costly assignment.
4) A production manager wants to determine how many units of each product to produce weekly to
maximize weekly profits. Production requirements for the products are shown in the following table.
Product Material 1 (lbs.) Material 2 (lbs.) Labor (hours)
A 3 2 4
B 1 4 2
C 5 none 3.5
Material 1 costs $7 a pound, material 2 costs $5 a pound, and labor costs $15 per hour. Product A sells
for $101 a unit, product B sells for $67 a unit, and product C sells for $97.50 a unit. Each week there
are 300 pounds of material 1; 400 pounds of material 2; and 200 hours of labor. Also, there is a weekly
demand of 10 units of product C each week. Use Excel to formulate and solve this problem.
Use this information to answer the following questions.
A beer distributor wants to prepare an aggregate plan for the next 6 weeks. Demand can be met using
regular time production, overtime production, or a combination of both as illustrated below:
Week
1 2 3 4 5 6
Demand (cases) 165 155 140 155 130 155
Capacity (cases)
Regular 150 150 150 150 150 150
Overtime 20 10 10 10 10 10
Cost per Unit
Regular time $50
Overtime $75
Inventory per week $5
5) Refer to the table above. Assume that beginning inventory is zero. Use Excel to formulate and solve
this multiperiod production problem to minimize total costs.
6) Refer to the table above. Assume that the beer distributor has the option of “subcontracting” beer
cases to supplement the existing capacity. Subcontracting can handle a maximum of 5 cases per week.
Subcontracting cost per case is $80. Also, due to an upcoming holiday season, the beer distributor wants
to maintain a safety stock of at least 5 cases per week. Use Excel to formulate and solve this
multiperiod production scheduling problem.
7) A company wants to determine how to allocate its $200,000 advertising budget to market a new
cereal. The company is considering newspaper ads, television ads, and radio ads. The following table
illustrates the cost of advertising in these different media and the exposure to new customers reached by
increasing the number of ads in each medium.
Media and Number of Ads No. of New Customers reached Cost per ad
Newspaper: 1-5 700 $500
Newspaper: 6-10 500 $400
Television: 1-10 9000 $5000
Television: 11-20 7500 $4000
Radio: 1-10 4000 $2000
Radio: 11-20 300 $1500
Use Excel to formulate and solve this problem to maximize audience exposure.
8) An insurance company has three secretaries, A, B, and C, who are each capable of processing four
different types of insurance claims. The amount of time required by each secretary to process a
particular type of a claim is summarized in the following table.
Processing Time in Hours
Claim Type
1 2 3 4
A 2 3 2 4
Secretary B 4 5 3 1
C 3 2 1 5
On a typical week, the insurance firm has 5 type 1 claims, 4 type 2 claims, 2 type 3 claims, and 3 type 4
claims. Each secretary works a maximum of 40 hours per week. The office manager wants to know how
many of each type of an insurance claim should be processed by each secretary to minimize the total
processing time. Use Excel to formulate and solve this problem.