Zadanie „Analiza klientów - sprzedaż i płatności”
Firma YYY zajmuje się hurtową sprzedażą dwóch grup produktów („A” i „B”). W tabeli przedstawiono listę wystawionych przez YYY w analizowanym roku faktur dla odbiorców. Informacja o kliencie (odbiorcy) zakodowana została poprzez jego numer. Lista klientów znajduje się w oddzielnym arkuszu. Zostali oni podzieleni na trzy segmenty rynku oraz cztery regiony.
Faktury mają termin płatności zależny od segmentu rynku, do którego przypisany został klient:
dealerzy autoryzowani - 30 dni
inne hurtownie - 21 dni
zakłady przemysłowe - 14 dni.
Polecenia należy wykonywać w miarę potrzeb w oddzielnych arkuszach
Polecenia:
Informacje o każdej fakturze uzupełnij o nazwę klienta, segment rynku i region. Zaleca się zastosowanie funkcji WYSZUKAJ.PIONOWO.
Dla każdej faktury oblicz przeterminowanie zapłaty w dniach (liczba dodatnia - płatność po terminie, ujemna - przed terminem), wykorzystując informacje o terminach płatności stosowanych w danym segmencie rynku. Zaleca się zastosowanie funkcji JEŻELI.
Dla kolumny „Data zapłaty” wprowadź regułę sprawdzania poprawności umożliwiającą wprowadzenie daty nie wcześniejszej od daty wystawienia faktury.
Zablokuj na ekranie wiersz nagłówka.
Przygotuj tabelę do wydruku wykorzystując orientację poziomą, wpasowanie danych w szerokość strony, blokując na każdej stronie wiersz nagłówka, dodając numerowanie stron.
W oddzielnym arkuszu (kopia arkusza „Faktury”) ogranicz za pomocą filtrowania prostego widoczne wiersze. Widoczne mają być wyłącznie faktury o przeterminowaniu przekraczającym 10 dni pochodzące z segmentów: dealerów autoryzowanych lub innych hurtowni.
Za pomocą filtra zaawansowanego wybierz (filtrowanie w miejscu) wyłącznie faktury, które w przypadku segmentu dealerów autoryzowanych przekroczyły wartość 10.000 zł, natomiast w przypadku innych hurtowni przekroczyły wartość 8.000 zł, pomiń faktury w segmencie zakładów przemysłowych.
Za pomocą filtra zaawansowanego skopiuj do innego arkusza nazwy klientów, którzy jednorazowo zakupili towar o wartości przekraczającej 14.000 zł (eliminując duplikaty).
W oddzielnym arkuszu przygotuj dane i za pomocą sum częściowych zsumuj wartość sprzedaży w poszczególnych miesiącach. Zaleca się zastosowanie funkcji MIESIĄC.
W oddzielnym arkuszu za pomocą sum częściowych oblicz dla poszczególnych klientów: średnią wartość faktur, średnie przeterminowanie faktur i odchylenie standardowe przeterminowania faktur. Skopiuj zagregowane dane (średnie i odchylenie standardowe) dla poszczególnych klientów do oddzielnego arkusza (pomijając dane elementarne).
W oddzielnym arkuszu przygotuj dla każdego klienta oblicz współczynnik wiarygodności płatniczej, określony jako średnie przeterminowanie płatności faktury ważone kwotą faktury (współczynnik sumy iloczynów dni przeterminowania i kwot płatności faktur przez sumy kwot faktur dla każdego klienta).
W oddzielnym arkuszu przygotuj, wykorzystując dwupoziomowe sumy częściowe, informacje o łącznej sprzedaży w poszczególnych segmentach rynku (w kolejności: zakłady przemysłowe, dealerzy autoryzowani, inne hurtownie) oraz w ramach segmentów - dla każdego klienta.
Wykorzystując dane z poprzedniego punktu przygotuj wykres kołowy pokazujący udziały poszczególnych segmentów rynku w sprzedaży całkowitej.
Na podstawie wcześniej przygotowanych sum pośrednich przygotuj wykres kolumnowy prezentujący miesięczne wartości sprzedaży. Przeprowadź analizę regresji za pomocą linii trendu typu liniowego, prognozując sprzedaż 3 miesiące do przodu.
Przygotuj w oddzielnym arkuszu dane do wykresu i wykres liniowy ukazujący na oddzielnych krzywych skumulowane wartości sprzedaży (liczone od początku roku) dla każdej grupy produktów (A oraz B). Sprawdź możliwości formatowania skali osi czasu (skala czasu na osi kategorii X, zmiana jednostki podstawowej).
Wykorzystując funkcję SUMA.JEŻELI oblicz w nowej kolumnie arkusza „Klienci” łączną wartość sprzedaży dla każdego klienta. Podaj udział procentowy sprzedaży dla klienta w łącznej sprzedaży dla wszystkich klientów.
Wykorzystując funkcje SUMA.WARUNKÓW oraz LICZ.WARUNKI zbuduj dwie tabele zawierające odpowiednio informacje o sprzedaży oraz o liczbie faktur dla poszczególnych klientów według dni tygodnia: w wierszach - klienci, w kolumnach - poszczególne dni tygodnia.
Wykorzystując funkcję BD.SUMA oblicz dla poszczególnych segmentów rynku łączną wartość sprzedaży w transakcjach jednorazowo przekraczających 10.000. Wykorzystaj nadanie nazwy obszarowi bazy danych.
Wykorzystując funkcję BD.ŚREDNIA oblicz dla poszczególnych segmentów rynku średnie przeterminowanie faktur w transakcjach jednorazowo przekraczających 10.000.
Zbuduj 2-3 przykładowe tabele przestane oraz wykres przestawny i przeanalizuj dane o sprzedaży w różnych przekrojach. Jako elementy danych (miary) można traktować np. sumę wartości faktur, liczbę faktur, średnie przeterminowanie. Jako pola wierszy, kolumn lub stron (wymiary) można traktować np. region, segment rynku, grupę produktów, daty. Zaleca się wykorzystanie hierarchicznych zależności wymiarów. Sformatuj w tabeli przestawnej dane numeryczne. Dla wybranych wartości utwórz szczegółowe listy pozycji.