Cashflow Rollforward

Templates
1

Hi,

We currently have a convoluted way of exporting our cashflow for our clients. Whereby we have a template in the workflow which links to a report. The report is then where the export originally comes from.

To streamline our process, we are wanting to do away with the report and only use the template from the workflow.

I know how to add the previous year in, however I am struggling to get the template to use the data from the previous year, in particular the figures where we have overtyped them.

We would like to add in the rollforward option so that when completing the 2023 accounts, we can rollforward the previous year’s so that it will appear in the 2022 column.

The code we currently use is below, this is before putting in the 2022 column as well:
Main:
{% assign row_title = “Profit/(loss) for the financial period” %}
{% capture key_name %}profit_loss_for_the_financial_year{% endcapture %}
{% assign lay_out = “Automatic_negative” %}
{% assign mapping_range = “4__8,900__930” %}
{% include “parts/layout” %}

Part:
{% when ‘Automatic_negative’ %}
{% assign row_title_key = key_name %}
{% unless key_name == ‘profit_loss_for_the_financial_year’ %}
{% assign current_value = period_acc_curr | range:mapping_range %}
{% assign current_opening_value = period_opening_cy | range:mapping_range %}
{% assign cy_value = current_value-current_opening_value %}
{% else %}
{% assign cy_value = period_acc_curr | range:mapping_range %}
{% assign cy_value = cy_value-rounding2 %}
{% endunless %}

{% assign cy_value = cy_value | currency:0, invert:true %}

{% if cy_value == 0 or cy_value == blank %}
{% assign cy_value_zero = 1 %}
{% else %}
{% assign cy_value_zero = 0 %}
{% endif %}

{% if cy_value_zero != 1 %}
{% capture row_name %}{% if row_title contains “Decrease/(increase)” %}{% comment %}assets{% endcomment %}{% if cy_value < 0 %}(Increase){% else %}Decrease{% endif %}{% endif %}{% if row_title contains “Increase/(decrease)” %}{% comment %}liabilities{% endcomment %}{% if cy_value < 0 %}(Decrease){% else %}Increase{% endif %}{% endif %}{% if row_title contains “Profit/(loss)” %}{% if cy_value < 0 %}(Loss){% else %}Profit{% endif %}{% endif %}{{ row_title | replace:“Decrease/(increase)”,“” | replace:“Increase/(decrease)”,“” | replace:“Profit/(loss)”,“” }}{% endcapture %}
|{% input custom.[row_title_key].name | default:row_name %}
|{% input custom.[row_title_key].value_cy as:currency precision:0 default:cy_value assign:cy_value %}
{% $1+ cy_value %}
{% newline %}
{% endif %}

I have tried to update and put the rollforward in, but this was coming up with a custom drop error.

Please can someone help with this?

Thanks,

Rory

2

Hi @rory.grant ,

Currently I don’t see any column in your code related to the previous period.

However, if you will implement this you can indeed use rollforward. Let’s say you create variable custom.[row_title_key].value_py to refer to the value of the previous period. Your rollforward code can look like this:

{% rollforward custom.[row_title_key].value_cy custom.[row_title_key].value_py %}
{% rollforward nil custom.[row_title_key].value_cy %}

Keep in mind that default values are never part of a rollforward. If you want to be sure you have the same value as in the previous period, make sure to recreate the default that was used there.

You can find more information on the rollforward functionality here.

Kind regards,

Michiel

3

Hi Michiel,

Thank you. I’ve now updated the code for the previous period & used the rollforward you have mentioned. (as below).

I have trialled this on one of our clients, and now see what you mean about the default values, as it does not pick up the prior year figures until I typed over one with a new figure. The intention of the report is that it is all coded to pick up the figures as a default to minimise our work on the schedule.

Does this therefore mean that we are going to need to code the template so that it is looking at the figures for last year as a default and use the roll forward for anything over typed?

Thanks

  {% assign row_title_key = key_name %}
  {% unless key_name == 'profit_loss_for_the_financial_year' %}
    {% assign current_value = period_acc_curr | range:mapping_range %}
    {% assign current_opening_value = period_opening_cy | range:mapping_range %}
    {% assign cy_value = current_value-current_opening_value %}
  {% else %}
    {% assign cy_value = period_acc_curr | range:mapping_range  %}
    {% assign cy_value = cy_value-rounding2  %}
  {% endunless %}
  
  {% assign cy_value =  cy_value | currency:0, invert:true %}
  
  {% if cy_value == 0 or cy_value == blank %}
    {% assign cy_value_zero = 1 %}
  {% else %}
    {% assign cy_value_zero = 0 %}
  {% endif %}
  
   {% if cy_value_zero != 1 %}
   {% capture row_name %}{% if row_title contains "Decrease/(increase)" %}{% comment %}assets{% endcomment %}{% if cy_value < 0 %}(Increase){% else %}Decrease{% endif %}{% endif %}{% if row_title contains "Increase/(decrease)" %}{% comment %}liabilities{% endcomment %}{% if cy_value < 0 %}(Decrease){% else %}Increase{% endif %}{% endif %}{% if row_title contains "Profit/(loss)" %}{% if cy_value < 0 %}(Loss){% else %}Profit{% endif %}{% endif %}{{ row_title | replace:"Decrease/(increase)","" | replace:"Increase/(decrease)",""  | replace:"Profit/(loss)","" }}{% endcapture %}
    |{% input custom.[row_title_key].name | default:row_name %}   
    |{% input custom.[row_title_key].value_cy as:currency precision:0 default:cy_value assign:cy_value %}
      {% rollforward custom.[row_title_key].value_cy custom.[row_title_key].value_py %}
      {% rollforward nil custom.[row_title_key].value_cy %}
    |{% input custom.[row_title_key].value_py as:current precision:0 default:py_value assign:py_value %}
    
    {% $1+ cy_value %}
    {% newline %}
  {% endif %}
4

Exactly @rory.grant .

Default values are not stored in the database, they are being calculated when you open the template. Therefor if you want to show them in the next period, the best option is to also add the logic for the default of the previous year column.

Regards,

Michiel