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

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

На прикладі аналізу економічної задачі розглянемо методику побудови відповідей на більшість типових завдань практикуму. Рахується, що читач ознайомився з відповідним матеріалом учбових посібників [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$.

Дата добавления: 2015-10-16