Growth percentage per year in P & L budget forecast

Templates
, ,
1

I want to add the growth percentage in the P & L budget forecast in the future years.
Can you give me advice and help me with (example) code.
I will try to explain what I want to do.

Step 1
Select in the drop down menu the sector in which the company is active.
Step 2
Select in the drop down menu the number of employees.

if sector is food and employees is 1-5 then growth_percentage is 0.02 (2%)
if sector is food and employees is 6-10 then growth_percentage is 0.022 (2,2%)
if sector is food and employees is 11-20 then growth_percentage is 0.025
if sector is food and employees is 21-50 then growth_percentage is 0.028
if sector is food and employees is 51-100 then growth_percentage is 0.03
if sector is food and employees is > 100 then growth_percentage is 0.035

if growth percentage is … then add this to the revenue last year and place this new figure for revenue in the first future year.
then add the growth percentage to the revenu of this first future year and place this new figure for revenue in the second future year. Etc. etc up to and including the fifth future year.

Growth%20percentage%20per%20year
Growth%20percentage%20per%20year2631×1323 48.3 KB

{::infotext as=“hover”}
Select the sector in which the company is active.
The sector together with the number of employees of the company determines the average growth rate of similar companies in the sector.The data of
the average growth figures per sector comes from the statistics bureau and trend reports from the top 3 banks and is updated twice a year.
{:/infotext}

{% t= “Construction|Retail non-food|Services|Food|Healthcare|Wholesale|Hospitality & Leisure|Industry/Manufacturing|Agriculture & Horticulture|Mobility & Transport” %}
{% capture sector_types %}{% t “Construction|Retail non-food|Services|Food|Healthcare|Wholesale|Hospitality and Leisure|Industry|Agriculture & Horticulture|Mobility & Transport” %}{% endcapture %}

|Sector
|---------+#
|{% input custom.val.sector as:select options:sector_types option_values:“Construction|Retail non-food|Services|Food|Healthcare|Wholesale|Hospitality & Leisure|Industry|Agriculture & Horticulture|Mobility & Transportbouw” %}

{% assign multip = custom.val.sector %}

{::infotext as=“hover”}
Select the number of employees.
The number of employees together with the sector determines the average growth rate of similar companies in the sector.The data of the
average growth figures per sector comes from the statistics bureau and trend reports from the top 3 banks and is updated twice a year.
{:/infotext}

{% t= “1 - 5 | 6 - 10 | 11 - 20 | 21 - 50 | 51 - 100 | > 100” %}
{% capture employees_types %}{% t “1 - 5 | 6 - 10 | 11 - 20 | 21 - 50 | 51 - 100 | > 100” %}{% endcapture %}

|Number of employees
|---------+#
|{% input custom.val.employees as:select options:employees_types option_values:“1 - 5 | 6 - 10 | 11 - 20 | 21 - 50 | 51 - 100 | > 100” %}

{% assign multip = custom.val.employees %}

Average sector revenue growth for {{company.name}}

|{% input.custom.letter.growth_percentage as:percent %}|


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

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

| 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 | {{ -1revenue | currency }} {% for i in (1…5) %}| {% capture year %}plus_{{ forloop.index }}y{% endcapture %}{% assign revenue_value = period[year].accounts.70 %}{{ -1revenue_value | currency }}{% endfor %}

2

Hi @NickS,

Assuming that the growth-percentage is calculated starting from the amount of the current year minus 1, you can build your table as follows:

{::infotext as=“hover”}
Select the sector in which the company is active.
The sector together with the number of employees of the company determines the average growth rate of similar companies in the sector.The data of
the average growth figures per sector comes from the statistics bureau and trend reports from the top 3 banks and is updated twice a year.
{:/infotext}

{% t= “Construction|Retail non-food|Services|Food|Healthcare|Wholesale|Hospitality & Leisure|Industry/Manufacturing|Agriculture & Horticulture|Mobility & Transport” %}
{% capture sector_types %}{% t “Construction|Retail non-food|Services|Food|Healthcare|Wholesale|Hospitality and Leisure|Industry|Agriculture & Horticulture|Mobility & Transport” %}{% endcapture %}

|Sector
|---------+#
|{% input custom.val.sector as:select options:sector_types option_values:“Construction|Retail non-food|Services|Food|Healthcare|Wholesale|Hospitality & Leisure|Industry|Agriculture & Horticulture|Mobility & Transportbouw” %}

{% assign multip = custom.val.sector %}

{::infotext as=“hover”}
Select the number of employees.
The number of employees together with the sector determines the average growth rate of similar companies in the sector.The data of the
average growth figures per sector comes from the statistics bureau and trend reports from the top 3 banks and is updated twice a year.
{:/infotext}

{% t= “1 - 5 | 6 - 10 | 11 - 20 | 21 - 50 | 51 - 100 | > 100” %}
{% capture employees_types %}{% t “1 - 5 | 6 - 10 | 11 - 20 | 21 - 50 | 51 - 100 | > 100” %}{% endcapture %}

|Number of employees
|---------+#
|{% input custom.val.employees as:select options:employees_types option_values:“1 - 5|6 - 10|11 - 20|21 - 50|51 - 100|> 100” %}

{% assign multip_empl = custom.val.employees %}

Average sector revenue growth for {{company.name}}

|{% input.custom.letter.growth_percentage as:percent %}|

{% assign growth_perc = 1.22 %}

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

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

| 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…6) %}|----------{% endfor %}+
|Revenue {% for i in (1…6) %}| {{ -1 x revenue x (growth_perc**i) | currency }}{% endfor %}

The above code will generate a table for 6 years (e.g. 2017-2022) where the profit of 2016 is taken as basis and multiplied with the growth_perc (e.g. 1,22) to the power of one in 2017, the power of two in 2018 and so on.

Note that exponentiation/power should be coded as ‘**’.

Hope the above helps you to further build your P&L budget forecast.

Kind regards,
Robin

3

Thank you.:smiley:
We’re almost there.
But how to the assign the right growth percentage bij choosing the sector and the number of employees in the drop doen menus.

I have this table with the growth percentage per sector per employees

| Sector | 1-5 | 6-10 | 11-20 | 21-50 | 51-100 | > 100
|----------------------------|---------|------- -|---------|---------|----------|---------+
| Construction | 0.021 | 0.022 | 0.025 | 0.028 | 0.021 | 0.012 |
| Retail non-food | 0.025 | 0.025 | 0.025 | 0.025 | 0.025 | 0.025 |
| Services | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 |
| Food | 0.022 | 0.022 | 0.022 | 0.022 | 0.022 | 0.022 |
| Healthcare | 0.04 | 0.04 | 0.04 | 0.04 | 0.04 | 0.04 |
| Wholesale | 0.025 | 0.025 | 0.025 | 0.025 | 0.025 | 0.025 |
| Hospitality and Leisure | 0.02 | 0.02 | 0.02 | 0.02 | 0.02 | 0.02 |
| Industry | 0.023 | 0.023 | 0.023 | 0.032 | 0.023 | 0.023 |
| Agriculture & Horticulture | 0.036 | 0.036 | 0.036 | 0.036 | 0.036 | 0.036 |
| Mobility & Transport | 0.02 | 0.02 | 0.02 | 0.02 | 0.02 | 0.02 |

4

@NickS,

You can assign the correct growth percentage by using different cases, e.g.:

{% t= “Construction|Retail non-food|Services|Food|Healthcare|Wholesale|Hospitality & Leisure|Industry/Manufacturing|Agriculture & Horticulture|Mobility & Transport” %}
{% capture sector_types %}{% t “Construction|Retail non-food|Services|Food|Healthcare|Wholesale|Hospitality and Leisure|Industry|Agriculture & Horticulture|Mobility & Transport” %}{% endcapture %}

|Sector
|---------+#
|{% input custom.val.sector as:select options:sector_types option_values:“Construction|Retail non-food|Services|Food|Healthcare|Wholesale|Hospitality & Leisure|Industry|Agriculture & Horticulture|Mobility & Transportbouw” %}

{% t= “1 - 5 | 6 - 10 | 11 - 20 | 21 - 50 | 51 - 100 | > 100” %}
{% capture employees_types %}{% t “1 - 5 | 6 - 10 | 11 - 20 | 21 - 50 | 51 - 100 | > 100” %}{% endcapture %}

|Number of employees
|---------+#
|{% input custom.val.employees as:select options:employees_types option_values:“1 - 5|6 - 10|11 - 20|21 - 50|51 - 100|> 100” %}

{% case custom.val.sector %}
{% when ‘Construction’ %}
{% case custom.val.employees %}
{% when ‘1 - 5’ %}
{% assign growth_perc = 1.12 %}
{% when ‘6 - 10’ %}
{% assign growth_perc = 1.14 %}
{% endcase %}
{% when ‘Services’ %}
{% case custom.val.employees %}
{% when ‘1 - 5’ %}
{% assign growth_perc = 1.16 %}
{% when ‘6 - 10’ %}
{% assign growth_perc = 1.18 %}
{% endcase %}
{% endcase %}

Hope you can complete your code!

Good luck!

Kind regards,
Robin

1 Like
5

Thanks.

How do you use {% assign growth_perc = ** %} with {{ -1 x revenue x (growth_perc**i) | currency }}

{% assign growth_perc = 1.02 %}

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

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

| Description 1 |{% 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…6) %}|----------{% endfor %}+
|Revenue {% for i in (1…6) %}| {{ -1revenue(growth_perc) | currency }}{% endfor %}



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

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

| Description 2 |{% 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 | {{ -1revenue | currency }} {% for i in (1…5) %}| {% capture year %}plus_{{ forloop.index }}y{% endcapture %}{% assign revenue_value = period[year].accounts.70 %}{{ -1revenue_value | currency }}{% endfor %}

Growth%20percentage%20per%20y
Growth percentage per y.png2639×737 26.9 KB

see print screen
Description 1 is how it is now but not right
Description 2 is right but without the growth forecast years