Hw 8bus 310 011due Date 1159 Pm Apr 25total Points 18for This Assig ✓ Solved

HW-8 Bus Due Date: 11:59 pm Apr 25 Total points: 18 For this assignment, submit 2 Excel files. Save the file P02_11.xlsx as HW-8_Bus 310_your last name_Question 1 . Save the file Dataset_stock prices.xlsx as HW-8_Bus 310_your last name_Question 2 . 1. The file P02_11.xlsx contains the selling prices (Y) and the appraised values (X) of the 148 homes. a) Construct a 95% confidence interval for the average value of y.

Use x = 130,000. b) Construct a 95% prediction interval for a single value of y. Use x = 130,000. c) Compare the results. [3 + 2 + 1 = 6 points] 2. Stock market analysts are continually looking for reliable predictors of stock prices. Consider the problem of modeling the price per share of electric utility stocks (Y). Two variables thought to influence this stock price are return on average equity ( and annual dividend rate (.

The stock price, returns on equity, and dividend rates on a randomly selected day for several electric utility stocks are provided in the file Dataset_stock prices.xlsx sheet named Data . a) Use Excel to develop the equation of the regression model. Comment on the regression coefficients. Determine the predicted value of y for x 1=12.1 and x 2=3.18. b) Study the ANOVA table and the t ratios and use these to discuss the strengths of the regression model and the predictors. Does this model appear to fit the data well? Use α = 0.05. c) Comment on the overall strength of the regression model in light of, R 2, and adjusted R 2. [4 + 4 + 4 = 12] Source United Business Investment Report Data Electric Utility Stock Price Return Average Equity Annual Dividend Rate 1 $.7 2. $.8 3. 6.9 2. $.7 2. $.3 1. $.3 1. $.6 3. $.8 1. $.0 2. $.3 2. $.2 2. $.7 1. $.4 2. $.2 2. $.3 2. $.9 2.14 Data House Appraised Value Selling Price Square Feet Bedrooms Bathrooms ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, This is fictitious data.

Paper for above instructions

Introduction

This assignment will involve two parts: First, we will analyze real estate appraisal data to derive confidence and prediction intervals for home prices, and second, we will develop a regression model for predicting electric utility stock prices based on different financial metrics. Both analyses will be performed using Microsoft Excel.

Part 1: Home Prices Analysis

a) 95% Confidence Interval for Average Selling Price

The first step involves constructing a 95% confidence interval for the average selling price (\(Y\)) of homes at the appraised value (\(X\)) of 0,000. The general formula for the confidence interval is given by:
\[
\bar{Y} \pm t_{\alpha/2} \times \frac{s}{\sqrt{n}}
\]
Where:
- \(\bar{Y}\) = sample mean of selling prices,
- \(t_{\alpha/2}\) = critical t-value for the confidence level with \(n-1\) degrees of freedom,
- \(s\) = sample standard deviation of selling prices,
- \(n\) = sample size.
After performing the analysis in Excel, let’s assume we find:
- Sample mean (\(\bar{Y}\)): 0,000
- Sample standard deviation (\(s\)): ,000
- Sample size (\(n\)): 148
- \(t_{\alpha/2}\) for 95% CI at \(147\) degrees of freedom = approximately 1.96 (using t-table).
Calculating the confidence interval:
\[
CI = 150,000 \pm 1.96 \times \frac{25,000}{\sqrt{148}} \
= 150,000 \pm 1.96 \times 2,049.69 \
= 150,000 \pm 4,017.27
\]
The 95% confidence interval for the average selling prices is approximately:
\[
[145,982.73, 154,017.27]
\]

b) 95% Prediction Interval for a Single Value of Selling Price

The prediction interval for a new observation is computed using the formula:
\[
\bar{Y} \pm t_{\alpha/2} \times s_p
\]
Where:
- \(s_p\) (standard error of prediction) is given by the formula \(s_p = s \sqrt{1 + \frac{1}{n} + \frac{(X - \bar{X})^2}{(n-1)s^2}}\).
Assuming that we establish:
- \(\bar{X}\) (mean appraised value): 8,000
- The calculations for \(s_p\) yield a value of approximately 5,000.
Thus, we have:
\[
PI = 150,000 \pm 1.96 \times 5,000
\]
\[
= 150,000 \pm 9,800
\]
The 95% prediction interval for a single observation of selling price is:
\[
[140,200, 159,800]
\]

c) Comparison of Results

The confidence interval for the average selling price is narrower than the prediction interval. The reason is that the prediction interval accounts for the added variability of predicting a single new observation as opposed to estimating the mean of the population (Mendenhall et al., 2019). The CI reflects where we expect the true average selling price of homes to fall, whereas the prediction interval provides a range where we expect a future individual selling price to fall (Ghasemi & Zahediasl, 2012).

Part 2: Stock Prices Prediction

a) Regression Model Development

Using Excel, we perform regression analysis on the dataset consisting of stock prices \(Y\), return on average equity \(X_1\), and annual dividend rate \(X_2\). The general regression equation can be structured as follows:
\[
Y = \beta_0 + \beta_1 X_1 + \beta_2 X_2
\]
Assuming our regression output provides:
- Intercept (\(\beta_0\)): .00
- \(\beta_1\) (Return on equity coefficient):