Replace Entire Roof?

As the saying goes....anything is doable if you have enough money.....and the Government says OK.
A structural engineer would need to be involved.
Before you pay him you should talk to the building department and ask if its possible from their end.

Thats good advice. Im also concerned I'l put all that effort into it but end up with a space so small that no one wants to go up there.

Attic conversions are a major source of problems. What seems so 'easy' ... well, to 'do it right,' it's not easy at all.

Your first concern is strength. Rafters are not usually designed with enough strength to support living space. They're just there to hold the roof on!

Next is the matter of the space itself. There are rules regarding minimum ceiling height, access, and such.

The usual design is for a roof to keep an insulated ceiling (for the floor below) in the shade. Even 4" of foam, sprayed under a hot roof deck, will transmit summer heat like a hot plate. You really need an air space, with free flowing air, under the roof deck.

Finally, attics are usually where wires, plumbing, ducts, and vents are run. You'll need to find a place for these- and that place often needs to remain accessible.

You're right about the new roof. Cost is the factor here and since it's a new 2nd story build, you'll get the most value (include resell) if you put up regulation height 2nd story. Go 8' high ceiling and 3-4:12 roof. Following the budget, the skin first (middle beam and ceiling joists include), all drywall and electric, plan for plumbing (must put in a full bath now or later). Then you finish as necessary, although at this point, might as well if just a few dollars available.
If all you need is office, you may want to leave the attic alone. New England small colonials have attic access from the hall. You won't have the room for regular stairs. If considering resale, you must have min 7.5' ceiling for most of the useable space.
For a new space in MA, you must include or plan how to cool, heat, insulate, vent, electric, and plumb. These things sometimes can't be done later without extensive doing-the-same-work-two/three times.