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

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

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

УПРАВЛІННЯ

 

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

 

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

Практикум

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

 

Затверджено

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

ради ДонДУУ

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

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

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

 

 

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

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

 

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

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

 

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


Зміст

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

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

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

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

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

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

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

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

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

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

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

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

Варіант №1. 19

Варіант №2. 22

Варіант №3. 24

Варіант №4. 27

Варіант №5. 30

Варіант №6. 34

Варіант №7. 37

Варіант №8. 41

Варіант №9. 44

Варіант №10. 47

Варіант №11. 49

Варіант №12. 51

Варіант №13. 53

Варіант №14. 55

Варіант №15. 57

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

Додаток 1. 61

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

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

 


Передмова

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

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

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

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

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

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

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

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

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

 

 


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

    На прикладі аналізу економічної задачі розглянемо методику побудови відповідей на більшість типових завдань практикуму. Рахується, що читач ознайомився з відповідним матеріалом учбових посібників [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. Тепер слід уточнити існуючу модель розраховуючи витрати на переробку за здобутим рівнянням, якщо потужність заводу не повністю використовується. Для цього, звісно, повинна застосовуватися функція ЕСЛИ (вкладена).

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

    Компанія «Схід» працює на українському ринку, але фінансово прив’язана до іноземної валюти – долару. Тому коливання курсу долару впливає на прибуток. Наприклад, зростання курсу призводить до зменшення попиту та падінню обсягів продажів. Щоб запобігти цьому, слід визначити закон коливання курсу за останніми даними, тобто тренд. Спрогнозувати значення курсу на наступний період планування та скорегувати ціну на продукцію, змінивши тим самим об’єм продажів в більший чи менший бік.

Рис. 11. Дані курсу долару за останні місяці

    Excel надає можливість побудувати декілька трендів за наданим рядом значень (рис. 11). Для цього слід побудувати графік та в його параметрах обрати один з припустимих типів тренду (рис. 12).

Рис. 12. Обирання типу тренду

    Далі, в цьому вікні на вкладці «Параметры» потрібно увімкнути дві опції: «показывать уравнение на диаграмме» та «поместить на диаграмму величину достоверности аппроксимации R^2».

    Саме коефіцієнт апроксимації (детермінації) надасть нам можливість обрати краще рівняння. З курсу економетрії [4] відомо, що чим ближче коефіцієнт R2 до одиниці, тим точніше рівняння тренду описує зміни ряду, а отже точніше буде прогнозне значення на основі цього рівняння.

    При виконанні такого завдання не потрібно приводити графічні тренди. Замість цього слід надати у звіті таку таблицю:

Рис. 13. Оцінка якості тренду

    Очевидно, що найкращий тренд надає поліноміальна залежність четвертої степені. Найгірші – логарифмічна та ступенева. Отже, обираємо для прогнозу рівняння y = 0,0004x^4 - 0,0057x^3 + 0,0174x^2 - 0,0032x + 5,1362. Саме для цього тренду у звіті слід надати графік (рис. 14).

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

    За здобутим рівнянням прогнозне значення курсу дорівнює Y(9) = 5,136. Отже, якщо ціна глазурованого ізюму складала 2,07$ за курсом 5,09, то при його зростанні приблизно на 0,05 грн (це 0,01$) попит впаде за умовами задачі приблизно на 15 000 кг.

    Таким чином можемо скорегувати модель, уточнивши попит, або встановити ціну ізюму 2,06$ для збереження об’ємів продаж.

Зауваження! При розрахунку тренду та прогнозу можна використовувати матеріал курсу «Економетрія». Цей факт надає можливість нарахування додаткових балів при захисті звіту практикуму.

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

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

    Для цього будуємо таблицю, де заголовки стовпців це ціна ізюму, а заголовки рядків – ринкова вартість винограду (рис. 15).

Рис. 15. Формування таблиці підстановки

    У ліву кутову комірку таблиці треба вписати формулу обчислення прибутку, але легше на її послатися – вписати у комірку F6 формулу =D29. Після цього слід виділити усю таблицю F6:P18 та обрати у меню «Данные» засіб «Таблиця подстановки». Далі треба заповнити діалогове вікно, що з’явилося.

Рис. 16. Діалогове вікно таблиці підстановки

    Комірка D3 – це ціна глазурованого ізюму у нашій моделі, D6 – ринкова вартість винограду.

Рис. 17. Результат підстановки

    Аналіз здобутих даних відкрив важливий факт: при ринковій ціні винограду від 0,15$ до 0,30$ для отримання максимального прибутку слід знижувати ціну ізюму, а от починаючи з 0,35$ ціну слід збільшувати. Максимальні значення прибутку для кожного значення ринкової вартості винограду у таблиці обведені рамками (рис. 17).

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

    Звіт з практикуму друкується на аркушах формату А4 (21 на 29,7 см) білого паперу зі стандартними полями: верхнє та нижнє по 2 см, ліве 3 см і праве 1,5 см.

    Параметри форматування основного шрифту звіту та абзаців: Times New Roman, розмір 14, вирівнювання за шириною, автоматичний перенос слів увімкнено, міжрядковий інтервал 1,5, відступ 1,25 см.

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

    Звіт повинен містити таки розділи:

· Титульний лист (див. додаток 1).

· Зміст.

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

· Відповіді на завдання практикуму.

· Загальний висновок.

· Додатки.

· Список літератури.

Зміст звіту має бути генерований автоматично засобами текстового редактору. Для цього попередньо слід використати стиль заголовків декількох рівнів (не менш двох). Формат змісту довільний, але номери сторінок по правому краю.

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

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

Окрім посилань на рисунки, при відповіді на завдання практикуму мають бути посилання на відповідні додатки, розташовані у кінці звіту.

Відповідь на кожне завдання мусить мати заголовок (наприклад, «завдання 1»), який буде відображений у змісту при його генерації.

    У загальному висновку слід охарактеризувати модель, що побудована. Вказати припущення, що були використані при моделюванні, ступінь відповідності моделі реальній економічній ситуації. Також описати шляхи вдосконалення моделі для аналізу нових економічних питань.

    Додатки. На відміну від іншої частини звіту, додатки друкуються виключно в тій програмі, у якій виконувалося завдання, тобто у Excel. Правила для оформлення додатків наступні:

· Кожен додаток міститься на окремому листі Excel, ім’я листа відповідає номеру завдання (наприклад, «Завдання 1»).

· Якщо лист містить формули, то їх треба показати на додатковому листі з таким же ім’ям, але з позначкою «формули».

· Додаток може займати декілька сторінок.

· Кожна сторінка додатку повинна мати колонтитул, який формується засобами Excel, де вказати номер додатку, ім’я листа, номер сторінки, дату, шифр спеціальності та прізвище з ініціалами виконувача. 

· Один додаток може відповідати декільком завданням. Його лист повинен мати відповідне ім’я (наприклад, «Завдання 1-3»).

Список літератури містить джерела, які були використані при виконанні завдань практикуму.

Забороняється внесення правок або доповнень у звіт від руки та додавання ксерокопій.

Усі листи звіту підшиваються до папки або переплітаються. Окрім звіту для захисту необхідно мати при собі електронний носій (дискету, флеш, лазерний диск або доступ мережею) з виконаними завданнями практикуму та звітом.


Дата добавления: 2018-02-15; просмотров: 11;