Лабораторная работа №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 – каждая книга должна приносить доход, и не быть убыточной.

 

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

 

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