Excel 95 % confidence interval

[statlearner123]

Hello everybody,
I am learning about data science at the moment. I want to calculate the 95% confidence interval for the amount of Long-Term Debt that I could expect to have with Long Term Assets of $50,000,000. I calculated a regression analysis for the impact of long-term assets on long-term debt below.


I had to two ideas to come up with the confidence interval. I would appreciate your help which one is correct!

Version 1:
Lower 95% interval:
Y= 0.57*50-20.52 = 8.056965152

Upper 95% interval:
Y= 1.05*50 +4.84 = 57.47532624

Version 2:
=CONFIDENCE.NORM(0.05, 4.283516535, 12)
4.283516535 is the Standard Error from the Regression Statistics table.
Lower confidence: $30.34
Mean: $32.77
Upper confidence: $35.19


Thank you for your help!

 

[gdaem]

My guess would be the second option. Excel calculates confidence intervals for you though, check out this article: https://support.office.com/en-us/article/confidence-function-75ccc007-f77c-4343-bc14-673642091ad6

 

[Dason]

I don't really understand what you're doing with either of your calculations but neither look like you're doing what you should be doing according to the formulas for confidence interval for prediction in simple linear regression. The confidence function gdeam linked is for a population mean whereas you want the confidence at a point on your regression line so this won't do.

Have you googled "confidence interval for prediction in simple linear regression"? You'll get the formula and some examples.