F303 INTERMEDIATE FINANCE - SPRING 1997 - HOLDEN, MYSKER, WEDIG

PROJECT 1: EX-ANTE 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 Excel-based 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:

 

Overview

 The project asks you to download the Excel-based Interactive Optimizer plus either the Excel data file or the Lotus data file. Then do the following things:

 

  1. pick any five of the twenty three stock indices and calculate their means, standard deviations, and correlations and calculate the average riskfree rate,

    2.  

  2. for your five stock indices, calculate portfolio weights for the following three portfolios:

 

  1. print out your results for steps (1) and (2), but there is no need to print out the dataset,

    2.  

  2. enter the following info into the Excel-based Interactive Optimizer:

 

  1. print the range A1:T14 using a Landscape page orientation – this range contains:

 

  1. enter the equal-weights in the "Own Portfolio" section and print the range A1:T14 using a Landscape page orientation,

    2.  

  2. enter the precision-weights in the "Own Portfolio" section and print the range A1:T14 using a Landscape page orientation,

    3.  

  3. add a written comment on one of the printed pages as to which of the three portfolios (value-weighted, equally-weighted, or precision-weighted) offers the best risk-return trade-off 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:00-5: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)
  1. To calc. the std dev of the sample, type:
  2. (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 Excel-based Interactive Optimizer from the F303 Home Page.

     

    1. Double-click on the Netscape icon.

      2.  

    2. Enter the Web address: www.bus.indiana.edu/finweb/f303home.htm

      3.  

    3. Click on Download the Project 1 dataset.

      4.  

    4. 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."

      5.  

    5. Click on Download the Excel-based Interactive Optimizer.

      6.  

    6. 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."

      7.  

    7. 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.