We have faced negative profits due to the covid restrictions which makes use eligible for tax receivable with the Danish state. We however budget with growing profits over the next couple of years, and would like to create a calculation for the tax where we first deplete the receivable, and then use cash to fund the rest of the tax. Is there some formula for this?
Hey Bastian, sounds like a great use case! Here’s how I’d go about it :
- Start by creating a variable for the
Taxwhich has negative values for periods with tax receivable and positive values for the months with payable.
- Create a variable that calculates the
Cumulative Balancewhich accounts for the accumulated receivables and the receivables that have already been used in profitable months. The formula would look something like :
Cumulative Balance[previous]+Tax[current]-Payable Amount[previous](Create a variable for the
Payable Amountwe will get to in the next point)
- Finally you can write the formula for the
max(0, Cumulative Balance). This way you will only have to pay if the
Cumulative Balanceis positive i.e you have depleted the receivables.
Here’s an example model, you can clone the same by clicking on “Use this template” to the top right