Zastosowanie informatyki w przedsiębiorstwie handlowym - lab. z programu Excel 2007
Wydział Zarządzania, WZ, UTP, Bydgoszcz
=========================================================
=========================================================
=========================================================
Kopiujemy poprzedni arkusz do nowego tak jak to zostało objaśnione w poście: Excel: "Szukaj wyniku". Tam też zostało wyjaśnione wstawianie nowych wierszy.
Dane:
Dane:
- firma handluje trzema rodzajami towarów
- planowane ceny zakupu, koszt, planowana sprzedaż oraz oczekiwany zysk podano w tabeli 1
Tabela 1
Tabela 1 wygląda nieco inaczej, niż w poprzednim zadaniu. Zmiany:
- "Oczekiwany zysk" zmieniamy na 15000
- Zamiast "Wskaźnika narzutu" mamy "Planowaną sprzedaż". Wartości do wpisania przepisujemy z rysunku poniżej.
Polecenie:
1. Ustal ograniczenia na cenę sprzedaży, przyjmując, że:
- nie może ona być wyższa od ceny zakupu o więcej niż o 20% (pamiętaj, aby ta wartość była ujęta w osobnej komórce, patrz rysunek poniżej)
- musi pokryć jednostkowe koszty indywidualne związane z towarem
2. Znajdź ceny sprzedaży, które przy obliczonych ograniczeniach umożliwiają osiągnięcie:
- maksymalnego zysku
- planowanego zysku
3. Po znalezieniu cen sprzedaży zapewniającej planowany zysk oblicz wskaźnik narzutu na cenę zakupu oraz wskaźnik marży
Ad 1.
Najpierw przepisujemy tabelę z rysunku poniżej.
Cena sprzedaży maksymalnej nie może być większa od ceny zakupu o więcej, niż wynosi podany wskaźnik narzutu, czyli 20%. Zatem nasze maksimum to cena zakupu + cena zakupu pomnożona przez maksymalny wskaźnik narzutu.
Działanie wygląda następująco:
- Cena sprzedaży max -> cena zakupu (tab 1) + cena zakupu (tab 1) * max wskaźnik narzutu (komórka H15) np. =E7+E7*$H$15
Formułę kopiujemy dla towarów B i C.
Cena sprzedaży minimalnej musi pokryć cenę zakupu oraz koszty jednostkowe, dlatego musimy zsumować te dwie wartości. Formuła:
- Cena sprzedaży min -> cena zakupu (tab 1) + koszty jednostkowe (tab 1) np. =E7+E8
Formułę kopiujemy dla towarów B i C.
Uzupełniona tabela wygląda tak:
Ad 2.
Tabela numer 2 nie różni się od tej skopiowanej w zadaniu poprzednim, oprócz wierszy:
- 1. Cena sprzedaży
- 2. Wielkość sprzedaży
"Cenę sprzedaży" wpisujemy ręcznie, przepisując wartości "Cena sprzedaży min" z punktu "Ad 1".
Dla "Wielkości sprzedaży" robimy odniesienie do wartości zawartych w tabeli 1, np. =E11. Odniesienia kopiujemy dla dwóch kolejnych kolumn, czyli towarów B i C. Nie wpisujemy tych wartości ręcznie, gdyż odniesienie pozwala nam edytować tabelę 1 i automatycznie wszystkie parametry w tabeli 2 nam się przeliczają.
Teraz odpalamy Solver'a. Najpierw obliczamy dla jakich cen sprzedaży osiągniemy maksymalny zysk. Jako komórkę celu wybieramy "9. Zysk", dla "równa:" wybieramy "Maks", komórki zmienne to "1. Cena sprzedaży" dla wszystkich towarów. Warunki ograniczające to te zawarte w tabeli z adnotacji 1. Nasza cena sprzedaży musi się zawierać w podanych tam wartościach. Czyli np. Dla towaru A cena musi być większa-równa 34, a mniejsza-równa 36 złotych.
Pamiętajmy, że przy dodawaniu warunków nie musimy robić odniesienia do pojedynczych pól, ale możemy zaznaczyć od razu kilka komórek na raz. Całość przedstawia rysunek poniżej.
Pamiętajmy, że przy dodawaniu warunków nie musimy robić odniesienia do pojedynczych pól, ale możemy zaznaczyć od razu kilka komórek na raz. Całość przedstawia rysunek poniżej.
Klikamy "Rozwiąż" i OK. Oto nasze rozwiązanie:
Maksymalny zysk oraz ceny dla tego obliczenia przepisujemy do nowo utworzonej tabelki obok polecenia numer 2.
Teraz druga część polecenia, czyli dla jakich cen osiągniemy planowany zysk wynoszący 15000 złotych. Odpalamy solver'a, nie zmieniamy nic oprócz parametru "Równa:" z "Maks" na "Wartość" i wpisujemy 15000. Potem klikamy "Rozwiąż" i OK.
Moje wyniki przedstawia rysunek poniżej, ale każdemu mogą wyjść inne, a zadanie i tak będzie rozwiązane poprawnie. Najważniejsze jest dobrze ustawić opcje solver'a.
Moje wyniki przedstawia rysunek poniżej, ale każdemu mogą wyjść inne, a zadanie i tak będzie rozwiązane poprawnie. Najważniejsze jest dobrze ustawić opcje solver'a.
Wynik przepisujemy do tabeli znajdującej się przy poleceniu numer 2.
Ad 3.
Najpierw robimy taką tabelę:
Zaznaczamy komórki zaznaczone na różowo, klikamy PPM, wybieramy formatuj komórki. W oknie, które wyskoczyło wchodzimy w zakładkę "Liczby", wybieramy "Procentowe" oraz 2 miejsca po przecinku.
Wskaźnik narzutu na cenę zakupu to:
((cena sprzedaży (tab 2) - cena zakupu (tab 1)) : cena zakupu (tab 1) np. dla mojego arkusza =(E38-E7)/E7.
Wskaźnik marży (od ceny sprzedaży) to:
((cena sprzedaży (tab 2) - cena zakupu (tab 1)) : cena sprzedaży (tab 2) np. dla mojego arkusza =(E38-E7)/E38.
Ad 3.
Najpierw robimy taką tabelę:
Zaznaczamy komórki zaznaczone na różowo, klikamy PPM, wybieramy formatuj komórki. W oknie, które wyskoczyło wchodzimy w zakładkę "Liczby", wybieramy "Procentowe" oraz 2 miejsca po przecinku.
Wskaźnik narzutu na cenę zakupu to:
((cena sprzedaży (tab 2) - cena zakupu (tab 1)) : cena zakupu (tab 1) np. dla mojego arkusza =(E38-E7)/E7.
Wskaźnik marży (od ceny sprzedaży) to:
((cena sprzedaży (tab 2) - cena zakupu (tab 1)) : cena sprzedaży (tab 2) np. dla mojego arkusza =(E38-E7)/E38.
Pamiętajmy o nawiasach, aby zachować odpowiednią kolejność wykonywania działań. Gotowe formuły kopiujemy do pozostałych towarów i otrzymujemy takie rozwiązania:
