Question

H&H is a tannery that supplies high quality exotic leather to major international fashion houses and customers globally. Tanning is the process of treating skins and hides of animals to produce leather. The three major steps in the production of leather are curing, beamhouse operations and tanning. Due to the increased demand and limited production space, H&H sourced pre-tanned leather (the supplier has already completed the curing and beamhouse operations processes), and raw and untreated skin (another separate supplier). The company has a production area of 7500 sq m.

The weekly demand for tanned leather is at least 22,000 sq ft. There is a supplier who provides H&H pre-tanned leather at three different grade: Grade A, B, and C. After purchasing the pre-tanned leather, H&H will then perform the last step in tanning the leather before shipping it to clients. H&H has a small section within the production facility that could perform curing and beamhouse operations on raw and untreated skin, up to 12,000 sq ft per week. It costs $15 per sq ft for H&H to perform the pre-tanned process while it could purchase up to 10,000 sq ft of grade A pre-tanned leather, 14,000 sq ft of grade B pre-tanned leather, 18,000 sq ft of grade C pre-tanned leather per week at $60 per sq ft, $53 per sq ft, and $46 per sq ft, respectively. It costs H&H $34 per sq ft to purchase raw and untreated skin.

The tanning process will result in shrinkage and the final yield depends on several factors such as the raw skin quality and pre-tanned process quality. 1 sq ft of Grade A pre-tanned leather will result in 0.95 sq ft of finished leather, while 1 sq ft of Grade B pre-tanned leather will result in 0.80 sq ft of finished leather and 1 sq ft of Grade C pre-tanned leather will result in 0.70 sq ft of finished leather. H&H in-house produced pre-tanned leather generally results in a yield of 0.75 sq ft of finished leather for every 1 sq ft of pre-tanned leather. The tanning equipment has the equivalent of 1200 production hours per week. For the tanning process, every sq ft of Grade A, B and C pre-tanned leather would require 1 minute, 2 minutes and 4 minutes, respectively. The in-house produced pre-tanned leather would require 5 minutes per sq ft for the tanning process.

(a) You are just hired as a purchasing officer at H&H. Develop an LP model to minimise the cost of leather purchasing, solve it with Microsoft Excel and make your recommendations, and show the Sensitivity Report. State assumptions you made in formulation if there are any.   

Answer the following questions by using the Microsoft Excel solution output you obtained for Question 1(a) and do not re-run your LP model in Excel for the following scenarios.   

(b) During the presentation to showcase your recommendation, a colleague raised a query on why the cost of running the tanning machines is not being taken into consideration? The management proposed that you re-run your analysis again noting the cost of running the tanning machines. What is your response to this demand?

(c) Your manager asked you if it makes sense to increase the capacity of the in-house capability in curing and beamhouse operations which currently stands at 12,000 sq ft per week. Interpret the solution output and develop your advice.

Answer 1

Minimum Demand for Tanned Leather = 22,000 sq. ft. per week

Cost of Pre Tanned Leather from inhouse production can be calculated as follows

Cost of Raw Material = $34 per sq. ft.

Cost of Curing and Beamhouse operations = $15 per sq. ft.

Total Cost for inhouse production of Pre Tanned Leather = $49 per sq. ft.

The other details provided in the question can be summarised as follows in the below table:

	Grade A	Grade B	Grade C	Inhouse
Price Per sq. ft. (input)	60	53	46	49
Yield from per sq ft	.95	.80	.70	.75
Price per sq. ft. (output)	63.16	66.25	65.71	65.33
Tanning Time per sq. ft. (input)	1	2	4	5
Tanning Time per sq. ft. (Output)	1.05	2.5	5.71	6.67
Max Availability (input)	10000	14000	18000	12000
Max Availability (Output)	9500	11200	12600	9000

Price per sq ft (output) = Price per sq. ft. (Input) / Yield per sq. ft.

Tanning Time per sq. ft. (output) = Tanning Time per sq. ft. (input) / Yiled from per sq. ft.

Max Availability of Output = Max Availability of Input * Yield from per sq. ft.

Since we have to solve the Linear Programming problem on excel, we can input the data in excel as given in the image below

Only the output values are considered in the excel solution

Formulas in the cells are provided in below images

Apply solver available in the data tab with the following options

in the options tab, check the assume non-negativity box and click solve.

It should give you the following result:

The first part of the question can be answered with the help of Excel LP Solver method as provided above

b) The cost of running the tanning machines should be considered since the time taken by different grade of pre tanned leather for tanning is different. We need cost of running the tanning machine to solve this problem futher.

c)It doesn't make sense to increase the inhouse capacity