Лабораторна робота №5 Моделювання на основі даних за допомогою електронних таблиць Microsoft Excel

« Назад

Мета роботи: вивчити основи використання Microsoft Excel з елементами моделювання, що може бути застосовано для вирішення широкого спектра управлінських задач.

Вступ

Управлінські рішення значною мірою базуються на оцінці та інтерпретації даних. Однак інтерпретація даних можлива тільки за наявності деякої концептуальної схеми. Складно відповісти, що первинне, схема чи збір даних. Очевидно, що дані необхідні для успішного моделювання. Прагнення створити більш досконалу модель призводить до збору та зберіганню додаткових даних або нових типів даних. Один з перших прибічників моделювання в сфері менеджменту, Черчмен (C.West Churchman), стверджував, що насправді не існує «необроблених» даних, в тому розумінні, що сам факт збору та фіксації даних завжди відображає деякі схеми світосприйняття, тобто певну модель, що побудовано в умі (хоча ця модель може явно й не усвідомлюватися). Тим не менше однією з особливостей технологічної цивілізації, принаймні в технічній сфері, є одночасні збір та використання даних та моделей.

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

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

Щоб побачити наскільки тісно пов’язані числа та моделі, розглянемо, як в фірмі А приймається рішення про те, скільки коштів виділити на маркетинг в Європі. Для цього корпоративної бази даних запитуються дані про витрати на маркетинг в Європі та загальному обсязі доходів від збуту за останні 12 років, потім вони розміщуються в таблиці Excel, та створюються дві діаграми, що показані на рис. 1.

Завдання 1. Створіть базу даних та побудуйте діаграми так, як це показано на рис. 1

Після вивчення поданих на рис. 1 даних виконавчий директор може припустити, що існує зв’язок між витратами на маркетинг та доходами від збуту. Наприклад, він вважає, що дохід в певному році напряму залежить виключно від витрат на маркетинг в тому ж році та не залежить від доходу та маркетингових витрат попередніх років. Він може висунути й інші гіпотези, наприклад, що доходи відповідають довгостроковій тенденції незалежно від витрат на маркетинг, або на них впливає деяка комбінація маркетингових витрат та довгострокової тенденції. Очевидно, що відповідні зв’язки будуть залежати від багатьох факторів, пов’язаних з реальним середовищем, в якому оперує фірма А. А щодо інших факторів, таких як загальні економічні умови? Які фактори реального світу можуть стояти за різним ступенем ефективності маркетингу в різні роки? Які реальні відносини відображають ці дані? (Це можуть бути відмінності в методах реклами, в чутливості ринку та попиту, які в свою чергу є наслідками відмінностей в економічних умовах, погоді та політиці уряду).

Рис. 1 - Витрати на маркетинг в Європі та загальний дохід від збуту, Excel

 

Завдання 2. Приклад – Tech CopyInc.

Розглядаючи даний приклад, виконуйте всі дії за зразком (див. рис. 2, 3, 4, 5, 6, 7, 8, 9, 10), намагаючись досягти в своїй роботі максимальної подібності із запропонованим розв’язком даної задачі (включаючи всі елементи форматування). Після виконання роботи спробуйте прийняти управлінське рішення, враховуючи отримані результати. Сформулюйте та обґрунтуйте його письмово в розділі ВИСНОВКИ.

Емілі та Білл Петерсони вирішили створити компанію - Tech Copy Inc., що буде встановлювати копіювальні машини, що самообслуговуються, в приміщеннях замовника – бібліотеки, університети, інститути, торгові центри і т. і. Щоб звести до мінімуму основні витрати, вони збираються брати в оренду потужні копіювальні машини, до яких підключені автомати для прийому монет та платіжних карток. Окрім плати за оренду та інших витрат на копіювальні машини, компанія Tech Copy може додатково платити організаціям-клієнтам, що надають місце для встановлення цих машин. Таким чином, оплата складається з фіксованої щомісячної плати за оренду приміщення плюс (необов'язково) деякі заохочувальні виплати. В межах свого бізнес-плану Емілі та Білл зробили деякі припущення.

Кількість копіювальних машин, що орендуються (змінна розв'язку) - 40

Щомісячна кількість копій з однієї машини (змінна розв'язку) - 30 000

Ціна однієї копії (змінна розв'язку) - $0.05

Змінна витрат на одну копію (витратні матеріали, ремонт і т.і.) - $0.03

Щомісячна орендна плата за приміщення для машини (змінна розв'язку) - $150

Інші щомісячні витрати:

Витрати на оренду однієї копіювальної машини - $250

Витрати на інкасацію грошей з однієї машини - $35

Інші фіксовані витрати на одну машину - $50

Для аналізу прибутковості нового підприємства було розроблено модель на базі електронної таблиці Excel, що представлена на рис. 2.

Рис. 2 - Розрахунок початкової моделі компанії Tech Copy Inc., Excel

Рис. 3 - Початкова модель компанії Tech Copy Inc., Excel

Петерсони планують вивчити альтернативні варіанти структури орендної плати за відведену для копіювальних машин площу. Окрім фіксованої щомісячної орендної плати $150 за одну машину можна спробувати запропонувати клієнтам більш низьку орендну плату плюс певні комісійні з кожної зробленої копії. Наприклад, організація-клієнт може отримати орендну плату за надану для однієї машини площу в розмірі $50 плюс комісійні з кожної зробленої копії в розмірі 0,5 цента. Ще один можливий варіант - фіксована орендна плата $75 плюс 1 цент комісійних з кожної копії, зробленої понад встановлений щомісячний ліміт в 20 000 копій. Перш ніж оголосити ці три альтернативні схеми плати за оренду площу, Петерсони будуть порівнювати обсяги копіювання, відповідні точки беззбитковості для даних варіантів.

Замість того, аби створювати та аналізувати модель для кожного варіанту орендної плати у вигляді окремої таблиці, доцільніше подати всі три альтернативи в трьох стовбцях однієї таблиці. Це дозволить порівнювати основні параметри альтернативних варіантів (рис. 4).

Рис. 4 - Розрахунок трьох варіантів орендної плати компанії Tech Copy Inc., Excel

Рис. 5 - Три варіанта орендної плати компанії Tech Copy Inc., Excel

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

Рис. 6 - Команда Подбор параметра, Excel

В діалоговому вікні «Подбор параметра» необхідно ввести в поле Установить в комірці посилання на комірку, що має вихідну змінну (в даному випадку ще буде комірка «Чистий дохід»), ввести 0 в поле Значение (значення, що відповідає точці беззбитковості), ввести в поле Изменяя значение ячейки посилання на комірку, що має змінну, що змінюємо, (тут - щомісячний обсяг виробництва копіювальної машини), як показано на рис. 7, а потім натиснути кнопку ОК.

Excel виконає ряд послідовних розрахунків, підставляючи різні значення в комірку, що змінюємо, аби отримати (якщо це можливо) бажаний результат в комірці В20, як показано на рис. 8. В даному випадку точка беззбитковості в першому варіанті оплати оренди досягається при обсязі 24 250 копій щомісяця для кожної машини.

Рис. 7 - Діалогове вікно Подбор параметра, Excel

Рис. 8 - Щомісячний обсяг, що гарантує беззбитковість за першим варіантом оплати оренди, Excel

Застосувавши засіб «Подбор параметра» до двох інших варіантів оплати оренди (в полі Установить в комірці діалогового вікна «Подбор параметра» зазначаються комірки С20 та D20 відповідно), можна порівняти значення, за яких досягаються точки беззбитковості, за всіма трьома варіантами (рис. 9).

Рис. 9 - Порівняння значень, за яких досягаються точки беззбитковості, Excel

Петерсонів також цікавлять так звані точки байдужості, в яких чистий прибуток не залежить від вибору альтернативних варіантів. Нехай кожна копіювальна машина робить 30 000 копій щомісяця, в 1-му варіанті передбачається фіксована орендна плата $150 щомісяця за установку однієї машини, альтернативні схеми оплати оренди наведені на рис. 9. Якою має бути фіксована орендна плата в інших схемах оплати, щоб отримати той самий чистий прибуток, що й варіанті 1? Щоб відповісти на це питання, необхідно додати в таблицю, що показано на рис. 5, формули, де з чистого прибутку варіанта 1 віднімається чистий прибуток варіанта 2 та варіанта 3 відповідно (рис. 10).

Рис. 10 - Розрахунок різниць значень чистого прибутку для різних варіантів, Excel

Після цього за допомогою засобу «Подбор параметра» можна знайти точку байдужості, тобто точку, в якій різниця між чистим прибутком в варіантах 2 та 1 дорівнює нулю, як показано на рис. 11. Аналогічно знаходиться точка байдужості для варіанту 3 (в порівнянні з варіантом 1); в даному випадку встановлюється значення в комірці D22, а змінюється значення в комірці D11. Кінцевий результат пошуку точок байдужості показано на рис. 12.

Надані на рис. 12 результати можна отримати з вихідної моделі за допомогою простих алгебраїчних дій, але підхід з використанням засобу «Подбор параметра» застосовується і для більш складних моделей, в яких створення необхідних формул не очевидне або ускладнене.

Рис. 11 - Знаходження точки байдужості для варіантів 1 та 2, Excel

Рис. 12 - Точки байдужості для варіантів 2 та 3, Excel