Wyliczenie wartości i ilości sprzedaży dla produktów, pracowników czy punktów handlowych

Wyliczenie wartości i ilości sprzedaży dla produktów, pracowników czy punktów handlowych

Częstym problemem z jakim spotykają się właściciele firm jest określenie wartości, ilości sprzedaży dla konkretnego pracownika, produktu czy też stoiska. Poniższy przykład przedstawia sposób za pomocą którego w łatwy sposób można ocenić który produkt sprzedawał się najlepiej, który pracownik jest najbardziej efektywny czy też kiedy sprzedaż jest najwyższa. Przystępując do tego rodzaju obliczeń właściciel prowadzący firmę powinien dysponować tabelą w Excelu zawierającą sprzedaż dzienną wg kryteriów które należy obliczyć.

Firma kosmetyczna sprzedaje swoje produkty poprzez małe stoiska usytuowane w centrach handlowych. W tej chwili firma posiada cztery stoiska umiejscowione po jednym w centrach handlowych na Ursynowie, Mokotowie, Woli i Śródmieściu. Wszystkie transakcje są ewidencjonowane w Excelu. Na stoiskach sprzedawane są produkty do pielęgnacji ciała a także produkty kosmetyczne. Właściciela firmy w tej chwili interesuje analiza sprzedaży szminek. Częste pytania na jakie musi odpowiedzieć właściciel firmy przy ocenie wydajności pracowników czy też ilości sprzedaży to:

Ile wynosiły wpływy ze sprzedaży szminki „NYC Appleicious” przez Joanne?

Ile wynosiły wpływy ze sprzedaży ze stoiska na Mokotowie?

Jaka była średnia ilość szminek sprzedawanych przez Martę

Jaka była średnia ilość szminek sprzedawanych na Woli?

Ile było sprzedaży szminek dla stoiska w Śródmieściu podczas drugiej zmiany?

Ile wynosiły wpływy ze sprzedaży podczas pierwszej zmiany?

Ile wynosi całkowita wartość sprzedaży każdego z produktów?

Ile wynosi całkowita wartość sprzedaży każdego z produktów przez poszczególnych sprzedawców?

Rysunek poniżej przedstawia fragment danych dotyczących sprzedaży tychże szminek.

01

Łączna ilość transakcji w styczniu i lutym 2013 to 2.400. Rysunek przedstawia tylko fragment ilości transakcji aby pokazać w jaki sposób dane są uporządkowane do analizy. Ważne informacje do oceny wydajności sprzedaży przez właściciela firmy kosmetycznej to stoisko na którym dokonano sprzedaży (kolumna B); data sprzedaży (kolumna C); produkt (kolumna D); ilość sztuk sprzedanych podczas jednej sprzedaży (kolumna E); cena jednostkowa (kolumna F); wartość sprzedaży (kolumna G) sprzedawca (kolumna H) i zmiana (pracownicy pracują na stoiskach w systemie dwuzmianowym – kolumna I).

Aby odpowiednio ocenić pracowników czy też wartość sprzedaży należy skorzystać z funkcji BAZ DANYCH dostępnych w Excelu. Aby zademonstrować ogromne i wszechstronne możliwości funkcji statystycznych baz danych w artykule zostanie przedstawionych kilka przykładów ilustrujących ich zastosowanie.

Obliczenie wartości sprzedaży konkretnego produktu dla konkretnego sprzedawcy

Ile wynosiły wpływy ze sprzedaży szminki „NYC Appleicious” przez Joanne?

Aby odpowiedzieć na to pytanie należy skorzystać z funkcji BD.SUMA. Tabela poniżej przedstawia obliczenie za pomocą tej funkcji.

02

W pierwszej kolejności należy przygotować mini-tabelkę (komórki J3:K4) w której wskażemy excelowi co chcemy obliczyć. Ponieważ chcemy obliczyć sprzedaż konkretnej szminki przez konkretnego sprzedawcę to w komórce J3 wpisano tytuł kolumny w której jest umieszczona szminka. W komórce J4 wpisano nazwę konkretnego produktu. Komórka K3 to tytuł kolumny w której są wymienieni sprzedawcy. Komórka K4 zawiera imię sprzedawcy, którego chcemy poddać ocenie. Obliczenie zostało dokonane w komórce N4. Klikając na tą komórkę należy znaleźć w funkcjach dostępnych na karcie „narzędzia główne” znaleźć BD.SUMA. Po kliknięciu powinno wyskoczyć okno dialogowe, które należy wypełnić tak jak jest to przedstawione poniżej

03

W polu „baza” należy zaznaczyć całą bazę danych, którą mamy przygotowaną do analizy. W tym przypadku będzie to A1:I2401 (na rysunku widać pierwsze 24 sprzedaże ze wszystkich 2.400)

W polu „Pole” musimy wskazać excelowi nr kolumny którą chcemy zsumować. Ponieważ chcemy policzyć wpływy ze sprzedaży będzie to kolumna G, gdzie mamy podane wartości sprzedaży. W to pole należy wpisać 7 ponieważ ta kolumna jest siódmą kolumną licząc od lewej.

W polu „kryteria” należy wskazać kryteria wg których chcemy obliczyć wartość sprzedaży. W tym przykładzie właściciela firmy interesuje konkretny produkt i sprzedawca także należy zaznaczyć mini-tabelkę K3:L4. Po kliknięciu w OK powinien w komórce N4 pojawić się wynik obliczeń. 129,13 zł oznacza, iż sprzedawca Joanna sprzedała szminki NYC Appleicious na łączną kwotę 129,13 zł.

Te same obliczenia co BD.SUMA można wykonać funkcją SUMA, natomiast funkcja bazy danych ma jedną istotną zaletę. O ile funkcja SUMA dodaje zawartość wszystkich komórek wskazanego zakresu, funkcja BD.SUMA pozwala określić, za pomocą kryteriów, podzbiór wierszy sumowanego w ramach zakresu. Także w o wiele prostszy sposób można wyliczyć interesujące właściciela firmy kryteria z całej bazy danych.

Obliczenie średniej ilości sprzedanych produktów na jedną transakcję

Ile wynosiła średnia ilość sprzedanych szminek na jedną transakcję na Mokotowie?

Aby odpowiedzieć na tak postawione pytanie należy skorzystać z funkcji BD.ŚREDNIA. W komórkach K6:K7 wstawiono mini-tabelę pokazującą co chcemy obliczyć

04

Tytuł kolumny to „stoisko” a w komórce K7 wpisano nazwę interesującej właściciela firmy dzielnicę – Mokotów. Obliczenia dokonano w komórce N7. Okno dialogowe dla BD.ŚREDNIA należy wypełnić następująco:

05

W polu „baza” należy zaznaczyć całą bazę z której czerpiemy dane do obliczeń – A1:I2401.

W polu „pole” należy podać 5 ponieważ kolumna z ilością sprzedaży jest piątą kolumną licząc od lewej.

W polu „kryteria” należy wskazać mini-tabelę z kryteriami – komórki K6:K7.

Po kliknięciu w OK pojawi się wynik. Jak widać w komórce N7 średnia ilość sprzedanych szminek podczas jednej sprzedaży to 3,01.

Obliczenie ilości sprzedaży każdego z produktów

Jaka była całkowita ilość sprzedanych poszczególnych produktów?

Aby podać odpowiedź na to pytanie należy użyć funkcji BD.SUMA a także zbudować tabelę danych z jedną zmienną. W pierwszej kolejności jednak należy podać excelowi kryteria wg których chcemy obliczyć sumę.

06

Kryteria te zostały wpisane w komórki K10:K11 jako przykładowe. W tym momencie można podać w komórce K11 dowolny produkt jaki firma ma w ofercie, nie musi być to szminka Manhattan LipsS2Last. Chodzi tu o to aby wynik obliczeń BD.SUMA wskazywał excelowi co ma obliczać przy budowie tabeli danych obliczonej w kolejnym kroku. W komórce N11 należy obliczyć BD.SUMA. Okno dialogowe powinno się wypełnić w następujący sposób.

07

Jak widać w komórce N11 ilość sprzedaży Manhattan LipsS2Last to 376 sztuk. Aby obliczyć sprzedaż wszystkich produktów należy zbudować tabelę danych z jedną zmienną. W tym celu w komórkach K14:K34 zostały wymienione wszystkie produkty dostępne w sprzedaży.

08

W komórce L13 wpisano =N11, czyli wskazano co ma Excel obliczyć na przykładzie szminki Manhattan LipsS2Last. Zaznaczając całą tabelę tak jak na rysunku powyżej, tj komórki K13:L34 należy przejść na kartę „dane”, gdzie w menu „analiza warunkowa” znajdziemy „tabela danych”

09

Po kliknięciu w „tabela danych” pojawi się okno dialogowe. W „kolumnowa komórka wejściowa” należy kliknąć w komórkę K11. Dzięki temu pokażemy excelowi, że wszystkie produkty wymienione w tabeli ma obliczyć w ten sam sposób jak ilość sprzedaży szminki Manhattan LipsS2Last. Po kliknięciu w ok Excel powinien podać wynik ilości sprzedaży dla każdego z produktów.

10

W ten prosty sposób Excel obliczył ilość sprzedaży każdej ze szminek.

Przychód dla poszczególnych punktów sprzedaży

Ile wynosi całkowita wartość sprzedaży przez poszczególne stoiska dla każdej ze zmian?

Aby odpowiedzieć na tak postawione pytanie należy skorzystać z funkcji BD.SUMA a także zbudować tabelę danych, tak jak w poprzednim przykładzie, ale dla dwóch zmiennych: stoiska i zmiany (pracownicy pracują w systemie dwuzmianowym). Mini tabelkę z kryteriami zbudowano w komórkach K40:L41. Także w tym przykładzie należy podać excelowi przykładowe stoisko i przykładową zmianę dla których obliczymy wartość sprzedaży. W tym przypadku jest to Śródmieście i druga zmiana.

11

Obliczenie funkcji BD.SUMA dokonano w komórce N41 a okno dialogowe powinno zostać wypełnione w następujący sposób:

12

Jak widać w komórce N41 wartość sprzedaży dla stoiska w śródmieściu podczas drugiej zmiany to 2 774,78 zł.. Także w tym przypadku obliczenia z tej komórki posłużą nam jako przykład obliczeń, które ma wykonać Excel. Aby obliczyć wartość sprzedaży dla wszystkich stoisk i obydwu zmian należy zbudować tabelę jak na rysunku poniżej.

13

W kolumnie K wpisano wszystkie stoiska a wierszu 44 wymieniono obydwie zmiany. W komórce K44 wpisano =N41, co oznacza że właśnie wg tej komórki Excel ma dokonać obliczeń. Zaznaczając całą tabelę K44:M48 należy na karcie „dane”, w menu „analiza warunkowa”, kliknąć w „tabela danych”. okno dialogowe należy wypełnić w następujący sposób:

14

„wierszowa komórka wejściowa” to komórka L41, czyli zmiana dla której należy obliczyć sprzedaż – w poziomie są wymienione obydwie zmiany.

„kolumnowa komórka wejściowa” to komórka K41, czyli stoisko – w pionie wymieniliśmy wszystkie stoiska i stąd w kolumnie należy wskazać excelowi komórkę K41. Po kliknięciu w OK Excel powinien pokazać wyniki obliczeń.

15

Jak widać na rysunku poniżej w prosty sposób Excel policzył wartości sprzedaży dla wszystkich stoisk i wszystkich zmian na których pracują sprzedawcy. Aby wyniki obliczeń były czytelniejsze można posłużyć się wykresem. W tym celu zaznaczmy całą tabelę K44:M48 i przechodzimy do karty „wstawianie” gdzie należy kliknąć na wykres „kolumnowy”. Poniżej obliczeń Excel wyświetli wykres, który w przejrzysty sposób przedstawi wyniki obliczeń.

16

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *