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
