Hi,
We have a group of clients that they are changing the % rate for the cost plus calculation in the middle of the year.
Our cost plus template calculates the mark up from the total expenses adding the % so if the next month we add a different %, the cost plus will be re-calculated from the begging in of the year and taking into account what it is already bill but all at the current rate.
Would anyone have an idea how we can add a formula to add expenses from Jan to March for example at X% and from April to Dec at Y%?
Below it is a snapshot of our cost plus template for the cost section:
|Total profit for Cost Plus purposes
|
{% for detail in (1…y) %}
{% assign expense_costs = custom.cost.centre[detail] %}
{% assign prev_expense_costs = period.minus_1p.accounts.include_zeros.[current_account].first.custom.cost.centre[detail] %}
{% for item in (1…x) %}
{% capture key2 %}{{ item }}_{{ detail }}{% endcapture %}
{%$9+ custom.other[key2].centre %}
{%$14+ period.minus_1p.accounts.include_zeros.[current_account].first.custom.other[key2].centre %}
{% endfor %}
|{{ expense_costs+$9 | currency_dc }}
{% assign total_expense_cost = expense_costs+$9 %}
{% assign prev_total_expense_cost = prev_expense_costs+$14 %}
{% assign total_profit = total_profit | append:total_expense_cost |append:‘;’ %}
{% assign prev_total_profit = prev_total_profit | append:prev_total_expense_cost |append:‘;’ %}
{% assign $9 = 0 %}
{% assign $14 = 0 %}
{% endfor %}
|{{ $1+$4 | currency_dc }}
|
|{{$2 | currency_dc}}
{% newline %}
{% assign total_profit = total_profit | split:‘;’ %}
{% assign prev_total_profit = prev_total_profit | split:‘;’ %}
Hi @olga.salamanca and welcome to the Silverfin Developer Community!
It’s not clear where the rate is applied to the expenses from the code snippet you have posted.
Do you also have a date for every expense? As that would be crucial to be able to determine which rate should be applied to it.
I’m going to assume that you do have the date. Then I would suggest something as follows.
This new calculation also only needs to happen if you are working with two rates, so make sure to put it in an if-statement or it will apply to all files in all periods. You could introduce a boolean that should be checked if you want to use a different rate, and rollforward the boolean to blank so that behaviour is reset in the next period for example.
I’ve made the cut-off date static 30th of April 23, but this could also look at e.g. a custom variable.
x is the old rate, y is the new rate.
custom.cost.centre[cost_date] holds the date of the expense.
{% comment %}Boolean if you want to use the new rate{% endcomment %}
{% assign use_two_rates = custom.rates.two_rates | default:false %}
{% comment %}Format the cut-off date{% endcomment %}
{% assign cut_off_date = "2023-04-30" | date:"%Y-%m-%d" %}
{% comment %}Format date of the current expense{% endcomment %}
{% assign expense_date_formatted = custom.cost.centre[cost_date] | date:"%Y-%m-%d" %}
{% if use_two_rates %}
{% comment %}If date is before the cut off, apply old rate x, else apply new rate y{% endcomment %}
{% if expense_date_formatted < cut_off_date %}
{% assign marked_up_cost = expense_costs*x %}
{% else %}
{% assign marked_up_cost = expense_costs*y %}
{% endif %}
{% else %}
{% assign marked_up_cost = expense_costs*y %}
{% endif %}
{% comment %}Store all the markedup costs in a variable that holds the total{% endcomment %}
{% assign total_expense_cost = total_expense_cost+marked_up_cost %}
Kind regards,
Romy