Excel Modeling

CT: Excel Modeling (1)

Due: 10/17/2016

In order to receive credit for this competency test, you must submit the completed competency test to the

course blackboard site (under “Competency Tests”, click on the link for this competency test itself, upload your

document, and hit the “submit” button) by the start of your on class on 10/17/2016. Late assignments will not

be accepted for course credit, but will be evaluated for learning purposes. This is an individual (not a group)

assignment. Successful completion of this competency test gives you the opportunity to demonstrate the excel

modeling competency toward your class grade.

excel modeling: use Excel to implement and then draw appropriate conclusions from a difference-equation-

based mathematical model

The grading rubric for how this competency test will be assessed is attached, along with a technical note describing

how to create “IF statements” in Excel (you’ll need to use these in your model).

IN ORDER TO BUILD THE MODEL REQUIRED FOR THIS ASSIGNMENT, YOU WILL NEED TO

USE “IF STATEMENTS” IN YOUR EXCEL MODEL. UNLESS YOU ARE ALREADY VERY

COMFORTABLE WITH USING “IF STATEMENTS” IN YOUR EXCEL EQUATIONS, PLEASE

WATCH THE FOLLOWING TUTORIALS (WATCH BOTH, ALL THE WAY THROUGH) BEFORE

YOU BEGIN THE ASSIGNMENT:

 http://www.youtube.com/watch?v=r_ur2ZzPwNQ

 https://www.youtube.com/watch?v=LEc3Va_ODf0

The Problem Background* You are an engineer with a deep understanding of mechanics and aerodynamics who also works as a skydiving

instructor. One day, you are on a plane following a group of beginners as they make their first jump when one of

the students turns to you and yells that a previous student hit their head as they left the plane and is apparently

unconscious falling in a spread-eagle position. You are five seconds behind the student when you jump and dive

head first in a very streamlined position. Your task is to use Excel to model appropriate difference equations and

determine if you can catch and rescue the unconscious student. (If you catch the student, you can assume that

you can deploy both of your chutes immediately and drift calmly to the ground at a descent rate of 10 ft/sec.)

To determine whether or not you will be able to perform the rescue you need to evaluate the position of both the

student and yourself as a function of time and of position above the ground. You will need some “initial

conditions” as well as a formula for acceleration, and these are given below:

y(leaving plane) = yo = 5000 ft (This is the altitude of the plane)

V(leaving plane) = Vo = 0 ft

sec (This is the initial dropping speed when leaving the airplane)

a = (32.2 − CVy 2)

ft

sec2 (This calculates downward acceleration, accounting for aerodynamic drag)

In the case of the student the value of C is Cs=0.0011 /ft. In your case, the value of C is Ci=0.00038 /ft. *

Consequently, the acceleration formulas are different in each case:

a student

= a s = 32.2 - 0.0011V

s

2( ) ft

sec 2

a instructor

= a inst

= 32.2 - 0.00038V s

2( ) ft

sec 2

ainstructor= ainst=(32.2-0.00038Vinst 2 )

ft

sec2

The difference equations that describe both descents are the following:

Vi+1 = Vi + aiΔt

or, if we plug in the formula for acceleration:

Vi+1 = Vi + (g-CVi 2)Δt

the position is given by:

yi+1 = yi - ViΔt

*These numbers are calculated from published drag coefficients for human shapes falling in the atmosphere. They are not the drag

coefficients, but include additional considerations such as the density of the air and cross sectional area of the human.

The problem is further complicated by the fact that you leave the plane five seconds later than the unconscious

student. If we choose t=0 to represent the time at which the student leaves the plane, the equations describing the

student’s position (ys(i+1)) and velocity (Vs(i+1)) as a function of time are:

Vs(i+1) = Vs(i) + (32.2 – 0.0011V 2 s(i))Δt

ys(i+1) = ys(i) – Vs(i)Δt

When you leave the plane 5 seconds later, your position (yinst(i+1)) and velocity (Vinst(i+1)) are described by the same

equations, but with a smaller drag coefficient:

Vinst(i+1) = Vinst(i) + (32.2 – 0.00038V 2 inst(i))Δt

yinst(i+1) = yinst(i) – Vinst(i)Δt

Finally, if you catch the student and deploy your chutes you will both continue your descent at 10 ft/sec until you

reach the ground. (In this scenario you need to embed an “IF” statement that slows the descent rate to this speed

for both the instructor and the student.)

Using these difference equations, and using t=1 sec, model these parallel trajectories and determine if this story

has a happy ending. Instructions for completing the assignment are given on the next two pages.

What to Submit on Blackboard

1. A single Excel spreadsheet that computes and compares both trajectories. For grading purposes, this spreadsheet must include a plot that shows the entire trajectory of descent of both the student and

instructor.

2. A .doc or .pdf document containing the following information (written in completed sentences): 1. A brief statement on whether the story has a happy ending. 2. If the story has a happy ending, answer the following questions:

a. Starting at the time at which the student was knocked unconscious, how long did it take for the instructor to catch and rescue the student?

b. At what altitude was the student rescued? c. Starting at the time at which the student was knocked unconscious, how long did it take for

the student and instructor reach the ground?

3. If the story has an unhappy ending, answer the following questions: a. What was the necessary altitude for the rescue to be possible with 1000 feet to spare? b. In this new scenario, starting at the time at which the student was knocked unconscious,

how long did it take for the instructor to catch and rescue the student?

c. In this new scenario, at what altitude was the student rescued? d. In this new scenario, starting at the time at which the student was knocked unconscious,

how long did it take for the student and instructor reach the ground?

CT: Excel Modeling (1)

Due: 10/17/2016

Student Name:

excel math model: use Excel to implement and then draw appropriate conclusions from a difference-equation-based

mathematical model

Competency Demonstrated (Y/N)?

Excel Modeling Competency Grading Rubric:

The accuracy of the model needs to be assessed for this competency. In order to have successfully demonstrated

mastery of the competency, the following attributes must all be present in the submitted .xls or .xlsx model file(s).

 The spreadsheet is organized and logical________

 The trajectories of both the student and instructor are calculated correctly__________

 The trajectories of both the student and instructor are plotted __________

 Logic in the model is included that slows the descent rate for the student and instructor after rescue to 10ft/sec ________

o Note: If initial rescue was unsuccessful, the student was to increase altitude until it was possible so this should still be a demonstrated part of the spreadsheet logic.

 The time and altitude of rescue predicted by the student’s model are calculated, noted in the report, and are accurate ________

 The total time to reach the ground for both the instructor and student are calculated, noted in the report, and are accurate________

NOTES: