Figured's standard Milk Tracker has multiple columns that automatically calculates values for the forecast.
NOTE: Any actual payment values are pulled directly from Xero
Below is the breakdown of how those payments have been calculated.
The Standard Payment column is calculated by Production kgMS x (Milk Price + Capacity adjustment)
NOTE: this will always come from the Your Milk Price column unless left blank, in which case it will default to the Company Milk Price.
This season payment
Also in the milk tracker is This Season Payment, which is Standard Advance Payment + Further Payments.
To calculate the Further Payments you first need to calculate the the milk price increase. This is calculating the adjusted payment you will receive for the milk prices increasing as the year progresses.
Milk price increase is calculated from difference between this months milk price and last months milk price as shown above in blue.
The Further Payments is then calculated by taking the Milk price increase x Total historical milk production for the year.
Milk price increase: $5.20 less $5.05 = $0.15 cents
Total historical milk production: Total YTD production 420,890 - Current month milk production 49,225 = 371,665
371,6665 kgMS x $0.15 cents = $55,750
This is then displayed in the Further Payments column.
To calculate the Retrospective Payments you will first need to work out the milk price increase in the same way you would when calculating further payments, as shown in blue above.
The Retrospective Payments are then calculated by taking the Total milk production x Milk price increase.
NOTE: this is displayed in the retrospective payments section of the current season as well as the deferred income column of the next season.
The final column of your milk tracker, Total Payment, is then the sum of This Season Payment (Standard Advance Payment + Further Payments) + Deferred Income.