Real-World Linear Programming with Excel
Created byGeorge Mauser
2 views0 downloads

Real-World Linear Programming with Excel

Grade 9Math3 days
This project challenges ninth-grade students to apply linear programming to solve real-world optimization problems using Excel. Through engaging entry events like simulations and environmental challenges, students identify variables and constraints to formulate ten-variable, ten-equation scenarios. They then use Excel's Solver tool to set objectives and solve problems, interpreting the results in context. This experience hones their skills in quantitative problem-solving, decision-making, and digital tool usage, fostering a deeper understanding of mathematical applications in various fields.
Linear ProgrammingOptimizationExcel SolverReal-World ApplicationsMathematical ModelingProblem-SolvingProject-Based Learning
Want to create your own PBL Recipe?Use our AI-powered tools to design engaging project-based learning experiences for your students.
📝

Inquiry Framework

Question Framework

Driving Question

The overarching question that guides the entire project.How can we use linear programming to solve real-world optimization problems by setting up ten-variable, ten-equation scenarios in Excel?

Essential Questions

Supporting questions that break down major concepts.
  • What is linear programming and how is it used to solve real-world problems?
  • How do we identify variables and constraints in a linear programming problem?
  • What are the steps involved in setting up and solving a linear programming problem using Excel?
  • How can we interpret the solution of a linear programming problem in the context of the given scenario?
  • What role does optimization play in linear programming, and why is it important?

Standards & Learning Goals

Learning Goals

By the end of this project, students will be able to:
  • Understand and apply the concept of linear programming to solve real-world problems.
  • Develop skills in identifying and defining variables and constraints for linear programming models.
  • Gain proficiency in using Excel to set up and solve linear programming problems involving multiple variables and equations.
  • Interpret and validate the solutions obtained from linear programming models in practical contexts.
  • Explore the importance of optimization in decision-making processes.

Common Core Standards

HSN-Q.A.1
Primary
Use units as a way to understand problems and to guide the solution of multi-step problems; choose and interpret units consistently in formulas; choose and interpret the scale and the origin in graphs and data displays.Reason: This project involves setting up and solving a linear programming problem. Understanding units and their interpretation is fundamental in identifying and managing variables within the equations.
HSN-CN.A.1
Secondary
Know there is a complex number i such that i² = –1, and every complex number has the form a + bi with a and b real.Reason: Understanding mathematical concepts including complex numbers may enhance comprehension of variable manipulation in linear programming problems.
A-CED.A.3
Primary
Represent constraints by equations or inequalities, and by systems of equations and/or inequalities, and interpret solutions as viable or nonviable options in a modeling context.Reason: Students will be representing constraints by equations and interpreting solutions in the context of the scenarios they develop for their linear programming models.
A-REI.D.11
Supporting
Explain why the x-coordinates of the points where the graphs of the equations y = f(x) and y = g(x) intersect are the solutions of the equation f(x) = g(x); find the solutions approximately using technology.Reason: Analyzing solutions of systems of equations is critical when interpreting the outcome of a linear programming problem that has been set up in Excel.

Entry Events

Events that will be used to introduce the project to students

Marketplace Simulation

Kick off the project by transforming the classroom into a bustling marketplace simulation, where students adopt roles such as business owners, consumers, and resource managers. Introduce a scenario where they must optimize resources to maximize profits, leading to the creation of their own linear programming models. This real-world application connects directly to the project's goals and taps into students' interest in economics and entrepreneurship.

Escape Room Challenge

Engage students with a math-themed escape room, where each puzzle they solve unlocks a variable or equation in their linear programming problem. By the end of the game, they'll have all ten variables and equations needed for the project. This immersive experience challenges conventional classroom settings and fosters collaborative inquiry as students work through interconnected mathematical challenges.

City Planner for a Day

Present students with a scenario where they become city planners tasked with designing a sustainable city that optimizes residential, commercial, and public spaces. They'll use linear programming to balance competing needs and constraints, making the learning process personally relevant and deeply engaging. This scenario leverages creative problem-solving skills aligned with real-world urban planning challenges.

Eco-Challenge: Save the Forest

Create an environmental context as students work with conservationists to develop a plan that balances economic growth and forest preservation. They'll model variables such as deforestation rates and eco-tourism potential using linear programming techniques. This project invites students to explore environmental science and advocacy, fostering a sense of social responsibility along with mathematical inquiry.
📚

Portfolio Activities

Portfolio Activities

These activities progressively build towards your learning goals, with each submission contributing to the student's final portfolio.
Activity 1

Marketplace Variable Explorer

Students begin their journey by exploring the concept of variables within the Marketplace Simulation. As they engage in role-play, they identify key economic elements that could be transformed into variables for a linear programming model, such as resources available, cost, and desired profits.

Steps

Here is some basic scaffolding to help students complete the activity.
1. Participate in the Marketplace Simulation, taking note of different roles and their responsibilities.
2. Identify potential variables (e.g., materials, labor cost, revenue targets) during the simulation.
3. Document observations in a graphic organizer to define and describe each variable.

Final Product

What students will submit as the final product of the activityA graphic organizer listing ten potential variables along with their descriptions.

Alignment

How this activity aligns with the learning objectives & standardsAligns with HSN-Q.A.1 by using units and understanding them in context for variable identification.
Activity 2

Equation Formulation Workshop

In this activity, students transform the variables identified into equations. They learn how to formulate linear equations by representing relationships between the variables identified in the marketplace scenario, reflecting real-world economic principles in mathematical terms.

Steps

Here is some basic scaffolding to help students complete the activity.
1. Review the ten variables listed in the graphic organizer.
2. Research and understand relationship dynamics between these variables from economic perspectives.
3. Group related variables and compose mathematical equations that express these relationships.

Final Product

What students will submit as the final product of the activityA set of ten equations derived from the identified variables.

Alignment

How this activity aligns with the learning objectives & standardsAligns with A-CED.A.3 as students represent constraints using equations and systems to model contextual relationships.
Activity 3

Excel Solver Bootcamp

Students attend a hands-on workshop to learn how to input their equations into Excel and use Excel's Solver tool. By setting objectives, constraints, and using the Solver function, they gain practical skills in digital problem-solving methods.

Steps

Here is some basic scaffolding to help students complete the activity.
1. Input the created equations into an Excel spreadsheet.
2. Use Excel's Solver tool to define the objective and set constraints based on the problem scenario.
3. Run Solver and analyze the solution output.

Final Product

What students will submit as the final product of the activityAn Excel file containing the linear programming setup and solutions.

Alignment

How this activity aligns with the learning objectives & standardsSupports A-REI.D.11 as students gain experience with technology to find and analyze solutions through graphical and tabular representations.
Activity 4

Interpretation and Presentation Panel

In the final stage, students interpret the solutions of their linear programming model and present their findings. This activity emphasizes understanding and communicating the significance of their model in the context of the original marketplace or real-world scenario.

Steps

Here is some basic scaffolding to help students complete the activity.
1. Review the solutions obtained from the Excel Solver.
2. Prepare a presentation explaining how the solutions address the original scenario’s goals.
3. Reflect on the optimization process and discuss possible improvements or real-world implications.

Final Product

What students will submit as the final product of the activityA presentation with visuals and explanations interpreting the problem's solution.

Alignment

How this activity aligns with the learning objectives & standardsAddresses learning goals to interpret and validate solutions, aligning with HSN-Q.A.1.
🏆

Rubric & Reflection

Portfolio Rubric

Grading criteria for assessing the overall project portfolio

Linear Programming Assessment Rubric

Category 1

Problem Setup and Variable Identification

Assess the ability to identify and define key variables within a given scenario, reflecting comprehension of economic and mathematical principles.
Criterion 1

Variable Identification

Correct identification and description of ten key variables in the provided scenario.

Exemplary
4 Points

All ten variables are accurately identified with clear and thorough descriptions reflecting sophisticated understanding.

Proficient
3 Points

Most variables are correctly identified with clear descriptions reflecting solid understanding.

Developing
2 Points

Some variables are identified, but descriptions are incomplete or unclear, reflecting basic understanding.

Beginning
1 Points

Few variables are correctly identified, with vague or incorrect descriptions, indicating limited understanding.

Criterion 2

Graphic Organizer Completeness

The thoroughness and clarity of the graphic organizer used to document variables.

Exemplary
4 Points

The graphic organizer is complete, detailed, and comprehensively structures the variables and their relationships.

Proficient
3 Points

The graphic organizer is mostly complete and logically structures most of the variables and relationships.

Developing
2 Points

The graphic organizer is incomplete and only loosely structures variables.

Beginning
1 Points

The graphic organizer lacks critical details and offers minimal insight into variable relationships.

Category 2

Equation Formulation and Constraint Representation

Evaluate the creation of linear equations from identified variables and the representation of constraints in the context of the problem.
Criterion 1

Equation Correctness

Accuracy and appropriateness of the formulated equations.

Exemplary
4 Points

Equations are precise, logically constructed, and represent the scenario constraints accurately.

Proficient
3 Points

Equations are mostly correct, with logic that aligns well with the scenario constraints.

Developing
2 Points

Equations have some inaccuracies or lack logic in representing constraints.

Beginning
1 Points

Equations are largely incorrect or irrelevant to the scenario constraints.

Category 3

Excel Solver Application

Assess the ability to effectively use Excel for solving the linear programming model, focusing on technical skills and solution accuracy.
Criterion 1

Excel Solver Setup

Correct implementation of the Solver tool in Excel, setting appropriate goals and constraints.

Exemplary
4 Points

Solver setup is flawless, with accurate goal settings and constraints leading to correct solutions.

Proficient
3 Points

Solver setup is mostly accurate, with minor errors that do not significantly impact the solution.

Developing
2 Points

Solver setup has noticeable errors impacting the solution accuracy.

Beginning
1 Points

Solver setup is incorrect, leading to invalid solutions.

Category 4

Solution Interpretation and Communication

Evaluate the ability to interpret and present the solution, explaining its significance and implications in the context of the scenario.
Criterion 1

Solution Interpretation

Depth and accuracy in interpreting the linear programming solution.

Exemplary
4 Points

Interpretation is insightful, connecting the solution comprehensively with the scenario’s requirements.

Proficient
3 Points

Interpretation is clear, adequately linking the solution to the scenario’s requirements.

Developing
2 Points

Interpretation lacks depth, with limited connection between the solution and scenario requirements.

Beginning
1 Points

Interpretation is minimal, with little to no connection to the scenario requirements.

Criterion 2

Presentation Quality

Effectiveness of the presentation in communicating findings and implications.

Exemplary
4 Points

Presentation is strong, engaging, and clearly communicates the solution’s significance with comprehensive visuals.

Proficient
3 Points

Presentation is clear and mostly effective with good use of visuals.

Developing
2 Points

Presentation is unclear at times, with visuals that may not fully support the message.

Beginning
1 Points

Presentation is ineffective, lacking clarity and sufficient visuals.

Reflection Prompts

End-of-project reflection questions to get students to think about their learning
Question 1

Reflect on the real-world scenario you worked on during this project. How has your understanding of linear programming's application in solving complex problems evolved?

Text
Required
Question 2

On a scale of 1 to 5, how confident do you feel about using Excel to solve linear programming problems after this project?

Scale
Required
Question 3

What was the most challenging aspect of this project, and how did you overcome it?

Text
Optional
Question 4

Reflect on the optimization concepts you learned in this unit. How do they apply to real-world decision-making processes?

Text
Required
Question 5

Which entry event (marketplace simulation, escape room challenge, city planner scenario, or eco-challenge) did you find most engaging, and why?

Multiple choice
Optional
Options
Marketplace Simulation
Escape Room Challenge
City Planner for a Day
Eco-Challenge: Save the Forest