Rachunkowość zarządcza - martynowicz.netmartynowicz.net/studenci/Materialy - II Zajecia.pdf ·...

Rachunkowość zarządcza

laboratoria komputerowe

MS Excel

PROWADZĄCY: ALEKSANDRA MARTYNOWICZ

OPRACOWAŁ: ZDZISŁAW KES

Wrocław, luty-czerwiec 2010

R a c h u n k o w o ś ć z a r z ą d c z a L A B O R A T O R I U M

2

Spis treści:

1. Wymagania wstępne .................................................................................................................................. 3

2. Analiza progu rentowności produkcji wielo-asortymentowej ................................................................... 3

3. Analiza progu rentowności produkcji jedno-asortymentowej ................................................................... 8

4. Wybór asortymentu produkcji przy ograniczonych zdolnościach produkcyjnych ................................... 12


3

1. Wymagania wstępne

Studenci uczestniczący w laboratoriach powinni znad podstawy funkcjonowania arkusza kalkulacyjnego. W

szczególności powinni potrafid:

uruchomid aplikację MS Excel,

otworzyd i zapisad plik w formacie xls,

wprowadzad do komórek teksty, wartości, formuły i funkcje,

formatowad komórki,

kopiowad i wklejad zawartośd komórek różnymi sposobami (menu górne, skróty klawiaturowe i przy

pomocy uchwyt przeciągania).

Zajęcia będą prowadzone z wykorzystaniem materiałów przygotowanych wcześniej w postaci elektronicznej

(dostępnych na platformie WUE), tablicy, projektora. Materiały dla studentów obejmują plik PDF oraz pliki

xls. W pliku PDF studenci otrzymają opisy zadao, polecenia, rozwiązania a w plikach xls znajdują się dane

źródłowe przygotowane do pracy w laboratorium.

2. Analiza progu rentowności produkcji wielo-asortymentowej

Na podstawie informacji o kosztach zmiennych i stałych można przeprowadzad analizę progu rentowności,

która informuje o poziomie sprzedaży gwarantującym jednostce gospodarczej wynik na sprzedaży równy 0.

W przypadku sprzedaży wielo-asortymentowej próg rentowności najczęściej wyznaczany jest w ujęciu

wartościowym z założeniem niezmienności struktury sprzedaży. Ponadto przy tego rodzaju sprzedaży

istnieją progi rentowności dla całego przedsiębiorstwa jak i poszczególnych asortymentów. Dodatkowo przy

sprzedaży wielu asortymentów zachodzi możliwośd szybszej sprzedaży asortymentów lepszych (lub

gorszych) co będzie powodowad, że próg rentowności będzie osiągany szybciej (lub wolniej).

Wyznaczenie progu rentowności dla produkcji wieloasortymentowej jest zagadnieniem skomplikowanym.

Wiąże się to z tym, że próg rentowności nie jest jednym punktem, lecz zbiorem punktów, których liczba jest

równa liczbie rozpatrywanych produktów. Głównym problemem utrudniającym przeprowadzenie analizy

progu rentowności w warunkach produkcji wieloasortymentowej jest podejście do kosztów stałych.

Można wyróżnid trzy odmienne podejścia do kosztów stałych ze względu na ich wpływ na sposób

przeprowadzenia analizy progu rentowności w warunkach produkcji wieloasortymentowej. Oto one:

1) koszty stałe są w całości rozliczone miedzy poszczególne produkty,

ĆWICZENIE 3

ZAGADNIENIE

od 20 do 30 min


4

2) koszty stałe w całości odnoszone są do przedsiębiorstwa,

3) koszty stałe w części są rozliczone między produkty, a w części do przedsiębiorstwa.

Pierwsza metoda ma zastosowanie w sytuacji, gdy cała kwota kosztów stałych została rozliczona miedzy

produkty. Najważniejszym zagadnieniem jest tutaj rozliczenie kosztów stałych między produkty, w tym

zwłaszcza dobór odpowiedniej podstawy dla tego rozliczenia. Jest to zagadnienie podobne do rozliczenia

kosztów pośrednich. Jako podstawę do rozliczenia kosztów stałych między produkty przyjmuje się

najczęściej marżę pokrycia zrealizowaną na poszczególnych produktach. W celu rozliczenia kosztów stałych

na produkty należy obliczyd współczynnik narzutu:

,

.1

n

i

i

ss

M

KWNK

gdzie:

WNKs – współczynnik narzutu kosztów stałych, Ks – koszty stałe przedsiębiorstwa, Mi – globalna marża pokrycia osiągnięta na sprzedaży i-tego produktu (Mi = mi

. Qi ,), Qi – wielkośd sprzedaży i-tego produktu, mi – jednostkowa marża pokrycia i-tego produktu (mi = pi – kzi) pi – cena sprzedaży i-tego produktu, kzi – jednostkowy koszt zmienny i-tego produktu.

Narzut kosztów stałych na poszczególne produkty jest obliczony według wzoru:

Ksi = WNKs . Ks.

Produkcję poszczególnych asortymentów rozpatruje się dalej niezależnie od siebie i analizę progu

rentowności dla każdego asortymentu przeprowadza się niezależnie od siebie, tak jak dla produkcji

jednorodnej. Ilościowe progi rentowności poszczególnych produktów wyznacza się na podstawie wzoru:

zii

si

oikp

KQ (i = 1,2,...,n),

gdzie:

Qoi – ilościowy próg rentowności i-tego produktu, pi – cena sprzedaży i-tego produktu, kzi – jednostkowy koszt zmienny i-tego produktu.

Wartościowe progi rentowności poszczególnych produktów wyznacza się na podstawie:


5

Soi = pi . Qoi (i = 1,2,...,n),

natomiast globalny wartościowy próg rentowności:

,SSn

1i

oio

gdzie: Soi –wartościowy próg rentowności i-tego produktu, So – globalny wartościowy próg rentowności.

W drugiej sytuacji, tzn. gdy koszt stałe zostały w całości przyporządkowane przedsiębiorstwu, w sposób

jednoznaczny można wyznaczyd jedynie wartościowy próg rentowności. Jest to próg rentowności przy danej

strukturze asortymentowej produkcji. Zmiana tej struktury powoduje zazwyczaj zmianę wysokości progu

rentowności. Wartościowy próg rentowności dla całego przedsiębiorstwa jest stosunkiem kosztów stałych

do przeciętnej marzy pokrycia wszystkich produktów:

,U

KS s

0

gdzie:

.

1

1

n

i

ii

n

i

ii

Qp

Qm

U

Aby obliczyd progi rentowności dla poszczególnych asortymentów konieczne jest przemnożenie progu

rentowności dla całej firmy przez udział procentowy przychodów ze sprzedaży danego produktu.

W przedsiębiorstwie wytwarzany jest sprzęt AGD. W ofercie wyróżnia się 8 asortymentów oznaczonych: P1,

P2, P3, P4, L1, Z1, Z2, LZ1, LZ2, LZ3. Na podstawie danych w pliku Zadanie2.xls ustal próg rentowności dla

całego przedsiębiorstwa (przy wykorzystaniu przeciętnej marży pokrycia) oraz progi minimalny (przy

założeniu sprzedaży od najlepszego do najgorszego asortymentu) i maksymalny (przy założeniu sprzedaży

od najgorszego do najlepszego asortymentu) oraz każdego z asortymentów oddzielnie. Dodatkowo wyznacz

progi rentowności dla całego przedsiębiorstwa w sposób graficzny.

Rozwiązanie zadania polega na wykonaniu następujących kroków:

1. Ustalenie marży pokrycia i progu rentowności dla przedsiębiorstwa i asortymentów.

2. Narysowanie wykresu.

3. Ustalenie progu rentowności dla założenia, że sprzedawano wyroby od najlepszego do najgorszego.

4. Ustalenie progu rentowności dla założenia, że sprzedawano wyroby od najgorszego do najlepszego.

Ad. 1. Przed obliczeniem progu rentowności dla przedsiębiorstwa, jako całości należy obliczyd przychody ze

sprzedaży oraz koszty zmienne. W tym celu zaznaczamy komórki B15:K16 i z klawiatury wpisujemy formułę

POLECENIE

ROZWIĄZANIE


6

=B4*B$6 i wciskamy skrót klawiaturowy CTRL+Enter, aby wypełnid cały zakres formułami (przy korzystaniu

z tego skrótu aktywną komórką w zaznaczonym zakresie musi byd komórka znajdująca się w jego lewym

górnym rogu – tu B15). Następnie w celu rozszerzenia zakresu zaznaczonych komórek do kolumny L

wciskamy skrót SHIFT+(strzałka kursora) i bezpośrednio po tym, przy pomocy skrótu lewy_ALT+=

wprowadzamy funkcję SUMA w kolumnie Razem dla przychodów i kosztów zmiennych. W podobny sposób

wprowadzamy formułę obliczającą marżę pokrycia. Zaznaczamy komórki B17:L17, wprowadzamy formułę

=B15-B16 i wciskamy skrót CRTL+Enter.

Następnie wprowadzamy formuły dla zakresów (formuła jest wprowadzana do pierwszej komórki w

zakresie i następnie kopiowana do pozostały komórek w zakresie) i komórek:

B18:L18 Stopa marży pokrycia *%+ =B17/B15

B19:L19 Udział przychodów *%+ =B15/$L$15

L20 Próg rentowności dla przedsiębiorstwa *zł+ =I8/L18

B21:K21 Próg rentowności dla asortymentów *zł+ =$L$20*B19

B22:K22 Próg rentowności dla asortymentów *szt.] =B21/B4

W komórce L20 próg rentowności dla przedsiębiorstwa obliczany jest, jako iloraz kosztów stałych (I8) i

przeciętnej stopy marży pokrycia (L18).

Ad. 2. Wyznaczenie na wykresie progu rentowności wiąże się z określeniem współrzędnych dla dwóch

punktów. Pierwszy punkt jest wyznaczany dla założenia, że sprzedaż wynosi zero a drugi, że sprzedano

wszystkie asortymenty. Pierwszą współrzędną dla pierwszego punktu wpisujemy do komórki B27 (wartośd

0) a drugą współrzędną do komórki B28 ( =-I8 – czyli ujemne koszty stałe). Pierwszą współrzędną drugiego

punktu wpisujemy do komórki C27 ( =L15 – czyli łączne przychody ze sprzedaży) a drugą współrzędną do

komórki C28 ( =L17-I8 – czyli globalna marża pokrycia minus koszty stałe).

Następnie należy zaznaczyd komórki A27:C28 i menu górnego wybrad opcję

{Wstaw|Wykres}(Wstawianie|Wykres|Punktowy połączony liniami). W pierwszym kroku kreatora

wykresów w polu Typ wykresu należy wybrad typ XY (Punktowy), w polu podtypu wykresu wskazujemy na

Wykres punktowy z punktami danych połączonymi liniami i wcisnąd przycisk Dalej. W drugim kroku kreatora

wciskamy tylko przycisk Dalej. W trzecim kroku kreatora opisujemy tytuł wykresu (Excel 2007 – Narzędzia

wykresów|Układ|Tytuł wykresu) (Analiza progu rentowności) i opis osi X (Przychody *zł+) i Y (Wynik *zł+). W

ostatnim kroku kreatora zaznaczamy opcję Jako nowy arkusz i wciskamy przycisk Zakoocz (Excel 2007 –

Narzędzia wykresów|Projektowanie|Przenieś wykres|Nowy arkusz).

Ad. 3. Ustalenie minimalnego progu rentowności wiąże się posortowaniem asortymentów od najlepszego

(o największej stopie marży pokrycia) do najgorszego (o najmniejszej stopie marży pokrycia). Przed


7

posortowaniem asortymentów należy skopiowad wartości z komórek B14:K18 do zakresu B34:K38. W tym

celu zaznaczamy zakres kopiowany i wciskamy skrót klawiaturowy CTRL+C, stawiamy kursor na komórce

B34 i wybieramy z menu górnego opcję ,Edycja|Wklej specjalnie- (Excel 2007 – Narzędzia

podstawowe|Wklej specjalnie) i w oknie Wklej specjalnie zaznaczamy opcję Wartości i wciskamy przycisk

OK. Przy zaznaczonym obszarze B34:K38 wybieramy z menu górnego opcję ,Dane|Sortuj}, dalej w oknie

Sortowanie wciskamy przycisk Opcje i zaznaczamy opcję Sortuj od lewej do prawej i wciskamy przycisk OK.

Następnie w polu Sortuj według należy wybrad Wiersz 38, zaznaczyd opcję Malejąco (Od największej do

najmniejszej) i wcisnąd przycisk OK.

W części oznaczonej, jako dane do wykresu należy wprowadzid następujące formuły (dla zakresu komórek

formuła jest wprowadzana do pierwszej komórki zakresu i następnie na całośd zakresu):

C41:L41 Przychody ze sprzedaży *zł] =SUMA(B35:$B35)

C42:L42 Wynik na sprzedaży *zł+ =$B42+SUMA(B37:$B37)

B43 Próg rentowności minimalny *zł+ =H41-H42/H38

Przy tych obliczeniach zakłada się, że asortymenty są sprzedawane narastająco wg ustalonej na podstawie

stopy marży porycia kolejności. Na początku nic nie jest sprzedawane, dalej sprzedaje się wyroby najlepsze,

potem dodawane są wyroby drugie w kolejności, trzecie … i tak dalej. Próg rentowności minimalny jest to

suma przychodów uzyskanych narastająco z asortymentów generujących stratę i ilorazu tej (minimalnej)

straty (ze zmienionym znakiem) oraz stopy marży pokrycia asortymentu przy sprzedaży, którego otrzymano

minimalny zysk (nastąpiła zmiana znaku wyniku).

Aby dodad serię danych pokazujących próg minimalny do wcześniej stworzonego wykresu należy przejśd do

zakładki Wykres1 i wybrad z menu górnego opcję ,Wykres|Dodaj dane-(Excel 2007 – Narzędzia

wykresów|Projektowanie|Zaznacz dane|Dodaj i zaznaczamy obszar B41:L41 w wartościach x oraz B42:L42

w wartościach y) W oknie Dodaj dane zaznaczamy komórki z zakresu B41:L42 i wciskamy przycisk OK. Gdy

pojawi się okno Wklej specjalnie wystarczy zaznaczyd tylko opcję Kategorie (wartości X) w pierwszym

wierszu i wcisnąd przycisk OK.

Ad. 4. Ustalenie maksymalnego progu rentowności wiąże się posortowaniem asortymentów od najgorszego

(o najmniejszej stopie marży pokrycia) do najlepszego (o największej stopie marży pokrycia). Przed

posortowaniem asortymentów należy skopiowad wartości z komórek B14:K18 do zakresu B49:K53. W tym

celu zaznaczamy zakres kopiowany i wciskamy skrót klawiaturowy CTRL+C, stawiamy kursor na komórce

B49 i wybieramy z menu górnego opcję ,Edycja|Wklej specjalnie- (Narzędzia główne|Schowek|Wklej

specjalnie) i w oknie Wklej specjalnie zaznaczamy opcję Wartości i wciskamy przycisk OK. Przy zaznaczonym

obszarze B49:K53 wybieramy z menu górnego opcję ,Dane|Sortuj-, dalej w oknie Sortowanie wciskamy


8

przycisk Opcje i zaznaczamy opcję Sortuj od lewej do prawej i wciskamy przycisk OK. Następnie w polu

Sortuj według należy wybrad Wiersz 53, zaznaczyd opcję Rosnąco i wcisnąd przycisk OK.

W części oznaczonej, jako dane do wykresu należy wprowadzid następujące formuły:

C56:L56 Przychody ze sprzedaży *zł =SUMA(B50:$B50)

C57:L57 Wynik na sprzedaży *zł+ =$B57+SUMA(B52:$B52)

B58 Próg rentowności minimalny *zł+ =G56-G57/G53

Aby dodad serię danych pokazujących konstrukcję progu maksymalnego do wcześniej stworzonego wykresu

należy przejśd do zakładki Wykres1 i wybrad z menu górnego opcję ,Wykres|Dodaj dane- (Narzędzia

wykresów|Projektowanie|Zaznacz dane|Dodaj i zaznaczamy obszary B56:L56 jako x i B57:L57 jako y). W

oknie Dodaj dane zaznaczamy komórki z zakresu B56:L57 i wciskamy przycisk OK. Gdy pojawi się okno Wklej

specjalnie wystarczy zaznaczyd tylko opcję Kategorie (wartości X) w pierwszym wierszu i wcisnąd przycisk

OK.

3. Analiza progu rentowności produkcji jedno-asortymentowej

Analiza progu rentowności może byd wykorzysta przy podejmowania decyzji krótkookresowych. W

przedsiębiorstwie SAWA w związku z nieakceptowanym poziomem planowanego wyniku na przyszły okres

zaproponowano cztery propozycje zmian różnych parametrów finansowych, np. cen, kosztów zmiennych

czy kosztów stałych. Dla poszczególnych propozycji należy ustalid stopę marginesu bezpieczeostwa oraz

rentownośd, aby decydenci mogli wybrad optymalny wariant działalności. W związku z występowaniem

kilku wariantów decyzji w MS Excelu można usprawnid proces podejmowania decyzji poprzez wykorzystanie

menadżera scenariuszy, który służy do łatwego zarządzania danymi wyjściowymi i wynikowymi wielu

wariantów.

Próg rentowności wyznaczany jest zarówno ilościowo jak i wartościowo. Ilościowy próg rentowności Qo jest

taką ilością sprzedaży (produkcji=sprzedaż), przy której przychód ze sprzedaży jest równy kosztom

całkowitym. Ilościowy próg rentowości wyznaczany jest na podstawie wzoru:

.z

so

kp

KQ

Wartościowy próg rentowności jest to taka wartośd sprzedaży przy której przychody ró4.nają się

całkowitym kosztom. Wartościowy próg rentowności może byd na podstawie wzoru:

ĆWICZENIE 4

ZAGADNIENIE

od 30 do 40 min


9

.

p

k1

KS

z

so

gdzie: KS – koszty stałe, p – cena sprzedaży kz – jednostkowe koszty zmienne S0 – próg rentowności wartościowy Q0 – próg rentowności ilościowy.

Graficzna ilustracja progu rentowności produkcji jednorodnej jest przedstawiona na rysunku.

S,K

So

0 Qo Q

Próg rentowności

Zysk

Strata

S

K

W przedsiębiorstwie SAWA sporządzono i przedstawiono zarządzającym następujące plany finansowe:

Wariant wyjściowy

Ilość [szt.] 3 200 000

Stawka Wartość

Sprzedaż 2,050 6 560 000,00

Koszty wytworzenia produktów 4 920 000,00

materiały bezpośrednie 0,875 2 800 000,00

wynagrodzenia bezpośrednie 0,300 960 000,00

inne koszty zmienne 0,200 640 000,00

koszty stałe wydziałowe 520 000,00

Koszty sprzedaży 1 156 000,00

koszty zmienne 0,125 400 000,00

inne koszty zmienne sprzedaży 0,205 656 000,00

koszty stałe 100 000,00

Koszty administracji 300 000,00

zmienne 0,050 160 000,00

stałe 140 000,00

Koszty zmienne razem 1,755 5 616 000,00

Koszty stałe razem 760 000,00

Koszty razem 6 376 000,00

Przewidywany wynik [zł] 184 000,00

Próg rentowności [szt.] 2 576 271

POLECENIE


10

Stopa marży bezpieczeństwa [szt.] 19,49%

Aktywa trwałe [zł] 1 200 000

Aktywa obrotowe [zł] 300 000

Aktywa razem [zł] 1 500 000

Rentowość [%] 12,27%

Przedstawiony w planach poziom stopy marży bezpieczeostwa (19,49%), jak również rentownośd (12,27%)

zostały zanegowane przez zarząd. W związku poszukiwaniem lepszych wyników przedstawiono cztery

warianty działalności:

a) Specjalista d/s zarządzania uważa, że należy podnieśd cenę o 0,10 zł na jednostkę, co będzie związane z

pewną sumą wydatków na reklamę i na zmianę jakości produktu. Zmiana jakościowa produktu

pociągnie za sobą koszty 500 000 zł (modernizacja zdolności produkcyjnych). Koszty dodatkowej

reklamy szacowane są na 100 000 zł. Przewiduje się, że wielkośd sprzedaży będzie spadad powoli i

możliwe będzie utrzymanie sprzedaży na poziomie 2 900 000 jednostek.

b) Specjalista d/s sprzedaży zaproponował wzrost wielkości sprzedaży i jest przekonany, że firma może

sprzedad 4 850 000 jednostek, jeśli cena zostanie obniżona 0,10 zł na jednostkę. Ponieważ zakład

produkujący ten wyrób pracuje obecnie przy maksymalnym wykorzystaniu zdolności produkcji,

specjalista d/s sprzedaży obliczył, że konieczne będzie poniesienie dodatkowych kosztów bieżącego

okresu w wysokości 40 000 zł.

c) Specjalista d/s marketingu jest zdecydowanie przeciwny drugiej propozycji i uważa, że dodatkowe

wydatki poniesione na reklamę są w stanie zwiększyd sprzedaż do 3 500 000 jednostek bez zmiany ceny

produktu. Proponuje on, aby rocznie wydatki na reklamę wynosiły 250 000 zł oraz 300 000 zł na

zwiększenie zdolności produkcyjnych.

d) Specjalista d/s finansowych uważa, że należy działad w zakresie zmian w kosztach zmiennych i

proponuje, aby poprzez wprowadzenie ulepszeo organizacyjnych w procesie produkcyjnym obniżyd

inne koszty zmienne ponoszone na wydziale produkcyjnym o 0,03 zł na jednostkę. Pociągnie to za sobą

wzrost kosztów stałych zakładu w bieżącym okresie o 40 000 zł.

Dodatkowe informacje:

1. Planowany zaangażowany kapitał na dany rok wynosi 1,5 mln zł, z czego 1,2 mln zł przypada na

finansowanie aktywów trwałych, a 0,3 mln zł na pokrycie aktywów obrotowych.

2. Wartośd zaangażowanych aktywów obrotowych będzie wzrastad lub maled wraz ze zmianami ilości

sprzedawanych jednostek.

3. Koszty zmienne pozostaną niezmienne na jednostkę produktu z wyjątkiem specjalnych kosztów

sprzedaży, które zawsze stanowią 10% jednostkowej ceny sprzedaży.


11

4. Nakłady inwestycyjne rozliczane są w czasie i odnoszone w koszty proporcjonalnie przez okres 10 lat

(1/10 nakładów przypada na koszty okresu bieżącego a 9/10 zwiększa wartośd aktywów trwałych).

W pliku Zadanie3.xls znajdują się sformatowane i przygotowane dane do budowy scenariuszy. Odpowiednie

komórki zawierają formuły odwołujące się do zmienianych danych, takich jak: cena, ilośd, inny koszt

zmienny wydziałowy, koszty stałe wydziałowe (m.in. amortyzacja maszyn i urządzeo), koszty stałe sprzedaży

(m.in. reklama) w taki sposób, aby komórki wynikowe (stopa marży pokrycia, rentownośd) pokazywały

właściwe wartości.

Zanim zostaną utworzone scenariusze należy nazwad komórki zmieniane i wynikowe. Pozwala to na o wiele

łatwiejszą interpretację wyników działania menadżera scenariuszy niż w przypadku, w gdy w

podsumowaniu scenariuszy pojawiają się tylko nic niemówiące adresy komórek. W celu nadania nazw

poszczególnym komórkom należy wybrad z menu górnego opcję ,Wstaw|Nazwa|Definiuj- (Excel 2007-

Formuły|Nazwy zdefiniowane|Definiuj nazwę) lub (ustawid się w definiowanej komórce, a następnie w

białym polu nad kolumną A nadad nazwę jak w tabeli i zatwierdzid enter) i w polu Nazwy w skoroszycie

wpisad odpowiednią (nazwy nie mogą zawierad spacji i znaków działao matematycznych, nie mogą zaczynad

się od cyfr i byd identyczne jak adresy komórek w arkuszu) nazwę dla komórki a w polu Odwołuje się do

wprowadzid odpowiadający danej nazwie adres i wcisnąd przycisk Dodaj. Procedurę definicji nazw komórek

należy powtórzyd dla następujących danych.

Lp. Nazwa Adres komórki Typ komórki

1. Cena B4 zmieniana 2. Ilośd C2 zmieniana 3. Koszty_stałe_wydziałowe C9 zmieniana 4. Koszty_stałe_sprzedaży C12 zmieniana 5. Inne_jednostkowe_koszty_zmienne_produkcji B8 zmieniana 6. Aktywa_trwałe C24 zmieniana

Lp. Nazwa Adres komórki Typ komórki

1. Stopa_marży_bezpieczeostwa C23 wynikowa 2. Rentownośd C27 wynikowa

Po zdefiniowaniu nazw wybieramy z menu górnego opcję ,Narzędzia|Scenariusze- (Excel 2007 – Dane|

Narzędzia danych|Analiza symulacji|Mendżer scenariuszy) i wciskamy przycisk Dodaj. W oknie Dodaj

scenariusz w polu Nazwa scenariusza wpisujemy Wyjściowy, w polu Komórki zmieniane wpisujemy

wszystkie komórki oznaczone w arkuszu kolorem pomaraoczowym oddzielając je średnikiem (można

wprowadzid te adresy klikad lewym przyciskiem myszy na odpowiednich komórkach z wciśniętym

klawiszem CTRL). Po wciśnięciu przycisku OK pokaże się okno Wartości scenariusza, które należy tylko

zatwierdzid przyciskiem OK. Procedurę dodawania scenariuszy należy powtórzyd 4 razy wprowadzając w ich

ROZWIĄZANIE


12

wartościach odpowiednie dane wynikające z treści propozycji zgłaszanych przez Dyrektorów. W tabeli

znajdują się wartości dla poszczególnych scenariuszy.

Komórki zmieniane Warianty

Wyjściowy Specjalista d/s marketingu

Specjalista d/s zarządzania

Specjalista d/s sprzedaży

Specjalista d/s finansowych

Koszty_stałe_wydziałowe 520 000 550 000 570 000 560 000 560 000

Inne_jednostkowe_koszty_zmienne 0,200 0,200 0,200 0,200 0,170

Cena 2,050 2,050 2,150 1,950 2,050

Ilośd 3 200 000 3 500 000 2 900 000 4 850 000 3 200 000

Koszty_stałe_sprzedaży 100 000 250 000 200 000 100 000 100 000

Aktywa_trwałe 1 200 000 1 470 000 1 650 000 1 200 000 1 200 000

Po zakooczeniu dodawania scenariuszy może je przeglądad korzystając z przycisku Pokaż lub wyświetlid

raport wciskając przycisk Podsumowanie. Po wybraniu przycisku podsumowanie wyświetli się okno

Podsumowanie scenariuszy, w którym w polu Komórki wynikowe należy wpisad komórki C23 i C27.

Podsumowanie może byd bardzo przydatne w podejmowaniu decyzji przez zarząd, jaki wariant działalności

wybrad.

Wskaż według Ciebie wariant optymalny.

4. Wybór asortymentu produkcji przy ograniczonych zdolnościach produkcyjnych

W decyzjach krótkookresowych często pojawia się zagadnienie wyboru optymalnego planu produkcji. W

tego typu decyzjach rozpatruje się dwie sytuacje: przy niepełnym i pełnym wykorzystaniu zdolności

produkcyjnych. W pierwszej sytuacji należy podjąd decyzję czy produkowad, czy nie produkowad dany

asortyment. Natomiast w drugiej sytuacji dodatkowo należy jeszcze ustalid ilośd wytwarzanych jednostek.

W obu sytuacjach uwzględnia się jednostkową marżę pokrycia (cena minus koszt zmienny jednostkowy).

Jeżeli marża dla asortymentu jest większa od zera należy ten asortyment wytwarzad. Jednak w przypadku

ograniczonych możliwości produkcyjnych należy uwzględnid stosunek marży pokrycia do czynnika

ograniczającego (najczęściej jest to norma roboczogodzin lub maszynogodzin na wytworzenie jednostki

produktu). W warunkach korzystania z MS Excela zadanie optymalizacji planu produkcji można wykonad

przy wykorzystaniu dodatku solver.

W przedsiębiorstwie produkcyjnym wytwarza się 5 asortymentów. Popyt na te asortymenty przekracza

możliwości produkcyjne, w związku z czym należy produkowad tylko te asortymenty, których marża

pokrycia przypadająca na czynnik ograniczający jest największa. W tabeli podano potrzebne dane do

ustalenia planu produkcji.

ĆWICZENIE 4

ZAGADNIENIE

od 20 do 30 min

POLECENIE

POLECENIE EXTRA


13

Asortyment Cena

sprzedaży [zł/szt.]

Jednostkowy koszty zmienny

[zł/szt.]

Norma pracochłonności

[h/szt.]

Możliwości sprzedaży

[szt.]

Wyrób A 15,50 10,20 0,40 5000

Wyrób B 12,60 9,30 0,25 6000

Wyrób C 10,20 5,60 0,60 4500

Wyrób D 22,00 15,30 0,80 3000

Wyrób E 5,90 5,10 0,15 10000

Koszty stałe w miesiącu szacuje się na poziomie 40 000 zł a ilośd dostępnych godzin w miesiącu wynosi

15 000 h. Ustal plan produkcji pozwalający na osiągnięcia maksymalnego wyniku przy podanych

założeniach.

W pliku Zadanie4.xls znajdują się dane źródłowe wykorzystywane do uruchomienia solvera. Zanim solver

zostanie uruchomiony należy wprowadzid do arkusza formuły obliczające wynik na sprzedaży. Należy

obliczyd Wykorzystane godziny, Jednostkową marżę pokrycia i Globalną marże pokrycia. Na rysunku

podano formuły.

Następnie z menu górnego ,Narzędzia|Solver- (Excel 2007 - Dane|Analiza|Solver) uruchamiamy Solvera. W

sytuacji, gdy Solvera nie ma w menu górnym wybieramy opcję ,Narzędzia|Dodatki- (Excel 2007 – naciskamy

na ikonkę MS Office|Opcje programu excel|Dodatki|Zarządzaj dodatkami|Przejdź), zaznaczamy opcję

Dodatek Solver i wciskamy przycisk OK (patrz rysunek).

ROZWIĄZANIE


14

Po uruchomieniu Solvera należy wpisad w polu Komórka celu – I9 i w polu Komórki zmienianie wpisad zakres

$F$2:$F$6. Następnie dodajemy warunki ograniczające wciskając przycisk Dodaj i wypełniając pola Adres

komórki, operator matematyczny i Warunek ograniczający. W warunkach ograniczających (patrz tabela)

należy wprowadzid wszystkie komórki dotyczące ilości sprzedaży, która nie może byd większa od popytu

oraz mniejsze od 0. Ponadto należy wprowadzid warunek ograniczający łączną ilośd godzin, aby nie

przekraczały 15000.

Komórka Nazwa Formuła

$G$7 Razem Wykorzystane godziny [h] $G$7<=$G$8

$F$2 Wyrób A Sprzedaż [szt.] $F$2<=$E$2

$F$2 Wyrób A Sprzedaż [szt.] $F$2>=0

$F$3 Wyrób B Sprzedaż [szt.] $F$3<=$E$3

$F$3 Wyrób B Sprzedaż [szt.] $F$3>=0

$F$4 Wyrób C Sprzedaż [szt.] $F$4<=$E$4

$F$4 Wyrób C Sprzedaż [szt.] $F$4>=0

$F$5 Wyrób D Sprzedaż [szt.] $F$5<=$E$5

$F$5 Wyrób D Sprzedaż [szt.] $F$5>=0

$F$6 Wyrób E Sprzedaż [szt.] $F$6<=$E$6

$F$6 Wyrób E Sprzedaż [szt.] $F$6>=0

Po wprowadzeniu warunków ograniczających wciskamy przycisk Rozwiąż i w oknie Solver-Wyniki wciskamy

przycisk OK.

Rachunkowość zarządcza - martynowicz.netmartynowicz.net/studenci/Materialy - II Zajecia.pdf ·...

Documents

Transcript of Rachunkowość zarządcza - martynowicz.netmartynowicz.net/studenci/Materialy - II Zajecia.pdf ·...