F303 INTERMEDIATE FINANCE  SPRING 1997  HOLDEN, MYSKER, WEDIG
PROJECT 1: EXANTE PORTFOLIO FORMATION
Files
You may use either Excel (5.0 or higher) or Lotus for Windows (4.0 or higher) for steps (1)(3). Steps (4)(8) require the use of the Excelbased Interactive Optimizer. The F303 Home Page provides a data file for this project in both formats and the Interactive Optimizer (see access directions below). The data file contains:
 60 months of returns for twenty three stock indices
 60 months of returns for the U.S. one month Treasury Bill (the riskfree asset) and
 market capitalizations for the twenty three countries that can be used to calculate the weights of a "value weighted" portfolio.
Overview
The project asks you to download the Excelbased Interactive Optimizer plus either the Excel data file or the Lotus data file. Then do the following things:
 pick any five of the twenty three stock indices and calculate their means, standard deviations, and correlations and calculate the average riskfree rate,
 for your five stock indices, calculate portfolio weights for the following three portfolios:
 valueweighted,
 equallyweighted, and
 precisionweighted,
 print out your results for steps (1) and (2), but there is no need to print out the dataset,
 enter the following info into the Excelbased Interactive Optimizer:
 the means, standard deviations, and correlations of the five indices,
 the average riskfree rate,
 the valueweights in the "Own Portfolio" section,
 print the range A1:T14 using a Landscape page orientation – this range contains:
 the MeanStd Dev graph,
 the optimal risky portfolio weights graph,
 the numerical weights, portfolio mean and portfolio standard deviation of:
 the optimal risky portfolio,
 the minimum variance portfolio,
 the "own portfolio,"
 enter the equalweights in the "Own Portfolio" section and print the range A1:T14 using a Landscape page orientation,
 enter the precisionweights in the "Own Portfolio" section and print the range A1:T14 using a Landscape page orientation,
 add a written comment on one of the printed pages as to which of the three portfolios (valueweighted, equallyweighted, or precisionweighted) offers the best riskreturn tradeoff and why.
Turn in your printouts and both of your spreadsheets on an 3 &1/2 inch disc.
Key Dates
Date 
Activity 
1/21 
Kickoff of project 1 
1/24 
Optional computer lab for Project 1, 1:005:00 p.m., in BU415, BU417, BU419, and PV151. 
1/28 
Project 1 due date 
Relevant Spreadsheet Functions
Goal 
Excel Functions 
Lotus Function 
1. To calc. the mean, type: 
=AVERAGE(C3:AZ3) 
@AVG(C3.AZ3) 
 To calc. the std dev of the sample, type:
 (to calc. the std dev of the population,)

=STDEV(C3:AZ3)
=STDEVP(C3:AZ3) 
@STDS(C3.AZ3)
@STD(C3.AZ3) 
To calc. the correlation, type: 
=CORREL(C3:AZ3,C4:AZ4) 
@CORREL(C3.AZ3,C4.AZ4) 
To calc. the sum, type

=SUM(BO3:BO6) 
@SUM(BO3:BO6) 
Relevant Spreadsheet Commands
Goal 
Excel Commands 
Lotus Commands 
1. To lock row and column headings 
Window Freeze Panes 
View Freeze Titles 
2. To cycle relative/absolute versions 
F2 Put Cursor on Term F4 (Repeat) 
F2 Put Cursor on Term F4 (Repeat) 
Directions for downloading the Project 1 dataset and the Excelbased Interactive Optimizer from the F303 Home Page.
 Doubleclick on the Netscape icon.
 Enter the Web address: www.bus.indiana.edu/finweb/f303home.htm
 Click on Download the Project 1 dataset.
 When you see the Netscape message "No Viewer Configured," click on "Save to Disk." Alternatively, if you see the Microsoft Internet Explorer message "Confirm File Open," click on "Save As."
 Click on Download the Excelbased Interactive Optimizer.
 When you see the Netscape message "No Viewer Configured," click on "Save to Disk." Alternatively, if you see the Microsoft Internet Explorer message "Confirm File Open," click on "Save As."
 Open the downloaded files in Excel as usual.
Important Rule for working with files in the UCS Clusters:
Before you walk away from the PC, go into the Window’s Explorer and check that you have saved the final versions of your spreadsheets on your floppy disk. It is easy to accidentially save your files to the Hard Disk and lose your work if you did not save it to a floppy disk.