Zastosowanie informatyki w przedsiębiorstwie handlowym - lab. z programu Excel 2007
Wydział Zarządzania, WZ, UTP, Bydgoszcz
=========================================================
=========================================================
=========================================================
Szukanie minimalnego wskaźnika narzutu na cenę zakupu w firmie handlowej.
Dane:
- firma handluje trzema rodzajami towarów
- planowane ceny zakupu, koszty oraz wielkość sprzedaży podano w tabeli 1
Tabela 1
Tworzymy taką tabelę, wykorzystując komórki Excel'a i uzupełniamy ją tak jak widzimy na rysunku powyżej. Tam gdzie widzimy liczby, wpisujemy liczby, w tej tabeli bowiem nie ma formuł. Pamiętajmy, aby NIE używać opcji "Wstawianie -> Tabela".
Polecenie:
Jaki należy zastosować wskaźnik narzutu na ceny zakupu, aby osiągnąć planowany zysk?
Polecenie to przepisujemy do jednej z komórek Excel'a. Następnie tworzymy komórkę gdzie będzie znajdował się wskaźnik narzutu, który będziemy musieli obliczyć.
Teraz przepisujemy do komórek Excel'a tabelę numer 2.
Tabela 2
Uzupełnianie tabeli nr 2
Zanim przejdziemy do uzupełniania tabeli musimy pamiętać, aby komórkę gdzie znajduje się wskaźnik narzutu odpowiednio oznaczyć poprzez użycie znaku dolara "$" przed symbolem wiersza i kolumny np. $g$18. W praktyce oznacza to, że gdy dla jednej kolumny utworzymy formułę i przeniesiemy ją do kolejnej to odpowiednie współrzędne pola ulegną zmianie, a zastosowanie znaku dolara zachowa nam adres komórki ze wskaźnikiem narzutu bez zmian.
Poniższe formuły tworzymy tylko dla towaru A, a następnie przenosimy je do kolejnych dwóch kolumn.
Wpisujemy przykładowy wskaźnik narzutu - 10%.
- Cena sprzedaży -> cena zakupu (tab 1) + cena zakupu*wskaźnik narzutu np. "=D10+D10*$G$20"
- Przychody -> cena sprzedaży (tab 2) * wielkość sprzedaży (tab 1) np. =D27*D11
- Wartość zakupu -> cena zakupu (tab 1) * wielkość sprzedaży (tab 1) np. =D10*D11
- Marża handlowa -> przychody (tab 2) - wartość zakupu (tab 2) np. =D28-D29
- Koszty indywidualne -> wielkość sprzedaży (tab 1) * jednostkowe koszty indywidualne (tab 1) np. =D11*D12
- Marża II stopnia -> marża handlowa (tab 2) - koszty indywidualne (tab 2) np. =D30-D31
- Koszty wspólne (stałe) -> wpisujemy adres komórki "Koszty wspólne" z tabeli 1 np. =G13
- Kolumna "Razem" to suma odpowiednich wielkości dla Towaru A, B i C. Wpisujemy np. =SUMA(D28:F28) dla wiersza przychody w tabeli nr 2 i formułę przenosimy w dół, aż do kosztów stałych
- Zysk -> Zsumowane marże II stopnia w kolumnie "Razem" - koszty stałe np. =G32-G33
Funkcja "Szukaj wyniku"
Aby skorzystać z tej funkcji, w głównym menu zakładek wybieramy "Dane", potem "Analiza warunkowa" lub "Analiza symulacji", potem "Szukaj wyniku"
Okno, które się pojawi zawiera następujące pola:
- Ustaw komórkę (Zysk)
- Wartość (110000)
- Zmieniając komórkę (Wskaźnik narzutu)
Sposób uzupełnienia przedstawia poniższy rysunek:
Adresy komórek możemy wpisać ręcznie lub kliknąć na zaznaczony przycisk i wskazać odpowiednie pole, po czym kliknąć ten przycisk jeszcze raz.
Gdy już mamy wszystko uzupełnione, klikamy OK i mamy obliczony wskaźnik narzutu - 16,98%
Koniec zadania :)
Plik z laborkami
Pobierz Plik
PS 1. Obliczanie progu rentowności
W oknie szukaj wyniku w pole "Wartość" zamiast "110000" wpisujemy "0" i klikamy OK. Wyszło 15,83%
PS 2. Zaokrąglanie ceny sprzedaży
W tabeli 2, dla wiersza "Cena sprzedaży" stosujemy formułę "=ZAOKR.GÓRA(Formuła jaka była w tym polu poprzednio;2)"
np. "=ZAOKR.GÓRA(D10+D10*$G$20;2)". Dwójka po średniku oznacza do ilu miejsc po przecinku mamy zaokrąglić. Zaokrąglając cenę sprzedaży dla wszystkich towarów, zmienia nam się wartość zysku na 110900