Yes. The physical motivation is that topological field theories, as examples of quantum field theories, should be
fully local, meaning that one should be able to calculate any information about a (fully extended) TQFT $Z$ on a
manifold $M$ by cutting $M$ into pieces, formulating $Z$ on these pieces, and gluing. The takeaway in physics is
that in any class of systems thought to be described by topological field theories, one should be able to determine
the TQFT for a particular system from how the system behaves on a neighborhood of a point.
Even though we don't know how to define quantum field theories precisely, physicists know they must satisfy a few
important axioms, including locality. This means that all of the data in a quantum field theory formulated on a
manifold $M$ satisfies a sheaflike axiom: one must be able to glue the fields together from information on an
arbitrary small open cover of $M$. So a principal bundle with connection on $M$ is OK, as is a differential form,
but a CW structure is not. Similarly, things such as the Lagrangian or Hamiltonian must be expressible in terms of
data satisfying this condition. This implies that information one can calculate about the QFT, such as state
spaces or partition functions, also satisfies a gluing axiom: they are determined somehow from the information
entering the theory, which we can formulate on small balls around any point, satisfying some kind of gluing
condition.
In functorial TQFT, we're not in general provided with fields or Hamiltonians, but only things like partition
functions and state spaces, and their lower-dimensional analogues. But TQFTs are QFTs, so they must satisfy
locality. This motivated Atiyah's original definition: the partition function $Z(M)$ must be local, so we can cut
$M$ into a sequence of cobordisms and compose the maps between state spaces to compute partition functions. This is
only expressing locality in one direction out of $n$, so we should be able to repeat this process in the other
$n-1$ directions to compute the partition function in terms of fully local data, i.e. describing $M$ as glued
together from many copies of $\mathbb R^n$, then using whatever $Z(\mathbb R^n)$ means to compute the partition
function of $M$.
From this perspective, we see $\mathbb R^n$ instead of points, which may seem strange. The reason is that in a
cobordism category of Riemannian or Lorentz manifolds, which you'd use for functorial non-topological QFT, one has
to add a collar to manifolds in codimension one, so when fully extending, one has a two-dimensional collar in
codimension 2, and so on. In this way full locality is seen by $\mathbb R^n$, an $n$-dimensional collar around a
point. But for cobordisms between smooth manifolds, a collar is redundant data, so the axioms of fully extended
TQFT drop the collars and just use the higher-codimension manifolds, whence $Z(\mathrm{pt})$: the cobordism
hypothesis says a TQFT should be determined by fully local data, which would mean cutting any manifold into copies
of $\mathbb R^n$, and the axioms of TQFT replace $Z(\mathbb R^n)$ by $Z(\mathrm{pt})$.
That's part of the cobordism hypothesis; it also calculates what kinds of objects $Z(\mathrm{pt})$ can be.
Unfortunately I'm not sure of the physical motivation for that part.
Moving on to consequences in physics. I don't know of a general statement, but I'll discuss an interesting example
in the theory of topological phases of matter. These are
condensed-matter systems which display surprising topological properties, and condensed-matter theorists are
interested in classifying them (in a fixed dimension $n$, say). It's not yet known what the mathematical definition
of a topological phase of matter is, but physicists often formulate them in terms of "lattice field theories":
discretized QFTs on manifolds equipped with something like a triangulation, and such that the fields, Hamiltonian,
etc. are also suitably discretized in terms of purely combinatorial information. (The locality axiom is different
in this setting, expressed in terms of graph distance.) It is believed that the low-energy physics of such systems
is described by fully extended TQFTs, but even formulating a precise conjecture, never mind a proof, is currently
open at both physical and mathematical levels of rigor, as far as I know. Moreover, the low-energy TQFT should
classify the lattice system in some strong sense. This provides a (conjectural) approach to classifying topological
phases of matter: given an $n$-dimensional TQFT $Z$, build a topological phase with $Z$ as its low-energy TQFT, and
then classify $n$-dimensional TQFTs.
Of course, explicating the cobordism hypothesis in dimensions greater than 2 is tricky, but this approach appears
to be working. For example, the $\mathrm{SO}_3$-homotopy fixed points in the 3-category of $\mathbb C$-linear
monoidal categories are conjectured to be spherical fusion categories (one direction is known, due to
Douglas-Schommer-Pries-Snyder), and the TQFT associated to a spherical fusion
category $\mathsf C$ is the Turaev-Viro-Barratt-Westbury (TVBW)
TQFT for $\mathsf C$. In physics, topological phases of matter in (spacetime) dimension 3 on oriented manifolds are
believed to be classified by the Levin-Wen model associated to a
spherical fusion category, and Kirrilov Jr proves a result which provides strong
evidence that the low-energy theory of the Levin-Wen model for $\mathsf C$ is the TVBW TQFT for $\mathsf C$.
Another example is the classification of symmetry-protected topological (SPT) phases. These are (conjecturally) the
topological phases of matter whose low-energy theories are invertible TQFTs. The cobordism hypothesis implies that
these are classified by homotopy classes of maps out of Madsen-Tillmann spectra (though there's now a proof of this
fact independent of the cobordism hypothesis, due to Schommer-Pries), and one
can compare this classification with other classifications of SPTs. This was taken up by
Freed-Hopkins, who found agreement between this and other kinds of
classifications for a variety of dimensions and symmertry groups.
For some more detail on the motivation for the cobordism hypothesis, as well as some other applications in physics,
I recommend Dan Freed's expository article.
