Transcript
54 ? Test Item File
Chapter Three ? 55
Excel Problems
1. An investment company currently has $1 million dollar 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
2. 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). Use Excel to model and solve this problem to determine the least costly job-machine assignment.
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
3. Refer to the data provided in question number 2. 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.
5. 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
Assume that beginning inventory is zero. Use Excel to formulate and solve this multiperiod production problem to minimize total costs.
6. Refer to question number 5. 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 that each is 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.
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.
10. 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
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. Consider the same information provided in question 10. However, now assume that demand for retail outlet three (R3) has increased to 300 units. How will this affect the prior solution to question 10? Use Excel to formulate and solve this problem.
12. Consider the same information provided in question 10. However, now assume that the road leading from factory F1 to retail outlet R1 is currently closed due to construction. How will this affect the prior solution to question 10? 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:
Car
Bay 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 illustrated 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.
