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:
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
To be submitted:
1. Your memorandum in the form of a Word document or PDF file; this MUST have a completed Student Information Page at the front. You can get a copy of this from the UG Information Vault on the website, or the All UG Common Page on Moodle. It is this memorandum on which your mark will be based. You should submit this via the following link:
MN1505 Report
You will receive an electronic submission receipt ID via email. It is your responsibility to keep this safe as proof of submission.
2. Your Excel spreadsheet file, and your Access database file. These are submitted to prove that you have carried out the Access and Excel work yourself. They will only be reviewed if it is necessary to rule out plagiarism. Failure to submit these files means that your assignment as a whole will not be graded. You should submit these files via the following link:
MN1505 Access and Excel files for assignment

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:
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
To be submitted:
1. Your memorandum in the form of a Word document or PDF file; this MUST have a completed Student Information Page at the front. You can get a copy of this from the UG Information Vault on the website, or the All UG Common Page on Moodle. It is this memorandum on which your mark will be based. You should submit this via the following link:
MN1505 Report
You will receive an electronic submission receipt ID via email. It is your responsibility to keep this safe as proof of submission.
2. Your Excel spreadsheet file, and your Access database file. These are submitted to prove that you have carried out the Access and Excel work yourself. They will only be reviewed if it is necessary to rule out plagiarism. Failure to submit these files means that your assignment as a whole will not be graded. You should submit these files via the following link:
MN1505 Access and Excel files for assignment