**Solve this problem using Excel (suggestion: print this page and cross off the directions as you complete them).**

Supplies are dropped from an airplane to land at a certain point. The velocity of the package at release from the airplane is the speed of the airplane, **v0x=275 ft/s.** The acceleration of the package due to gravity is **ay** **= -32.2 ft/s2**. The displacement in the y direction can be found using the following equation: y – y0 = v0yt + ½ayt2. The final position of the package (y) is on the ground, and y0 represents the height of the plane when the package is dropped. The initial velocity in the y direction (v0y) is zero. Therefore the equation becomes: y = y0 + ½ayt2.

The distance the package travels in the x direction can be found from the equation: x – x0 = v0xt + ½axt2. The final position of the package (x) is measured from the initial position (x0). If x0 is taken as zero, and if the plane is traveling at a constant speed (ax is zero), then the equation becomes: x = v0xt.

**Step-by-step Excel Instructions:**

1. Create a table for the initial data:

i. In cell B2 put the label “Plane height:”

ii. In cell C2 put the initial height of the plane in feet (ft): 450

iii. In cell B3 put the label “Acceleration (g):”

iv. In cell C3 put the acceleration of gravity in ft/s^2: -32.2

v. In cell B4 put the label “Velocity:”

vi. In cell C4 put the velocity in ft/s: 275

vii. In cells D2, D3, and D4, place the appropriate units

2. Create a table for the data and calculations:

i. In cells B6, C6, and D6 respectively, put the labels “t”, “x”, and “y”.

ii. In cell B7, enter the initial time: 0

iii. Increment the time in ½-second intervals in column B, from B8 to B16.

iv. Center all of the labels and data in columns B, C, and D.

v. Enter the formulas to calculate 1) x in cell C7 and 2) y in cell D7.

vi. Copy the formula from C7 to C8 through C16 and from D7 to D8 through D16.

vii. Format the numbers in the y column to one decimal point.

3. Create separate graphs of 1) “x vs. t” and of 2) “y vs. t”. Using the “x vs. t” graph as an example:

i. Select the x data (C7:C16), and then select the “**line with markers**” plot type.

ii. To add the time on the horizontal axis, right-click on the chart and chose “**Select Data**…”. In the “**Select Data Source**” dialog box, chose “**edit**” for the **Horizontal (Category)**. In the next dialog box, simply select (drag the mouse over) the time data (B7:B16).

iii. Add **major vertical gridlines**, **chart titles**, and **axis labels** using the buttons under the “**Layout**” tab. Include the correct units in the axis titles and rotate the vertical axis title to the horizontal.

iv. Remove the “series” labels from these two charts (Layout/legend).

v. Repeat these steps for the “y vs. t” graph.

4. Create a graph of “x and y vs. t”

i. highlighting the x and y data before choosing the chart type or by copying and modifying one of the existing charts using the “**Select Data…**” command.

ii. Change the series labels to read “x” and “y” or “horizontal distance” and “vertical distance”.

iii. Add the appropriate chart title and axis labels.

5. Use trial and error:

i. Change the values in the “t” column to determine, by trial and error, the time at which the package hits the ground.

b. Using the “Goal Seek” tool:

i. Goal Seek can be found under the “**Data**” tab, “**What-If Analysis**” button.

ii. To use goal seek, open the Goal Seek dialog box: **Set cell:** **D16**, **To Value: 0**, **By changing cell: B16**.

iii. Record your answer in B18, then return the spreadsheet to its initial configuration.

**What to Submit:**

Write a memorandum to the instructor that __explains the data tables and the graphs__. **DO NOT** refer to “cell B18” or any other cell (When looking at the data table in a Word document, how can you locate cell D9?).

1. Copy and paste the graphs (3) and the data tables into a Word document. It is recommended that you** paste the data tables and each graph separately as a Picture (Windows or Enhanced Metafile).** **Do not **paste the data or the graphs as a Graphic Object, or as an Excel Object (use “**Paste Special**” found under “**Paste”** on the Ribbon). **Use appropriate titles or captions.**

2. Explain each graph and data table. **In the text, refer to each graph and table using appropriate labels.**

3. Explain the number (time) obtained by using the “Goal Seek” function.

4. Carefully arrange the text, data tables, and charts to print correctly (use **Print Preview**).

5. Insert page numbers and a header if you have multiple pages.