This assignment is based on the exercises you should have carried out in your own time on the use of Microsoft Access and Microsoft Excel (see the Moodle page for MN1505). 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: 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] Make a new table based on the OWNED table. It should: Be named CURRENT PORTFOLIO Include all the fields from the OWNED table 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. 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 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 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 You should save the query you used to do this Check the design of the CURRENT PORTFOLIO table and change the data type for the SELL FOR field to NUMBER 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 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. 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. 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. Make a new table based on the CURRENT PORTFOLIO table. It should: Be named AFTER SELLING Only include data about stocks that are going to be kept. You should save the query you used to do this. Make a table based on the COULD BUY table. It should: Be named POSSIBLE BUYS Include all the fields from the COULD BUY table. 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. 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 You should save the query you used to do this Update the POSSIBLE BUYS table to give information about stocks that should be bought. Save each query before going on to the next 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. 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. Delete from the POSSIBLE BUYS table rows which show stocks that should be ignored. Close the INVEST.accdb database and Access. TASK 2: Excel Analysis Open Excel. 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. Augment the CURRENT PORTFOLIO worksheet to compute industry concentrations: 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. Insert a pivot table showing the total portfolio book value for each industry. Using the pivot table, compute the percentage that each industry bears to the whole portfolio. 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. Create a worksheet showing a new portfolio that will eventually combine retained stocks and stocks that could be bought: Make a copy of the AFTER SELLING worksheet. Rename this new worksheet NEW PORTFOLIO. Delete the following columns: SELL STOCK RATIO, SELL? and SELL FOR. Add a MARKET VALUE column whose values are obtained by calculating the number of shares owned multiplied by the current market price. Make a copy of the POSSIBLE BUYS worksheet so that you can use the data to generate ideas about new stocks to buy. Rename this new worksheet BUY PROJECTIONS Delete the columns 52 WEEK HIGH, BUY STOCK RATIO and BUY? 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. Use SUM() to calculate totals for the BOOK VALUE and MARKET VALUE columns. 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. Insert a pivot table showing the total portfolio book value for each industry for all stocks shown in the NEW PORTFOLIO worksheet. Using the pivot table, compute the percentage that each industry bears to the whole portfolio. 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: 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. 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. You will have to refresh the data in the pivot table each time you want to test the industry concentrations. You should prepare three possible scenarios, saving each as a worksheet with a new name: SCENARIO1, SCENARIO2 and SCENARIO3. Once you have decided on one scenario which you consider to be the best for the new portfolio, export the data to Access: Make a copy of the SCENARIOn worksheet Rename the new worksheet to EXPORT 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. 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: MN1505_[student ID]_1 Replacing [student ID] with your 9 digit student ID number. 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 I do not expect you to use in-text references nor to provide a bibliography/reference list at the end of the memorandum. SUBMISSION OF ASSIGNMENT This assignment must be submitted electronically via the Moodle page for MN1505. You are strongly recommended to keep a copy of all submitted assignments. The deadline for submissions is 12.00 noon on Tuesday 15th March 2016
Use the order calculator below and get started! Contact our live support team for any assistance or inquiry.
[order_calculator]
