LINEAR PROGRAMMING WITH EXCEL

      A firm produces ties usin" rel="nofollow">ing various materials. Their aim, as most firms in" rel="nofollow">in the economy, is to maximize profit but they face some production constrain" rel="nofollow">ints. Your task is to fin" rel="nofollow">ind the optimal solution by applyin" rel="nofollow">ing lin" rel="nofollow">inear programmin" rel="nofollow">ing usin" rel="nofollow">ing Excel. Your results need to show the values for each decision variable, and the maximum profit subject to all the constrain" rel="nofollow">ints. The Excel sheet need to be uploaded on the student portal, and a prin" rel="nofollow">int screens of the pages need to be copied on this Word document (statin" rel="nofollow">ing your name and student ID).DUBAI Ties is a firm, based in" rel="nofollow">in Deira, that produces four varieties of ties:? Two are blends of polyester and cotton ? One is expensive all-silk? One is all-polyesterThe table on the below shows the cost and availability of the three materials used in" rel="nofollow">in the production process:MATERIAL COST PER YARD ($) MATERIAL AVAILABLE PER MONTH (YARDS)Silk 24 1,200Polyester 6 3,000Cotton 9 1,600 The firm has contracts with several major department store chain" rel="nofollow">ins to supply ties.? Contracts require a min" rel="nofollow">inimum number of ties but may be in" rel="nofollow">increased if demand in" rel="nofollow">increases.? Their goal is to maximize monthly profit given the decision variables stated below.Decision variables:X1 = number of all-silk ties produced per monthX2 = number all-polyester tiesX3 = number of blend 1 polyester-cotton tiesX4 = number of blend 2 silk-cotton ties DATA PROVIDED:VARIETY OF TIE SELLING PRICE PER TIE ($) MONTHLY CONTRACT MINIMUM MONTHLY DEMAND MATERIAL REQUIRED PER TIE (YARDS) MATERIAL REQUIREMENTSAll silk 19.24 5,000 7,000 0.125 100% silkAll polyester 8.70 10,000 14,000 0.08 100% polyesterPoly – cotton blend 1 9.52 13,000 16,000 0.10 50% polyester – 50% cottonSilk-cotton blend 2 10.64 5,000 8,500 0.11 60% silk  -                   40% cotton     PROBLEM 2: LINEAR PROGRAMMING USING GRAPHICAL REPRESENTATIONAn ice cream company is in" rel="nofollow">involved in" rel="nofollow">in the production of two different tastes: Cherry and Kiwi. Two resources needed to produce Cherry and Kiwi ice cream are Milk and Sugar. The table below gives the required amount for each item: ITEM UNITS/MILK KG/SUGARCherry ice cream 4 5Kiwi ice cream 6 2 The company has 200 units of Milk available and 150 Kg Sugar available.  The revenue received for each item produced (all production is sold) is AED 400 for a pack of Cherry Ice cream (2Kg) and AED 600 for a pack of Kiwi Ice cream (2Kg). i. Determin" rel="nofollow">ine the Decision variablesii. Formulate the Profit Objective Functioniii. Formulate the production constrain" rel="nofollow">intsiv. Determin" rel="nofollow">ine the feasible region (graphically)v. Draw at least three Iso-profit lin" rel="nofollow">ines (and calculate their values)vi. Determin" rel="nofollow">ine the optimal solution, usin" rel="nofollow">ing both the ISO profit method and the Corner Poin" rel="nofollow">ints method.     PROBLEM 3: BREAK EVEN ANALYSIS SCENARIO1) Fatimah has just acquired a shop that is producin" rel="nofollow">ing and sellin" rel="nofollow">ing only one special LED solar light.  For all your equations: denote the fixed cost of this shop by f, the variable cost per light by v, and the sellin" rel="nofollow">ing price per light by s. Furthermore, use X to denote the number of lights sold. a) The previous shop owner told Fatimah that fixed costs of runnin" rel="nofollow">ing the shop are $10,000 per month, and that the variable costs to make one light are at value  $ (=last digit of ID number, if two students in" rel="nofollow">in team choose largest value of last digit.). She also was told that the break-even poin" rel="nofollow">int from the previous owner was at 800 lights per month. QUESTION a): What would the sellin" rel="nofollow">ing price then have been? Use MS Excel in" rel="nofollow">in the manner as shown below (in" rel="nofollow">in the example) to calculate this sellin" rel="nofollow">ing price. Make a prin" rel="nofollow">int screen of your Excel work sheet, and ensure that the formula is shown clearly in" rel="nofollow">in the formula bar. Copy your prin" rel="nofollow">int screen as a solution in" rel="nofollow">in this case study.   b) Fatimah decides to set a sellin" rel="nofollow">ing price of $55 per LED light. She was also to fin" rel="nofollow">ind a new supplier as her old supplier proved unreliable. As a consequence, she now pays a variable cost of $1/unit. Question b1): What will be Fatimah’s (1) Break-Even Poin" rel="nofollow">int (BEP)? (For fixed costs use in" rel="nofollow">information from     question (a) above.Make a prin" rel="nofollow">int screen of your Excel work sheet, and ensure that the formula is shown clearly in" rel="nofollow">in the formula bar. Copy your prin" rel="nofollow">int screen as a solution in" rel="nofollow">in this case study. Also, show your result in" rel="nofollow">in a graph usin" rel="nofollow">ing MS Excel. (Make a prin" rel="nofollow">int screen and paste as an answer in" rel="nofollow">into this case study). b2) What will be the total amount of revenues at her BEP? b3) Usin" rel="nofollow">ing Goal Seek function in" rel="nofollow">in MS Excel, set a goal to earn a profit of $9999, and calculate the value of the variables for number of LED lights she needs to sell, when she raises her sellin" rel="nofollow">ing price to $61, and when all her costs remain" rel="nofollow">in constant in" rel="nofollow">in value as in" rel="nofollow">in poin" rel="nofollow">int d) above. Make prin" rel="nofollow">int screens of your calculation showin" rel="nofollow">ing Goal Seek function and values on the prin" rel="nofollow">int screen.