Normally it's used in a discussion, negotiation or argument, when someone is declaring that they've made their decision and isn't willing to discuss it any further. For example, your wife wants her cousin to stay on the couch for a few days. You tell her that you don't like her cousin and your house is too small for another person. She pleads with you to let him stay. You say "He's not staying, and that's final." It's not always the nicest thing to say, by the way.