Excel Problem Solving

1 / 22

# Excel Problem Solving - PowerPoint PPT Presentation

Excel Problem Solving. How is Excel helpful with problems?. Excel has many tools to help us solve problems! Excel has tools for… Storing, organizing, manipulating, and analyzing data Providing tabular and graphical representation Obtaining numeric solutions.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## Excel Problem Solving

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Excel Problem Solving

How is Excel helpful with problems?

Excel has many tools to help us solve problems!

Excel has tools for…

• Storing, organizing, manipulating, and analyzing data
• Providing tabular and graphical representation
• Obtaining numeric solutions
Excel & the Problem Solving Method
• Define
• Represent
• Graphs, tables, flow charts
• Plan
• Hierarchy, sort data, filter data
• Implement
• Conditional formatting, calculations, graphical analysis
• Evaluate
• Statistical Analysis, conditional formatting
Example

Let's use some of Excel's problem solving tools and the Problem Solving Method to work on a sample problem.

Sample Problem

What is the optimal cruising altitude for a given 250 mile flight plan?

Sample Problem: Additional Info
• Jet propulsion is more efficient at higher altitudes, but climbing uses more fuel.
• On short flights the climb represents a larger portion of the total fuel required (due to more time spent climbing).
Sample Problem: Given Info
• Fuel needed for taxi and take-off : 3324 lb
• Fuel needed for climbing: 0.646 (lb/ft) x altitude(ft)
• This chart estimates the rates of fuel consumption while cruising at a given altitude
1. Define Sample Problem

What is the optimal cruising altitude for a flight plan?

• This is an optimization problem
• We need to find the optimum altitude at which the total fuel consumption is the lowest.
1. Define Sample Problem

What is the optimal cruising altitude for a flight plan?

We need to recognize the tradeoffs:

• As altitude increases, more fuel is consumed by climbing
• The rate of fuel consumption decreases while cruising at higher altitudes
1. Define Sample Problem

Let's review the given information:

• Fuel needed for taxi and take-off = 3324 lb
• Fuel needed for climbing = 0.646 (lb/ft) x Altitude(ft)
• To cruise at a given altitude, fuel consumption is a product of distance and rate at that altitude.
• Fuel required for descent and taxi is approximately half of that required for climbing
2. Represent Sample Problem

The flight path consists of the four phases below

3. Plan Sample Problem

Let's review the equations to calculate fuel consumption at each phase:

• Ftake off + taxi = 3324 lb
• Fclimb= 0.646 lb/ftx Altitude(ft)
• Fcruise= Distance(mile) x Rate (lb/mile) [from given table]
• Fdescent+land= Fclimb/2 = 0.323 lb/ftx Altitude(ft)
3. Plan Sample Problem

To calculate total fuel required:

Ftotal = Fuel required at each phase

Ftotal= Ftake off + taxi + Fclimb + Fcruise + Fdescent+land

= 3324 lb

+ 0.646 lb/ftx Altitude(ft)

+ Distance(mile) x Rate(lb/mile)

+0.323 lb/ft X Altitude(ft)

4. Implement Sample Problem

Ftaxi+takeoff= 3324 lb

Fclimb = 0.646 X Altitude

Fcruise = Distance X Rate

Fdescent+land =

0.323 X Altitude

4. Implement Sample Problem

Lowest value for total fuel consumption is at 14,000 ft.

4. Implement Sample Problem

The sort function lets us see this result more clearly

5. Evaluate Sample Problem

Can we use other approaches involving the same variables to verify our calculations?

• The problem has been solved numerically
• A graphical representation can confirm our answer
• Here, we will create a scatter plot of Total Fuel Consumption vs. Altitude…
5. Evaluate Sample Problem

This confirms our previous answer!

Identify the minimum value

this occurs at approx. 14,000 ft.

Problem Solving

Could we have solved this problem another way?

Yes!

We used a manual method for solving this optimization problem. Excel also has the capability of solving these problems using a tool called Solver, which automatically finds the most optimum solution.

So... What did we learn?

Excel is a tool for…

• Storing, organizing, manipulating, and analyzing data
• Providing tabular and graphical presentations
• Obtaining numerical solutions

Excel is a very useful and important problem solving tool!