Data Analysis

4204 Data Analysis (.5 credit)

This one-semester course focuses on analyzing and solving typical problems found in business, industry, and science. A highlight of this course will be students managing their own fictitious companies to become the most profitable company in competition with the other student companies. Students will use Microsoft Excel’s advanced capabilities to determine such parameters as their company’s production rates, advertising levels, and overtime requirements. Other topics include collecting and analyzing data, interpreting the results, evaluating errors, and presenting the results.

Prerequisite: Technology Utilization or equivalent

[Open to grades 10-12]

Course Outline

I. Working with lists

Students have worked with tabular presentations of data in various contexts. Tables exhibit data in one form for one purpose, and may be formatted in various ways. Lists are not so much the things to be displayed as the raw data from which many different forms of display may be derived. They are a source of information, from which many different tables may be derived as separate objects.

A. Introduction to lists

1. What is a data list

2. Creating a list

3. Using a data form to edit data in the list

4. Creating natural language formulas

B. Viewing a data list

1. Sorting data

2. Using auto-filters

3. Using custom auto-filters

4. Using conditional formatting

5. Using the subtotals

6. Using outlining to organize a list

C. Pivot Tables

1. Using pivot tables to summarize a list

2. Creating a pivot table

3. Changing the layout of a pivot table

4. Adding a field to a pivot table

5. Refreshing a pivot table

II. Data tables

Students will learn how to apply Excel to perform basic cost-volume-profit analysis. They will use Excel’s one-variable and two-variable data tables to perform what-if analyses, examining the effect that changing one or more input cells has on key result cells in their workbooks. Students will use Scenario Manager to explore the ramifications of different scenarios on the results of their workbooks.

A. One-variable data tables

1. Creating a one variable table

2. Using a one variable table

3. Graphing the data in one variable table

B. Two-variable data tables

1. Creating a two variable table

2. Using a two variable table

3. Graphing the data in two variable table

C. Using the scenario manager

D. Principles of cost-volume-profit relationships

III. Four analysis methods

What-if analysis:   Examine a number of alternative outcomes
Trial and error:     Discover the input value or values that produce a specific result
Goal seek:            Discover the input value that produces a specific goal
Solver:                 Find the input values that will optimize an output given several constraints.

A. What-if analysis

B. Trial and error

C. Goal seek

1. Input (changing cell)

2. Goal (set cell)

D. Solver

1. Optimization criteria

2. Input and output variables

3. Constraints

a. Binding and non-binding

b. Slack

4. Answer report

IV. Steps of data analysis

Students will be guided through each of these steps while conducting a laboratory experiment.

A. Identify the Problem

1. Translate the problem into mathematical language and symbols

2. Process this input mathematically

B. Develop a Plan

1. Identify data which is to be collected

2. Decide upon how to collect and analyze any needed data

3. Organize proposed tasks into a coherent plan

C. Collect Data

1. Identify the inputs (independent variables)

2. Identify the outputs (dependent variables) which will be measured

3. Determine how much data needs to be collected

4. Record and organize the collected data

5. Determine the accuracy of the measurements

D. Analyze the Data

1. Summarize and evaluate the results

2. Propose models to explain the results

3. Decide if additional data is needed

E. Formulate the Solution

1. Draw conclusions and support them

2. Evaluate information, perceive patterns, and identify relationships,

F. Assess the Solution

1. Interpret results with respect to the original problem

2. Assess how well the solution satisfies the original problem

G. Communicate the Results

1. Communicate the results in an appropriate format

2. Present results in written, oral and visual formats

V. Production Planning Project

This course-long project will allow students to apply what they have learned to a common business problem. Students will determine how many products to produce each week in order to maximize their profit over the entire project.