Применение информационных технологий обработки экономических данных при анализе рынка товаров и услуг

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

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

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

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

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

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

 

 

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

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

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

 

 Таблица 3

Оптимизационная задача

 

 

 

 

 

 

 

 

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

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

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

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

Рис. 6 Лист интерфейса

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

Рис. 7 Главная форма

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

Рис. 8  Форма ввода данных

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

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

Рис. 9  Оптимизация

Также каждый лист программы оснащен кнопкой возврата к главной форме.

 

Заключение

В данной курсовой работе был рассмотрен такой сегмент  рынка как «Бытовая техника», в  котором выделено 4 товара: пылесосы, СВЧ –печи, стиральные машины и холодильники». Продавцами, конкурирующими в этом сегменте, являются: «Bosch», «Samsung», «LG».

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

1. происходит плавное снижение цен на стиральные машины и СВЧ- печи, цены на пылесосы возрастают, а цены на холодильники относительно стабильны.;
2. проведенный в работе прогноз говорит о том, что на десятый прогнозируемый период (апрель 2010 года) средние цены на товары составят:

• Пылесос = 3961,51 руб.;

• Стиральная машина = 15144,1 руб.;
• СВЧ- печи =  3637,22 руб.;
• Холодильник =  23095,61  руб.

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

 

 

 

 

Список использованных источников

 

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. Сайт «Планета Exel» http://www.planetaexcel.ru/

 

 

 

 

 

 

 

 

 

 

 

 

Приложения

Приложение 1

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

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

Private Sub VD_Click()

'Ввод даных

Glavn.Hide

Vvod.Show

End Sub

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

Private Sub Svodn_Click()

'

' Макрос5 Макрос

'

 

    Sheets("Лист1").Select

    Range("A1").Select

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

        "ИсходныеДанные!R1C1:R120C4", Version:=xlPivotTableVersion12). _

        CreatePivotTable TableDestination:="Лист1!R1C1", TableName:= _

        "СводнаяТаблица1", DefaultVersion:=xlPivotTableVersion12

    Sheets("Лист1").Select

    Cells(1, 1).Select

    ActiveWorkbook.ShowPivotTableFieldList = True

    With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields( _

        "Наименование товара (услуги)")

        .Orientation = xlColumnField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields( _

        "Наименование продавца (поставцика)")

        .Orientation = xlPageField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Период ")

        .Orientation = xlRowField

        .Position = 1

    End With

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

        ("СводнаяТаблица1").PivotFields("Цена, руб."), "Количество по полю Цена, руб." _

        , xlCount

    With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields( _

        "Количество по полю Цена, руб.")

        .Caption = "Среднее по полю Цена, руб."

        .Function = xlAverage

    End With

    ActiveWorkbook.ShowPivotTableFieldList = False

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 = 98.615

        Selection.Top = 495.542

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

        Selection.Left = 200.615

        Selection.Top = 440.129

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

        Selection.Left = 115.615

        Selection.Top = 200.677

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

        Selection.Left = 225.615

        Selection.Top = 75.774

    ActiveChart.ChartArea.Select

End Sub

 

Код кнопки «Оптимизационная задача»

Private Sub Optimizacia_Click()

Glavn.Hide

Opt.Show

End Sub

 

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

Private Sub Выход_Click()

Glavn.Hide

End Sub

 

Приложение 2

Форма оптимизации

 

Кнопка «ok»

 

Private Sub ok_Click()

Worksheets("Оптимизация").Select

Worksheets("Оптимизация").Cells(12, 3) = CSng(TextBox1)

Range("D5:D8").Select

Range("D5").Activate

Selection.ClearContents

Opt.Hide

    Range("D12").Select

    SolverOk SetCell:="$D$12", MaxMinVal:=1, ValueOf:="0", ByChange:="$D$5:$D$8"

    SolverAdd CellRef:="$D$5:$D$8", Relation:=4, FormulaText:="целое"

    SolverAdd CellRef:="$D$5:$D$8", Relation:=3, FormulaText:="0"

    SolverAdd CellRef:="$E$12", Relation:=1, FormulaText:="$C$12"

    SolverOk SetCell:="$D$12", MaxMinVal:=1, ValueOf:="0", ByChange:="$D$5:$D$8"

    SolverSolve

End Sub

 

 

Кнопка «Назад»

Private Sub Назад_Click()

Opt.Hide

Glavn.Show

End Sub

Приложение 3

 

Форма ввода  данных

Код кнопки «Добавить»

Private Sub Добавить_Click()

Worksheets("ИсходныеДанные").Select

Cells(2, 1).Select

Selection.EntireRow.Insert

    Worksheets("ИсходныеДанные").Cells(2, 1) = Товар

    Worksheets("ИсходныеДанные").Cells(2, 2) = Продавец

    Worksheets("ИсходныеДанные").Cells(2, 3) = CDate(Период)

    Worksheets("ИсходныеДанные").Cells(2, 4) = CSng(Цена)

Range("A2:D2").Select

    Selection.Font.Bold = False

Rows("2:2").RowHeight = 17.25

End Sub

Код кнопки «Назад»

Private Sub Назад_Click()

Vvod.Hide

Glavn.Show

End Sub

 

Инициализация формы

Private Sub UserForm_Initialize()

Application.Worksheets("ИсходныеДанные").Select

 

Продавец.AddItem "Bosch"

Продавец.AddItem "Samsung"

Продавец.AddItem "LG"

 

Товар.AddItem "Пылесосы"

Товар.AddItem "СВЧ-печи"

Товар.AddItem "Стиральные машины"

Товар.AddItem "Холодильники"

 

Период.AddItem "Июль 2012"

Период.AddItem "Август 2012"

Период.AddItem "Сентябрь 2012"

Период.AddItem "Октябрь 2012"

Период.AddItem "Ноябрь 2012"

Период.AddItem "Декабрь 2012"

Период.AddItem "Январь 2013"

Период.AddItem "Февраль 2013"

Период.AddItem "Март 2013"

End Sub