Wykorzystanie Excela dla rozliczeń różnic kursowych na rachunku bankowym.

Rozliczenie różnic kursowych na rachunku bankowym stanowi bardzo duże wyzwanie jeżeli firma nie dysponuje dedykowanym oprogramowaniem naliczającym różnice automatycznie. Problem potęgowany jest dodatkowo przez niejednoznaczne przepisy w zakresie wykorzystywanych kursów. Obecnie w świetle ostatniego wyroku WSA sposób kalkulacji przybliżył się do rzeczywistości, i w świetle bieżącej wykładni nie trzeba wykorzystywać hipotetycznych kwotowa które miałyby miejsce gdyby transakcja wymiany walut została przeprowadzona przez bank z usług którego Spółka korzysta. Oczywiście jeżeli mówimy o operacjach rachunku walutowego realizowanych w walucie tego rachunku, o żadnych transakcjach wymiany walut nie ma mowy. Wpłata, saldo i wypłata ewidencjonowane są w tej samej walucie.

Naturalnym rozwiązaniem, zgodnym z ustawa o rachunkowości jest przyjecie do rozliczenia kursu średniego NBP. Pozostaje jednak kolejny problem do rozwiązania, a mianowicie sposób naliczania kursu historycznego. W uproszczeniu możemy stwierdzić że każda wpłata na rachunek buduje nam kurs historyczny, natomiast wypłata ostatecznie rozlicza różnice kursowe. Na prostym przykładzie liczbowym wygląda to następująco:

• Wpłata 100 EUR na rachunek przy kursie średnim 3,93 buduje nam kurs historyczny.
• Wypłata 100 EUR przy kursie średnim 4,04 spowoduje konieczność naliczenia różnic kursowych 100 x (3,93-4,04) = 11zł. To tak jakbyśmy wypłacili więcej (404zł) niż wcześniej nam wpłynęło (393zł), pomimo tego, że cały czas jest to 100EUR.

Sytuacja z naliczeniem kursu wypłaty jest prosta ponieważ znamy kurs z danego dnia. Sytuacja komplikuje się przy naliczeniu kursu historycznego, zwłaszcza kiedy mamy wiele operacji na rachunku. W praktyce stosuje się dwa podejścia:

• Wycena według kursu historycznego średnioważonego
• Wycena według metodologii FIFO (pierwsze weszło pierwsze wyszło)

Pierwsza metoda polega na obliczeniu średnioważonego kursu ze wszystkich wpływów od momentu założenia rachunku do dnia rozliczanej operacji wypływu. Podstawową zaletą tej metody jest łatwość w użyciu. Druga metoda jest bardziej skomplikowana, ale z drugiej strony daje dokładniejsze wyniki i bardziej jest zgodna z logiką. Polega na takim obliczeniu kursu historycznego, aby do jego kalkulacji weszły ostatnie nie rozliczone wpłaty składające się kwotę rozliczanej obecnie wypłaty. W praktyce polega to na:

• Znalezieniu wpłat na rachunek, które nie zostały jeszcze rozliczone.
• Ostatnie nierozliczone wpłaty powinny być równe rozliczanej wypłacie
• Do rozliczenia wypłaty użyjemy kursu średnioważonego z tych wpłat.

Jak łatwo można się domyśleć, najczęściej wykorzystywaną metoda jest metoda kursu średniego. Najważniejszym argumentem jest fakt, że aby stosować metodologie FIFO konieczne jest wdrożenie złożonego oprogramowania. Na tym przykładzie chciałbym zadać kłam takiemu twierdzeniu, pokazując jak samodzielnie można przygotować funkcję w arkuszu MS Excel, która zabezpieczy poprawne naliczenie różnic zgodnie z metodologią FIFO.  
Pierwszym krokiem jest przygotowanie szablonu do ewidencji operacji na rachunku bankowym. Do każdej daty należy przypisać kurs średni NBP. Do tego celu można wykorzystać funkcję WYSZUKAJ.PIONOWO według daty notowania i połączyć nasz szablon z tabelą notowań.
Przykładowy szablon powinien wyglądać jak na obrazku poniżej:

Z punktu widzenia naszej analizy kluczowe są kolumny H, I, J. Kolumna H zawiera automatyczne wyliczenie salda rachunku na dzień, natomiast w kolumnach I i J wyliczane są różnice kursowe według dwóch metod.

Dla przykładu w wierszu 5 wprowadzono w tych kolumnach następujące formuły:

Kolumna	Formuła
H	=H4+F5-G5
I	=(Fifo_fx(B5;F$3:F5;G$3:G5;E$3:E5)-E5)*G5
J	=(SUMA.ILOCZYNÓW(F$3:F5;E$3:E5)/SUMA(F$3:F5)-E5)*G5

Kalkulacja salda rachunku jest stosunkowo prosta i polega a dodaniu do salda poprzedniego (wiersz wyżej) różnicy pomiędzy wpływem i wypływem z danego dnia.
Kalkulacja różnicy w kolumnie J (według kursu historycznego) polega na:

• Obliczeniu kursu historycznego [SUMA.ILOCZYNÓW (F$3:F5;E$3:E5) /SUMA(F$3:F5)], jako sumy iloczynów kolumny w której znajdują się kursy i kolumny w której znajdują się wpływy. Całość podzielona jest przez sumę tych wpływów. Taka prosta formuła wylicza na kurs średni na dany moment. Ważne jest aby w formule użyć odpowiednich adresów blokując indeksem $ wiersz od którego zaczynamy zakres. Dzięki temu przeciągając formułę w dół arkusza, w miarę dodawania nowych rekordów, kalkulacja będzie uwzględniać kompletny zakres danych.
• Znając kurs historyczny pozostaje tylko odjąć od niego kurs bieżący wypłaty i pomnożyć przez kwotę wypłaty aby uzyskać różnice kursową.

Podobny algorytm wykorzystany został w kalkulacji FIFO, z tym, że kurs historyczny obliczony jest z wykorzystaniem tzw. funkcji użytkownika Fifo_fx. Funkcję taką buduje się w edytorze Visual Basic (wywoływany kombinacją ALT+F11):
Wygląd edytora przedstawia obrazek poniżej:

Aby przygotować funkcję musimy pamiętać o strukturze konstrukcji:
Function Nazwa_Funkcji (parametry)
End Function
Dzięki powyższemu zapisowi, przygotowana przez nas funkcja będzie widoczna w arkuszu tak jak każda inna funkcja Excela:

W kategorii funkcji użytkownika znajdziemy Naszą funkcję, co więcej program pomoże nam ją wprowadzić:

Żeby jednak funkcja była użyteczna, musimy przekazać programowi nasze intencje w języku dla niego zrozumiałym. Dlatego wchodzimy na pole programowania z użyciem Visual Basic for Application czyli popularnych makr. Wbrew pozorom nie jest to skomplikowane, a w przyszłości powoduje wręcz, że łatwiej jest napisać własną niż męczyć się ze złożonymi formułami. Szczególnie dotyczy to przypadku, kiedy mamy wiele różnych warunków.
Na przykładzie naszej funkcji kod budowany jest następująco:

Tak jak wcześniej wspomniałem zaczynamy od ciała funkcji, czyli zamknięcia zapisów pomiędzy definicją nazwy (Function) oraz  jej końcem (End Function). Logika powyższego algorytmu sprowadza się do:

• Przyjęcia do analizy danych takich jak pozycja rozliczanej wpłaty na liście (n), zakres wszystkich obrotów debetowych (wpływy), zakres wszystkich obrotów kredytowych (wypływy) oraz zakres wszystkich kursów.
• Obliczamy jaka jest suma wypływów, które nastąpiły do tej pory
• Następnie rozpoczynamy analizę kolejnych wpływów historycznych (suma_wplywy) w ten sposób że:
  • Jeżeli suma wpływów będzie większa od sumy dotychczasowych wypływów, możemy rozpocząć analizę konkretnych wpływów, które utworzą nam kurs historyczny.
  • Musimy sprawdzić czy analizowany wpływ (zakres_dt(y)) nie został w części już rozliczany używając warunku (suma_wplywy - suma_wyplywy) >= zakres_dt(y).
  • Jeżeli ten warunek jest prawdziwy, znaczy to, że analizowany wpływ nie był częściowo rozliczony. Teraz musimy sprawdzić czy wpływ ten nie jest większy od pozostałego do rozliczenia wypływu zakres_dt(y) > (zakres_cr(n) - fifo_suma).
  • Jeżeli jest większy, to do rozliczenia kursu powinniśmy przyjąć wartość nierozliczonego dokumentu wypływu fifo_kurs = fifo_kurs + (zakres_cr(n) - fifo_suma) * zakres_kurs(y); fifo_suma = zakres_cr(n)

W ten sposób w zmiennej fifo_kurs zbieramy wpłaty historyczne nierozliczone które składają się na rozliczaną wypłatę pomnożone przez odpowiadające im kursy. W zmiennej fifo_suma zbieramy wartości tych wpłat. Na końcu pozostaje obliczenie kursu historycznego jako ilorazu fifo_kurs i fifo_suma.
Tak przygotowana funkcja może być wykorzystana nawet z podstawową znajomością Excela, a jej użycie nie różni się w praktyce poziomem skomplikowania od innych „wbudowanych” funkcji. W ten sposób metoda FIFO może być wykorzystana przy użyciu arkusza kalkulacyjnego, bez dodatkowych nakładów na specjalistyczne oprogramowanie. Jak widać na analizowanym przykładzie w warunkach dużej zmienności kursów walut, zastosowanie tej metody jest bezpieczniejsze i bardziej opłacalne z punktu widzenia wyniku. Metoda kursu średniego zwróciła stratę na poziomie 11 315 zł, natomiast zastosowanie metody FIFO pozwoliło ograniczyć ją do 4 079 zł.