Controlling operacyjny w przedsiębiorstwie handlowym - lab. z programu Excel 2007
Wydział Zarządzania, WZ, UTP, Bydgoszcz
=========================================================
=========================================================
=========================================================
Temat:
Analiza dynamiki budżetu
Ta laborka składa się z dwóch prostych zadań.
Zadanie 1
Najwięcej czasu zajęło przepisywanie tabeli do zadania pierwszego, dlatego, żeby nie tracić czasu pobierz plik. Mamy w nim dwie tabele:
W tej z lewej, wpisane wartości liczbowe zostały już podane. Puste pola w tabeli lewej i prawej musimy poobliczać.
Tabela z lewej
Mamy taką tabelę:
Przychód to "cena sprzedaży" (wiersz 7) pomnożona przez "wartość sprzedaży" (wiersz 8).
Formuła dla komórki B9:
- "=B7*B8"
- przenosimy ją do pozostałych trzech komórek w wierszu
Marża brutto to "przychód" (wiersz 9) minus "koszt zakupu" (wiersz 10).
Formuła dla komórki B11:
- "=B9-B10"
- przenosimy ją do pozostałych trzech komórek w wierszu
Wydatki jest to suma wszystkich wydatków zawartych w budżecie, wypisanych w budżecie między wierszami 12, a 25, które też wliczamy.
Formuła dla komórki B26:
- "=SUMA(B12:B25)"
- przenosimy ją do pozostałych trzech komórek w wierszu
Zysk jest to "przychód" (wiersz 9) minus "wydatki" (wiersz 26).
Formuła dla komórki B27:
- "=B9-B26"
- przenosimy ją do pozostałych trzech komórek w wierszu
Obliczona tabela z lewej przedstawia się tak:
Tabela z prawej
Tutaj mamy obliczyć wskaźniki wzrostu, a okresem bazowym będzie okres I.
Oznacza to, że w danym wierszu tabeli prawej wprowadzamy dzielenie wszystkich komórek z tabeli lewej, które są w tym samym wierszu, przez tą komórkę z tabeli lewej, która jest w kolumnie "Okres I".
W skrócie, będąc w tym samym wierszu i mając 4 okresy, dzielimy je kolejno przez "okres I".
Nasza formuła dla komórki H6:
- "=B6/B6"
Teraz jeśli przeniesiemy formułę w prawo to ciągle będziemy mieli dzielenie jednej komórki przez nią samą, a mamy dzielić przez tą z pierwszej kolumny.
Dlatego też w mianowniku przed symbolem kolumny trzeba postawić "$".
Poprawna formuła to:
- "=B6/$B6"
Teraz przenosimy formułę do wszystkich komórek tabeli i otrzymujemy:
Zadanie 2
Tutaj musimy przekopiować jedną z tabel z poprzedniej laborki, która jest w orientacji pionowej i wkleić ją w orientacji poziomej. No a potem trochę trzeba ją zmodyfikować.
Jako efekt finalny powinniśmy otrzymać taką tabelę:
W pliku to tej laborki w arkuszu 2 mamy na razie coś takiego:
Jeśli nie masz pliku z poprzednich zajęć pobierz go i otwórz.
Zaznacz ten obszar w tabeli 1 i przekopiuj:
Teraz przejdź do pliku z tej laborki, przejdź na arkusz 2 i kliknij PPM na komórkę A10 i wybierz "Wklej specjalnie...".
W oknie, które się pojawi zaznaczamy "Transpozycja" i klikamy OK.
Na razie wklejona tabela wygląda tak:
Trzeba ją najpierw sformatować, aby wyglądała czytelnie, następnie trzeba będzie dodać i usunąć pewne wiersze i kolumny.
Najpierw zaznaczamy komórki od A10 do N15 i klikamy na przycisk pokazany na rysunku:
Następnie poszerzamy wiersz numer 10, zaznaczamy zawarte w nim komórki, klikamy PPM i wybieramy "Formatuj komórki...".
Przechodzimy do zakładki "Wyrównanie" i zaznaczamy "Zawijaj tekst". Klikamy OK.
Teraz usuwamy kolumny "L", a potem "I". Zawierają kolejno:
- L - "suma marż na poziomie sprzedawców"
- I - "suma marż na poziomie towaru"
W tym celu klikamy na symbol kolumny, klikamy PPM i wybieramy "Usuń".
Teraz klikamy na symbol wiersza numer 16 PPM i wybieramy "Wstaw", robimy jeszcze raz to samo, następnie robimy także to samo tylko, że dla wiersza 14.
Na razie mamy taką tabelę:
Najpierw skupimy się na komórkach od I10 do L18.
Zawartość komórki:
- I11 przenosimy do I14
- I15 przenosimy do I17
- K11 przenosimy do K18
Jeśli w komórkach w tym zakresie są jakieś formuły to kasujemy je klawiszem "Delete".
I mamy:
Teraz przenosimy się na lewo i do komórki B14 i B17 wpisujemy "Suma", a do A18 wpisujemy "Razem".
Tabela jest gotowa, aby wprowadzić nowe formuły. Wygląda teraz tak:
Przechodzimy do komórki C14. Jest to suma wartości przychodów ze sprzedaży towarów od 1 do 3, dla sprzedawcy 1.
Formuła dla komórki C14:
- "=SUMA(C11:C13)"
- formułę przenosimy w ramach tego wiersza, aż do kolumny H.
Podobnie postępujemy dla drugiego sprzedawcy. Formuła dla komórki C17:
- "=SUMA(C15:C16)"
- formułę przenosimy w ramach tego wiersza, aż do kolumny H włącznie.
Mamy na razie taką tabelę:
Przechodzimy do komórki J14. Marża III stopnia to różnica "Marży II stopnia" i "kosztów sprzedaży związanych ze sprzedawcą".
Formuła dla J14:
- "=H14-I14"
Dla komórki J17 postępujemy analogicznie.
Formuła dla J17:
- "=H17-I17"
Teraz mamy:
Przechodzimy do ostatniego wiersza, czyli tego z numerem 18, do komórki C18.
W tym wierszu sumujemy wiersze 14 i 17, gdyż tam znajdują się sumy wartości w kolumnach dla poszczególnych sprzedawców.
Formuła dla C18:
- "=C14+C17"
- formułę przenosimy w obrębie tego wiersza, aż do kolumny J włącznie
Na koniec przechodzimy do komórki L18. Marża IV stopnia to różnica "Marży III stopnia" i "kosztów utrzymania działu sprzedaży".
Formuła dla L18:
- "=J18-K18"
Cała tabela po formatowaniu przedstawia się tak:
Pliki do pobrania: