Упражнение №4

Экономическое содержание задачи: автоматизация расчета зарплаты.

Расчет зарплаты в соответствии с действующим законодательством достаточно сложный процесс, учитывающий множество нюансов. Для расчетов зарплаты предлагается использовать рабочую книгу Excel с множеством листов. На отдельном листе расположены неизменные исходные данные – список сотрудников и некоторые дополнительные данные. Расчеты в каждом месяце ведутся на отдельных листах. Стандартные бланки – расчетная и платежная ведомости также расположены на отдельных листах и заполняются автоматически.

Вначале нужно ввести исходные данные, не меняющиеся от месяца к месяцу, то есть список сотрудников предприятия (рис. 1.27).

Рисунок 1.27 – Список сотрудников и праздники

В правой части листа мы расположили список праздников текущего года. Он нужен для автоматического подсчета рабочих дней в любом месяце. Для ввода даты используйте формат ячейки Дата.

Чтобы работало автоматическое определение количества рабочих дней, должен быть установлен пакет анализа.

После того, как введены исходные данные, нужно создать расчетный лист и ввести в него формулы (рис. 1.28).

Ячейке J1 необходимо присвоить формат «Дата» – «март 2001».

Рисунок 1.28 – Образец расчетного листа

Далее вводим формулы (таблица 1.13).

Таблица 1.13 – Формулы для реализации задачи

Ячейки	Формулы	Область копирования
D2	=ЧИСТРАБДНИ(ДАТА(ГОД(J1);МЕСЯЦ(J1) ;1);КОНМЕСЯЦА(J1;0);Сведения!G2:G12)	-
A7	=ЕСЛИ(B7<>"";1;"")	A8-A10
C7	=ЕСЛИ(B7<>"";ВПР(B7;Сведения!$A$3: $E$10;2;ЛОЖЬ);"")	C8-C10
D7	=ЕСЛИ(B7<>"";ВПР(B7;Сведения!$A$3: $E$10;3;ЛОЖЬ);"")	D8-D10
G7	=ЕСЛИ(B7<>"";D7/$D$2*E7;"")	G8-G10
K7	=СУММ(G7:J7)	K8-K10
L7	=K7*14%	L8-L10
M7	=K7*11%	M8-M10
N7	=K7*3%	N8-N10
O7	=K7*4%	O8-O10
P7	=K7*3,4%	P8-P10
Q7	=K7*0,2%	Q8-Q10
R7	=K7*0,2%	R8-R10
T7	=ЕСЛИ(И(B7<>"";W7=0);400;0)	T8-T10
U7	=ЕСЛИ(B7<>"";ВПР(B7;Сведения! $A$3:$E$10;5;ЛОЖЬ);"")	U8-U10
V7	=U7*300	V8-V10
X7	=ЕСЛИ(B7<>"";ЕСЛИ(S7<20000;(K7-T7-V7-W7)*13%;(K7-V7-W7)*13%);"")	X8-X10
Y7	=ЕСЛИ(B7<>"";K7-F7-X7;"")	Y8-Y10
Z7	=СУММ(K7:K10)	-
AA7	=СУММ(Y7:Y10)	-

Лабораторная работа №6. Проведение анализа и поиск приемлемых решений. Оптимизация сроков и объемов производства или закупок.

В Excel встроено мощное средство поиска решений. Оно реализовано как надстройка. Чтобы ее включить, необходимо нажать кнопку Office, после чего нажать кнопку Параметры Excel. Выбрать пункт Надстройки и нажать кнопку Перейти. После этого необходимо поставить галочку напротив пункта Поиск решения и нажать OK.

Экономическое содержание задачи: казалось бы, выгодно печатать большой тираж, чтобы получить небольшую себестоимость, и постепенно распродавать его. Однако длительное замораживание средств и складские расходы сводят на нет все преимущества большого тиража. Нужно подобрать такой тираж, при котором соотношение стоимости тиража и расходов на его хранение получится оптимальным. Составив расчетную таблицу, можно попытаться подобрать нужные значения. Но вручную перебирать разные варианты достаточно утомительно, значительно удобнее задать условия и почти сразу получить наиболее подходящий вариант. Для этого в Excel и включено средство принятия решений.

Для простоты считаем, что нужно в течение года продавать четыре разные книги, и получить при этом максимальный доход. Исходные данные для расчетов вносятся в верхнюю часть таблицы (рис.1.29).

В первых четырех строках вводятся заголовки, а с пятой по восьмую строки вводятся информация о конкретных книгах.

Рисунок 1.29 – Исходные данные для расчета

В первом столбце вводятся название книги, а во втором – ее отпускная цена. В нашей простой модели не учитываются оптовые скидки. В столбцах с C по F вводится себестоимость печати одной книги, для разных объемов тиража. Отметим, что при тираже менее 1000 себестоимость может быть выше отпускной цены. В столбце G вводится количество продаваемых в месяц книг и начале продаж. Так как часто количество продаж уменьшается со временем, в следующем столбце вводится коэффициент такого уменьшения. Если ввести 100%, продажи будут постоянными. При вводе значения 90% во втором месяце продажи уменьшатся на 10% от первого, в третьем уменьшатся на 10% от продаж во втором месяце, и так далее.

В столбце I вводятся расходы на хранение одной книги в течение месяца. В эти расходы входят как оплата склада, так и разнообразные накладные расходы, а также в этом числе учитывается замораживание средств в товаре. То есть, в этот столбец вводится обобщенный показатель расходов при хранении в течение месяца одного экземпляра книги. Для реальной модели вместо числа нужно вводить расчетную формулу, но мы реализуем более простой вариант.

В строках, расположенных ниже, располагается таблица издания книг по месяцам и наличие книг на конец каждого месяца (рис.1.30).

Рисунок 1.30 – Таблица тиражей и остатков книг

В верхней таблице вводятся тиражи книг, изданные в конкретные месяцы. Вы можете ввести вручную нужные цифры, но лучше предоставить Excel возможность найти нужное решение, то есть заполнить эту таблицу. В нашем примере мы зададим печать больших тиражей в начале срока, чтобы распродавать книги в течение года без допечатки. В дальнейшем вы увидите, что этот вариант далеко не оптимален.

В таблице 1.14 приведены формулы для реализации поставленной задачи.

Таблица 1.14 – Формулы для реализации задачи

Ячейки	Формулы	Область копирования
B18	=B12-$G5	B19-B21
C18	=C12+B18-$G5*$H5^(СТОЛБЕЦ()-2)	C18:M21

Еще ниже необходимо расположить таблицу с доходами в каждом месяце от каждой книги (рис.1.31). При этом расходы будут отображаться отрицательными числами, а доходы – положительными.

Рисунок 1.31 – Доходы издателя

Как и для прошлой таблицы, нужно ввести всего две формулы, после чего с помощью автозаполнения размножить их по всем ячейкам.

Таблица 1.15 – Формулы для реализации задачи

Ячейки	Формулы	Область копирования
B25	=G5*B5-ЕСЛИ(B12<1000;C5;ЕСЛИ (B12< 5000;D5;ЕСЛИ(B12<10000;E5;F5)))*B12-B18*I5	B26-B28
C25	=(B18+C12-C18)*$B5-ЕСЛИ (C12<1000;$C5;ЕСЛИ(C12<5000;$D5; ЕСЛИ(C12<10000;$E5;$F5)))*C12-C18*$I5	C25:M25
N25	=СУММ(B25:M25)	N26-N28
D30	=СУММ(B25:M28)	-

Подробнее разберем каждую из формул.

В ячейке В25 приведена следующая формула:

=G5*B5-ЕСЛИ(B12<1000;C5;ЕСЛИ(B12<5000;D5;ЕСЛИ (B12<10000; E5;F5)))*B12-B18*I5

Выражение G5*B5 вычисляет доход от продажи книг в первом месяце. Он определяется как произведение отпускной цены на наличные продажи. Из полученной суммы вычитаются расходы на издание книги в текущем месяце, вычисляемых следующим образом:

ЕСЛИ(B12<1000;C5;ЕСЛИ(B12<5000;D5;ЕСЛИ(B12<10000;E5;F5)))

Три условных оператора позволяют определить стоимость издания книги в зависимости от тиража. Если тираж менее 1000, то стоимость берется из столбца С. В противном случае, если тираж менее 5000, стоимость берется из столбца D и так далее.

Также из дохода вычитается произведение B18*I5, то есть расходы на хранение тиража книг в этом месяце.

Единственное отличие формулы, приведенной в ячейке С25 – проданные в текущем месяце книги определяются по формуле:

B18+C12-C18. К количеству книг в текущем месяце прибавляется тираж вновь изданных книг и из результата вычитается количество книг в предыдущем месяце.

После того, как все формулы введены, можно приступать к подбору решений.

Перейдите на вкладку Данные и на панели Анализ, нажмите кнопку Поиск решения, чтобы открыть диалог настройки параметров поиска. В диалоге вы должны задать цель поиска, установить ограничения и указать, какие ячейки нужно ввести в поля ввода (рис.1.32).

Рисунок 1.32 – Диалог «Поиск решений»

В поле целевой ячейки введите $D$30 и с помощью переключателя выберете максимальное значение, то есть в качестве цели нужно максимизировать суммарный доход за год. В поле Изменяя ячейки введите $B$12:$M$15. При поиске решений нужно менять тиражи изданий разных книг в разные месяцы.

Далее нужно ввести ограничения. Нажмите кнопку Добавить, и в появившемся диалоге введите ограничения:

1) $B$12:$M$15>=0 – тиражи книг не могут быть отрицательными;

2) $B$18:$M$21>=0 – в любом месяце должны быть в наличии все книги;

3) $N$25:$N$28>=0 – каждая книга должна приносить доход, и не быть убыточной.

Далее нажмите кнопку Выполнить, после чего начнется процесс поиска решения. Если в следующем диалоге будет сказано, что решение найдено, нажмите кнопку ОК, и все поля в таблице будут заполнены нужными значениями.

Описанные приемы можно использовать не только для определения оптимальных тиражей книг, но и для оптимизации количества и сроков выпуска или закупок любых товаров. Конечно, это не совсем задача бухгалтера, но на данном примере хорошо видны особенности поиска решений.