Paper instructions:
In a formal 250 to 500 word essay, describe the four components of an information system. Why is it important to consider each of them when designing and installing an information system?

Your essay should include an introductory paragraph and a conclusion.

Follow APA format for structure. Support your essay with 2 to 3 credible references beyond the course materials. Please note Wikipedia, Investopedia and similar websites are not credible academic references.

< p >

< pstyle=" ">

< p >

< p >

< p >

< p >

< p >

information system
In the Access section you have to know how to
Create and run Make-table queries
Create and run Update queries
Create and run Delete queries
Put in query criteria to select only some records
Perform a calculation
All of the information about this is in Microsoft Access: Session 3 in the Independent Study sections on the Moodle page for MN1505. Despite the fact that I told you at the beginning of this course you needed to do this independent study work on Access and Excel to be able to do the assignment, only 126 out of the 385 students registered on the course have bothered to look at Microsoft Access: Session 3. It is no surprise that you are therefore finding it difficult.
Some students are trying to us an IIF for Access section 3(c). If you don’t already know how to do this, don’t bother trying it. You can do this easily with two different queries, one to update the records you will SELL, and a second to update the records you will KEEP
There seems to be a lot of confusion about Excel section 6(c). Here you are making a model in the BUY PROJECTIONS sheet into which you will later put more information, so bits of it will be blank. Models are often like this, and examples were included in the independent study material. Specifically here, there is no data for the AVERAGE PRICE and BOOK VALUE columns, because we don’t have that data for the shares we are thinking of buying. If you look at the definition of AVERAGE PRICE (“shows the average purchase price of the shares owned in the portfolio”) then we cannot have information for this about shares we don’t yet own. If you want you can copy the MARKET PRICE data into the AVERAGE PRICE column and then construct a BOOK VALUE. But basically, I just wanted the columns to be there so that when you copy from BUY PROJECTIONS to the bottom of the NEW PORTFOLIO sheet, the columns will match up with the data which is already there
For 7, you will make a pivot table for all the shares (both ones you already own and the ones which you are thinking about buying). You either make this on MARKET VALUE (if you have one for all shares) or BOOK VALUE (if you don’t have a MARKET VALUE for all shares).
For 8, you plug in different numbers of shares in the section at the bottom which you copied from BUY PROJECTIONS.
You have to think of some logical reason which you can justify for the different numbers of shares you suggest buying, and you have to use evidence (in the form of data and/or charts from Excel and/or Access) to back up your argument. You could also use evidence from general knowledge you have about the industries in which Ellen might invest
You do not have to buy shares from all the different industry categories
For industry categories where you do buy/own shares then the concentrations have to meet Ellen’s criteria

MN1505 Assignment (30%)
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:
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