[silvergray545]

I've run into a problem that I can't seem to figure out. I'm working on a case study that deals with normal distribution. Our professor wants us to use excel to get our answers but when I put the formula in excel, I get a different answer than I do by doing it by hand. I'll explain.

The mean and standard deviation are already given. There are a few numbers that you need to plug in for X. The excel command is =NORMDIST(x,mean,standard deviation,1)

To solve it by hand, you need to find the z score with the given values. The formula for that is z = (x-mean)/standard deviation then you take the z score and look it up on the chart to find the percentage.

With that being said, I'll give you an example of one of the problems. x = 20%, mean = 8.8% and the standard deviation = 5.98%.

Plug the values into excel and the answer is 0.969459572.

Do the math by hand (.2-.088)/.0598 which gives you a z score of 1.87. Then look it up on the chart and it is .4693

.97 and .47 are two completely different numbers. Anybody know what I'm doing wrong?

Below is the assignment if you need further clarification. Thanks

"Question One (twenty points)
You are hired as a consultant by the Alliance Financial Company. Alliance’s chief financial officer (CFO), Ms. Gail Roberts, has asked you to aid her in determining the appropriateness of a number of different investment categories for several Alliance customers. The table below shows the mean return and the standard deviation for nine different investment classes of investments sold by Alliance over the last 10 years. It is known that the returns follow a normal distribution.

Investment class Mean Return (%) Standard Deviation (%)
Fixed annuities 8.31 0.54
Cash equivalents 7.73 0.81
U.S. Treasury bonds 8.80 5.98
U.S. Investment-grade corporate bonds 9.33 7.92
Non-U.S. government bonds 10.95 10.47
Domestic large cap stocks 11.71 15.30
International equities 14.02 17.16
Domestic mid-cap stocks 13.64 18.19
Domestic small cap stocks 14.93 21.82

(i) Ms. Roberts has asked you to develop a table with some information that could be used in generating recommendations for different customers. Specifically, what she would like to know for each investment class is: what is the probability of a return for
(1) Greater than 5%,
(2) Greater than 20%,
(3) Greater than 50%,
(4) A loss (a return of less than 0)
(5) A loss of more than 10%, and
(6) A loss of more than 20%

Summarize the results in a table format"

[BTM]

If this can wait until tomorrow morning when my wine blood content isn't what it currently is I can probably help you on this.

[silvergray545]

It's due tomorrow at 5 lol

[BTM]

PM right

[silvergray545]

Yes

[BTM]

OK. I'll see what I can do tomorrow

[ghosthi32]

you would btm

[silvergray545]

Thanks.

Also, for anyone else that wants to try, when I do a lot of the functions in excel, I'm getting a lot of 1's and 0's as answers. Not sure why.

[BTM]

Originally Posted by silvergray545

Thanks.

Also, for anyone else that wants to try, when I do a lot of the functions in excel, I'm getting a lot of 1's and 0's as answers. Not sure why.

Are they supposed to be yes or no answers?

[silvergray545]

Originally Posted by BTM

Are they supposed to be yes or no answers?

No, you have to find the probability the x values.

[BTM]

In the meantime check your decimal places (simple stuff has confounded me before as well)...this week has been slow I'll take a look tomorrow

[DougLikesBMW]

Check www.khanacademy.org

[Buegie]

the .47 number only represents the area to the right of the mean, you need to add .5 to it if you want the cumulative percentage (note: .47+.5=.97). Excel returns the whole thing, but with the formula, you need to "finish it off" so to speak. See if the graph below makes it clear, if not I'll try to explain more tomorrow.

[Buegie]

Originally Posted by silvergray545

Thanks.

Also, for anyone else that wants to try, when I do a lot of the functions in excel, I'm getting a lot of 1's and 0's as answers. Not sure why.

that could just be due to excel not displaying enough figures. If I know what you're talking about, excel is just rounding the number for display purposes to either 0 or 1. Try adding decimal points w/ this button w/ arrow pointing to left:

[Quick6EF]

im 23 and graduated college wat is this

[silvergray545]

Ok, here's a more detailed post. I think I figured out the problem. You need to subtract 1 from the equation on excel to get the probability. But I still don't understand why I'm getting 1's, 0's and other weird numbers. Doing it by hand doesn't work either. The z scores are completely off the chart. I'll incude the case study and the work I've done.

I appreciate the responses guys. Thanks

[silvergray545]

Originally Posted by Buegie

the .47 number only represents the area to the right of the mean, you need to add .5 to it if you want the cumulative percentage (note: .47+.5=.97). Excel returns the whole thing, but with the formula, you need to "finish it off" so to speak. See if the graph below makes it clear, if not I'll try to explain more tomorrow.

Ahh you can just add .5 to .47 to get the answer! My prof wants us to subtract .47 from .5 which gives us .03 and then subtract that from 1, which then gives us .97 lol. I can't believe I didn't notice the shortcut.

[silvergray545]

Originally Posted by DougLikesBMW

Check www.khanacademy.org

Thanks for that site! I bookmarked it!

[fdsasdasdf]

Statistics major here. PM me with your questions in the future, I can always use practice to not forget the beginning stuff.

"You never know if you really know something unless you can successfully teach it to someone that really doesn't want to be there."
- Some grad student in the stats department

[silvergray545]

Originally Posted by Zell

Statistics major here. PM me with your questions in the future, I can always use practice to not forget the beginning stuff.

"You never know if you really know something unless you can successfully teach it to someone that really doesn't want to be there."
- Some grad student in the stats department

Really? Thanks a lot man. I'm gonna send you a friend request. Does that chart that I created look right to you?

[BTM]

Looks like you got this figured out. Zell is much better at statistics than I am...more of an occasional dorky hobby for me and took a fair amount in undergrad...but yea I've seen some of Zell's posts and he knows whats up.

[NYCGP]

B's pants shrink when he sees statistics...