I have used RedGate products faithfully for the last 6 year. I recommend it quite frequently to others, and I use it religiously (as I'm writing this reply I have it open in another tab as part of my daily routine). The custom alerts are great for expanding the functionality and their support team is awesome.

Aside from it being useful for the DBA, the analysis graphs it offers can be extremely valuable during team meetings and to show management different activity (management loves graphs). These graphs have been great in tracking down problems, and identifying trends.

That said...can you do it all with tsql, ssrs, excel, etc?...sure you can, however for shops with a single DBA, it's likely that you have quite a few other projects that require your attention. In my opinion, SQL Monitor has been a great tool in my belt to assist me in observing new environments, as well as continuous monitoring.