Tutorialos: Excel: "Warianty prostego budżetu dla kilku towarów"

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

Wydział Zarządzania, WZ, UTP, Bydgoszcz

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

W tej laborce chodzi o wykonanie prostego budżetu, ale tym razem dla kilku towarów i kilku wariantów.

Żeby nie tracić czasu na przepisywanie całej tabeli, najpierw pobierz plik do tego laboratorium z wypisanymi tabelami oraz danymi liczbowymi, ale bez obliczeń
i formuł. Pobierz plik. Formatowanie możecie pozmieniać wedle własnego uznania.

Plik składa się z trzech tabel. Tabela pierwsza zawiera:

wielkość sprzedaży
cenę zakupu
jednostkowe koszty zmienne
cena sprzedaży
koszty stałe sprzedaży

dla pięciu towarów od A do E.

Tabela druga zawiera wielkość bazową i 3 warianty, na podstawie których musimy dokonać obliczeń.

W tabeli trzeciej mamy komórki do uzupełnienia. Dla każdego wariantu musimy obliczyć:

wielkość sprzedaży
cenę sprzedaży
cenę zakupu

z podziałem na towary od A do E.

A następnie wszystkie te wartości trzeba będzie podliczyć w tabeli numer 4.

Polecenie:

oblicz marżę pokrycia III stopnia dla wielkości bazowych
rozważ 3 podane warianty i oblicz marżę pokrycia III stopnia dla każdego z nich

Uzupełnianie tabeli 3

Wypełnienie tej tabeli jest proste, gdyż wystarczy napisać dla trzech komórek (B34, B41, B48) odpowiednie formuły oraz w odpowiednim miejscu wstawić "$".

Tabela 3, część 1 - wielkość sprzedaży

Do wypełnienia tej części potrzebne będą:

kolumna: wielkość sprzedaży (tabela 1)
wiersz: wzrost wolumenu sprzedaży (tabela 2)

Dla komórki B34, czyli dla towaru A, dla wielkości bazowych mamy:

wielkość sprzedaży dla towaru A (tab 1) + (wielkość sprzedaży dla towaru A (tab 1) * wzrost wolumenu sprzedaży (tab 2))
czyli: "=B9+B9*B24"

Teraz jeśli przeniesiemy formułę w prawo będziemy mieli kolejno:

"=C9+C9*C24"
"=D9+D9*D24"
"=E9+E9*E24"

Zależy nam jednak, aby przemnażać tylko przez kolumnę "B" w tabeli 1, czyli przez komórki

B9 (dla towaru A)
B10 (dla towaru B)
B11 (dla towaru C)
B12 (dla towaru D)
B13 (dla towaru E)

Dlatego też stawiamy jedne znak "$" przed B:

"$B9".

Teraz gdy przeniesiemy formułę w prawo dla kolejnych wariantów obliczamy wielkość sprzedaży cały czas dla kolumny B.

Gdy natomiast przenosimy formułę w dół to dla kolejnych towarów zmienia się wartość wiersza, czyli obliczamy dla komórki B10, B11 itd.

Gdybyśmy postawili dwa znaki "$" to dla pierwszego towaru po przeniesieniu formuły w prawo obliczenia byłyby prawidłowe, ale po przeniesieniu formuły w dół, dla kolejnych towarów obliczone wartości byłby takie, jak dla towaru A, czyli byłby błąd.

Na razie mamy taką formułę (w komórce B34):

"=$B9+$B9*B24"

Drugi składnik, czyli wzrost wolumenu sprzedaży w tabeli 2, dla kolejnych towarów A, B, C, D, E w tabeli 3, czyli dla następnych wierszy się nie zmienia.

Zarówno dla towaru A, jak i towaru E, obliczeń dokonujemy dla wiersza numer 24 i nie może się on zmieniać. Dlatego dolar musi być postawiony przed wartością wiersza:

B$24

Jeśli postawimy "$" także przed B, to po przeniesieniu formuły (w prawo) dla kolejnych wariantów 1, 2, 3, wartości będą obliczane tak jak byśmy przemnażali przez wielkość bazową (tab 2), czyli byłby to błąd.

Gotowa formuła wygląda tak:

"=$B9+$B9*B$24"

Tę formułę przenosimy do komórek z zakresu od B34 do E38 i mamy:

Tabela 3, część 2 - cena sprzedaży

Do wypełnienia tej części potrzebne będą:

kolumna: cena sprzedaży (tabela 1)
wiersz: wzrost ceny sprzedaży (tabela 2)

Tutaj formuła jest analogiczna do tej z poprzedniej części, dlatego nie będę wyjaśniał dlaczego stosujemy lub nie stosujemy znaku "$".

Formuła dla komórki B41:

cena sprzedaży (tab 1) + (cena sprzedaży (tab 1) * wzrost ceny sprzedaży (tab 2))
czyli "=$E9+$E9*B$25"

Formułę przenosimy w prawo, a następnie w dół, wypełniając komórki od B41 do E45. Wyniki przedstawiają się tak:

Tabela 3, część 3 - cena zakupu

Do wypełnienia tej części potrzebne będą:

kolumna: cena zakupu (tabela 1)
wiersz: wzrost ceny zakupu (tabela 2)

Tutaj formuła także jest analogiczna do tej z poprzednich części, dlatego tu również nie będę wyjaśniał dlaczego stosujemy lub nie stosujemy znaku "$".

Formuła dla komórki B48:

cena zakupu (tab 1) + (cena zakupu (tab 1) * wzrost ceny zakupu (tab 2))
czyli "=$C9+$C9*B$26"

Formułę przenosimy w prawo, a następnie w dół, wypełniając komórki od B48 do E52. Wyniki przedstawiają się tak:

Cała tabela 3 przedstawia się następująco:

Tabela 4

Ta tabela będzie najtrudniejsza do uzupełnienia, gdyż ze względu na wiele towarów, trzeba będzie stosować formułę "suma iloczynów".

Ogólne wyjaśnienie poszczególnych wielkości:

Przychody ze sprzedaży: wielkość sprzedaży (tab 3) * cena sprzedaży (tab 3)
Wartość zakupu: wielkość sprzedaży (tab 3) * cena zakupu (tab 3)
Marża I stopnia: przychody ze sprzedaży (tab 4) - wartość zakupu (tab 4)
Koszty zmienne sprzedaży: jednostkowe koszty zmienne sprzedaży (tab 1) * wielkość sprzedaży (tab 3)
Marża II stopnia: marża I stopnia (tab 4) - koszty zmienne sprzedaży (tab 4)
Koszty stałe sprzedaży: koszty stałe sprzedaży (tab 1)
Marża III stopnia: marża II stopnia (tab 4) - koszty stałe sprzedaży (tab 4)
Wskaźnik marży III: marża III stopnia (tab 4) / przychody ze sprzedaży (tab 4)

Uzupełnienie tabeli 4

Podane poniżej formuły dotyczyć będą wielkości bazowej, na koniec zaznaczamy całą kolumnę i przenosimy w prawo dla pozostałych trzech wariantów.

Przychody ze sprzedaży to suma:

przemnożenia wielkości sprzedaży towaru A (tab 3) i ceny sprzedaży towaru A (tab 3)
przemnożenia wielkości sprzedaży towaru B (tab 3) i ceny sprzedaży towaru B (tab 3)
...
przemnożenia wielkości sprzedaży towaru E (tab 3) i ceny sprzedaży towaru E (tab 3)

W skrócie przemnażamy wielkość (tab 3) i cenę (tab 3) sprzedaży dla każdego towaru, a następnie sumujemy wszystko.

Formuła:

"=SUMA.ILOCZYNÓW(B34:B38; B41:B45)"

Suma iloczynów przemnaża odpowiadające sobie komórki, a następnie sumuje wszystkie wyniki mnożenia.

Wartość zakupu to suma:

przemnożenia wielkości sprzedaży towaru A (tab 3) i ceny zakupu towaru A (tab 3)
przemnożenia wielkości sprzedaży towaru B (tab 3) i ceny zakupu towaru B (tab 3)
...
przemnożenia wielkości sprzedaży towaru E (tab 3) i ceny zakupu towaru E (tab 3)

W skrócie przemnażamy wielkość sprzedaży (tab 3) i cenę (tab 3) zakupu dla każdego towaru, a następnie sumujemy wszystko.

Formuła:

"=SUMA.ILOCZYNÓW(B34:B38; B48:B52)"

Marża I stopnia to różnica:

przychodów ze sprzedaży (tab 4)
wartości zakupu (tab 4)

Formuła:

"=B57-B58"

Koszty zmienne sprzedaży to suma:

przemnożenia jednostkowych kosztów zmiennych sprzedaży towaru A (tab 1) i wielkości sprzedaży towaru A (tab 3)
przemnożenia jednostkowych kosztów zmiennych sprzedaży towaru B (tab 1) i wielkości sprzedaży towaru B (tab 3)
...
przemnożenia jednostkowych kosztów zmiennych sprzedaży towaru E (tab 1) i wielkości sprzedaży towaru E (tab 3)

W skrócie przemnażamy jednostkowe koszty zmienne sprzedaży (tab 1) i wielkość sprzedaży (tab 3) dla każdego towaru, a następnie sumujemy wszystko.

Przy czym tu dla wielkości bazowych i pozostałych wszystkich wariantów, jednostkowe koszty zmienne sprzedaży są takie same (znajdują się w jednej kolumnie tabeli 1) stąd musimy zastosować jednego dolara dla pierwszej części sumy iloczynów.
Dolara stawiamy przed symbolem kolumny, gdyż to ona (kolumna) ma pozostać niezmienna, zwłaszcza, że formuła będzie przenoszona w prawo (co powoduje zmianę symbolu nazwy kolumny na kolejne litery alfabetu).

Formuła:

"=SUMA.ILOCZYNÓW($D9:$D13; B34:B38)"

Marża II stopnia to różnica:

marży I stopnia (tab 4)
kosztów zmiennych sprzedaży (tab 4)

Formuła:

"=B59-B60"

Koszty stałe sprzedaży to suma:

kosztów stałych sprzedaży dla wszystkich towarów (tab 1)

W skrócie sumujemy koszty stałe sprzedaży, które znajdują się w tabeli 1 dla wszystkich pięciu towarów.

Podobnie jak poprzednio, dla wszystkich wariantów mamy te same koszty stałe sprzedaży, więc przy przenoszeniu formuły w prawo, symbol kolumny musi pozostać niezmienny, stąd "$" w tym miejscu.

Formuła: