Since coherence is a local property and is preserved by finite direct sums, the general question reduces to the structure sheaf. But this is coherent only in trivial cases.
If $M$ is a smooth manifold whose connected components have positive dimension, then $C^{\infty}_M$ is not coherent.
For a proof, see Prop. 7.3.8 in the course notes by Andrew Lewis on sheaf theory. By a projection argument, it suffices to deal with $M=\mathbb{R}$. If $f$ is a smooth function, which is $>0$ on $\mathbb{R}_{>0}$ and $=0$ on $\mathbb{R}_{\leq 0}$, then the kernel of the multiplication map $f : C^{\infty}_{\mathbb{R}} \to C^{\infty}_{\mathbb{R}}$ is not of finite type: Loot at the stalk at $0$. The kernel $I$ is given by those germs of smooth functions vanishing on $\mathbb{R}_{\geq 0}$. If $\mathfrak{m}$ is the maximal ideal of functions vanishing at $0$, then we have $I = \mathfrak{m} I$ by Taylor expansion. Since $I \neq 0$, Nakayama's Lemma shows that $I$ is not finitely generated.
This is my understanding of this yoga. It may not be exactly what you seek and may differ from another person's point of view. Also I apologize for my bad english.
For Grothendieck, many things should have a relative version. So instead of considering just a space $X_0$, consider a morphism $f:X\rightarrow S$ thought as a family of spaces $s\mapsto X_s:=f^{-1}(s)$.
Here is an easy example of relative thinking : Say you want to attach numerical invariants to your first space $X_0$, for example its dimension, you should instead replace it by a function on $S$, and you get the relative dimension. Under some assumptions, this function behave quite nicely.
If now you want to attach invariants that are sets, or abelian groups, to your first space $X_0$. Well there is a very nice tool that do exactly what you want for the relative version : presheaves on $S$. And under some assumptions, these are closely related to sheaves.
The six operations arise this way if you want to study cohomology.
There are many cohomology theories out there, and many of them have their 6 operations. But they do not behave exactly of the same way, so let's say we work with ordinary cohomology of (nice) topological spaces.
It is also better to work with derived categories. Indeed, the cohomology of space is in fact a complex up to quasi-isomorphism, more than just the groups $H^i$. Also, for example, the functor $f^!$ exists only at the level of derived categories. If $X$ is a space, let $D(X)$ be the derived category of sheaves on $X$ and let $\mathbb{Z}_X$ be the constant sheaf with value $\mathbb{Z}$ on $X$.
Let start with the first functor $f_*$ or rather $Rf_*:D(X)\rightarrow D(S)$. Well, this is exactly the functor that computes cohomology. Indeed, if $f$ is proper, $(R^if_*\mathbb{Z}_X)_s=H^i(X_s,\mathbb{Z})$ (this is the so-called proper base change theorem). So this is exactly the relative version of cohomology. In fact, if $f$ is proper and smooth (=submersion), then $R^if_*\mathbb{Z}$ are local systems on $S$.
There is also $f_!$ or rather $Rf_!:D(X)\rightarrow D(S)$. It does the same thing, but with cohomology with compact support : $(R^if_!\mathbb{Z}_X)_s=H^i_c(X_s,\mathbb{Z})$. This times, one does not need to assume $f$ proper.
And of course the tensor product $\otimes$ gives the multiplicative structure on cohomology. One can then speak of derived analogue of the Kunneth formula...
Now, if $g:T\rightarrow S$, there is the functor $g^*:D(S)\rightarrow D(T)$. In some sense this is the one that justify the whole thing : this is the functor which correspond to changing the base from $S$ to $T$. For example, if $j:U\rightarrow S$ is the inclusion of an open subset, one can form $X_U=f^{-1}(U)$ and $j^*Rf_*\mathbb{Z}_X=R(f_{|X_U})_*\mathbb{Z}_{X_U}$. This is a (very easy) special case of a very deep one : the smooth base-change theorem. Changing bases is VERY useful. For example, base change to the universal cover, so that local systems become constant. Or in algebraic geometry, base change to the algebraic closure. And of course, taking stalks are already special cases of base change...
And finally, the functor $f^!:D(S)\rightarrow D(X)$ and the internal Hom are there to deal with duality. (Note that $f^!$ is right adjoint to $f_!$, not left). Instead of a global duality between $H^i_c$ and $H_{d-i}$, we now have local versions, allowing local computations and so on... Just for completeness, if $S$ is a point, $f_*f^!\mathbb{Z}$ is the Borel-Moore homology and $f_!f^!\mathbb{Z}$ is the ordinary homology (at least if $X$ is nice enough).
Best Answer
Consider the opposite category of the category of Abelian groups. In that category, direct limits will not preserve exact sequences because in the category of Abelian groups inverse limits don't preserve exact sequences.