Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

Excel Assignment for Stock Portfolio Tracking in CIMS170 Computer Applications - Prof. Lin, Assignments of Microcomputers

An excel assignment for students in cims170 computer applications course. The assignment requires creating a worksheet to keep track of a stock portfolio, including formulas for calculating costs, current values, gains/losses, and percentages of portfolio. The document also covers formatting, creating graphs, printing, and embedding the chart in a word memo. Students are expected to sort the table, format currency and percentage values, save the worksheet, and submit both the excel spreadsheet and word document.

Typology: Assignments

Pre 2010

Uploaded on 08/18/2009

koofers-user-0p4
koofers-user-0p4 🇺🇸

10 documents

1 / 2

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
CIMS170 COMPUTER APPLICATIONS
Name: ____________________________
EXCEL ASSIGNMENT
Due Date: see your Class Schedule or Blackboard Assignments folder for the due date
PROBLEM: Create the worksheet exactly as shown below to keep track of a stock portfolio.
A B C D E F G H I
1
Stock
No. of
Shares
Purchase
Price Cost
Current
Price
Current
Value
Gains/
Losses
% of
Portfolio
Risk
Level
2SunTrust Bank 50 22.56 68.70
3Bank of America 50 48.92 44.08
4Wachovia 300 36.75 40.28
5Citigroup 200 40.20 34.58
6Capital One 150 37.12 52.79
7 Total
1. FORMULA: Enter the following formulas to calculate the variables:
Variables Formula
Cost = No. of Shares * Purchase Price
Current Value = No. of Shares * Current Price
Gains/Loss = Current Value minus Cost
Totals for Cost, Current
Value, and Gains/Losses
= use AUTOSUM to calculate these values
% of Portfolio = use ABSOLUTE REFERENCE and Current Value to calculate this value
Risk Level Use VLookup to determine the risk level (red, yellow, green) of the stocks.
The risk level is based on the difference between the current price and the
purchase price of the stock. The VLookup table is arranged as follows:
Red=-50, yellow =0, green=10
G/L Label In cell J1, add the column label “G/L Label”. Write an IF function in cell J2
to display Gains if cell G2 is a positive number and Losses if G2 is a
negative number. Copy the IF function to cells J3:J6.
2. FORMAT:
Sort the table based on the Stock column alphabetically.
Format all currency values to displace currency with commas and two decimal places.
Format percentage values to two decimal places.
Save the worksheet onto your USB or hard drive as Stock Investment.xlsx.
Center and bold all headings.
Use AutoFormat to give the worksheet a professional appearance.
Create a textbox with shadow to show the stock with the highest percentage.
1
pf2

Partial preview of the text

Download Excel Assignment for Stock Portfolio Tracking in CIMS170 Computer Applications - Prof. Lin and more Assignments Microcomputers in PDF only on Docsity!

CIMS170 COMPUTER APPLICATIONS Name: ____________________________

EXCEL ASSIGNMENT

Due Date : see your Class Schedule or Blackboard Assignments folder for the due date PROBLEM : Create the worksheet exactly as shown below to keep track of a stock portfolio. A B C D E F G H I 1 Stock No. of Shares Purchase Price Cost Current Price Current Value Gains/ Losses % of Portfolio Risk Level (^2) SunTrust Bank 50 22.56 68. (^3) Bank of America 50 48.92 44. (^4) Wachovia 300 36.75 40. (^5) Citigroup 200 40.20 34. (^6) Capital One 150 37.12 52. 7 Total

1. FORMULA : Enter the following formulas to calculate the variables: Variables Formula Cost = No. of Shares * Purchase Price Current Value = No. of Shares * Current Price Gains/Loss = Current Value minus Cost Totals for Cost, Current Value, and Gains/Losses = use AUTOSUM to calculate these values % of Portfolio = use ABSOLUTE REFERENCE and Current Value to calculate this value Risk Level Use VLookup to determine the risk level (red, yellow, green) of the stocks. The risk level is based on the difference between the current price and the purchase price of the stock. The VLookup table is arranged as follows: Red=-50, yellow =0, green= G/L Label In cell J1, add the column label “G/L Label”. Write an IF function in cell J to display Gains if cell G2 is a positive number and Losses if G2 is a negative number. Copy the IF function to cells J3:J6. 2. FORMAT :  Sort the table based on the Stock column alphabetically.  Format all currency values to displace currency with commas and two decimal places.  Format percentage values to two decimal places.  Save the worksheet onto your USB or hard drive as Stock Investment.xlsx.  Center and bold all headings.  Use AutoFormat to give the worksheet a professional appearance.  Create a textbox with shadow to show the stock with the highest percentage. 1

CIMS170 COMPUTER APPLICATIONS

3. GRAPHS : Create the following two graphs in the same worksheet:  Create a column graph to show both the Purchase Price and Current Price for all five stocks. Include an appropriate title and the legend. Label the X and Y axes. Show the data table.  Create a pie chart to show the percentages of the stock portfolio. Include an appropriate title and the legend. Explode the stock with the smallest share, and apply a texture fill effect to the chart background. 4. PRINT :  Header: put CIMS 170- Your Section Number on the left and your name on the right  Footer: put Today’s date on the left and page number on the right  Center the worksheet horizontally and vertically on the page, and print the worksheet and the two charts to fit all on one page using the landscape view  Print the formula on a separate page using the landscape view. Make sure that all formulas are visible.

  1. EMBEDDED : Create a memo using MS Word Memo template, and then copy the pie chart from the Excel spreadsheet onto the Word document. Write a paragraph of text to describe your assignment.
  2. SUBMIT : Upload both your excel spreadsheet and Word document to Blackboard in the Assignments folder, and submit a hard copy of your spreadsheet, formula, and Word memo to your instructor for grading. 2