# Tutorial File Budget Formulas For Multi-Currency Financial Planning

Universal
Double-Entry Accounting with foreign currencies
Education

The possibility to enter formulas is available in Banana Accounting Plus only with the Advanced plan.

## In Banana WebApp openen

Open Banana Accounting Plus op uw browser zonder enige installatie. Pas het sjabloon aan, voer de transacties in en sla het bestand op uw computer op.

## Model documentatie

The possibility to enter formulas is available in Banana Accounting Plus only with the Advanced plan. Update now!

This example will explain how to use formulas specific to double-entry bookkeeping with multi-currency functionalities.

Documentation on use of formula in the Budget table.

## Amount calculation in base currency

The formula will calculate the value in the account's currency first and then the relative value in the base currency.

## Exchange rates and historical rates

• Doc 10 and 12.
If an exchange rate is indicated in the exchange rate column, the program will calculate the amount in the base currency (EUR in this case) using the exchange rate indicated in this row.
• Doc 11 and 13.

If there is no exchange rate indicated, the program will apply the exchange rate in the Exchange rates table for this calculation as well as for the successive operation in the case a Repeat function is applied.

• If historical exchange rates are defined in the Exchange rates table, via adding a date, the program will apply the exchange relative to the date or the previous, most recent one.

## Use of formulas in the base currency

All formulas available in the Budget table can be used in the Formula column.

The following examples show the value in the base currency (EUR) calculated according to a Formula.

• Doc 21.
The sales value is multiplied according to the Formula EUR value. The exchange rate in the corresponding column will be ignored.
• Doc 22.
As in the previous example, but for the value of "AmountCurrency" which is the result of the formula.
• Doc 23.
In the “budget.js” document we have defined an exchange rate function calculating the value on the basis of the contents in the row.
With these functions it is possible to create exchange rate simulations.

### Example for Exchange rate function

The USD exchange rate is established on a monthly basis.

A constant exchange rate is used for GBP instead.

The function will result as the amount of AmountCurrency multiplied by the exchange rate.

```  ```
function cambio() {
var importo = row.value("AmountCurrency");
var cambio = 1;
var moneta = row.value("ExchangeCurrency");
var data = Banana.Converter.toDate(row.value("JDate"));
var mese = data.getMonth() + 1;
if (moneta == "USD") {
cambio = 0.95;
if (mese == 1)
cambio = 0.96;
else if (mese == 2)
cambio = 0.97;
}
if (moneta == "GBP") {
cambio = 1.30;
}
return importo * cambio;
}
```
```

## Formulas for calculation of exchange rate differences

For further information on calculation of exchange rate differences please refer to information about transactions in multi currency and to the Create exchange rate differences command.

• The currency is always the base currency.
• Amounts must not be entered in the account's currency.
• Exchange rate is left blank and will be ignored, even if a value is entered.
• The budgetExchangeDifference function calculates the exchange rate difference for an account. This value represents the non realized exchange rate profit or loss.
.
• The function will return a positive value if it is an exchange rate loss and a negative (credit) in case of exchange rate profit.

Exchange rate profit and exchange rate loss need to be entered in two different rows.

• The resulting amount represents the revaluation necessary in the base currency to ensure that the account balance in the base currency matches the balance of the account in the currency converted at the exchange rate used for the calculation of exchange rate differences.

• The first parameter "CUSD" of the budgetExchangeDifferences function is the account in foreign currency for which the exchange rate difference will be calculated.
• The second parameter is the exchange rate to be applied.
• If no exchange rate is indicated, the historical exchange rate relative to the date of the entered transaction is applied.
• When "current" is used, the exchange in the Exchange rate tables with no date will be used.
• An exchange my also be indicated. A point "." must be used as a decimal separator.
```  ```
credit(budgetExchangeDifference("CUSD"))
credit(budgetExchangeDifference("CUSD", "current"))
credit(budgetExchangeDifference("CUSD", "0.95"))
```
```

## Modelvoorbeeld

Luke's coffeehouse

Table: Accounts
Group Description Currency Sum In Gr1
BALANCE SHEET
ASSETS
Cash Cash on hand EUR 1
Bank Bank account EUR 1
BankUSD USD Bank account USD 1
BankGBP GBP Bank account GBP 1
Customers Customers account EUR 1
Furniture Furniture EUR 1
Inventory Inventory EUR 1
1   TOTAL ASSETS   00 1

LIABILITIES AND EQUITY
Suppliers Suppliers EUR 2
Loan Loan EUR 2
Equity Owner equity EUR 2
PL   Profit(-) or loss (+) from current year   2 1
2   TOTAL LIABILITIES AND EQUITY   00 1

PROFIT AND LOSS STATEMENT
INCOME
Sales Income from sales EUR 4
Exchange Rate Profit Exchange rate profit EUR 4
Interest income Interest income EUR 4
4   TOTAL INCOME   02

EXPENSES
Personnel Personnel expenses EUR 3
Goods Cost of goods EUR 3
Rent Rental and related expenses EUR 3
Interest expense Interest expense EUR 3
Exchange rate loss Exchange rate loss EUR 3
Depreciation Depreciation EUR 3
3   TOTAL EXPENSES   02 1
1
02   Profit(-) Loss(+) from Profit and Loss Statement   PL 1

00   Difference should be zero (void)

Table: Exchange rates
Ref.Currency Currency Text
USD US Dollars
GBP British Pounds
USD US Dollars
USD US Dollars
USD US Dollars
USD US Dollars
USD US Dollars
USD US Dollars
USD US Dollars
USD US Dollars

GBP British Pounds
GBP British Pounds
GBP British Pounds
GBP British Pounds
GBP British Pounds
GBP British Pounds
GBP British Pounds
GBP British Pounds