PROBLEM 1: LINEAR PROGRAMMING WITH EXCELA firm produces ties using various materials. Their aim, as most firms in the economy, is to maximize profit but they face some production constraints. Your task is to find the optimal solution by applying linear programming using Excel. Your results need to show the values for each decision variable, and the maximum profit subject to all the constraints. The Excel sheet need to be uploaded on the student portal, and a print screens of the pages need to be copied on this Word document (stating your name and student ID).DUBAI Ties is a firm, based 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 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 chains to supply ties. Contracts require a minimum number of ties but may be increased if demand 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 involved 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. Determine the Decision variablesii. Formulate the Profit Objective Functioniii. Formulate the production constraintsiv. Determine the feasible region (graphically)v. Draw at least three Iso-profit linesvi. Determine the optimal solution, using both the ISO profit method and the Corner Points method.
PROBLEM 3: BREAK EVEN ANALYSIS SCENARIO1) Fatimah has just acquired a shop that is producing and selling 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 selling 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 running 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 team choose largest value of last digit.). She also was told that the break-even point from the previous owner was at 800 lights per month. QUESTION a): What would the selling price then have been? Use MS Excel in the manner as shown below (in the example) to calculate this selling price. Make a print screen of your Excel work sheet, and ensure that the formula is shown clearly in the formula bar. Copy your print screen as a solution in this case study. b) Fatimah decides to set a selling price of $55 per LED light. She was also to find 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 Point (BEP)? (For fixed costs use information from question (a) above.Make a print screen of your Excel work sheet, and ensure that the formula is shown clearly in the formula bar. Copy your print screen as a solution in this case study. Also, show your result in a graph using MS Excel. (Make a print screen and paste as an answer into this case study).
b2) What will be the total amount of revenues at her BEP?
b3) Using Goal Seek function 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 selling price to $61, and when all her costs remain constant in value as in point d) above. Make print screens of your calculation showing Goal Seek function and values on the print screen.