How to sum up totals in P&L forecast

Templates
, , ,
1

Hi Sam,

I want to add the following totals in the P & L forecast. Do you know a way I can do that?

  • Operating income = Other operating rev + Change in work + Change in inventory.
  • Gross margin = (Net revenue + Operating income) - (Cost of sales + Cost of outsourced work + Other purchase).
  • Operating profit (EBITDA) = Gross margin - (Wages+ social + Pension + other staff + Accomodation cost + Sales costs + transport costs + Other operating).
  • EBIT = EBITDA - depreciation of tangible and intangible assets).
  • Result before taxation = EBIT + (Interest income - interest expenses) + (exeptional income - exeptional expenses) + (revenues + changes in value).
  • Result after taxation = Result before taxation - (corporate taxes + income taxes - result participations).
  • Net profit = Result after taxation.
  • Adding to reserves = Net profit - Dividend

{% assign growth_perc = 1.02 %}

{% assign revenue = period.accounts | range:“70” %}
{% assign net_revenue = period.accounts | range: “70” %}
{% assign other_operating_revenues = period.accounts | range:“74” %}
{% assign change_in_work = period.accounts | range:“0” %}
{% assign change_in_inventory_finished_goods = period.accounts | range:“0” %}

{% assign cost_of_sales = period.accounts | range:“0” %}
{% assign cost_of_outsourced_work = period.accounts | range:“0” %}
{% assign other_purchase = period.accounts | range:“0” %}

{% assign wages_and_salaries = period.accounts | range:“455” %}
{% assign social_security_costs = period.accounts | range:“0” %}
{% assign pension_costs = period.accounts | range:“0” %}
{% assign other_staff_costs = period.accounts | range:“0” %}
{% assign accommodation_costs = period.accounts | range:“0” %}
{% assign sales_costs = period.accounts | range:“0” %}
{% assign transport_costs = period.accounts | range:“0” %}
{% assign other_operating_expenses = period.accounts | range:“61” %}

{% assign depreciation_of_assets = period.accounts | range:“63” %}

{% assign interest_income = period.accounts | range:‘7513’ %}
{% assign interest_expenses = period.accounts | range:“7513” %}
{% assign extraordinary_income = period.accounts | range:“76” %}
{% assign extraordinary_expenses = period.accounts | range:“65” %}
{% assign other_revenues = period.accounts | range:“7510” %}
{% assign changes_in_value = period.accounts | range:“0” %}

{% assign corporate_taxes = period.accounts | range:“67” %}
{% assign income_tax = period.accounts | range:“0” %}

{% assign result_participations = period.accounts | range:“0” %}

{% assign dividend = period.accounts | range:“0” %}

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

{::infotext as=“hover”}
By choosing a previous year you can make a valuation with three to five future forecast years at your own discretion
{:/infotext}

| 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 (0…4) %}|----------{% endfor %}+
|Revenue |{{ -1revenue | currency }} {% for i in (0…4) %}| {{ -1growth_percrevenue | currency }} {% assign revenue = growth_percrevenue %} {% endfor %}
|Net revenue |
|Other operating revenues |{{ -1other_operating_revenues | currency }} {% for i in (0…4) %}| {{ -1growth_percother_operating_revenues | currency }} {% assign other_operating_revenues = growth_percother_operating_revenues %} {% endfor %}
|Change in work in progress |{{ -1change_in_work | currency }} {% for i in (0…4) %}| {{ -1growth_percchange_in_work | currency }} {% assign change_in_work = growth_percchange_in_work %} {% endfor %}
|Change in inventory of finished goods |{{ -1change_in_inventory_finished_goods | currency }} {% for i in (0…4) %}| {{ -1growth_percchange_in_inventory_finished_goods | currency }} {% assign change_in_inventory_finished_goods = growth_percchange_in_inventory_finished_goods %} {% endfor %}
|Operating income |
|Cost of sales |{{ -1cost_of_sales | currency }} {% for i in (0…4) %}| {{ -1growth_perccost_of_sales | currency }} {% assign cost_of_sales = growth_perccost_of_sales %} {% endfor %}
|Cost of outsourced work |{{ -1cost_of_outsourced_work | currency }} {% for i in (0…4) %}| {{ -1growth_perccost_of_outsourced_work | currency }} {% assign cost_of_outsourced_work = growth_perccost_of_outsourced_work %} {% endfor %}
|Other purchase |{{ -1other_purchase | currency }} {% for i in (0…4) %}| {{ -1growth_percother_purchase | currency }} {% assign other_purchase = growth_percother_purchase %} {% endfor %}
|Gross margin |
|Wages and Salaries |{{ -1wages_and_salaries | currency }} {% for i in (0…4) %}| {{ -1growth_percwages_and_salaries | currency }} {% assign wages_and_salaries = growth_percwages_and_salaries %} {% endfor %}
|Social security costs |{{ -1social_security_costs | currency }} {% for i in (0…4) %}| {{ -1growth_percsocial_security_costs | currency }} {% assign social_security_costs = growth_percsocial_security_costs %} {% endfor %}
|Pension costs |{{ -1pension_costs | currency }} {% for i in (0…4) %}| {{ -1growth_percpension_costs | currency }} {% assign pension_costs = growth_percpension_costs %} {% endfor %}
|Other staff costs |{{ -1other_staff_costs | currency }} {% for i in (0…4) %}| {{ -1growth_percother_staff_costs | currency }} {% assign other_staff_costs = growth_percother_staff_costs %} {% endfor %}
|Accommodation costs |{{ -1accommodation_costs | currency }} {% for i in (0…4) %}| {{ -1growth_percaccommodation_costs | currency }} {% assign accommodation_costs = growth_percaccommodation_costs %} {% endfor %}
|Sales costs |{{ -1sales_costs | currency }} {% for i in (0…4) %}| {{ -1growth_percsales_costs | currency }} {% assign sales_costs = growth_percsales_costs %} {% endfor %}
|Transport costs |{{ -1transport_costs | currency }} {% for i in (0…4) %}| {{ -1growth_perctransport_costs | currency }} {% assign transport_costs = growth_perctransport_costs %} {% endfor %}
|Other operating expenses |{{ -1other_operating_expenses | currency }} {% for i in (0…4) %}| {{ -1growth_percother_operating_expenses | currency }} {% assign other_operating_expenses = growth_percother_operating_expenses %} {% endfor %}
|Operating profit (EBITDA) |
|Depreciation of assets |{{ -1depreciation_of_assets | currency }} {% for i in (0…4) %}| {{ -1growth_percdepreciation_of_assets | currency }} {% assign depreciation_of_assets = growth_percdepreciation_of_assets %} {% endfor %}
|Operating profit (EBIT) |
|Interest income |{{ -1interest_income | currency }} {% for i in (0…4) %}| {{ -1growth_percinterest_income | currency }} {% assign interest_income = growth_percinterest_income %} {% endfor %}
|Interest expenses |{{ -1interest_expenses | currency }} {% for i in (0…4) %}| {{ -1growth_percinterest_expenses | currency }} {% assign interest_expenses = growth_percinterest_expenses %} {% endfor %}
|Extraordinary income |{{ -1extraordinary_income | currency }} {% for i in (0…4) %}| {{ -1growth_percextraordinary_income | currency }} {% assign extraordinary_income = growth_percextraordinary_income %} {% endfor %}
|Extraordinary expenses |{{ -1extraordinary_expenses | currency }} {% for i in (0…4) %}| {{ -1growth_percextraordinary_expenses | currency }} {% assign extraordinary_expenses = growth_percextraordinary_expenses %} {% endfor %}
|Other revenues |{{ -1other_revenues | currency }} {% for i in (0…4) %}| {{ -1growth_percother_revenues | currency }} {% assign other_revenues = growth_percother_revenues %} {% endfor %}
|Changes in value |{{ -1changes_in_value | currency }} {% for i in (0…4) %}| {{ -1growth_percchanges_in_value | currency }} {% assign changes_in_value = growth_percchanges_in_value %} {% endfor %}
|Result before taxation |
|Corporate taxes |{{ -1corporate_taxes | currency }} {% for i in (0…4) %}| {{ -1growth_perccorporate_taxes | currency }} {% assign corporate_taxes = growth_perccorporate_taxes %} {% endfor %}
|Income tax |{{ -1income_tax | currency }} {% for i in (0…4) %}| {{ -1growth_percincome_tax | currency }} {% assign income_tax = growth_percincome_tax %} {% endfor %}
|Result participations |{{ -1result_participations | currency }} {% for i in (0…4) %}| {{ -1growth_percresult_participations | currency }} {% assign result_participations = growth_percresult_participations %} {% endfor %}
|Result after taxation |
|Net profit |
|Dividend (shareholders financing) |{{ -1dividend = period.accounts | currency }} {% for i in (0…4) %}| {{ -1growth_percdividend = period.accounts | currency }} {% assign dividend = period.accounts = growth_percdividend = period.accounts %} {% endfor %}
|Adding to reserves |

P%26L%20prinstscreen
P%26L%20prinstscreen1576×1447 121 KB

2

Dag Nick

Dit eenvoudig voorbeeld zou je verder moeten helpen:

{% assign revenue = 1000 %}
{% assign rev_perc = 1.2 %}

{% assign cost = 600 %}
{% assign cost_perc = 1.15 %}

{% for i in (1..4) %}
  {% assign revenue = revenue*rev_perc %}
  {% capture profit_var %}profit_{{i}}{% endcapture %}
  {% assign [profit_var] = revenue %}
{% endfor %}

{% for i in (1..4) %}
  {% assign cost = cost*cost_perc %}
  {% capture profit_var %}cost_{{i}}{% endcapture %}
  {% assign [profit_var] = [profit_var]-cost %}
{% endfor %}

{% for i in (1..4) %}
  {% capture profit_var %}cost_{{i}}{% endcapture %}
  {{ [profit_var] }}
{% endfor %}

Met vriendelijke groeten
Sam

3

Hi Sam,

Thanks.

For some reason I am unable to add up the amounts.
On the line ‘Operating income’ I want to have the totals of these 3 items per year:
other_operating_revenues
change_in_work
change_in_inventory_finished_goods

Would you please write the code for this (total per year Operating income) then I can probably do the rest myselves.
Thank you.

{% assign growth_perc = 1.02 %}

{% assign other_operating_revenues = period.accounts | range:“74” %}
{% assign change_in_work = period.accounts | range:“0” %}
{% assign change_in_inventory_finished_goods = period.accounts | range:“0” %}

{% 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 (0…4) %}|----------{% endfor %}+
|Other operating revenues |{{ -1other_operating_revenues | currency }} {% for i in (0…4) %}| {{ -1growth_percother_operating_revenues | currency }} {% assign other_operating_revenues = growth_percother_operating_revenues %} {% endfor %}
|Change in work in progress |{{ -1change_in_work | currency }} {% for i in (0…4) %}| {{ -1growth_percchange_in_work | currency }} {% assign change_in_work = growth_percchange_in_work %} {% endfor %}
|Change in inventory of finished goods |{{ -1change_in_inventory_finished_goods | currency }} {% for i in (0…4) %}| {{ -1growth_percchange_in_inventory_finished_goods | currency }} {% assign change_in_inventory_finished_goods = growth_percchange_in_inventory_finished_goods %} {% endfor %}
|Operating income |

Total%20operating%20income%20printscreen
Total operating income printscreen.png2417×518 32 KB

4

We’ve tried something for a while and now it works.
Thank you very much. :smiley:

5

After trying different things, we succeeded.
Thank you.

6

Sorry for the late reaction.

Glad you found something yourself!

Kind regards
Sam