Niedziela, 17 października 2021

Wskazówka programu Excel: Modelowanie i analiza "Co jeśli? Analiza za pomocą tabel przestawnych

13 września 2021

Jest to jeden z serii poradników dotyczących tabel przestawnych w Excelu, opracowanych przez konsultanta ds. oprogramowania AccountingWEB Davida Cartera. Tutoriale zostały stworzone dla odbiorców z Wielkiej Brytanii, ale równie dobrze sprawdzają się w USA.

Zachęcamy do zapoznania się z innymi tutorialami Cartera dotyczącymi tabel przestawnych, w tym:

  • Budżetowanie za pomocą tabel przestawnych w Excelu (Budgeting with the Excel Pivot Tables)

Analizowanie danych sprzedażowych przy użyciu tabel przestawnych ExcelaWyciąganie danych przy użyciu tabel przestawnych ExcelaAudyt i raportowanie finansowe przy użyciu tabel przestawnych ExcelaBudżetowanie przy użyciu tabel przestawnych Excela - część IIModelowanie i analizy typu "co jeśli? Analiza przy użyciu tabel przestawnych w Excelu

MODELOWANIE I ANALIZA "CO-JEŚLI" PRZY UŻYCIU TABEL PRZESTAWNYCH W EXCELU

Wszyscy księgowi używają arkuszy kalkulacyjnych do modelowania finansowego i analiz typu "co by było gdyby". Jednak nieuporządkowany format tradycyjnego arkusza kalkulacyjnego oznacza, że szczególnie duże modele mogą okazać się domkiem z kart, w którym nikt nie ufa wynikom ani nie rozumie, jak do nich doszedł.

Aby wyniki były wiarygodne, dane źródłowe muszą być przede wszystkim odpowiednio ustrukturyzowane. Tabele przestawne zapewniają taką strukturę i dają w efekcie wiarygodny, dobrze udokumentowany model, który można łatwo modyfikować w miarę zmieniających się okoliczności.

Modelowanie finansowe jest również jednym z najłatwiejszych zastosowań tabel przestawnych. Aby zacząć, wystarczy wpisać kilka danych do kilkunastu komórek arkusza kalkulacyjnego, uruchomić kreatora tabel przestawnych i już interaktywnie budujesz swój model na ekranie. Prześledź tutorial Davida Cartera, aby przekonać się, jakie to naprawdę proste!

UWAGA: Ten samouczek zawiera trzy pliki
:1/ Instrukcje startowe - instrukcje dotyczące tworzenia plików tabeli przestawnej. Wydrukuj je i wróć do AccountingWEB2/ Samouczek
Word - pobierz ten przewodnik i wydrukuj go, lub postępuj zgodnie z nim poniżej3/
Plik Excel - zawierający dane "What If"

.

Pobierz i zapisz go na dysku twardym.


Plik STARTUP zawiera szczegółowe instrukcje, jak przejść przez samouczek, ale jeśli masz jakiekolwiek problemy, wyślij e-mail do Roba Bensona ze szczegółami plików, z którymi masz problemy.

WPROWADZENIE â MODELOWANIE I ANALIZA CO JEŚLI? ANALIZA ZA POMOCĄ TABEL PRZESTAWNYCH

Ten poradnik pokazuje, jak skonfigurować model prognostyczny za pomocą tabel przestawnych Excela i jak wykorzystać go do obliczeń typu "co by było, gdyby".

Wszyscy księgowi używają arkuszy kalkulacyjnych do modelowania finansowego. Jednak tradycyjne metody pozostawiają dane nieuporządkowane. Powoduje to trudności w późniejszym czasie, gdy model musi zostać zmieniony lub gdy ktoś inny musi zinterpretować formuły. Jeśli zbudujesz swoje modele za pomocą tabel przestawnych, Twoje dane źródłowe będą odpowiednio ustrukturyzowane, a formuły odpowiednio udokumentowane, co pozwoli uniknąć tych problemów.

W tym ćwiczeniu jesteś wydawcą czasopisma. Używasz modelu do prognozowania przyszłych przychodów. Przychody pochodzą z różnych źródeł i zależą od takich zmiennych jak liczba prenumeratorów czasopisma, średnia opłata za prenumeratę, liczba stron w każdym miesięcznym wydaniu, średnia opłata za stronę reklamową, itd.

Przejście przez ten samouczek powinno zająć Ci około 30 minut. Jeśli to możliwe, dobrze jest, aby dwie osoby robiły to razem, jedna czytając instrukcję, a druga obsługując klawiaturę.

Będziesz szeroko korzystał z lewego i prawego przycisku myszy. Kiedy mówię "prawy przycisk myszy", mam na myśli kliknięcie prawym przyciskiem myszy. Kiedy mówię âclickâ na własną rękę, mam na myśli âclick lewy przycisk myszyâ.

Samouczek został opracowany i przetestowany w programie Excel 97. Uwagi w nawiasach kwadratowych [] wskazują na różnice między wersjami Excela. Jeśli nie jesteś pewien, którą wersję masz, sprawdź to teraz, uruchamiając Excela. Po prawej stronie menu głównego kliknij na Pomoc, a następnie na O programie Microsoft Excel. Wersja programu znajduje się w górnej części ekranu.

Samouczek w dużym stopniu wykorzystuje funkcję "Formuły" wprowadzoną po raz pierwszy w Excelu 97. W związku z tym na Państwa komputerze musi być zainstalowany program Excel 97 lub Excel 2000.

NIEWYJAŚNIONE INSTRUKCJE
Instrukcje zawarte w tym samouczku powinny być wystarczająco jasne, aby użytkownik mógł przejść do końca. Jeśli któryś z tekstów jest niejasny, proszę przeczytać go drugi raz. Jeśli po drugim przeczytaniu nadal jest to niejasne, proszę wysłać mi notatkę na adres [email protected]. Jeśli jakiś fragment regularnie sprawia trudności, nie ma problemu z jego korektą.

KORYGOWANIE BŁĘDÓWJeśli
instrukcje wydają się w porządku, ale w pewnym momencie to, co widać na ekranie, nie zgadza się z tym, co mówi samouczek, spróbuj kliknąć kilka razy na ikonę Cofnij w Excelu (lewa strzałka na pasku ikon u góry). Spowoduje to anulowanie poprzednich kroków. Jeśli jednak błąd nie ujawni się natychmiast, być może lepiej nie próbować poprawiać błędów, tylko wrócić i zacząć od nowa.

Czego się
nauczyszCel tego poradnika jest dwojaki. W pierwszej kolejności jest to przedstawienie ogólnej koncepcji wykorzystania tabel przestawnych do konkretnych zastosowań, w tym przypadku do modelowania finansowego.

Za pierwszym razem nie możesz oczekiwać, że zapamiętasz każdą sekcję i prawdopodobnie pod koniec zapomnisz, co robiłeś na początku. Jeśli więc chcesz nauczyć się, jak szczegółowo działają tabele przestawne, przygotuj się na dwu- lub trzykrotne przejście przez samouczek. Za drugim lub trzecim razem przekonasz się, że wiele rzeczy, które wcześniej nie były dla Ciebie oczywiste, zaczyna się układać w całość, ponieważ wtedy koncentrowałeś się tylko na wykonywaniu instrukcji.

1. URUCHOMIENIE

Uruchom program Excel. Otwórz plik: Whatif

Skoroszyt Whatif zawiera dwa arkusze â STANDARD i ŹRÓDŁO.

Kliknij na arkusz STANDARD. Wydrukujcie teraz jeden egzemplarz.

2. arkusz standardowy

Arkusz STANDARD zawiera pewne prognozy przyszłych dochodów, które zostały sporządzone w tradycyjny sposób. Twoim zadaniem w tym poradniku będzie stworzenie tych samych prognoz za pomocą tabel przestawnych.

Najpierw jednak zinterpretujmy arkusz STANDARD.

Jesteście Państwo wydawcą kilku miesięczników, Magazyn1, Magazyn2, Magazyn3 itd. W tym przypadku projektujemy przyszłe strumienie przychodów dla Magazynu1.

Przychody pochodzą z 3 źródeł â miesięcznych prenumerat od czytelników, przychodów z reklam oraz niewielkiej prowizji, którą otrzymujesz od reklamodawców za każdym razem, gdy czytelnik kupi jeden z ich produktów, który był reklamowany w Twoim magazynie.

W momencie rozpoczęcia budżetu, na początku kwietnia, masz 20 000 prenumeratorów. Przewiduje się, że liczba abonentów będzie wzrastać o około 2 000 miesięcznie.

Dochód z prenumeratyKażdego miesiąca Twoi
członkowie płacą miesięczną prenumeratę w wysokości 5£, która w 4. miesiącu, lipcu, wzrośnie do 6£
.

Dochód z reklam
Średnio otrzymujesz 4000£ w reklamie na każdą stronę magazynu. Dokładna liczba stron zmienia się w zależności od miesiąca. Szacuje się, że w sierpniu przychody z reklam wzrosną do 4 400£ za stronę.

Dochód z prowizji Za
każdym razem, gdy Twoi czytelnicy kupują produkty reklamowane w magazynie i podają numer referencyjny magazynu, Twój reklamodawca daje Ci rabat w wysokości 2,5% wartości sprzedaży
.

Proszę przestudiować arkusz STANDARD, a w szczególności wzory w kolumnie B, aby zrozumieć, w jaki sposób obliczane są poszczególne rodzaje dochodów.

Kiedy już zrozumiesz, w jaki sposób obliczane są przychody, kliknij na arkusz ŹRÓDŁO. 3.

3. arkusz "źródło

W arkuszu ŹRÓDŁO znajdują się dane źródłowe, na podstawie których generowane są tabele przestawne. Część z tych danych została już wprowadzona. W arkuszu znajdują się 4 kolumny:

TITLE zawiera nazwę czasopisma.

MIESIĄC zawiera miesiąc.

NEWSUBS zawiera liczbę nowych prenumeratorów, których planujesz zapisywać co miesiąc.

SUBSCRIBERS zawiera całkowitą liczbę prenumeratorów przewidywaną na koniec każdego miesiąca.

Wszystkie wartości w kolumnach od A do C zostały wpisane. Jednak wartość SUBSCRIBERS w kolumnie D jest wartością obliczoną. Kliknij komórkę D2.

Zobaczysz formułę =C2+20000

Kiedy budżet rozpoczyna się 1 kwietnia, masz 20 000 istniejących abonentów. Do końca kwietnia przewidywane jest kolejne 2 000 nowych abonentów, co daje 22 000 na koniec kwietnia.

Kliknij na D3, liczbę za maj. Wzór to = d2+c3

Jest to prosta formuła bilansu bieżącego, obliczająca, że: abonenci na koniec tego miesiąca = zamknięcie subskrybentów w zeszłym miesiącu + nowi subskrybenci w tym miesiącu.

Ta sama formuła bilansu bieżącego jest kontynuowana w pozostałej części kolumny D.

4. TWORZENIE TABELI PRZESTAWNEJ

Rozpoczniemy tworzenie tabeli przestawnej. Kliknij komórkę C3 lub dowolne miejsce w obrębie danych.

Z menu głównego na górze ekranu wybierz :

Dane - Raport tabeli przestawnej [Excel 2000 = Raport tabeli przestawnej i wykresu].

Pojawi się ekran Kreator tabeli przestawnej, krok 1 z 4. Kliknij przycisk Dalej [Excel 2000 â Krok 1 z 3].

Krok 2 z 4 W polu Zakres należy wpisać: $A$1:$D$7.

Kliknij przycisk Dalej.

Krok 3 z 4: Pojawia się okno COLUMN-ROW-DATA. Po prawej stronie znajdują się cztery nagłówki kolumn arkusza ŹRÓDŁO [uwaga: jeśli używasz programu Excel 2000, musisz najpierw kliknąć przycisk Układ].

Przeciągnij i upuść nagłówki kolumn w następujący sposób:

MIESIĄC do obszaru KOLUMNA

TYTUŁ do obszaru STRONA

SUBSKRYBENTÓW do obszaru DANE

NEWSUBS do obszaru DANYCH pod SUBSKRYBENTAMI

[Po upuszczeniu w obszarze DANE, SUBSCRIBERS powinien mieć napis âSum of SUBSCRIBERSâ. Jeżeli jest napisane âLiczba SUBSKRYBENTÓWâ, kliknij dwukrotnie na Liczbę SUBSKRYBENTÓW. Pojawi się okienko. Zmień âCountâ na âSumâ. Jeśli NEWSUBS mówi âCountâ, popraw to w ten sam sposób].

Teraz kliknij na Zakończ. [Jeśli korzystasz z Excela 2000, OK, a następnie Zakończ].

Zostanie wygenerowana tabela przestawna. Pod każdym nagłówkiem miesięcznym widzisz całkowitą liczbę subskrybentów i całkowitą liczbę nowych subskrybentów w danym miesiącu.

Kliknij z powrotem na arkusz ŹRÓDŁO, aby sprawdzić, czy oryginalne dane źródłowe nadal tam są.

Teraz kliknij prawym przyciskiem myszy z powrotem na nowy arkusz z tabelą przestawną. Zmień jej nazwę na PIVOT.

Przyjrzyj się swojemu wydrukowi STANDARD. Sprawdź, czy liczba subskrybentów w tabeli przestawnej jest taka sama jak na wydruku. 5.

5. CIENIOWANIE NAGŁÓWKÓW KOLUMN

[Excel 97
]Kliknij szary przycisk pola "MIESIĄC" w komórce B3. Wszystkie wiersze sum częściowych zostaną podświetlone
lub
[Excel

2000]
Przesuń wskaźnik myszy nad szary przycisk pola âMONTHâ w komórce B3


.


Przesuwaj go stopniowo w dół, aż przybierze kształt grubej pionowej strzałki w dół. Następnie kliknij lewym przyciskiem myszy. Wszystkie wiersze sum częściowych zostaną podświetlone.



Aby dodać cieniowanie, kliknij żółtą ikonę âWypełnij koloremâ w dolnym rzędzie ikon, drugą od prawej (ta, która wygląda, jakby nalewała farbę do puszki).

Teraz wyśrodkuj miesiące, klikając ikonę Wyśrodkuj. Kliknij również ikonę B jak Pogrubienie

Z jakiegoś powodu cieniowanie komórek w Excelu usuwa linie siatki. Aby zachować linie siatki, najpierw znajdź ikonę âBordersâ po lewej stronie żółtej ikony âFill Colorâ (wygląda ona jak cztery ćwiartki).

Kliknij strzałkę w dół w prawej części ikony Borders.

Pojawi się okienko z 12 możliwymi borderami. W dolnym wierszu, drugim od lewej, wybierz opcję âAll Bordersâ.

Kliknij w dowolnym miejscu, aby usunąć wyróżnienie.

6. USUŃ âGRAND TOTALSâ

Wartości âGrand Totalâ w kolumnie H są fałszywe i muszą zostać usunięte. Aby to zrobić:

Kliknij prawym przyciskiem myszy na tabeli przestawnej w celu uzyskania menu.

Wybierz: Opcje. [Pojawi się okno Opcje tabeli przestawnej.

Znajdź opcję âGrand totals for Rowsâ w lewym górnym rogu. Usuń zaznaczenie. OK

Tabela przestawna zostanie przeliczona, a kolumna âGrand Totalâ zniknie.

7. WPROWADŹ MIESIĘCZNĄ OPŁATĘ ZA SUBSKRYPCJĘ DO arkusza roboczego âSOURCEâ

Przychód z subskrypcji jest obliczany jako liczba subskrybentów x subskrypcja miesięczna.

Arkusz ŹRÓDŁO zawiera już liczbę subskrybentów. Teraz musimy dodać miesięczną opłatę za subskrypcję.

Kliknij arkusz ŹRÓDŁO. W komórce E1 wpisz nagłówek kolumny: SUBÂŁ_MTH.

W komórkach od E2 do E7 wpisz 5,5,5,6,6,6 (w lipcu abonament wzrasta do 6 zł miesięcznie).

Kliknij na arkusz PIVOT.

Kliknij prawym przyciskiem myszy na tabeli przestawnej, aby wyświetlić menu. Wybierz: Kreator

Pojawia się okienko Krok 3. Z listy opcji znajdującej się na dole wybierz: Wstecz

Pojawia się okienko Zakres, Krok 2. Zakres to $A$1:$D$7.

Powyżej widać komórki od A1 do D7 otoczone liniami okrężnymi.

Nowa kolumna E znajduje się poza tymi liniami. Należy ją uwzględnić poprzez zmianę $D$7 na $E$7.

Aby to zrobić: W polu Zakres kliknij na lewo od âDâ w komórce $D$7.

Usuń D, naciskając klawisz âDeleteâ po prawej stronie dużego klawisza Enter.

Wpisz E tak, aby było $e$7, a następnie kliknij Dalej. Krok 3 pojawia się ponownie.

Kliknij ponownie na Wstecz. Sprawdź, czy linie poruszają się teraz po wszystkich danych. Następna strona

Pojawia się obszar ROW-COLUMN-DATA [Excel 2000, kliknij na Układ].

Zauważ, że na dole listy po prawej stronie pojawił się napis SUBÂŁ_MTH. Przeciągnij i upuść go pod Suma NOWYCHUBYTKÓW w obszarze DANE. Zakończ.

W dolnej części tabeli przestawnej pojawiła się miesięczna opłata abonamentowa.

8. UŻYCIE FORMUŁ DO OBLICZENIA PRZYCHODU Z SUBSKRYPCJI

Teraz, aby obliczyć Przychód z subskrypcji jako Liczba subskrybentów x Miesięczna opłata subskrypcyjna.

Kliknij prawym przyciskiem myszy na tabelę przestawną, aby wyświetlić menu. Wybierz: Formuły - Pole wyliczone

Pojawi się okno âInsert Calculated Fieldâ.

W polu âFormulaâ kliknij na lewo od 0. Następnie usuń 0:

Na liście pól znajdź SUBSCRIBERS i podświetl je. Następnie kliknij na Wstaw pole.

SUBSCRIBERS pojawia się w polu Formuła.

Po prawej stronie SUBSCRIBERS dodaj gwiazdkę (â*â), co oznacza âmultiply byâ.

Wróć do listy pól. Znaleźć SUBÂŁ_MTH i podświetlić je. Wstaw pole

W polu Formuła powinna się teraz pojawić formuła =SUBSCRIBERS*'SUBÂŁ_MTH'.

Teraz kliknij pole âNazwaâ tuż powyżej.

Zastąpić Pole1 całkowitym przychodem z subskrypcji.

Kliknij przycisk Dodaj, a następnie OK na dole.

W dolnej części tabeli przestawnej pojawi się nowy wiersz zatytułowany âSuma całkowitego przychodu z subskrypcjiâ.

Kliknij prawym przyciskiem myszy w dowolnym miejscu nowego wiersza. Pojawi się menu. Wybierz: Pole [jeśli Excel 2000, Ustawienia pola] - Liczba - Waluta - Miejsca dziesiętne = 0 - Znak ÂŁ OK OK.

Wiersz Całkowite przychody z subskrypcji przyjmuje przecinki i znaki funta.

W ten sposób kończymy obliczanie pierwszego źródła dochodu.

9. WPROWADŹ STRONY I OGŁOSZENIA. Stawki do arkusza roboczego âSOURCEâ

Teraz opracujemy dochód z drugiego źródła - reklamy. Obliczamy ją jako szacunkową stawkę 4000£ za każdą stronę, na której ukazuje się czasopismo. Przewiduje się, że w kwietniu czasopismo będzie miało 22 strony. Zatem kwietniowy dochód z reklam powinien wynieść 22 x 4000 ÂŁ = 88 000 ÂŁ.

Zauważ, że w lipcu przychody z reklam na jedną stronę mają wzrosnąć z 4000£ do 4400£.

Kliknij ponownie na arkusz ŹRÓDŁO.

W polu F1 wpisz nagłówek kolumny "STRONY".

W polach od F2 do F7 wpisz: 22, 22, 24, 25, 20, 24.

W G1 wpisz nagłówek kolumny ADÂŁ_STRONA

W polach od G2 do G7 wpisz: 4000, 4000, 4000, 4400, 4400, 4400.

Kliknij na arkusz PIVOT.

Kliknij prawym przyciskiem myszy na tabeli przestawnej, aby wyświetlić menu. Wybierz: Wizard .

Pojawi się krok 3. Z listy opcji znajdującej się na dole wybierz: Wstecz

Pojawia się pole Zakres, krok 2. Zakres to $A$1:$E$7.

Powyżej widoczne są komórki od A1 do E7 otoczone liniami okręgów.

Nowe kolumny F i G muszą zostać włączone do zakresu.

W polu Zakres kliknij na lewo od znaku E w $E$7.

Usuń E, naciskając klawisz âDeleteâ po prawej stronie dużego klawisza Enter.

Wpisz G tak, aby zakres brzmiał teraz $A$1:$G7 (dużymi lub małymi literami).

Kliknij na Dalej. Kliknij ponownie na Wstecz, aby sprawdzić, czy linie są w porządku.

Ponownie klikamy na Dalej. Pojawia się obszar ROW-COLUMN-DATA.

PAGES i ADÂŁ_PAGE zostały dodane do listy po prawej stronie.

Przeciągnij i upuść pozycje PAGES i AD£_PAGE pod Sumą całkowitych przychodów z subskrypcji w obszarze DANE. Zakończ.

W dolnej części tabeli przestawnej pojawiają się pozycje âSuma STRON i âSuma STRON ADÂŁâ.

10. OBLICZANIE PRZYCHODU Z REKLAM

Dochód z reklam oblicza się jako liczbę stron x stawkę reklamową za stronę. Dlatego:

Kliknij prawym przyciskiem myszy na tabeli przestawnej, aby uzyskać menu. Wybierz: Formulas - Calculated Field (Formuły - Pole obliczone)

Pojawi się okno âInsert Calculated Fieldâ.

W polu âFormulaâ kliknij na lewo od 0. Usuń 0. Następnie:

Na liście pól znajdź PAGES i podświetl je. Następnie kliknij na Wstaw pole.

PAGES pojawia się w polu Formuła

Po prawej stronie PAGES dodaj gwiazdkę (â*â), co oznacza âmultiply byâ.

Wróć do listy pól. Znajdź ADÂŁ_PAGE i podświetl je. Wstaw pole

W polu Formuła powinna się teraz pojawić formuła =PAGES*'ADÂŁ_PAGE'.

Kliknij teraz na pole Nazwa.

Zamień pole 1 na Total Advertising Income

Kliknij na Dodaj, a następnie OK na dole

Na dole tabeli przestawnej pojawi się "Suma całkowitego przychodu z reklamy".

11. PRZEGLĄD WYDARZEŃ

Można zauważyć sekwencję związaną z budowaniem modelu. Po pierwsze, dodajemy nową zmienną do arkusza ŹRÓDŁO. Następnie przechodzimy do tabeli przestawnej i wyświetlamy tę zmienną. Po trzecie, w tabeli przestawnej definiujesz formułę obliczeniową opartą na zmiennej.

Zauważ, że nie musisz wyświetlać zmiennych w arkuszu ŹRÓDŁO w tabeli przestawnej. Jest to opcjonalne.

Zauważ również, przy okazji, że każdy wiersz nie musi zaczynać się od âSum ofâââ. Na przykład:

Kliknij prawym przyciskiem myszy w dowolnym miejscu dolnego wiersza. Pojawi się menu. Wybierz: Pole [Settings]

Pojawia się okno pola tabeli przestawnej. W polu âNazwaâ czytamy: Suma całkowitych przychodów z reklam.

Kliknij na prawo od znaku âfâ w polu âSum ofâ.

Użyj klawisza backspace, aby usunąć Suma. OK

Pamiętaj, że zawsze musisz zostawić puste miejsce przed Total â w przeciwnym razie Excel wyświetli komunikat: âNazwa pola tabeli przestawnej już istniejeâ.

Teraz zmień formatowanie liczb również w tym wierszu: Pole - Liczba - Waluta - Miejsca dziesiętne = 0 - Symbol = ÂŁ - OK - OK - OK.

12. DODAJ ZMIENNE PROWIZJI DO ARKUSZA ŹRÓDŁOWEGO

Trzecie źródło dochodów pochodziło z prowizji. Kiedy czytelnicy kupują produkty reklamowane w magazynie, reklamodawca płaci Ci 2,5% prowizji od wartości zakupu.

Obliczanie prowizji jest nieco bardziej skomplikowane. Najpierw szacuje się, jaka część czytelników dokona zakupu, a na tej podstawie oblicza się liczbę kupujących.

Następnie szacuje się średnią wartość każdego zakupu, którego dokona kupujący.

Na podstawie tych dwóch wartości obliczasz przewidywaną całkowitą wartość âGross Reader Purchases’.

Następnie mnożysz Wartość Brutto Zakupów Czytelników przez stawkę prowizji 2,5%, aby przewidzieć swój przyszły Dochód z Prowizji.

Kliknij na arkusz ŹRÓDŁO.

W komórce H1 wpisz nagłówek kolumny %KUPUJĄCY.

W komórkach od H2 do H7 wpisz procent czytelników, którzy mają dokonać zakupu. Są to:

0.15, 0.16, 0.14, 0.17, 0.17, 0.18

W komórce I1 wpisujemy nagłówek kolumny BUY£_VAL

W komórkach od I2 do I7 wpisz: 20, 20, 20, 20, 20 , 20, 20.

W komórce J1 wpisz nagłówek kolumny %COMMN.

W komórkach od J2 do J7 wpisz standardową prowizję 2,5% jako: 0.025

13. OBLICZ ZAKUPY CZYTELNIKÓW BRUTTO

Kliknij ponownie na arkusz PIVOT.

Kliknij prawym przyciskiem myszy na tabeli przestawnej, aby wyświetlić menu. Wybierz: Wizard .

Pojawi się krok 3. Z listy opcji znajdującej się na dole wybierz: Wstecz

W polu Range zmień $G$7 na $J$7 (duże lub małe litery)

Kliknij przycisk Next. Następnie kliknij Back. Sprawdź, czy dane są otoczone Next ponownie.

Pojawi się obszar ROW-COLUMN-DATA.

Przeciągnij i upuść %BUYERS pod Total Advertising Income w obszarze DATA.

Przeciągnij i upuść pozycję BUY£_VAL pod Suma %BUYERS. Zakończ.

W dolnej części tabeli przestawnej pojawi się teraz Suma %BUYERS i Suma BUY£_VAL.

Kliknij prawym przyciskiem myszy w wierszu %BUYERS. Pole â Liczba â Procent â bez miejsc dziesiętnych.

Kliknij prawym przyciskiem myszy na tabeli przestawnej, aby uzyskać menu. Wybierz: Formuły - Pole obliczone.

Na liście pól znajdź SUBSCRIBERS i zaznacz je. Następnie kliknij na Wstaw pole.

SUBSCRIBERS pojawi się w polu Formuła

Dodaj gwiazdkę.

Wstaw %BUYERS do pola formuły.

Dodaj gwiazdkę.

Wstawić BUY£_VAL do pola formuły.

Pole Formuły powinno teraz wyświetlać: =SUBSCRIBERS*'%BUYERS'*'BUY£_VAL'.

W polu Nazwa zastąpić Pole1 na: Gross Reader Purchases (Zakupy czytelników brutto)

Kliknij na Dodaj, a następnie OK.

Na dole tabeli przestawnej pojawia się âSuma zakupów czytelników brutto.

14. OBLICZANIE DOCHODU Z PROWIZJI

Teraz, aby obliczyć dochód z prowizji.

Kliknij prawym przyciskiem myszy na tabelę przestawną. Wybierz: Kreator. Pojawi się okno ROW-COLUM-DATA

Upuść %COMMN w obszarze DATA pod Suma zakupów czytelników brutto.

Zakończ. W tabeli przestawnej wyświetlana jest miesięczna stawka prowizji 0,025.

Kliknij prawym przyciskiem myszy, aby uzyskać menu. Wybierz: Formuły â Obliczone pole.

W polu Formuły wstawiamy: Zakupy czytelnika brutto (znajduje się na dole listy).

Dodaj gwiazdkę.

Wstaw %COMMN do pola Formuły.

Pole Formuły powinno teraz wyświetlać: ='Gross Reader Purchases'*'%COMMN'.

W polu Nazwa wpisz: Total Commission Income

Kliknij na Dodaj, a następnie OK

Na dole tabeli przestawnej nie pojawia się żaden nowy wiersz!!!

W rzeczywistości nic nie jest nie tak. Do tej pory, za każdym razem, gdy tworzyłeś nową formułę, Excel automatycznie umieszczał ją w tabeli przestawnej. Ale po 11 lub więcej wierszach zatrzymuje się i musisz sam ją wstawić. Aby to zrobić:

Kliknij prawym przyciskiem myszy na tabelę przestawną. Wybierz: Kreator. Pojawi się okno ROW-COLUM-DATA

Pole Total Commission Income znajduje się na dole po prawej stronie na liście nazw pól.

Przeciągnij i upuść pole Total Commission Income pod Suma %COMMN w obszarze DATA.

Zakończ. W dolnej części tabeli przestawnej pojawi się napis âSum of Total Commission Incomeâ.

Kliknij prawym przyciskiem myszy wiersz Całkowity przychód z prowizji. Pole â Liczba â Waluta - bez miejsc dziesiętnych - znak ÂŁ.

15. OBLICZ CAŁKOWITY DOCHÓD

Wszystkie trzy źródła dochodu zostały teraz obliczone. Zbudowałeś swój model.

Aby wszystko zakończyć, w komórce A18 wpisz: = A8 âSum of Total Subscription Incomeâ.

W komórce A19 wpisz = A11. W komórce A20 wpisz = A16.

Kliknij komórkę A18.

Trzymając wciśnięty klawisz Shift, naciśnij klawisz strzałki w dół, aby podświetlić komórki od A18 do A20.

Trzymając wciśnięty klawisz Shift, naciśnij strzałkę w prawo, aby rozszerzyć podświetlenie na kolumnę G.

Z menu głównego: Edycja - Wypełnij - W prawo. Pojawiają się wartości miesięczne.

Z menu wybierz: Format - Komórki - Liczba - Waluta - Bez miejsc dziesiętnych - Znak ÂŁ - OK.

Kliknij na komórkę B21 na dole kolumny B.

Podświetl komórki od B21 do G21.

Kliknij ikonę âAutosum. Trzy wiersze z dochodami zostaną zsumowane.

Kliknij ikonę B, aby pogrubić sumy.

Znajdź ikonę Granice po lewej stronie żółtej ikony Kolor wypełnienia. Kliknij strzałkę w dół na tej ikonie.

Wybierz opcję "Gęste obramowanie" znajdującą się w prawym dolnym rogu.

Kliknij w dowolnym miejscu, aby usunąć podkreślenie.

Miejmy nadzieję, że sumy powinny zgadzać się z tymi na wydruku STANDARD.

16. LISTA FORMUŁ

Po skonfigurowaniu modelu wyświetl teraz listę formuł w następujący sposób.

Kliknij prawym przyciskiem myszy na tabeli przestawnej. Wybierz: Formuły â Listuj formuły.

Zostanie utworzony nowy arkusz zawierający listę formuł użytych w tabeli przestawnej.

Zmień nazwę nowego arkusza na FORMULAS.

17. WYKONYWANIE OBLICZEŃ TYPU "CO BY BYŁO, GDYBY? OBLICZENIA

Aby zobaczyć efekt jakichkolwiek zmian w zmiennych, dokonujemy zmian w arkuszu ŹRÓDŁO, a następnie wracamy do arkusza PIVOT i używamy polecenia ODNÓW DANE, aby zobaczyć wynik. Na przykład:

Załóżmy, że liczba nowych członków nie wzrasta tak szybko, jak przewidywano. Kliknij na arkusz ŹRÓDŁO.

Zmień kolumnę C âNEWSUBSâ na: 1800, 1900, 2000, 2000, 1500, 2500

Kliknij z powrotem na PIVOT. Kliknij prawym przyciskiem myszy, aby wyświetlić menu.

Trzymając wzrok na wierszach 6 i 21, wybierz: Odśwież dane. Wszystkie liczby się zmienią. UWAGA: Jeśli w którejś z kolumn widoczne są hasze, zwiększ szerokość kolumny.

18. ZMIANA MODELU

(Najpierw zanotuj wartość Zakupów czytelnika brutto w kwietniu. Powinno to być 65 400 zł)

Zmiana modelu jest również bardzo prosta.

Na przykład, niektórzy nowi abonenci będą przybywać na początku miesiąca, a niektórzy na końcu. Tak więc, podczas szacowania przychodów z prowizji, zdecydujesz się uśrednić nowych abonentów w każdym miesiącu, zmieniając wzór na Zakupy Czytelników Brutto z:

=SUBSCRIBERS*%BUYERS*BUY£_VAL

na: =(SUBSCRIBERS-(NEWSUBS/2))*%BUYERS*BUY£_VAL

Aby to zrobić, kliknij prawym przyciskiem myszy z menu. Wybierz: Formuły â Obliczone pole.

Kliknij na strzałkę w dół po prawej stronie pola Nazwa (które zawiera Pole1).

Na liście znajdują się różne formuły.

Wybierz opcję Zakupy czytelnika brutto.

W polu Nazwa pojawia się âGross Reader Purchasesâ, a przycisk âAddâ zmienia się na âModifyâ.

Zmień formułę na: =(SUBSCRIBERS-(NEWSUBS/2))*%BUYERS*BUY£_VAL

Kliknij na OK. Zakupy Czytelników brutto w kwietniu wynoszą teraz 62.700£.

Kliknij na arkusz Formuły. Zauważ, że formuła dla Zakupów czytelników brutto nie uległa zmianie

Kliknąć prawym przyciskiem myszy na Formularze na dole arkusza. Usunąć ten arkusz.

Kliknij prawym przyciskiem myszy na tabeli przestawnej, aby uzyskać menu. Wybierz: Formuły â Lista formuł.

Zostanie wygenerowana nowa lista formuł. Formuła Zakupy czytelnika brutto jest teraz poprawna.

OK. Czas zrobić sobie przerwę. Plik - Zamknij - Zapisz swoją pracę. Odpręż się.

19. CO MAM DALEJ ROBIĆ?

a) ZBUDUJ WŁASNY MODEL

Budowanie modeli za pomocą tabel przestawnych jest bardzo proste. Weź model, który już stworzyłeś w tradycyjny sposób i sprawdź, czy możesz go odtworzyć w tabeli przestawnej. Utrzymuj ten pierwszy model tak prosty, jak to tylko możliwe, dopóki nie nabierzesz pewności w tej technice. Następnie spróbuj zrobić jeszcze jeden, bardziej ambitny.

b) DODATKOWE TUTORIALE SELF-TEACH W
momencie pisania tego tekstu (16 maja 2000 r.) na stronie Accountingweb.co.uk dostępnych jest sześć tutoriali dotyczących tabel przestawnych w Excelu. Znajdują się one w sekcji Expert Guides z menu głównego.

Jeśli Twoim głównym zainteresowaniem jest księgowość, możesz pracować przez Budżetowanie z tabelami przestawnymi â Część druga. Ten samouczek wykorzystuje dane budżetowe, które zostały użyte tutaj i pokazuje, jak dodać fakty, raportować miesięczne i roczne fakty w porównaniu z budżetem oraz obliczać wariancje.

Następnie wypróbuj Audyt i raportowanie finansowe z użyciem tabel przestawnych. Ten moduł wykorzystuje 2000 transakcji księgowych z pakietu Sage Line 50 i pokazuje, jak sprawdzić je pod kątem błędów przed obliczeniem rachunku zysków i strat oraz bilansu.

Alternatywnie, jeśli interesuje Cię analiza sprzedaży lub hurtownia danych, samouczek Analizuj dane sprzedaży z tabelami przestawnymi pokazuje, jak zaimportować 4000 faktur sprzedaży z pakietu księgowego, takiego jak Great Plains lub Navision, i analizować je na wiele różnych sposobów, aby pokazać sprzedaż według grupy produktów, sprzedaż według klientów w miesiącu, zysk brutto według kierownika sprzedaży, i tak dalej.

b) UŻYWANIE TABEL PIVOT NA WŁASNYCH DANYCH RACHUNKOWYCH

Oczywiście, jednym z głównych powodów nauki tabel przestawnych musi być wykorzystanie ich do analizy danych własnej firmy. Jeśli dane znajdują się w bazie danych, takiej jak Microsoft Access, tabele przestawne są zawarte w Access 2000. Jeśli dane znajdują się w pakiecie innej firmy, takim jak Great Plains, Navision, Sage itp., musisz dowiedzieć się, jakie dane musisz z nich wyodrębnić i jak to zrobić.

Samouczek Sage Line 50 i tabele przestawne dotyczy pobierania surowych danych z powszechnie używanego pakietu księgowego Sage Line 50, przygotowania ich do użycia w tabeli przestawnej i przeprowadzenia wstępnej analizy. Samouczek ten omawia kluczowy problem, z którym musi się zmierzyć każdy, kto chce korzystać z tabel przestawnych: dane, które chcesz analizować, prawdopodobnie znajdują się w jakimś pakiecie księgowym innej firmy, przeznaczonym do przetwarzania transakcji. Zanim w ogóle zaczniesz je analizować, może być wymagane wiele pracy, aby wyodrębnić te dane i przekształcić je do formatu gotowego do pracy z tabelami przestawnymi. (W pewnym stopniu ten kurs został zastąpiony przez kurs âAudyt i sprawozdawczość finansowaâ).

KONIEC SAMOUCZKA

DAVID CARTER, 15 maja 2000

David Carter, mieszkający w Hemel Hempstead, Herts, jest niezależnym konsultantem, który instaluje systemy księgowe i systemy przetwarzania zamówień w małych i średnich firmach. Pisze również recenzje małych i średnich pakietów księgowych dla prasy komputerowej. Od 1996 roku używa tabel przestawnych Excela w takich dziedzinach, jak rachunkowość finansowa i zarządcza, budżetowanie, kalkulacja kosztów pracy, analiza sprzedaży, analiza portfela zamówień i kontrola produkcji. Z przyjemnością zapozna się z wszelkimi opiniami na temat tego poradnika i można się z nim skontaktować pod adresem [ email protected]

Zobacz więcej pomocnych wskazówek!