Courses/Computer Science/CPSC 203/CPSC 203 Template/Fall 2009 Teaching Assistants/F09 Thomas Burt/Lab 5

Week 3 - Lab 5
The spreadsheet below contains general automotive sales data, hybrid vehicle sales data, and gas prices for the U.S. market. This exercise is a continuation to Navneet: Week 2 - Lab 2. Download the spreadsheet below, complete the tasks, and answer the following questions.

[[Media:Navneet_-_Spreadsheet_Example_3_-_US_Car_Sales.xls]]

Tasks

 * Plot gas prices from January 2004 to May 2007
 * Plot hybrid vehicle sales from January 2004 to May 2007
 * Calculate the correlation between gas prices and hybrid vehicles sales from January 2004 to May 2007
 * Hint: use Pearson product-moment correlation coefficient []
 * It's called "PEARSON" in Excel.

Questions

 * How many hybrid vehicles make up the top twenty vehicles sales?
 * Note - use a count-if statement
 * What are the market share percentages for the top twenty vehicle sales in August 2008? Use a chart to display this information.
 * How weakly/strongly correlated are hybrid vehicles sales to the price of gas? What is the correlation coefficient?

Quiz 1 will be held on Wednesday, September 30th at 10:00AM

 * Open book, open note
 * No internet, cellphones or PDA's
 * 45 minutes
 * Submit to Blackboard
 * Email to yourself & me in case something happens

Skills For Quiz

 * 1) Type in some basic data – used for rest of the quiz
 * 2) Autofill a field(s)
 * 3) Set up appropriate Field formats (Currency, Numbers, Text, etc.)
 * 4) Calculate Basic Statistics (Min, Max, Average, SD, Median)
 * 5) Use Chart Wizard to do a default chart on a data set.
 * 6) Customize a chart according to good chart design principles
 * 7) Do a custom calculation using a formula (possibly in several steps)
 * 8) Use a nested If ... Then statement to define classes.
 * 9) Use a Lookup table to define classes.
 * 10) Summarize data in a Pivot table.
 * 11) Forecasting (examples may be spreadsheet for a budget, manufacturing, or a simple AI model)
 * 12) Some basic text functions (examples are concatenation, count-if, and matches to a particular text value)
 * 13) Spreadsheets design rules