Excel 2007 w firmie.
Controlling, finanse
i nie tylko
Autor: Sebastian Wilczewski, Maciej Wrzód
ISBN: 978-83-246-1292-5
Format: B5, stron: 392
Wykorzystaj możliwoS
ci Excela, aby ułatwić sobie pracę
i zarządzanie domowymi finansami
" Jak wykorzystywać funkcje zagnieżdżone do obliczania premii?
" Jak importować dane finansowe z wielu x
ródeł?
" Jak tworzyć zestawienia zbiorcze z zastosowaniem sum częS
ciowych?
Microsoft Office Excel 2007 to nowoczesne narzędzie, które można wykorzystać nie
tylko w biurze, ale także do sprawnego zarządzania finansami domowymi. Program ten
pozwala na dokonywanie zaawansowanych i skomplikowanych wyliczeń, analiz
i zestawień, a poza tym posiada ogromne możliwoS
ci prezentacji danych. Dzięki
zagnieżdżaniu funkcji może być stosowany do wyliczania premii lub wskazywania
pracowników, którzy mają dostać nagrodę. W warunkach domowych przy użyciu Excela
możesz dokonać wyboru najlepszej oferty kredytowej lub funduszu inwestycyjnego.
 Excel 2007 w firmie. Controling, finanse i nie tylko pokazuje, w jaki sposób korzystać
z programu w charakterze narzędzia analiz. Nie ma znaczenia, czy dopiero zaczynasz
pracę z Excelem, czy też używałeS
go już wczeS
niej. Książka zawiera wiele
praktycznych ćwiczeń oraz konkretnych przykładów wraz z objaS
nieniami, które
wskazują na różne zastosowania tego programu. Dowiesz się między innymi, w jaki
sposób używać Excela do planowania płynnoS
ci finansowej albo jak obliczyć okres
zwrotu nakładów. Nauczysz się tworzyć prezentacje danych oraz własne funkcje.
Co ważne, ten podręcznik został skonstruowany tak, żebyS
mógł nie tylko
wykorzystywać, ale i łączyć poszczególne funkcjonalnoS
ci  i w efekcie biegle poruszać
się wS
ród ogromnej liczby możliwoS
ci tej niesamowitej aplikacji.
" Funkcje statystyczne, logiczne i tekstowe
" Funkcje matematyczne i trygonometryczne
" Adresy względne i bezwzględne
" Operacje finansowe z wykorzystaniem daty i czasu
Wydawnictwo Helion
" Funkcje zagnieżdżone
ul. KoS
ciuszki 1c
" Analiza finansowa
44-100 Gliwice
" Analiza wielokryterialna
tel. 032 230 98 63
" Udostępnianie skoroszytu
e-mail: helion@helion.pl
" Ochrona danych przed kopiowaniem
" Automatyzacja pracy z danymi
Excel 2007  praktyczny zestaw nowoczesnych rozwiązań
do zarządzania finansami firmowymi i domowymi
SPIS TREŚCI
Wstęp 7
1. Zastosowanie wybranych funkcji
do analizy finansowej i nie tylko 11
Funkcje statystyczne .............................................................................. 13
Wyszukiwanie wartości maksymalnej (zastosowanie
funkcji Max()) na przykładzie analizy sprzedaży ................... 13
Wyszukiwanie wartości minimalnej (zastosowanie
funkcji Min()) na przykładzie analizy sprzedaży .................... 16
Wyszukiwanie średniej (zastosowanie funkcji Średnia())
na przykładzie analizy sprzedaży ........................................... 19
Zliczanie osób i innych elementów (funkcja Licz.warunki())
spełniających dane warunki .................................................... 21
Funkcje logiczne ..................................................................................... 23
Wykonywanie obliczeń zależnych od spełnienia
innych warunków (funkcja Jeżeli()) ........................................ 23
Sprawdzanie czy dane spełniają jeden z wielu warunków
(funkcja LUB()) ......................................................................... 27
Funkcje tekstowe ................................................................................... 29
Pobieranie wybranych fragmentów tekstu
do dalszego wykorzystania (funkcja Fragment.Tekstu()) ....... 29
A
ączenie kilku ciągów znaków w jeden
(funkcja Złącz.Teksty()) ............................................................ 32
Excel 2007 PL. Controlling, finanse i nie tylko
Zamiana ciągu znaków na ciąg pisany tylko małymi
literami (funkcja Litery.Małe()) ...............................................34
Zamiana ciągu znaków na ciąg pisany tylko wielkimi
literami (funkcja Litery.Wielkie()) ...........................................34
Zamiana ciągu znaków na ciąg pisany od wielkiej litery
(funkcja Z.Wielkiej.Litery()) .....................................................34
Funkcje wyszukiwania i adresu .............................................................35
Wyszukiwanie informacji o osobach, towarach
(zastosowanie funkcji Wyszukaj.pionowo())
na przykładzie przygotowania formatki faktury ....................35
Funkcje matematyczne i trygonometryczne ..........................................40
Obliczanie sumy (zastosowanie funkcji Suma())
na przykładzie analizy sprzedaży ...........................................40
Sumowanie tylko tych elementów, które spełniają
zadane kryteria (zastosowanie funkcji Suma.jeżeli()),
na przykładzie analizy sprzedaży ...........................................42
Zaokrąglanie danych finansowych
(zastosowanie funkcji Zaokr()) .................................................45
2. Adresy względne i bezwzględne 49
Adresy względne .....................................................................................50
Adresy bezwzględne ...............................................................................56
3. Operacje finansowe z wykorzystaniem
daty i czasu 65
Data i godzina jako część zestawień finansowych ................................66
Właściwa prezentacja daty i czasu w arkuszach
programu Microsoft Office Excel 2007 ...................................71
Obliczenia prowadzone na dacie i czasie ...................................79
Funkcje kategorii Data i godzina ................................................81
4. Funkcje zagnieżdżone 101
Kilka słów o zagnieżdżaniu funkcji .....................................................102
Wykorzystywanie zagnieżdżeń funkcji do wyliczania premii ............103
Wykorzystanie funkcji zagnieżdżonych do wskazania
pracowników, którzy mają otrzymać nagrodę jubileuszową ..........108
Kilka dodatkowych informacji .............................................................112
5. Pobieranie danych finansowych
z różnych z
ródeł 113
Import danych finansowych z wielu z
ródeł ........................................114
Dostęp do danych opublikowanych na stronach internetowych ........124
Pobieranie informacji z programu Microsoft Outlook .........................133
4
Spis treści
6. Analiza finansowa 141
Wykonywanie działań analitycznych na danych z wielu arkuszy
na przykładzie sumowania sprzedaży z różnych okresów ............. 142
Konsolidacja danych na przykładzie analizy czasu pracy
poszczególnych pracowników w poszczególnych miesiącach
i przy poszczególnych projektach ..................................................... 145
Filtrowanie danych .............................................................................. 150
Stosowanie autofiltru na przykładzie analizy informacji
o sprzedaży ............................................................................ 150
Stosowanie filtru zaawansowanego na przykładzie
analizy informacji o pracownikach ....................................... 157
Zliczanie obiektów występujących w bazie za pomocą funkcji
Bd.ile.rekordów.A() na przykładzie analizy sprzedaży .................... 162
Tworzenie zestawień zbiorczych  zastosowanie
sum częściowych .............................................................................. 165
Tworzenie elastycznych analiz za pomocą tabel
i wykresów przestawnych ................................................................ 172
Wykorzystanie tabel przestawnych ......................................... 172
Wykorzystanie wykresów przestawnych ................................ 179
7. Właściwa prezentacja
danych finansowych 183
Formatowanie danych finansowych ................................................... 184
Formatowanie niestandardowe wykorzystywane w finansach
i controllingu ..................................................................................... 201
Formatowanie warunkowe .................................................................. 206
Budowa wykresów w oparciu o dane finansowe ................................ 217
Przebudowa wykresów na potrzeby analizy finansowej .................... 223
8. Funkcje finansowe 239
Przyszła wartość inwestycji  zastosowanie funkcji FV() ................. 240
Dyskontowanie wartości  zastosowanie funkcji PV() ...................... 245
Stopa zwrotu ........................................................................................ 248
Stopa zwrotu z kapitału (ROI) .................................................. 248
Stopa zwrotu z kapitału własnego (ROE) ................................. 248
Wewnętrzna stopa zwrotu  zastosowanie funkcji IRR() ...... 248
Wyliczanie raty kredytowej i analiza oprocentowania ...................... 251
Obliczanie raty kredytu przy ratach równych
 zastosowanie funkcji PMT() .............................................. 251
Obliczanie raty kredytu przy ratach malejących ..................... 253
Efektywna i nominalna stopa procentowa .............................. 257
Sposoby wyliczania amortyzacji .............................................. 260
5
Excel 2007 PL. Controlling, finanse i nie tylko
9. Zagadnienia optymalizacji danych 267
Analiza  co jeżeli w przypadku danych finansowych ........................269
Analiza wrażliwości poprzez tabele danych ........................................274
Analiza wielowariantowa z wykorzystaniem scenariuszy .................281
Optymalizacja danych przy wielu niewiadomych ...............................288
Analiza danych .....................................................................................301
Histogram ..................................................................................301
Korelacja ....................................................................................303
10. Zarządzanie płynnością finansową
oraz segmentami rynku 309
Wydzielanie rejonów rynku .................................................................310
Analiza wielokryterialna ......................................................................319
Planowanie płynności finansowej ........................................................326
11. Analiza inwestycji 333
Obliczanie okresu zwrotu nakładów ...................................................334
Określenie bieżącej i przyszłej wartości inwestycji
 funkcja NPV() ................................................................................336
Wybór najkorzystniejszej inwestycji  indeks zyskowności .............338
12. Praca wielu osób na jednym skoroszycie 341
Udostępnianie skoroszytu ....................................................................342
Śledzenie zmian w udostępnionym skoroszycie ..................................346
Akceptacja i odrzucanie zmian ............................................................350
Ochrona skoroszytu ..............................................................................351
Ochrona arkusza ...................................................................................353
Nadawanie uprawnień do komórek .....................................................356
Zabezpieczenie pliku hasłem ................................................................358
Ochrona danych przed drukowaniem, kopiowaniem
i dystrybucją ......................................................................................359
13. Wykorzystanie makra i własnych funkcji
do usprawnienia analiz finansowych 363
Tworzenie funkcji finansowych ...........................................................364
Automatyzacja pracy z danymi  wykorzystanie makr ....................372
Rejestracja makra ......................................................................372
Skorowidz 383
6
9
ZAGADNIENIA
OPTYMALIZACJI
DANYCH
Ten rozdzia ma na celu prezentacj mo liwo ci analitycznych
programu Microsoft Office Excel 2007. Program nie tylko umo -
liwia obliczanie i w a ciw prezentacje danych, pozwala równie
przy wykorzystaniu odpowiednich narz dzi obliczy równania
z jedn oraz z wieloma niewiadomymi. Mo liwe jest tak e wielo-
wariantowe prezentowanie danych finansowych.
Z tego rozdzia u dowiesz si :
w jaki sposób przeprowadza analiz  co je eli
i wyszukiwa rozwi za równa z jedn niewiadom ,
jak mo na wykorzysta program Microsoft Office Excel
2007 do analizy wra liwo ci,
jak atwo prowadzi analiz wielowariantow ,
w jaki sposób oblicza równania z wieloma niewiadomymi,
Excel 2007 PL. Controlling, finanse i nie tylko
jakie narz dzia analizy statystycznej dost pne
s w programie.
Program Microsoft Office Excel 2007 poza szerokimi mo liwo-
ciami obliczania i prezentowania danych finansowych pozwala
na bardzo zaawansowan ich analiz . Wykorzystuj c znajduj c
si na wst ce zak adk Dane, uzyskuje si dost p do narz dzi s u-
cych do przeprowadzania optymalizacji. Narz dzia te znajduj
si w dwóch sekcjach: Narz dzia danych oraz Analiza. Ta druga
sekcja domy lnie nie jest w czona. Aby uzyska dost p do narz -
dzi Analiza danych oraz Solver, nale y zainstalowa odpowiednie
dodatki. Mo na to wykona poprzez przej cie do polecenia Opcje
programu Excel znajduj cego si w rozwijanym menu otwieranym
przyciskiem pakietu Office.
Aby zainstalowa dodatki umo liwiaj ce optymalizacj danych,
nale y:
1. Wybra przycisk pakietu Office (jedyny przycisk menu
w nowym interfejsie Excela 2007), a nast pnie wskaza
polecenie Opcje programu Excel, co prezentuje rysunek 9.1.
Rysunek 9.1.
Wybór
przycisku
programu
Excel
268
Rozdzia 9. Zagadnienia optymalizacji danych
2. W oknie Opcje programu Excel przej do sekcji Dodatki
w lewej cz ci okna  zostan zaprezentowane aktywne
i nieaktywne dodatki programu.
3. W sekcji Zarz dzaj wybra z listy rozwijanej Dodatki
programu Excel, a nast pnie wcisn przycisk Przejd & .
4. Po otwarciu okna Dodatki zaznaczy przycisk opcji
przy dodatkach: Analysis ToolPak oraz Dodatek Solver,
po czym wybra przycisk OK (rysunek 9.2).
Rysunek 9.2.
Okno Dodatki
5. Dodatki zostan zainstalowane i umieszczone na zak adce
Dane w sekcji Analiza.
Analiza  co je eli
w przypadku danych
finansowych
Jedn z podstawowych funkcjonalno ci zwi zanych z optymali-
zacj danych jest mo liwo symulowania sytuacji  co je eli ,
a wi c rozwi zywania równa z jedn niewiadom . Co stanie
si z zyskiem firmy, je li zmniejszona zostanie mar a, b d jak
b d wygl da y koszty przedsi wzi cia przy u yciu dro szych
269
Excel 2007 PL. Controlling, finanse i nie tylko
materia ów wyko czeniowych  to podstawowe pytania, na które
firmy musz odpowiada codziennie.
Doskona ym narz dziem u atwiaj cym tak analiz danych jest
polecenie Szukaj wyniku, które w atwy sposób umo liwia odnale-
zienie wskazanej warto ci niewiadomej przy okre lonych wszyst-
kich pozosta ych warto ciach.
Polecenie umieszczone jest na zak adce Dane w sekcji Narz dzia
danych pod list rozwijan Analiza symulacji. Po wyborze pole-
cenia Szukaj wyniku otwiera si okno z tak sam nazw zawie-
raj ce trzy argumenty do wype nienia (rysunek 9.3).
Rysunek 9.3.
Okno Szukanie
wyniku
Argumenty oznaczaj kolejno:
Ustaw komórk  w tym polu mo liwe jest wy cznie
wstawiania adresu komórki, która musi by wype niona
formu ; jest to komórka, której wynik znamy.
Warto  miejsce, gdzie nale y wpisa , jak warto ma
przyj komórka okre lona w polu Ustaw komórk , mo e
to by zarówno liczba ujemna, jak i dodatnia.
Zmieniaj c komórk  pole, w którym nale y wstawi
komórk , która jest niewiadom rozwi zywanego
równania, np. komórka zawieraj ca zmienian mar .
Aby wykorzysta polecenie Szukaj wyniku, nale y:
1. Wstawi formu obliczaj c równanie, np. warto
zamówienia na okre lon liczb produktów przy za o onej
mar y, jak na rysunku 9.4.
2. Formu a powinna wygl da nast puj co: =C3*(1+C4)*C5.
270
Rozdzia 9. Zagadnienia optymalizacji danych
Rysunek 9.4.
Przyk adowe
dane finansowe
3. Przyjmuj c, e Warto zamówienia powinna osi gn
3000 z , obliczy , jaka powinna by narzucona Mar a.
4. Przej w zak adce Dane do listy rozwijanej Analiza
symulacji i wybra polecenie Szukaj wyniku.
5. W oknie Szukanie wyniku wpisa warto ci jak
na rysunku 9.5, co pozwoli osi gn za o on Warto
zamówienia.
Rysunek 9.5.
Warto ci
w oknie
Szukanie
wyniku
6. Po wyborze przycisku OK warto komórki C7 zostanie
ustawiona na 3000 z , a wysoko Mar y powinna
osi gn 19%.
7. Dodatkowo dost pne b dzie jeszcze okno Stan szukania
wyniku, dzi ki któremu mo na zaakceptowa b d
odwo a wyliczone warto ci.
8. Po naci ni ciu przycisku OK wyliczone warto ci zostan
zachowane w poszczególnych komórkach.
wiczenie 9.1.
Wykorzystuj c plik 9_1.xlsx, obliczy warto raty kredytu inwe-
stycyjnego, a nast pnie okre li , jak d ugi powinien by okres sp a-
cania kredytu, bior c pod uwag , e wysoko sp acanej raty nie
mo e przekroczy 1200 z miesi cznie.
271
Excel 2007 PL. Controlling, finanse i nie tylko
Plik 9_1.xlsx zawieraj cy podstaw do tego wiczenia jest
udost pniony wraz z innymi materia ami dotycz cymi
ksi ki pod adresem: ftp://ftp.helion.pl/przyklady/
ex27wf.zip.
Aby obliczy rat kredytu, a nast pnie wskaza , jak d ugo b dzie
on sp acany przy racie kapita owej w wysoko ci 1200 z , nale y:
1. W programie Microsoft Office Excel 2007 otworzy plik
9_1.xlsx.
2. W arkuszu Kredyt przej do komórki F13.
3. Wybra z wst ki zak adk Formu y, a nast pnie z listy
rozwijanej Finansowe wstawi funkcj PMT.
4. Okre li w oknie funkcji poszczególne argumenty,
jak na rysunku 9.6, wprowadzaj c znak minus
w argumencie Wa, aby warto raty by a liczb dodatni ,
i wcisn przycisk OK.
Rysunek 9.6. Argumenty funkcji PMT
5. Wyliczona rata jest zbyt wysoka, wi c z zak adki Dane
w sekcji Narz dzia danych wybra list Analiza symulacji,
a nast pnie polecenie Szukaj wyniku.
272
Rozdzia 9. Zagadnienia optymalizacji danych
6. W oknie Szukanie wyniku w polu Ustaw komórk
wpisa F13.
7. W polu Warto zdefiniowa wysoko raty na 1200,
a w polu Zmieniaj c komórk wpisa adres F10  okno
polecenia prezentuje rysunek 9.7  i wcisn OK.
Rysunek 9.7.
Wype nione
okno Szukanie
wyniku
8. Potwierdzi wprowadzone obliczenia kolejnym
przyciskiem OK.
9. Wynik wiczenia wskazuje na 113,5 rat, a wi c po 114
ratach (czyli po przesz o 9 latach) nast pi sp ata ca o ci
zaci gni tego kredytu inwestycyjnego.
Efekt wiczenia prezentuje rysunek 9.8. Wynik wiczenia zosta
zapisany w pliku o nazwie 9_1_wynik.xlsx.
Rysunek 9.8.
Wynik
wiczenia
273
Excel 2007 PL. Controlling, finanse i nie tylko
Analiza wra liwo ci
poprzez tabele danych
Analiza wra liwo ci jest jedn z podstawowych funkcjonalno ci
programu Microsoft Office Excel 2007, je li zacznie si rozpa-
trywa jego mo liwo ci analityczne, szczególnie w perspektywie
finansowej. Otó analiza wra liwo ci pozwala ustali próg ren-
towno ci inwestycji przy zmieniaj cych si czynnikach. Po pro-
stu mo liwe jest zaobserwowanie, w jaki sposób dane wyj ciowe
zawarte w arkuszu zostan zmienione w przypadku zmiany wska-
zanych danych wej ciowych umieszczonych w tym arkuszu.
Analiza wra liwo ci mo e by wykorzystywana we wszystkich
tych wyliczeniach, które maj wskaza , przy jakim poziomie
kosztu jednostkowego i ceny op acalne jest wej cie na rynek lub
przy jakim koszcie zmiennym warto prowadzi dzia alno . Ana-
liza wra liwo ci pozwala ustali jeden b d wiele czynników
zmiennych (o których szerzej w rozdziale 10.), np. przy jakim
oprocentowaniu i okresie sp aty rat najlepiej wzi kredyt in-
westycyjny.
W przypadku pracy z analiz wra liwo ci w programie Microsoft
Office Excel 2007 idealnym narz dziem jest polecenie Tabela
danych. Umo liwia ono tablicowanie wzorów matematycznych
wed ug wskazanego szablonu. Polecenie Tabela danych umiesz-
czone jest na wst ce w zak adce Dane i, podobnie jak w przy-
padku Szukaj wyniku, równie nale y je wybra z listy Analiza
symulacji.
Tabela danych umo liwia stablicowanie jednego wzoru do zazna-
czanego zakresu, mo e to by jedna lub wiele kolumn, wa ne jest
jednak, aby w lewej górnej komórce zaznaczonego zakresu poja-
wi a si formu a, która ma zosta stablicowana. Dodatkowo
w przypadku wpisywania formu y dla argumentów, które b d
zmienne w tabeli, nale y wykorzysta komórki zewn trzne w sto-
sunku do ca ej pó niej zaznaczonej tabeli. W momencie urucho-
mienia polecenia Tabela danych program wstawi po prostu do
274
Rozdzia 9. Zagadnienia optymalizacji danych
zaznaczonych komórek odpowiedni wynik formu y ze wskaza-
nymi argumentami, a eby móg on obliczy wynik, musi podsta-
wi zmienne argumenty do wolnych komórek.
Okno Tabela danych zawiera dwa pola:
Wierszowa komórka wej ciowa  odpowiada za warto ci
w tabeli danych umieszczone w pierwszym wierszu.
Kolumnowa komórka wej ciowa  odpowiada za warto ci
tabeli umieszczone w pierwszej kolumnie.
Aby wykorzysta polecenie Tabela danych dla prostego wyliczenia
tabliczki mno enia, nale y:
1. W programie Microsoft Office Excel 2007 zbudowa
kolumn oraz wiersz zawieraj ce komórki wype nione
kolejnymi liczbami od 1 do 10, co prezentuje rysunek 9.9.
Rysunek 9.9.
Podstawa
do wyliczenia
tabliczki
mno enia
2. Nast pnie na przeci ciu si kolumn i wierszy tabeli,
czyli w komórce A1, wstawi formu pozwalaj c
na wyliczenie poszczególnych wyników mno enia,
wykorzystuj c w tej formule komórki zewn trzne
w stosunku do tabeli danych.
3. Przyk adowa formu a mo e wygl da nast puj co: =A15*B15.
4. Zaznaczy ca y zakres, w którym ma zosta stablicowany
wzór mno enia, a wi c od A1 do K10.
5. Przej na wst ce do zak adki Dane i wybra z listy
rozwijanej Analiza symulacji polecenie Tabela danych.
275
Excel 2007 PL. Controlling, finanse i nie tylko
6. Wype ni okno Tabela danych warto ciami
jak na rysunku 9.10 i wcisn przycisk OK.
Rysunek 9.10.
Okno Tabela
danych
7. Program wyliczy wynik, który powinien wygl da
jak na rysunku 9.11.
Rysunek 9.11.
Wynik Tablicy
mno enia
Aby skorzysta z tablicowania wzorów w przypadku analizy wra -
liwo ci, cz sto wykorzystuje si wy cznie jedn zmienn (wiele
zmiennych to analiza wielowariantowa, o czym szerzej w roz-
dziale 10.). Analiza wra liwo ci mo e zosta zaprezentowana na
podstawie nast puj cego prostego przyk adu: firma sprzedaje
lizaki po 1 z za sztuk , ich koszt jednostkowy wynosi 50 gr. Popyt
kszta tuje si na poziomie 3000 sztuk, jednak cena jest równie
czynnikiem wp ywaj cym na jego wielko : otó popyt obni a si
o warto iloczynu 1000 sztuk i aktualnej ceny. Firma chce zoba-
czy , jaka cena dla takiego towaru b dzie najbardziej adekwatna
na rynku, zaczynaj c badanie od 50 gr, a ko cz c na 3 z (ze sko-
kiem o 25 gr). Aby wykorzysta tablicowanie wzorów w takiej
analizie wra liwo ci, nale y:
1. Zbudowa w a ciwy model dla wskazanych danych,
w nowym pliku programu Microsoft Office Excel 2007
wstawi w komórk B1 s owo Cena, w komórk B2 s owo
Koszt zmienny, w komórce B3 umie ci s owo Popyt,
a w komórce B5 Zysk.
276
Rozdzia 9. Zagadnienia optymalizacji danych
2. W komórce C1 umie ci cen 1 z , w komórce C2 koszt
jednostkowy 0,5 z .
3. W komórce C3 wprowadzi formu wyliczaj c wielko
popytu, tj. =3000 1000*C1.
4. W komórce C4 wyliczy wska nik zysku =C1*C3 C2*C3.
5. Przej do komórki B10 i wprowadzi liczb 0,5 z .
6. W nast pnej komórce wstawi liczb 0,75 z .
7. Zaznaczy obie liczby i przeci gn do do u
a do otrzymania wyniku 3 z .
8. W komórce C9 wprowadzi formu obliczaj c Zysk,
czyli ponowi formu z komórki C4, a wi c =C1*C3 C2*C3.
9. Arkusz programu powinien wygl da jak na rysunku 9.12.
Rysunek 9.12.
Analiza
wra liwo ci
ceny lizaka
10. Zaznaczy zakres komórek od B9 do C20.
11. Na wst ce wybra zak adk Dane, a nast pnie z listy
rozwijanej Analiza symulacji polecenie Tabela danych.
12. Poda wy cznie jedn zmienn dla kolumnowej komórki
wej ciowej  b dzie to komórka zawieraj ca w formule
cen , a wi c C1.
277
Excel 2007 PL. Controlling, finanse i nie tylko
13. Po klikni ciu przycisku OK program stablicuje wzór
zawarty w komórce C7, wstawiaj c do poszczególnych
komórek jako zmienn cen za sztuk lizaka.
14. Wyniki prezentuje rysunek 9.13.
Rysunek 9.13.
Analiza
wra liwo ci
ceny lizaka
15. Dok adnie wi c mo na okre li , i przy powy szych
za o eniach najbardziej po dan cen b dzie 1,75 z
za sztuk .
wiczenie 9.2.
Wykorzystuj c plik 9_2.xlsx wyliczy akceptowalny poziom kosztu
zmiennego umo liwiaj cy maksymalizacj miesi cznego zysku
z produkcji woreczków do lodów przy niezmiennych pozosta ych
warto ciach.
Plik 9_2.xlsx zawieraj cy podstaw do tego wiczenia jest
udost pniony wraz z innymi materia ami dotycz cymi ksi ki
pod adresem: ftp://ftp.helion.pl/przyklady/ex27wf.zip.
Aby dokona w a ciwego obliczenia poziomu kosztu, nale y:
1. Otworzy plik o nazwie 9_2.xlsx i przej do komórki C6.
2. Wstawi formu obliczaj c przychód ze sprzeda y, czyli
iloczyn ceny i popytu: =C4*C1.
278
Rozdzia 9. Zagadnienia optymalizacji danych
3. W komórce C8 umie ci formu wyliczaj c warto
zysku brutto, czyli przychodu pomniejszonego o koszty
zmienne, a wi c =C6 C4*C2.
4. W komórce C12 powinna si znale formu a wskazuj ca
na zysk operacyjny, czyli pomniejszenie zysku brutto
o koszty sta e, a wi c =C8 C10.
5. Natomiast w komórce C16 wpisa wyliczenie
uwzgl dniaj ce podatek do zap acenia, czyli formu
=C12 C12*C14.
6. Dla wyliczenia zysku wykorzystanego w tablicy wstawi
w komórce F3 formu wskazuj c na warto przychodu =C6.
7. W komórce G3 podobnie wskaza na zysk operacyjny
z komórki C12, a w komórce H3 na zysk netto umieszczony
w komórce C16.
8. W komórce I3 wstawi formu obliczaj c warto
ca kowit kosztów, a wi c =C10+C4*C2.
9. Wype niony arkusz powinien wygl da jak na rysunku 9.14.
Rysunek 9.14. Analiza wra liwo ci kosztów
279
Excel 2007 PL. Controlling, finanse i nie tylko
10. Zaznaczy zakres komórek od E4 do I24.
11. Na wst ce wybra zak adk Dane, a nast pnie z listy
rozwijanej Analiza symulacji polecenie Tabela danych.
12. Poda wy cznie jedn zmienn dla kolumnowej komórki
wej ciowej  b dzie to komórka zawieraj ca w formule
cen , a wi c C2.
13. Po klikni ciu przycisku OK program stablicuje wzór
zawarty w komórkach F3, G3, H3 oraz I3, wstawiaj c
do nich jako zmienn koszt jednostkowy.
14. W komórce H30 wstawi funkcj wyliczaj c maksymaln
warto z zakresu H4:H24  formu a wygl da nast puj co:
=max(H4:H24).
15. Wyniki wiczenia prezentuje rysunek 9.15.
Rysunek 9.15. Analiza wra liwo ci ceny lizaka
16. Maksymalny zysk przy okre lonych warunkach wynosi
wi c 11340 z , w zwi zku z tym optymalnym poziomem
kosztu zmiennego jest 1 z .
Wynik wiczenia zosta zapisany w pliku 9_2_wynik.xslx.
280
Rozdzia 9. Zagadnienia optymalizacji danych
Analiza wielowariantowa
z wykorzystaniem
scenariuszy
Kolejnym bardzo przydatnym elementem w programie Microsoft
Office Excel 2007 jest Mened er scenariuszy, który umo liwia
przeprowadzenie wielowariantowej analizy danych. Za pomoc
scenariuszy bardzo atwo mo na zbudowa kilka wariantów dzia-
alno ci firmy, np. optymistyczne przychody, zak adane przychody,
oraz sprawdza , jak przy zdefiniowanych argumentach b d nast -
powa y zmiany. Dzi ki scenariuszom w atwy sposób mo na za-
prezentowa kilka wariantów tej samej sytuacji, wybieraj c t
najbardziej realn i odpowiedni . Dla przyk adu mo na zbudowa
model prezentuj cy zarobki firmy ubezpieczeniowej w zale no ci
od liczby zatrudnionych agentów ubezpieczeniowych, zmieniaj c
w scenariuszach liczb agentów i sprawdzaj c, na jakim poziome
kszta towa si b d koszty i przychody.
Oczywi cie aby dobrze wykorzysta funkcjonalno scenariuszy,
najwa niejsze jest zbudowanie w a ciwego modelu do analizy.
Dopiero po tym podaje si , które komórki wyliczaj ce warto ci
w modelu b d zmiennymi dla poszczególnych scenariuszy. Roz-
wi zanie to jest rozwi zaniem odwrotnym do polecenia Szukaj
wyniku  tam wiadomo by o, jaki jest ostateczny wynik, nale-
a o wyliczy wy cznie warto argumentu, który na ten wynik
wp ywa, w przypadku scenariuszy znane s argumenty umo li-
wiaj ce obliczenie wyniku.
Po zbudowaniu modelu mo na przyst pi do tworzenia scenariu-
szy. Polecenie Mened er scenariuszy umieszczone jest na wst ce
w zak adce Dane jako ostatnie na li cie Analiza symulacji. Po
wyborze polecenia otwarte zostanie okno o nazwie Mened er sce-
nariuszy, w którym mo liwe jest zarz dzanie wszystkimi dost p-
nymi scenariuszami. Wybranie przycisku Dodaj& spowoduje
otwarcie nowego okna o nazwie Dodawanie scenariusza (rysunek
281
Excel 2007 PL. Controlling, finanse i nie tylko
9.16), w którym wpisuje si nazw scenariusza oraz zaznacza ko-
mórki zmieniane, po czym okre la warto ci zaznaczonych komórek
dla dodawanego scenariusza.
Rysunek 9.16. Okna Dodawanie scenariusza
W celu zaznaczenia kilku komórek le cych w ró nych
miejscach arkusza mo na wykorzysta umieszczony
na klawiaturze przycisk Ctrl w po czeniu z lewym
przyciskiem myszy. Po prostu po wyborze pierwszej
komórki wcisn klawisz Ctrl i kolejno lewym przyciskiem
myszy dodawa zaznaczenie.
Kiedy zostan dodane wszystkie scenariusze, mo na je pojedyn-
czo wy wietla , wykorzystuj c do tego celu przycisk Poka , jak
i doda za pomoc przycisku Podsumowanie& nowy arkusz z pod-
sumowaniem wszystkich dost pnych scenariuszy, warto ciami
poszczególnych komórek oraz wynikami komórek docelowych.
Podsumowanie mo e mie format tabeli przestawnej b d zwy-
k ego podsumowania danych.
Aby doda scenariusze pozwalaj ce wyliczy zwrot z inwestycji
w lokat terminow (z kapita em pocz tkowym 1000 z , na 6,5%
rocznie) przy okresie oszcz dzania pi lat i przy ró nych warto-
ciach wp acanych rat (50, 100, 500, 1000 z ), nale y:
282
Rozdzia 9. Zagadnienia optymalizacji danych
1. W programie Microsoft Office Excel 2007 zbudowa
w a ciwy model, np. w komórce B1 wstawi 1000 z ,
w komórce B3 wpisa 6,5%, w B5 wprowadzi formu
=5*12, a w B7 umie ci warto podstawow raty,
czyli 100 z .
2. W komórce B9 wstawi funkcj FV, wykorzystuj c
zak adk Formu y i list rozwijan Finansowe.
3. Okre li poszczególne argumenty funkcji (dzi ki znakom
minus dla Raty i Wa wy wietlony zostanie dodatni wynik
funkcji) jak na rysunku 9.17 i wcisn OK.
Rysunek 9.17.
Argumenty
funkcji FV
4. Przej do zak adki Dane i z listy rozwijanej Analiza
symulacji wskaza polecenie Mened er scenariuszy,
a nast pnie wybra przycisk Dodaj& .
5. W oknie Dodawanie scenariusza w polu Nazwa
scenariusza wpisa Podstawowy, a w komórki zmieniane
wstawi B7 i klikn przycisk OK.
6. W oknie Warto ci scenariusza nie zmienia warto ci,
pierwszy scenariusz jest podstaw .
7. Doda kolejny scenariusz za pomoc przycisku Dodaj.
8. Nada scenariuszowi nazw Pesymistyczny i nie zmieniaj c
adresu komórki, potwierdzi przyciskiem OK.
9. W oknie Warto ci scenariusza wprowadzi warto 50.
283
Excel 2007 PL. Controlling, finanse i nie tylko
10. Doda dwa kolejne scenariusze o nazwie Ostro ny
i Optymistyczny oraz odpowiadaj ce im warto ci
 500 i 1000.
11. Po wprowadzeniu ostatniego scenariusza potwierdzi
przyciskiem OK.
12. Okno Mened er scenariuszy powinno wygl da jak
na rysunku 9.18.
Rysunek 9.18.
Okno Mened er
scenariuszy
13. Mo liwe jest teraz przegl danie wyników oblicze funkcji
FV dla poszczególnych scenariuszy, wystarczy zaznaczy
odpowiedni scenariusz i wybra przycisk Poka .
14. Ostatnim krokiem b dzie zbudowanie podsumowania.
15. Po wybraniu przycisku Podsumowanie& otwarte zostanie
okno Podsumowanie scenariuszy, w którym nale y
zaznaczy opcje jak na rysunku 9.19 i wcisn przycisk OK.
Rysunek 9.19.
Wybór sposobu
wstawienia
podsumowania
284
Rozdzia 9. Zagadnienia optymalizacji danych
16. Zostanie dodany nowy arkusz zawieraj cy poszczególne
warto ci komórek zmienianych oraz wynik funkcji FV,
co prezentuje rysunek 9.20.
Rysunek 9.20. Arkusz Podsumowanie scenariuszy
Warto zauwa y , i w podsumowaniu scenariuszy zarówno
w kolumnach, jak i w wierszach dost pny jest przycisk rozwija-
j cy i zwijaj cy zgrupowane informacje.
wiczenie 9.3.
Wykorzystuj c plik 9_3.xlsx, oblicz zysk netto, dodaj cztery scena-
riusze zawieraj ce wskazane w arkuszu warto ci kosztów oraz cen
jednostkowych, a nast pnie dodaj podsumowanie scenariuszy.
Plik 9_3.xlsx zawieraj cy podstaw do tego wiczenia jest
udost pniony wraz z innymi materia ami dotycz cymi ksi ki
pod adresem: ftp://ftp.helion.pl/przyklady/ex27wf.zip.
Aby obliczy zysk i doda scenariusze, nale y:
1. W pliku 9_3.xlsx w arkuszu Zysk netto umie ci aktywn
komórk w C10.
2. Zbudowa formu wyliczaj c Przychody ze sprzeda y,
a wi c iloczyn ceny i ilo ci sprzedanych towarów. Formu a
wygl da nast puj co: =C5*C8.
285
Excel 2007 PL. Controlling, finanse i nie tylko
3. Przej do komórki C12 i obliczy Zysk b d strat brutto,
które s ró nic przychodów ze sprzeda y i kosztów
zmiennych, a wi c =C10 C6*C8.
4. W komórce C16 wstawi formu wyliczaj c Zysk
operacyjny, a wi c ró nic pomi dzy zyskiem (strat )
brutto a kosztami operacyjnymi. Formu a wygl da
nast puj co: =C12 C14.
5. Przej do komórki C20 i zbudowa formu obliczaj c
Zysk netto przed opodatkowaniem, czyli ró nic zysku
operacyjnego i odsetek kredytu =C16 C18.
6. W komórce C24 wstawi ostatni formu wyliczaj c
Zysk netto, a wi c ró nic pomi dzy zyskiem netto przed
opodatkowaniem a wyliczon warto ci podatku  nale y
jednak pami ta , i podatek p acony jest wy cznie wtedy,
kiedy firma osi ga zysk. Formu a powinna zatem bra
pod uwag warto ci ujemne, wygl da wi c nast puj co:
=JE ELI(C20<0;C20;C20 C20*C22).
7. Na wst ce z zak adki Dane wybra z listy Analiza
symulacji polecenie Mened er scenariuszy.
8. W oknie Mened era scenariuszy wcisn przycisk Dodaj&
i wpisa nazw dla pierwszego scenariusza: Podstawowy oraz
okre li komórki zmieniane: C5 i C6. Okno Edytowanie
scenariusza prezentuje rysunek 9.21.
Rysunek 9.21.
Okno
Edytowanie
scenariusza
286
Rozdzia 9. Zagadnienia optymalizacji danych
9. W oknie Warto ci scenariusza pozostawi niezmienione
dane, a nast pnie za pomoc przycisku Dodaj przej
do wstawiania kolejnego scenariusza.
10. Wstawi trzy kolejne scenariusze zaprezentowane
w arkuszu Scenariusze, a wi c:
Optymistyczny C5=22,5; C6=15,2,
Pesymistyczny C5=17,2; C6=16,
Oczekiwany C5=21,2; C6=18,2.
11. Po wpisaniu ostatniego scenariusza wcisn przycisk OK.
Okno Mened era scenariuszy powinno wygl da jak
na rysunku 9.22.
Rysunek 9.22.
Wstawione
wszystkie
scenariusze
12. Z okna Mened er scenariuszy wybra przycisk
Podsumowanie& .
13. Zaznaczy jako Typ raportu  Podsumowanie scenariuszy
oraz wskaza jako komórk wynikow C24 i wcisn
przycisk OK.
14. W nowym arkuszu zostanie wstawione podsumowanie,
co prezentuje rysunek 9.23.
Wynik wiczenia zapisany zosta w pliku 9_3_wynik.xlsx.
287
Excel 2007 PL. Controlling, finanse i nie tylko
Rysunek 9.23. Podsumowanie scenariuszy
Optymalizacja danych
przy wielu niewiadomych
Na pocz tku rozdzia u zosta a zaprezentowana mo liwo oblicza-
nia równa finansowych z jedn niewiadom . Bardzo cz sto jed-
nak w tak dynamicznie zmieniaj cym si rodowisku jak finanse
firmy nieznanych jest du o wi cej elementów. Pomocnym narz -
dziem w analizie finansowej, umo liwiaj cym maksymalizacj
wyniku finansowego lub minimalizacj kosztów prowadzenia
dzia alno ci, jest Solver. Dodatek Solver pozwala na znalezienie
najlepszego rozwi zania dla wskazanych komórek  optymali-
zuje cel przy okre lonych warunkach ograniczaj cych. Narz dzie
to nale y doda do programu Microsoft Office Excel 2007 w spo-
sób, jaki zosta zaprezentowany na pocz tku rozdzia u. Po w -
czeniu dodatku Solver jest on dost pny na wst ce w zak adce
Dane w sekcji Analiza.
Podczas pracy z poleceniem Solver najwa niejsze jest zbudowa-
nie z posiadanych informacji w a ciwego modelu, dopiero potem
mo na wywo a okno Solver (rysunek 9.24).
W oknie najwa niejsze s trzy elementy:
komórka docelowa  komórka, której warto ci maj zosta
zoptymalizowane do okre lonego celu (maksymalizacji,
minimalizacji lub wskazanej warto ci),
288
Rozdzia 9. Zagadnienia optymalizacji danych
Rysunek 9.24.
Okno polecenia
Solver
komórki zmieniane  komórki, które w modelu
s niewiadomymi, jakie program musi rozwi za ,
ograniczenia  na o one w modelu ograniczenia.
Dodatkowe ustawienia mo na wywo a , wybieraj c przycisk Opcje
otwieraj cy kolejne okno (widoczne na rysunku 9.25).
Rysunek 9.25.
Okno Opcje
Najwa niejsze ustawienia w tym oknie pozwalaj na dok ad-
ne okre lenie metod wyliczania wyniku. Poszczególne opcje
oznaczaj :
Maksymalny czas  liczba sekund, w czasie których
Solver musi odnale rozwi zanie; maksymalna warto ,
któr mo na wprowadzi , to 32767.
Liczba iteracji  liczba po rednich oblicze ; mniejsza
warto skraca czas obliczania; maksymalna warto
to równie 32767.
289
Excel 2007 PL. Controlling, finanse i nie tylko
Dok adno  warto wskazuj ca na dok adno
tolerancji dla komórki ograniczenia; warto ci z zakresu
od 0 do 1; dok adno tym wi ksza, im wi cej miejsc
dziesi tnych.
Tolerancja  warto wskazuj ca w procentach
akceptowalne odchylenie od warto ci optymalnej dla
komórki docelowej spe niaj cej ograniczenia ca kowite;
zastosowanie wy cznie dla zada z ograniczeniami
ca kowitymi.
Przyjmij model liniowy  opcja przyspieszaj ca
wyszukiwanie rozwi zania w przypadku optymalizacji
liniowej.
Przyjmij nieujemne  komórki zmieniane bez okre lonej
dolnej granicy w ograniczeniach przyjmuj minimaln
warto równ zero.
Automatyczne skalowanie  opcja umo liwiaj ca
skalowanie, je li mi dzy danymi i wynikami jest du a
ró nica warto ci.
Poka wynik iteracji  zaznaczenie tej opcji pozwala
na zatrzymanie poszukiwania rozwi zania i wy wietlenie
ka dej iteracji (ka dego mo liwego rozwi zania).
Po rozwi zaniu zadania pojawia si okno Solver  Wyniki (rysunek
9.26), w którym mo liwe jest pozostawienie wyliczonego rozwi -
zania lub przywrócenie warto ci pocz tkowych. Dodatkowo wy-
liczony wynik po klikni ciu przycisku Zapisz scenariusz& mo e
zosta zapisany w Mened erze scenariuszy do pó niejszego wy-
korzystania.
Rysunek 9.26.
Okno wyników
290
Rozdzia 9. Zagadnienia optymalizacji danych
Ostatnim elementem dost pnym w tym oknie s raporty. Zostan
one dodane w nowych arkuszach, je li ich nazwy b d pod wie-
tlone. Dost pne s trzy typy raportów:
Wyników  prezentuje informacje o warto ciach
oryginalnych i ko cowych dla komórek zmienianych,
wskazuje, jakie zosta y na o one ograniczenia oraz jak
warto przyjmuje komórka celu.
Wra liwo ci  informacje wskazuj ce na czu o
rozwi zania zawartego w komórce celu oraz na niewielkie
zmiany w ograniczeniach.
Granic  zawiera informacje o komórkach docelowych
oraz zmienianych wraz z warto ciami ko cowymi, a tak e
prezentuje doln i górn granic , któr mog przyj
poszczególne komórki zmieniane.
W przypadku na o enia warunku okre laj cego wynik jako
liczby ca kowite Solver wy wietli wy cznie raport wyników.
Przyk adowe zastosowanie polecenia Solver mo na pokaza na
nast puj cym zadaniu.
Firma produkuje cztery rodzaje zabawek: klocki, samochody,
pistolety na wod i telefony. W fabryce zatrudnionych jest dwu-
dziestu pracowników, którzy do wykorzystania maj cztery ma-
szyny. Zabawki sk adaj si z okre lonej liczby takich samych
cz ci. Cena, jednostkowy czas pracy osób, jednostkowy czas pracy
maszyn oraz wykorzystywane cz ci poszczególnych zabawek
kszta tuj si na poziomie:
Jednostkowy Jednostkowy czas Wykorzystywane
Cena
czas pracy osób pracy maszyn cz ci
samochód 13 z 0,5 0,1 5
telefon 20 z 1 0,24 15
klocki 17 z 0,7 0,055 10
pistolet 10 z 0,2 0,14 7
291


Wyszukiwarka