Excel: "Rachunek marż pokrycia"










Controlling operacyjny w przedsiębiorstwie handlowym - lab. z programu Excel 2007

Wydział Zarządzania, WZ, UTP, Bydgoszcz 

=========================================================
=========================================================
=========================================================

Temat: 
Rachunek marż pokrycia - SPRZEDAŻ DETALICZNA 
- sprzedawca -> towar

Najpierw pobierz plik Excela z tabelami, ale bez obliczeń.
Polecenia:

  • Tabela 1 - oblicz kolejne stopnie marży
  • Tabela 2 - oblicz wskaźniki marż pokrycia
  • Tabela 3 - oblicz strukturę przychodów, kosztów i marż
  • Tabela 4 - znajdź towar o najwyższej i najniższej rentowności oraz towar o największym i najmniejszym udziale w przychodach
  • Tabela 5 - Ustal ranking towarów według rentowności



Polecenie 1

Tabela 1 zawiera trochę danych liczbowych oraz pola do uzupełnienia.


Marża I stopnia to "Przychody ze sprzedaży netto":
  • minus "zmniejszenia (rabaty, upusty)"
  • minus "koszty sprzedanych towarów w c. zakupu"

Zatem komórka B21 będzie zawierała formułę:
  • "=B18-B19-B20"
  • formułę przenosimy do kolumny F włącznie.



Marża II stopnia to marża I stopnia:
  • minus "koszty sprzedaży związane z towarami"

Formuła w komórce B23:
  • "=B21-B22"
  • formułę przenosimy do kolumny F włącznie.



Suma marż na poziomie towaru to:
  • suma marż II stopnia dla wszystkich towarów sprzedawanych przez jednego sprzedawcę
  • zatem tę sumę obliczamy osobno dla sprzedawcy 1 i 2

Formuły:
  • komórka B24 (scalona): "=SUMA(B23:D23)"
  • komórka E24 (scalona): "=SUMA(E23:F23)"
Jako, że formuły są w komórkach scalonych często przenoszenie formuł do komórek pojedynczych może być niemożliwe, dlatego podałem osobno formuły dla tych dwóch komórek scalonych


Marża III stopnia to "suma marż na poziomie towaru":

  • minus "koszty sprzedaży związane ze sprzedawcą"

Formuły:
  • komórka B26 (scalona): "=B24-B25"
  • komórka E26 (scalona): "=E24-E25"

Suma marż na poziomie sprzedawców to:
  • suma marż III stopnia dla obu sprzedawców razem
Formuła:
  • "=B26+E26"




Marża IV stopnia to "suma marż na poziomie sprzedawców":
  • minus "koszty utrzymania działu sprzedaży"
Formuła:
  • "=B27-B28"



Kolumna "Razem":

Jest to kolumna, gdzie zsumowane są wszystkie liczby w danym wierszu. 
Przechodzimy do komórki G18. Formuła:
  • "=SUMA(B18:F18)"
Przenosimy formułę do wierszy znajdujących się poniżej w obrębie tej tabeli, czyli do wiersza numer 29 włącznie.

Po tych obliczeniach tabela 1 przedstawia się tak:




Polecenie 2

Tabela 2 wygląda tak samo, jak tabela 1, ale nie ma podanych żadnych wartości liczbowych oraz komórki z danymi mają formatowanie procentowe.



Zaznaczamy komórki od B36 do G47


Klikamy PPM, Formatuj komórki..., zakładka "Liczby", opcja "Procentowe", OK.


Ważne!!!

Każda komórka w tabeli 2 jest stosunkiem odpowiednika tej komórki w tabeli 1 do komórek: "Przychody ze sprzedaży netto" w tabeli 1. W zależności od tego, czy komórka jest pojedyncza, czy scalona, dzielimy przez jedną komórkę lub przez kilka zsumowanych. Wszytko wyjaśni się w dalszej części.


Komórki od B36 do G41:

Tutaj musimy wpisać właściwą formułę ($ w odpowiednim miejscu) w komórce B36 i przenieść ją na pozostałe, aż do G41. 

Formuła wstępna dla B36:
  • "=B18/B18"
W tym sektorze dzielimy komórki przez "przychody ze sprzedaży netto", ale tylko w obrębie danego towaru, czyli jeśli jesteśmy w kolumnie B, to mamy stosunek komórek w kolumnie B do "przychodów ze sprzedaży netto" dla towaru 1, który jest w kolumnie B. 
Gdy jesteśmy w kolumnie C to dzielimy przez "przychody ze sprzedaży netto" dla towaru 2, który jest w kolumnie C. Itd...

Zatem przy przeniesieniu formuły symbol kolumny  dla "przychodów ze sprzedaży netto" powinien się zmieniać, natomiast symbol wiersza nie, bo dzielimy wciąż przez to co znajduje się tylko w jednym wierszu (numer 18).

Pierwsza część formuły (licznik) nie ma żadnych znaków "$", gdyż muszą się zmieniać komórki poddawane dzieleniu.

 Formuła ostateczna dla sektora od B36 do G41:

  • "=B18/B$18" dla komórki B36
  • przenosimy ją dalej do wszystkich komórek sektora

Na razie mamy takie wyniki:




 Komórki od B42 do G44 (scalone):

 Jako, że komórki tutaj są scalone, musimy w naszym stosunku dzielić przez zsumowane "przychody ze sprzedaży netto" dla danego sprzedawcy.

 Formuła wstępna dla komórki B42:
  • "=B24/(SUMA(B18:D18))"

 Jako, że chcemy dzielić przez to samo i jednocześnie formułę będziemy przenosić w dół, to musimy zablokować przyrost wartości symbolu wiersza.

 Formuła:

  • "=B24/(SUMA(B$18:D$18))"

Analogicznie postępujemy dla sprzedawcy drugiego, który ma sprzedaje tylko 2 towary oraz dla kolumny "Razem".

Formuła, komórka E42:
  • "=E24/SUMA(E$18:F$18)"

Formuła, komórka G42:
  • "=G24/G$18"



Komórki od B45 do G47 (scalone):

Dla komórki scalonej B45 dzielimy całą komórkę przez sumę całkowitą "przychodów ze sprzedaży netto" dla obu sprzedawców. Pamiętajmy, żeby zablokować wiersze znakiem dolara.

Formuła dla komórki B45:
  • "=B27/G$18"

A dla komórki G45:
  • "=G27/G$18"


Cała tabela 2 wygląda tak:





Polecenie 3

Tabela 3 wygląda tak samo, jak tabela 1 i tabela 2, też musimy wszystko uzupełnić i także należy zmienić formatowanie komórki na procentowe.

 Każda komórka w tabeli 3 jest stosunkiem odpowiednika tej komórki w tabeli 1 do komórki w kolumnie "Razem" w tabeli 1, która jest w tym samym wierszu. 
Jeśli jesteśmy np. w wierszu "zmniejszenia (rabaty, upusty)" to dzielimy komórki w tym wierszu przez komórkę "Razem", która także jest w wierszu "zmniejszenia (rabaty, upusty)".
Dzięki temu wiemy, jaki udział w zyskach lub kosztach ma sprzedaż poszczególnych towarów.


 Sektor B54 do G59

 Będąc w jednym wierszu tabeli 1 dzielimy każdą komórkę przez komórkę w kolumnie "Razem".

 Formuła dla komórki B54:

  • "=B18/G18"
Jednak, gdy przeniesiemy ją w prawo zmienia się symbol kolumny mianownika, przez co w kolejnych komórkach nie będziemy dzielili przez kolumnę "Razem". Dlatego stawiamy przed G znak "$". Przy przenoszeniu w dół zmiana symbolu wiersza jest wskazana, gdyż w każdym kolejnym wierszu dzielimy przez inną komórkę w kolumnie razem.

 Mamy zatem:

  • "=B18/$G18"
Formułę przenosimy aż do komórki G59.
Mamy:


Sektor komórek scalonych

Tutaj zasada jest ta sama, dzielimy komórki do całości danego parametru. Tutaj nie trzeba używać znaku "$", gdyż nie przenosimy formuł w prawo.

Dla komórek scalonych od B60 do B62:
  • "=B24/G24" dla B60 i przenosimy aż do B62

Dla komórek scalonych od E60 do E62:
  • "=E24/G24" dla E60 i przenosimy aż do E62


Dla komórek scalonych od G60 do G62:
  • "=G24/G24" dla G60 i przenosimy aż do G62



Dla komórek scalonych od B63 do B65:
  • "=B27/G27" dla B63 i przenosimy aż do B65

Dla komórek scalonych od G63 do G65:
  • "=G27/G27" dla G63 i przenosimy aż do G65


Cała uzupełniona tabela 3 wygląda tak:





Polecenie 4

Tabela 4 składa się z dwóch części. Pierwsza pokazuje towary o najwyższej i najniższej rentowności. Druga przedstawia towary o największym i najmniejszym udziale w przychodach.


Tabela 4a - rentowność

 Najpierw uzupełniamy kolumnę "Marża II". Musimy w niej zawrzeć wartość maksymalną i minimalną tabeli 2 z wiersza "Marża II na poziomie towaru". Dla naszego dokumentu jest to wiersz 41. Używamy formuł MAX i MIN i podajemy przeszukiwany obszar (dane liczbowe z wiersza 41, bez kolumny "Razem").

 Mamy zatem formuły, kolejno:

  • "=MAX(B41:F41)"
  • "=MIN(B41:F41)"

Teraz uzupełniamy kolumnę "Pozycja".
Posłużymy się formułą "Podaj.Pozycję".

 Składa się ona z 3 części oddzielonych średnikiem.
=Podaj.Pozcję(

  • 1 - Szukana wartość;
  • 2 - Przeszukiwana tabela;
  • 3 - Typ porównania)
Szukana wartość - to wartość jakiej szukamy, dla nas jest to wartość maksymalna, która znajduje się w komórce C72.

Przeszukiwana tabela - to zakres, w którym będziemy szukać. Dla nas jest to wiersz 41, ale podajemy w nim tylko komórki liczbowe, bez kolumny "Razem".

Typ porównania - wpisujemy "0", co oznacza, że formuła ma odszukać dokładnie tą wartość, jaka jest podana w "szukana wartość".

Formuła zwraca, która w kolejności jest dana komórka w danym zakresie.

Czyli nasze formuły wyglądają tak:
  • "=PODAJ.POZYCJĘ(C72;B$41:F$41;0)"
  • "=PODAJ.POZYCJĘ(C73;B$41:F$41;0)"

Użyłem znaku "$" dwukrotnie, gdyż wpisałem formułę do komórki D72 i następnie ją przeniosłem w dół do D73. Dzięki temu nie zmienił się zakres komórek przeszukiwanych, natomiast wartość szukana przeskoczyła na kolejną komórkę (C73).


Na koniec kolumna "Towar". Ma się tu pojawić nazwa towaru, który ma największą i najmniejszą rentowność. Tutaj musimy użyć formuły "Indeks". Składa się ona z dwóch pól.

=INDEKS(
  • tablica;
  • numer wiersza)

Tablica - obszar, z którego wybierzemy komórkę i jej zawartość. Wybieramy spośród etykiet tabeli, czyli nazw towarów od 1 do 5, wiersz 35.

Numer wiersza - tutaj nie podajemy numeru wiersza, tylko adres komórki, gdzie jest podana pozycja, gdzie znajduje się szukana wartość maksymalna i minimalna, czyli to co przed chwilą robiliśmy (D72 i D73).

Mamy zatem:
  • "=INDEKS(B$35:F$35; D72)"
  • "=INDEKS(B$35:F$35; D73)"

Mamy zatem:




Tabela 4b - udział w przychodach

Postępowanie jest tutaj analogiczne jak poprzednio, tyle, że tutaj szukamy najwyższego i najniższego przychodu, dlatego teraz przeszukujemy wiersz 54 w tabeli 3, czyli "Przychody ze sprzedaży netto".


Kolumna "Udział":
  • "=MAX(B54:F54)"
  • "=MIN(B54:F54)"

Kolumna "Pozycja":
  • "=PODAJ.POZYCJĘ(C77;B$54:F$54;0)"
  • "=PODAJ.POZYCJĘ(C78;B$54:F$54;0)"

Kolumna "Towar":
  • "=INDEKS(B$53:F$53; D77)"
  • "=INDEKS(B$53:F$53; D78)"

Mamy więc:




Polecenie 5

Tutaj mamy stworzyć ranking rentowności na poziomie marży II stopnia. Do tego celu będzie potrzebna formuła "MAX.K". Formuła zwraca k-tą wartość w danym zbiorze. Formatowanie komórek w kolumnie "Wskaźnik z MB II" należy zmienić na procentowe.

=MAX.K(
  • tablica;
  • k)

Tablica - w niej szukamy wartości (wiersz 41)
k - numer pozycji we wskazanej tablicy (A84-A88)

Dla nas tablica to wiersz 41 w tabeli 2, czyli "Marża II na poziomie towaru". 
Natomiast "k" to komórki z kolumny "Miejsce w rankingu". Jako "k" nie wpisujemy wartości liczbowej, tylko dajemy komórkę, w której ta wartość jest. Dzięki temu gdy zmienimy wartość w kolumnie "Miejsce w rankingu" wszystko się automatycznie przeliczy.

 Formuły dla kolumny "Wskaźnik z MB II":
  • "=MAX.K(B$41:F$41; A84)"
  • formułę następnie przenosimy w dół

Kolumnę "Towar" można tu uzupełnić tak jak w poprzedniej tabeli, czyli przy użyciu formuły indeks i podaj pozycję. Tutaj zrobimy to za pomocą tych samych formuł, ale bez tworzenia dodatkowych kolumn w tabeli. Oznacza to, że jedna formuła będzie zawarta w drugiej.

 Zacznijmy od formuły "podaj pozycję".

 Szukana wartość to to co otrzymaliśmy przed chwilą, przy użyciu formuły "max.k" (B84-B88).

 Przeszukiwana tabela to wartości liczbowe wiersza 41, tabeli numer 2, czyli "Marży II na poziomie towaru".

Typ porównania to "0", gdyż ma znaleźć tą samą wartość.

 Mamy zatem:

  • "=PODAJ.POZYCJĘ(B84;B$41:F$41;0)"
  • formuły na razie NIE przenosimy w dół

Zostanie zwrócona pozycja, na której znajduje się wskaźnik, przy czym jest to pozycja w kolejności od lewej dla tabeli 2, a nie od wartości największej.


Teraz trzeba tę formułę obudować. Tutaj dla formuły "indeks" pole tablica to komórki w wierszu, w którym są nazwy towarów, czyli w wierszu 53. 
Następnie po średniku nie podajemy numeru wiersza, zatem wprowadzamy "0", natomiast numer kolumny to nasza formuła "podaj pozycję", która zwróciła numer kolumny, w której dany wskaźnik się znajduje.

Mamy zatem (to na dole to jedna formuła, którą rozdzieliłem na dwa wiersze dla lepszej czytelności):
  • "=INDEKS(B$53:F$53;0;
  • PODAJ.POZYCJĘ(B84;B$41:F$41;0))"

Teraz formułę przenosimy w dół. Gotowa tabela 5:







Autor: