9) Consider the following data that illustrates demand, production costs, and expected capacity for a
given product.
Month
1 2 3 4
Demand (units) 100 125 200 175
Production cost/Unit $20 $25 $30 $35
Production Capacity (units) 150 150 150 150
Inventory carrying costs are estimated at $4 per unit per month. The firm wants to produce at least 50
units per month to uniformly utilize its existing resources. Assume that beginning inventory is zero. Use
Excel to formulate and solve this problem to determine how many units of the product should be
produced monthly to meet the expected demand at minimal cost.
Use this information to answer the following questions.
A company can ship its product from any of its three factories, F1, F2, and F3, to any of its retail outlets,
R1, R2, and R3. The capacity, demand, and shipping cost information is provided as follows:
Demand (units) Capacity (units)
R1: 300 F1: 250
R2: 500 F2: 350
R3: 200 F3: 400
Shipping Cost/unit ($)
R1 R2 R3
F1 1 3 2
F2 3 4 2
F3 2 2 3
10) Refer to the information above. The company wants to come up with an optimal shipping strategy to
minimize its total shipping cost. Use Excel to formulate and solve this problem.
11) Refer to the information above. Assume that demand for retail outlet three (R3) increases to 300
units. How will this affect the shipping cost? Use Excel to formulate and solve this problem
12) Refer to the information above. Assume that the road leading from factory F1 to retail outlet R1 is
currently closed due to construction. How will this affect the shipping cost? Use Excel to formulate and
solve this problem.
13) A company manufactures two types of lawn mowers: riding mowers and push mowers. The
company has the option of manufacturing the mowers in-house or outsourcing the entire operation. In-
house manufacturing requires the following four operations: production, assembly, electrical wiring, and
final inspection. The following table summarizes the weekly hours of processing time available and the
processing time required by each operation.
Hours Required per Mower
Riding Mower Push Mower Hours Available
Production 3 1.5 40
Assembly 5 2 30
Electrical Wiring 1 0.5 45
Final Inspection 0.5 0.25 40
The company has a weekly demand of 5 riding mowers and 3 push mowers. The company makes its
riding mowers in-house for $500 each and its push mower for $200 each. Alternatively, it can outsource
its riding and push mowers for $550 and $225 each, respectively. Use Excel to formulate and solve this
“make” or “buy” problem.
14) A factory operates 7 days a week. Due to labor union regulations, employees are allowed to work a
maximum of 5 consecutive days. The minimum number of employees that are needed on a given day is
provided as follows:
Day Number of Employees Needed
Sunday 20
Monday 30
Tuesday 35
Wednesday 40
Thursday 37
Friday 30
Saturday 25
Use Excel to formulate and solve this labor planning problem to determine the minimum number of
employees needed each day.
15) In a small suburban town, firefighters work 8-hour shifts. Assume there are six shifts each day that
are divided into six 4-hour periods. The minimum number of firefighters needed on each shift is
illustrated below.
Shift Number of Firefighters Needed
Midnight-4 a.m. 5
4 a.m.-8 a.m. 6
8 a.m.-Noon 10
Noon-4 p.m. 12
4 p.m.-8 p.m. 8
8 p.m.-Midnight 5
Firefighters must report to work at the beginning of the above time periods and must work eight
consecutive hours. Use Excel to formulate and solve this labor planning problem to determine the
minimum number of firefighters needed on each shift.
16) A furniture manufacturer produces three products, desks, tables, and chairs. These products can be
produced during either the morning shift or the evening shift. The morning shift has a labor cost of $20
per hour with a maximum availability of 20,000 hours. The evening shift has a labor cost of $25 per
hour and is limited to 15,000 hours. The labor, lumber, and demand requirements are given as follows:
Resource Desk Table Chair
Labor (hours) 0.75 2 0.5
Lumber (pounds) 7 15 4
Nails (pounds) 0.5 2 0.25
Demand: 50 75 100
Assume that there are 20,000 pounds of lumber and 20,000 pounds of nails available for both shifts.
Use Excel to formulate and solve this problem to minimize total labor cost.
17) A manufacturer of 19″ LCD monitors encounters the following quarterly production cost, demand,
and production capacity for its monitors:
Quarter
1 2 3 4
Production Cost/LCD $290 $275 $265 $255
Demand 500 550 600 550
Maximum Production Capacity 600 700 600 700
To maintain a stable workforce, the manufacturer wants to produce at least 500 units each quarter. To
meet unexpected fluctuation in demand, the company wants to hold at least 100 monitors in stock each
quarter. The cost of carrying a monitor in stock is $5 each quarter. Assume that beginning inventory is
zero. Use Excel to formulate and solve this problem to determine to minimize production and inventory
costs.
18) A real estate developer is planning to build an office complex. Currently, there are three office sizes
under consideration: small, medium, and large. Small offices can be rented for $600 per month, medium
offices can be rented for $750 per month, and large offices can be rented for $1000 per month. Each
small office requires 600 square feet, each medium office requires 800 square feet, and each large office
requires 1000 square feet. The current plot of land available to the developer is 100,000 square feet.
The developer wants to ensure that the office complex has at least 3 units of each office size. Use Excel
to formulate and solve this problem to maximize total revenue.
19) Speedy Lube has 4 bays in its shop and 3 cars waiting for an oil change. The estimated time (in
minutes) to change the oil and filter for each car in each bay is given as follows:
CarBay A B C
1 10 12 9
2 15 13 12
3 14 12 10
4 13 11 12
Which car should be assigned to which bay if Speedy Lube wants to minimize its total service time?
Use Excel to formulate and solve this problem.
20) A company is considering various advertising media to promote its new toy. Pertinent
information regarding potential customers reached and costs per advertisement is given below:
No. of Customers Maximum Times Cost per
Media Reached Available per Month Advertisement
Daytime TV 1000 10 $2000
Evening TV 3000 8 $3500
Daytime Radio 500 15 $1000
Evening radio 750 15 $1500
Newspaper 900 4 $500
Currently, the company has a monthly advertising budget of $150,000. The marketing department has
imposed the following restrictions:
• No more than $20,000 may be spent on radio advertising each month.
• At least 5 TV ads must be used each month.
Use Excel to formulate and solve this problem to maximize monthly audience exposure.
21) Three types of fertilizer are manufactured by a company—Regular, Supergro, and Jungle Feeder.
Regular should have at least 10 percent nitrogen and 16 percent phosphorous. Supergro should have at
least 12 percent nitrogen and 20 percent phosphorous, and Jungle Feeder should have at least 15 percent
nitrogen and 18 percent phosphorous. These are made by using two components—A and B.
Component A costs $0.30 per pound and is 14 percent nitrogen and 18 percent phosphorous.
Component B costs $0.50 per pound and is 20 percent nitrogen and 24 percent phosphorous. The
demand for Regular is projected to be 1000 pounds, while each of the others has a demand of 2000
pounds. Formulate and solve the appropriate linear program to determine how many pounds of
Components A and B to include in each type of fertilizer, respectively.