I was recently asked to assist a Forecaster user with the following scenario:
We need to calculate an account on the input screen based on a driver and a percentage (overhead rate). In our calculation set we have the formula:
We do not want to enter the percentage value into every month of the year for the RATE row, because it doesn't change. However, when we enter .46 into the YTD total column for the input set, the calculations are always zero. How do we get the calculation to work?
This is actually a very easy item to accomplish in Forecaster. But it takes a little understanding of what is going on.
On the input screen, when you enter a value in a calculated period's column, Forecaster will spread that value back to the periods which make it up, using the current spread method assigned to the row. If there is no spread method, it attempts to perform an EVEN spread. As in all other spread method scenarios, the spread calculations will put any remainder value into the last open period that makes up the total (i.e. December for a total year column).
The math involved also takes the number of decimal places you are showing into account. So, if there are 0 decimal places in the columns or on the row, the calculation will assume you want no decimal places in the result. For a number like ".46", this results in all periods being 0, except december which will have the full .46 added into it.
The solution is actually very easy.
1. In the line set, add at least two (2) decimal places to the RATE row.
2. Change the calculation set such that the formula becomes:
Note: In the above, "TOTAL" should be replaced by the Code for Calculations for the Total column you are using.
3. Close and reopen the Input Screen after saving the changes to the Lines and Calculations. If the new decimal setting doesn't show for the RATE row, refresh the rows to the default line set.
4. Now enter .46 into the total column for the RATE row.
At this point, the .46 will spread back (evenly) to the periods, with any remainder going into the last open period. However, the TOTAL will be correct. The updated formula that runs will now pull the value from the cell where you entered the .46 (TOTAL.RATE). It will use this value across the entire OVHD row and your calculations will now be correct.