Programming Using Advanced Excel

Assignment: 1

You have a data set known as “food” dataset which indicates the relationship between food expenditure and income[1] of households with three family members. Do the following in MS Excel.

1. What is the t_c with 95% confidence and 40 observations

Ans:

=TINV( α, df)

Where α is level of significance and df the degrees of freedom

2. What is the interval estimate of

Ans:

[2]

If confidence intervals are wider (Implying larger standard error) it suggests that there is not much information in the sample about . If an interval estimate is narrow, it suggests that we have learned more about .

3. Define Null hypothesis and based on Null explain what are the various alternatives to rejecting Null hypothesis?

If Null hypothesis H0: = c (in significant test case c=zero) is true, then the test statistic follows a t distribution with m= N-2 degree of freedom:

When we reject null hypothesis H₀, there are three alternatives

H1: β_k > C (1) {C = 0 in significance test case}

H1: β_k < C (2)

H1: β_k ≠ C (3)

· If alternative hypothesis (1) is true, then the value of computed test statistic will tend to be unusually large. We will reject H₀ if the test statistic is in the right tail of the distribution.

· If alternative hypothesis (2) is true, then the value of computed test statistic will tend to be unusually small. We will reject H₀ if the test statistic is in the left tail of the distribution.

· If alternative hypothesis (3) is true, then the value of computed test statistic will tend to be unusually small or large. We will reject H₀ if the test statistic is in the left tail or the right tail of the distribution.

4. Do the test of significance if α = 0.05; H0: β₂ = 0 and H₁: β₂ > 0. Which test is suitable?

5. Do the test of significance if α = 0.01; H0: β₂ = 5.5 and H₁: β₂ > 5.5. Which test is suitable?

6. Do the test of significance if α = 0.05; H0: β₂ = 15 and H₁: β₂ < 15. Which test is suitable?

7. Do the test of significance if α = 0.05; H0: β₂ = 7.5 and H₁: β₂ ≠15. Which test is suitable?

8. What is the alternate test to comparing the test-statistic value to the critical value(s)?

9. Do the p- value test, if α = 0.01; H0: β₂ = 5.5 and H₁: β₂ > 5.5.

10. Do the p- value test, if α = 0.05; H0: β₂ = 5.5 and H₁: β₂ < 5.5.

11. Do the p- value test, if α = 0.05; H0: β₂ = 7.5 and H₁: β₂ ≠ 7.5.

Is the regression model (estimated by inbuilt Excel Package) using single tail or two tail test?

Reference:

Excel_GENEVIEVE BRIAND, R. CARTER HILL-Using Excel For Principles of Econometrics-Wiley (2011)

Assignment: 2

Estimate the food expenditure model for “food data”.

1. What are the predicted values of Y.

2. Given X = 20, predict the value of Y.

3. What are the prediction intervals for the predicted Y.

4. What is correlation coefficient between Food expenditure and Income?

5. What is coefficient of determination R²?

R² = SSR / SST = 1 – SSE/ SST

SSR is sum square due to regression

SST is the total sum of squares

SSE is the sum of squared errors or sum of squared residuals

Or

R² = r² _xy

Changing the scale:

y = b1 + b₂x + e

6. If Scale of X is changed (x is multiplied by a constant 100)

Y = B1 + B₂/Cx . C + e

7. If Scale of Y is changed (Y is divided by a constant 100)

Y /C = B1/C + B₂ /C x + e / C

8. If Scale of both Y and X is changed (Both x and y are multiplied by 4)

Y *C = B1*C + B₂ x * C + e *C

Excel_GENEVIEVE BRIAND, R. CARTER HILL-Using Excel For Principles of Econometrics-Wiley (2011)