Лабораторная работа №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 – каждая книга должна приносить доход, и не быть убыточной.
Далее нажмите кнопку Выполнить, после чего начнется процесс поиска решения. Если в следующем диалоге будет сказано, что решение найдено, нажмите кнопку ОК, и все поля в таблице будут заполнены нужными значениями.
Описанные приемы можно использовать не только для определения оптимальных тиражей книг, но и для оптимизации количества и сроков выпуска или закупок любых товаров. Конечно, это не совсем задача бухгалтера, но на данном примере хорошо видны особенности поиска решений.