Weeks 5 and 6: Analyzing Spreadsheet Data

For this project you will be analyzing data utilizing a spreadsheet software package. The purpose of this project is to enhance and then highlight your skills at analyzing a database utilizing a spreadsheet program. This week you are supplied with one Excel Workbook that has three different databases for you to analyze (see attachments below) To get started, download this workbook and save it on your computer. These tasks require you to reorganize data, build charts and tables, and then explain what the data is telling you. If your spreadsheet skills are at all rusty, it may be beneficial to go into the Content Section and review some of the PowerPoint Presentations, videos, and GCFLearnFree materials prior to getting started.

Start by downloading and saving the databases from the hyperlink below to your computer. Database 1 will be used for Jobs 1 through 4, Database 2 for Jobs 5 and 6, and Database 3 for Job 7. There are complete instructions inside the Workbook you will download, however, here is a short description of each Job you will be completing over the next two weeks:

• Job 1: Reorganize data by month, analyze, and report on your findings. The first worksheet tab is labeled “Database 1 & Instructions” Average Temperatures for Washington DC 1990 – 2001. Use the data in this first tab to complete Jobs 1 – 4. You will first sort the data by month and create a table and a line chart of the average temperatures by month. Complete instructions for this project are located in the Workbook under the Raw Database 1 tab. There is a Help Video video included in the Content Section (Readings and Resources) section for weeks 5 & 6 (Video 1) that explains how to sort data and use a formula to obtain averages.
• Job 2: Analyze the same data you used in Job 1, but reorganize the data by year. In this Job, you will create a table and line chart using the annual data. Complete instructions are on the first tab marked “Database 1 & Instructions”
• Job 3: Create a histogram of the data in Database 1. Use the same Database; there are instructions for this project on that first worksheet tab. If you have not created a histogram before, there is a short video in the Content section that can help you get started.
• Job 4: Create a Pivot Table. Use the same database 1 to create a Pivot Table of the maximum monthly data by year. The Pivot table is an excellent way to give a visual representation of summarized data. Finally, you are asked to explain any trends you see in your table. There is a Training video included in the Content section for weeks 5 & 6 labeled Video 2
• Jobs 5 & 6: Create Pivot Tables. For these two projects you will use data found on the worksheet tab marked “Raw Database 2 & Instructions.” Here you will create two different pivot tables that analyse first by counts and then by the average price by region.
• Job 7: Create a Pareto Analysis. In this final project, you will use the data on the Worksheet tab marked “Database 3 & Instructions.” The database on this page contains the suppliers, products, and order size for the Month of September. You will be creating and then answering questions regarding two Pareto charts that you will create. There are instructions in the tab Marked “Database 3” For how to create a Pareto chart. There is a YouTube video included in the content section under weeks 5 & 6 to help you through creating a Pareto analysis. There are also detailed instructions for how to create a Pareto Analysis on the Database 3 page.

Remember to save your work often. By Sunday midnight of week 6 submit one Excel Workbook that includes all seven jobs. Use the naming convention: JSmith_Weeks 5&6_Project7