I need to analyze data (portion of the retirement investment portfolio) and graph the not so obvious info (such as total value) in a bar, pie, and line graph.
I need to do this in excel and come up with formulas, name for the graphs, Y' and X's.
What information should I be looking for as an investor (that's meaningful and not obvious)?
Which info is suitable with which graph?
Data is as follows:
Stock # Shares 9/30/96 10/31/96 11/30/96 12/31/96 1/31/97 2/28/97
CHV 65 62.63 65.75 67.00 65.00 63.38 64.50
GE 38 91.00 96.75 104.00 98.88 103.50 102.88
GM 50 48.00 53.63 57.63 55.75 59.00 57.88
IP 41 42.50 42.63 42.50 40.50 40.88 41.63
MRK 30 70.38 73.88 83.00 79.63 90.63 92.13
MMM 45 69.75 76.38 83.75 83.00 82.25 92.00
I'll assume you have all of your data in a Excel spreadsheet with each name, number, price, or date in a separate cell. In my spreadsheet I have the stocks in rows and the date is different for each column.
Your suggestion of a looking the "Total Value of Portfolio" is a good start. In this case, what is important is how the the value is change with time. So I would construct a line graph with the dates along the x axis and the total value in dollars of the portfolio at each time. You will need a formula that sums the product of the # of shares and the price for each equity.
An example of such a formula would be:
(of course your cells might be ...
This works with analyzing data and information that investors should look at. Graphing data for investors are provided.