Question Details

[solution] » Requirements for Business Intelligence Capstone Project. A

Brief item decscription

Step-by-step solution file


Item details:

Requirements for Business Intelligence Capstone Project. A
More:

attached.....please solve it .........................................................................................................................


Requirements for Business Intelligence Capstone Project.

 

A company produces three types of alarm systems ?S1, S2, and S3 ? and supplies them to a

 

retailer. It is contractually obligated to meet the demands of the retailer for each alarm system.

 

Because of limited capacity the company may not have sufficient machining, assembly, and

 

finishing time available to satisfy the entire demand in each period through its regular

 

production runs. Contractual obligation requires the company to make up the shortfall in

 

production through special production runs at higher costs. The company aims to meet the

 

retailer?s demands at minimum cost.

 

LP Formulation:

 

Task 1: (10 Points)

 

Formulate a linear programming (LP) model that may be solved to identify the optimal

 

production plan for the company in each time period.

 

Specifically, you must define the decision variables, objective function, and constraints in your

 

LP model using the following parameters:

 

In each time period, for each product i?(1, 2,3) : Di is the demand (number of units required) for product i . Ci Ci ti t ia is the assembly time (in minutes) required to produce each unit of product i . ti R is the cost (in dollars) for producing each unit of product i in a regular run. S is the cost (in dollars) for producing each unit of product i in a special run. m f is the machining time (in minutes) required to produce each unit of product i . is the finishing time (in minutes) required to produce each unit of product i . Further, assume that: 300 hours of machining time is available for regular run. 1 240 hours of assembly time is available for regular run. 240 hours of finishing time is available for regular run. LP Parameter Estimation:

 

You must now use available data to estimate the parameters of the LP formulated in Task 1.

 

m

 

a

 

f

 

R

 

Estimation of t i , t i , t i , and Ci : The text file ?production.csv? contains 7 columns: SerialNo, BatchNo, ProductCode,

 

MachineTime, AssemblyTime, FinishTime, and Cost. Using any DBMS of your choice, create a

 

table PRODUCTION with SerialNo as its primary key and the 6 other columns as attributes and

 

insert the 15,000 records from production.csv into the table. SerialNo is a unique identifier

 

assigned to each unit produced by the company; ProductCode specifies the product type;

 

BatchNo identifies the batch in which an item is produced (items are produced in batches of 10

 

units of a product type); MachineTime, AssemblyTime, and FinishTime specify the time (in

 

minutes) taken by each process (machining, assembly, and finishing) to produce a unit; the last

 

attribute, Cost, specifies the cost (in dollars) of producing the unit in a regular run.

 

Task 2: (10 Points)

 

Formulate an SQL query to obtain the average machining time, assembly time, finishing time,

 

m and cost per unit for each product type as estimates of the parameters t i a f , t i , t i , and CiR of the LP model. In your report, you must:

 

1. Specify your SQL query to obtain the estimates.

 

2. Specify your parameter estimates in the table below. Round all estimates to 1 decimal

 

place.

 

Parameters for

 

Regular Production S1 Product type

 

S2 S3

 

2 m Machine Time ( t i

 

Assembly Time (

 

a

 

f

 

ti )

 

Finish Time ( t i )

 

R

 

Regular Cost ( Ci )

 

S

 

Estimation of special run cost Ci : R

 

It is known that the regular production cost Ci is a linear function of the machining, R 0 m m a a f f assembly, and finishing times for each product type. That is, Ci = ?i + ? i t i + ?i t i + ? i t i , where

 

? 0i is the fixed cost incurred to produce each unit of i , and ? mi , ? ai , and ? fi are respectively the costs per minute for machining, assembly, and finishing each unit of product

 

i during regular run. Task 3: (6 Points)

 

Run regressions to estimate the coefficients ? 0i , ? mi , ? ai , and ? fi for each product i . In your report, please explain how you obtained the data for the 3 regressions to estimate the

 

coefficients. Then present your parameter estimates in the table below. Round all estimates to 1

 

decimal place.

 

Coefficients for

 

Regular Production

 

0

 

Intercept ( ? i ) S1 Product type

 

S2 S3 MACHINE TIME (

 

? mi )

 

ASSEMBLY TIME ( 3 ? ai )

 

f

 

FINISH TIME ( ? i ) The fixed costs 0 ?i associated with the production of each unit of i is the same under the regular and the special run, but the cost per minute for machining, assembly, and finishing are

 

50% higher in the special run than for the regular run.

 

Task 4: (4 Points)

 

Use the above relationship to estimate that the cost for producing each unit of product i in a

 

S

 

0

 

m m

 

a a

 

f f

 

special run as Ci =? i +1.5 ( ? i t i + ?i t i + ? i t i ) . Present the estimates in the following format:

 

Product type

 

Special production cost per unit ( C S1 S2 S3 S

 

i )

 

Estimation of demand Di The text file ?demand.csv? contains the retailer?s sales data by region (North, South, East, and

 

West) for the three alarm systems over the last 52 time periods. For example, the first row shows

 

that 119 units of S1 were sold in the East region in time period 1, and the last row shows that 177

 

units of S3 were sold in the West region in time period 52.

 

Create a table called DEMAND with a composite primary key made up of the attributes Period,

 

ProductCode, and Region. Sales is the fourth attribute in the DEMAND table. Insert all 624

 

records from demand.csv into the DEMAND table.

 

Task 5: (10 points)

 

Extract the data needed for predicting demand for S1 by formulating an SQL query that lists

 

4 the Period and the sum of the total sales for S1 from all four regions in each of the 52 time

 

periods as S1demand. Similarly, formulate two more SQL queries to obtain the 52 records for

 

S2demand and S3demand.

 

In your report, specify the 3 SQL queries to obtain S1demand, S2demand and S3demand.

 

Task 6: (10 Points)

 

Use the results returned by the queries formulated in Task 5 in forecasting models to predict

 

the demands Di in time period 53 for each product. You should consider various prediction and forecasting methods that you are familiar with. Use

 

the method that you think is most accurate in estimating demands. In your report, please present

 

the estimates for time period 53 in the following format:

 

Product type

 

Di

 

Demand (

 

) in period S1 S2 S3 53 5 Optimal LP Solution:

 

Task 7: (10 Points)

 

Solve the LP formulated in Task 1 using the parameters estimated in Tasks 2, 4, and 6 to

 

determine the optimal production plan for period 53.

 

Report the minimum production cost achievable, number of units of each product type to be

 

produced under the regular and special production runs, and the resources used during regular

 

run in the following format:

 

Minimum cost attainable:

 

Number of units produced

 

Regular Run

 

Special Run S1 Resources in regular run

 

MACHINE TIME

 

ASSEMBLY TIME

 

FINISH TIME Minutes used S2 S3 Sensitivity Analysis:

 

Task 8. (3+12 = 15 Points).

 

Perform sensitivity analysis by changing one parameter at a time (leaving all other parameters

 

fixed at the values used in Task 7) and answer the following questions.

 

(a) By how much does the total production cost change as the demand for each product type

 

changes by 1 unit?

 

(b) At most how much should the company be willing to pay to

 

(i) Increase the availability of machining time by one hour during regular run? (ii) Increase the availability of finishing time by one hour during regular run? (iii) Increase the availability of assembly time by one hour during regular run? Quality Control

 

6 The text file ?defective.csv? contains 2 columns. The first column DefectiveID is an identifier,

 

and the second column SerialNo specifies the serial number of a defective product. Create a table

 

DEFECTIVE with DefectiveID as its primary key and insert all 591 records from defective.csv

 

into the table. Note that SerialNo in the DEFECTIVE table is a foreign key that references the

 

primary key in the PRODUCTION table.

 

The text file ?quality.csv? contains 5 columns containing data from quality control tests run on

 

1500 batches of items produced. Create a table QUALITY with BatchNo as its primary key and

 

Test1, Test2, Test3, and Test4 as its other 4 attributes. Insert all 1500 records from quality.csv into

 

the table. Note that BatchNo in the PRODUCTION table is a foreign key that references the

 

primary key BatchNo in the Quality table.

 

Any batch that contains more than one defective items is deemed to be of poor quality; a batch

 

with at most one defective item is considered to be of good quality.

 

Task 9: (10 Points)

 

Formulate an SQL query that lists all 5 columns from the QUALITY table and adds a derived

 

column BatchQuality that contains ?Poor? if the batch is of poor quality (contains at least 2

 

defective items) and ?Good? otherwise.

 

In your report, include:

 

1. The SQL query for task 9

 

2. The results of the query in a file batchQuality.csv. Task 10: (10 Points)

 

Use the data obtained from Task 9 to train and test a Classification Tree that predicts

 

7 BatchQuality based on values of the features Test1, Test2, Test3, and Test4.

 

In your report:

 

1. Specify the rules that you obtained in Task 10 in the canonical form:

 

IF ?. THEN ?

 

2. Present the classification accuracy of this set of rules in the form:

 

Number of batches

 

Predicted Poor

 

Quality

 

Predicted Good

 

Quality Actually Poor Quality Actually Good Quality If you wish, you may also use other prediction and classification methods (such as Logistic

 

Regression, Neural Nets, and Discriminant Analysis) to classify BatchQuality based on values of

 

the features Test1, Test2, Test3, and Test4, and comment on the classification accuracy of these

 

methods. Summary of deliverables:

 

Deliverable

 

Project selection

 

Mid-Term Report

 

Final Report Tasks

 

?

 

1, 2, 3, 4, 5, & 6

 

7, 8, 9, & 10 Weight

 

05%

 

50%

 

45% Due Date

 

May 16, 2016

 

June 27, 2016

 

August 8, 2016 8

 







About this question:
STATUS
Answered
QUALITY
Approved
ANSWER RATING

This question was answered on: Feb 21, 2020

PRICE: $24

Solution~00065708775.zip (18.37 KB)

Buy this answer for only: $24

This attachment is locked

We have a ready expert answer for this paper which you can use for in-depth understanding, research editing or paraphrasing. You can buy it or order for a fresh, original and plagiarism-free copy (Deadline assured. Flexible pricing. TurnItIn Report provided)

Pay using PayPal (No PayPal account Required) or your credit card. All your purchases are securely protected by PayPal.
SiteLock

Need a similar solution fast, written anew from scratch? Place your own custom order

We have top-notch tutors who can help you with your essay at a reasonable cost and then you can simply use that essay as a template to build your own arguments. This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student. New solution orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

Order Now