HomeView your syllabusHelp with your projectDownload CenterCommonly asked questionsWeb links and sites

An overview of the Portfolio Model About Formulas About Functions Quick Fx Guide - detailed info about required functions How and where to go download investment data

 

Sac State Home   College of Business 
Client Portfolio Model 

This page was designed to help you build the client page project demanded of you in your class.  Included along the way are specific hints.
   

The basic model developed in your class allows a client (or user) to select different assets within a portfolio and determine what proportion to hold in each asset/asset class to achieve a pre-determined rate of return.

The beauty of the model is found in its ability to be retrofitted to any number of applications and circumstances. The techniques and methods allow a wide assortment of uses and varied means to present data - limited only by your ability and creativity. 

          
 List of Client Project Sheets. The Client Portfolio Model uses a minimum of 8 worksheets within the workbook to work. 

They are (Click on link for specific info):

 

Raw Data Sheet

This sheet is the location where the downloaded information goes. On this sheet the monthly (weekly, daily, etc) stock quotes and paid dividends. The information is best sourced from the MSN MoneyCentral website. 


   
Top

 

Financial Data

This sheet includes the various companies, their ticker and associated financial information. The info included is up to you but should include: recent price, P/E Ratio, Dividend, EPS, Market Cap, Average Volume, Industry, and Homepage. You find the information from the web (Yahoo, Fool, MoneyCentral, etc) and insert the relevant and CORRECT information here.

 

Data Sheet

The Data Sheet provides the model with two functions. First, this sheet collects the raw data and finds the monthly returns for each company. Using the closing prices and any dividends paid out you assemble the returns and can discover any number of things: How the company moves in relation to other stocks, or the market. Or, it can be graphed showing the stock's history or its trend.

The second function of the Data Sheet is to provide a means of isolating the specific stocks that are included in the portfolio. For instance, if there are 5 stocks in the portfolio, then those specific 5 stocks (and their returns) are selected. Once these returns are isolated, you can use Statistics (Covariance) to determine how these stocks interact and relate to each other.

This sheet is the first one which links data from other sheets and builds formulas that use data from other sheets. For instance, the stocks from the portfolio are found on the Client Sheet. To enter them on the data sheet, simply choose what cell you want to place them, and type :

= and then click <Client> Tab, and then ticker for the first company, and press <Enter>. Moving to the right one cell, repeat for the second company, etc.


   
Top

 

Matrix Sheet

This is where the model gets interesting! The matrix sheet brings the information from the data sheet and manipulates it using matrix algebra to derive various matrices.  Huh? 

Remember that the data sheet isolates the selected companies and their returns. Next you find the covariances between Stocks A, B, C, D, and E. As shown: 

Cov (AA) Cov (AB) Cov ( AC) Cov (AD) Cov (AE)
Cov (BA) Cov (BB) Cov (BC) Cov (BD) Cov (BE)
Cov (CA) Cov (CB) Cov (CC) Cov (CD) Cov (CE)
Cov (DA) Cov (DB) Cov (DC) Cov (DD) Cov (DE)
Cov (EA) Cov (EB) Cov (EC) Cov (ED) Cov (EE)

With the resulting information, you develop a system that allows the user to create any number of hypothetical portfolios and displays the level of risk for each possible one.

 

Equity Sheet

This sheet will determine what proportions of the selected stocks will make up an ALL-EQUITY (no Bonds) portfolio. This sheet separates into two sections: All-Equity - no constraint, and All-Equity with Constraint. 

The graph made up of the various returns in this Bond Sheet and the Standard Deviation (risk) creates the Efficient Frontier. Of course there would be two Frontiers> One has no constraint, and a lower one w/ constraint.


   
Top

 

Bond&Equity Sheet

This sheet determines the proportions of various assets of a portfolio that includes both Equity and Bonds. Similar to the Equity sheet there are two sections (w/ Constraint, and w/o Constraint). An added element on this sheet is the finding of the slope. The slopes allow you to find the maximum slope. This represents the minimum variance portfolio. Or the portfolio along the frontier with the lowest risk.

Along with the Bond Sheet, this sheet determines the proportions used within the portfolio. As a result is the heart of the model.

 

Client Sheet

This is the "Client Interface". This sheet contains the various options that can be changed such as what assets, constraints, returns, etc desired. At a minimum, this sheet will show what assets make up the portfolio, the rate of return, and proportions. However much additional information you include is up to you, your design, and client needs.


   
Top

 

History Sheet

This sheet shows each company's history, information, and financial results. For each company, create a snapshot regarding what the company does, and how it is doing. As a result, the client can check more detailed info regarding the various companies in the portfolio.


   
Top