You've certainly already thought of this ...

You get two things going:

1. The XLA (Number crunching Macros and Worksheets you don't want to
release), AND
2. The Template (With formatting stuff, navigation stuff, and charting
stuff)

You end up adding a hidden sheet in the template for navigation and
when your macros run you store data in the navigation sheet so the
Template can be saved as a normal Workbook.

On large projects you can allow business analysts to maintain the
template.

In other words, you have to handle it with coding. No easy work
around.