Budżetowanie w Excelu - Malina Cierzniewska-Skweres, Jakub Kudliński
«
»
Reflow text when sidebars are open.
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
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
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.