business intelligence 5

We define our model parameters as follows

· is the demand (number of units required) for product .

· is the cost (in dollars) for producing each unit of product .

· is the cost (in dollars) for procuring each unit of product from the external supplier.

· is the machining time (in minutes) required to produce each unit of product .

· is the assembly time (in minutes) required to produce each unit of product .

· is the finishing time (in minutes) required to produce each unit of product .

· is the number of products of type i produced inhouse

· is the number of products of type i outsourced

where i = 1,2,3

Estimation of , , , from given data

From the file production.csv we can find an estimate of the average machining time, assembly time, finishing time, and cost per unit for each product type by taking the sample mean.

The values for these parameters (rounding off to two decimal points) are found as follows:

t1m = 16.00

t2m = 20.00

t3m = 24.00

t1a = 12.00

t2a = 16.00

t3a = 20.00

t1f = 10.00

t2f = 15.00

t3f = 19.99

C1p = 150

C2p = 180.01

C3p = 239.98

We express the same in a tabular format:

Estimation of demand for S1,S2,S3 in time period 53

We first do exploratory analysis of the time series data to decide which method to use for predicting demand in the 53rd time period. We plot the time series data for each of S1, S2 and S3.

Plot of demand for product S1 vs time period

Plot of demand for product S2 vs time period

Plot of demand for product S3 vs time period

We find that for all 3 products demand increases linearly with time period. This suggest a model using a linear fit between demand and time period would be appropriate for predicting demand in the 53rd time period.

Hence we use a regression model with x as demand and y as the time period to estimate the demand for each product in time period 53 rounding off demand to the nearest integer.

For S1

Regression equation is found to be y = 459.5+3.4x

The regression model is significant with a f-value less than 0.05 and has a high R-squared value of 0.9999 indicating good fit

Demand in time period 53 = 459.5+3.4*53 = 640

For S2

Regression equation is found to be y = 502.5+4x

The regression model is significant with a f-value less than 0.05 and has a high R-squared value of 0.9999 indicating good fit

Demand in time period 53 = 502.5+4*53 = 715

For S3

Regression equation is found to be y = 420+4x

The regression model is significant with a f-value less than 0.05 and has a high R-squared value of 0.9999 indicating good fit

Demand in time period 53 = 420+4*53 = 632

In table format the data can be represented as

Cost of outsourcing is already given to us

Available hours for various activities are also given as follows

· 300 hours of machining time is available for regular run.

· 240 hours of assembly time is available for regular run.

· 240 hours of finishing time is available for regular run.

We build a LP model in Excel and arrive at the following optimal production schedule:

Sensitivity analysis

Part a)

Part b)

(i) At most company will be willing to pay $0 for increasing the availability of machining time by one hour during regular run

(ii) At most company will be willing to pay $180 for increasing the availability of assembly time by one hour during regular run

(iii) At most company will be willing to pay $0 for increasing the availability of finishing time by one hour during regular run

Quality control

Rules obtained in canonical form

The following rules were obtained on using classification tree using rpart() function in R on the data with 80% training set and 20% test set

IF Test 2 < 1.965, THEN classify quality as POOR

IF Test 2 >= 1.965 and Test 3< 2.545 THEN classify as POOR

IF Test2 >=1.965 and Test 3 >=2.545 THEN classify as GOOD

Classification accuracy

Training set

Accuracy = (521+676)/1200 = 99.75%

Test set

Accuracy = (123+175)/300 = 99.33%