R
rpastor
Can anyone help me with this project for work? I have started it bu
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.
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.