Будування табличної моделі

МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ

ДОНЕЦЬКИЙ ДЕРЖАВНИЙ УНІВЕРСИТЕТ

УПРАВЛІННЯ

Верзілов О.М., Літвак О.Г.

Комп’ютерний

Практикум

Методичні рекомендації та завдання
для студентів усіх спеціальностей
напрямку «Менеджмент»

Затверджено

на засіданні методичної

ради ДонДУУ

Донецьк ДонДУУ 2008

Верзілов О.М., Літвак О.Г.

Комп’ютерний практикуми: Методичні рекомендації та завдання для студентів усіх спеціальностей напрямку «Менеджмент». – Донецьк: ДонДУУ, 2008. – 59 с.

Комп’ютерний практикум містить варіанти завдань відповідно до робочої програми курсу інформатики, типові приклади виконання моделювання, розрахунків, використання програмних засобів, методичні вказівки до виконання та вимоги до оформлення звіту.

Для студентів усіх форм навчання, що вивчають сучасне застосування комп’ютерних технологій в економічних задачах.

Рецензенти: Тернов С.О., зав.кафедри вищої математики та інформатики Донецького Інституту автомобільного транспорту, доцент, канд.техн. наук;

Тамуров В.І., доцент кафедри вищої математики ДонДУУ, канд.техн. наук.

© Донецький державний університет управління, 2008

Зміст

Передмова.. 4

Типовий приклад.. 5

Постановка задачі 5

Будування табличної моделі 5

Аналіз чутливості (еластичності) 8

Точка беззбитковості 11

Регресійний аналіз. 12

Добір типу тренду та прогноз. 13

Таблиця підстановки.. 15

Вимоги до оформлення звіту.. 17

Підбивання підсумків практикуму.. 18

Варіанти завдань.. 20

Варіант №1. 20

Варіант №2. 23

Варіант №3. 25

Варіант №4. 28

Варіант №5. 31

Варіант №6. 35

Варіант №7. 38

Варіант №8. 42

Варіант №9. 45

Варіант №10. 48

Варіант №11. 51

Варіант №12. 52

Варіант №13. 54

Варіант №14. 57

Варіант №15. 59

Література.. 62

Додаток 1. 63

Додаток 2. Постачальники.. 64

Додаток 3. Клієнти.. 66

Передмова

Практикум з інформатики містить методичні вказівки та рекомендації до виконання завдань практикуму. Матеріал практикуму відповідає навчальній програмі курсу «Інформатика та комп’ютерна техніка».

Мета практикуму - поглибити та закріпити практичні навики, одержані внаслідок вивчення дисципліни "Інформатика та комп'ютерна техніка", навчитись швидко вибирати відповідні програмові засоби та забезпечувати якісний зв'язок між використовуваними засобами.

У результаті проходження курсу студенти повинні:

* Знатипринципи розв'язання практичних задач із застосуванням обчислювальної техніки, основні характеристики базового та прикладного програмного забезпечення, методи роботи з цим забезпеченням та методи обміну інформацією поміж програмними засобами.

* Вміти обрати спеціалізоване прикладне програмне забезпечення та підготовити його до роботи на персональному комп'ютері, організувати введення, обробку та зберігання інформації, орієнтуватися на можливості та якість обраного програмного забезпечення, отримувати тверді копії результатів роботи, отримувати та коректувати проміжні результати, встановлювати зв'язок між програмовим забезпеченням.

* Одержати навички роботи на персональному комп’ютері відповідно до кваліфікації користувача, отримати нові знання з організації та реалізації обробки інформації на персональному комп’ютері.

Завдання з комп'ютерного практикуму являють собою практичні завдання, що вирішуються суб’єктами підприємницької діяльності, підприємством із застосуванням методів математичного моделювання, численних методів в економіці, систем керування базами даних, засобів формалізації, обробки та відображення інформації в зручному для користувача вигляді.

Конкретні завдання уточнюються та затверджуються викладачем безпосередньо перед началом курсу «Комп’ютерний практикум».

Базою для проходження курсу «Комп’ютерний практикум» повинні стати підготовлені комп'ютерні класи ДонДУУ.

Типовий приклад

На прикладі аналізу економічної задачі розглянемо методику побудови відповідей на більшість типових завдань практикуму. Рахується, що читач ознайомився з відповідним матеріалом учбових посібників [1] та [2], особливо з главами про розв’язання оптимізаційних задач та статистичного аналізу даних.

Постановка задачі

Компанія по виробництву харчових продуктів «Схід» розташована в зоні виноробства. Восени вона купує виноград у виробників, сушить його, щоб здобути ізюм, обливає глазур’ю та продає глазурований ізюм виробникам пластівців для сніданку і кондитерським компаніям. По весні, на початку сезону вирощування винограду, компанія «Схід» має прийняти декілька взаємозалежних рішень. По-перше, слід вирішити, скільки винограду купувати за існуючим контрактом поставок, а по-друге, визначити ціну на глазурований ізюм.

Компанія «Схід» має контракт з виробником, котрий за заявкою на початку сезону повинен поставити восени вказану кількість винограду за ціною 0,25$ за кг. Відсутню кількість винограду компанія мусить купувати восени за вільною ринковою ціною, яка може коливатися у межах від 0,15$ до 0,35$ за кг.

На основі досвіду минулих років відомо, що 1) за ціною 2,15$ за кг клієнти замовляють 700 000 кг глазурованого ізюму; 2) при кожному зниженні ринкової ціни на 0,01$ загальний попит буде зростати на 15 000 кг.

Цукрову глазур компанія купує за ціною 0,30$ за кг. Для виготовлення 1 кг глазурованого ізюму необхідно 2,5 кг винограду та 0,5 кг глазурі (вода випаровується в процесі сушіння ізюму). Окрім витрат на сировину, компанія також несе витрати по перетворенню винограду в ізюм (витрати на переробку), які складають 0,20$ на переробку 1 кг винограду при виробничий потужності заводу компанії 3 500 000 кг винограду. Переробка винограду над цієї кількості потребує звернення до конкурента, котрий бере 0,60$ за перетворення 1 кг винограду в ізюм. Крім того, завод по переробці винограду має постійні накладні витрати, що становлять 200 000$ на рік (розробка та впровадження новітніх технологій, амортизація, фонд заробітної платні та інше).

Будування табличної моделі

Таблична модель – це перше з чого слід починати свої дії щодо вирішення поставлених завдань практикуму. Моделювання з використанням електронних таблиць не зводиться до простого розрахунку рівнянь, які зв’язують змінні. При будуванні моделі слід передбачити, як вона буде аналізуватися, та виходячи з цього так представити модель на робочому листі Excel, щоб вона мала наступні властивості:

1. Була логічно коректною.

2. Представляла основні альтернативи для порівняння.

3. З нею зручно було б проводити маніпуляції, які необхідні для аналізу.

4. Люди, що не приймали участь у створенні моделі, мали змогу легко її зрозуміти.

5. Зовнішнє оформлення моделі було привабливим.

Щоб забезпечити перелічені властивості та створити якісну модель треба дотримуватися наступних правил:

1. Необхідно чітко визначити усі змінні.

2. Слід чітко визначити входи моделі, змінні рішення та параметри.

3. Треба вказати критерії ефективності та вихідні змінні.

4. Параметри повинні зберігатися в окремих комірках робочого листа для зручності документування та аналізу.

5. Якщо можливо, слід відокремлювати змінні, які представляють фізичні величини, від фінансових змінних.

6. Використовувати можливості форматування для виділення заголовків таблиць та комірок, що надаються Excel.

Отже, побудуємо табличну модель виробництва глазурованого ізюму з метою оцінки річного прибутку. Припустимо для визначеності, що за контрактом купується 1 000 000 кг винограду.

Зверніть увагу, що усі значення задачі присутні в моделі (рис. 1). Деякі параметри було обчислено додатково. Наприклад, рівняння попиту, на основі якого вираховується об’єм продажів. За умовами задачі кожне зниження базової ціни у 2,15$ на 0,01$ збільшує попит на 15 000 кг. Отже це лінійне рівняння: попит = А * ціна + В.

Визначити коефіцієнт А та вільний член В можна двома шляхами:

1. Математичний. Треба використати рівняння прямої, що проходить крізь дві завдані точки [3].

2. Графічний. Теж достатньо двох пар значень 2,15$, 700 000 та 2,14$, 715 000. Треба побудувати графік та у параметрах додати рівняння тренду (рис. 2). При цьому слід обирати тип «Точечная».

Якщо у завданні не вказано шлях, то вибір вільний. Знайдені параметри рівняння вписані у комірки D12:D13.

На рис. 3 наведено всі формульні вирази, що використано в моделі. Зверніть увагу, що для автоматизації моделі була використана функція ЕСЛИ. Перший раз для розрахунку об’єму закупівлі винограду на ринку. Друга функція ЕСЛИ рахує витрати на переробку і не дозволяє перевищити потужність заводу компанії, та третя, в разі перевищення потужності рахує додаткові витрати на переробку у конкуруючої компанії.

Рис. 1. Таблична модель

Рис. 2. Рівняння тренду

Рис. 3. Таблична модель, формули

Отже прибуток винайдено, він дорівнює 337 500$. При цьому модель мала наступні припущення:

1. Ринкова ціна винограду узята максимальною, хоча маючі статистичні дані минулих років, її вибір можна обґрунтувати (спрогнозувати).

2. Рівняння попиту лінійне. Це припущення дійсне лише поблизу базової ціни у 2,15$.

3. Закупівля винограду за контрактом фіксована.

Зауваження! Будування табличної моделі показано для задачі, яка має одну змінну рішення. Якщо у інших задачах їх більше, то роблять за аналогією, будуючи більше стовпців.

Аналіз чутливості (еластичності)

Розібраний далі приклад аналізу чутливості стосується тільки задач з однією змінною рішення. Якщо ж їх більше, то для аналізу слід використовувати звіти засобу «Поиск решения», які описані у навчальному посібнику [2] та в даній роботі не повторюються.

У ході аналізу чутливості досліджується вплив відносно малих змін вхідних змінних на інші змінні, частіш усього – на показник ефективності. У даній задачі нас цікавить вплив малих змін ціни ізюму на прибуток, який є критерієм ефективності. Тобто, на скільки відсотків зміниться прибуток при заданій відсотковій зміні ціни (рис. 4).

Рис. 4. Аналіз чутливості

Для аналізу було додано рядки відсотків зміни базової ціни (рядок 3) і зміни прибутку (рядок 31). Рядки параметрів моделі були сховані (рядки 6-16), або згруповані, тому що не становлять інтересу для аналізу чутливості.

Таким чином, аналіз чутливості показав, що зменшення базової ціни на п’ять відсотків призведе до збільшення прибутку на 3%. Але і об’єм продажів буде становити при цьому 850 000 кг (комірка Е18) замість базових 700 000 кг.

Перевіримо, а чи дає ціна у 2,05$ максимальний прибуток. В тім що це не так легко переконатися хоча б з графіку залежності прибутку від ціни. Обравши дані рядків 4 і 30, здобудемо наглядне представлення.

Рис. 5. Графік залежності ціни від прибутку

Вочевидь, оптимальна ціна знаходиться десь між 2,05 та 2,10$. Знайти точне значення можна за допомогою засобу «Поиск решения». Це буде найпростіший варіант його застосування, оскільки не буде потребувати уведення обмежень (графічно видно що це локальний максимум). Встановлюємо цільову комірку у Е30 (рис. 4), а комірка що змінюється – Е4.

Рис. 6. Оптимальна ціна

Отже з’ясувалося, що найбільший прибуток у 346 901,04$ буде здобутий за ціною 2,07$.

Точка беззбитковості

Аналізуючи початкову модель задачі (рис. 1), можна знайти що прибуток залежить не тільки від ціни глазурованого ізюму, а і від таких параметрів, як витрати на купівлю винограду, витрати на купівлю глазурі, витрати на переробку та постійні накладні витрати.

Беззбитковість означає що у найгіршому випадку буде отримано дохід, що дорівнюватиме загальним витратам, тобто прибуток буде дорівнювати нулю.

Припустимо, що ринкова ціна винограду різко зростає. Логічним кроком є збільшення ціни на ізюм. Але це призведе до зниження попиту та об’єму продажів, втрати клієнтів і як слідство – до зниження прибутку. Отже необхідно деякій час тримати ціну, але важливо знати межу збільшення ринкової ціни винограду, щоб не понести збитки.

Використаємо засіб «Подбор параметра» (рис. 7).

Рис. 7. Добір параметра

D29 – це комірка з прибутком. Значення, що встановлюється – це нуль – беззбитковість. $D$6 – містить значення ринкової ціни винограду.

Рішення буде мати такий вигляд:

Рис. 8. Визначення точки беззбитковості

Визначено, що найвища ринкова ціна винограду, при якій компанія буде мати позитивний прибуток, складає 0,68$. Зверніть увагу що базова ціна змінена з 2,15$ на 2,07$. Якщо це не зробити, то результати будуть інші.

Регресійний аналіз

Компанія «Схід» за останні декілька років мала стабільний прибуток, але статистичний відділ на останній нараді надав дані (рис. 9) щодо витрат на переробку винограду, які не співпадають з модельними. Виявилося, що витрати різко збільшуються, якщо завод працює не на повну потужність. Цей факт можна пояснити, наприклад, додатковими витратами у зв’язку з простоєм обладнання.

Рис. 9. Дані статистичного відділу

Таким чином, наша задача – визначити рівняння залежності витрат на переробку від кількості винограду. Оберемо для цього засіб «Регрессия» з меню «Анализ данных». Вхідним інтервалом Y має бути F3:F10, а вхідним інтервалом X – G3:G10 – незалежна змінна.

Рис. 10. Регресійний аналіз

Здобуті результати (рис. 10) говорять о дуже щільній кореляційній залежності (коефіцієнт кореляції 0,99) та високій статистичній значимості здобутих параметрів (статистика Фішера 463).

Рівняння лінійної залежності має вигляд: Витрати на переробку = 0,13227 * Кількість винограду + 229369,1. Тепер слід уточнити існуючу модель розраховуючи витрати на переробку за здобутим рівнянням, якщо потужність заводу не повністю використовується. Для цього, звісно, повинна застосовуватися функція ЕСЛИ (вкладена).