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

Разделы: Автомобили Астрономия Биология География Дом и сад Другие языки Другое Информатика История Культура Литература Логика Математика Медицина Металлургия Механика Образование Охрана труда Педагогика Политика Право Психология Религия Риторика Социология Спорт Строительство Технология Туризм Физика Философия Финансы Химия Черчение Экология Экономика Электроника

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

 

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

 

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

 







Дата добавления: 2015-09-18; просмотров: 677. Нарушение авторских прав; Мы поможем в написании вашей работы!



Расчетные и графические задания Равновесный объем - это объем, определяемый равенством спроса и предложения...

Кардиналистский и ординалистский подходы Кардиналистский (количественный подход) к анализу полезности основан на представлении о возможности измерения различных благ в условных единицах полезности...

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

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

Шрифт зодчего Шрифт зодчего состоит из прописных (заглавных), строчных букв и цифр...

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

РЕВМАТИЧЕСКИЕ БОЛЕЗНИ Ревматические болезни(или диффузные болезни соединительно ткани(ДБСТ))— это группа заболеваний, характеризующихся первичным системным поражением соединительной ткани в связи с нарушением иммунного гомеостаза...

Предпосылки, условия и движущие силы психического развития Предпосылки –это факторы. Факторы психического развития –это ведущие детерминанты развития чел. К ним относят: среду...

Анализ микросреды предприятия Анализ микросреды направлен на анализ состояния тех со­ставляющих внешней среды, с которыми предприятие нахо­дится в непосредственном взаимодействии...

Типы конфликтных личностей (Дж. Скотт) Дж. Г. Скотт опирается на типологию Р. М. Брансом, но дополняет её. Они убеждены в своей абсолютной правоте и хотят, чтобы...

Studopedia.info - Студопедия - 2014-2024 год . (0.012 сек.) русская версия | украинская версия