Final Exam

BSAD 343, Business Analytics, Spring 2020

About

Clearly show and organize your work. Each answer should appear below the corresponding question. Be careful not to modify existing text and code. For multiple choice questions you can place an (x) mark next to the text to mark your selection. Where necessary execute the already included code chunk, and add yours to perform required calculations. Best to view first in Preview mode!

Problem 1: (6 points)

Answer the below by typing your answer following each question. Each question is graded as marked.

1. Which one(s) of the following statements is(are) true with regards to a star schema? (1pt)

1. Each table has to have a primary key

2. Fact table is the biggest transactional table

3. Dimension tables are best suited for categorical data

4. All dimension tables are connected to fact table

5. All above (x)

2. A set of data has a median much larger than the mean. What can we say about the data distribution? (1pt)

1. Skew left (x)

2. Symmetrical

3. Skew Right

4. Normal

3. Which of the below is not a suited format for textual data? (1pt)

1. JSON

2. HTML

3. JPEG (x)

4. ASCII

5. CSV

4. You are given the following linear regression model describing the cost C of heating bill in $ versus the temperature T in Fahrenheit: C = 2T + 50. What are the units of the intercept and the slope? (1pt) The intercept and slope must also be in dollars. This is because no matter what the T (temperature) is, we will come out to a C value in dollars, therefore the intercept and slope must be in dollars as well to output the dollar value we need for C. Although the T is in degrees farhenheit, this is the only variable that will be in a different unit, but the slope and intercept will be dollars to coincide with C, or our output value.

5. The closer data values are to the mean, the more potential for noise to occur (1pt)

1. True

2. False (x)

6. You are given a data set with 100 entries. Twenty entries have zero values. When do we use the formula sum(data)/100 to compute the average of the data set? (1pt)

1. When zero values mean true values (x)

2. When zero values represent missing values

3. When zero values mean true as well as missing values

4. None of the above

Problem 2: (13 points)

Read the description of the dataset “EuroPetStore.csv” in the pdf file provided to you.

Read the dataset in R studio and solve the answers below.

mydata = read.csv("EuroPetStore.csv", header=TRUE, sep=",")
head(mydata)

Answer the below by inserting your code and typing your answer following each question. Each question is graded as marked.

1. Does the data in “Fuel Volume” contain any outliers?. Show your calculations, and explain your answer (2pts)

#Identify Fuel Volume Variable
fuelvolume = mydata$Fuel.Volume
Upper_lim_fuelvolume = mean(fuelvolume)+3*sd(fuelvolume)
Upper_lim_fuelvolume

[1] 69834.69

lower_lim_fuelvolume = mean(fuelvolume)-3*sd(fuelvolume)
lower_lim_fuelvolume

[1] 55870.83

After finding the values of our upper and lower limits, I used conditional formatting in exel to highlight any outliers in the data, and then I scrolled through to see if any values were highlighted. No values are highlighted, and I manually double checked, of which I could find no outliers.

2. Draw a scatter plot between “Sales” and “Fuel Price”; make Sales the dependent variable. Clearly label the x- and y- axis (1pt)

sales = mydata$Sales
fuelprice = mydata$Fuel.Price
plot(fuelprice, sales, xlab='Price', ylab='Sales', main='Plot of Fuel Price vs Sales')

3. Calculate the correlation coefficient between “Sales” and “Temperature”. What can you tell about the relationship? (1pt)

temp = mydata$Temp
cor(temp, sales)

[1] 0.60106

4. Which of the following is a better predictor of “Sales” a) Fuel Price b) TV c) Radio or d) Temperature? And why? To answer this question, you need to create multiple models and compare them. You need to derive and display the summary of the results from the models; you need to compare the results and explain which of the four variables above is a better predictor of Sales and why; you need to write down the equation representation of the best predicting model; you need to explain the meaning of the coefficients in the equation of the best predicting model. (5pts)

TV = mydata$TV
radio = mydata$Radio
lm1= lm (sales ~ fuelprice)
summary(lm1)

Call:
lm(formula = sales ~ fuelprice)

Residuals:
   Min     1Q Median     3Q    Max 
 -4422  -1732   -235   2084   5124 

Coefficients:
            Estimate Std. Error t value Pr(>|t|)    
(Intercept) 10836.88    3212.67   3.373 0.001062 ** 
fuelprice     105.85      27.74   3.816 0.000237 ***
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 2300 on 99 degrees of freedom
Multiple R-squared:  0.1282,    Adjusted R-squared:  0.1194 
F-statistic: 14.56 on 1 and 99 DF,  p-value: 0.0002367

lm2 = lm(sales ~ TV)
summary(lm2)

Call:
lm(formula = sales ~ TV)

Residuals:
    Min      1Q  Median      3Q     Max 
-5144.2 -1740.8     4.2  1399.7  4906.2 

Coefficients:
             Estimate Std. Error t value Pr(>|t|)    
(Intercept) 22372.760    250.939   89.16   <2e-16 ***
TV             16.740      3.112    5.38    5e-07 ***
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 2167 on 99 degrees of freedom
Multiple R-squared:  0.2262,    Adjusted R-squared:  0.2184 
F-statistic: 28.94 on 1 and 99 DF,  p-value: 5.003e-07

lm3 = lm(sales ~ radio)
summary(lm3)

Call:
lm(formula = sales ~ radio)

Residuals:
    Min      1Q  Median      3Q     Max 
-4952.0 -1999.2   -46.5  1945.3  4644.5 

Coefficients:
             Estimate Std. Error t value Pr(>|t|)    
(Intercept) 22228.547    285.692  77.806  < 2e-16 ***
radio          10.379      2.234   4.646 1.04e-05 ***
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 2232 on 99 degrees of freedom
Multiple R-squared:  0.179, Adjusted R-squared:  0.1707 
F-statistic: 21.59 on 1 and 99 DF,  p-value: 1.043e-05

lm4 = lm(sales ~ temp)
summary(lm4)

Call:
lm(formula = sales ~ temp)

Residuals:
    Min      1Q  Median      3Q     Max 
-5260.9 -1261.4  -208.5  1439.0  4618.6 

Coefficients:
            Estimate Std. Error t value Pr(>|t|)    
(Intercept) 18998.48     577.49  32.898  < 2e-16 ***
temp          226.47      30.26   7.483 3.02e-11 ***
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 1968 on 99 degrees of freedom
Multiple R-squared:  0.3613,    Adjusted R-squared:  0.3548 
F-statistic:    56 on 1 and 99 DF,  p-value: 3.019e-11

Based on our calculations, we can see that the model that is best fit and best predictor of sales is model 4, comparing sales and temperature. Although this is strange, it is the best correlation given our linear models. From it, we can derive the equation: sales = 18998.48+226.47*xtemp The first coefficient is the intercept of our model, which means that this is the starting point of our line. The second coefficient is the slope, or the amount by which sales will increase for every single increase in temperature that we have in our model.

6. Use the best predicting model from question 4, and calculate the error squared in the fitted/predicted “Sales” if the independent variable = 15 ?(1pt)

salespredict = 18998.48 + 226.47*(15)
salespredict

[1] 22395.53

error = salespredict - 26873
error

[1] -4477.47

errorsquared = error^2
errorsquared

[1] 20047738

7. Use the best predicting model from question 4, and predict the value of “Sales” if the independent variable = 33 (1pt)

salespredict2 = 18998.48 + 226.47*(33)
salespredict2

[1] 26471.99

8. The output of the model you have chosen always passes through the mean of x and y. Show calculations to prove this is true in this case (2pts)

mean(sales)

[1] 23063.73

mean(temp)

[1] 17.9505

proof = 18998.48+226.47*(17.9505)
proof

[1] 23063.73

Problem 3: (6 points)

A manufacturing company produces two types of desks, deluxe (X) and standard (Y). Each deluxe desk requires 2.5 hours to assemble, 3 hours for polishing, and 1 hour to package. Each standard desk requires 1 hour to assemble, 3 hours for polishing, and 2 hours to package. The firm can only do 20 hours for assembling, 30 hours for polishing, and 16 hours of packaging per week. Profit is $3 per deluxe and $4 per standard. The company needs to optimize the profit by deciding on how much to produce from each type of desks.

Answer the below by inserting your code and typing your answer following each question. Each question is graded as marked.

# Require will load the package only if not installed 
# Dependencies = TRUE makes sure that dependencies are install
if(!require("lpSolveAPI",quietly = TRUE))
  install.packages("lpSolveAPI",dependencies = TRUE, repos = "https://cloud.r-project.org")

1. What is the linear programming formulation of this problem? Define the decision variables, objective function, and constraints. (4pts)

# We start by creating an object with `0` constraint and `2` decision variables. The object name `lpdesk` is discretionary.
lpdesk <- make.lp(0, 2) 

# Decision variables are X, which is number of X deluxe desks, and Y, which is number of standard desks produced. We will look to maximize Z. Maximizing profit, so objective function is Z = 3x+4y.

#Assembling Constraint = 2.5X + 1Y <= 20
#Polishing Constraint = 3X + 3Y <= 30
#Packaging Constraint = 1X + 2Y <= 16
#Non negativity constraints = X >= 0, Y >= 0

# Define type of optimization as maximum and to avoid the unnecessary screen outputs in the worksheet dump the screen output into a variable called `dump`
dump = lp.control(lpdesk, sense="max")
# Set the objective function with the proper coefficients associated with the decision variables
set.objfn(lpdesk, c(3, 4))

# add a constraint for the maximum allowed time budgets of 20 assembling hrs, 30 polish hrs, and 16 packaging hrs respectively. Then, non negativity constraints.
add.constraint(lpdesk, c(2.5, 1), "<=", 20)
add.constraint(lpdesk, c(3, 3), "<=", 30)
add.constraint(lpdesk, c(1, 2), "<=", 16)
add.constraint(lpdesk, c(1, 0), ">=", 0)
add.constraint(lpdesk, c(0, 1), ">=", 0)

2. Solve for the optimal solution. Write down the optimal values of the objective function and decision variables (2pts)

# View the problem formulation in tabular/matrix form
lpdesk

Model name: 
            C1    C2        
Maximize     3     4        
R1         2.5     1  <=  20
R2           3     3  <=  30
R3           1     2  <=  16
R4           1     0  >=   0
R5           0     1  >=   0
Kind       Std   Std        
Type      Real  Real        
Upper      Inf   Inf        
Lower        0     0        

# Solve 
solve(lpdesk) 

[1] 0

# Display the objective function optimum value
get.objective(lpdesk)

[1] 36

# Display the decision variables optimum values
get.variables(lpdesk) 

[1] 4 6

Based on our model, it seems that the max profit we can attain given our constraints of time is a profit of $36. This is done by producing 4 deluxe and 6 standard desks.