If I shred a $20 bill, is society poorer? What is the effect on the real economy? If the answer is yes, society is poorer, then the converse must hold true: that the government could make us all wealthier by simply printing money.
However, a collapse in the money supply must have an effect on the real economy. One less Chipotle burrito gets sold. More formally, Milton Friedman's entire thesis was that a collapse in the money supply caused the Great Depression.
Which is it, yes or no? And what are its reverberations throughout the &quot;real&quot; economy?
You have to make distinction between short-run and long-run.
Short-run: In short-run money is not neutral, meaning that in short-run money and other nominal variables can affect real output. A one example why this is true are sticky wages. In presence of sticky wages wages (for example due to nominal contracts that take time to renegotiate) a fall in money supply and deflation would lead to increase in real wages above the equilibrium level. This would cause excess unemployment and loss of output until wages would finally adjust.
So in short-run amount of money and nominal variables in general have a real effect on economy although this effect is not as simple and clear cut as saying that shredding one bill will lower output and printing one more will increase it as the effect of changes in money supply depend on macroeconomic conditions (for example at zero lower bound increases in money supply will be mostly offset by drop in velocity of money  see Krugman; 1998). However, discussing fully the reverberations money have in the short run is beyond the scope of SE answer. I recommend having look at Blanchard et al. Macroeconomics: an European Perspective chapters 3-10, 20-22 and 24 with emphasis on chapters 3-10 which have the core.
Long-run: In the long-run you get the classical dichotomy and money are neutral. This is almost definitional since long-run is in macroeconomics defined as a long period of time where among other things prices have time to adjust and are flexible.
This can be best seen from the AS-AD diagram below that I took from Mankiw's Principles of Economics:

As you can see short-run aggregate supply is increasing in aggregate price level so money are not neutral and can affect the level of production. However, the long-run aggregate supply is orthogonal on price level. No matter what the aggregate price level is the production will be always same and determined by the production possibilities of an economy (i.e. determined by real factors that affect the production possibilities of an economy).
This is intuitive as economy ultimately cannot produce more than it is possible given its production function and resource constraints. If an economy faces following production function $F(L) = 5\sqrt(L)$ and we have $100$ units of labor then we cannot produce more than $50$ units of output no matter how much money we print or destroy. Volume of money in circulation in the long-run does not make factors of production more productive nor it increases the stocks of factors production so by itself it can’t change how much economy can produce in the long-run.