Применение информационных технологий обработки экономических данных при анализе рынка товаров и услуг (на примере «ОАО Пищекомбинат Беж

 

 

7. Решение оптимизационной задачи

 

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

• прогнозируемая цена;
• закупочная цена (себестоимость);
• система скидок для каждого товара в зависимости от размера партии;
• сумма оборотного капитала фирмы.

Пусть – виды товаров (минимум 4);

 – закупочная цена i-го  вида товара;

 – прогнозируемая цена  продажи i-го вида товара (ее получают  с помощью линии тренда); 

 – размер партии для i-го  вида товара;

 – затраты на приобретение  партии товаров; 

 – сумма оборотного капитала  фирмы;

 – прибыль.

Тогда зависимость закупочной цены от размера партии для i-го товара описывается функцией

(7)

Сумма затрат на приобретение товаров рассчитывается:

(8)

Общая прибыль вычисляется:

(9)

Нужно определить значения размеров партий ( ), при котором прибыль ( ) будет максимальной. При этом необходимо учитывать ограничения:

(10)

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

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

Для того, чтобы решить данную задачу необходимо  ввести розничную цену продажи оборудования для 13-ого периода, которая установлена с помощью прогноза цены (линия тренда), закупочную цену (процент от розничной цены – 0,35 ), установленный капитал прибыли.

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

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

Целевой ячейкой в данном случае является итоговая прибыль, которая в результате выполнения задачи должна быть максимальной за счет изменения количества реализуемого товара. При этом необходимо ввести следующие ограничения: во-первых, количество товара должно быть целым значением, поскольку продажа оборудования по частям невозможна, также количество товара должно быть ≥ 0, поскольку оно не может принимать отрицательных значений, а также затраты не должны превышать сумму оборотного капитала. Таким образом, с помощью инструмента «Поиск решения» введем эти условия (Рис. 3):

Рис.3 «Поиск решения»

 

Полученное решение сохраним с помощью «Диспетчера сценариев» (рис.5).

Рис.4 «Результаты поиска решения»

 

Таким образом, мы получаем самое оптимальное решение поставленной задачи. Итоги решения оптимизационной задачи приведены в табл. 3

 

Таблица 3 «Оптимизационная задача»

8. Создание листа интерфейса для управления задачей

 

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

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

В курсовой работе первый лист – это лист интерфейса, на нем расположена кнопка «Главная форма» (Рис.5):

Рис.5  «Лист интерфейса»

При её нажатии появляется «Главная форма» (Рис.6) - форма, с помощью которой возможно выполнение ввода данных, расчет динамики и прогноза цен, создание сводной таблицы, решение оптимизационной задачи (приложение 1).

Рис.6 «Главная форма»

При нажатии на кнопку «Ввод данных» открывается форма ввода данных  (Рис.7). Наименование производителя, товара и период выбирается из открывающегося списка, затем вводится цена товара, после чего нажимаем «Добавить» для осуществления добавления товара в таблицу. Кнопка «Назад» реализует возврат к главной форме (приложение 2).

Рис.7 «Форма ввода данных»

 

При нажатии на главной форме кнопок «Динамика цен» или «Прогноз цены» на отдельных листах строятся диаграммы.

При нажатии кнопки «Оптимизационная задача» появляется форма для ввода суммы оборотного капитала (Рис.8). После того нажатия кнопки ОК происходит поиск решения (приложение 3).

Рис.8 «Оптимизационная задача»

 

 

 

 

 

 

 

 

 

 

 

 

 

Заключение

В данной курсовой работе был рассмотрен такой сегмент рынка как «Пряники», в котором выделено 4 товара: Мука, Сахар, Яйцо, Масло. Продавцами, конкурирующими в этом сегменте, являются: поставщики данных товаров: ООО «Мелькрук», ООО «Агроторг», ООО "Лакомка", ООО «Квадро».

Исходя из проведенного анализа по всем 4 товарам и 4 конкурирующим продавцам, можно сделать следующие выводы:

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

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

 

 

 

 

 

 

 

Список литературы

 

1. Титоренко, Г.А. Информационные технологии управления: учеб. пособие для вузов / под ред. Г.А. Титоренко. - 2-е изд., доп. - М.: ЮНИТИ, 2005. - 438 с.
2. Уокенбах Джон. Microsoft Office Excel 2007. Библия пользователя. /Пер.с англ. М.:ООО «И.Д. Вильямс», 2008. –816 с.
3. Гарбер Г.З. Основы программирования на Visual Basic и VBA в Excel 2007. –М.: СОЛОН-ПРЕСС, 2008. – 192с.
4. Гарнаев А.Ю. Использование MS Excel и VBA в экономике и финансах. – СПб.: - Санкт-Петербург, 2000.-336с.
5. Биллинг В.А. VBA в Office 2000. Офисное программирование. – М.: Издательско-торговый дом «Русская редакция», 1999. – 480с.
6. Брага, В.В., Бубнова, Н.Г., Вдовенко, Л.А., Гусев, В.И. Автоматизированные информационные технологии в экономике: учеб. для вузов / под ред. Г.А. Титоренко. - М.: ЮНИТИ, 2003. - 399 с.
7. Демиденко А.И. Информационные системы и технологии: методические указания к выполнению курсовой работы для студентов всех форм обучения направления 080100 – «Экономика». – Изд. 4-е, перераб. и доп. Брянск: БГТУ, 2013. – 14с.
8. Информационные технологии управления: учеб. пособие для вузов / под ред. Г.А. Титоренко. - 2-е изд., доп. - М.: ЮНИТИ, 2009.  - 438 с.
9. Лавренов С.М. Excel: сборник примеров и задач / С.М. Лавренов. - М.: Финансы и статистика, 2009. - 333 с.
10. Малышев  С.А.  Самоучитель VBA. Как  это  делается  в Word,  Excel, Access / С.А. Малышев; под ред. С.Л. Корякина-Черняка. -  СПб.: Наука и техника, 2009. - 490 с.

 
 
 

Приложения

 

 

Приложение 1

Главная форма

 

Код кнопки «Ввод данных»

Private Sub VD_Click()

Glavn.Hide

Vvod.Show

End Sub

 

Код кнопки «Сводная таблица»

Private Sub Svodn_Click()

Sheets("Сводная таблица").Select

ActiveSheet.PivotTables("СводнаяТаблица1").PivotCache.Refresh

End Sub

 

Код кнопки «Динамика цен»

Private Sub Dinamica_Click()

    Sheets("Динамика цен").Select

    ActiveWindow.SelectedSheets.Delete

    Charts.Add

    ActiveChart.SetSourceData Source:=Sheets("Сводная таблица").Range("B7")

    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Динамика цен"

    ActiveChart.ChartArea.Select

    ActiveChart.ChartType = xlLineMarkers

    With Selection.Border

        .ColorIndex = 16

        .Weight = xlThin

        .LineStyle = xlContinuous

    End With

    Selection.Shadow = False

    With Selection.Interior

        .ColorIndex = 19

        .PatternColorIndex = 1

        .Pattern = xlSolid

    End With

End Sub

 

Код кнопки «Прогноз цены»

Private Sub Prognoz_Click()

Sheets("Прогноз цены").Select

    ActiveWindow.SelectedSheets.Delete

    Charts.Add

    ActiveChart.SetSourceData Source:=Sheets("Сводная таблица").Range("B7")

    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Прогноз цены"

    ActiveChart.ChartArea.Select

    ActiveChart.ChartType = xlLineMarkers

    ActiveChart.SeriesCollection(1).Select

    ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=1, _

        Backward:=0, DisplayEquation:=True, DisplayRSquared:=False).Select

    ActiveChart.SeriesCollection(2).Select

    ActiveChart.SeriesCollection(2).Trendlines.Add(Type:=xlLinear, Forward:=1, _

        Backward:=0, DisplayEquation:=True, DisplayRSquared:=False).Select

    ActiveChart.SeriesCollection(3).Select

    ActiveChart.SeriesCollection(3).Trendlines.Add(Type:=xlLinear, Forward:=1, _

        Backward:=0, DisplayEquation:=True, DisplayRSquared:=False).Select

    ActiveChart.SeriesCollection(4).Select

    ActiveChart.SeriesCollection(4).Trendlines.Add(Type:=xlLinear, Forward:=1, _

        Backward:=0, DisplayEquation:=True, DisplayRSquared:=False).Select

    

    ActiveChart.ChartArea.Select

    With Selection.Border

        .Weight = xlHairline

        .LineStyle = xlNone

    End With

    Selection.Shadow = False

    With Selection.Interior

        .ColorIndex = 19

        .PatternColorIndex = 1

        .Pattern = xlSolid

    End With

    ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select

        Selection.Left = 406.615

        Selection.Top = 185.542

    ActiveChart.SeriesCollection(2).Trendlines(1).DataLabel.Select

        Selection.Left = 406.615

        Selection.Top = 56.129

    ActiveChart.SeriesCollection(3).Trendlines(1).DataLabel.Select

        Selection.Left = 406.615

        Selection.Top = 97.677

    ActiveChart.SeriesCollection(4).Trendlines(1).DataLabel.Select

        Selection.Left = 406.615

        Selection.Top = 257.774