Quantitative Analysis | Assignment 1

Quantitative Analysis
Student Name
Institution
1

Question 1
a) After taking into accounts the various costs associated with both pre-tanned leather and
raw & untreated leather on top of the production constraints, the excel solver was applied
to model the production cost (ModB, 2019).
The table below summarizes the outcome of the model
Leather Supply
Raw and untreated Total
A B C
Quantity (sq ft) 10000 6400 0 9840
Purchase price $60.00 $53.00 $46.00 $34.00
Material cost $600,000.00 $339,200.00 $0.00 $334,560.00 $1,273,760.00
Pre-tanning cost $0.00 $0.00 $0.00 $147,600.00 $147,600.00
Finished leather (sq ft) 9500 5120 0 7380 22000
Production hrs (Tanning equipments) 10000 12800 0 49200 72000
Objective function
Minimise total costs $1,421,360.00
Constraints
Finished leather 22000 >= 22000
Raw and untreated leather 9840 <= 12000
A 10000 <= 10000
B 6400 <= 14000
C 0 <= 18000
Production hrs (Tanning) 72000 <= 72000
LP Model to minimise the cost of leather purchasing
Pre-tanned
From the model H&H should purchase 1000sq ft of grade A pre-tanned leather, 6400sq ft
of grade B and no grade C. The quantity of raw and untreated leather to be purchased
should be 9840 sq. ft. Purchasing this product mix will cost a total of $1,421,360. The
sensitivity report is attached below.
2

Microsoft Excel 16.0SensitivityReport
Worksheet: [1093992BUS107GBARSIData.xlsx]Question1
Report Created: 10/5/20199:06:11PM
Variable Cells
Final Reduced
Cell Name Value Gradient
$B$5 Quantity (sq ft) A 10000 0
$C$5 Quantity (sq ft) B 6400 0
$D$5 Quantity (sq ft) C 0 0.119905947
$E$5 Quantity (sq ft) Raw and untreated 9840 0
Constraints
Final Lagrange
Cell Name Value Multiplier
$B$16 Finished leather A 22000 66.79999927
$B$17 Raw and untreated leather A 9840 0
$B$18 A A 10000 -3.240000389
$B$19 B A 6400 0
$B$20 C A 0 0
$B$21 Production hrs (Tanning) A 72000 -0.220000318
In the LP formulation the following assumptions were made
 The selling cost per unit of the final tanned leather from grade A, B, C and the
raw and untreated leather is uniform.
 There are no other production limitations apart from the ones listed in the model.
For example, labour supply is abundant.
 The cost of purchasing a unit of grade A, B, C and raw leather will remains
constant for the foreseeable future.
b) Once the raw and untreated leather get through the curing and beamhouse process the
material obtained (pre-tanned leather is identical) for grade A, B, C and the raw leather
purchased. The cost of running the tanning machine will thus be uniform for all the
martials. The cost is thus irrelevant as its inclusion will have no impact on the final
purchase cost of the materials.
c) From the sensitivity repot, the Lagrange multiplier of raw and untreated leather is 0. This
means increasing its quantity will have no impact on the total purchase cost. The firm has
no economic advantage in increasing the in-house capability.
3