Let's just look at 2001 to the present, the "Patriot" era. Here are the top scoring offenses and their season results:
2001: St. Louis - 14-2, lost in Super Bowl
2002: Kansas City - 8-8, missed playoffs
2003: Kansas City - 13-3, lost in Divisional round
2004: Indianapolis - 12-4, lost in Divisional round
2005: Seattle - 13-3, lost in Super Bowl
2006: San Diego - 14-2, lost in Divisional round
2007: New England - 16-0, lost in Super Bowl
2008: San Diego - 8-8, lost in Divisional round
2009: New Orleans - 13-3, won Super Bowl
2010: New England - 14-2, lost in Divisional round
2011: Green Bay - 15-1, lost in Divisional round
So only one out of the last 11 top-scoring offenses has won the Super Bowl, and only four of those teams even made the Super Bowl.
I don't think you can win in today's NFL with a lousy offense, but it doesn't appear that you can get away with simply having a dominant offense and very little else.