CIS2200 Excel Project 2019 Spring PDF

Preview

Summary

It is just a tutorial. ...

Description

CIS 2200

Prof. Pai-chun Ma

CIS 2200 - INTRODUCTION TO INFORMATION SYSTEMS AND TECHNOLOGIES Zicklin School of Business – Baruch College - CUNY Professor: E-Mail: Office:

Pai-chun Ma [email protected] Room 11-243

Phone: Fax:

(646) 312-3369 CIS Dept (646) 312-3351

MS EXCEL COMPETITIVE PROJECT – MANAGING YOUR RETIREMENT Due 3/19/19 (Tuesday) 11:50PM OR before (Project is Mandatory for everyone, but Submission for competition is Optional) Assume that you have graduated from Baruch College, worked for 3 years and saved $50,000 in your retirement fund. Instead of putting your retirement money in an ill-managed mutual fund, you decide to aggressively manage the money with your own portfolio with about 6-months time horizon. Follow the MS Excel Tutorial, create your own stock portfolio with exactly 5 stocks (traded in NYSE or NasdaqGS with minimal market capitalization of $ 1 billion US Dollars) from Technology sector under Yahoo! Finance’s Industry Center (http://biz.yahoo.com/ic/ind_index.html). Analyze your portfolio with historical prices from September 10, 2018 to March 8, 2019, and be sure that your portfolio delivers better return on investment (ROI) and still keep its risk (stdev, standard deviation) at par of the benchmark, QQQ (an Exchange Traded Fund tracking larger technology companies). Assume that you keep minimal cash (cash is less than the cheapest stock) and invest at least 1 share for each stock in your portfolio, and you can sell the whole portfolio any time during this holding period. No fraction of a share is allowed. Requirements: 1. Your submission should include at least the following 4 sheets: DailyPrices, ClosingPrices, Portfolio, and DailyGraph. 2. You must use solver to find shares allocation (quantity) for your portfolio. So, your Portfolio sheet must contain solver’s specification. 3. Except dates, prices, volumes, tickers and labels on DailyPrices sheet, labels, shares (quantity) and total available fund ($50,000) on Portfolio sheet, and labels on ClosingPrices sheet, NO numbers or tickers as lables are allowed in any other place of the project. 4. Your portfolio needs to outperform (higher overall ROI and same or less Stdev) the QQQ. 5. Your submission has to arrive Blackboard by the due date and time. No e-mail submission will be accepted.

1

CIS 2200

Prof. Pai-chun Ma

Class Competition: The top 3 students, who get the best ROI and still keep the risk (stdev, standard deviation) of the portfolio at or less than the benchmark (QQQ), will earn 30% (1st place), 20% (2nd place) and 10% (3rd place) more points on their corresponding In-class MS Excel Exam scores as rewards. On-line Submission (Optional): Your project is mandatory, but your on-line submission of your project is optional. So, if you wish to enter the class competition, then you need to follow the instructions below to submit your work to Blackboard. Save your Excel file as ExcelProject_yourlastname_yourfirstname and submit it through BlackBoard on or before 11:00PM on 3/19/19. The submission window will close at 11:00PM on 3/19/19. No late assignment will be accepted. No e-mail submission is allowed. You also need to remember your ROI, so that you can write on your in-class MS Excel exam to enter the competition.

In-class MS Excel Exam on 3/20/19 (20 pts) Be sure that you attend the in-class MS Excel Exam on 3/20/19 (Wednesday). You will have assigned seating for this exam. When you arrive at classroom on 3/18/19, do not enter the classroom until the seating plan is posted outside of the classroom. You need to remember your seat number and exam ID before you enter the classroom and locate your seat. The exam covers everything that has been covered in MS Excel, including all examples used in class, tutorial and MS Excel Project.

2

CIS 2200

Prof. Pai-chun Ma

Sample questions on the In-Class MS Excel Exam: (You will be given all the related worksheets) ClosingPrices Sheet:

Write the cell content (in formula view) of Q1-Q5. Answers: Q1: Q2: Q3: Q4: Q5:

=DailyPrices!H68 =SUMPRODUCT(B3:F3,B$69:F$69)+H$1 =Portfolio!D12 =STDEV(I3:I66) =Portfolio!E8

NO Partial Points!!! (Pay attention to the row and column location of the cell) DO YOU KNOW HOW (Excel) and WHY (Context)? (memorize these formula would not pass the test!)

3...