Background
BagCo. produces leather jackets ($330 price), handbags ($90 price) and wallets ($70 price). A jacket requires 8 ?2of leather, handbag 2 ?2 and 3 ?2 for wallets. The labor requirements for the three products are 12, 5 and 2 hours respectively. In addition, products requires machining hours and it is required for each product as following, 2 hrs, 1 hrs and 0.5 hrs respectively. The resources weekly limitation 700 ?2 of leather, 900 labor hours and 350 machining hours.
Questions 1. (10 points) Model the problem as a linear program. Define decision variables, objective function, and constraints.
2. (5 points) Use EXCEL Solver to determine the optimum production schedule for BagCo. Explain the solution.
3. (10 points) Based on the optimum solution of your LP model, which resource do you recommend reducing because is not getting used (the resource is abundant)? Which resources are scarce (fully used)?
4. (5 points) Obtain the sensitivity report of EXCEL Solver and explain the report.
Use the Sensitivity Report to answer questions (5 to 9)
5. (10 points) (A) What is the effect of increasing leather availability by 50?2 , (B) is it feasible to increase labor hours to 1000 hours? If possible, provide the new total cost, if not explain the reason? (Explain your answer based on sensitivity report only)
6. (10 points) If the company is studying the option of cutting resources down because of management difficulty, and they have the option to reduce the leather amount or labor working hours, which resource you recommend? (Explain your answer based on sensitivity report only)
7. (10 points) If the company is studying to reduce one of the products prices on an offer, which product do you recommend and why? (Explain your answer based on sensitivity report only)
8. (20 points) What is the effect on the solution if the company: (Explain your answer based on sensitivity report only)
a. Reduce the price of the handbag by 30$
b. Increase the price of the handbag by 40$
c. Reduce the price of the jacket by 60$
d. Increase the price of the wallet by 16$
9. (20 points) Assume that cost of one unit of any additional resource ( 1 m2 fabric, 1 labor hour and 1 machine hour) is 20 $, explain below questions by using the sensitivity analysis; (Explain your answer based on sensitivity report only) a. Which resource (or resources) do you suggest to company to buy? Explain. b. What is the amount of resources do you suggest buying? Explain. c. What is the final objective value if you buy extra resources? Show calculations.
Data:
lj | hb | w | limitation | |
Price | 330 | 90 | 70 | |
leather | 8 | 2 | 3 | 700 |
labor | 12 | 5 | 2 | 900 |
machine hrs | 2 | 1 | 0.5 | 350 |
1. Decision Variables: Let L, H, and W be the number of Leather Jacket, Handbag, and Wallets produced respectively.
Objective Function: Maximize the total profit
Zmax = 330L+90H+70W
Constraints:
8L+2H+2W <= 700 (Leather availability)
12L+5H+2W <= 900 (Labor availability)
2L+1H+0.5W <= 350 (Machining hrs availability)
L, H, W >=0
2. Solving using solver:
The solver is an excel plug in which can be installed form excel options. After installation, it is available in the data segment of the excel sheet. Once installed and launched, the parameters can be added
Spreadsheet Model along with formula:
Adding Parameters in solver:
1st: enter Green highlighted cell (objective function) in the set objective fireld
2nd: select Max
3rd: enter the yellow cells (decision variables) in the by changing variable cells field
4th: in constraints, click on add, enter the blue cells in the dialogue box which appears. on the left the left side values, select relationship in the middle and in the right enter the right side values of the inequality signs. similarly repeat for the next constraints by clicking on add button. Then click ok.
5th; Select Simplex Lp in solving method
6th: Click solve
Solution and Sensitivity Report:
In Solver results dialogue box, select sensitivity and click ok.
Optimum Production Schedule:
lj | hb | w | |
Quantity | 65 | 0 | 60 |
Leather Jacket = 65
Wallet = 60
Objective Function = 25650
The maximized profit with production quantities of leather jacket, handbag, and wallet being 65, 0, and 60 respectively with the full utilization of leather and labor resources but approximately half utilization of the machining hour is 25650.
3. Refer to the blue highlighted cells in the above screenshot. The Machining Hours resource can be reduced as the utilization is only 160 while availability is 350. Leather and Labor resources are being utilized fully (Utilization = Availability).
4. Refer to the sensitivity report generated during the solution in the final step.
Variable Cells part:
Final Value is the optimal solution of the decision variables
Reduced Cost is the amount by which the objective function coefficient would have to increase (as this is a maximization problem) so that the corresponding variable assumes a positive value in the optimal solution. If the final value of a variable is positive, the reduced cost is always 0.
Objective Coefficient is the unit profit of each product.
Allowable increase and Decrease is the range within which the objective coefficient can change without affecting the final value of the variables, (the objective function value might change)
Constraints Part:
The final Value is the optimal utilization of the resource.
Constraint RH side is the limit of the resource
The shadow price is the value by which the objective function would change for a unit change in the constraint RH side within the limits RH side + allowable increase and RH side - Allowable decrease.
5. a. Leather availability is increased by 50m2. The allowable increase is 650 is then the objective function would increase by 50*shadow price = 50*9 = 450. The new profit = 25650+450 = 26100
b. Labor hours increased to 1000 hours. The increase is 1000-900 = 100 hours. The allowable increase is up to 900+150 = 1050. Hence the objective function would increase by shadow price*100 = 21.5*100 = 2150. The new profit = 25650+2150 = 27800
Background BagCo. produces leather jackets ($330 price), handbags ($90 price) and wallets ($70 price). A jacket...
Plata Company produces two products: a mostly handcrafted soft leather briefcase sold under the label Maletin Elegant and a leather briefcase produced largely through automation and sold under the label Maletin Fina. The two products use two overhead activities, with the following costs: Setting up equipment $ 3,000 Machining 23,000 The controller has collected the expected annual prime costs for each briefcase, the machine hours, the setup hours, and the expected production. Elegant Fina Direct labor $9,000 $3,000 Direct materials...
A manufacturer of clothing makes pants, shirts, and jackets. The profit on a pair of pants is $12.00, on a shirt is $8.00, and on a jacket is $18.00. Both pants, shirts and jackets require the work of sewing operators and cutters. There are 60 hours of sewing operator time and 48 hours of cutter time available. It takes 10 minutes to sew one pair of pants, 6 minutes to sew a shirt, and 14 minutes to sew one jacket....
Leather Ltd has been in business for many years making hand-made, high end, leather goods. One of its products is a designer jacket. There are two divisions involved in the production of jackets, the Cutting Centre and the Stitching Division. Budgeted production for both divisions is 500 jackets per year. The Cutting Centre specialises in cutting out the unique designs and currently only supplies to the Stitching Division, which expertly hand stitches the product to make a jacket of exceptional...
Brown Furniture Company makes three kinds of office furniture:
chairs, desks, and tables. Each product requires skilled labor in
the parts fabrication department, unskilled labor in the assembly
department, machining on some key pieces of equipment, and some
wood as raw material. At current prices, the unit profit
contribution for each product is known, and the company can sell
everything it manufactures. The size of the workforce has been
established, so the number of skilled and unskilled labor hours is...
OBJECTIVE CORNERSTONE 4.3 Exercise 4.15 Drivers and Product-Costing Accuracy McCourt Company produces two types of leather purses: standard and handcrafted. Both purses use equipment for cutting and stitching. The equipment also has the capability of creating standard designs. The standard purses use only these standard designs. They are all of the same size to accommodate the design features of the equipment. The handcrafted purses can be cut to any size because the designs are created manually. Many of the manually...
Zippy motorcycle manufacturing produces two popular pocket bikes (miniature motorcycles with 49cc engines): the Razor and the Zoomer. In the coming week, the manufacturer wants to produce up to 700 bikes and wants to ensure the number of Razors produced does not exceed the number of Zoomers by more than 300. Each Razor produced and sold results in a profit of $70 while each Zoomer results in a profit of $40. The bikes are identical mechanically and only differ in...
Zippy motorcycle manufacturing produces two popular pocket bikes (miniature motorcycles with 49cc engines): the Razor and the Zoomer. In the coming week, the manufacturer wants to produce up to 700 bikes and wants to ensure the number of Razors produced does not exceed the number of Zoomers by more than 300. Each Razor produced and sold results in a profit of $70 while each Zoomer results in a profit of $40. The bikes are identical mechanically and only differ in...
Drivers and Product-Costing Accuracy McCourt Company produces two types of leather purses: standard and handcrafted. Both purses use equipment for cutting and stitching. The equipment also has the capability of creating standard designs. The standard purses use only these standard designs. They are all of the same size to accommodate the design features of the equipment. The handcrafted purses can be cut to any size because the designs are created manually. Many of the manually produced designs are in response...
Drivers and Product-Costing Accuracy McCourt Company produces two types of leather purses: standard and handcrafted. Both purses use equipment for cutting and stitching. The equipment also has the capability of creating standard designs. The standard purses use only these standard designs. They are all of the same size to accommodate the design features of the equipment. The handcrafted purses can be cut to any size because the designs are created manually. Many of the manually produced designs are in response...
Create a spreadsheet in excel and develop a sensitivity and answer report. Use the sensitivity and answer report to answer the questions below. Do not reuse solver. Please show your work and where you get the information: Zippy motorcycle manufacturing produces two popular pocket bikes (miniature motorcycles with 49cc engines): the Razor and the Zoomer. In the coming week, the manufacturer wants to produce up to 700 bikes and wants to ensure the number of Razors produced does not exceed...