As part of a training exercise, the captain of the battleship U.S.S. Iowa is attempting to

sink a training buoy 90 kilometers away using the 16" main deck guns. Consulting her

ballistics handbook, she finds the following function which describes the trajectory of a

projectile:

X

y = x( tana) + 1/29v₂cosa

where g = -0.00981 km/s², y is the vertical distance of the projectile (km) and x is the

horizontal distance from the ship (km). The projectile is fired with muzzle velocity vo and

at an angle a from horizontal.

Build an Excel spreadsheet to implement this equation. If the captain first attempts a

shot with muzzle velocity vo = 0.942 km/s and at an angle a = 35°, calculate and plot the

path of the projectile from x = 0 to 90 km, in 5 km increments. Assume the deck of the

ship is at sea level. Use proper annotations within the sheet and label your plot

appropriately. Your program must use both absolute and relative references. Structure

your program so that the sheet updates automatically when the angle a is changed.

Finally, use the spreadsheet you have created to answer the following question (use

guess and check). Report your answers clearly in the Excel worksheet.

1. On her first attempt given above, the captain will undershoot the buoy. Keeping

the same initial velocity, what does the angle a need to be (approximately) in

order for the projectile to hit the buoy at x = 90 km? (assume +/- 0.1 km vertically

counts as a hit)

Submit your spreadsheet with a cover page. Title both your Excel

spreadsheet and your submission.