R

#### rpastor

keep running into road blocks. I am only interested in the "buy low

sell high" strategy, as I have already completed the moving averag

ones:

Case: Mutual Fund Portfolio

--------------------------------------------------------------------------------

Introduction:

Investment analysis should be one of the most important topics whic

everyone studies. Investments are the major opportunities whic

everyone has for improving their financial position. Simulatio

analysis is the key tool to help you explore and compare alternativ

investment options. This case addresses the use of simulation t

analyze stock market investment options. We shall focus attention o

mutual funds to illustrate how simulation analysis can help yo

identify plans for participating in the stock market which can yiel

returns much larger than CDs and other fixed returns and yet do so wit

only small risks. You should be able to see that this same approach ca

be used to analyze your opportunities in other investment situation

such as trading currencies, real estate, collectibles, ... .

Basically the concept is to identify and compare "strategies" fo

managing your investment portfolio. A "strategy" is a plan or guid

which helps you make decision choices. We shall focus attention in thi

case on a strategy for "buying and selling" in our mutual fund, that is

in identifying when to buy and when to sell, rather than worrying abou

what funds to buy. We'll pick a fund for demonstration purposes whic

has been characteristically a strong performer. A very, very larg

collection of similar funds exists, all of which have roughly the sam

pattern of performance. It is probably not as important to pick th

right fund as it is to know when to buy and when to sell. We shal

contrast the performance of three strategies to see how muc

variability can occur by choosing different strategies.

The first strategy we shall explore is "buy and hold". This strateg

suggests that we put our money into a fund and let it stay ther

indefinitely. The second strategy we'll explore is "buy low, sel

high". While in fact we cannot do this realistically, since thi

strategy depends upon knowing tomorrow's results before they occur, i

is a great benchmark against which to compare other strategies (sinc

this is the very best we can do). The third strategy we'll explore i

moving averages (this comes in a large variety of forms: 10 week movin

average, 20 week moving average, ... ). The question is "How much bette

is one strategy than another?

To analyze these strategies we shall use historic data. This i

reasonable to do since we have no reason to presume that futur

occurrences in the stock market will be drastically different that wha

has taken place over the last 10 years. If our system is adaptive an

allows us to react with reasonable quickness to changes in the market

we need not be concerned that the Dow is at 3000 rather than 2000. W

want our system to be simple enough that we don't have to spend a grea

deal of time using it. We want to construct it and then, more or less

let it run, telling us when we need to act.

The operational environment for managing your mutual fund investmen

suggests that you originally put your money into a money market accoun

and then tell the fund management services when you want to switch thes

dollars into the mutual fund account. There after you can move yo

dollars back and forth between your mutual fund account and your mone

market account by merely calling the fund management services.

To make this first analysis a little easier we shall ignore the effect

of loads (essentially extra charges you might have to pay up front o

when you sell your shares) and other distributions. Similarly we shal

not explore the associated tax implications. These are things which yo

can readily do to extend this case analysis for your own purposes.

Prerequisites:

The fundamental concepts of EXCEL and the concepts associated wit

simple interest and buying and selling mutual fund shares.

Instructions:

The data we have chosen is a typical five year span of data with weekly

data for about several different funds. We shall use the Magellan data.

Set up an EXCEL (or other worksheet) and lay it out roughly as

follows:

Week Date MMshares MMprice MM$ Action State MFshares MFprice MF$ Other

----------------------------------------------------------------------------------------------------------------------------------------

where

MMshares is the end of week count of shares you have in the money

market.

MMprice is always $1 per share.

MM$ is the end of week dollar value of your money market account (since

each

share is worth $1, then your money market shares count will be exactly

equal

to your money market value, numerically).

Action is (Buy, Sell, or Hold)

State is end of week state ("In" the market, that is in the mutual

fund, or "Out" of the market, that is in the money market fund).

MFshares is the end of week count of shares you have in the mutual fund

(when you put

money into the mutual fund, you determine the number of shares you get

by

dividing the dollar value you put in by the price per share at that

time of the fund

MFprice is the end of week price per share of the mutual fund (net

asset value).

MF$ is the end of week value of your mutual fund account (the MFshares

times the MFprice).

Assumptions:

I have given you $10,000 to begin with. Money Market interest is 6%

annually (So what do you do to get the weekly interest rate?). We are

able to see this week's prices, and then act immediately. So we can

trade using those prices. Think of this as if we get the prices

Thursday night, make our decision, and then call in our trading order

and it takes place on Friday at the same price.

Think of everything taking place at the end of the week. So if you

move from the MM into the MF at the end of week j, shouldn't you get

the benefit of interest earned during week x while you sat in the MM?

Similarly, if in week j, you move from the MF into the MM, you don't

get MM interest that week, but you do get to use the MF price at the

end of that week.

Instructions:

To assist you we have already created a template for your spreadsheet

(DS Case Mutual Fund New.xls) which includes the mutual fund prices.

You need to write the logic for the shares trading. You will need to

construct the simulation model by writing the model logic into your

spreadsheet (so that it will do this automatically).

To accomplish this, you should do the following:

1. Open up this Mutual Fund Case spreadsheet.

2. Fill out the data you need the simulation table (look in columns

beginning with J) as per the instructions in the yellow notes block.

These will be the columns marked as "known".

3. Then begin writing the logic for the action and state and shares

and dollars columns. Note the order of formula construction shown

above the simulation table. This is very important. While we might

have one possible variation that would work, this is the logic we would

like to see. Hence, you will write the action formula, then the state

formula, then the shares formulas, and then the dollar formulas.

Assume that by the end of week 1, all monies have been placed in the

Money Market account, hence the balance at that time is $10,000 (the

initial funding for this Case). Except for a strategy that might call

for leaving it there forever, move the money into the mutual fund

account during week 2 for all other strategies. Hence, the first week

you should use your trading strategy should be week 3 of the

simulation. For the moving average strategy, let's say the 20 week

one, your first trading formula would be in week 20, since you would

have enough data by then to execute it. Between the second week and the

20th week, your funds would just stay in your mutual fund account. If

your action in week j is to "Buy", then your state in week j would be

"In". That is, if we have an action in a given week, the state for

that week should reflect where we would be given that action. Since

the buy low sell high strategy doesn't have to wait, it's first formula

entry will be in week 3.

4. I suggest that your first spreadsheet should be "buy low sell

high". Once you have that operational, copy the entire sheet into a

new sheet in this same workbook, using the Move or Copy Sheet option in

the Edit menu (note you have to check the "Create a copy" checkbox in

the dialogue box that pops up when you choose this menu option). Buy

double clicking the tabs at the bottom of your worksheet you can label

these new sheets something like, "BLSH, 20 week, 30 week, etc." Hence,

you will have all of your investment strategies on separate worksheets

within this workbook. And the amount of manual copying you will have

to do is minimal. It is very easy this way to create the buy and hold

strategies since all you have to do is enter "Hold" into the third week

action value and copy it to the bottom of your spreadsheet.