You ask how is it possible to input 10g of acceleration at one point and see more than that somewhere else on the structure. You actually input more than 10g because you are also suddenly turning on 1g at t=0 by including Standard Earth Gravity.  I more refined analysis would allow the shaft to come to static equilibrium with the gravity load, then apply the transient shock.  A simpler approach is to turn off gravity.

With the sinusoidal input, the peak Y acceleration is 115 m/s^2.
Without gravity, the peak Y acceleration is                 108 m/s^2

Another reason is that the mass and stiffness of the structure create amplification of input acceleration to create more or less acceleration at various points along the length. Every dynamics textbook starts with a simple mass, spring, damper system. When the input frequency is close to the first natural frequency of the system, the ratio of the acceleration of the mass to the acceleration of the base is greater than 1.0

You noticed that the point you applied the full sine curve of acceleration ended up 11 mm higher than it started. Well you didn’t need to run a dynamic simulation to know that. Simply integrate acceleration twice with respect to time to get displacement and you will see exactly the curve for displacement that you plot in the simulation.

If you want the point to end at the same point it started from, you need a different waveform.  You could use a wavelet that has an amplitude of 10g in one direction, but it first goes in the opposite direction at about -6g before it turns around and hits 10g and has another -6g at the end.

If you perform an FFT on this acceleration waveform, you will see the peak frequency content is at 37 Hz, which is the same as the full sine curve that was 0.027s long.

Your final quesiton noted that a displacement BC sets Y=0  in the modal but that point moved in the transient. That is because it was a modal superposition analysis that applied a base acceleration BC on that same point. Base acceleration shakes the boundary condition you told it to, so it moved.