Zastosowanie Informatyki w Finansach i Bankowości II Analiza Wrażliwości przy wykorzystaniu VBA

1. Pierwszy etap polega na utworzeniu prostego modelu opłacalności inwestycji bezpośrednio w arkuszu kalkulacyjnym. Wszelkie obliczenia w sekcji „Projekcje” nie mogą odbywać się bezpośrednio na komórkach, do których będą wprowadzane dane, tylko na komórkach, w których wprowadzona wartość np. przychodów ze sprzedaży będzie skorygowana o komórkę zawierającą współczynnik zmienności o domyślnej wartości 1.

2. Komórki zawierające współczynniki zmienności powinny otrzymać odpowiednie nazwy, np.: zm_p (wsp. dla przychodów), zm_k, zm_n, zm_d, zm_t.

3. Rachunek opłacalności należy sporządzić korzystając wyłącznie z danych zawartych w kolumnach E i F zgodnie ze wzorem poniżej.

4. Należy pamiętać aby komórki zawierające wyniki otrzymały odpowiednie nazwy: npv, irr, npvr, pb.

5. Okres zwrotu jest opcjonalny i można go policzyć tylko dodając moduł zawierający odpowiednią funkcję VBA!

6. Po wykonaniu arkusza z rachunkiem opłacalności można przejść do wykonania arkusza zawierającego tabele z analizą wrażliwości.

7. Po utworzeniu tabeli należy nadać nazwy następującym obszarom (zakresom komórek) w tabeli.

a. C4:K4 – zm

b. C6:K9 – raport_p

c. C12:K15 – raport_k

d. C18:K21 – raport_n

e. C24:K27 – raport_t

f. C30:K33 – raport_d

8. Po nadaniu nazw można przystąpić do tworzenia odpowiednich makr. Należy przejść do edytora VBA i dodać nowy moduł dla makr. Pierwsze makro będzie dotyczyło wrażliwości opłacalności na zmiany przychodów operacyjnych.

Sub Wrazliwosc_Przychodow()

For n = 1 To 9

Range("zm_p").Value = Range("zm").Cells(1, n).Value Range("raport_p").Cells(1, n).Value = Range("npv").Value Range("raport_p").Cells(2, n).Value = Range("irr").Value Range("raport_p").Cells(3, n).Value = Range("npvr").Value Range("raport_p").Cells(4, n).Value = Range("pb").Value Next n

Range("zm_p").Value = 1

End Sub

9. Ponieważ należy ocenić wrażliwość na cztery pozostałe czynniki jakimi są: poziom kosztów, stopy podatku, dyskonto i nakłady, należy utworzyć w analogiczny sposób następujące makra.

a. Wrazliwosc_Kosztow

b. Wrazliwosc_Podatek

c. Wrazliwosc_Naklady

d. Wrazliwosc_Dyskonto

10. Ostatnim etapem jest utworzenie makra uruchamiającego powyższe makra oraz przypisanie go do przycisku w arkuszu z analizą wrażliwości.

Sub Wrazliwosc_Razem()

Wrazliwosc_Przychodow

Wrazliwosc_Kosztow

Wrazliwosc_Dyskonto

Wrazliwosc_Podatek

Wrazliwosc_Naklady

End Sub

11. Opcjonalnie można dodać moduł zawierający następujące makro pozwalające na obliczanie okresu zwrotu oraz wpisanie następującej formuły w odpowiedniej komórce - =payback(C21:M21).

Function Payback(cvec)

Dim csum

Dim i As Integer

If cvec(1) >= 0 Or Application.Sum(cvec) < 0 Then Payback = "Brak zwrotu"

Else

csum = 0

For i = 1 To Application.Count(cvec) csum = csum + cvec(i)

If csum > 0 Then

Exit For

End If

Next i

csum = csum - cvec(i)

Payback = Application.Round(i - 2 - csum / cvec(i), 5) End If

End Function

Funkcja na podstawie: Zaawansowane Modele Finansowe z wykorzystaniem Excela i VBA, M. Jackson i M. Staunton, WileyFinance 2004, Helion