Final Exam 3

BSAD 343, Business Analytics, Spring 2020

About

Clearly show and organise your work. Each answer should appear below the question marked in red. 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!

Part 1: Multiple Questions (15 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 TRUE

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

2. Symmetrical

3. Skew Right TRUE

4. Normal

3. Entering Male into a column field (attribute) labeled Marital Status is what type of business logic integrity violation? (1pt)

1. Field integrity

2. Relational integrity TRUE

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

1. JSON

2. HTML

3. JPEG TRUE

4. ASCII

5. CSV

5. The term high-fidelity data was used in class to mean what exactly? (1pt)

1. Preparing the data

2. Streaming the data

3. Preserving Data Integrity TRUE

4. Garbage-in/Garbage Out

6. Mark the two statements where the combination of words is inconsistent (1pt)

1. Explanatory Quantitative

2. Exploratory Qualitative INCONSISTENT

3. Explanatory Qualitative

4. Exploratory Quantitative INCONSISTENT

7. A target function describes the relationship of independent variable as function of dependent variables (1pt)

1. True TRUE

2. False

8. You are given a 4 x 4 correlation matrix. What is the maximum number of distinct correlation values? (1pt)

10

9. Which of the below is a qualitative description of the data? (1pt)

1. Scatter plot

2. Mean and Range

3. Linear regression line

4. Total number of samples TRUE

10. 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? (2pts)

50 is the intercept, 2 is the slope 11. As part of a linear optimization marketing problem, you are given a constraint in reference to two decisions variables X and Y representing the amount of dollars spent on Radio ads (X) and TV ads (Y). The amount of dollars spent on Radio ads should be at most 30% of the total amount of dollars spent on ads. Write the mathematical formulation for the constraint. (2pts)

X + Y = 1 X ≤ .3 1-X = Y 1-Y = X

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

1. True

2. False FALSE

13. 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

2. When zero values represent missing values TRUE

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

4. None of the above

Problem 2: Linear Regression (15 points)

Residential customers in Austin consume an average of about 1,000 kWh of electricity per month, with usage higher during hot summer months and lower in the winter. Included data show monthly average usage in kWh by month for residential customers taken over a period from 2000-2016, and include monthly electric bill amounts.

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

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

1. Does the data on energy Kwh consumption contain any outliers?. Show your calculations, and explain your answer (2pts) To complete the outlier test, first you need to calculate the quartiles. The median is 668 for the average_kwh data. So, we can calculate that the lower quartile is 643.25, and the upper quartile is 837.75. This means that there is both a lower, and a higher outlier. The lower outlier is 602, the higher outlier is 987.

2. Draw a scatter plot. Clearly label the x- and y- axis (2pts)

mydata = read_csv(file= "energy.csv")

Parsed with column specification:
cols(
  date = col_datetime(format = ""),
  average_kwh = col_double(),
  average_bill = col_double()
)

averageKWH = mydata$average_kwh
averagebill = mydata$average_bill
plot(averageKWH,averagebill)

3. Calculate the correlation coefficient between the energy consumption and monthly bill. What can you tell about the relationship? (2pts)

cor(averagebill,averageKWH)

[1] 0.93997

The Correlation value of 0.93997 is pretty close to 1.0 (which would mean a perfect correlation), so it appears that there is a strong relationship between the two values.

4. Derive a linear regression model and display the summary statistics . Separately, write down the linear equation representation. (3pts)

scatter.smooth(x=averageKWH, y=averagebill)

cor(mydata[c(2,3)])

             average_kwh average_bill
average_kwh      1.00000      0.93997
average_bill     0.93997      1.00000

reg <- lm(averageKWH ~ averagebill)
summary(reg)

Call:
lm(formula = averageKWH ~ averagebill)

Residuals:
    Min      1Q  Median      3Q     Max 
-248.50  -53.37   -5.27   54.67  316.45 

Coefficients:
            Estimate Std. Error t value Pr(>|t|)    
(Intercept) 277.8496    18.8976   14.70   <2e-16 ***
averagebill   7.6840     0.2008   38.27   <2e-16 ***
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 91.98 on 193 degrees of freedom
Multiple R-squared:  0.8835,    Adjusted R-squared:  0.8829 
F-statistic:  1464 on 1 and 193 DF,  p-value: < 2.2e-16

```

5. Interpret the results for the intercept and the slope in the context of the problem. Specify the units when doing so (2pts) The interception according to the summary above is 277.8496 dollars/kmh. This is the point where averagebill would be equal to the average kmh.

6. Calculate the error squared in the predicted energy bill if the average consumption is 903 Kwh?(2pts) The residual error is 91.98 and the std. error is 0.2008. So, if the amount consumed was 903, it would be 9.7 standard deviations above the median. 9.7 multiplied by 0.2008 would give a predicted error of 1.9467

7. A simple linear regression model always passes through the mean of x and y. Show calculations to prove this is true in this case (2pts) The mean of the x data is 728, the mean of the y data is 69.5. Both of these averages are on the linear regression model. ## Data Sources * “The official city of Austin open data portal” - Residential average monthly Kwh and bills: https://data.austintexas.gov/Utilities-and-City-Services/Residential-Average-Monthly-kWh-and-Bills/d9pb-3vh7