:V]HONLHSUDZD]DVWU]HĪRQH1LHDXWRU\]RZDQHUR]SRZV]HFKQLDQLHFDáRĞFL
OXEIUDJPHQWXQLQLHMV]HMSXEOLNDFMLZMDNLHMNROZLHNSRVWDFLMHVW]DEURQLRQH
:\NRQ\ZDQLHNRSLLPHWRGąNVHURJUDILF]QąIRWRJUDILF]QąDWDNĪHNRSLRZDQLH
NVLąĪNLQDQRĞQLNXILOPRZ\PPDJQHW\F]Q\POXELQQ\PSRZRGXMHQDUXV]HQLH
SUDZDXWRUVNLFKQLQLHMV]HMSXEOLNDFML

:V]\VWNLH]QDNLZ\VWĊSXMąFHZWHNĞFLHVą]DVWU]HĪRQ\PL]QDNDPLILUPRZ\PL
EąGĨWRZDURZ\PLLFKZáDĞFLFLHOL

$XWRURUD]:\GDZQLFWZR+(/,21GRáRĪ\OLZV]HONLFKVWDUDĔE\]DZDUWH
ZWHMNVLąĪFHLQIRUPDFMHE\á\NRPSOHWQHLU]HWHOQH1LHELRUąMHGQDNĪDGQHM
RGSRZLHG]LDOQRĞFLDQL]DLFKZ\NRU]\VWDQLHDQL]D]ZLą]DQH]W\PHZHQWXDOQH
QDUXV]HQLHSUDZSDWHQWRZ\FKOXEDXWRUVNLFK$XWRURUD]:\GDZQLFWZR+(/,21
QLHSRQRV]ąUyZQLHĪĪDGQHMRGSRZLHG]LDOQRĞFL]DHZHQWXDOQHV]NRG\Z\QLNáH
]Z\NRU]\VWDQLDLQIRUPDFML]DZDUW\FKZNVLąĪFH

5HGDNWRUSURZDG]ąF\%DUEDUD*DQFDU]:yMFLFND
3URMHNWRNáDGNL-DQ3DOXFK

)RWRJUDILDQDRNáDGFH]RVWDáDZ\NRU]\VWDQD]D]JRGą6KXWWHUVWRFN

:\GDZQLFWZR+(/,21
XO.RĞFLXV]NLF*/,:,&(
WHO
HPDLORQHSUHVV#RQHSUHVVSO
:::KWWSRQHSUHVVSONVLĊJDUQLDLQWHUQHWRZDNDWDORJNVLąĪHN

'URJL&]\WHOQLNX
-HĪHOLFKFHV]RFHQLüWĊNVLąĪNĊ]DMU]\MSRGDGUHV
KWWSRQHSUHVVSOXVHURSLQLH]DH[DQ
0RĪHV]WDPZSLVDüVZRMHXZDJLVSRVWU]HĪHQLDUHFHQ]MĊ

3OLNL]SU]\NáDGDPLRPDZLDQ\PLZNVLąĪFHPRĪQD]QDOHĨüSRGDGUHVHP
IWSIWSKHOLRQSOSU]\NODG\]DH[DQ]LS

,6%1

&RS\ULJKW‹+HOLRQ

3ULQWHGLQ3RODQG

•

Kup książkę

•

Poleć książkę

•

Oceń książkę

•

Księgarnia internetowa

•

Lubię to! » Nasza społeczność

SPIS TRE!CI

WST P

9

1.

BUD!ET FIRMY HANDLOWEJ
— USTALANIE WARUNKÓW BONUSÓW DLA KLIENTÓW

11

1.1. Opis zagadnienia

11

1.2. Model sprzeda!y i wyników bud!etu

12

1.3. Zadanie analityka

13

1.4. Tabela danych jako narz"dzie analizy symulacji

14

2.

MIESI CZNE PREMIE DLA SPRZEDAWCÓW

21

2.1. Opis zagadnienia

21

2.2. Microsoft Query — pobieranie danych zewn"trznych

23

2.3. Niestandardowe pozycje obliczeniowe tabeli przestawnej

— element obliczeniowy

30

2.4. Modyfikowanie/usuwanie elementów obliczeniowych

oraz wy$wietlanie listy utworzonych elementów

33

2.5. Od$wie!anie danych w tabeli oraz modyfikacja kwerendy

w Microsoft Query

33

3.

STOSOWANIE TABEL PRZESTAWNYCH DO ANALIZY
DU!YCH ZBIORÓW DANYCH SPRZEDA!OWYCH

35

3.1. Opis zagadnienia

35

3.2. Struktura danych wykorzystywanych w tabeli przestawnej

36

3.3. Tworzenie tabeli przestawnej. Dodawanie, usuwanie i przenoszenie pól

37

3.4. Modyfikacja pól tabeli przestawnej

39

3.5. Zastosowanie filtru raportu tabeli przestawnej

43

3.6. Wy$wietlanie stron filtru raportu jako arkuszy

45

Kup ksiąĪkĊ

Pole

ü ksiąĪkĊ

4

| Zastosowanie Excela w pracy analityka finansowego, specjalisty ds. controllingu i analityka sprzeda!y

4.

TYGODNIOWY RAPORT MAR!Y BRUTTO
NA TOPOWYCH PRODUKTACH (TOP30) — TABELE

47

4.1. Opis zagadnienia

47

4.2. Wyznaczenie numeru tygodnia w zestawieniu faktur sprzeda!y

48

4.3. Przypisanie produktom kosztu materia%owego

50

4.4. Budowa tabeli przestawnej obrazuj&cej sprzedan& ilo$'

poszczególnych produktów w ka!dym tygodniu

51

4.5. Niestandardowe pozycje obliczeniowe tabeli przestawnej

— pole obliczeniowe

52

5.

TYGODNIOWY RAPORT MAR!Y BRUTTO NA TOPOWYCH
PRODUKTACH (TOP30) — GRAFICZNA PREZENTACJA

59

5.1. Opis zagadnienia

59

5.2. Sprawdzanie poprawno$ci danych — lista rozwijalna

60

5.3. Funkcja WE(DANETABELI

63

5.4. Wykres ko%owy — struktura sprzeda!y (ilo$ci) w wybranym

przez u!ytkownika tygodniu

68

5.5. Wykres skumulowany kolumnowy — porównanie wybranej

wielko$ci z czterech tygodni dla ka!dego z produktów

70

6.

KONSOLIDACJA TYGODNIOWYCH
FORECASTÓW SPRZEDA!Y

73

6.1. Opis zagadnienia

73

6.2. Pliki do planowania sprzeda!y

73

6.3. Konsolidacja danych

74

6.4. Konsolidacja bez %&czy ze +ród%em danych

79

6.5. Zamiana plików z danymi +ród%owymi

80

6.6. Edycja %&czy w zestawieniu podsumowuj&cym

80

7.

WIEKOWANIE NALE!NO$CI I ZOBOWI%ZA&

83

7.1. Opis zagadnienia

83

7.2. Przygotowywanie zestawienia

84

7.3. Funkcja WYSZUKAJ.PIONOWO

86

7.4. Wiekowanie przy u!yciu tabeli przestawnej

89

Kup ksiąĪkĊ

Pole

ü ksiąĪkĊ

S P I S T R E " C I |

5

8. LISTY WINDYKACYJNE — KORESPONDENCJA SERYJNA 93

8.1. Opis zagadnienia

93

8.2. Baza klientów i szablon pisma

93

8.3. Podpi"cie bazy do szablonu i drukowanie listów

95

8.4. Drukowanie kopert

100

9.

ANALIZA I PROGNOZOWANIE SPRZEDA!Y SEZONOWEJ 105

9.1. Opis zagadnienia

105

9.2. Budowa modelu

106

9.3. Trend liniowy i funkcja REGLINX

107

10. PRZYSZ'E WP'YWY I WYDATKI

115

10.1. Opis zagadnienia

115

10.2. Miesi&c p%atno$ci — funkcja MIESI-C

117

10.3. Tydzie: p%atno$ci — funkcja WEEKNUM

118

10.4. Rodzaj wp%ywów — funkcja JE;ELI

118

10.5. Przysz%e wp%ywy — tabela przestawna

121

11. MACIERZ KOSZTÓW TRANSPORTU

PRZEWO*NIK – KLIENT

125

11.1. Opis zagadnienia

125

11.2. Budowa macierzy — tabela przestawna

126

11.3. Funkcja JE;ELI.B<-D

129

11.4. Wyró!nienie warto$ci — formatowanie warunkowe

130

12. GRAFICZNA PREZENTACJA SPRZEDA!Y

WED'UG WOJEWÓDZTW

133

12.1. Opis zagadnienia

133

12.2. Dane sprzeda!owe — tabela przestawna

134

12.3. Wybór miesi&ca — lista rozwijalna

134

12.4. Pobieranie danych z tabeli — funkcja WE(DANETABELI

139

12.5. Graficzna prezentacja

140

13. GRAFICZNA PREZENTACJA SK'ADOWYCH WYNIKU

BRUTTO NA SPRZEDA!Y

145

13.1. Opis zagadnienia

145

13.2. Okre$lenie granic kolumn

146

Kup ksiąĪkĊ

Pole

ü ksiąĪkĊ

6

| Zastosowanie Excela w pracy analityka finansowego, specjalisty ds. controllingu i analityka sprzeda!y

13.3. Wykres skumulowany kolumnowy

148

13.4. Dodanie warto$ci poszczególnych kolumn wykresu — pola tekstowe

151

14. MODEL DO WYZNACZANIA PROCENTU UPUSTU

PRZY ZACHOWANYM POZIOMIE PROCENTU
MAR!Y ZAMÓWIENIA

153

14.1. Opis zagadnienia

153

14.2. Model

153

14.3. Szukaj wyniku

157

15. TWORZENIE W'ASNYCH FUNKCJI EXCELA

163

15.1. Opis zagadnienia

163

15.2. Tworzenie niestandardowych funkcji

163

15.3. Argumenty opcjonalne

165

15.4. Tworzenie opisu funkcji i definiowanie kategorii

166

15.5. Tworzenie dodatku z funkcjami

167

Kup ksiąĪkĊ

Pole

ü ksiąĪkĊ

Rozdzia0 7.

WIEKOWANIE NALE-NO!CI

I ZOBOWI$ZA%

CEL: STWORZENIE NARZ%DZIA SJU@HCEGO DO WIEKOWANIA NALE@NO"CI.
NARZ)DZIA:

1. FUNKCJA WYSZUKAJ.PIONOWO
2. TABELA PRZESTAWNA

7.1. OPIS ZAGADNIENIA

Przewa!nie systemy finansowo-ksi"gowe, z których korzysta firma, posiadaj&
narz"dzie s%u!&ce do wiekowania nale!no$ci i zobowi&za: w okre$lonych przez
u!ytkownika przedzia%ach czasu. Osoba tworz&ca raport w systemie podaje jedy-
nie dzie:, na który ma by' przyj"ty stan nale!no$ci lub zobowi&za:, oraz inter-
wa%y czasowe przeterminowanych nale!no$ci (zobowi&za:), w jakich ma zosta'
przedstawione zestawienie. Dodatkowo zestawienie tworzone jest w podziale
na odbiorc" w przypadku nale!no$ci oraz na dostawc" w przypadku zobowi&-
za:. Co jednak, je!eli analityk pracuje na systemie, w którym nie ma takiego
narz"dzia, a co poniedzia%ek z samego rana musi tworzy' wiekowanie nale!-
no$ci? W jaki sposób ma sobie zautomatyzowa' prac", tak aby nie traci' niepo-
trzebnie cennego czasu, a powtarzaj&c& si" cz"$' pracy zast&pi' automatycznym
narz"dziem? Dodatkowo gotowe raporty w systemie pokazuj& cz"sto jedynie
kwoty nale!no$ci poszczególnych klientów. Pojawia si" problem, je!eli chcemy
wiedzie', jakie dokumenty sk%adaj& si" na dan& kwot". Nale!y wtedy szuka'
odpowiednich pozycji w kartotekach odbiorców czy te! na kontach analitycz-
nych nale!no$ci. W tym rozdziale przedstawi" sposób tworzenia narz"dzia dla
naszego analityka, które umo!liwi mu usprawnienie i przyspieszenie tworzenia
raportu wiekowania nale!no$ci, jak równie! pozwoli na sprawne dotarcie do
poszczególnych dokumentów sk%adaj&cych si" na interesuj&c& go kwot".

Zak%adamy, i! nasz pracownik dzia%u analiz nie posiada narz"dzia systemo-

wego do tworzenia raportu wiekowania nale!no$ci. Jedyne, co mo!e otrzyma'
z systemu, to zestawienie faktur niezap%aconych przez klientów (rysunek 7.1).

Takie zestawienie zawiera nast"puj&ce informacje: nr faktury, nazw" kontra-

henta, dat" dokumentu, termin p%atno$ci oraz niezap%acon& kwot". Tak niewiele

Kup ksiąĪkĊ

Pole

ü ksiąĪkĊ

8 4

| Zastosowanie Excela w pracy analityka finansowego, specjalisty ds. controllingu i analityka sprzeda!y

Rysunek 7.1. Zestawienie niezap%aconych faktur

informacji „wyci&gni"tych” z systemu pozwoli naszemu analitykowi na stwo-
rzenie zestawienia pokazuj&cego wiekowanie nale!no$ci w podziale na danego
odbiorc" z dok%adno$ci& co do dokumentu. Wykorzystamy do tego celu tabele
przestawne oraz funkcj"

WYSZUKAJ.PIONOWO

.

7.2. PRZYGOTOWYWANIE ZESTAWIENIA

Pierwszym krokiem b"dzie okre$lenie dla ka!dego z dokumentów liczby dni
od podanej przez analityka daty oraz terminu p%atno$ci. Ujemna liczba b"dzie
oznacza%a, !e dana faktura jest bie!&ca, natomiast dodatnia liczba — !e dana
kwota z dokumentu jest przeterminowan& nale!no$ci&. W komórce I1 wpiszmy
dat"

2011-08-01

— b"dzie to dzie:, na który wykonane zostanie wiekowanie

nale!no$ci. Kolumn" F nazwijmy

liczba dni

. Nast"pnie przyciskiem F2 wstawmy

formu%", która b"dzie od podanej w I1 daty odejmowa%a termin p%atno$ci dla
ka!dego z dokumentów (rysunek 7.2). Uwaga: nale!y pami"ta' o zablokowaniu
komórki zawieraj&cej podan& dat". Formu%" nale!y skopiowa' do samego ko:ca
zestawienia.

Kolejnym krokiem jest okre$lenie przedzia%ów czasowych wyra!onych

w dniach, wed%ug których b"d& klasyfikowane nale!no$ci. W naszym przypadku
b"d& to nale!no$ci bie!&ce oraz przeterminowane w przedzia%ach: 1 – 14, 15 – 30,
31 – 45, 46 – 60, 61 – 90, 91 – 180 i powy!ej 180 dni. W zestawieniu budujemy
dodatkow& tablic" z przedzia%ami, jak na rysunku 7.3.

Kup ksiąĪkĊ

Pole

ü ksiąĪkĊ

W I E K O W A N I E N A L E @ N O " C I I Z O B O W I H Z A & |

8 5

Rysunek 7.2. Formu%a wyliczaj$ca liczb& dni przeterminowania ka dego z dokumentów

Rysunek 7.3. Tablica z przedzia%ami

Tablica sk%ada si" z dwóch kolumn. W pierwszej wstawiona jest dolna granica

danego zakresu, a w drugiej — opis zakresu. Dla przedzia%u o nazwie bie $ce
wstawiona jest bardzo du!a liczba ujemna, !eby ka!da liczba dni poni!ej 0 uzna-
wana by%a za nale!no$' bie!&c&. Do naszego zbioru danych dla wiekowania
nale!no$ci dodajemy kolejn& kolumn" o nazwie przedzia%y (rysunek 7.4).
W nowo dodanej kolumnie dla ka!dego z dokumentów zostanie okre$lony prze-
dzia% na podstawie wyliczonej liczby dni. To zadanie zostanie wykonane przy
u!yciu funkcji

WYSZUKAJ.PIONOWO

.

Rysunek 7.4. Dodanie kolumny przedzia%y

Kup ksiąĪkĊ

Pole

ü ksiąĪkĊ

8 6

| Zastosowanie Excela w pracy analityka finansowego, specjalisty ds. controllingu i analityka sprzeda!y

7.3. FUNKCJA WYSZUKAJ.PIONOWO

Funkcja

WYSZUKAJ.PIONOWO

s%u!y do wyszukiwania i dopasowywania do siebie

danych znajduj&cych si" w dwóch osobnych tabelach. Idealnie nada si" do
naszego zadania, czyli przyporz&dkowania ka!demu z dokumentów na pod-
stawie warto$ci w dniach odpowiedniego przedzia%u wiekowania.

Sk%adnia funkcji to:

= WYSZUKAJ.PIONOWO(szukana_warto$%;tablica;nr_kolumny;kolumna)

Argumenty funkcji to:

!

szukana_warto$%

— warto$' (lub odwo%anie), która ma zosta' odszukana

w pierwszej kolumnie tablicy. Krótko mówi&c, ten argument okre$la, czego
szukamy.

!

tablica

— wskazuje, gdzie szukamy interesuj&cych nas danych. W pierwszej

kolumnie tabeli musz& znajdowa' si" warto$ci, po których wyszukujemy,
czyli takie same jak w argumencie

szukana_warto$%

.

!

nr_kolumny

— numer kolumny, z której ma zosta' zwrócona warto$' z tabeli

przeszukiwanej.

!

kolumna

— warto$' logiczna (

0

lub

1

), która okre$la, czy funkcja ma znale+'

dopasowanie dok%adne (

0

), czy przybli!one (

1

).

W kolumnie przedzia%y dla pierwszej faktury wstawiamy nasz& funkcj", prze-

chodz&c na wst&!k" Formu%y/Biblioteka funkcji/Wyszuk.i odwo%./WYSZUKAJ.
PIONOWO (rysunek 7.5).

Nast"pnie poszczególne argumenty funkcji wype%niamy tak jak na rysunku 7.6.

1. Argument Szukana_warto"# — wskazujemy liczb" dni dla pierwszego doku-

mentu.

2. Argument Tabela_tablica — wskazujemy nasz& tabel" z przedzia%ami. Nale!y

zablokowa' jej adres, aby nie zmienia%a si" ona przy kopiowaniu formu%y
na reszt" pozycji.

3. Argument Nr_indeksu_kolumny — wpisujemy cyfr"

2

, czyli dla danych

w dniach pobierzemy dan& nazw" przedzia%u.

4. Argument Przeszukiwany_zakres — podajemy

1

(warto$' logiczn& PRA-

WDA), tak aby w przypadku nieznalezienia dok%adnej szukanej warto$ci

Kup ksiąĪkĊ

Pole

ü ksiąĪkĊ

W I E K O W A N I E N A L E @ N O " C I I Z O B O W I H Z A & |

8 7

Rysunek 7.5. Lokalizacja funkcji WYSZUKAJ.PIONOWO

Rysunek 7.6. Argumenty funkcji WYSZUKAJ.PIONOWO

zwróci% nam przybli!ony wynik z tabeli z przedzia%ami. Je!eli podaliby$my

0

(warto$' logiczn& FA'SZ), funkcja dla nieznalezionych warto$ci zwróci%aby
b%&d

#N/D

.

Wynikiem dzia%ania funkcji

WYSZUKAJ.PIONOWO

dla pierwszej liczby dni rów-

nej 386 (komórka F2) b"dzie przedzia% o nazwie przeterminowane powy ej 180
(rysunek 7.7). Funkcj" kopiujemy do ko:ca naszego zestawienia.

Kup ksiąĪkĊ

Pole

ü ksiąĪkĊ

8 8

| Zastosowanie Excela w pracy analityka finansowego, specjalisty ds. controllingu i analityka sprzeda!y

Rysunek 7.7. Wynik dzia%ania funkcji WYSZUKAJ.PIONOWO

W ten oto sposób otrzymali$my ko:cowe zestawienie faktur wraz z nie-

zb"dnymi informacjami do stworzenia zestawienia wiekowania nale!no$ci
(rysunek 7.8).

Rysunek 7.8. Ko(cowe zestawienie do wiekowania nale no"ci

Kup ksiąĪkĊ

Pole

ü ksiąĪkĊ

W I E K O W A N I E N A L E @ N O " C I I Z O B O W I H Z A & |

8 9

7.4. WIEKOWANIE PRZY U@YCIU

TABELI PRZESTAWNEJ

Najszybszym sposobem stworzenia zestawienia wiekowania z otrzymanego
zbioru danych dotycz&cych nale!no$ci b"dzie u!ycie tabeli przestawnej. Struk-
tura tabeli przestawnej wygl&da nast"puj&co (rysunek 7.9):

1. Etykiety kolumn — pole przedzia%y.

2. Etykiety wierszy — pole kontrahent.

3. Warto"ci — pole kwota (format liczbowy z separatorem tysi&ca, bez miejsc

po przecinku).

Rysunek 7.9. Struktura tabeli przestawnej

Po tych wszystkich operacjach nasz analityk otrzyma% zestawienie wieko-

wania nale!no$ci wed%ug kontrahentów w podziale na okre$lone przedzia%y,
które mo!e dowolnie zmienia' (rysunek 7.10). Dodatkowo widniej& w zesta-
wieniu sumy kwot zaleg%ych nale!no$ci wed%ug kontrahentów (suma wiersza)
i wed%ug przedzia%ów (suma kolumny).

Kup ksiąĪkĊ

Pole

ü ksiąĪkĊ

9 0

| Zastosowanie Excela w pracy analityka finansowego, specjalisty ds. controllingu i analityka sprzeda!y

Rysunek 7.10. Gotowe zestawienie wiekowania nale no"ci

Je!eli chcemy wiedzie', jakie dokumenty sk%adaj& si" na dan& kwot", wystar-

czy j& dwukrotnie klikn&' i w nowym arkuszu zostan& wyfiltrowane wszystkie
dokumenty sk%adaj&ce si" na ni& (rysunek 7.11).

Rysunek 7.11. Pozycje wybranej kwoty

Drugim rozwi&zaniem na pokazanie dokumentów sk%adowych kwot jest

dodanie do obszaru Etykiety wierszy tabeli przestawnej pola faktura. Dzi"ki
temu otrzymamy zestawienie bardziej szczegó%owe w podziale nale!no$ci wed%ug
kontrahenta/faktury w poszczególnych przedzia%ach. Do ka!dej pozycji kontra-
henta zostanie dodana podsuma (rysunek 7.12).

Po pobraniu nowych danych zadaniem naszego analityka b"dzie jedynie

wklejenie nowego zestawu faktur, zmiana daty, na któr& ma zosta' wykonane
wiekowanie, sprawdzenie, czy funkcje w kolumnach przedzia%y i liczba dni

Kup ksiąĪkĊ

Pole

ü ksiąĪkĊ

Czytaj dalej...

W I E K O W A N I E N A L E @ N O " C I I Z O B O W I H Z A & |

9 1

Rysunek 7.12. Dodanie pola faktura do Etykiety wierszy

obejmuj& ca%y zestaw danych, oraz od$wie!enie tabeli przestawnej. W taki sam
sposób analityk mo!e wykona' wiekowanie zobowi&za:. Jak wida', stworzone
narz"dzie mo!e s%u!y' do wiekowania zarówno nale!no$ci, jak i zobowi&za:.
Jest elastyczne, poniewa! umo!liwia zmian" przedzia%ów oraz daty wiekowania.
Co najwa!niejsze, zaoszcz"dza te! du!o czasu u!ytkownikowi i usprawnia jego
prac". Taki raport jest równie! przejrzysty dla osoby czytaj&cej go i zawiera
wszelkie potrzebne informacje na temat nale!no$ci od poszczególnych kontra-
hentów z poziomem szczegó%owo$ci do dokumentu.

Kup ksiąĪkĊ

Pole

ü ksiąĪkĊ