Assignment
Linear Programing Formulation and Sensitivity Analysis
ADM2302 students are reminded that submitted assignments must be neat, readable, and well-organized. Assignment marks will be adjusted for sloppiness, poor grammar and spelling, as well as for technical errors. This assignment can be done in a group of one, two, three or four students. Plagiarism on assignments will not be accepted, each student must sign the statement of integrity. Solutions to the Case Study are to be prepared using the Report to Management format provided in the ADM2302 Course Outline.
This assignment can NOT be hand written.
The assignment is to be submitted electronically as a single PDF file via blackboard learn by Sunday October 30th prior to 23:59. Front page of the PDF document has to include title of the assignment, course code and section, student name and student number.
Second page is the statement of integrity that must be signed.
E- mail questions related to the assignment should be sent to the Teaching Assistant or posted on the Blackboard Learn course website "Discussion Area" (viewed by all).
Problem 1
First American Bank issues five types of loans. In addition, to diversify its portfolio, and to minimize risk, the bank invests in risk-free securities. The loans and the risk-free securities with their annual rate of return are given below:
Rates of Return for Financial Planning Problem
|
Type of Loan or Security
|
Annual Rate of Return (%)
|
|
Home mortgage (first)
Home mortgage (second)
Commercial loan Automobile loan
Home improvement loan Risk-free securities
|
6
8
11
9
10
4
|
The bank's objective is to maximize the annual rate of return on investments subject to the following policies, restrictions, and regulations:
1. The bank has $90 million in available funds.
2. Risk-free securities must contain at least 10 percent of the total funds available for investments.
3. Home improvement loans cannot exceed $8,000,000.
4. The investment in mortgage loans must be at least 60 percent of all the funds invested in loans.
5. The investment in first mortgage loans must be at least twice as much as the investment in second mortgage loans.
6. Home improvement loans cannot exceed 40 percent of the funds invested in first mortgage loans.
7. Automobile loans and home improvement loans together may not exceed the commercial loans.
8. Commercial loans cannot exceed 50 percent of the total funds invested in mortgage loans.
a. Formulate algebraically this Linear Programing problem. (11 points)
b. Find the optimal solution using Solver. (Provide a printout of the corresponding "Excel Spreadsheet" and the "Answer Report"). Include "managerial statements" that communicate the results of the analyses (i.e. describe verbally the results).
Problem 2
Morton and Monson Inc. is a small manufacturer of parts for the aerospace industry. The production capacity for the next four months is given as follows:
|
Production Capacity in Units
|
|
Month
|
Regular Production
|
Overtime Production
|
|
January
February March April
|
3,000
2,000
3,000
3,500
|
500
400
600
800
|
The regular cost of production is $500 per unit and the cost of overtime production is $150 per unit in addition to the regular cost of production.
The company can utilize inventories to reduce fluctuations in production, but carrying one unit of inventory costs the company $40 per unit per month. Currently there are no units in inventory. However, the company wants to maintain a minimum safety stock of 100 units of inventory during the months of January, February, and March and wants to have 300 units in inventory at the end of April.
The estimated demand for the next four months is as follows:
|
Month
|
January
|
February
|
March
|
April
|
|
Demand
|
2,800
|
3,000
|
3,500
|
3,000
|
The production manager is in the process of preparing a four-month production schedule.
a. Formulate algebraically the LP problem that provides the optimal production schedule and minimizes the total cost. (10 points)
b. Find the optimal schedule that minimizes total cost using Solver (Provide a printout of the corresponding "Excel Spreadsheet" and the "Answer Report"). Include "managerial statements" that communicate the results of the analyses (i.e. describe verbally the results).
Problem 3
A classic linear programming problem involves minimizing trim loss. Here is one version of the problem:
A mill cuts 20-foot pieces of wood into several different lengths: 8-foot, 10-foot and 12- foot. The mill has a certain amount of 20-foot stock on hand and orders for the various sizes. The objective is to fill the orders with as little waste as possible. For example, if two 8-foot lengths are cut from a 20-foot piece, there will be a loss of 4 feet, the leftover amount.
Currently, the mill has 350 20-foot pieces of wood on hand and the following orders, which must be filled from stock on hand:
|
Size in feet
|
Number Ordered
|
|
8
|
276
|
|
10
|
100
|
|
12
|
250
|
a. Formulate algebraically an LP model that will enable the mill operator to satisfy the orders with minimum trim loss. (Hint: List the different ways the 20-foot pieces could be cut into the desired sizes.)
b. Using your notation from a, write an equation for
i. The amount of waste that would result given a solution.
ii. The number of pieces of each size (8-foot, 10-foot, and 12-foot).
c. Formulate this same linear programming problem on a spreadsheet and SOLVE using Excel solver (Provide a printout of the corresponding "Excel Spreadsheet" and the "Answer Report"). Include "managerial statements" that communicate the results of the analyses (i.e. describe verbally the results).
CASE STUDY: Yeltsin Farms Report to Management Format
Igor Yeltsin operated a farm under the former Russian collective farm system. The collective farm raised hogs for distribution by the central government as its main activity. Previously, Igor was told how many hogs to raise each year by Moscow's central planning agency and was allocated the necessary animal feed to raise the hogs. With the new market- driven economy, Igor receives no instructions on how to operate his hog farm and must survive as best he can on his own by buying animal feed, raising hogs, and selling them to any buyers he can find. Hogs were fed a combination of corn and potatoes mixed to assure that minimum amounts of two primary nutrients were met: crude protein and calories. Corn and potato supplies have become less certain, and Igor has contracted to buy waste food scraps from a nearby food processing plant to supplement the previous hog diet of corn and potatoes. Igor has turned to you, a recently arrived United Nations consultant, for help in managing his farm in the new and very uncertain Russian market economy.
On his own, Igor contracted to sell up to 100 hogs to a Moscow butcher in the next month for a fixed price of 450 rubles per hog. In addition, Igor contracted to buy up to 800 kilograms of waste food from the processing plant for a price of 10 rubles per kilogram. He believes he can buy any amount of corn for 19 rubles per kilogram and can purchase up to 600 kilograms of potatoes from a nearby potato farm for 15 rubles per kilogram.
The table below presents the monthly requirements per hog in units of protein and thousands of calories (i.e. Kilo-calories). It also presents, for the three sources of animal feed, the amount of crude protein and Kilo-calories supplied per kilogram.
MINIMUM
REQUIREMENT AMT. SUPPLIED
AMT. SUPPLIED
AMT. SUPPLIED
NUTRIENT
PER HOG PER MONTH
PER KILOGRAM OF CORN
PER KILOGRAM OF FOOD WASTE
PER KILOGRAM OF POTATOES
|
Crude Protein
|
174
|
18
|
9
|
15
|
|
Kilo-calories
|
1400
|
30
|
120
|
80
|
To help Igor understand how to run his farm, another United Nations consultant has built the following LP algebraic formulation and its corresponding Excel model:
H= number of Hogs Sold
C= Corn Purchased (in Kg)
F= Food scraped Purchased (in Kg)
P=Potatoes purchased (in Kg)
Objective function:
Maximize net revenue=450H-19C-10F-15P
Subject to:
H<=100
F<=800
P<=600
18C+9F+15P>= 174H (amount of crude protein)
30C+120F+80P>= 1400 H (amount of kilo-calories)
The Excel model above shown displays the optimum answer to maximize Igor's operating income. The corresponding solver Sensitivity Report is also presented:
Igor has many questions to ask about operating his farm, which you will expertly answer by examining the Sensitivity Report and/or the Excel solver output provided above.
Some of Igor's questions cannot be answered from the Solver information given above. If so, answer as best you can from the information provided above, state why a more detailed answer is not available, and if appropriate, re-formulate the excel spreadsheet model and re-run Solver to produce the answers he seeks.
Questions:
1. "If I follow your recommended approach to managing my farm so as to maximize my operating income next month, how much will I earn from selling the 100 hogs?" asks Igor. "How many hogs will I sell, and how much corn, food scraps, and potatoes should I now purchase in that case?".
2. "I am confused", laments Igor. "You are telling me to buy fractional kilograms of corn and food scraps for my hogs. I have to make my purchases in whole kilograms. What should I do?"
3. "I think I can persuade the butcher to buy another 5 hogs from me at the same 450 ruble price. Would my situation improve if I try to sell him 105 hogs instead of 100? By how much would my operating income be improved, if at all?" asks Igor.
4. "You have to understand that everything is very volatile just now". Igor comments. "For example, it could easily happen that the cost of the corn that I buy might suddenly change. How much could the cost of corn increase before I would have to change my plans?'.
5. "Business relationships are really uncertain in Russia just now. I am nervous that the farm manager selling me the potatoes will not honor his promise to deliver the 600 kilograms. If he delivers only 500 kilograms of potatoes, how much will I be hurt and what changes do I need to make in my decisions?"
6. "Are there any alternate ways for me to achieve the same optimal operating income, involving, for example, different purchases of corn, food, or potatoes?" If so, what are they?" asks Igor.
7. "An international salesman from the Ralston-Purina Company stopped by the farm and wants to sell me prepackaged dry hog feed imported from Poland. He wants to charge me 16 rubles per kilogram for the packaged hog food, which includes transportation from Poland. He says each kilogram of his product provides 16 units of crude protein and 150 kilocalories. Should I start importing hog feed from Poland? And how much should I buy, if any, and what would be the impact of this opportunity on my optimal operating income?" asks Igor.