-Caterpie - Low Tier
: It couldn't possibly be any easier to obtain. Common and before the first gym.
: Butterfree's stats are mediocre. Decent Special and Speed but poor Defence and HP (and Attack). It does help that it evolves early though, since it gives it a moment to shine.
: It has access to the powders, giving it some utility, and access to Psychic moves this early is great. However, its moves lack power overall (Psybeam is its strongest attack). It learns Psychic and Mega Drain as TMs but Psychic is quite a valuable TM and Butterfree is probably not the best thing to use it on.
: It has decent power early on, but as the game goes on you'll find that it starts to fail to kill things quickly enough, taking big hits in return.
: It doesn't have any STAB moves. Its typing blows defensively and later on in the game it may limit the number of things that Butterfree should be used against.
: Brock - One of the best choices here since they don't have rock attacks and they lose to Confusion.
Misty - Starmie beats you but it is fairly evenly matched against the others.
Lt. Surge - Electric attacks kill it.
Erika - Wins here.
Koga - Butterfree is actually weak to Poison in 1st gen so its a risky choice in RB (it should still contribute though thanks to its psychic attacks). In Yellow, none of Koga's pokemon have poison attacks so it isn't as bad.
Sabrina - Her pokemon kind of 'out-special' Butterfree. They resist its strongest attack too. Not a good choice here.
Blaine - No.
Giovanni -In RB, it actually isn't THAT bad. It can beat the Nidos, and none of his pokemon have rock attacks. In Yellow it is much worse off because of Rock Slide, Thunder and perhaps Persian's Slash.
Lorelei - You lose here.
Bruno - If you have Mega Drain then you can stop Onix before it can kill you. It resists fighting but Machamp and Hitmonlee's (and Hitmonchan's in RB) normal attacks will do a lot.
Agatha - Psychic is super-effective against all of them. It is weak to Wing Attack and Acid though. As per usual beating the ghosts can come down to luck (you have to hope that Hypnosis misses and that Confuse Ray doesn't completely screw you over).
Lance - Useless here, especially in Yellow.
Blue - In RB it COULD beat Rhydon, Exeggutor and Venusaur. In Yellow, Exeggutor is the only thing I would feel "comfortable" against.
-Additional Comments -
I think that Butterfree is somewhat borderline in its tier placement. Its availability, early evolutions, psychic attacks and useful status moves are definite pluses, but its low stats and limited offensive movepool basically mean that it is just medocre on the battlefield. I think that low is appropriate. If low tier gets separated again though, don't put it in Bottom since it does have SOME attributes.