Question

Can you use gave a detailed instruction on how to input in excel ?

Reserve Problems Chapter 11 Section 2 Problem 1 The department of health studied the number of patients who need liver transplantation. The following data are the Liver Transplantation Waiting List (LTWL), where y is the size (in number of patients) and x is the corresponding year: y 1362 1778 3031 4001 5010 | 7092 | 79347863 8038 8708 8675 89949325 x 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 Round your intermediate answers to four decimal places (e.g. 98.7654). (a) Fit the simple linear regression model using the method of least squares. Find the estimate of o Round your answer to the nearest integer (e.g. 9876). (b) How does the LTWL size change in average for a year? Round your answer to the nearest integer (e.g. 9876). BE (c) Estimate the number of patients in the list in 2016. (d) Calculate the fitted value of y corresponding to x = 2011. Find the corresponding residual. Round your answer to the nearest integer (e.g. 9876). ỹ= Round your answer to the nearest integer (e.g. 9876). e =

Answer 1

For simplicity, let's assign t=0 to year 1999 and counting number of years after that. Then year 2000 corresponds to the value of t=1 and so on.

Put values for X, 't' and Y in different columns as shown -

X	Y	t
2000	1362	1
2001	1778	2
2002	3031	3
2003	4001	4
2004	5010	5
2005	7092	6
2006	7934	7
2007	7863	8
2008	8038	9
2009	8708	10
2010	8675	11
2011	8994	12
2012	9325	13

Then we need to perform simple linear regression for dependent variable 'Y' and independent variable 't'.

For this, you need to use the 'Data Analysis' add-on from the 'Data' tab to access 'Regression' function as shown -

File Home Insert Draw Page Layout Formulas Data Review View Help Power Pivot Share Le From Text/CSV DE Queries & Connections + Lo Recent Sources Lè Existing Connections Data Analysis 000 From Web Properties A1 14 Z Clear Reapply Advanced EE Group 4 Ungroup Subtotal ? Solver Get Data Sort Stocks Filter Refresh All Geography 欧罗密 From Table/Range Text to Columns What-If Forecast Analysis Sheet Edit Links G Get & Transform Data Queries & Connections Data Types Sort & Filter Data Tools Forecast Outline Analyze B1 Y А B C D E F. G H 1 K M N O P Q R S T U V < 1 X 2 1362 1 Data Analysis ? х 3 1778 2 4 3 OK 5 4 Cancel 5 6 7 6 3031 4001 5010 7092 7934 7863 8038 8708 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 Analysis Tools Covariance Descriptive Statistics Exponential Smoothing F-Test Two-Sample for Variances Fourier Analysis Histogram Moving Average Random Number Generation Rank and Percentile Regression Help 8 7 9 8 10 9 11 10 12 8675 11 13 8994 12 9325 13 14 15

Then a prompt box will open up asking you to input the dependent variable and independent variable. Fill in the corresponding ranges by just selecting the cell region for each 'Y' and 't'. Also make sure to mark the 'Labels' option as we have labels in our first row. Your input should be similar to this -

A B C D E F G H — J K T 1 x Y t 1362 1 N Regression ? 3 1778 3031 4001 2 3 Input Input Y Range: OK 1 4 $B$1:$B$14 4 5 6 7 Cancel 5010 5 Input X Range: $C$1:$C$14 1 6 Help 8 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 7 7092 7934 7863 8038 Labels Confidence Level: Constant is Zero % 8 95 8708 Output options 9 10 11 12 13 14 15 16 17 1 9 10 11 12 13 8675 8994 9325 Output Range: New Worksheet Ply: New Workbook Residuals Residuals Standardized Residuals Residual Plots Line Fit Plots 18 19 Normal Probability Normal Probability Plots 20 21 22 רר

Thats it. You should get the regression output as shown -

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.948612089
R Square	0.899864896
Adjusted R Square	0.890761705
Standard Error	949.6871213
Observations	13
ANOVA
	df	SS	MS	F	Significance F
Regression	1	89154801.78	89154801.78	98.85158599	7.83196E-07
Residual	11	9920961.912	901905.6284
Total	12	99075763.69
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	1393.846154	558.7469035	2.494593071	0.029791278	164.0525111	2623.639797
t	699.9010989	70.39549297	9.942413489	7.83196E-07	544.9616635	854.8405343

Thus, we can use this output to solve the question as shown-

(a)

The estimate of standard error of estimate is: Standard error = $\widehat{\sigma}$ = 949.6871213

Thus, the estimate of $\sigma^2$ is:

( 1 (949.6871213)2 = 901905.6284

Rounding upto nearest integer gives: $\widehat{\sigma}^2 = 901906$

------------------------------------------------

(b)

The regression equation is written as -

$\widehat{Y} = \widehat{\beta_{0}} + \widehat{\beta_{1}}t$

Where $\widehat{\beta_{1}}$ represents the rate of change of dependent variable 'Y' with respect to the independent variable 't'.

From the output, we get the slope coefficient as: 699.9010989

So, LTWL size changes by 700 in average for a year.

------------------------------------------

(c)

For year 2016, t = 2016 - 1999 = 17

And the regression equation is -

$\widehat{Y} = 1393.846154 + (699.9010989)t$

So, for t = 17 we get the predicted value of Y as -

$\widehat{Y} = 1393.846154 + (699.9010989)(17) = 13292.16484$

So, estimated number of patients in list = 13292

------------------------------------------------

(d)

For year 2011, t = 2011 - 1999 = 12

And the regression equation is -

$\widehat{Y} = 1393.846154 + (699.9010989)t$

So, for t = 12 we get the predicted value of Y as -

$\widehat{Y} = 1393.846154 + (699.9010989)(12) = 9792.659314$

So, estimated number of patients in list = 9793

And as the actual number of patients in list in 2011 = 8994 (from data given)

So, error = e = 9793 - 8994 = 799.

____________________________________________

Note that, you would get the same result if you run the regression using 'X' directly as the independent variable. You can try doing it for practice if you want. Its a general habit of parametrizing time in years to smaller scale in order to reduce the variance of data.

Please ask if you have any doubt(s) in comment section.