Excel Problem Solving

1. Excel Problem Solving

2. 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

3. 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

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

5. Sample Problem What is the optimal cruising altitude for a given 250 mile flight plan?

6. 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).

7. 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

8. 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.

9. 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

10. 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

11. 2. Represent Sample Problem The flight path consists of the four phases below

12. Fuel consumption differs at each phase

13. 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)

14. 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)

15. 3. Plan Sample Problem

16. 4. Implement Sample Problem Ftaxi+takeoff= 3324 lb Fclimb = 0.646 X Altitude Fcruise = Distance X Rate Fdescent+land = 0.323 X Altitude

17. 4. Implement Sample Problem Lowest value for total fuel consumption is at 14,000 ft.

18. 4. Implement Sample Problem The sort function lets us see this result more clearly

19. 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…

20. 5. Evaluate Sample Problem This confirms our previous answer! Identify the minimum value this occurs at approx. 14,000 ft.

21. 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.

22. 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!