Ticker | Company | Current Price | 12 Month Risk/Reward | Curr Price/ 52Wk High | Curr Price/ 52Wk Low | % Ch Price Year-to-Date | Div Yield | Anl EPS before NRI | Current Fiscal Yr Cons Est | Next Fiscal Yr Cons Est | P/E using 12 mo EPS | P/E using 12 mo Est | Div 5yr Growth | 5 Yr Hist EPS Gr | Next 3-5 Yr Est EPS Gr rate | Price/ Sales | Price/ Book | Zacks Rank | Avg Broker Rating | Fiscal Month End |
MCD | Mcdonalds Corp | 90.25 | 0.89 | 1.10 | -10.05 | 3.10 | 5.27 | 5.55 | 6.15 | 16.84 | 15.38 | 18.04 | 15.54 | 9.86 | 3.34 | 6.27 | 3 | 1.83 | 12 | |
CMCSA | Comcast Corp A | 31.35 | 0.97 | 1.59 | 32.22 | 2.07 | 1.58 | 1.90 | 2.22 | 18.77 | 15.14 | 22.84 | 21.03 | 14.66 | 1.44 | 1.77 | 3 | 1.35 | 12 | |
DIS | Disney Walt | 47.36 | 0.97 | 1.63 | 26.29 | 1.27 | 2.54 | 3.01 | 3.46 | 17.22 | 14.11 | 11.15 | 5.63 | 12.60 | 2.04 | 2.13 | 2 | 1.92 | 9 | |
OMC | Omnicom Grp | 47.61 | 0.92 | 1.32 | 6.80 | 2.52 | 3.33 | 3.68 | 4.10 | 14.17 | 12.21 | 17.66 | 0.75 | 11.55 | 0.93 | 3.20 | 3 | 2.31 | 12 | |
TWX | Time Warner Inc | 37.73 | 0.97 | 1.36 | 4.40 | 2.76 | 2.89 | 3.21 | 3.64 | 12.58 | 10.98 | 7.68 | -2.71 | 11.27 | 1.24 | 1.23 | 3 | 1.81 | 12 | |
HD | Home Depot | 52.22 | 0.99 | 1.83 | 24.22 | 2.22 | 2.47 | 2.90 | 3.31 | 20.01 | 16.93 | 4.43 | 0.91 | 13.48 | 1.12 | 4.43 | 3 | 1.74 | 1 | |
TGT | Target Corp | 59.23 | 1.00 | 1.27 | 15.64 | 2.03 | 4.32 | 4.29 | 4.84 | 13.52 | 13.06 | 20.99 | 7.39 | 11.06 | 0.56 | 2.50 | 2 | 1.76 | 1 | |
SYY | Sysco Corp | 29.21 | 0.93 | 1.15 | -0.41 | 3.70 | 1.98 | 2.02 | 2.10 | 14.11 | 14.44 | 6.36 | 4.01 | 6.33 | 0.55 | 3.50 | 3 | 2.80 | 6 | |
WMT | Wal-Mart Stores | 72.11 | 1.00 | 1.49 | 20.67 | 2.20 | 4.49 | 4.90 | 5.36 | 15.71 | 14.12 | 13.62 | 9.40 | 9.65 | 0.54 | 3.32 | 2 | 2.18 | 1 | |
GIS | Genl Mills | 38.65 | 0.94 | 1.11 | -4.36 | 3.41 | 2.56 | 2.67 | 2.90 | 15.16 | 14.35 | 11.74 | 10.02 | 7.72 | 1.50 | 3.63 | 4 | 1.71 | 5 | |
KO | Coca Cola Co | 77.98 | 0.99 | 1.22 | 11.45 | 2.62 | 3.84 | 4.07 | 4.46 | 20.10 | 18.25 | 8.11 | 8.50 | 7.79 | 3.73 | 5.32 | 3 | 1.69 | 12 | |
PEP | Pepsico Inc | 69.87 | 0.99 | 1.16 | 5.30 | 3.08 | 4.40 | 4.05 | 4.43 | 16.02 | 16.44 | 7.51 | 6.47 | 4.19 | 1.63 | 4.81 | 4 | 1.67 | 12 | |
AVP | Avon Prods Inc | 15.81 | 0.55 | 1.05 | -9.50 | 5.82 | 1.64 | 0.94 | 1.17 | 11.63 | 14.88 | 4.71 | 0.90 | -1.80 | 0.61 | 4.13 | 5 | 2.85 | 12 | |
CL | Colgate Palmoli | 103.90 | 0.99 | 1.30 | 12.46 | 2.39 | 5.03 | 5.37 | 5.88 | 20.33 | 18.42 | 12.95 | 11.06 | 8.20 | 2.93 | 19.96 | 3 | 2.48 | 12 | |
CLX | Clorox Co | 72.33 | 0.97 | 1.13 | 8.67 | 3.32 | 4.16 | 4.27 | 4.55 | 17.39 | 16.92 | 9.99 | 5.10 | 6.86 | 1.74 | #N/A | 3 | 3.07 | 6 | |
PG | Procter & Gambl | 61.73 | 0.91 | 1.05 | -7.47 | 3.64 | 3.93 | 3.91 | 4.26 | 15.79 | 15.76 | 10.17 | 4.67 | 7.01 | 1.99 | 2.63 | 4 | 2.22 | 6 |
<colgroup><col><col><col><col span="3"><col span="2"><col><col span="3"><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Hey guys. I'm new here so I am sorry if I break any forum rules.
My question is as follows: I have an add-in that automatically generates the above data for me. There are many more columns and rows in the actual spread sheet, but it contains some proprietary data. What I would like to do is create a ranking system for these companies using the data in the columns. I would like to give certain columns more weight than others and in the end have useful data(i.e. a flat 1-100 ranking, or maybe they each have a score out of 20). The problem is that in some columns a low number is good while in another a high number is good, or a low absolute value is the best.
I am relatively proficient in excel so even if you just point me to a good formula or outline a possible way I would appreciate it.