Wtorek, 19 marca 2024

Wskazówki dotyczące Excela: Audyt i raportowanie finansowe z wykorzystaniem tabel przestawnych

6 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 za pomocą tabel przestawnych w Excelu

Samouczek 4: Audyt i sprawozdawczość finansowa

W tej kontynuacji swojego pierwszego samouczka Sage Line 50 David Carter przeprowadza audyt 2000 rekordów księgowych (bilans próbny, podsumowanie kontroli dłużników, uzgodnienie bankowe, analiza dziennika) i drukuje raporty finansowe (bilans, zyski i straty, zyski i straty poszczególnych działów, podsumowania sprzedaży klientów) â wszystko w ułamku czasu, jaki zajęłyby konwencjonalne metody.

Mówi David: âTo jest ukoronowanie tej serii i jeśli miałbym tylko jeden tutorial, który chciałbym, aby księgowy zobaczył, aby zrozumiał potęgę Excela i tabel przestawnych, to byłby to właśnie ten. Mamy wyodrębnione dane z Sage za pomocą programu SagePivot, i przyszedł âpivot-table gotowyâ do natychmiastowej analizy, więc byliśmy w stanie umieścić Excel prosto do pracy. Wyniki są zdumiewające.â

Wreszcie, David ilustruje jeden naprawdę niebezpieczny błąd projektowy w Excelu i prosi was wszystkich o wysłanie e-maila do Billa Gatesa z prośbą o jego usunięcie.

Teraz czytaj dalej!

Samouczek âAudyt i sprawozdawczość finansowaâ pokazuje, jak używać tabel przestawnych Excela do szybkiego identyfikowania błędów w zestawie kilku tysięcy rekordów księgowych, a następnie drukować raporty finansowe i raporty rentowności działów â wszystko to w ułamku czasu, jaki zajęłyby te same zadania przy użyciu konwencjonalnych metod.

Tutorial jest zbudowany wokół dwóch plików. Plik SAGEPIVOT.CSV zawiera 2 052 rekordy pochodzące z zestawu danych demonstracyjnych dostarczonych przez firmę Sage wraz z wersją 6 pakietu Line 50. Dane te zostały wyprowadzone z Sage przez program do ekstrakcji danych âSagePivotâ. Dodatkowo dołączony jest plik Word, który informuje o tym, jak analizować rekordy przy użyciu programu Excel.

Proszę zacząć od wydrukowania instrukcji pobierania plików z AccountingWEB. Znajdują się one w dwustronicowym pliku Word. Kiedy pojawią się na ekranie, wybierz Plik â Drukuj, aby je wydrukować.
Kliknij teraz dwukrotnie na instrukcje STARTUP.

Korzystając z instrukcji zawartych w Startup, wróć teraz do AccountingWEB i pobierz dwa pliki, których będziesz używał w tym samouczku. Są to plik WORD zawierający instrukcje dla programu Excel oraz plik SAGEPIVOT.CSV zawierający przykładowe dane.

Z naszego doświadczenia wynika, że niewielka część użytkowników będzie miała problemy z pobraniem tych plików. W razie jakichkolwiek problemów prosimy o kontakt z Robem Bensonem z AccountingWEB, a my prześlemy kopie plików z danymi w odpowiedzi.

WPROWADZENIE DO SAMOUCZKA Z ZAKRESU AUDYTU I SPRAWOZDAWCZOŚCI FINANSOWEJ

Niniejszy samouczek jest obiecaną częścią drugą pierwszego samouczka Sage Line 50, który został wydany 2 lutego 2000 roku.

Różni się on od pierwszego samouczka na dwa sposoby. Po pierwsze, wykorzystuje znacznie pełniejszy zestaw danych demonstracyjnych dostarczonych przez Sage wraz z wersją 6 Line 50. (z menu Sage: â Otwórz â Firma demonstracyjna). Znajdują się tam 2 052 rekordy obejmujące 12 miesięcy od 31/12/1998 do 31/12/1999. Te dane demonstracyjne zostały nieco zmienione, główna zmiana polega na przyjęciu, że rok finansowy firmy trwa od lipca do czerwca.

Drugą różnicą jest to, że zamiast eksportować dane z Sage poprzez jeden ze standardowych raportów Sage, użyliśmy specjalnie napisanego programu do wygenerowania pliku z danymi. Ten program, o nazwie âSAGEPIVOTâ, jest przeznaczony do wyodrębnienia danych z Sage i zestawić je w formacie idealnym do analizy przez Excel i tabel przestawnych (więcej szczegółów na temat SAGEPIVOT na końcu tej instrukcji).

W pewnym stopniu, zatem, pierwszy tutorial Sage był âPrzedâ, podczas gdy ten drugi jest âPoâ. Pierwszy samouczek spędził dużo czasu na doprowadzeniu surowych danych do stanu umożliwiającego ich analizę. W tym samouczku to zadanie zostało już wykonane za Ciebie. Możesz więc od razu rozpocząć analizę danych i wykorzystać wszystkie niezwykłe możliwości analityczne, jakie ma do zaoferowania Excel.

Jeśli po raz pierwszy korzystasz z tabel przestawnych, rozważ najpierw zapoznanie się z samouczkiem "Budżetowanie z tabelami przestawnymi Excela" (dostępnym w sekcji Poradniki eksperta). Ma on na celu zapoznanie początkującego użytkownika z podstawowymi funkcjami tabel przestawnych. Obecny samouczek jest bardziej zaawansowany, ponieważ jest znacznie dłuższy (21 stron w porównaniu z 8), a także zakłada pewne wcześniejsze doświadczenie.

Przejście samouczka powinno zająć Ci około 40 minut. Jeśli to możliwe, dobrze jest, aby dwie osoby robiły to razem, jedna czytając instrukcje, 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â.

Niniejszy samouczek można uruchomić w dowolnej wersji programu Excel od wersji Excel 5 (wydanej w 1994 r.), czyli w programie Excel 5, Excel 7 (dla systemu Windows 95), Excel 97 lub Excel 2000. Samouczek został opracowany i przetestowany na Excelu 97. Uwagi w nawiasach kwadratowych [] wskazują na różnice między innymi wersjami programu Excel. Jeśli nie jesteś pewien, jaką wersję programu posiadasz, sprawdź to, uruchamiając program Excel. Po prawej stronie menu głównego kliknij na Pomoc, a następnie na O programie Microsoft Excel. Wersja znajduje się w górnej części ekranu.

Przed rozpoczęciem pracy należy upewnić się, że formaty daty w komputerze są ustawione prawidłowo dla Twojej części świata. W przeciwnym razie tabela przestawna może nie być w stanie poprawnie grupować dat. Kliknij na: Start - Ustawienia - Panel sterowania - Ustawienia regionalne. Jeśli mieszkasz w Wielkiej Brytanii, powinny one brzmieć English (United Kingdom) lub English (British).

Najlepiej byłoby, gdyby instrukcje zawarte w tym poradniku były na tyle jasne, abyś był w stanie przebrnąć przez nie do końca. Jeśli któryś z tekstów jest niejasny, proszę przeczytać go drugi raz. Jeśli po drugim przeczytaniu nadal jest on niejasny, proszę wysłać mi notatkę na adres [email protected]. Jeśli jakiś fragment regularnie sprawia trudności, nie ma problemu, aby go poprawić.

Jeśli okaże się, że 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ę od razu, być może lepiej nie próbować poprawiać żadnych błędów, tylko wrócić i zacząć od nowa. Prawdopodobnie będzie to szybsze na dłuższą metę, a w każdym razie nie jest złym pomysłem przejść przez tutorial dwa razy. Za drugim razem przekonasz się, że wiele rzeczy, które nie zostały zauważone za pierwszym razem, ponieważ koncentrowałeś się tylko na podążaniu za instrukcjami, jest na swoim miejscu.

A. 1. IMPORTUJ PLIK CSV âSAGEPIVOTâ DO EXCELA

Uruchom program Excel. Plik - Otwórz

Przejdź do folderu, w którym znajduje się plik SAGEPIVOT.CSV Nie widzisz pliku SAGEPIVOT.CSV

Zmień Pliki typu: na Wszystkie pliki Pojawia się SAGEPIVOT. Otwórz go.

Pojawiają się dane. Są one posortowane w kolejności konta nominalnego (NLAC â pierwsza kolumna).

Kliknąć na cegiełkę â1â po lewej stronie wiersza 1. Górny wiersz zostaje podświetlony.

Kliknąć ikonę B. Nagłówki kolumn w wierszu 1 stają się pogrubione.

Podświetl cały arkusz, klikając w górną cegiełkę LH powyżej 1 i na lewo od A. Następnie:

Kliknij dwukrotnie pomiędzy nagłówkami kolumn A i B, aby dostosować wszystkie kolumny do najlepszego dopasowania.

Kliknij na cegiełkę â2â po lewej stronie wiersza 2. Cały wiersz zostanie podświetlony.

Zamroź nagłówki kolumn, wybierając z menu głównego: Window - Freeze Panes

Teraz podświetl kolumnę AMOUNT klikając na nagłówek kolumny âEâ. Następnie z menu:

Format â Komórki â Liczba - Dec Miejsca 2 - 1000 separator? TAK - â1234.10 na dole na czerwono

Wyśrodkuj te kolumny: NLAC, PD, TRAN, DEPT, NLTYP, ACTYP, SGGPNO, SGTPNO, SPN

Wyrównanie do lewej te kolumny: REFNO, ACCTANAL1, ACCTANAL2. ACCTANAL3, ACCTNO

Kliknij z powrotem na komórkę A2.

Na koniec wybierz: Plik - Zapisz jako: SAGEPIVOT

Zapisz jako typ: zmień na skoroszyt Excela

Uwaga: oryginalny plik SAGEPIVOT.CSV pozostaje niezmieniony. Jeśli chcesz wykonać tutorial po raz drugi, nadal masz na dysku plik SAGEPIVOT.CSV w oryginalnej postaci.

A. 2. ZAWARTOŚĆ BAZY DANYCH

Excel potrafi bezpośrednio odczytywać pliki CSV, więc plik SAGEPIV.CSV otworzył się od razu. W programie SagePivot każdy rekord zawiera w sumie 32 pola. W arkuszu kalkulacyjnym rekordy są wyświetlane jeden pod drugim, jeden na wiersz (znany jako âList viewâ). To sprawia, że łatwo jest zobaczyć wiele rekordów na raz, ale trudno jest zobaczyć długie rekordy, takie jak te, ponieważ nie można wyświetlić wszystkie pola na ekranie. Dlatego spróbuj wyświetlić rekordy w tradycyjnym widoku wprowadzania danych âForm view’ w następujący sposób:

Kliknij w dowolnym miejscu arkusza Wybierz z menu: Dane - Formularz

W widoku âFormatâ pojawia się jeden rekord. W prawym górnym rogu widnieje napis Rekord 1 z 2052.

Kliknij dwukrotnie na Znajdź następny. Pojawia się rekord 3 z 2052.

Patrząc na ten rekord (âMcNally Machine), w zasadzie pierwsze 11 pól aż do DEPT to szczegóły wprowadzane za każdym razem, gdy wpisujesz transakcję. Pola po tym są polami analizy, które będą przydatne podczas podsumowywania danych. Pola te to:

NLAC = kod konta nominalnegoNL_SHORT
= pierwsze 15 liter opisu konta nominalnego (NLNAME)
ACCT_SHORT = pierwsze 15 liter nazwy dostawcy lub odbiorcy (ACCTNAME)
DETAILS = narracja wpisywana przez księgowegoAMOUNT


=



kwota transakcjiREFNO



= numer referencyjny



transakcji
, czyli np. nr faktury, nr czeku itp**PD




=





okres w roku finansowym firmy
NO






= numer referencyjny transakcji.






Jest to unikalny numer sekwencyjny automatycznie przypisany do transakcji przez Sage.
TRAN = typ transakcji. SI = Faktura Sprzedaży, JD = Obciążenie Dziennika, etcDATE =
Data transakcji (tj. data wpisana przez księgowego).
DEPT = kod wydziału (jeśli istnieje) wpisany przez księgowegoDEPTNAME =
nazwa wydziału
**FINYR = rok obrotowy firmy***BKPAGE

=


numer strony wyciągu bankowego (patrz sekcja B 10)
TC = kod podatku, np. 1 = 17,5% VAT, 0 = zerowa stawka VAT itd*DATENT



= data wprowadzenia




transakcji (




tzn.





BILANS = bieżące saldo na rachunku nominalnymNLTYP
=

typ

rachunku
nominalnego
(rachunek zysków i strat PL lub bilans BS)
ACTYP = inny typ rachunku nominalnego (rachunek bankowy, kontrolny lub


księgowy


)
DEL = transakcja usunięta? 1 = Tak 0 = Nie*SAGEGROUP =
grupa nominalna (np. Koszty administracyjne)
*SGGPNO = numer grupy nominalnej (wyłącznie do celów sortowania)
*SAGETOPGROUP = nominalna TopGrupa (np. Koszty ogólne


,


Środki Trwałe)
*SGTPNO = numer górnej grupy (wyłącznie do celów sortowania)
NLNAME = pełny nominalny opis kontaCOA

=


plan kont zawierający SAGEGROUP i SAGETOPGROUPACCTANAL1


=



kod analizy 1 w zapisie klienta lub dostawcyACCTANAL2



= kod analizy




2 â




â â





ACCTANAL3 = kod analizy 3





â â â â






ACCTNO = kod konta dostawcy lub klienta**SPN






=







âSâ dla Sprzedaży, âPâ dla Purchases, âNâ dla NominalACCTNAME =
pełna nazwa dostawcy lub klienta






























[Uwaga dla użytkowników SagePivot: Pozycje oznaczone * przechodzą przez SagePivot tylko wtedy, gdy używasz wersji 6 Line 50. Jeśli używasz wersji 5, będą one wyświetlane jako puste pola. Istnieją niespójności w sterownikach Sage ODBC. Elementy oznaczone ** nie są obecne w wersji 1 SagePivot, ale są planowane w wersji 2. Pozycja oznaczona *** (BKPAGE â strona wyciągu bankowego) nie jest dostępna w Sage.ale powinna być (patrz sekcja B. 10 w celu dalszej dyskusji).

Na koniec wybierz Zamknij, aby powrócić do zwykłego widoku arkusza kalkulacyjnego âListâ.

A. 3 PORUSZANIE SIĘ PO PLIKU

Kliknij na A2. Teraz przejdź do dolnej części pliku w następujący sposób:

Znajdź drugi klawisz âCtrlâ znajdujący się w prawym dolnym rogu klawiatury pod klawiszem Enter. Przytrzymaj go kciukiem prawej ręki.

Teraz dotknij strzałki w dół środkowym palcem prawej ręki. Przejdziesz do dolnego wiersza, 2053.

Wyrób sobie nawyk robienia Ctrl+Home, Ctrl+strzałka w dół, Ctrl+strzałka w dół itd. tylko prawą ręką, nie obiema.

Teraz wróć na górę pliku, do komórki A2. (przytrzymaj prawy klawisz Ctrl - naciśnij strzałkę w górę - puść klawisz Ctrl - raz strzałkę w dół)

A.4. DWUETAPOWY PROCES â BADANIE RACHUNKOWOŚCI I SPRAWOZDAWCZOŚĆ FINANSOWA

Sporządzenie zestawu sprawozdań finansowych jest procesem dwuetapowym. Po pierwsze, należy sprawdzić transakcje w celu zidentyfikowania i skorygowania wszelkich błędów (etap "audytu"). Następnie przechodzi się do etapu drugiego i wyodrębnia się sprawozdania finansowe, takie jak sprawozdanie z zysków i strat oraz bilans.

Zwykle więc dane z Sage są eksportowane dwukrotnie. Na pierwszej kopii danych przeprowadziłbyś kontrolę audytową, znalazłbyś błędy, a następnie poprawiłbyś je w Sage. Następnie, wiedząc, że Sage jest teraz dokładny, wyeksportowałbyś dane z niego po raz drugi i z tej drugiej kopii stworzyłbyś raporty finansowe.

Nie jest to praktyczne, aby zrobić to w samouczku. Dlatego też, gdy znajdziemy błąd, poprawimy go w Excelu i będziemy kontynuować. Pamiętaj jednak, że w prawdziwym przypadku dokonałbyś poprawek w Sage, a nie w Excelu, a następnie ponownie wyeksportował dane do Excela po raz drugi.

W poniższym samouczku, sekcje oznaczone literą B dotyczą etapu audytu. Sekcje oznaczone literą C dotyczą drugiego etapu, tworzenia sprawozdań finansowych. Każda sekcja jest samodzielna, więc jeśli brakuje Ci czasu lub chciałbyś zobaczyć tylko stronę poświęconą sprawozdawczości finansowej, możesz od razu przejść do sekcji C.1 Sporządzanie bilansu.

CZĘŚĆ PIERWSZA - BADANIE SPRAWOZDAŃ FINANSOWYCH

B. 1 SPRAWDZANIE ZAWARTOŚCI KOLUMNY "DATA

Zawsze należy sprawdzić, czy kolumna DATE zawiera prawidłowe daty. (Pojedyncza pusta lub nieprawidłowa wartość daty uniemożliwi funkcji Group grupowanie dat w miesiące i pojawi się komunikat o błędzie â âCannot Group That Selectionâ).

Kliknij komórkę J10 w kolumnie DATE. Kliknij ikonę AZ. Baza danych zostanie posortowana w kolejności DATE.

Spójrz na pierwszą datę w wierszu 1. Jest to 31/12/98. Wygląda to dobrze.

Teraz Ctrl-Down Arrow, aby przejść do dolnego wiersza 2053. Są tam dwie daty 27/09/09!

Jako zasadę należy przyjąć, że jedna data na tysiąc zostanie wprowadzona z błędnym rokiem. Takie błędy są łatwe do wychwycenia, ponieważ pojawiają się na samym początku lub końcu posortowanego pliku.

W prawdziwym życiu poprawiłbyś te daty i wszelkie inne błędy w samym Sage Line 50 poprzez: menu Plik â Konserwacja â Korekty. Jednak na potrzeby tego ćwiczenia poprawimy daty w Excelu.

Zmień 27/09/09 na 27/09/99 w obu przypadkach.

Dodatkowo podświetl komórki M2050 i M2051 w kolumnie FINYR.

Skopiuj 99_00 do komórek M2052 i M2053 poniżej.

Wróć do komórki A2 za pomocą kombinacji klawiszy Ctrl+Home.

Teraz posortuj bazę danych w pierwotnej kolejności. Wybierz: Data - Sort â Sort by NLAC - Then By: DATE

B. 2 ODTWORZENIE BILANSU PRÓBNEGO

Naszym pierwszym zadaniem jest odtworzenie raportu Sage Trial Balance. Będzie to dowód na to, że wszystkie transakcje zostały poprawnie przeniesione z Sage do Excela. Zrobimy to za pomocą tabeli przestawnej.

Kliknij w dowolnym miejscu na arkuszu, a następnie z menu wybierz:

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

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

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

[Sprawdź dokładnie ten zakres. Pierwsza połowa powinna zawsze wynosić $A$1. Druga połowa powinna zawierać ostatni wiersz, który zanotowałeś â 2053. Jeśli liczba jest wyższa, np. 2054, oznacza to, że tabela przestawna zawiera pusty rekord i polecenie Grupuj nie powiedzie się, gdy spróbujesz pogrupować pole Data na miesiące.

Zdarza się również, że pole Zakres jest puste lub zawiera napis âDatabaseâ. W takim przypadku należy kliknąć wskaźnikiem myszy wewnątrz pola i usunąć jego zawartość. Teraz kliknij na komórkę A1. Wpisz dwukropek. Przytrzymaj klawisz Ctrl i wciśnij klawisz âEndâ. W ten sposób powinien zostać wyświetlony zakres SagePivot!$A$1:$AF$2053].

Kliknij przycisk Dalej.

Krok 3 z 4: Pojawia się okno COLUMN-ROW-DATA. Po prawej stronie znajdują się wszystkie nagłówki kolumn Twojego arkusza kalkulacyjnego [uwaga: jeśli korzystasz z Excela 2000, musisz najpierw kliknąć na Układ].

[Jeśli zamiast tego pojawi się komunikat âA pivot table field name is not validâŚ.â, to albo brakuje jednego z nagłówków kolumn, albo Twój Zakres zawiera pustą kolumnę po prawej stronie (która oczywiście nie ma nagłówka). Anuluj, a następnie sprawdź Wiersz 1 i skrajnie prawą kolumnę Zakresu].

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

NLTYP do obszaru ROW
NLAC do obszaru ROW poniżej NLTYP
NL_SHORT do obszaru ROW poniżej NLAC
BALANCE do obszaru ROW poniżej NL_SHORT


AMOUNT do obszaru DATA.

[AMOUNT powinien teraz zawierać napis âSum of AMOUNTâ. Jeśli jest napisane âLiczba KWOTâ, kliknij dwukrotnie na Liczbę KWOT. Pojawi się okienko. Zmień âCountâ na âSumâ]

Teraz w obszarze ROW kliknij dwukrotnie na NLAC Pojawi się pole tabeli przestawnej.

W obszarze Sumy częściowe po lewej stronie zmień z Automatycznie na Brak. OK

Zmień również wartość sum częściowych w polach NL_SHORT i BALANCE z automatycznej na Brak.

(Jako ogólną zasadę przyjmij, że zawsze będziesz musiał ustawić pola ROW na âNo Subtotalsâ).

Teraz kliknij w Zakończ. Zostanie wygenerowana tabela przestawna [Excel 2000, OK - Zakończ].

B. 3 FORMATOWANIE TABELI PRZESTAWNEJ

Najpierw zajmiemy się uporządkowaniem tabeli przestawnej. Kliknij prawym przyciskiem myszy dowolną liczbę w kolumnie âTotalâ E.

Pojawi się menu. Na górze lub w środku znajduje się âFormatuj komórkiâ.

Z menu wybieramy: Pole. [Excel 2000 = Ustawienia pola, Excel 5 lub 7 = Pole tabeli przestawnej].

Pojawia się okno dialogowe âPivot Table Fieldâ. Z listy opcji po prawej stronie wybierz: Liczba

Pojawia się okno dialogowe âFormatuj komórkiâ. Z listy âKategoriaâ wybierz: Number Następnie:

Decimal Places = 2 - Use 1000 separator? TAK - klikamy na czerwono na -1234,10 na dole listy - OK - OK.

Tabela przestawna pokazuje teraz wszystkie liczby z dokładnością do 2 miejsc po przecinku, a kredyty są na czerwono z minusami.

Teraz kliknij lewym przyciskiem myszy na NLAC w komórce B2. Kolumna zostanie podświetlona. [Excel 2000, patrz niżej]

[Excel 2000 - zwróć uwagę, że odwołania do komórek w Excelu 2000 są zwykle dwa wiersze niżej niż w Excelu 97
.Przesuń wskaźnik myszy nad NLAC w komórce B4, aż pojawi się czarna strzałka w dół, a następnie kliknij lewym przyciskiem myszy. Kolumna zostanie podświetlona].

Wyśrodkuj kody kont nominalnych, klikając ikonę Wyśrodkuj.

[Uwaga: Jeśli nie możesz uzyskać efektu podświetlenia lub czarnej strzałki w dół, być może masz wyłączoną funkcję Zaznaczanie. Aby ją włączyć: Kliknij prawym przyciskiem myszy na menu - Select - Enable Selection]

Teraz w ten sam sposób podświetl kolumnę BALANCE. Następnie z menu:

Format - Komórki - Liczba - Miejsca dziesiętne = 2 - 1000 separator? TAK -1234,10 na czerwono OK - OK.

Poszerz kolumnę C, NL_SHORT

Kliknij w cegiełkę â5â po lewej stronie wiersza 5. [Excel 2000 â cegiełka â7â] Wiersz jest podświetlony

Wybierz: Okno - Zamroź szyby

B. 4 UZGADNIANIE SALDA PRÓBNEGO

Najpierw musisz sprawdzić, czy sumy obliczone przez tabelę przestawną zgadzają się z saldami w Sage:

Kliknij w komórkę F3 obok 50.000 [Excel 2000 = komórka F5. Odwołania do komórek są dwa wiersze niżej].

Z menu głównego wybierz: Wstaw - Funkcja.

W okienku znajdują się dwie listy. Z prawej listy zaznacz IF i kliknij OK.

Wpisujemy: Test logiczny : d3=e3 [Excel 2000 d5 = e5]
Wartość jeśli prawda: ok
Wartość jeśli fałsz: błąd OK âokâ pojawia się w F3

.

Kliknąć na OK w F3. Umieść wskaźnik myszy w dolnym prawym rogu F3, aż stanie się on czarnym krzyżykiem. Podwójne kliknięcie myszką. Formuła zostaje skopiowana do dolnej części arkusza.

Wszystkie wartości w kolumnie F są OK, z wyjątkiem sum cząstkowych BS i PL, które sumują się do 46.906,26.

Dane zostały zaimportowane poprawnie i można przejść do następnego etapu.

[Zauważ, że nie jest to do końca poprawne. Na kontach PL SALDO powinno tak naprawdę być równe sumie transakcji tylko dla tego roku obrotowego. Ale nie było możliwości dostosowania do tego danych demonstracyjnych Sage].

Teraz usuń kolumnę F zawierającą âOKâ's. (Podświetl kolumnę F. Naciśnij klawisz Delete)

Kliknij prawym przyciskiem myszy na menu - Kreator - usuń BILANS z obszaru ROW. Zakończ.

Na koniec kliknij prawym przyciskiem myszy na âSheet1â na dole tego arkusza. Zmień nazwę tego arkusza na TB

B. 5 POKAZANIE BILANSU PRÓBNEGO WEDŁUG MIESIĘCY

Mając teraz sprawdzone salda danych, podzielimy je według miesięcy.

Kliknij prawym przyciskiem myszy na tabeli przestawnej, aby uzyskać menu. Wybierz: Kreator [Excel 5 lub 7 â Tabela przestawna]

Przenieś NLTYP z obszaru ROW do obszaru PAGE.

Upuść FINYR do obszaru KOLUMNA. (znajduje się on na górze czwartej kolumny nazw pól)

Upuść PD w obszarze KOLUMNA na prawo od FINYR. Zakończ

Tabela przestawna została przeliczona i pokazuje rok 98_99, okresy od 06_grudnia do 12_czerwca, a następnie rok 99_00, okresy od 01_lipca do 06_grudnia (pamiętaj, że Twój rok finansowy zaczyna się w lipcu).

Uwaga 1: W tym miejscu zilustrujemy funkcje Cofnij i Ponów w Excelu, które są bardzo przydatne w przypadku popełnienia błędu, szczególnie w przypadku tabel przestawnych:

Kliknij na ikonę Cofnij (strzałka wygięta w lewo). Bilans handlowy pojawi się ponownie. Kliknij na nim ponownie.

Kliknij teraz na ikonę Ponów (strzałka obok wygięta w prawo). Kliknij ponownie. Okresy wracają.

Jeśli w tym poradniku wprowadzisz błędną instrukcję, użyj ikony Cofnij, aby anulować błąd.

Uwaga 2. Zwróć również uwagę na wartość pola NL_SHORT. Długość pola NL_SHORT wynosi 15 znaków w porównaniu z 35 znakami dla pełnej nazwy NL_NAME w kolumnie Z, co oznacza, że możesz zmieścić więcej kolumn na ekranie.

Chcemy zobaczyć tylko dane z tego roku finansowego. Dlatego: [Excel 5, Excel 7 lub Excel 97].

Kliknij dwukrotnie prawym przyciskiem myszy na szary przycisk pola FINYR w C3. Pojawi się menu. Wybierz: Pole

W polu Ukryj elementy na dole po lewej stronie zaznacz 98_99 i OK.

Rok i miesiące dla 98_99 znikną. Rok 99_00 od lipca do grudnia pozostaje. .

[Excel 2000] Na szarym przycisku pola FINYR w C3 znajduje się strzałka w dół. Kliknij na nią.
Usuń zaznaczenie przy 98_99. OK
Rok i miesiące dla 98_99 znikają. Rok 99_00 od lipca do grudnia pozostaje. .

Obecnie mamy dwa zestawy sum głównych w kolumnach I i J. Potrzebujemy tylko jednego.

Kliknij dwukrotnie prawym przyciskiem myszy na FINYR w C3. Wybierz: Pole [lub Ustawienia pola].

Zmień opcję Sumy częściowe z Automatycznie na Brak. OK Jeden zestaw sum częściowych został usunięty.

B. 6 WYŚWIETLANIE NAGŁÓWKÓW KOLUMN (tylko Excel 97 i Excel 2000)

Aby poprawić wygląd nagłówków kolumn miesięcznych, wykonamy następujące czynności:

[Excel 97] Kliknij lewym przyciskiem myszy przycisk pola PD w komórce D3. Wszystkie miesiące zostaną podświetlone
lub
[Excel 2000] Przesuń wskaźnik myszy nad PD w

komórce D3.

Przesuwaj go stopniowo w dół, aż zmieni się w grubą pionową strzałkę w dół. Kliknij lewym przyciskiem myszy. Wszystkie miesiące zostaną podświetlone.

[Kliknij żółtą ikonę âFill Colorâ w dolnym rzędzie ikon, drugą od prawej (tę, która wygląda, jakby wylewała puszkę farby).

Kliknij na ikonę środka. Następnie kliknij ikonę B, aby je pogrubić.

Kliknięcie w dowolnym miejscu spowoduje usunięcie podświetlenia. Miesiące są wyśrodkowane, pogrubione i zacienione na żółto.

B. 7 WYSZUKIWANIE PODEJRZANYCH SALD

Teraz należy sprawdzić dane miesięczne, w szczególności konta P & L.

Kliknąć na strzałkę w dół w B1 , obok NLTYP. Z listy należy wybrać: PL.

Widzimy rachunki P&L za ostatnie 6 miesięcy, od lipca do grudnia.

Przeglądając dane miesięczne, płaca brutto w wysokości 16.200 za lipiec w C16 wydaje się być podwojona. Również wartość czynszu za wrzesień w pozycji E21 wydaje się zaniżona.

Kliknij dwukrotnie na 16 200,00 w komórce C16 â Płace brutto. Zostanie wygenerowany Arkusz 2.

Wydaje się, że wynagrodzenia za czerwiec zostały zaksięgowane dwukrotnie, raz w dzienniku 621, raz w dzienniku 664.

Klikamy z powrotem na arkusz TB.

Teraz kliknij dwukrotnie na pozycję â50 w E21 dla czynszu za wrzesień. Zostanie wygenerowany kolejny arkusz drill down.

W arkuszu znajdują się 4 wpisy za wrzesień. Wygląda na to, że coś poszło nie tak z przedpłatami.

Klikamy z powrotem na arkusz TB.

Usuń dwa utworzone przed chwilą arkusze drill down. (kliknij prawym przyciskiem myszy na âsheet2â menu, Delete, OK)

Po usunięciu drugiego arkusza drilldown wracasz do arkusza TB.

B. 8 ANALIZA DANYCH WEDŁUG RODZAJU TRANSAKCJI

Ważną cechą, na którą należy zwrócić uwagę, jest to, że używając TRAN jako pola strony, można sprawić, że Excel będzie natychmiast obliczał miesięczne sumy nominalne dla poszczególnych typów transakcji, takich jak faktury zakupu lub drobne płatności gotówkowe. Pomaga to sprawdzić, czy zostały one poprawnie przeanalizowane do wartości nominalnej.

Kliknij prawym przyciskiem myszy na tabelę przestawną TB, aby wyświetlić menu. Wybierz: Kreator [Excel 5 lub 7 â Tabela przestawna].

Upuść TRAN w obszarze PAGE pod NLTYP. Zakończ.

Tabela przestawna pozostaje niezmieniona, ale TRAN pojawił się jako pole Strona w komórce A2.

Kliknij strzałkę w dół w komórce B1 obok NLTYP. Zmień PL na All. Pojawiają się ponownie konta B/Sheet

Klikamy na strzałkę w dół w B2 obok TRAN. Pojawia się lista typów transakcji.

Przewiń listę w dół i wybierz PI (Purchase Invoices).

Tabela przestawna pokazuje teraz miesięczne sumy analizy nominalnej tylko dla faktur zakupu.

Pojawia się oczywisty błąd: 200 zł za sprzedaż S.E. we wrześniu nie powinno znaleźć się na fakturze zakupu.

Kliknij dwukrotnie liczbę 200 w komórce E9. Pojawia się arkusz z wierszami. Faktura od Wallace Office Equipment za âusługiâ została przeanalizowana do konta sprzedaży. Oczywiście jest to błąd. Poprawimy to.

Klikamy na arkusz SAGEPIVOT. Kliknij komórkę C10 w kolumnie ACCT_SHORT.

Kliknij ikonę AZ. Baza danych jest posortowana w kolejności Account Name, zaczynając od ABS Garages.

Przejdź w dół do grupy transakcji Wallace Office Equipment, zaczynając od wiersza 977.

Znajdź wiersz 997. Faktura jest analizowana pod kątem 4003, Sprzedaż południowo-wschodnia. Normalnie byłoby to 5000, Materials Purchased, jak w wierszu 998 poniżej.

Jak wspomniano wcześniej, normalnie poprawiłbyś błąd w Sage, a następnie ponownie uruchomił program SagePivot z czystymi danymi źródłowymi. Aby jednak zachować ciągłość prezentacji, poprawimy go teraz w Excelu.

Zmień analizę nominalną na 5000, Zakup materiałów, w następujący sposób:

Skopiuj komórki A998 i B998 na A997 i B997.

Skopiuj również komórki od U998 do Z998 na od U997 do Z997.

Wróć do arkusza TB. Kliknij prawym przyciskiem myszy na menu: Odśwież dane. 200 zniknie.

Usuń arkusz drilldown, który właśnie utworzyłeś dla tej transakcji (arkusz4?).

Wróć do arkusza TB, kliknij na strzałkę w dół w B2 obok TRAN (obecnie jest to PI).

Przejdź na górę listy i wybierz (All). Pojawi się ponownie kompletny bilans handlowy.

B. 9 UZGADNIANIE KONTROLI DŁUŻNIKÓW Z RAPORTEM WIEKOWYCH DŁUŻNIKÓW

Kluczowym zadaniem podczas audytu jest potwierdzenie, że suma sald kont klientów na raporcie Wiekowi dłużnicy w Księdze Sprzedaży zgadza się z saldem na kontrolce Dłużnicy w Księdze Nominalnej.

Załóżmy, że w tym przypadku się nie zgadzają. Łączna wartość w raporcie Wieloletnich Należności wynosi 32 214,32£, podczas gdy w Kontrolce Należności jest to 31 589,32£, co oznacza rozbieżność o 625£. Analiza konta Kontroli Dłużników za pomocą tabeli przestawnej zwykle pozwala znaleźć przyczynę problemu.

Utworzymy podsumowanie konta kontrolnego dla konta 1100, Kontrola należności. Najszybszym sposobem jest wykonanie kopii tabeli przestawnej TB, w następujący sposób:

Kliknij prawym przyciskiem myszy na TB w dolnej części arkusza. Wybierz: Przenieś lub Kopiuj.

Pojawi się okno Przenieś lub kopiuj. Zaznacz pole Utwórz kopię na dole po lewej stronie. OK

Zostanie utworzony nowy arkusz TB(2). Kliknij ponownie prawym przyciskiem myszy na menu i zmień nazwę na Dłużnicy.

Kliknij prawym przyciskiem myszy na tabeli przestawnej, aby uzyskać menu. Wybierz: Kreator. Pojawi się ekran ROW-COLUMN-DATA.

Przeciągnij i upuść FINYR i PD z obszaru KOLUMNY.

Przenieś TRAN z obszaru STRONA do obszaru KOLUMNA.

Usuń NLTYP z obszaru STRONA. Przenieś NLAC do obszaru STRONA.

Przeciągnij i upuść DATE nad NL_SHORT w obszarze ROW. Zakończ.

Tabela przestawna została przeliczona. Wygląda ona nieco chaotycznie. Nie przejmuj się.

Najpierw kliknij strzałkę w dół obok NLAC w B2. Z listy kont nominalnych wybierz 1100.

Kontrola dłużników pojawia się w całej kolumnie B. Jednak pole DATE jest odejmowane.

Kliknij dwukrotnie lewym przyciskiem myszy na polu DATE w komórce A5. Pojawi się pole tabeli przestawnej.

Zmień opcję Sumy częściowe z Automatycznie na Brak. OK

Teraz kliknij dwukrotnie prawym przyciskiem myszy na polu DATE w komórce A5. Wybierz: Group and Outline â Group.

W polu Grupowanie wybierz Miesiące i Lata (pod Kwartałami) . OK

Mamy miesięczne podsumowanie kontrolne dla Konta Kontrolnego Dłużników pokazujące, od prawej do lewej, sumę wpływów, faktur, rabatów, not uznaniowych, wpłat na konto i dzienników.

Zacieniuj na żółto, wyśrodkuj i wytłuść nagłówki kolumn ( JC, JD, SA itd.). (Zobacz sekcję B6, jeśli nie pamiętasz, jak to zrobić).

Winowajca znajduje się w D17 wśród czasopism. W listopadzie ktoś zaksięgował dziennik nominalny bezpośrednio na koncie Debtors Control. Kliknij dwukrotnie na -625 Journal Credit w D17, aby zobaczyć szczegóły.

W arkuszu drill down widać, że była to przedpłata czynszu, nr dziennika. 931. Został on omyłkowo zaksięgowany na koncie 1100 "Kontrola dłużników", podczas gdy powinien być zaksięgowany na koncie 1103 "Przedpłaty".

Tak jak poprzednio, dokonamy korekty w programie Excel. Klikamy na arkusz SAGEPIVOT.

Klikamy na komórkę F10 w kolumnie "Nr ref. Następnie klikamy na ikonę AZ.

Przewiń w dół do wiersza 1648, w którym znajduje się dziennik 931 w grupie transakcji âPREPAYâ.

W dzienniku 931 zmień pozycję 1100 Dłużnicy na 1103 Przedpłaty.

Teraz kliknij ponownie na arkusz Dłużnicy. Kliknij prawym przyciskiem myszy, aby uzyskać menu. Odśwież dane

Z listopadowych JC zniknęło -625. Suma całkowita w K19 koryguje się do 32 214,32, co zgadza się z sumą sald księgi sprzedaży.

Usuń arkusz drill down utworzony dla Przedpłaty czynszu.

B. 10 WYKRYWANIE BŁĘDÓW W UZGADNIANIU KONT BANKOWYCH

Kolejnym kluczowym zadaniem audytowym jest uzgodnienie salda bankowego z saldem na wyciągu bankowym. Często popełniane są błędy we wcześniejszych uzgodnieniach bankowych, tj. pozycje zostały zaznaczone, które nie powinny być zaznaczone i odwrotnie. Zanim bank dokona uzgodnienia, błędy te muszą zostać wykryte i usunięte.

Można użyć tabeli przestawnej do wykrycia błędów uzgodnienia bankowego, o ile program uzgodnienia bankowego zapisał odniesienie do wyciągu bankowego (na przykład numer strony wyciągu).
Niestety, w Sage, gdy zaznaczasz płatność podczas uzgadniania bankowego, nie ma przepisu, aby zapisać numer strony wyciągu lub inne odniesienie. W tym tutorialu założyłem jednak, że Sage pozwala na zapisanie numeru strony wyciągu bankowego podczas uzgadniania i w kolumnie BKPAGE N w SAGEPIVOT zapisałem numery stron wyciągu bankowego dla większości transakcji bankowych.

Używając tabeli przestawnej możesz teraz szybko posortować konto bankowe do tej samej kolejności stron, co wyciąg bankowy. Wszelkie błędy można wykryć porównując saldo bieżące na końcu strony na wyciągu bankowym z saldem bieżącym na końcu strony w tabeli przestawnej. Jeśli zaczynają się one różnić, oznacza to, że dana strona zawiera błąd.

Odtwórz wyciąg bankowy w następujący sposób:

Kliknij prawym przyciskiem myszy na dole arkusza Dłużnicy. Wybierz: Przenieś lub Kopiuj.

Pojawi się okno Przenieś lub kopiuj. Zaznacz pole Utwórz kopię na dole po lewej stronie. OK

Zostanie utworzony nowy arkusz - Dłużnicy(2). Zmień jego nazwę na Bank

Najpierw kliknij strzałkę w dół w B2 obok NLAC. Z listy kont wybierz 1200.

Bank Current Ac pojawia się w całej kolumnie C.

Kliknij prawym przyciskiem myszy, aby wyświetlić menu. Wybierz: Wizard. Pojawia się ekran ROW-COLUMN-DATA.

Przeciągnij i upuść TRAN z obszaru COLUMN.

Usuń Years i DATE z obszaru ROW.

Przeciągnij i upuść BKPAGE (tzn. âBank Pageâ) pod NL_SHORT w obszarze ROW. Zakończ

Tabela przestawna została przeliczona i pokazuje wartość każdej strony wyciągu bankowego, strony od 242 do 254.

W komórce D5 po prawej stronie słowa "Suma" wpisz słowo "Saldo".

W komórce D6, naprzeciwko liczby 1076,84, wpisz formułę = C6 1076,84 pojawia się w komórce D6

W komórce D7 wpisz formułę = D6+C7 -4226,92.

Skopiuj formułę z D7 do D19 Stworzyliśmy ponownie zestawienie sald bieżących.

Salda w kolumnie D powinny być równe saldom na koniec każdej strony wyciągu bankowego. Tak więc saldo na końcu ostatniego wyciągu bankowego, strona 254, powinno wynosić 31 658,71.
Jeżeli saldo bieżące w kolumnie D zaczyna odbiegać od salda na wyciągach bankowych, na danej stronie pojawi się błąd uzgadniania.

Pozycja -18.551.04 w C19 nie ma strony z wyciągiem. Jest to wartość nierozliczonych czeków i pokwitowań.

Kliknąć dwukrotnie na -18.551.04.

Zostanie wygenerowany arkusz drill down. Jest to lista nieprzedstawionych płatności i pokwitowań.

Usunąć arkusz z wierszami. Nastąpi powrót do arkusza TB.

Po dodaniu nieprzedstawionych czeków (-18 551,04) do salda wyciągu (31 658,71) otrzymujemy saldo księgi kasowej (13 107,67).

Uzgadnianie salda z wyciągiem bankowym za pomocą konwencjonalnych metod może trwać godzinami. Dzięki tabelom przestawnym można to zredukować do minut. Jednak Państwa pakiet księgowy musi umożliwiać zapisanie odniesienia do wyciągu bankowego, gdy w czasie uzgadniania należy zaznaczyć płatność lub wpływ!

B. 11 SPRAWDZANIE DZIENNIKA ZAKUPÓW ZA POMOCĄ AUTOFILTRU

Oprócz tabel przestawnych, funkcje SORT i AUTOFILTR w Excelu pozwalają na bardzo szybkie przeszukiwanie tysięcy transakcji. Są one szczególnie przydatne przy skanowaniu dzienników i ścieżek audytu.

Kliknij na główną bazę danych SAGEPIVOT. Ctrl+Home, aby przejść do górnego wiersza.

Obecnie dane wyeksportowane z programu Sage są uporządkowane według kodów kont nominalnych (NLAC). Załóżmy, że chcesz obejrzeć transakcje w kolejności, w jakiej zostały wprowadzone. Aby to zrobić, wystarczy:

Kliknij komórkę H10, w kolumnie âNOâ. Następnie kliknij ikonę AZ.

Dane są teraz posortowane w kolejności âNOâ, czyli Numeru Transakcji.

Załóżmy, że chcesz teraz przyjrzeć się w szczególności fakturom zakupu. Wybierz: Dane - Filtr - Autofiltr.

Przy każdym nagłówku kolumny pojawiają się czarne strzałki w dół.

[Może się zdarzyć, że ekran przeskoczy teraz do najbardziej na prawo położonych kolumn w bazie danych, AA, AB, AC itd. Jest to irytująca usterka. Aby go usunąć, należy nacisnąć Ctrl + Home, aby powrócić do komórki A2].

Kliknij czarną strzałkę w dół w I1, w kolumnie âTRANâ. Pojawi się lista typów transakcji.

Z listy należy wybrać: PI (faktury zakupu). Teraz wyświetlane są tylko transakcje typu PI.

Chcemy przejrzeć Dziennik zakupów za miesiące tego roku obrotowego. Aby to zrobić:

Kliknij strzałkę w dół w komórce M1, w kolumnie FINYR. Wybierz 99_00.

Ctrl+Home, aby wrócić do komórki A2.

Teraz kliknij strzałkę w dół w komórce G1, kolumna PD. Pojawi się lista okresów w roku 99_00.

Klikamy na 01_Jul. Autofiltr pokazuje tylko lipcowe faktury zakupu.

Kliknij ponownie na strzałkę w dół w komórce G1. Tym razem wybierz: 03_Sep

Wyświetlone zostaną faktury za wrzesień. Zwróć uwagę, że na ekranie wyświetlana jest nazwa konta nominalnego oraz nazwa dostawcy, które są pomijane w dziennikach zakupów Sage.

Zauważ również, że strzałki w dół w G1 PD oraz w I1, TRAN są zacienione na niebiesko. Oznacza to, że na te kolumny nakładane są filtry.

Na koniec, z menu: Dane - Filtr - Pokaż wszystkie. Wszystkie transakcje zostaną wyświetlone na nowo.

B. 12 DODANIE IKONY "POKAŻ WSZYSTKIE" DO PASKA IKON

Excel posiada najczęściej używane ikony, które są wstępnie ustawione na pasku ikon u góry ekranu. Istnieje jednak jeszcze kilka innych przydatnych ikon. Jedną z nich jest ikona âPokaż wszystkoâ. Dzięki niej nie musisz klikać Data â Filter â Show All za każdym razem, gdy chcesz wyłączyć filtry. Przeniesiemy ją na pasek ikon i umieścimy na prawo od żółtej ikony âFill Colorâ:

Z menu wybierz: View - Toolbars - Customize (na samym dole). Kliknij zakładkę Polecenia.

Z listy po lewej stronie kliknij na Data Na liście po prawej stronie Show All jest wyświetlane piąte miejsce w dół.

Przeciągnij i upuść ikonę Pokaż wszystko tak, aby znalazła się pomiędzy ikonami Kolor wypełnienia i Duże A.

Zamknij okno dialogowe Customize.

B. 13 ZESTAWIENIE WSZYSTKICH TRANSAKCJI DLA JEDNEGO KONTA

Niektóre konta nominalne, np. Mispostings lub Suspense, wymagają dokładnego sprawdzenia. Dlatego:

Strzałki Autofiltr w dół powinny być nadal widoczne. Ctrl+Home, aby przejść do komórki A2.

Kliknij na strzałkę w dół w komórce B1 obok NL_SHORT. Pojawia się lista kont nominalnych.

Z listy wybierz: Mispostings Acc Pojawiają się wszystkie transakcje analizowane do Mispostings a/c.

Ponownie kliknij strzałkę w dół w komórce B1. Tym razem spójrz na konto Adjustments...

Podobnie, możesz chcieć przyjrzeć się wszystkim księgowaniom na jednym koncie dostawcy.

W tym celu należy kliknąć na Pokaż wszystko na pasku ikon. Wszystkie rekordy zostaną ponownie wyświetlone.

Teraz klikamy na strzałkę w dół w C1, w kolumnie ACCT_SHORT. Pojawia się lista nazw.

Wybrać McNally Machine. Pojawiają się wszystkie transakcje z udziałem McNally Machinery.

B. 14 WYŚWIETLANIE LISTY DZIENNIKÓW

Chcesz zobaczyć listę wszystkich dzienników nominalnych zaksięgowanych w tym roku finansowym.

Kliknij ikonę Pokaż wszystko, aby wyświetlić wszystkie rekordy. Następnie w kolumnie M wybierz: FINYR = 99_00.

Ctrl+Home, aby przejść do A2. Kliknij na strzałkę w dół w I1 TRAN. Pojawi się lista typów transakcji.

Aby zobaczyć dziennik w całości, musisz zobaczyć obie strony, tzn. zarówno JC, jak i JD.

W tym celu należy wybrać: (CustomâŚ) Pojawia się okienko Custom Autofilter.

W górnym polu LH, kliknij strzałkę w dół i zmień equals na begins with

W górnym polu RH wpisz: J następnie kliknij OK. Wyświetlone zostaną wszystkie transakcje JD i JC.

Zauważ, że czerwcowe pensje zostały wpisane dwukrotnie. Widzieliśmy to wcześniej w rozdziale B 7.

B. 15 ZNALEZIENIE CZEKU NA KONCIE BANKOWYM

Załóżmy, że na wyciągu bankowym znajduje się czek na kwotę 4 193,58 GBP i nie jesteśmy pewni, czy został on kiedykolwiek wprowadzony do systemu Sage. AUTOFILTR umożliwia natychmiastowe prześledzenie pojedynczej transakcji:

Kliknij na ikonę Pokaż wszystko, aby wyświetlić wszystkie transakcje.

Teraz kliknij strzałkę w dół w komórce B1, NL_SHORT. Pojawi się lista kont nominalnych.

Wybierz: Bank Current Ac. Autofiltr odfiltruje wszystko oprócz transakcji bankowych.

Teraz kliknij strzałkę w dół w komórce E1, w kolumnie KWOTA.

Pojawi się lista wszystkich wartości, które znalazł Excel. Z listy wybierz: â4,193.58

Excel znalazł ten wpis. W programie Sage była to transakcja nr 768, ale w REFNO nie było zarejestrowanego czeku.

Teraz wybierz: Data - Filter - Autofilter. Strzałki w dół znikają, a wszystkie transakcje pojawiają się ponownie.

Na tym kończymy część poświęconą audytowi. Teraz nadszedł czas na sporządzanie raportów finansowych.

Jeśli zaczynasz się męczyć i Twoja koncentracja spada, teraz jest dobry moment, aby zrobić sobie przerwę. Zapisz plik, a następnie wróć za około 20 minut i zacznij ponownie od tego momentu. Musisz być świeży.

CZĘŚĆ DRUGA â SPORZĄDZANIE SPRAWOZDAŃ FINANSOWYCH

C. 1 SPORZĄDZANIE BILANSU

Kliknij na główny arkusz SAGEPIVOT. Kliknij w dowolnym miejscu na dane. Z menu wybierz:

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

Pojawi się ekran Kreator tabel przestawnych, krok 1 z 4. Kliknij przycisk Dalej.

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

[Sprawdź dokładnie zakres. Pierwsza połowa powinna zawsze zawierać $A$1. Druga połowa powinna zawierać ostatni wiersz, który zanotowałeś â 2053. Jeśli liczba jest wyższa, np. 2054, oznacza to, że tabela przestawna zawiera pusty rekord i polecenie Grupuj nie powiedzie się, gdy spróbujesz pogrupować pole Data na miesiące.

Zdarza się również, że pole Zakres jest puste lub zawiera napis âDatabaseâ. W takim przypadku należy kliknąć wskaźnikiem myszy w polu i wymazać jego zawartość. Kliknij teraz na komórkę A1. Wpisz dwukropek. Przytrzymaj klawisz Ctrl i naciśnij klawisz âEndâ. W ten sposób powinien zostać wyświetlony zakres $A$1:$AF$2053].

Kliknij przycisk Dalej. Pojawi się komunikat: âYou will use less memory if â¦â¦...â Wybierz No.

Krok 3 z 4: Pojawia się pole COLUMN-ROW-DATA. Po prawej stronie znajdują się wszystkie nagłówki kolumn w arkuszu kalkulacyjnym.

Przeciągnij i upuść te nagłówki:

SGTPNO do obszaru ROWSGTOPGROUP do obszaru ROW
pod nimSGGPNO do
obszaru ROW pod

nimSAGEGROUP

do obszaru ROW pod


nimNLAC


do obszaru ROW pod



nimNL_SHORT



do obszaru ROW pod nim




.




NLTYP do obszaru PAGEDEPTNAME do obszaru
PAGE poniżej

AMOUNT do obszaru DATA

AMOUNT do obszaru DANE [Po upuszczeniu do obszaru DANE, AMOUNT powinna teraz mówić âSum of AMOUNTâ. Jeśli jest napisane âLiczba AMOUNTâ, kliknij dwukrotnie na Liczbę AMOUNT. Pojawi się okienko. Zmień âCountâ na âSumâ]

Na koniec wybierz Zakończ. Tabela przestawna zostanie obliczona.

[Jeśli zamiast tego pojawi się komunikat o błędzie âMicrosoft Excel nie może dokonać tej zmiany, ponieważ jest zbyt wiele elementów wiersza lub kolumnyâŚ.â, Twój komputer ma za mało pamięci, aby pomieścić wszystkie pola ROW.
Będziesz musiał je zmniejszyć. Zacznij od usunięcia NLAC lub NLNAME i sprawdź, czy to zadziała].

C. 2 SFORMATUJ TABELĘ PRZESTAWNĄ

Poszerz kolumny B i D.

Masz sumy częściowe na wszystkich polach wiersza, ale chcesz je mieć tylko na pierwszym polu, SGTPNO.

Aby usunąć sumy częściowe z pozostałych pól, najpierw kliknij dwukrotnie lewym przyciskiem myszy na polu SAGETOPGROUP w B5.

Pojawi się okno Pole tabeli przestawnej. Zmień opcję Sumy częściowe z Automatycznie na Brak. OK

W ten sam sposób usuń sumy częściowe z pól SGGPNO, SAGEGROUP i NLAC.

Kliknij prawym przyciskiem myszy w dowolnym miejscu kolumny Suma G. Z menu wybierz: Pole [lub Ustawienia pola].

Wybierz: Number - Number - Decimal Places = 0 - Use 1000 separator? TAK - -1234 na czerwono OK OK

Zacieniuj wiersze sum częściowych â1 Razemâ, â2 Razemâ w następujący sposób: (użytkownicy Excela 5/7 pomiń sekcję C 3)

[Excel 97
]Kliknij pozycję â1 Sumaâ w komórce A13 . Wszystkie wiersze sum częściowych są podświetlone
lub
[Excel 2000]
Przesuń wskaźnik myszy na szarą cegiełkę â13â naprzeciwko â1 Razemâ w komórce A13. Przesuwaj go stopniowo w prawo, aż przybierze kształt grubej poziomej czarnej strzałki. Następnie kliknij lewym przyciskiem myszy. Wszystkie linie sum częściowych zostaną podświetlone.



Aby dodać cieniowanie, kliknij strzałkę w dół na żółtej ikonie âKolor wypełnieniaâ. Z palety kolorów wybierz jasny turkusowy lub jasnozielony w dolnym rzędzie.

Kliknij w dowolnym miejscu, aby usunąć wyróżnienie. Wszystkie linie âTotalâ są cieniowane kolorem.

Często zdarza się, że nie można wyświetlić na ekranie wszystkich kolumn arkusza kalkulacyjnego. Można jednak wyświetlić kolumny znajdujące się najbardziej po prawej stronie, ukrywając inne kolumny.

Na przykład ukryjemy pole SGGPNO w kolumnie C:

Kliknij w górnej części kolumny C, aby ją podświetlić.

Następnie wybierz: Format - Kolumna - Ukryj. Kolumna C jest ukryta, a wraz z nią SGGPNO.

(Zarówno SGTPNO jak i SGGPNO istnieją tylko po to, aby posortować konta w odpowiedniej kolejności. Nie muszą być wyświetlane. Nie możemy jednak usunąć SGGPNO, ponieważ kolumna SAGEGROUP posortowałaby się wtedy w porządku alfabetycznym i stracilibyśmy prawidłową kolejność sortowania).

Wyśrodkuj kolumnę NLAC.

Kliknij cegiełkę â7â po lewej stronie komórki A7.

Wybierz: Okno - Zamroź panele

Kliknij strzałkę w dół w komórce B1. Wybierz BS. Zostanie wyświetlony Bilans.

Kliknij na Arkusz7 na dole tego arkusza. Zmień nazwę arkusza na BSHEET.

C.3 SPORZĄDZANIE SPRAWOZDANIA Z ZYSKÓW I STRAT

Następnie sporządzimy sprawozdanie z zysków i strat. Wykonaj kopię Bilansu w następujący sposób:

Kliknij prawym przyciskiem myszy na BSHEET na dole. Z menu wybierz: Przenieś lub Kopiuj

Pojawi się okno Przenieś lub Kopiuj. Zaznacz pole Utwórz kopię na dole po lewej stronie. OK

Zostanie utworzony nowy arkusz BSHEET(2). Zmień jego nazwę na PL

Kliknij strzałkę w dół w komórce B1. Wybierz PL. Zostanie wyświetlony rachunek zysków i strat.

Chcielibyśmy zobaczyć raport zysków i strat w rozbiciu na poszczególne miesiące.

Kliknij prawym przyciskiem myszy, aby wyświetlić menu. Wybierz: Kreator. Pojawi się ekran ROW-COLUMN-DATA.

Przeciągnij i upuść FINYR do obszaru KOLUMNY. Następnie upuść PD po jego prawej stronie. Zakończ.

Tabela przestawna zostanie przeliczona, wyświetlając rok i okresy u góry.

Chcemy zobaczyć tylko dane z tego roku finansowego. W związku z tym: [Excel 5, Excel 7 lub Excel 97].

Klikamy dwukrotnie prawym przyciskiem myszy na szary przycisk pola FINYR w G4. Pojawi się menu. Wybierz: Pole

W polu Ukryj elementy na dole po lewej stronie zaznacz 98_99 i OK.

Rok i miesiące dla 98_99 znikną. Rok 99_00 od lipca do grudnia pozostaje. .

[Excel 2000] Na szarym przycisku pola FINYR w G4 znajduje się strzałka w dół. Kliknij na nią.
Usuń zaznaczenie przy roku 98_99. OK
Rok i miesiące dla 98_99 znikają. Rok 99_00 od lipca do grudnia pozostaje. .

Może się zdarzyć, że nie będzie widać wszystkich kolumn. Zawęź kolumny od A do F tak bardzo, jak tylko możesz.

W polu FINYR jest suma częściowa (patrz kolumna M). Aby usunąć sumę częściową:

Kliknij dwukrotnie lewym przyciskiem myszy na polu FINYR. Zmień wartość sumy częściowej na Brak. OK Kolumna M zniknie.

Kolumna NLAC nie jest nam potrzebna. Kliknij na nagłówek kolumny âEâ.

Kliknij prawym przyciskiem myszy, aby wyświetlić menu. Wybierz: Ukryj [Zwróć uwagę, jak menu prawego przycisku myszy, jeśli klikniesz poza tabelą przestawną, różni się od menu prawego przycisku myszy, jeśli klikniesz wewnątrz tabeli przestawnej].

Podświetl nagłówki kolumn miesięcznych w następujący sposób: (tylko Excel 97 i Excel 2000)

[Excel 97] Kliknij lewym przyciskiem myszy przycisk pola PD w komórce H4. Wszystkie miesiące zostaną podświetlone
lub
[Excel 2000] Przesuń wskaźnik myszy nad przycisk

pola PD w komórce H4.

Przesuwaj go stopniowo w dół, aż zmieni się w grubą pionową strzałkę w dół. Kliknij lewym przyciskiem myszy. Wszystkie miesiące zostaną podświetlone.

[Kliknij strzałkę w dół przy ikonie âWypełnij koloremâ w dolnym rzędzie ikon, druga od prawej (ta, która wygląda, jakby nalewała puszkę farby). Wybierz z palety kolor żółty.

Kliknij w ikonę środka. Następnie kliknij w ikonę B, aby je pogrubić.

Kliknij w dowolnym miejscu, aby usunąć zaznaczenie. Miesiące są wyśrodkowane, pogrubione i cieniowane na żółto.

C. 4 TWORZENIE RAPORTÓW RENTOWNOŚCI DZIAŁÓW

Jedną z wielkich zalet tabel przestawnych jest to, że tak łatwo można tworzyć raporty działowe. A zatem:

Klikamy na strzałkę w dół w komórce B2. Pojawia się lista działów, Dział sprzedaży, Dział księgowości itd.

Tylko kilka rekordów w danych demonstracyjnych Sage zostało przeanalizowanych według działów, ale wystarczą one do zilustrowania ogólnej idei.

Z listy wybierz Sprzedaż. Zostanie wyświetlony miniaturowy raport zysków i strat dla działu sprzedaży.

Ponownie kliknij strzałkę w dół w komórce B2. Tym razem wybierz: Zakupy

Ponownie klikamy na strzałkę w dół w komórce B2 i z dołu wybieramy (puste)

Zostanie wyświetlony raport rentowności dla Centrali (bez kodu działu)

Na koniec kliknij ponownie na strzałkę w dół B2. Wybierz (Wszystkie). Zostaną ponownie wyświetlone sumy firmy.

Pole STRONA jest niezwykle potężnym narzędziem do analizy wyników firmy według centrum kosztów, działu, oddziału, wydziału, pracy itp.

C. 5 PODSUMOWANIE RAPORTU ZYSKÓW I STRAT NA RÓŻNYCH POZIOMACH

Pełny raport zysków i strat firmy rozciąga się do wiersza 37, poza dolną część ekranu.

My podsumujemy P&L na najwyższym poziomie. Kliknij dwukrotnie prawym przyciskiem myszy na SGTPNO w A6.

Z menu wybierz: Grupa i konspekt - Ukryj szczegóły

P&L jest podsumowany na poziomie Sprzedaży, Zakupów, Kosztów bezpośrednich i Kosztów ogólnych.

Możesz drążyć w dół każdej sekcji, aby zobaczyć więcej szczegółów. Podwójnie kliknij na â1â w A7.

Pojawiają się poszczególne konta sprzedaży. Kliknij w dowolnym miejscu, aby usunąć wszelkie podświetlenia.

Aby ponownie podsumować sprzedaż, kliknij dwukrotnie miejsce poniżej â1â, np. w komórce A8 lub A9.

Przyjrzyj się teraz szczegółom pozostałych trzech sekcji 2, 3 i 4.

Upewnij się, że tabela przestawna została ponownie podsumowana w postaci sum dla 1, 2, 3 i 4.

Tym razem wyświetlimy dane na poziomie SAGEGROUP. Kliknij dwukrotnie prawym przyciskiem myszy na SAGEGROUP w D6

Z menu wybierz: Grupa i konspekt â Pokaż szczegóły

P & L zostaje rozwinięty do najniższego poziomu (NL_SHORT)

Kliknij ponownie dwukrotnie prawym przyciskiem myszy na SAGEGROUP. Następnie wybieramy: Group and Outline â Hide Detail.

NL_SHORT jest puste i tym razem tabela przestawna jest podsumowywana na poziomie SAGEGROUP.

Dzięki tabelom przestawnym można wybrać wyświetlanie raportów finansowych na różnych poziomach szczegółowości w zależności od czytelnika.

C. 6 TWORZENIE RAPORTÓW DOTYCZĄCYCH KLIENTÓW I DOSTAWCÓW

Za pomocą tabel przestawnych można również tworzyć przydatne raporty analizy sprzedaży lub zakupów.

Najpierw wykonaj kopię tabeli przestawnej PL, w następujący sposób:

Kliknij prawym przyciskiem myszy na PL w dolnej części arkusza. Wybierz: Przenieś lub Kopiuj.

Pojawi się okno Przenieś lub kopiuj. Zaznacz pole Utwórz kopię na dole po lewej stronie. OK

Zostanie utworzony nowy arkusz PL(2). Zmień jego nazwę: CustSupp

Przedtem musimy ponownie wyświetlić wszystkie ukryte kolumny.

Podświetl cały arkusz klikając na górną cegiełkę LH powyżej 1 i na lewo od A. Następnie:

Wybierz: Format â Column - Unhide. Ukryte kolumny pojawią się ponownie.

Kliknij w dowolnym miejscu, aby usunąć podświetlenie. Następnie kliknij prawym przyciskiem myszy na tabeli przestawnej, aby uzyskać menu.

Wybierz: Kreator. Pojawi się ekran ROW-COLUMN-DATA.

Usuń DEPTNAME z obszaru STRONA.

Przeciągnij i upuść ACCT_SHORT do obszaru STRONA poniżej NLTYP.

Przeciągnij i upuść SPN do obszaru STRONA poniżej. (SPN, âSales, Purchase, Nominalâ, znajduje się po prawej stronie listy pól).

Usunąć wszystkie pola z obszaru ROW.

Przeciągnij i upuść ACCTNAME do obszaru ROW (jest to najdalej wysunięte pole w prawo)
REFNO do
obszaru ROW pod nim

DETAILS

do obszaru ROW pod nim


TRAN


do obszaru ROW pod nim

Zakończ. Tabela przestawna zostanie przeliczona. Dostosuj szerokość kolumn A, B i C.

Wszystkie pola wierszy są odejmowane. Kliknij dwukrotnie lewym przyciskiem myszy na polu ACCTNAME w A7.

Pojawia się pole tabeli przestawnej. Zmień wartość sum częściowych z Automatycznie na Brak. OK

Zrób to samo dla REFNO i DETAILS.

Wyrównaj do lewej REFNO (klikając na REFNO w B7, a NIE na nagłówek kolumny B).

Ponownie dopasuj szerokości kolumn od A do D.

Najpierw podsumujemy obroty według miesięcy. Kliknij dwukrotnie prawym przyciskiem myszy na ACCTNAME w A7

Z menu wybierz: Group and Outline - Hide Detail

Wyświetlony zostanie miesięczny obrót dla każdego konta.

Aby pokazać tylko zakupy, kliknij na strzałkę w dół obok SPN w B3. Z listy wybierz P

Wyświetlone zostaną tylko konta dostawców.

Teraz ponownie kliknij strzałkę w dół w B3. Wybierz S Wyświetlane są konta klientów.

Aby zobaczyć sprzedaż w szczegółach, kliknij dwukrotnie prawym przyciskiem myszy na ACCTNAME w A7.

Wybierz: Group and Outline â Show Detail. Pojawiają się poszczególne wiersze szczegółów.

Teraz kliknij na strzałkę w dół obok ACCT_SHORT w B2.

Z listy nazw wybierz Morley Solicitors. Wyświetlone zostaną poszczególne pozycje sprzedane do Morley.

Na tym kończymy część poświęconą sprawozdawczości finansowej oraz samouczek. Zapisz swoją pracę. Odpręż się.

DOKĄD ZMIERZASZ?
Mój pierwszy grudniowy samouczek dotyczący budżetowania zacząłem od stwierdzenia, że dla księgowego w Excelu tabele przestawne stanowią najważniejsze osiągnięcie w dziedzinie informatyki od czasu wynalezienia arkusza kalkulacyjnego. Niezależnie od tego, czy interesujesz się budżetowaniem, analizą sprzedaży czy sprawozdawczością finansową, mam nadzieję, że ta seria samouczków stanowi mocny argument.

Zwróciłem również uwagę na to, że tabele przestawne nie są nowością: są one dostępne od czasu wydania Excela 5 w 1994 roku. Niewiarygodne jest to, że wszystko, co zrobiłeś w tym poradniku, mogłeś zrobić w dowolnym momencie w ciągu ostatnich sześciu lat.

Dlaczego więc tabele przestawne zrobiły tak mały postęp? Problem leży po stronie sprzedawców pakietów księgowych. Każdy sprzedawca musi napisać program, który wyprowadzi dane z plików w formacie specjalnie nadającym się do analizy w tabelach przestawnych. To jest to, co zrobiliśmy dla Sage Line 50 z SAGEPIVOT i to jest to, co musisz uzyskać od swojego dostawcy pakietu, aby to zrobić.

Jeśli porozmawiasz o tym z dostawcami, wszyscy będą twierdzić, że ich pakiet łączy się z Excelem. Prawdopodobnie tak jest, ale prawdziwym testem jest: czy możesz nacisnąć przycisk w pakiecie księgowym, aby wyprowadzić pliki transakcji księgi nominalnej do Excela, a następnie uruchomić Excela, załadować plik wyjściowy i od razu zacząć stosować tabele przestawne? Jeśli musisz spędzić dużo czasu na porządkowaniu danych, gdy są już w Excelu, to najwyraźniej nie są one gotowe do pracy z tabelami przestawnymi.

JEŚLI TWÓJ PAKIET RACHUNKOWY TO SAGE LINE 50
.Jeśli używasz Sage Line 50, wersja 5 lub 6, Twoje następne działanie jest proste. Kup kopię programu SAGEPIVOT, który kosztuje zaledwie 50 zł. Można go pobrać z AccountingWEB, w sekcji Expert Guides, i wypróbować przez 21 dni bez opłat. Alternatywnie, wyślij e-mail na adres [email protected]. (Jeśli, nawiasem mówiąc, próbowałeś tego programu wkrótce po jego pojawieniu się na AccountingWEB i miałeś problemy, to prawdopodobnie dlatego, że przez pierwsze 4 dni towarzyszył mu niewłaściwy plik Word i nie zawierał instrukcji Excel. Proszę pobrać jeszcze raz pliki Worda i Excela).

JEŚLI TWÓJ PAKIET KSIĘGOWY NIE JEST SAGE LINE 50W
chwili pisania tego tekstu (marzec 2000) wiem o dwóch dostawcach oprogramowania księgowego, którzy podejmują poważne wysiłki w celu zintegrowania tabel przestawnych ze swoimi pakietami. Jednym z nich jest AccountView, holenderska firma, która jest dla mnie nowością, ale posiada imponujący ogólnoeuropejski produkt klasy średniej (biuro w Londynie: tel.: 0171-477-6524). W Wielkiej Brytanii celują szczególnie w rynek rejestracji czasu pracy/księgowości projektowej. Drugą firmą jest Open Accounts, która specjalizuje się w systemach księgowości korporacyjnej klasy średniej. Open Accounts łączy również swój pakiet z Microsoft OLAP SQL Server w celu obsługi dużych wolumenów związanych z większą firmą, ponieważ Excel ma limit 65.000 rekordów. (Open Accounts: tel.: 01327-301900).

Inni dostawcy mają różne oferty. Niektórzy oferują ekranowe funkcje raportowania podobne do tabel przestawnych, które są przeznaczone do dostarczania regularnych raportów dla menedżerów w całej firmie. Zdecydowanie istnieje na nie zapotrzebowanie, ale są one zwykle nieco nieelastyczne i wymagają pół-programisty do
ich skonfigurowania. Tracisz tę cudowną możliwość korzystania z Excela, aby móc po prostu upuścić dane do tabeli przestawnej i rozpocząć analizę.

Tak więc, poproś dostawcę pakietu, aby udostępnić âflatâ pivot-table gotowy plik danych zawierający wszystkie swoje rekordy transakcji księgi nominalnej. W załączniku pierwszym poniżej podałem listę pól, które sugeruję, że każdy rekord powinien zawierać, ale będzie się ona różnić w zależności od pakietu. Zrób również kopię listy pól wyprowadzanych przez program SAGEPIVOT na stronie 3 tego poradnika i użyj jej jako przyk