Title: Dig the Data
Course: 21 Things for Students > Section: Dig the Data

Quest 1: Picture This

Special Agent Briefing

Secret agent, you will need to go on an undercover investigation to find out why the Superb Slushie Company is so successful. We need to know what colors are leading to their success and your report will also need to be top secret.  You will need to send the data to us in a picture so nobody is suspicious of your work.  Follow the steps below to successfully complete this quest.

Steps:

Step 1

Go to this interactive spinner to simulate the colors at the Superb Slushie Company.

Step 2

·        Click the Change Spinner button

·        Delete the first box “purple” and change the word to “blue”

·        Add a new color at the bottom of the table (purple)

·        Click the up triangle  so that it shows a 1

·        Choose dark purple on the right

·        Type “purple” into the table

·        Click the Apply button

Step 3

·        Click the Record Results button

·        Spin the spinner 20 times

·        Record the frequency (number of times an event occurs) of each color

Step 4

Transfer your results to a word processing document.  To get an accurate Wordle, words must be inputted consistently (i.e. if you are going to capitalize each color make sure each one is capitalized)

Example: (3 Blue, 2 Yellow, 1 Green, 3 Red, 4 Orange, 1 Purple)

Blue Blue Blue
Yellow Yellow
Green
Red Red Red
Orange Orange Orange Orange
Purple

Step 5  Highlight your data in the table above and copy it to your clipboard.

Step 6 Go to http://www.wordle.net/

·        Click on Create

·        Paste your data into the box

·        Click Go

·        You can change your picture if you choose by clicking Randomize at the bottom of the screen.

·        Take a secret image capture

Here is an example of what it may look like:

To complete this Quest

·        Save your secret image to your SSI Chief.  Be sure that you know which Secret File Space to use. (webpage, blog, flash drive, email, network drive, blackboard, Google)

·        Copy these questions to your space under the secret image capture and answer them for a summary of your findings.

What color is the most important for the business to be successful? |
How do you know this information by looking at your Wordle? |
What color could we remove from our store because it does not make us as much money as the others? |

Resources

http://www.mathplayground.com/probability.html

http://www.unpracticalmath.com/applets/virtual_spinner.html

http://teacherslovesmartboards.com/2008/11/smartboards-and-how-to-create-your-own-spinner.html/

Check off this quest

Quest 2: Falling into Place

OK secret agent, throughout this mission you will need to produce graphs to show your findings.  Therefore, you must be able to find and organize your data into a table or spreadsheet.  You may have some understanding of what is going on but a graph will put all of the data into place where it can make sense to others.  This Quest will have you collect data and prepare a spreadsheet for your SSI Chief.

Steps:

Step 1  Watch this video on Excel spreadsheets

Step 2  Open Microsoft Excel (or the program suggested by your SSI Chief like Google docs or Open Office)·        Create a new document·        Make 3 headings across the top row (Level, Lines Cleared, and  Score)

Step 3  Click this game link for a link to Tetris.  Play and collect your data.  Playfive games and record your data in the appropriate cell of your spreadsheet.

HINT…the right and left cursor move the pieces from side to side.  The up cursor rotates the piece and the down cursor moves the piece down quickly.

Step 4  On your spreadsheet

·        Freeze your headings
·        Change the headings font to 16 and make them bold
·        Change the font color to green for your best game
·        Create a text box and fill in these 2 statements…

1.     I predict my next game will be a level ______.
2.     The strategy that works best for me is ______

Step 5  Go back to the Tetris game and play one more time.

Step 6  Add the data for your Sixth game to your spreadsheet with red font color.

Step 7  Add this statement to your text box to evaluate your prediction.

1.     My prediction was (correct / incorrect).  Explain your evaluation.

To Complete this Quest

Take a screen shot of your spreadsheet and save it to your Secret FileSpace on the Dig The Data page or where your SSI Chief directs your work.

 Check off this Quest

Resources

You can use this link to play on Facebook and more.  May require downloads for some games. http://www.tetris.com/

Free play http://www.freetetris.org/game.php http://neave.com/n-blox/

App on Google play
https://play.google.com/store/apps/details?id=com.ea.tetrisfree_na&hl=en

Quest 3: Just Function

Secret Agent Briefing

As an agent, your SSI Chief will often need information as quickly as possible. Timing and efficiency could make you the better agent amongst others.  This quest will help you to create and use a top-secret profile of data (spreadsheet) and be able to find the right answers for your Chief.  Pay close attention to using functions and graphs so that you become a top agent. For this Quest you will need to check in with your SSI Chief to establish which investigation best fits your job description:

Car Cruising
-or-
Cell Phone Use

Steps:

Step 1 -  Watch the Excel tutorial (shown below) on how to use functions and build a bar graph.

Step 2  Go to your investigation that is assigned by your SSI Chief.

Car Cruising Activity…includes instructions

Cell Phone Activity…includes instructions

To complete this Quest

Your directions for completing this quest are listed in the directions for each activity. Be sure that you have submitted your work to your secret file space.

Cell Phone Use

- Cell Phone Activity.wav

Dig the Data - Cell Phone Usage

Student Activity

Your SSI Chief has been contacted by an advertising agency that represents several cell phone companies.  Your job is to investigate the data of a typical, middle school girl and her phone usage.  The data needs to be graphed and analyzed so that the companies can create new usage packages for the middle school aged population customer and their families.

You must use the given data to create a profile of Susie Smith’s phone usage.  Then create a graph to represent each set.  Lastly, you will need to draw conclusions from the information.

Steps:

Here is your data:

Susie Smith’s cell phone usage (in minutes) by month

Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sept | Oct | Nov | Dec
216 | 204 | 235 | 209 | 273 | 302 | 322 | 201 | 205 | 197 | 220 | 181

Suzie Smith’s text messages by month

Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sept | Oct | Nov | Dec
901 | 889 | 925 | 955 | 910 | 1178 | 1567 | 1601 | 927 | 892 | 900 | 989

Step 1  Create a new spreadsheet in Excel (Google docs, open office, etc).

Step 2 – Input Susie Smith’s data on 2 sheets.  (Advanced users can create 2 columns on 1 sheet and use the ctrl command to highlight the needed data for each graph)

Step 3 – Create a bar graph for each set of data. (2 graphs) ·        Highlight both columns of data

How does this number compare to the whole year’s average?
Click the insert tab
Select the bar or column graph
Select your chart type as a 2 Dimensional chart
Click on the layout tab & work in the chart title and axis title buttons.
Be sure to use specific information for your titles.

Step 4 – Paste a secret image capture of both pages with the graphs showing into your secret file space.

Step 5 – Answer the following 5 questions below your secret image captures in your secret file space.

What is the average number of minutes Susie used on cell phone calls?
What is the average number of text messages that Susie used?
What is the yearly total for minutes used and the total for texts?
How do these numbers compare?
Looking at both sets of data, explain a trend that you see in Susie’s cell phone use.
What is a logical explanation for what you see in her usage throughout the year?
Find the three highest text message months and exclude them.
About how many texts messages does she make monthly for the rest of the year?
How does this number compare to the whole year’s average?

To Complete this Quest

Save your Secret Image Captures and answers to the above questions in your Secret File Space or as specified by you SSI.

Check off this Quest

Car Cruising

- Car Cruising.wav

Audio for this page (click to start or stop it)

Dig the Data – Car Cruising

Student Activity

Your SSI Chief has been contacted by an advertising agency that represents car companies that want to sell more cars to teachers.  You are needed to collect the data to see what teachers seem to like in a vehicle.  The car companies also want to offer a discount to people if they buy a vehicle in their birth month.  Did you know that the tag on a license plate tells the birth month of the owner?

In this lesson, you will collect data from the cars in your school parking lot and create spreadsheets and graphs in Excel.  Next, you will analyze the data and answer questions about your teachers’ cars.

Be sure that you have a data collection sheet from your SSI Chief.

Steps:

Step 1 – Collect the data with your class or group.

Step 2 – Open the Car Data Spreadsheet (Best opened in Microsoft Excel).Here is also a pdf version .  At the bottom of our sample, notice the tabs that say color, models, and month. Be sure you’re on the correct tab when entering your data.

Step 3 – Fill in the number of the cars for each sheet or category (use the information you gathered in the parking lot).

Step 4 – Click back on the color tab. Highlight the information in both columns A and B.

Step 5 – Click the insert tab.

Step 6 – Select the bar or column graph.

Step 7 – Select your chart type as a 2 Dimensional chart.

Step 8 – Click on the layout tab and work in the Chart Title and Axis Title buttons. Be sure that your titles are specific to the information.

Step 9 – Create a graph for all three sets of data (Colors, Models and Month). You will need a secret image capture of all three pages with the graphs showing to submit in your secret file space.

Step 10 – Answer the following questions completely under your secret image captures.

What is the most popular color car in the lot?

Which month will the car companies give the most discounts according to this data?

Add up the number of cars on each sheet of data. What do you notice and why?

What are the top 3 models of cars in the lot?

Explain why a bar graph is the best choice to show each of these 3 data collections?

 To Complete this Quest

Save your Secret Image Captures and answers to the above questions in your Secret File Space or as specified by you SSI.

Check off this Quest

Quest 4: Sorting

Special Agent Briefing

Agents are often asked to show their findings over a period of time.  Data like that is shown with a line graph.  In this Quest you will be asked to collect data on a company that is trying to make money but often runs into problems.  Your SSI Chief would like you to run the company for 14 days to see what can be done.

As a secret agent you are being asked to investigate a business and how it runs.  You may choose from 2 businesses:

Lucrative Lemonade (Link to student activity with instructions)
-or-
The Coffee Stand             (Link to student activity with instructions)

Steps:

Step 1  Watch the Excel tutorial for this Quest below.

Step 2  Use the links above to go to your quest activity.  Your SSI Chief will direct you on which business to choose or will have you choose for yourself.

Lucrative Lemonade

Lemonade Stand.wav

Audio for this page (click to start or stop it)

Dig the Data - Lucrative Lemonade
Student Activity

In this Quest, your data collection will come from your work at the Lucrative Lemonade Stand.  Depending on the weather and customer preferences, the company will either gain or lose money.  The owner of the company has contacted your SSI Chief and requested a top agent to investigate the operations.  You will have 14 days at the company to try to make as much profit as possible.  Be careful because you can lose everything.  You must record all of your data and conditions in a spreadsheet and analyze the data over the 14 days of work.  Your SSI Chief will be watching for your report in your secret file space.

Hint: On the bottom right is a Fast Forward button. This will make your days go by quicker. It is recommend that you use this this function so you can finish all 14 days before the end of class.

Steps:

Step 1  Get a data collection sheet from your SSI Chief and something to write with or you may work directly on the Excel spreadsheet provided.  The columns that are white should be filled in each round before you start selling lemonade. Then the green columns will be filled in after you sell the day’s lemonade.

Link to  Excel Lucrative lemonade data collection sheet

Step 2  Go to the Lemonade Stand website .

You will be doing a 14 day simulation. (*Your computer will need the java plugin installed. You may have to click on a link to allow it to run, if it doesn’t work please inform your SSI Chief.)

Step 3  Notice you start with $20. Your goal is to make more money than you started with. However, in order to make money, you’ll have to spend some money. In this game, you’ll decide how many lemons, cups of sugar, and ice cubes to use per cup of lemonade. Also, you’ll have to buy the right amounts of each, plus cups. Don’t forget, though, your ice will always melt by the end of the day, bugs can get in your sugar, and lemons will spoil.

So, now buy all of your supplies in what you predict to be the “right” amount for the day.

Step 4  Next, on your information sheet, write down the temperature and the weather forecast. This will be important when deciding how to mix your lemonade for the day.

Step 5  Now it’s time to set up your recipe. How many lemons and cups of sugar are you putting per pitcher? How many ice cubes per cup? Set your preferences, and then write them down on your information sheet.

Step 6  On the next screen, choose how much to charge for each cup of lemonade. You need to make enough money per cup to cover your expenses, but don’t make it so expensive that people won’t buy it. (Optional…you can add up what you spent on your spreadsheet for the day to help decide how much to charge.)  Also, do not change your price during the day as it will not show in your data.

Step 7  Now, run the simulator. Customers will come up to your table and buy your lemonade or give you feedback. A dollar sign above their heads means they think it’s too expensive. See if you can figure out their other “comments.”

Step 8  At the end of each day, write down on your information sheet how many cups you sold, how many potential customers you had, and how much money you finished the day with.

Step 9  Repeat these steps for days 2-14, buying supplies, mixing your lemonade, and recording your results.  Between each day’s sales is where you can make changes to your recipe or price but be sure to record each day on your spreadsheet.  When you have completed all 14 days then it is time to analyze the data.  If you have been working on a paper collection sheet be sure to input everything into Excel.

Step 10  Highlight the daily balance column and click the insert tab to begin making your line graph that shows the change over 14 days.

Step 11  Customize your graph with a chart title and axes labels.
(Optional) You may choose to enhance your chart as seen in the Excel tutorial.      Remember that your starting amount was $20.00 and your goal was to make a profit.

Step 12  Answer the following question in a Word Processing program:

1.  How often did you profit?  Would you recommend this company to continue sales or shut down its business according to your profit?

2.  Did you go bankrupt and have to start over?  What happened? What would the line graph look like for a bankruptcy?

3.  How often did you change your recipe?  Describe how and why you changed it.

4.  What was the average price you charged for your lemonade?  (Do not forget about functions in your Excel program.)

5.  Describe your overall sales trend over the 14 days.  Was it mostly increasing, decreasing or a constant change daily?

To complete this Quest

To complete this Quest, you must save a secret image capture of your spreadsheet and graph to the secret file space.  Then save your word processing file in your secret file space and present the information to the SSI Department Chief.

Check off this Quest

Coffee Stand

- Cofffee Stand.wav

Audio for this page (click to start or stop it)

Dig the Data – Coffee Shop

Student Activity

In this Quest, your data collection will come from your work at the Coffee Shop.  Depending on the weather and customer preferences, the company will either gain or lose money.  The owner of the company has contacted your SSI Chief and requested a top agent to investigate the operations.  You will have 14 days at the company to try to make as much profit as possible.  Be careful because you can lose everything.  You must record all of your data and conditions in a spreadsheet and analyze the data over the 14 days of work.  Your SSI Chief will be watching for your report in your secret file space.

Steps:

Step 1  Get a data collection sheet from your SSI Chief and something to write with or you may work directly on the Excel spreadsheet provided.  The columns that are white should be filled in each round before you start selling coffee. Then the green columns will be filled in after you sell the day’s coffee.

Link to Excel Coffee Shop data collection sheet

Step 2  Go to the Coffee Shop website.

You will be doing a 14 day simulation. (*Your computer will need the java plug-in installed. You may have to click on a link to allow it to run, if it doesn’t work please inform your SSI Chief.)

Step 3  Notice you start with $30. Your goal is to make more money than you started with. However, in order to make money, you’ll have to spend some money. In this game, you’ll decide how much coffee, teaspoons of sugar, and cups of milk to use per cup of coffee. Also, you’ll have to buy the right amounts of each, plus cups. Don’t forget, though, your milk can spoil by the end of the day, ants can invade your sugar causing you to lose some or all of your supply, and your coffee can go stale.  If you are not careful, you will go bankrupt.

Buy all of your supplies in what you predict to be the “right” amount for the day.

Step 4  Next, on your information sheet, write down the temperature and the weather forecast. This will be important when deciding how to prepare the coffee for the day.

Step 5  Now it’s time to set up your recipe. How many teaspoons of coffee for each cup and how much milk and sugar to satisfy your customers? Set your preferences, and then write them down on your information sheet.

Step 6  On the next screen, choose how much to charge for each cup of coffee. You need to make enough money per cup to cover your expenses, but don’t make it so expensive that people won’t buy it. (Optional…you can add up what you spent on your spreadsheet for the day to help decide how much to charge.)  Also, do not change your price during the day as it will not show in your data.

Step 7  Now, run the simulator. Customers will come up to your table and buy your coffee and give you feedback. A dollar sign above their heads means they think it’s too expensive. You can roll over their other comments to see what they have to say.

Step 8  At the end of each day, write down on your information sheet how many cups you sold, how many potential customers you had, and how much money you finished the day with.

Step 9  Repeat these steps for days 2-14, buying supplies, making coffee, and recording your results.  Between each day’s sales is where you can make changes to your recipe or price but be sure to record each day on your spreadsheet.  When you have completed all 14 days then it is time to analyze the data.  If you have been working on a paper collection sheet be sure to input everything into Excel.

Step 10: Highlight the daily balance column and click the insert tab to begin making your line graph that shows the change over 14 days.

Step 11  Customize your graph with a chart title and axes labels.

(Optional) You may choose to enhance your chart as seen in the Excel tutorial.      Remember that your starting amount was $30.00 and your goal was to make a profit.

Step 12  Answer the following questions:

How often did you profit?  Would you recommend this company to continue sales or shut down its business according to your profit?

Did you go bankrupt and have to start over?  What happened? What would the line graph look like for a bankruptcy?

How often did you change your recipe?  Describe how and why you changed it.

What was the average price you charged for your coffee?  (Do not forget about functions in your Excel program.)

Describe your overall sales trend over the 14 days.  Was it mostly increasing, decreasing or a constant change daily?

To complete this Quest

To complete this Quest, you must save a secret image capture of your spreadsheet and graph to the secret file space.  Then save your word processing file in your secret file space and present your information to your SSI.

Check off this Quest

Quest 5: Sort it Out

Video 1: Autofill and Sort video tutorial posted on Vimeo.com

Introduction

Welcome back agents. Your company wants to be the first to provide help to countries that are struggling with different issues. The latest information has been released. Therefore they must receive the country information quickly and quietly to decide how to help. We need you to use your skills to prepare the documents and get them to your SSI Chief as quickly as possible.

Step 1 - Watch all 3 Excel tutorials provided to be sure that you are prepared. The videos are prepared matching to the topics you will be carrying out on this Quest ( Note we hope control agents do not prevent you from seeing these. They are hosted at Vimeo.com)

Video 1: Autofill and Sort is located at the top of this page

Video 2: Making pages

Video 3: Pivot Tables

Step 2 - Open the Sort it Out document provided below to use for this with Quest.

 Sort it Out.xls

Step 3 - Rename the main sheet of information to "Main Data" (shown in the video Part 1 at the top of this page)

Step 4 - Create a new sheet to represent the data with the Countries in alphabetical order (sort A-Z). Name this sheet "ABC Order". (shown in Video 2)

Step 5 - Your company wants to help out the countries that may be overpopulated. Therefore, they need a new sheet to represent the data to support this idea. Name the sheet "Population" and show the total amount of people that are included in the data on this sheet at the bottom of the columns (use formulas to total the column).

Step 6 - Life expectancy is the average age that people will live to be. Your company wants to help extend peoples' lives by teaching good health practices to those who do not live as long as others in the world.  A new sheet is needed to represent the data. Name the sheet "LIFE EXP", sort life expectancy by least to greatest.  Show the average life expectancy of all the countries included in the data on this sheet at the bottom of the column. Insert a text box at the bottom and explain how this number compares to the United States.

Step 7 -  Using the main data sheet, create a new sheet to find the total area (land and water) of the United States. Use that total to easily find the amount for each of the countries. Name this sheet "TOTAL AREA". Include a total amount of area that is represented by all of the countries in the data at the bottom of the column.

Step 8 -  Create a new sheet with a pivot table that includes Country, Continent and Life Expectancy. Name this sheet "PIVOT TABLE". Using a text box, explain which continent needs the most help in extending life expectancy. (shown in Video Part 3)

Step 9 - Check with your SSI Chief as to how you should turn this in (the completed sheet, screenshots, post it online...)

Dig the Data Question 1

In a spreadsheet program a cell is created by a column and a row.

- True
Feedback: Try Again
- False
Feedback: Great Job!

Dig the Data Question 2

Using the data in a spreadsheet, you can use a bar graph to show the changes over time.

- True
Feedback: Great job!
- False
Feedback: Try again

Dig the Data Question 3

In a spreadsheet program, a function is used to create a graph.

- True
Feedback: Great job!
- Fals
Feedback: Try again

Dig the Data Question 4

Data that is organized in a spreadsheet can help you to analyze and make predictions.

- True
Feedback: Great job!
- False
Feedback: Try again

Dig the Data Question 5

By using a spreadsheet program you can sort data and organize each sort on to a new sheet while keeping the original data intact.

- True
Feedback: Great job!
- False
Feedback: Try again

Dig the Data Award

Congratulations you have completed the Dig the Data Mission.

To receive your Badge or certificate for completion from your SSI Chief :

1.  Take this Dig the Data Quiz .  The password for the quiz is dig21 .  Do your best!

2.  Take a screenshot of your quiz results to show your SSI Chief.

3.  Please take this short survey to share your ideas on how to improve these activities and this 21things4students resource.

4.   Show your completed work for this mission to your SSI Chief to receive your Dig the Data Badge.