Затрати ресурсів на реалізацію товарів

Таблиця 27

Ресурси	Нормативи затрат ресурсів із продажу товарів	Обмежені обсяги ре­сурсів, bi
А	В	с
Робочий час, люд.-год.	a11 =0,1	a12= 0,2	a13 =0,4	b1= 1100
Площа торгових залів, м2	a21 = 0,05	a22 = 0,02	a23=0,02	b2 =120
Витрати обігу, грн.	a31 = 3	a32 = 1	a33 =2	b3 =3000
Прибуток в розрахунку на одиницю товару, грн.	C1 = 3	C2 = 5	C3= 4
План продажу товарів, од.	X1= ?	X2= ?	X3 = ?

Виконання:

1. Визначаю штатний розпис, тобто визначити, скільки пра­цівників, на яких посадах і з яким окладом потрібно прийняти на роботу.

Розраховую зар.плату: =А2*$Н$2+В2.

Розраховую сумарну зар.плату: =D2*E2.

Для розрахунку сумарного місячного фонду зарплати використовую автосумування. Фонд склав більше 30000 грн. Для того щоб вкластися в місячний фонд зарплати звільняю одного менеджера по роботі з товарами. Але місячний фонд зарплати все ще більший за норму на 75 грн. Для повної нормалізації виконую наступні дії: «Сервис» - «Подбор параметров»:

- «Установить в ячейке» = $F$11;

- «Значение» = 30000;

- «Изменяя ячейку» = $Н$2.

Отриманий результат:

2. Складаю оптимальний план завантаження облад­нання, яке переробляє зерно в муку, за критерієм максимиму прибутку при наявності обмежень на ресурси.

Згідно завдання заповнюю таблицю за допомогою наступних формул:

G6: =F6*B6*Дані!$B$4

H6: =F6*C6*Дані!$B$5

I6: =F6*D6*Дані!$B$6

J6: =F6*E6*Дані!$B$7

K6: =СУММ(G6:J6)

L6: =F6*Дані!$B$9

M: =F6*Дані!$B$15

N: =F6*Дані!$B$17

O: =СУММ(L6:N6)

Q: =P6/F6

R: =K6-O6

S: =R6/F6

T: =P6*S6

Ці формули поширюються на діапазон рядків: 6 – 8.

Клітинки P6 – P8 заповнюю наступним чином: відкриваю «Сервис» - «Поиск решения». Для першого варіанта у полі «Установить целевую ячейку» вказую клітинку $Т$6. У полі «Изменяя ячейки» вказую діапазон клі­тинок, значення яких програма змінює для побудови оптималь­ного значення (клітинки $Р$6). Обмеження задачі задаю в полі «Огра­ничения» діалогового вікна. В це поле вводжу наступні обмеження:

$Р$6<=Дані!$В$11,

$Q$6<=Дані!$В$13,

$P$6=int,

$Р$6>=0.

Після цього натискаю кнопку «Выполнить».

Для другого варіанта у полі «Установить целевую ячейку» вказую клітинку $Т$7. У полі «Изменяя ячейки» вказую діапазон клі­тинок, значення яких програма змінює для побудови оптималь­ного значення (клітинки $Р$7). Обмеження задачі задаю в полі «Огра­ничения» діалогового вікна. В це поле вводжу наступні обмеження:

$Р$7<=Дані!$В$11,

$Q$7<=Дані!SB$13,

$P$7=int,

$Р$7>=0.

Після цього натискаю кнопку «Выполнить».

Для третього варіанта у полі «Установить целевую ячейку» вказую клітинку $Т$8. У полі «Изменяя ячейки» вказую діапазон клі­тинок, значення яких програма змінює для побудови оптималь­ного значення (клітинки $Р$8). Обмеження задачі задаю в полі «Огра­ничения» діалогового вікна. В це поле вводжу наступні обмеження:

$Р$8<=Дані!$В$11,

$Q$8<=Дані!$В$13,

$P$8=int,

$Р$8>=0.

Після цього натискаю кнопку «Выполнить».

В клітинку T9 =МАКС(T6:T8)

Планування завантаження обладнання
Варіант завантаження	Вихід муки	Кількість, тон за годину
	Вищий гатунок	1 гатунок	2 гатунок	Висівки
	25%	20%	45%	10%	1,0
	50%	27%	13%	10%	0,6
	63%	18%	9%	10%	0,3
Разом

Продовження

Вартість продукції, грн.
Вищий гатунок	1 гатунок	2 гатунок	Висівки	Всього
80,00	52,00	99,00	6,00	237,00
96,00	42,12	17,16	3,60	158,88
60,48	14,04	5,94	1,80	82,26

Продовження

Змінні затрати, на 1т зерна	Обсяг, тон	Завантаження, годин	Прибуток, грн.
Зерно	Тара і склад	Інші	Всього			за год.	за тону	всоьго
150,00	25,00	13,00	188,00			49,00	49,00	156 800,00
90,00	15,00	7,80	112,80			46,08	76,80	193 536,00
45,00	7,50	3,90	56,40			25,86	86,20	108 612,00
								193 536,00

1. За допомогою програмних засобів MS Excel складаю оптимальний план товарообігу універма­гу за критерієм максимуму прибутку.

Клітинка Е6 містить максимальний прибуток. Для розрахунку використовую формулу:

=МАКС(B6*B7;C6*C7;D6*D7).

Для розрахунку продажу товару використовую «Поиск решения»:

«Установить целевую ячейку» - Е6; «Изменения ячейки» - $B$7:$D$7;

Задаю наступны обмеження:

Для товару А:

$B$7 <= ($E$3-$C$3*$C$7-$D$3*$D$7)/$B$3

$B$7 <= ($E$4-$C$4*$C$7-$D$4*$D$7)/$B$4

$B$7 <= ($E$5-$C$5*$C$7-$D$5*$D$7)/$B$5

Для товару В:

$C$7 <= ($E$3-$B$3*$B$7-$D$3*$D$7)/$C$3

$C$7 <= ($E$4-$B$4*$B$7-$D$4*$D$7)/$C$4

$C$7 <= ($E$5-$B$5*$B$7-$D$5*$D$7)/$C$5

Для товару С:

$D$7 <= ($E$3-$B$3*$B$7-$C$3*$C$7)/$D$3

$D$7 <= ($E$4-$B$4*$B$7-$C$4*$C$7)/$D$4

$D$7 <= ($E$5-$B$5*$B$7-$C$5*$C$7)/$D$5

План продаєу товару не може бути від’ємним:

$B$7:$D$7 >= 0

Таблиця розрахунків:

Згідно заданих вимог, оптимальним варіантом реалізації товарів являється товар групи В. При цьому досягається максимальний прибуток – 15000.

Висновок: використовуючи персональний комп’ютер навчився розв'язувати задачі планування, розглянув основні питан­ня організації автоматизованої обробки даних на АРМ планува­льника в умовах функціонування інтегрованої системи обробки економічної інформації.