How to assign actuals in a P & L to the correct financial year

[NickS]

I’m building a simple Profit & Loss budget forecast tool. With this tool you can generate a quick scan budget forecast within a few seconds at the main level for a period of 5 years.
If you choose, for example, financial year 2016, the table is shown with the years: 2016, 2017, 2018, 2019, 2020, 2021. If you choose, for example, financial year 2017, the table is shown with the years: 2017, 2018 2019, 2020, 2021, 2022.

My question:
How do I ensure that the 2017 Actuals are included in the 2017 column and the 2016 Actuals in the 2016 column.

So far I have the following:

{% assign start_date = period.year_start_date | date:"%Y" %}
{% assign end_date = period.year_end_date | date:"%Y" %}

{% assign revenue = period.accounts | range:‘70’ %}

| Description |{% if start_date == end_date %}{{ start_date }}{% else %}{{start_date}} - {{end_date}}{% endif %}{% for i in (1…5) %} | {{ INT(start_date+i) }}{% endfor %}
|------------|----------|----------|-----------|----------|----------|---------------+
|Revenue |{{revenue | currency }} | {{revenue | currency }} | {{revenue | currency }} | {{revenue | n_currency }} | {{revenue | currency }} | {{revenue | currency }}

[sven]

Hello @NickS,

you could check if the next period exists, with following code, and there in the if-statement assign a new variable:

{% if period.plus_1y.exists %}
  {% assign revenue_1y = period.plus_1y.accounts | range:"70" %}
{% endif %}

So the plus_1y let’s you go to the current period of next book year (1 book year).

you could also use this for instance fyi:

{% assign revenue_1y = period.plus_1y.accounts.70  %} 

PS it is not possible to call for budget-totals (actuals yes, but not budget-totals), so I’d suggest using report-templates for this (no need to use Liquid imo).

Let me know if this helps.

[NickS]

If I choose 2016, the revenue 2016 enters in column 2016, that is good, but in the 2017 column also enters the revenue 2016, this should be the revenue of 2017.

I probably doing something wrong.

{% assign start_date = period.year_start_date | date:"%Y" %}
{% assign end_date = period.year_end_date | date:"%Y" %}

{% assign revenue = period.accounts | range:“70” %}

{% assign revenue_1y = period.plus_1y.accounts.70 %}

| Description |{% if start_date == end_date %}{{ start_date }}{% else %}{{start_date}} - {{end_date}}{% endif %}{% for i in (1…5) %} | {{ INT(start_date+i) }}{% endfor %}
|------------|----------|----------|-----------|----------|----------|---------------+
|Revenue |{{revenue | n_currency }}| {{revenue | n_currency }} | {{revenue | n_currency }} | {{revenue | n_currency }} | {{revenue | n_currency }} | {{revenue | n_currency }}

[sven]

@NickS,

In the column of 2017 you’ll need to use the correct variable, which is revenue_1y. Sorry, I should’ve explain that more thoroughly…

So if you need 4 future years, you’ll need to create 4 new variables (revenue_1y, revenue_2y, … ) and add them to the correct column.

[NickS]

Thank you

Is there a way to write this with a shorter code.

{% assign revenue_1y = period.plus_1y.accounts.70 %}
{% assign revenue_2y = period.plus_2y.accounts.70 %}
{% assign revenue_3y = period.plus_3y.accounts.70 %}
{% assign revenue_4y = period.plus_4y.accounts.70 %}
{% assign revenue_5y = period.plus_5y.accounts.70 %}

[sven]

Good question @NickS !

Yes, you can.

You’ll need to loop over something that has 5 loops, but you already got that I see in your code.

So, here we go:

{% for i in (1..5) %}
{% comment %}the capture will create the middle part for each loop, beginning with plus_1y in the first loop, plus_2y in the second, ... {% endcomment %}
{% capture year %}plus_{{ forloop.index }}y{% endcapture %}
Value revenu = {{ period[year].accounts.70 }}
{% endfor %}

So the capture combines some code and standard text; if you want to read the value of that variable year, you’ll need to use [].

Hope this is clear for you.

[NickS]

Sorry, I understand a small part but not everything.

Do I put _1y etc. between the [ ] ??

How to insert/replace this:

{% for i in (1…5) %}
{% comment %}the capture will create the middle part for each loop, beginning with plus_1y in the first loop, plus_2y in the second, … {% endcomment %}
{% capture year %}plus_{{ forloop.index }}y{% endcapture %}
Value revenu = {{ period[year].accounts.70 }}
{% endfor %}

With this:

{% assign start_date = period.year_start_date | date:"%Y" %}
{% assign end_date = period.year_end_date | date:"%Y" %}

{% assign revenue = period.accounts | range:“70” %}

{% assign revenue_1y = period.plus_1y.accounts.70 %}
{% assign revenue_2y = period.plus_2y.accounts.70 %}
{% assign revenue_3y = period.plus_3y.accounts.70 %}
{% assign revenue_4y = period.plus_4y.accounts.70 %}
{% assign revenue_5y = period.plus_5y.accounts.70 %}

| Description |{% if start_date == end_date %}{{ start_date }}{% else %}{{start_date}} - {{end_date}}{% endif %}{% for i in (1…5) %} | {{ INT(start_date+i) }}{% endfor %}
|------------|----------|----------|-----------|----------|----------|---------------+
|Revenue | {{revenue | n_currency }} | {{revenue_1y | n_currency }} | {{revenue_2y | n_currency }} | {{revenue_3y | n_currency }} | {{revenue_4y | n_currency }} | {{revenue_5y | n_currency }}

[sven]

@NickS,

The part between [] is automatically generated by the code. Let me explain it further:

{% for i in (1..5) %}

{% endfor %}

This code is basically loops we create: the collection we loop over, exists of 5 elements. So each loop will be executed 5 times.

In each loop we are going to create a variable that takes the number of the loop ( = forloop.index )

So this:

{% for i in (1..5) %}
{{ forloop.index }}
{% endfor %}

will create as output:

1
2
3
4
5

That number can be used to create “plus_1y”, “plus_2y”, “plus_3y”, “plus_4y”, “plus_5y”.
With a capture, we can create the words “plus_” and “y” with the variable {{ forloop.index }} so that in each loop, a string is create:

{% for i in (1..5) %}
{% capture year %}plus_{{ forloop.index }}y{% endcapture %}
Result of the var year = {{ year }}
{% endfor %}

which will result in:

plus_1y
plus_2y
plus_3y
plus_4y
plus_5y

If you want to combine that part within a variable like period.accounts you’ll need to use [] to read those values correctly out, like this:

{% for i in (1..5) %}
{% capture year %}plus_{{ forloop.index }}y{% endcapture %}
Value revenu = {{ period[year].accounts.70 }}
{% endfor %}

Hope this clears it out for you. Of course, you’ll need to combine my code with your table, but you already did that part with this code in your original code:

{% for i in (1…5) %} | {{ INT(start_date+i) }}{% endfor %} 

But you’ll need to do that for each row (= newline). Hope you can take this on; let us know if you’re stuck.

[NickS]

for some reason I do not succeed, not enough experience yet.
can you show me the complete working code

I want to replace this:

{% assign revenue_1y = period.plus_1y.accounts.70 %}
{% assign revenue_2y = period.plus_2y.accounts.70 %}
{% assign revenue_3y = period.plus_3y.accounts.70 %}
{% assign revenue_4y = period.plus_4y.accounts.70 %}
{% assign revenue_5y = period.plus_5y.accounts.70 %}

---

for this:

{% for i in (1…5) %}
{% capture year %}plus_{{ forloop.index }}y{% endcapture %}
Value revenu = {{ period[year].accounts.70 }}
{% endfor %}

[robindeclercq]

Hi @NickS,

Your code looks fine, you should just replace the three dots in ‘(1…5)’ for two dots. In that case the code should work just fine.

In case this does not solve your query, can you perhaps share your complete code until now so we can further guide you?

Thanks!

Kind regards,
Robin

[NickS]

Hi Robin,

Thank you.
This is the complete code. Apparently I’m doing something wrong.

{% assign start_date = period.year_start_date | date:"%Y" %}
{% assign end_date = period.year_end_date | date:"%Y" %}

{% assign revenue = period.accounts | range:“70” %}

{% for i in (1…5) %}
{% capture year %}plus_{{ forloop.index }}y{% endcapture %}
Value revenu = {{ period[year].accounts.70 }}
{% endfor %}

| Description |{% if start_date == end_date %}{{ start_date }}{% else %}{{start_date}} - {{end_date}}{% endif %}{% for i in (1…5) %} | {{ INT(start_date+i) }}{% endfor %}
|------------|----------|----------|-----------|----------|----------|---------------+
|Revenue | {{ revenue | n_currency }} | {{revenue_1y | n_currency }} | {{revenue_2y | n_currency }} | {{revenue_3y | n_currency }} | {{revenue_4y | n_currency }} | {{revenue_5y | n_currency }}

This is what it should look like

[robindeclercq]

Hi @NickS,

You started out well, indeed for the column headers you should use the for-loop (1-5) to generate the 5 years of your forecast.

The same should be done for your table and table content. The for-loop that you use above should be placed in the table as follows:

{% assign start_date = period.year_start_date | date:“%Y” %}
{% assign end_date = period.year_end_date | date:“%Y” %}

{% assign revenue = period.accounts | range:“70” %}

| Description |{% if start_date == end_date %}{{ start_date }}{% else %}{{start_date}} - {{end_date}}{% endif %}{% for i in (1…5) %} | {{ INT(start_date+i) }}{% endfor %}
|------------|----------{% for i in (1…5) %}|----------{% endfor %}+
|Revenue | {{ -1 revenue | currency }} {% for i in (1…5) %}| {% capture year %}plus_{{ forloop.index }}y{% endcapture %}{% assign revenue_value = period[year].accounts.70 %}{{ -1*revenue_value | currency }}{% endfor %}

Note that in case you want to show the revenue of the current year, this should be excluded from the for-loop and should be fixed text.

Furthermore, we suggest to not longer use the ‘n_currency’, since this is legacy code, but simply multiply your value minus 1

In case something is still unclear, do not hesitate to reach out to us.

Kind regards,
Robin