Data Analysis - Spreadsheets

The objective of this assignment is to use spreadsheets to manage and analyze data. Specifically, it will introduce you to Google Spreadsheets which is a spreadsheet program comparable to Microsoft's Excel.

The lecture for this assignment is performed in Excel and the tutorial in part 1 below is done in Google spreadsheets. Google Spreadsheets and Excel have many of the same features and concepts. You must watch the Excel lecture and use the concepts learned to complete the assignment using Google Spreadsheets. Again, some of the Google Spreadsheets menus and ways of doing things may be different than what you see demonstrated in Excel, but they are very similar so you can find the equivalent way of doing things in Google Spreadsheets.

Part 1: Google Spreadsheets Tutorial

• Follow the steps in the Google Spreadsheet Tutorial. Many of the skills learned in this tutorial will be applied to part 2 of this assignment.


• The tutorial will teach you how to create three seperate spreadsheets that you will submit for grading:
  1. Checks
  2. Company
  3. Expenses


• These files must be shared with your instructor.
  
  

Part 2: MyGrades Spreadsheet

• Create a new Google Spreadsheet workbook for this course called "MyGrades", as shown in this example.
  
  
  • Use the same data for the fictitious students provided in the example. Put your name as the fourth student and fill in the *??* with your own information. If you have not yet received any grades in the course, make up grades for yourself - this is an excellent chance for "What If" analysis. By creating and using this spreadsheet you will be able to determine the grades you will need to get on future assignments, quizzes, etc. to achieve your desired final grade in the course.
  
  
  • Your Spreadsheet should look similar to the one shown in the example. Note all of the different tabs at the bottom of the spreadsheet, which must be included in your workbook.
  
  
  • You must include all formatting parts, such as bold, underline, italics, decimal places, and centering as seen in the example.
  
  
  • Formulas must be used to calculate the grades for all students including yourself. The Excel lecture demonstrates how to compose the necessary formulas to compute the grades. Formulas must be used where applicable on all sheets. Formulas must be used to calculate the following:
    • Average i.e. =(B1+C1+D1)/3 will not be accepted for full credit
    • Sum
    • Standard Deviation
  
  
  • Use the weights from the syllabus to compute a Weighted Average for each grading category, as seen in the example. In a Google Doc, answer these questions:
    1. Find The Data . Where did you get the data to calculate the weighted average?
    2. Plan To Solve The Problem. How did you plan to use the computation capability of the spreadsheet to determine the weighted average? (hint why did you use separate tabs for each data source?).
    3. Perform The Calculation. What was the formula that you put into the spreadsheet to calculate the weighted average? Show formula.
    4. Check Your Answers For Accuracy. How did you check your weighted average for accuracy? (hint - did you do a test case to which you knew the correct answer?).
    5. Explain The Steps Taken. Explain the steps you took, using proper spreadsheet terminology, to arrive at the weighted averages.
    6. Articulate The Solution. . What do the weighted averages mean? How would an instructor use them?
    There are 5 points in the rubric for presenting the problem and solution of finding weighted average in an organized, clear, and concise manner.


• Note that the Excel lecture assumes a final project done by partners. Depending on your class, you may or may not be working with a partner. If you are working with a partner, you should factor in the percentage efforts. If you are working individually you may omit the percentage effort and just calculate the grade based on the web page and video components (as shown in the example).


• 3D Pie Chart:The pie chart will show the weights from the syllabus for each category. In order to create the pie chart, you will need to do the following:
  
  • Create a new sheet. In one column type the categories (i.e. Quizzes) and in the next column type the weight for the item found in the syllabus (i.e. 25%).
  • Create a Pie Chart from the data you typed above. Note:Your own spreadsheet should include all the features you see in the example. (i.e., a legend, a title that must be your name, and data labels showing percent and label).

Part 3: Analyze Survey Results

• Use the survey form from the previous assignment to create a chart that analyzes your results. To do this:
  • Use this tutorial to create a new spreadsheet containing your survey responses.
  • Rename the new spreadsheet to "Survey Analysis".
  • Create a chart from the data collected in the spreadsheet.
    • You will need to reformat the data into one column containing the answer options (yes, no, maybe) and the next column containing the number of responses for each answer.
    • You choose which type of chart to create (Pie, Bar, etc.).
    • Numbers (or percentages) must be displayed on the chart indicating how many responses were collected for each answer.
    • Use the survey question as the title of the chart.
    • Place the chart in its own spreadsheet.
  • Share this file with your instructor as instructed below.