BUSINESS 275, WINTER 2016 ASSIGNMENT #2: LINEAR PROGRAMMING Administrative Matters 1.1 Deadline This assignment is due by 11 AM on Wednesday, March 23, 2016 in the correct labeled drop box outside room SBE 2201 AND in the electronic drop box at MyLS. If all the required material is not handed in at both places in the required format by 11:00 am, material will be accepted from 11:01 am on March 23rd till 1 pm on March 23rd with a 25% penalty. No assignments will be accepted after 1pm on March 23rd. 1.2 Assignment Objectives & Structure The objectives of this assignment are threefold: 1. To develop linear programming modeling skills, 2. To be able to solve linear programming problems using the Solver add-in to Excel, and 3. To practice creative thinking, a team approach to problem solving and time management. There are four questions on the assignment, with different weights. Each question will involve problem formulation, an Excel Solver solution, and some written discussion. More weight will be placed on the algebraic problem formulation than on the spreadsheet solution. Four Questions: Question 1: 45 marks (35 marks for formulation, 5 marks for excel spreadsheet and solution, 5 marks for report) Question 2: 20 marks (14 marks for formulation, 3 marks for excel spreadsheet and solution, 3 marks for report) Question 3: 20 marks (14 marks for formulation, 3 marks for excel spreadsheet and solution, 3 marks for report) Question 4: 15 marks Total : 100 marks 1.3 Submission Submit solutions to each problem separately using the cover pages for each problem which are available on MyLS. Use the \"equation editor\" in Microsoft Word to type out the algebraic linear programming problem. For each problem print out your spreadsheet model and answer report and include these as an appendix. Submit your work in word-processed report format, double-spaced. If the solutions to each question are not submitted separately, you will lose 10% of the marks. Submit hardcopies of everything. In addition to the hardcopy of your assignment, please submit ONE SPREADSHEET (in .xls or .xlsx format) to the drop box at MyLS. The spreadsheet must have one tab for each question and may include other tabs for related material (like Sensitivity Reports). The spreadsheets will not be graded but need to be made available for checking results in the paper submission. If the spreadsheet doesn't match the output in your submission you won't get marks for anything except problem formulation. Include the last names of your group members in the file name. 1.4 Assignment Groups This assignment is to be done in groups of less than or equal to three students (form your own groups) preferably from the same section. If you forma group with students from different sections, they must all be students of the same Instructor. You are strongly encouraged to work together as a team (versus assigning one question to each group member). You do NOT have to be in the same group as for assignment 1. BU 275 Assignment Two Winter 2016 Question 1 (45 marks) Tooba Winery produces two types of Shiraz wines: Classy and Plain. Each type of wine consists of High and Low grade grapes. It takes about 2 pounds of grapes to make a bottle of wine. At the start of 2016, the current inventory for each type of wines is as follows (numbers are in Cases of wine. Each case contains 12 bottles of wine) Classy Plain Cases of Wine 120 180 Tooba Winery is trying to design a production plan for the next 3 years. Each year the winery can produce at most 4000 bottles of wine. At the end of 2018, they want to have at least 200 cases of Classy and 150 cases of Plain (includes wine produced in 2018 and anything leftover from previous years). The projected market size for each product is as follows (numbers are in cases of 12 bottles): Classy(# of Cases of wine) Plain (# of Cases of wine) 2016 90 190 2017 150 170 2018 180 240 Wine cannot be sold in the year it is produced. Therefore, in 2016, the winery can only sell the inventory they had at the start of the year. All demand does NOT have to be met. Classy sells for $8/bottle and must consist of at least 70% high-grade grapes. Plain sells for $6/bottle and must consist of at least 60% high-grade grapes. The selling price is fixed over the three year horizon and will not depend on the age of the wine. The proportion of high grade grapes used in producing each type of wine can change from year to year but must meet the constraints of at least 70% high grades in Classy and at least 60% high grade grapes in Plain wine. Tooba Winery can buy grapes from two farmers. Each box of grapes from farmer 1 costs $10 and consists of 4 pounds of high-grade and 2 pounds of low-grade grapes. Each box of grapes from farmer 2 costs $8 and consists of 3 pounds of high-grade and 3 pounds of low-grade grapes. Assume that you can buy fractional boxes from farmers, and that you can make fractional bottles of wine. Further assume that the storage cost for the wine is zero. Formulate a linear programming problem to maximize Tooba's profit and solve it using Excel Solver. Present the solution in a table with the following rows and columns. Year 2016 Year 2017 Year 2018 Number of bottles of Classy Produced Number of bottles of Plain produced Can all forecasted demand be met for Classy? Can all forecasted demand be met for Plain? Explain your answers to the two questions in the table using not more than 6 lines of text. BU 275 Assignment Two Winter 2016 Question 2 (20 marks) A company is producing a product which requires one unit of each of the three parts for final assembly. When a department is operating, it produces all three parts according to the production schedule given in the table below. Department 1 Department 2 Production rate (units/hour) Part 1 Part 2 Part 3 8 6 9 6 11 5 Cost/hour 25 20 [Note: Department 1 produces 8 units of Part 1 AND 6 Units of Part 2 AND 9 units of Part 3 in one hour. Department 2 produces 6 units of Part 1 AND 11 units of Part 2 AND 5 units of Part 3 in an hour. In other words, one hour of production in each department yields all three parts and the number of each part produced is given in the table. Fractional hours can be used at the two departments. ] In a week 1050 finished (assembled) products are needed (but up to 1200 can be assembled if necessary). If Department 1 has 100 working hours available, but Department 2 has 110 working hours available, formulate the problem of minimizing the cost of producing the finished (assembled) products needed this week as a linear programming problem and solve it using Excel Solver. There is limited storage space and only 200 unassembled parts (of all types) can be stored at the end of the week. Write a report detailing your findings in not more than 6 lines of text. Question 3 (20 marks) You are the currency trader for an investment bank. Currently the bank has the following currency holdings (in millions). Holding (in millions) US Dollar 5 Canadian Dollar 12 British Pound 2 Euro 8 Considering that the value of Canadian dollar is rapidly depreciating, the bank has decided to adjust the holdings to have at least the following holdings of each currency Holding (in millions) US Dollar 6 Canadian Dollar 2 British Pound 4 Euro 10 Your job is to determine transfers between currencies, such that at the end of the exchange, the bank 's currency holdings satisfy the above minimums. In doing so, you should try to maximize the total value (in Canadian dollars) of holdings at the end of the exchange. The exchange rates between currencies are as follows US Dollar Canadian Dollar British Pound Euro US Dollar 1 0.71 1.39 1.07 Canadian Dollar 1.34 1 1.91 1.48 BU 275 Assignment Two Winter 2016 British Pound 0.7 0.5 1 0.74 Euro 0.9 0.63 1.29 1 Formulate a linear programming problem and solve it using Excel solver to determine the maximum value of the holdings(in Canadian dollars) after the exchange. Write a report detailing your findings in not more than 6 lines of text. [Note: The exchange rates are not symmetric.] Question 4 (15 marks) Consider the following LP problem, with two decision variable x and y, and unknown parameters M and N. (Constraints are numbered for ease of reference) Objective Function: constraint 1) x<=25 constraint 2) y<=0.4x constraint 3) x+y<=N constraint 4) x>=0 constraint 5) Maximize z = 4x+My y>=0 i. Assume M=2 and N=5 and solve the LP problem graphically to find the optimal values of x and y. Identify the binding constraints. (7 marks) ii. Let N=5. Determine the value of M for which there are alternate optimal solutions. Identify at least 3 optimal solutions for this value of M. (Use graphical method to do this part). (4 marks) iii. This part refers back to the original problem and is independent of part ii. Let N=5. Find the smallest positive integer M(>2) for which the optimal solution is different from the case of M=2. (2 marks) iv. This part refers back to the original problem and is independent of part ii and iii. Let M=2. Find the smallest integer N (> 5) for which the binding constraints are different from the case of N=5. What are the binding constraints in this case? (2 marks) BU 275 Assignment Two Winter 2016