

Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community for help and clear up your study doubts
Discover the best universities in your country according to Docsity users
Free resources
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
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
1 / 2
This page cannot be seen from the preview
Don't miss anything!
CIMS170 COMPUTER APPLICATIONS Name: ____________________________
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.