Computer science Lab5 and 6

Labs/Lab5/Lab5_Excel_SH.html

Lab 5: Spreadsheets

1. Learning Outcomes and Introduction
2. Task 1: Powers of 2, Powers of 10
3. Task 2: Importing and Sorting Data
4. Task 3: Graphing Data
5. Task 4: Functions
6. Submission

Learning Outcomes and Introduction

During this process, you will be able to:

• Demonstrate your ability to layout and format a spreadsheet
• Demonstrate the use of relative vs. absolute references in spreadsheets
• Demonstrate the use of functions in Excel
• Demonstrate the use of IF and VLOOKUP in Excel

Task 1:Powers of 2, Powers of 10 (20 marks)

Instructions

There is a reasonably close relationship between the powers of two and the powers of ten: 210 is a little more than 103, that is, 1024 is close to 1000. Similarly, 220 is more than 106 and the ratio is 1.049. The approximation is pretty good for a long distance though eventually it breaks down. Your task is to make a spreadsheet that shows how good the approximation is and find the place where the ratio first becomes greater than 2.

1. Start your spreadsheet program (such as Excel)

Enter Data:

• Put the numbers 0, 1, 2, ...,40 into column A.
• Put into column B a formula that will compute 2 raised to the power 10 times the value in column A.
• Put into column C a formula that will compute 10 raised to the power 3 times the value in column A.
• Put into column D a formula that will compute the ratio of B over C, that is, the ratio of how good or bad the approximation is.
• Set the cell format for column D to display exactly two digits after the decimal point.

Prepare a Chart:

• Select the correct range to create a chart that shows the ratio changing for the 40 rows.
• Use the chart wizard ("Insert>Chart>Column" or this icon ) to create a graph that shows the ratio.
• Move the chart so that is beside your data as shown in the picture below. Add an appropriate chart title and remove the " legend"

Save Worksheet:

• In this lab, you will be using a new sheet for each part, each with its own name. For task1, double-click on the tab that

says Sheet1

• Type the name Power2 in its place.
• Save the spreadsheet in a file called lab5_Firstname_Lastname under the folder COMP152\Lab5

Side Note: the spreadsheet application you are using will add the correct filename extension) Do this with as little typing and as much use of Excel's extension feature as possible; you can probably do it by typing no more than two or three rows and then extending them. Your table should look like this when done, except that it will have more rows, more data in the graph, and a highlighted row towards the end: Note: In the example below, numbers are displayed as "floating point". You do not have to format that way, most of us prefer more common looking number formats (comma style?). No matter what format and number of decimal places you choose to display - the spreadsheet software is actually using floating point in the background to ensure maximum accuracy.

Sample out put

Notice that the approximation gets worse at worse than linear rate. To see just how fast it is getting worse, right-click on the bars in the chart, then select Add " Trendline" from the Chart menu. Pick the trendline that gives the best fit to the data.

Task 2: Importing and Sorting Data (20 marks)

It's all well and good to create synthetic data to play with, but in the real world, one usually works with real numbers. In this section, you will experiment with data from Yahoo Finance, which provides a wealth of financial information in convenient formats. Your task is to generate a table that displays stock prices and relative performance for two stocks for the past two or three months. You can choose any two stocks you like; interesting pairs might be selected from among Amazon, Ebay, Google, Yahoo, IBM, Oracle, Ford, GM, etc.

Import Data

1. Go http://finance.yahoo.com input in the " Search Finance" box your first stock (such as Amazon).
2. Go to " Historical prices" (left bar on the screen), set the range of dates that you want, click on the button " Get Prices" .
3. Go to the bottom of the page and select "Download To Spreadsheet".
4. Either save the file and import it into Excel, or open it directly; the latter is easier.
5. Repeat for your second stock, using the same range of dates.

Note: You now have two Excel windows, each displaying something like the image shown: Attention: Move to a new tab in your lab5_Firstname_Lastname file, rename the tab to "Stock prices".

Sorting Data

Prepare Data:
• In each Excel window, delete all columns except "Date" and "Adj. Close" and delete the first row. This should leave exactly two columns in each Excel window
• Now copy/paste the two data sequences into the "Stock prices" sheet.
• Since you have two "Date" columns with the same information, you can now delete one of them. Important Note: After you get rid of the duplicate date column, your sheet should have three columns and you need to Make sure column A contains the dates. Your sheet should have now only three columns.
• Change the headings to the corresponding names of your stock companies.
• Select all the cells in column A which contain dates. Change the date format to a more readable date format (e.g. December 31, 1999)
• Select all the numbers in columns B and C format all numbers as "comma style". Resize columns if needed.
• Look through columns B and C to confirm matching data (When I did this one stock had a blank row where a dividend was paid). If necessary drag price data up if you see a blank. We want to see at least 60 - 70 rows of data (working days over 3 months) to a maximum of 250 rows of data.
Sort Data:
• Insert a new column A and insert in it numbers 1, 2 ... etc.
• The prices are in most recent first order and you need to reverse them:
• Select the three columns (include your headings in row 1), then on the Data menu, pick "Sort...", and sort the data by "Date" from oldest to newest.
• Be very careful doing sorting!! If you do it incorrectly you will create big problems!
• At this point, if you select columns B and C, you can compare the two stocks, but it won't be very interesting if their prices differ by too much. So the next step is to make two new columns that show how the prices have changed in proportion to the first value.
• In E2, enter the formula =C2/C$2. Extend it down to the end of column E.
• Repeat in F1 for column D, using the formula =D2/D$2.
Attention: The formula =C2/C$2 is not an error. Normally when Excel extends formulas it modifies cell references, using relative values. The $ in a reference like C$2 tells Excel to leave that cell reference unchanged ("absolute" instead of relative), so the subsequent formulas will be =C3/C$2, =C4/C$2, etc. Thus each cell will contain the ratio of the price to the initial price.

Sample out put

Task 3: Graphing Data (10 marks)

The next step is to draw a graph of your data. Excel will let you display data in a lot of different ways, some sensible and some definitely not. We want to see two graphs here. One can be a plain vanilla graph that shows the comparison between the two stocks in a simple way, like this:.

Instructions

1. Use the chart wizard ("Insert | Chart" or this icon to create a graph that looks approximately like the one above but with a meaningful title, proper labels, etc.
2. Use the chart wizard to create another graph from exactly the same data that is as different from the previous one as you can manage while still displaying the same information in a form that can potentially be understood. Place it near the other chart.
3. Make the two charts approximately the same size and position them so the charts and the numeric data can all be seen at once.
4. Preview your work. Look at your worksheet in Print (page) preview.
• In MS Office, selecting print preview and page layout lets you scale automatically to print on one page or perhaps multiple pages but a width of one page.
• You must do this operation for each worksheet individually as different sheets have different printing needs.
• We are not printing anything for this assignment but this may be important later. If you need a printed chart or worksheet to put in a hard copy assignment or report.
Reminder: Save the lab5_Firstname_Lastname file in your Lab5 folder and close the file.

Task 4: Functions (10 marks)

Reminder: Move to a new tab in your lab5_Firstname_Lastname file, rename the tab to "Coffee".

Create a spreadsheet with the information in the picture below.

Instructions

1. Display in column B the correct description of the product in column A.
• Use the vLookup function with information from "Product Table".
• Use absolute cell reference so you can copy the function down.
• Display in C the cost per unit (same hints from above)
• Write a formula in column D to calculate retail cost. Retail cost is calculated using the percentage in “Retail markup”.
• Create an IF function in column F to calculate total retail
• Use "Discount" if quantity is greater than 10
• In column G write a formula that calculates sales tax
  • To calculate sales tax use "Tax Rate" percentage
• In column H write a formula to calculate total (subtotal + taxes)
• Display the current date in cell B2; format the cell to a readable date format (eg. December 15, 2013)
• In my example, we have only four items to invoice. Add few more items to invoice.
• Preview your work and if necessary change the page layout and fit the sheet content to one printed page.
Attention: Save and close the file in your lab5 folder

Submission

At this point you should have two spreadsheets in your Lab5 folder. Check through them to make sure they look right. When everything is working, place a copy of your files into your lab5 Dropbox in D2L

OVERALL ASSESSMENT (10 marks)

Ten marks are set aside for overall assessment. 8 to 10 marks are given for outstanding work. The assessment proceeds as follows:

• 8 - 10 marks given for strong evidence of superior grasp of spreadsheet concepts. The assignment is completed in an exemplary manner.
• 4 - 7 marks given for evidence of reasonable understanding of the spreadsheet concepts. The assignment is correctly completed.
• 1- 3 marks given for evidence of some understanding of spreadsheet concepts. Three quarters of the assignment is correctly completed.

submit your lab5_Firstname_Lastname file.

NOTE: This assignment is to be done individually. You can help one another with problems and questions, but in the end everyone must do their own assignment.

Criteria	Marks
All tasks
OVERALL ASSESSMENT	10
Task 1
Powers of 2, Powers of 10	20
Task 2
Importing and Sorting Data	20
Task 3
Graphing Data	10
Task 4
Functions	15
Total	75