Docstoc

SBS Investments

Document Sample
SBS Investments Powered By Docstoc
					SBS Investments

Keith Tidwell CIS 300-30 Business Case Study August 10, 2007

Keith Tidwell CIS 300-30

Business Case Study

Portfolio

Background Information:
For this case study we were asked to put a portfolio together for Steve Cheathem, the owner of SBS Investments, Inc. He provides investment advice in such areas as tax advantage planning, retirement and estate planning, mutual funds, pensions, stocks, and risk management. Mr. Cheathem has been helping his clients manage their portfolios, which range from $100,000 to well over $5,000,000 for the last 15 years. This portfolio that I have compiled together for Mr. Cheathem will provide him with current data about his client’s investments, thus helping him keep better track of relative performance. The Portfolio Worksheet organizes the client’s equity (stock) information into an Excel list with up-to-date stock information from the web. Equipped with Pivot Tables and Charts, this Portfolio will be easily understood even by the clients themselves.

Problem Statement:
Mr. Cheathem has provided me with a partially completed worksheet template, as well as a database that includes the values for the symbol, company, industry, number of shares, purchase date, and purchase price for the portfolio he requires. Given the information he has already provided me I don’t believe this should be too much of a task to bare. Mr. Cheathem has already given me all I need and then some. I suspect I will transfer his database values over to an Excel worksheet. Then download the current market information for these respective securities from an on-line web query. With this current data in another worksheet, I should be able to transfer certain price quotes and ratios directly over to my “Analysis” page that will make the portfolio for Mr. Cheathem. In addition to these ratios will also be certain percentages that I feel will be of great need to Mr. Cheathem, which I will calculate given the information from my “Web Query” worksheet. I will then need to format all of the cells and values to make them more appeasing to the eye, so that even the clients can look at the portfolio and make since of it. Mr. Cheathem had specified that he would like to know the stock averages, minimums, maximums for the commission, purchase price, current value, return, and P/E ratio columns. He also requested the minimums and maximums for the portfolio percentages, current market price, and EPS columns. So for these I will make a separate worksheet titled “KeyInfo”. To wrap up the portfolio I will have provided the Pivot Tables and Charts that Mr. Cheathem had specifically asked for. I will likewise have answers to the ten specific questions Mr. Cheathem asked me to provide him answers for. This project will not only be thorough and detailed, but it will be so easy to understand that Mr. Cheathem will be able to show it to the Clients themselves and they will be able to make since of what he is advising them to do with there securities.

1

Keith Tidwell CIS 300-30

Business Case Study

Portfolio

Methodology and Analysis:
I took the database Mr. Cheathem provided me and I Imported the given values into an Excel worksheet. I did so by using the File> Export from the database into Excel under the name “Stock.xls”. I then closed the database, opened my “Stocks.xls” workbook and copied into clipboard. Where then I pasted the data from clipboard into my “Portfolio” worksheet as well as inputting into my “Analysis” worksheet where I will later be doing most of my work. From here I went to Data>Import External Data>Import Data>MSN MoneyCentral Stock Quotes>=Portfolio!B4:B103>=WebQuery!A1. This gave me my current market information I was looking for. After I obtained these quotes, I needed to transfer them over into my “Analysis” worksheet. This was easily done for the Market Capitalization, last price, P/E Ratio, and EPS values, as they just needed to be copied over from the “WebQuery” worksheet. It was when obtaining the values for Total Return, Annualized Gain (Loss) Percent, Holding Period (Years), Percentage of Total Portfolio, Current Market Value of Stock, Total Commission, and Total Cost (Including Commission) that I actually had to provide calculations. These calculations are as follows:  Total Return: =IF(K4="","",(K4-G4)/G4)  Annualized Gain (Loss) Percent: =IF(K4="","",(K4/J4)^(1/N4)-1)  Holding Period (Years): =IF(K4="","",ROUND(((TODAY()-F4)/365),2))  Percentage of Total Portfolio: =IF(K4="","",L4/$L$105)  Current Market Value of Stock: =IF(K4="","",K4*E4)  Total Commission: =IF(K4="","",IF(1.5%*(G4*E4)<12,12,IF(1.5%*(G4*E4)>75,75,1.5%*(G4*E4))))  Total Cost (Including Commission): =IF(K4="","",(G4*E4)+H4) After inputting the calculations, I made some additional cell formatting changes such as rounding values to two decimals by way of “Cell Format”. I added some Grand Totals to the bottom, as well as the Annualized Return. I did that with the following formula: =SUMPRODUCT(M4:M103,O4:O103).

2

Keith Tidwell CIS 300-30

Business Case Study

Portfolio

I then moved on to the “Key Info” worksheet where I provided the following tables: stock averages, minimums, maximums for the commission, purchase price, current value, return, and P/E ratio, as well as the minimums and maximums for the portfolio percentages, current market price, and EPS.

These were simply done by using the AVERAGE, MIN, and MAX formulas. The Annualized Return was obviously just transferred from the Analysis worksheet. These are very simple to understand so the Mr. Cheathem as well as the Client should understand each other very well when they get to this page.

3

Keith Tidwell CIS 300-30

Business Case Study

Portfolio

This brings me to the answering of Mr. Cheathem’s questions. Mr. Cheathem provided me with ten questions that he wanted exact and (for some) visual-based answers to. They were as follows: 1) Which Stock has performed the best over time (highest annualized gain)? Lowet Return (loss)? For this answer I used the Index and Match function with MAX & MIN to determine the given results:
Highest Lowest Heritage Oil Corporation AB Volvo (ADR)

2) Which Industry has performed the best over time (highest annualized gain)? Lowet Return (loss)?

For this answer I used the Index and Match function with MAX & MIN to determine the given results:
Highest Lowest Oil & Gas Auto

4

Keith Tidwell CIS 300-30

Business Case Study

Portfolio

3) Show a list of which, if any, stocks comprise more than 1% of the portfolio? Highlight all stocks in green that have an annualized return (gain) greater than 250%. And also highlight in red those stocks that have an annualized loss that is more than 75%. For this answer I used Auto Filter to show all the Stocks that comprise more than 1% of the portfolio. Once I did that I copied the results over to this worksheet. I then Conditional Formatted my analysis table to highlight all in green that had an annualized return (gain) greater than 250%. But there weren't any. I then did the same but to highlight in red the stocks with annualized loss more than 75%.
Company Percentage of Total Portfolio

Archer Daniels Midland Company C.R. Bard, Inc. Brilliance China Automotive Hldg. (ADR) Cummins Inc. Campbell Soup Company Cytyc Corporation Eaton Corporation Advanced Medical Optics, Inc. Flowserve Corporation General Mills GlobalSantaFe Corporation Hercules Offshore, Inc. Hewlett-Packard Company Heritage Oil Corporation International Business Machines Corp. IDEX Corporation Johnson Controls J.C. Penney Company, Inc. Kohl's Corp. Nordson Corporation Nobel Drilling PACCAR Inc Peerless Mfg. Co. Pentair, Inc. Pfeiffer Vacuum Technology AG (ADR) Robbins & Myers, Inc. Roper Industries, Inc. Sears Holdings Corporation Bayer Schering Pharma AG (ADR) Walmart

2% 1% 1% 4% 1% 1% 4% 1% 2% 1% 1% 1% 4% 1% 4% 2% 1% 2% 2% 1% 4% 2% 1% 1% 4% 2% 3% 4% 3% 5%

5

Keith Tidwell CIS 300-30

Business Case Study

Portfolio

4) Show a list of which, if any, stocks comprise less than 1% of the portfolio and the annualized return is greater than 100% or the annualized loss is more than 75%. For this answer I used Custom Auto Filter to find which stocks comprise less than 1% and an annualized return greater than 100% or the annualized loss more than 75%. [There were no stocks however, that met such criteria.]

5) Provide a table and accompanying chart to show the average return and the average P/E ratio by industry. What significant observations can you offer regarding this data? From this data provided by my tables and charts you can see that the Industry's: Oil&Gas, Food, and Computers yield the highest Average of Total Returns. Likewise, when looking at the P/E Ratio, they all do the same here as well, with the exception of the Auto Industry creeping up in the top three.

6

Keith Tidwell CIS 300-30

Business Case Study

Portfolio

6) If you had to select only three industry groups from this portfolio to focus on which ones would you choose? Provide some rationale for your response.

I would choose Oil&Gas, Computers, and Food. If you reference to Pivot Table you will notice that they have the highest percentages in Average P/E ratio, Average Total Return, as well as a higher Average Annualized Gain, in comparison to the rest of the industries.
Data Average of Annualized Gain (loss) percent (0.12) 0.11 (0.08) 0.17 0.05 (0.09) 0.05 0.24 0.01 0.11 0.05 Average of P/E Ratio 22.46 23.31 15.87 20.45 17.55 12.76 20.34 32.85 13.64 19.99 Average of Total Return (0.12) 0.18 (0.16) 0.30 0.16 (0.07) 0.10 0.49 0.05 0.12

Industry Segment Auto Computers Drugs Foods Health Services Insurance Manufacturing Oil & Gas Retail (blank) Grand Total

Outside of statistical reasons, it would make sense that these three will constantly be on the incline. We’re human, so we need food to survive. We drive everywhere, so Oil and Gas are very important, not too mention the increase in Gas prices we’ve had to deal with for the last few years. And in modern day society we depend on our Computers. So it only makes sense that these three would be among the best industries to focus on. 7) Of the “Mega Cap” firms, which has the highest return, PE ratio, and EPS? Provide a formula that correctly shows these three values and will also work for any new portfolio data. Due to the fact that there are no "Mega Cap" frims, I used the function ISNA to verify this. However if there was I would create a formula using the index and match to indicate which firm has the highest Return, P/E, and EPS.

7

Keith Tidwell CIS 300-30

Business Case Study

Portfolio

8) What are the "Small Cap" holdings that represent more than $25,000 in Current Total Market Value? By using Auto Filter I was able to single out the "Small Cap" firms, then I used Custom Auto Filter to narrow down my results to just the ones that had a Current Total Market Value greater than $25,000.
Company Current Total Market Value of Stock Market Cap Classification

Pfeiffer Vacuum Technology AG (ADR) Robbins & Myers, Inc.

$ $

79,050.00 31,999.50

SmallCap SmallCap

9) Based on Percentage of Total Portfolio, identify the top five and also the lowest five companies with which the client currently holds investments in. Considering the overall diversity of this client's portfolio, do you feel that these weightings are appropriate? Provide rationale for your answer. By way of Auto Filter I was able to designate a "Top Five" as well as a "Bottom Five". However I do not feel that these are necessarily the best and worst overall. Even though the Percentage of the Total Portfolio states these figures, they still aren't the best stocks in comparison to the others if you take in to consideration the Annualized Gain and your ROI.
Top Five
Company

Bottom Five
Company

Cummins Inc. Nobel Drilling Pfeiffer Vacuum Technology AG (ADR) Sears Holdings Corporation Walmart

Atlantic American Corporation Bonso Electronics International Inc. Gateway Energy Corporation Sun Microsystems, Inc. Titan Logix Corp.

8

Keith Tidwell CIS 300-30

Business Case Study

Portfolio

10) Based on the information provided in the Portfolio worksheet and your own analysis of this data, which stock(s) would you recommend be eliminated from the portfolio? What is your rationale for this decision? Given the information in this portfolio, I would recommend that the following stocks be eliminated. After searching through the Annualized Gain (loss) percent as well as the Total Return, these are the 10 worst stocks because they yield the greatest loss overall.
Company Annualized Gain (loss) percent Total Return

AB Volvo (ADR) AXA (ADR) The Gorman-Rupp Company IDEX Corporation ABIOMED, Inc. Bonso Electronics International Inc. IPC Holdings, Ltd. Kohl's Corporation Rackable Systems, Inc. Spartan Motors, Inc.

(0.61) (0.39) (0.47) (0.31) (0.47) (0.32) (0.36) (0.31) (0.37) (0.31)

(0.68) (0.36) (0.46) (0.45) (0.43) (0.41) (0.31) (0.33) (0.32) (0.35)

A good number of these stocks just happen to be in the auto industry. Its my feeling that it wouldn’t be a bad idea to just pull out of the auto industry all together because these are not doing to well at this time anyhow, especially AB Volvo. A lot of people nowadays resort to buying used cars, and/or smaller gas efficient vehicles considering the hike in gas prices lately. Likewise a good reason why some of these stocks that happen to be in the manufacturing industry are probably doing so poorly would be because many specifically American Manufacturing companies are outsourcing to save cost. America can have a foreign country produce our goods for us for a lot cheaper price than we would could here with all the salaries and minimum wages required of them to provide.

Conclusion:
Hopefully after reading this portfolio Mr. Cheathem will be able to show and explain into great detail with ease how it is we here at SBS Investments advise our clients how to invest their money most efficiently and wisely. We try to provide all the facts to our clients and make it look easy. That is, easy for even the most non-experienced client to read and understand.

9


				
DOCUMENT INFO