If an S is added to the end of an existing word, does the S have to be the first or last letter of the new word or can it be somewhere between?
It can be somewhere in between. The rules are that the tiles placed must be in a straight line (horizontal or vertical) connected to the existing words, and there cannot be any empty spaces between tiles placed.
For more information it may help to know that this is called 'hooking': https://en.m.wikibooks.org/wiki/Scrabble/Rules#Gameplay
The "s" can be placed anywhere you like on the new word, as long as it's part of the new word. For example, your opponent has built the word "bee" vertically. Then, you can add an "s" to "bee", making it "bees". Then, using the letter "s", you can build any valid word, such as "ants", "trash", "ask" or "rats".
Hope this helps!