The demand, I presume, is both agricultural and urban irrigation needs. The growth around I-25 between Denver and Fort Collins is evident, and Greeley is one of the fastest growing communities in the US. As far as location goes, the space between those hogbacks near Ted's Place is pretty much ideal, from a dam builders perspective. The water diversion out of the Poudre will take place east of the canyon mouth, or at least past the last take out, via a big canal.
As far as putting it out further east, the topography and geologic structure of the plains precludes this. It will be a relatively small reservoir, volume wise. Not sure about how big surface area wise. All in all, not much impact to the boating community.
Just to head off the sort of witty posts that the Buzz has been subject to lately, I know that reservoirs are bad. I'm not an advocate, just somebody with a little beta, rather than half-wit rhetoric. Besides, it sure is nice to have water come right out of the tap when you want to brush your teeth, or your arse. Everybody on this forum utilizes this priviledge.