Zastosowanie informatyki w przedsiębiorstwie handlowym - lab. z programu Excel 2007
Wydział Zarządzania, WZ, UTP, Bydgoszcz
=========================================================
=========================================================
=========================================================
Dane:
- firma handluje trzema rodzajami towarów
- planowane ceny zakupu, koszt, wskaźnik narzutu oraz oczekiwany zysk podano w tabeli 1
Tabela 1
Podobnie jak w ćwiczeniu numer 1 tworzymy taką tabelę, wykorzystując komórki Excel'a i uzupełniamy ją tak jak widzimy na rysunku powyżej. Można użyć opcji "kopiuj - wklej" i przekopiować tabelę z pierwszego zadania i tylko nieco ją zmodyfikować.
===============================
===============================
WAŻNE WAŻNE BARDZO WAŻNE
===============================
===============================
Dziś pani prowadząca przypomniała nam o tym, że jak się kopiuje jedną tabelę to musi ona być w docelowym arkuszu na tym samym miejscu, co w źródłowym arkuszu. Inaczej nie wyjdą nam wyniki, bo formuły choć dobre, będą się odnosiły do innych komórek.
Dlatego też mamy dwa sposoby, aby przekopiować wszystko poprawnie do innego arkusza.
1) Wchodzimy do arkusza źródłowego i zaznaczamy wszystkie wypełnione pola począwszy od komórki A1, potem klikamy na to zaznaczenie prawym przyciskiem myszy i wybieramy kopiuj.
Następnie przechodzimy do arkusza docelowego, klikamy na pole A1, potem prawy przycisk myszy i wklej.
2) Szybszy sposób, czyli kopiowanie całego arkusza, a nie tylko wybranych pól. Klikamy na nazwę arkusza prawym przyciskiem myszy, wybieramy opcję "Przenieś lub kopiuj".
Następnie zaznaczamy "Utwórz kopię" i "przenieś na koniec". W ten sposób jako ostatni pojawia nam się przekopiowany arkusz. Nosi tą samą nazwę co źródłowy i (2), dlatego zaraz po kopiowaniu warto ją zmienić.
Jeszcze jedna ważna rzecz
Gdy potrzebujemy między tabelami więcej miejsca, a nie chcemy, aby nam się formuły rozkraczyły, musimy użyć funkcji "wstaw wiersz". Najpierw klikamy na numer wiersza, który znajduje się po lewe stronie PPM i wybieramy "wstaw".
Potem możemy zaznaczyć np. 2 wiersze i ponownie wybrać "wstaw" i dostaniemy dwa nowe, czyste wiersze.
Tyle odnośnie kopiowania :)
========================
========================
Tam gdzie widzimy liczby, wpisujemy liczby, w tej tabeli bowiem nie ma formuł. Pamiętajmy, aby NIE używać opcji "Wstawianie -> Tabela".
Polecenie:
Ile sztuk każdego towaru należy sprzedać, aby osiągnąć planowany zysk?
Zastosuj narzędzie Solver.
Teraz tworzymy tabelę numer 2. Kopiujemy ją z pierwszego zadania, potem dodajemy wiersz "2. Wielkość sprzedaży". Poprawiamy też numerację nazw wierszy, aby do ostatniego z nich była przyporządkowana cyfra "9 i Zysk".
Uzupełnianie tabeli nr 2
To co mamy do obliczenia to "Wartość sprzedaży".
Pamiętajmy, że wpisujemy formuły tylko dla "Towaru A", a potem przenosimy je do na kolejne kolumny. Adres pola ze wskaźnikiem narzutu musi być oznaczony znakiem dolara np. "$H$11". Do działań matematycznych możemy używać znaków +, -, *, / lub stosować formuły np. =iloczyn(), =suma(), np. =H22+H23 lub =SUMA(H22;H23)
- Cena sprzedaży ->; cena zakupu (tab 1) + cena zakupu (tab 1) * wskaźnik narzutu (tab 1) np. "=E7+E7*$H$11"
- Wielkość sprzedaży -> To jest wartość, którą musimy obliczyć. Na razie wpisujemy wartość "100".
- Przychody -> cena sprzedaży (tab 2) * wielkość sprzedaży (tab 2) np. =ILOCZYN(E20;E21)
- Wartość zakupu -> cena zakupu (tab 1) * wielkość sprzedaży (tab 2) np. =ILOCZYN(E7;E21)
- Marża handlowa -> przychody (tab 2) - wartość zakupu (tab 2) np. =SUMA(E22;-E23)
- Koszty indywidualne -> jednostkowe koszty indywidualne (tab 1) * wielkość sprzedaży (tab 2) np. =ILOCZYN(E8;E21)
- Marża II stopnia -> marża handlowa (tab 2) - koszty indywidualne (tab 2) np. =SUMA(E24;-E25)
- Koszty wspólne (stałe) -> wpisujemy adres komórki "Koszty wspólne" z tabeli 1 np. =H9
- Kolumna "Razem" to suma odpowiednich wielkości dla Towaru A, B i C. Wpisujemy np. =SUMA(E22:G22) 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. =SUMA(H26;-H27)
Funkcja "Solver"
Funkcja ta domyślnie nie jest włączona, musimy ją włączyć. Najpierw wybieramy Plik -> Opcje programu Excel.
Następnie w oknie, które się pojawi wybieramy "Dodatki" i potem "Przejdź"
W kolejnym oknie, które się pojawi zaznaczamy opcje "Dodatek Solver" i klikamy OK. Program Excel instaluje dodatek.
Aby korzystać z Solvera, klikamy zakładkę Dane i po prawej stronie będzie przycisk tej funkcji.
Pora na rozwiązanie zadania przy pomocy funkcji Solver.
- jako "Komórka celu" wybieramy komórkę, gdzie jest nasz "9. Zysk". Wpisujemy adres ręcznie lub klikamy na przycisk zaznaczony w okrąg i wskazujemy interesujące nas pole.
- w rubryce "Równa" wybieramy "Wartość" i wpisujemy 110000
- dla "Komórki zmieniane" klikamy przycisk zaznaczony w okrąg i zaznaczamy 3 komórki, tak jak to widać na rysunku poniżej
- klikamy Rozwiąż i potem OK
Jeśli wartości dla "Wartość sprzedaży" to liczby niecałkowite, to musimy dodać w Solverze odpowiednie warunki. Nie możemy bowiem sprzedać 1,26 towaru. Zazwyczaj. Dlatego jeszcze raz włączamy Solver'a i klikamy przycisk "Dodaj".
W oknie jakie się pojawiło klikamy na przycisk oznaczony okręgiem, zaznaczamy nasze 3 komórki wartości sprzedaży, a w liście rozsuwanej wybieramy "int", czyli skrót od Integer - liczba całkowita. Klikamy OK i potem Anuluj, i znowu "Rozwiąż". Excel przelicza teraz możliwe rozwiązania, ale teraz szuka ich wśród liczb całkowitych.
Tak wygląda uzupełniona tabela numer 2. Pamiętajmy, iż skoro mamy 3 niewiadome to może być wiele rozwiązań i zazwyczaj nie ma jednej poprawnej kombinacji.
Laborki - plik do pobrania
Koniec