advice for a revenue table
I have a revenue table to keep track of projected income from small construction projects. Two projects are now actually under construction, and about a dozen are in various stages of planning/negotiations.
The revenue table projects revenue via calculation fields, taking a total project value multiplied by percentage of probability the project comes to fruition (thus 100% means a contract is signed and under construction), a new field for every month with projections to the end of 2016. These calculations are also constrained by revenue start and end dates, so it doesn’t keep projecting revenue into infinity.
The superiors would like to punch in their own values into revenue projection fields once the contracts are signed since the actual revenues vary from month to month based on whatever deals got hammered out with the clients.
Since the fields are currently calculations, they cannot override them.
I think the easiest thing to do is to change the field type to Number and then use a Calculated Value with the same calculation and allow an override. A coworker thinks it’s best for the long term to create a new table for actual revenue and use a script to move records over to the Actual Revenue table when the contracts are signed so that they can have free range over the fields in this new table.
What would be the best way to go forward?