Preview
A mutual fund portfolio manager wants to restructure her fund using “troubled-stock” buy-and-sell rules. You have been called in to automate the restructuring to some extent, using an Excel spreadsheet and Access database.
Background
Your friend Ellen has just taken over as manager of a mutual fund. The fund has several stocks, some of which have declined in price. Ellen thinks that the fund needs to be restructured, and she wants to weed out stocks that are ripe for sale. With the proceeds, she will buy other stocks that appear to be undervalued in the stock market.
Ellen has a database with a table for stocks the fund owns and another table for stocks the fund might buy. She wants to use the database and a spreadsheet to automate the portfolio restructuring. Knowing how good you are with Access and Excel, she has asked you for help.
Ellen wants to follow the “troubled stocks” strategy in her restructuring. The strategy has the following rules for selling and buying stocks:
• Ellen would sell a stock if its price has risen 25% or more since it was purchased. She reasons that such a stock’s price will not increase in value much more and might go down, so why not sell it now and take the profit?
• Ellen would use stock sale proceeds to buy stocks that are now selling for 75% or less that their highest price in the past 52 weeks. She seasons that a stock that has fallen that far might be ripe for an increase in price.
• The strategy also has rules for industry concentrations. She would not want to put more than 20% of the portfolio in stocks of companies in any one industry. However, she does not want trivial representation either, so she would want stocks of any industry to represent at least 8% of the portfolio.
The database INVEST.accdb has a table of data about stocks currently owned in the portfolio. The database also has a table of data about stocks that could possibly be purchased. The database tables are discussed next.
The OWNED Table
The OWNED stocks table shows data about the portfolio’s stocks. The fields are as follows:
• STOCK NAME: shows the company’s name
• INDUSTRY: shows the company’s industry
• SHARES: shows the number of shares of the stock owned in the portfolio
• AVERAGE PRICE: shows the average purchase price of the shares owned in the portfolio
• MARKET PRICE: shows the current share price in the stock market
The COULD BUY Table
The COULD BUY table shows data about stocks that Ellen thinks are interesting and worth following. The fields are as follows:
• STOCK NAME: shows the company’s name
• INDUSTRY: shows the company’s industry
• 52 WEEK HIGH: shows the stock’s highest market price in the past 52 weeks
• MARKET PRICE: shows the current share price in the stock market
TASK 1: Access Data Preparation
Ellen has rules governing how she would sell and buy stocks shown in the database tables. You want to restructure the portfolio using the data in Access and Excel. You will use the following steps:
1) Open the INVEST.accdb database, from the Moodle page for MN1505 Assignment section. Click on Enable Content at the top of the page and then on Save As at the top of the page and save the file in your MN1505 folder (you should have created this folder in your Y: drive earlier in the term). By default the database file will be saved with the name INVEST1.accdb. [If your Y: drive is full you may need to delete some of its contents to be able to do this]
2) Make a new table based on the OWNED table. It should:
a) Be named CURRENT PORTFOLIO
b) Include all the fields from the OWNED table
c) Include a new field to be called SELL STOCK RATIO that calculates the ratio of the current market price to the average price. This ratio’s values will govern the selling decision.
d) Include a new field to be called BOOK VALUE whose values are obtained by computing the book value (at the average price) of each stock
e) Include a new field to be called SELL? which will be used to identify those stocks that should be sold. Leave it blank for the moment (you should use “” as the value) — you will update it later
f) Include a new field to be called SELL FOR which will be used to calculate the sales values for stocks to be sold. Leave it blank for the moment — you will update it later
g) You should save the query you used to do this
h) Check the design of the CURRENT PORTFOLIO table and change the data type for the SELL FOR field to NUMBER
3) Update the CURRENT PORTFOLIO table to give information about stocks that should be kept or sold. Save each query before going on to the next
a) Identify stocks that should be sold by updating the value in the SELL? field to SELL. Remember that Ellen has decided to sell stocks whose price has risen 25% or more since they were purchased.
b) Identify stocks that should be kept by updating the value in the SELL? field to KEEP. Remember that Ellen has decided to sell stocks whose price has risen 25% or more since they were purchased.
c) Compute the sales values for stocks to be sold. If sold, the value is the number of shares times the current price. The sales value is zero if the stock will be kept. This will require two individual queries.
4) Make a new table based on the CURRENT PORTFOLIO table. It should:
a) Be named AFTER SELLING
b) Only include data about stocks that are going to be kept.
c) You should save the query you used to do this.
5) Make a table based on the COULD BUY table. It should:
a) Be named POSSIBLE BUYS
b) Include all the fields from the COULD BUY table.
c) Include a new field to be called BUY STOCK RATIO that calculates the ratio of the current market price to the 52-week-high price. This ratio’s values will govern the buying decision.
d) Include a new field to be called BUY? which will be used to identify those stocks that should be bought. Leave it blank for the moment (you should use “” as the value) — you will update it later
e) You should save the query you used to do this
6) Update the POSSIBLE BUYS table to give information about stocks that should be bought. Save each query before going on to the next
a) Identify stocks that should be bought by updating the value in the BUY? field to BUY. Remember that Ellen has decided to buy stocks that are now selling for 75% or less than their highest prices in the past 52 weeks.
b) Identify stocks that should not be bought by updating the value in the BUY? field to IGNORE. Remember that Ellen has decided to buy stocks that are now selling for 75% or less than their highest prices in the past 52 weeks.
c) Delete from the POSSIBLE BUYS table rows which show stocks that should be ignored.
7) Close the INVEST.accdb database and Access.
TASK 2: Excel Analysis
1) Open Excel.
2) Create Excel worksheets for the current portfolio, for the portfolio as it would be after selling stocks, and for the stocks that are possible buys by importing Access table data. You should rename the worksheets to CURRENT PORTFOLIO, AFTER SELLING and POSSIBLE BUYS, respectively, to increase spreadsheet usability.
3) Augment the CURRENT PORTFOLIO worksheet to compute industry concentrations:
a) Use the SUM() function to compute the total SELL FOR and total BOOK VALUEs, respectively. Note the value of stocks that would be sold, as this is the amount available to buy other stocks from those shown in the POSSIBLE BUYS worksheet.
b) Insert a pivot table showing the total portfolio book value for each industry.
c) Using the pivot table, compute the percentage that each industry bears to the whole portfolio.
4) Sort the data in the POSSIBLE BUYS spreadsheet by the values in the BUY STOCK RATIO column. Presumably the stock with the lowest ratio is the most depressed in price and, therefore, the most desirable to buy.
5) Create a worksheet showing a new portfolio that will eventually combine retained stocks and stocks that could be bought:
a) Make a copy of the AFTER SELLING worksheet.
b) Rename this new worksheet NEW PORTFOLIO.
c) Delete the following columns: SELL STOCK RATIO, SELL? and SELL FOR.
d) Add a MARKET VALUE column whose values are obtained by calculating the number of shares owned multiplied by the current market price.
6) Make a copy of the POSSIBLE BUYS worksheet so that you can use the data to generate ideas about new stocks to buy.
a) Rename this new worksheet BUY PROJECTIONS
b) Delete the columns 52 WEEK HIGH, BUY STOCK RATIO and BUY?
c) Add column headers and appropriate data or calculations for SHARES OWNED, BOOK VALUE and MARKET VALUE. The result should be a worksheet in the same format as the NEW PORTFOLIO worksheet, but containing different data.
d) Use SUM() to calculate totals for the BOOK VALUE and MARKET VALUE columns.
e) Copy and paste the data from the BUY PROJECTIONS worksheet to the end of the NEW PORTFOLIO worksheet data. It might be helpful to show these in a different colour to differentiate from those already owned.
7) Insert a pivot table showing the total portfolio book value for each industry for all stocks shown in the NEW PORTFOLIO worksheet.
a) Using the pivot table, compute the percentage that each industry bears to the whole portfolio.
8) You should then play “what-if” with the number of shares to be bought of the possible buys; you can do this manually or automate some/all of it if you are feeling adventurous. You could decide to choose an equal number of shares of each acceptable stock, or stocks could be bought to cure industry under-representation, or stocks with the best ratios could be emphasised. Remember that:
a) The amount Ellen has to spend is the amount shown as the sum of the SELL FOR values in the CURRENT PORTFOLIO worksheet. You do not need to hit this figure exactly, but should be close to it.
b) Ellen does not want to put more 20% of the portfolio in stocks of companies in any one industry, nor does she want stocks of any industry to represent less than 8% of the portfolio.
c) You will have to refresh the data in the pivot table each time you want to test the industry concentrations.
d) You should prepare three possible scenarios, saving each as a worksheet with a new name: SCENARIO1, SCENARIO2 and SCENARIO3.
9) Once you have decided on one scenario which you consider to be the best for the new portfolio, export the data to Access:
a) Make a copy of the SCENARIOn worksheet
b) Rename the new worksheet to EXPORT
c) Delete extraneous data so that only columns corresponding to OWNED table fields remain: Stock Name, Industry, Shares, Average Price and Market Price. Remember to delete the Pivot table and totals as well.
d) From Access import the EXPORT worksheet, making sure that you tell Access to use Stock Name as the primary key field, and that you save it as a new table, which you should call NOW OWNED.
TASK 3: DOCUMENTING FINDINGS IN A MEMORANDUM
For this assignment, you write a memorandum in Microsoft Word that documents your findings. In your memo, observe the following requirements:
• Your memo should have proper headings, such as Date, To, From, and Subject. You may use a Word memo template if you wish. You can address the memo to Ellen.Please use a fictional name rather than your own name on this memorandum.
• Briefly outline the situation. However, you need not provide too much background — you can assume that readers are generally familiar with your task — but you need to show that you have understood the situation and what is required of you.
• In the body of the memo, include detailed information on your three different scenarios and explain the stock purchase rules that you have followed for each.
• Support your memorandum graphically by inserting appropriate selections from the data in your database tables and/or spreadsheets.
• Explain why you have selected your preferred scenario as a recommendation for stock purchase by EllenAs you will be submitting this file for you assignment, you should save it with a name in the following format:
MARKING CRITERIA
The submitted and assessed part of this coursework is a business-style memorandum, rather than an academic essay. Thus, the marking criteria are different from those usually required for an academic essay. Your assignment will be assessed on the following criteria:
• Fulfilling the requirements of the brief
• Use of data and/or charts to support statements made
• Writing style
• Quality of presentation
STOCK NAME INDUSTRY 52 WEEK HIGH MARKET PRICE
ABC ENERGY 80 65
ABCD CONSUMER 70 74
ABCDE UTILITIES 96 82
ABCDEF CHEMICALS 53 52
BDF SERVICES 98 81
CDE UTILITIES 78 37
CDEF CHEMICALS 52 51
CDEFG SERVICES 73 73
CDEFGH ENERGY 100 74
CEG CONSUMER 95 47
EFG SERVICES 52 36
EFGH ENERGY 81 56
EFGHI CONSUMER 70 69
EFGHIJ UTILITIES 58 56
FHJ CONSUMER 88 39
GHI CONSUMER 92 45
GHIJ UTILITIES 100 54
GHIJK CHEMICALS 74 73
GHIJKL SERVICES 83 83
GIK CHEMICALS 90 48
IJK CHEMICALS 88 78
IJKL SERVICES 65 55
IJKLM ENERGY 71 70
IJKLMN CONSUMER 59 56
JLN CHEMICALS 64 47
KLM ENERGY 94 63
KLMN CONSUMER 93 38
KLMNO UTILITIES 78 78
KLMNOP CHEMICALS 85 80
KMO ENERGY 51 51
MNO UTILITIES 64 50
MNOP CHEMICALS 85 65
MNOPQ SERVICES 77 72
MNOPQR ENERGY 60 60
NPQ ENERGY 96 48
OPQ SERVICES 91 39
OPQR ENERGY 57 56
OPQRS CONSUMER 71 70
OPQRST UTILITIES 93 93
QRS CONSUMER 66 66
QRST UTILITIES 54 53
QRSTU CHEMICALS 92 90
QRSTUV SERVICES 86 80
QSV UTILITIES 93 49
STU CHEMICALS 81 75
STUV SERVICES 71 70
STUVW ENERGY 55 55
STUVWX CONSUMER 93 88
UVW ENERGY 69 54
UVWX CONSUMER 58 55
UVWXY UTILITIES 97 94
UVWXYZ CHEMICALS 76 76
WAY UTILITIES 70 61
WVY SERVICES 73 61
WXY UTILITIES 89 35
WXYZ CHEMICALS 70 70
WXYZA SERVICES 90 80
WXYZAB ENERGY 90 71
XYABCD UTILITIES 58 55
YZA SERVICES 94 41
YZAB ENERGY 97 95
YZABC CONSUMER 90 81
STOCK NAME INDUSTRY SHARES AVERAGE PRICE MARKET PRICE
ACE CAPITAL 1000 82 61
BCD FINANCE 5000 76 36
BCDE HEALTHCARE 1000 89 73
BCDEF TECHNOLOGY 9000 72 84
BCDEFG MINING 1000 91 70
DEF TECHNOLOGY 2000 69 38
DEFG MINING 8000 59 57
DEFGH CAPITAL 1000 97 108
DEFGHI FINANCE 3000 87 104
DFH CAPITAL 2000 74 80
EGI HEALTHCARE 5000 50 49
FGH CAPITAL 5000 95 56
FGHI FINANCE 8000 54 44
FGHIJ HEALTHCARE 6000 80 119
FGHIJK TECHNOLOGY 7000 77 103
HIJ HEALTHCARE 5000 86 63
HIJK TECHNOLOGY 1000 99 63
HIJKL MINING 8000 54 113
HIJKLM CAPITAL 1000 86 119
HJL HEALTHCARE 3000 95 74
IKM MINING 8000 74 52
JKL MINING 1000 62 61
JKLM CAPITAL 9000 96 56
JKLMN FINANCE 4000 50 113
JKLMNO HEALTHCARE 2000 100 102
LMN FINANCE 5000 62 59
LMNO HEALTHCARE 1000 80 84
LMNOP TECHNOLOGY 1000 81 83
LMNOPQ MINING 8000 95 74
LNP MINING 7000 69 72
NOP TECHNOLOGY 8000 66 70
NOPQ MINING 4000 65 43
NOPQR CAPITAL 3000 99 93
NOPQRS FINANCE 7000 81 97
PQR CAPITAL 7000 61 52
PQRS FINANCE 3000 73 72
PQRST HEALTHCARE 6000 79 97
PQRSTU TECHNOLOGY 9000 65 76
PQS FINANCE 5000 61 60
RST HEALTHCARE 3000 64 66
RSTU TECHNOLOGY 1000 87 88
RSTUV MINING 4000 63 105
RSTUVW CAPITAL 5000 72 111
SVW TECHNOLOGY 1000 66 82
TUV MINING 1000 64 75
TUVW CAPITAL 2000 55 113
TUVWX FINANCE 4000 69 117
TUVWXY HEALTHCARE 7000 50 83
UWY FINANCE 4000 68 51
VWX FINANCE 1000 61 56
VWXY HEALTHCARE 1000 75 94
VWXYZ TECHNOLOGY 1000 98 103
VWXYZA MINING 1000 82 86
VYZ CAPITAL 4000 54 55
XYZ TECHNOLOGY 6000 50 42
XYZA MINING 8000 96 104
XYZAB CAPITAL 1000 61 80
XYZABC FINANCE 1000 60 74
YABCDE TECHNOLOGY 9000 76 105
YAC TECHNOLOGY 4000 95 77
ZABC FINANCE 1000 71 79
ZABCD HEALTHCARE 5000 84 85
TAKE ADVANTAGE OF OUR PROMOTIONAL DISCOUNT DISPLAYED ON THE WEBSITE AND GET A DISCOUNT FOR YOUR PAPER NOW!