BUS107e Quantitative Methods: Group Assignment Solution, July 2019

This document presents a comprehensive solution to a BUS107e Quantitative Methods group assignment. It addresses three key questions: the first utilizes Excel Solver to model production costs for a leather goods company, determining optimal purchasing quantities of pre-tanned and raw leather to minimize costs, and includes a sensitivity report analysis. The second question applies linear programming to management, specifically in recruitment and deployment, discussing the challenges and solutions related to labor laws and workforce flexibility. The third question focuses on time series analysis, using a 4-quarter moving average of the RSI to forecast future values for 2019 and 2020. The assignment highlights the application of quantitative techniques in business decision-making, including optimization, forecasting, and resource allocation, with references to relevant academic sources.

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.0 Sensitivity Report
Worksheet: [1093992 BUS107GBARSIData.xlsx]Question 1
Report Created: 10/5/2019 9:06:11 PM
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

d) Since the shrinkage from the internally processed raw leather is not reduced, the option
will be more costly due to wastage and hence the firm will have to decist from purchasing
the raw leather. On the other hand, the relatively cheaper cost of grade B and C in
comparison to A will mean they will be the preferable brand. Being that B consumes less
hours in the tanning process it will the 1st choice. The firm will thus purchase 14000sq ft
of grade B and the remaining sq. ft needed will come from C. Due to the low unit cost of
C and B compared to A the total purchase cost will go down. Should the new technique
be available at no additional cost all recommend the firm to put it in operation.
e) All request the local supply to cut the supply of grade C and reduce the supply of grade B
to at most 6500sq ft per week while maintaining the supply of grade A to the current
level. H & H negotiation strategy is to threaten to shift to the purchase of raw and
untreated leather as an increase in the prices of pre-tanned leather will make their
purchase less economical.
Question 2
Application of LP in management
Linear programming can be applied by a manager to solve problems related to
recruitment and deployment of man power to the various organocation’s departments. Using the
cost minimisation as the objective function, LP can assist evaluate the minimum number of
employees that are required in various shifts to allow the firm meet production deadlines (Chand,
2019).
Problem faced when implementing LP in recruitment and deployment
When it comes to employee recruitment and deployment, there are labour laws that have
to be adhered to hence making the process less flexible. The managers may therefore find it hard
to just employ additional and release extra employee whenever they feel its uneconomical.
Solution to the problem
So as to allow the managers have a flexible group of employees that can be called upon
to take on a shift, the firm need to have a mixed of permanent employees and part time
employees. The permanent employees will be used in various shifts at anytime while the part
time can be given a contract that only requires then to be deployed by the managers only when a
need arise.
4

Question 3
a. The quarterly RSI can be focused using a 4-quarter moving average.
This can be calculated using the data analysis tool add in excel and a graph computed as
shown.
b. The moving average technique can afterwards ne applied to calculate the quarterly
forecasts for 2019 and 2020 and the solution obtained as follows.
For 2019 the expected moving average for the 4 quarters will be 100.4 and for 2020 the
expected average will be 104.5. These values are obtained from the graph and data in the
excel document attached.
5

References
Chand, S., 2019. Applications of Linear Programming for Solving Business Problems. [Online]
Available at: http://www.yourarticlelibrary.com/linear-programming/applications-of-linear-
programming-for-solving-business-problems-economics/28947
[Accessed 7 October 2019].
ModB, 2019. Linear Programming. [Online]
Available at: http://wps.prenhall.com/wps/media/objects/2234/2288589/ModB.pdf
[Accessed 7 October 2019].
6