I think we have to look at it beyond simply the additions and the subtractions - you also have to consider the existing young prospects on the roster, and how they might potentially improve.
Key factors that I see...
Loss of Evan Turner
Turner is one of those guys who made an impact (for better or worse) every time he stepped on the court. There were times when he would be a bit too gung-ho and it would hurt us, but his ability to create plays, as both a scorer and a distributor, was a huge key for us last season. Similarly important were his ability to rise up in the clutch, and his versatility (he played everything from PG to PF at some point or other).
I'm looking at Terry Rozier as the the guy I hope can step up and fill much of that void. He can handle the ball, he is improving as a passer, he is an excellent rebounder for a perimeter player, and he can get into the paint almost at will. Also he has already shown he can score and that he is fearless in the clutch. It's a bit in the "wishful thinking" category hoping that he can take that step, but I like Rozier a lot and I'm hopefuly he can prove me right.
Loss of Jared Sullinger
The only way I see this potentially affecting us is in the area of rebounding. Sully was a productive offensive player for us, but was also incredibly inconsistent - Horford will be a significant upgrade over Sullinger both offensively and defensively, but he's never been on Sully's level as a rebounder. That could hurt us potentially, which is why I would REALLY like to see us adding Nerlens Noel if we can. Noel isn't a standout rebounder, but he can at least hold his own and also defends the paint well.
Adding Green
I don't think this is going to have a huge impact for us. Assuming Hunter and Young don't make the active roster, we are pretty much 4-5 men (at a squeeze) deep at every perimeter position:
PG: Thomas / Rozier / Smart / Bradley (in emergencies)
SG: Bradley / Smart / Green / Rozier
SF: Crowder / Brown / Green / Jerebko / Smart (in emergencies)
I don't expect Danny would draft Brown so high just to bury him deep in the bench - I expect him to start the season out as Crowder's primary backup at the SF spot, and that the backup minutes will be his to earn / lose. I think Green will be there primarily for depth reasons (in case of injuries, in case of trades, or in case Brown proves too raw to play meaningful minutes minutes).
I think Green is going to have a role similar to that of Jerebko - he prboably won't contribute much on a day to day basis, but there are going to be moments (during injuries, foul trouble, etc) where Green he'll be called upon, and his presence will be invaluable.
Adding Horford
I think the biggest improvement we might gain from Horford is actually rim protection.
Last year Amir Johnson was the only guy on this roster who was legitimately capable of protecting the basket, but his health limited how much he could stay on the court - and every time he sat down, our interior defense was vulnerable.
The addition of Horford provides us with another capable interior defender who can protect the paint for us while Amir sits - it ensures that we can have a solid interior defender on the court, at all times. When those to are on the court together, this team will be borderline epic defensively - it should be the best defensive front court the Celtics have had since the KG/Perk duo.
Offensively, I'm not yet sure what to expect from Horford. He's one of the better post players in the NBA offensively (both as a scorer and as a passer), and yet he spent most of his offense last season standing out on the perimeter. Considering how lacking we are in post offense, I'd like to see us utilise Horford's inside game more - post him up, give him the ball, and run the offense through him. If we alternate between running our offense through Horford inside and Thomas on the perimeter, it should make us a tough offense to defend - especially with Bradley and Crowder being capable shooters on the outside.
Rebounding is going to likely hurt us, unless Horford can find a way to give us 9-10 rebounds a night, which I think will be a tough ask for a guy who hasn't put up those kind of rebounding numbers in years. Hopefully we'll be good enough offensively and defensively to make up for out lack of rebounding, but given the number of long jumpers this team will be throwing up I think it could be a real Achilles heel for us.
On the plus side our defense should be world class, which should help to feed into our transition game - which should be one of the best in the league. To add to that, two of our three most inefficient outside shooters (Sully, Turner) are gone, with Smart the only one left. Almost everybody on this team can now defend, run in transition, and shoot jumpers. Should be a fun, fun team to watch.