Економічна модель оптимізації закупівель та поставок кондитерських виробів на прикладі товариства з обмеженою відповідальністю "Гермес-Груп" (стр. 16 из 22)
Інформаційна система під назвою MagistrPro V 00-04-04 створена на базі Ехсеl. Система призначена для підвищення ефективності роботи підприємства шляхом оптимізації обсягів закупівель на підприємстві та максимізації прибутку, яке закуповує кондитерські вироби у підприємств-виробників та продає їх споживачам.
Інформаційна система підтримки прийняття рішень вирішує такі задачі:
- розрахунок втрат підприємства;
- фінансовий аналіз;
- графічний аналіз втрат та залишків підприємства;
- оптимізація обсягів закупівель за допомогою нової розробленої моделі по кожному з споживачів;
- розрахунок максимального прибутку;
- формування бланка замовлень.
Перераховані задачі в системі здійснюється шляхом створення багатомірної базі даних. Розроблена інформаційна система складається з підсистем:
1. Вихідні дані;
2. Підсистема фінансового аналізу;
3. Підсистема аналізу втрат;
4. Підсистема графічного аналізу;
5. Підсистема моделювання.
Система складається з інтерфейсу користувача, бази даних та бази моделей.
Інтерфейс користувача є невід'ємною частиною будь-якої інформаційної системи, що визначає всі можливі шляхи і зручності доступу до її окремих компонентів. Інтерфейс користувача створений за допомогою кнопок (кнопковий інтерфейс). Цей тип інтерфейсу використовує діалогові вікна, коли від користувача потрібно внести вихідну інформацію для виконання додатком різних задач, алгоритми рішення яких, звичайно сховані від користувача і реалізуються програмним шляхом у надбудовах (Рисунок 3.1).
Кнопки системи розроблені за допомогою елементу Кнопка на панелі інструментів. Кнопка СТАРТ забезпечує користувачеві перехід до головного меню та працює при виконанні наступного макросу:
Private Sub CommandButton1_Click()
Sheets("Лист2").Select
End Sub
Кнопка ВИХІД забезпечує вихід з створеної інформаційної системи та працює при виконанні такого макросу
Private Sub CommandButton2_Click()
ActiveWorkbook.Close
End Sub
Рисунок 3.1 – Інтерфейс користувача
Перехід до інструкції користувача здійснюється шляхом виконання макросу:
Private Sub CommandButton3_Click()
Sheets("Инструкція користувача").Select
End Sub.
Після натискання кнопки СТАРТ користувач потрапляє на лист ГОЛОВНЕ МЕНЮ (Рисунок 3.2)
Рисунок 3.2 – Головне меню
Головне меню дозволяє користувачеві при натисканні кнопок з назвами підсистем перейти на лист з підсистемой, яку він обрав. Кнопкою ЗАСТАВКА користувач вертається на лист під назвою інтерфейс користувача. Після натискання кнопок переходу до підсистем, виконуються схожі макроси. Іх різниця складаєтсья в назві сторінки, до якої повинен перейти користувач. Приведемо далі один з таких макросів:
Private Sub CommandButton5_Click()
Sheets("підс_матем").Select
End Sub
Цей макрос забезпечує перехід до підсистеми моделювання.
Вихідними даними інформаційної системи підтримки прийняття рішень є База цін (Рисунок 3.3) – це і є база даних. Всі проміжні дані перераховуються в базі моделей з кожним новим занесенням даних в базу даних.
Рисунок 3.3 – База цін – вихідні дані
Підсистема фінансового аналізу включає в себе: баланс та звіт про фінансові результати (вихідні дані); розрахунок коефіцієнтів та розрахунок горизонтального аналізу на підприємстві. Завдяки роботі цієї підсистеми керівник може одразу зробити висноски про роботу підприємства. Підсистема показує ретабельність підприємства, ліквідність, платоспроможність, обертання запасів та т. і. Горизонтальний аналіз показує динаміку валюти балансу. Підсистема включає в себе кнопки переходу, макроси яких наведені вище.
Підсистема аналізу втрат розраховує залишки товарів, тобто різницю закупівель та продажів. Цей пункт є необхідним, тому саме з цього розрахунку керівник підприємства може побачити, як працює підприємство, скільки воно недоотримує тисяч гривень за рік реалізації товарів. Підсистема працює завдяки виконанню таких макросів:
Макрос заповнення форми залишків, при виконаня якого заповнюється форма Залишків по підприємствах:
Sub потери()
'' потери Макрос
' Макрос записан 20.06.2009 (User)
' ActiveCell.FormulaR1C1 = "='Анализ З и П'!R[14]C"
Range("C6").Select
Selection.AutoFill Destination:=Range("C6:C8"), Type:=xlFillDefault
Range("C6:C8").Select
Selection.AutoFill Destination:=Range("C6:N8"), Type:=xlFillDefault
Range("C6:N8").Select
ActiveWindow.LargeScroll ToRight:=-2
Range("C9").Select
ActiveCell.FormulaR1C1 = "='Анализ З и П'!R[14]C"
.....................................................................
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("C13").Select
ActiveCell.FormulaR1C1 = "='Анализ З и П'!R[27]C"
Range("C13").Select
Selection.AutoFill Destination:=Range("C13:C15"), Type:=xlFillDefault
Range("C13:C15").Select
Selection.AutoFill Destination:=Range("C13:G15"), Type:=xlFillDefault
Range("C13:G15").Select
Range("G13:G15").Select
Selection.AutoFill Destination:=Range("G13:H15"), Type:=xlFillDefault
"='Анализ З и П'!R[27]C"
Range("C17").Select
Selection.AutoFill Destination:=Range("C17:H17"), Type:=xlFillDefault
Range("C17:H17").Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("C20").Select
ActiveCell.FormulaR1C1 = "='Анализ З и П'!R[39]C"
Range("C20").Select
Selection.AutoFill Destination:=Range("C20:C22"), Type:=xlFillDefault
Range("C20:C22").Select
Selection.AutoFill Destination:=Range("C20:Y22"), Type:=xlFillDefault
Range("C20:Y22").Select
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("C23").Select
ActiveWindow.SmallScroll Down:=3
ActiveCell.FormulaR1C1 = "='Анализ З и П'!R[39]C"
Range("C23").Select
Selection.AutoFill Destination:=Range("C23:Y23"), Type:=xlFillDefault
Range("C23:Y23").Select
ActiveWindow.LargeScroll ToRight:=-3
Range("C24").Select
ActiveCell.FormulaR1C1 = "='Анализ З и П'!R[39]C"
Range("C24").Select
Selection.AutoFill Destination:=Range("C24:Y24"), Type:=xlFillDefault
Range("C24:Y24").Select
ActiveWindow.LargeScroll ToRight:=-3
ActiveWindow.SmallScroll Down:=6
Range("C27").Select
ActiveCell.FormulaR1C1 = "='Анализ З и П'!R[51]C"
Range("C27").Select
Selection.AutoFill Destination:=Range("C27:K27"), Type:=xlFillDefault
Range("C27:K27").Select
.................
Selection.AutoFill Destination:=Range("C31:K31"), Type:=xlFillDefault
Range("C31:K31").Select
ActiveWindow.LargeScroll ToRight:=-2
Range("C34").Select
ActiveCell.FormulaR1C1 = "='Анализ З и П'!R[63]C:R[63]C[5]"
Range("C34").Select
ActiveCell.FormulaR1C1 = "='Анализ З и П'!R[63]C"
Range("C34").Select
Selection.AutoFill Destination:=Range("C34:C36"), Type:=xlFillDefault
Range("C34:C36").Select
Range("C34:C36").Select
Selection.AutoFill Destination:=Range("C34:H36"), Type:=xlFillDefault
Range("C34:H36").Select
ActiveWindow.SmallScroll ToRight:=-6
Range("C37").Select
ActiveCell.FormulaR1C1 = "='Анализ З и П'!R[63]C"
Range("C37").Select
...................................................................................................
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-30
Range("C6").Select
End Sub
Після заповнення форми вона може буди очищена, шляхом натискання кнопки ОЧИСТКА:
Sub Очистка1()
'' Очистка1 Макрос
' Макрос записан 16.06.2009 (User)
' Range("C3:N7").Select
Selection.ClearContents
Range("C10:H14").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=6
ActiveWindow.LargeScroll ToRight:=-1
Range("C17:Y21").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=9
Range("C24:K28").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=9
Range("C31:H35").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-33
Range("C3").Select
End Sub
Після того, як форма залишків заповнилась при натисканні кнопки ИТОГ ПОТЕРЬ ЗА ГОД з переходом на іншу сторінку виконується наступний макрос:
Sub итогпотерь()
'' итогпотерь Макрос
' Макрос записан 20.06.2009 (User)
' ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Итого за год").Select
Range("D5").Select
ActiveCell.FormulaR1C1 = "=SUM('Остатки и потери'!R[-2]C[-1]:R[-2]C[10])"
Range("D6").Select
ActiveCell.FormulaR1C1 = "=SUM('Остатки и потери'!R[-2]C[-1])"
Range("D7").Select
ActiveCell.FormulaR1C1 = "=SUM('Остатки и потери'!R[-2]C[-1]:R[-2]C[10])"
Range("D6").Select
ActiveCell.FormulaR1C1 = "=SUM('Остатки и потери'!R[-2]C[-1]:R[-2]C[10])"
Range("D8").Select
ActiveCell.FormulaR1C1 = "=SUM('Остатки и потери'!R[-2]C[-1]:R[-2]C[10])"
Range("E5").Select
ActiveCell.FormulaR1C1 = "=SUM('Остатки и потери'!R[5]C[-2]:R[5]C[3])"
Range("E6").Select
ActiveCell.FormulaR1C1 = "=SUM('Остатки и потери'!R[5]C[-2]:R[5]C[3])"
Range("E7").Select