Redakcja

Autorzy:

Malina Cierzniewska-Skweres, Jakub Kudliński

Kierownik grupy wydawniczej:

Ewa Ziętek-Maciejczyk

Wydawca:

Monika Kijok

Redaktor prowadzący:

Rafał Janus

Korekta:

Zespół

Skład i łamanie:

Norbert Bogajczyk

Projekt okładki:

Piotr Fedorczyk

Druk: Miller

ISBN: 978-83-269-3728-6

Copyright by Wydawnictwo Wiedza i Praktyka sp. z o.o.

Warszawa 2015

Wydawnictwo Wiedza i Praktyka sp. z o.o.

03-918 Warszawa, ul. Łotewska 9a

tel. 22 518 29 29, faks 22 617 60 10

NIP: 526-19-92-256

Numer KRS: 0000098264 – Sąd Rejonowy dla m.st. Warszawy, Sąd Gospodarczy XIII Wydział Gospodarczy Rejestrowy. Wysokość kapitału zakładowego: 200.000 zł

„Budżetowanie w Excelu” wraz z przysługującymi Czytelnikom innymi elementami dostępnymi w subskrypcji (e-letter, strona www i inne) chronione są prawem autorskim. Przedruk materiałów opublikowanych w „Budżetowaniu w Excelu” oraz w innych dostępnych elementach subskrypcji – bez zgody wydawcy – jest zabroniony. Zakaz nie dotyczy cytowania publikacji z powołaniem się na źródło.

Publikacja „Budżetowanie w Excelu” została przygotowana z zachowaniem najwyższej staranności i wykorzystaniem wysokich kwalifikacji, wiedzy i doświadczenia autorów oraz konsultantów. Zaproponowane w publikacji „Budżetowanie w Excelu” oraz w innych dostępnych elementach subskrypcji wskazówki, porady i interpretacje nie mają charakteru porady prawnej. Ich zastosowanie w konkretnym przypadku może wymagać dodatkowych, pogłębionych konsultacji. Publikowane rozwiązania nie mogą być traktowane jako oficjalne stanowisko organów i urzędów państwowych. W związku z powyższym redakcja nie może ponosić odpowiedzialności prawnej za zastosowanie zawartych w publikacji „Budżetowanie w Excelu” lub w innych dostępnych elementach subskrypcji wskazówek, przykładów, informacji itp. do konkretnych przypadków.

Wstęp

Zarządzanie procesem budżetowania wymaga tworzenia planów budżetowych, zestawienia wartości planowanych z danymi rzeczywistymi oraz tworzenia raportów odchyleń wartości rzeczywistego wykonania od wartości określonych w planie. Właśnie na tych trzech zagadnieniach koncentruje się niniejsza publikacja, która pokazuje, jak przeprowadzić proces budżetowania w Excelu. Opisuje kolejne etapy przygotowania budżetu na podstawie danych historycznych oraz prognoz przygotowanych przez poszczególnych handlowców. W kolejnych krokach przedstawia podstawowe analizy ułatwiające budżetowanie oraz importowanie (scalanie) danych z różnych źródeł w celu dokonania poprawek i całościowej analizy.

Tworząc raporty, dużo uwagi zwracamy na zbieranie danych. Jest to jednak dopiero połowa sukcesu. W raportach warto umieszczać również dodatkowe elementy graficzne, ułatwiające szybką interpretację wyników. Książka pokazuje, jak przygotować licznik przedstawiający procentową realizację budżetu oraz wykres informujący o różnicy między wartościami planowanymi a realizacją. Jeden rzut oka na ten wykres pozwoli zorientować się, jaki jest wynik sprzedaży w odniesieniu do budżetu.

Oprócz tego książka pokazuje, jak unikać typowych problemów podczas tworzenie budżetu w Excelu: błędów w formułach wyliczających; niepożądanej ingerencji użytkowników w strukturę arkuszy oraz problemów z aktualizacją łączy pomiędzy arkuszami. Książka dokładnie wyjaśnia, w jaki sposób Excel przechowuje łącza do plików znajdujących się na dyskach lokalnych, mapowanych lub w udziałach sieciowych.

1. Tworzenie planów budżetowych – tabela do planowania

Jednym z pierwszych etapów budżetowania sprzedaży jest prognozowanie sprzedaży przez przedstawicieli handlowych. Takie planowanie należy przeprowadzić w tabelach z podziałem na klientów i produkty (najczęściej: grupy produktów). Planuje się ilościowo (w jednostce odpowiadającej sprzedawanym produktom, np. kg), aby potem móc pomnożyć ilość przez cenę (ustalaną w odrębnym etapie budżetowania) i otrzymać wartość sprzedaży. W tym rozdziale pokazujemy, w jaki sposób stworzyć prostą tabelę do planowania.

1.1. Dane źródłowe

Najpierw omówimy etapy tworzenia tabeli do planowania, która zawiera podział na klientów i produkty. Do stworzenia takiej tabeli niezbędne są następujące dane (znajdują się w arkuszu Dane historyczne):

klient (id klienta, nazwa lub nazwa skrócona),

produkt (nazwa produktu lub nazwa grupy produktów),

rok, którego dotyczą dane,

ilość (w jednostce odpowiedniej dla danych, np. kg),

wartość (wartość sprzedaży – będzie potrzebna przy analizie danych).

Rysunek 1.1. Przykładowe dane źródłowe

Można oczywiście dodać więcej danych, choćby podział na miesiące. Wszystko zależy od stopnia szczegółowości planowania. Zakładamy, że przedstawiciele planują cały rok, który następnie rozbije się na miesiące odpowiednim kluczem historycznym.

Przykład takiej tabeli przedstawiony jest na rysunku 1.1. Dane znajdują się w tabeli o nazwie tbDaneHistoryczne.

Z danych źródłowych stworzymy teraz tabelę przestawną, z której następnie zaciągniemy dane do tabeli do planowania.

1.2. Tabela przestawna jako źródło do tabeli do planowania

Jeśli dane źródłowe przechowywane są w zewnętrznej bazie danych, takiej jak SQL czy Access, tabelę przestawną możemy oczywiście oprzeć bezpośrednio na nich. Jeśli dane znajdują się w Excelu – tak jak w opisywanej sytuacji – aby stworzyć tabelę przestawną, należy zaznaczyć dowolną komórkę danych źródłowych, z których tabela ma powstać. Następnie z menu Wstawianie/Tabele wybrać przycisk Tabela przestawna. Pojawi się okno Tworzenie tabeli przestawnej (rysunek 1.2.), które zawiera interesujące nas opcje: tabela oparta na tabeli tbDaneHistoryczne (Excel podpowiedział, ponieważ na początku zaznaczona była komórka z tego zakresu) w nowym arkuszu.

Rysunek 1.2. Okno Tworzenie tabeli przestawnej

Po zatwierdzeniu przyciskiem OK w nowym arkuszu pojawi się pusta tabela oraz Lista pól tabeli przestawnej. Od razu warto nazwać nowy arkusz oraz tabelę. Arkusz nazywamy poprzez dwukrotne kliknięcie na karcie z nazwą arkusza i wprowadzenie tekstu Tabela przestawna (zmianę zatwierdzamy klawiszem [Enter]). Tabelę przestawną zaś nazywamy, korzystając z menu Narzędzia tabel przestawnych/Opcje/Tabela przestawna, z pola Nazwa tabeli przestawnej. Tam wpisujemy tpAnaliza i również zatwierdzamy klawiszem [Enter].

Ponieważ chcemy, by nasza tabela zawierała informacje o tym, jaki klient, kiedy i ile danego produktu kupił, w tabeli umieścimy następujące pola (rysunek 1.3. przedstawia uzupełnioną listę pól i wynikową tabelę przestawną):

w polu wierszy (etykiety wierszy): klient, rok,

w polu kolumn: produkt,

w polu wartości: ilość.

Rysunek 1.3. Uzupełniona lista pól i wynikowa tabela przestawna

Ponieważ stworzona automatycznie tabela przestawna jest bardzo nieczytelna, należy ją sformatować. Po pierwsze,wyświetlimy tabelę w formie tabelarycznej bez sum częściowych (nic nie wnoszą do raportu). Następnie nadamy format liczbowy ilościom tak, by wyświetlane były one z separatorem tysięcy.

Aby dokonać wspomnianych zmian, należy ustawić się w dowolnym miejscu tabeli (tylko wtedy bowiem pojawi się menu Narzędzia tabel przestawnych i możliwa będzie zmiana opcji). Następnie trzeba wywołać menu kontekstowe, klikając prawym przyciskiem myszy w dowolnym miejscu tabeli. Z tego menu wybrać Opcje tabeli przestawnej, a z zakładki Wyświetlanie zaznaczyć opcję Układ klasyczny tabeli przestawnej (umożliwia przeciąganie pól w siatce). Wreszcie, aby usunąć sumy częściowe, należy z menu Narzędzia tabel przestawnych/Projektowanie/Układ/Sumy częściowe wybrać opcję Nie pokazuj sum częściowych. Warto jeszcze wyczyścić styl tabeli, czyli z menu Narzędzia tabel przestawnych/Projektowanie/Style tabeli przestawnej wybrać opcję Wyczyść. Dzięki temu widoczne będą ciemne linie oddzielające poszczególnych klientów, a pozostałe dane oddzielone będą szarymi liniami siatki.

Warto jeszcze zmienić nazwy wyświetlanych pól tabeli: Suma z ilość i Suma końcowa. Zamienimy je na „Ilość ” i „SUMA”. Można to zrobić na kilka sposobów. Jednym z nich jest zmiana tej nazwy w komórce, w której jest wyświetlana. Zatem dla Suma z ilość będzie to komórka A3, a dla Suma końcowa – H4. W tych komórkach należy wprowadzić taką nazwę, jaką chcemy, aby wyświetlała tabela. Należy jednak pamiętać o tym, by nowe nazwy nie pokrywały się z nazwami pól w źródle danych. Nie możemy zatem pola Suma z ilość nazwać ilość, gdyż taka nazwa już istnieje. Możemy jednak nazwać ją „ilość ” (ze spacją na końcu). Wyglądać będą tak samo, jednak dla Excela będą to już różne nazwy (efekt widać na rysunku 1.4.).

Ostatnią czynnością związaną z formatowaniem jest nadanie formatu liczbowego. W tym celu należy ustawić się w polu wartości tabeli przestawnej (na dowolnej liczbie), nacisnąć prawy klawisz myszy i z menu kontekstowego wybrać opcję Ustawienia pola wartości. Z okna o tej samej nazwie nacisnąć klawisz Format liczby, a następnie kategorię Liczbowe. Warto, aby liczba nie miała miejsc po przecinku (opcja: miejsca dziesiętne: 0) i miała separator tysięcy (opcja: użyj separatora 1000).

Po wykonaniu tych czynności osiągnięty zostanie efekt, pokazany na rysunku 1.4.

Rysunek 1.4. Tabela przestawna po sformatowaniu

Na koniec warto jeszcze zablokować okienka, aby ułatwić odbiorcy analizę tabeli. W tym celu należy ustawić się w komórce C5 i wybrać z karty Widok/Okno opcję Zablokuj okienka/Zablokuj okienka. Od tej pory, podczas oglądania danych, użytkownik zawsze będzie widział kolumny A i B oraz wiersze od 1 do 4.

W tabeli przestawnej znajdują się teraz informacje o ilości sprzedanych towarów. W kolejnych krokach dodamy do tej tabeli jeszcze wartość i cenę średnią tych produktów.

1.3. Tabela do planowania

Stwórzmy nowy arkusz, który nazwiemy Tabela do planowania, a w nim następującą tabelę (rysunek 1.5.):

Rysunek 1.5. Schemat tabeli do planowania

Istotne jest, aby w wierszach (zakres A2:A11) znalazły się nazwy klientów, w kolumnach nazwy produktów (zakres B1:F1), a na krańcach tabeli – formuły sumujące. Do zakresu B12:F12 wpiszmy więc formułę: =SUMA(B2:B11), a do zakresu G2:G11: =SUMA(B2:F2). W obu przypadkach najlepiej zrobić to za pomocą kombinacji klawiszy [Ctrl]+[Enter]. W tym celu należy zaznaczyć zakres B12:F12, wejść do edycji komórki B12 (klawisz F2) i zatwierdzić kombinacją klawiszy [Ctrl]+[Enter]. Dzięki temu formuła zostanie wprowadzona do wszystkich komórek zaznaczonego zakresu z zachowaniem formatowania. Analogicznie trzeba postąpić z zakresem G2:G11.

Więcej znajdziesz w wersji pełnej publikacji

Tematy publikacji w pełnej wersji

1. Tworzenie planów budżetowych – tabela do planowania

2. Analizy pomagające w planowaniu

3. Przygotowanie danych do importu do bazy danych

4. Ochrona przed niechcianą ingerencją użytkownika

5. Analiza porównawcza

6. Licznik pokazujący realizację budżetu

7. Wymiana danych między plikami

8. Wymiana danych – analiza cząstkowych danych z wielu skoroszytów

9. Wykres skumulowanej sprzedaży w porównaniu z budżetem

10. Budżet i realizacja – wykres obrazujący wartości różnic

Przygotowanie do wersji elektronicznej: RASTER studio, 603 59 59 71