But Excel formulas can't preserve historical values — when B4 changes, the formula recalculates and old data is lost.

You can change Formula > Calculation Options > Manual and copy & paste as values manually the current calculation or historical values (and Calculation Options > Automatic when done)

I don't understand the question and also not much data was provided, perhaps like this? Scorecard Draft