На головну

I. Лабораторні роботи

  1. A. Порядок виконання роботи
  2. I Актуальність дипломної роботи
  3. I. 10. Опишіть принципову схему установки і хід виконання лабораторної роботи.
  4. I. Цілі і завдання виконання курсової роботи
  5. I. Мета та завдання ВИПУСКНИЙ КВАЛІФІКАЦІЙНОЇ РОБОТИ
  6. I. Мета і завдання роботи.

Міністерство освіти Республіки Білорусь

Білоруський національний технічний університет

Факультет технологій управління і гуманітаризації

Кафедра «Митна справа»

Обробка економічної інформації

Методичні вказівки і завдання

За лабораторних робіт

Для студентів денного відділення

спеціальності I - 26.02.02 - «Менеджмент»

Мінськ 2005


УДК 330.47 (076.5)

ББК 65с. я73

О-23

У цьому виданні описані короткі теоретичні відомості, розглянуті приклади розв'язання задач, наведені завдання для лабораторних робіт і методичні рекомендації щодо їх виконання з дисципліни «Обробка економічної інформації». Метою лабораторних робіт є підготовка студентів до вмілому використанню методів обробки інформації, реалізованих в програмах MS Excel і STATISTICA: фінансові розрахунки; аналіз «що-якщо»; статистична обробка; регресійний аналіз і прогнозування.

Метою розрахунково-графічної роботи є закріплення навичок студентів щодо застосування електронних таблиць MS Excel в фінансово-економічній сфері (фінансові функції, інструменти аналізу «що-якщо»).

Упорядник О. в. Альшевскіх

рецензенти:

_____________________________________________________________________________

© Альшевскіх О. в.,

складання, 2005

I. Лабораторні роботи

Лабораторна робота № 1

Фінансово-економічні розрахунки. Аналіз «що-якщо»

Мета роботи: навчитися застосовувати фінансові функції MS Excel для розрахунків за цінними паперами, інвестицій, вкладами і позиками, використовувати механізм підбору параметра, таблиці підстановки і сценарії для варіантного аналізу.

Методичні рекомендації

фінансові функції застосовуються для розрахунків за позиками, вкладами, інвестиціям, цінних паперів, амортизації.

Для розрахунків з цінних паперів використовуються функції ДОХІД, ПОЛУЧЕНО, ЗНИЖКА, ЦІНА, ДНЕЙКУПОН і ін.

Функція ДОХІД обчислює дохід по облігаціях, який складають періодичні процентні виплати. Функція ЦІНА повертає ціну за 100 руб. номінальної вартості цінних паперів, за якими виплачується періодичний відсоток. Функція ПОЛУЧЕНО повертає суму, отриману в термін вступу в силу повністю забезпечених цінних паперів.

Для розрахунків за позиками, вкладами та інвестицій використовуються функції БС (БЗ), ПС (ПЗ або НЗ), КПЕР, СТАВКА (НОРМА), ПЛТ (ППЛАТ), ОСПЛТ (ОСНПЛАТ), ПРПЛТ (ПЛПРОЦ), ВСД (ВНДОХ), ЧПС (НПЗ), ЧИСТВНДОХ і ін. У дужках дані імена цих функцій для Excel 2000.

Функція БС повертає майбутнє (накопичене) значення вкладу.

Функція ПС повертає поточний обсяг внеску, т. Е. Загальну суму, яку складуть майбутні платежі, застосовується для знаходження величини позики, позики, початкового розміру вкладу.

Функція ПЛТ визначає величину платежу за відсотками за один період виплат.

Функція КПЕР повертає загальна кількість періодів виплати для даного вкладу, наприклад, якщо періодом виплат або нарахування відсотків був місяць, то функція поверне кількість місяців.

Функція СТАВКА повертає норму прибутку за один період, наприклад, якщо періодом виплат або нарахування відсотків був квартал, то функція поверне квартальну процентну ставку.

Дані функції призначені для розрахунків на основі періодичних платежів і постійної процентної ставки, в них кожен фінансовий аргумент виражається через інші.

При заповненні аргументів необхідно враховувати наступне:

1) проводиться виплата або отримання грошових коштів: сума, яка виплачується, вводиться зі знаком мінус;

2) періодичність виплат або нарахування відсотків: якщо цей період становить місяць, квартал або півріччя, то річна відсоткова ставка ділиться відповідно на 12, 4 або 2, а період виражається в місцях, кварталах, півріччях;

3) проводиться виплата на початку або в кінці періоду: якщо виплати проводяться в кінці періоду, то в розглянутих функціях аргумент «Тип» має дорівнювати 0, а для виплат на початку періоду, т. Н. обов'язкових платежів, «Тип» = 1.


приклад 1.1. Визначити прибутковість облігацій, за якими проводяться періодичні виплати відсотків, якщо відомі такі дані:

 Дата угоди  Дата вступу в силу  ставка  Ціна  погашення  частота  базис
 01.03.2003  01.12.2007  8,8%  96,545

Рішення:

1. Введемо вихідні дані в осередку аркуша Excel.

2. Для визначення прибутковості облігацій використовуємо функцію ДОХІД. Якщо її немає в списку фінансових функцій, то на комп'ютері потрібно встановити надбудову Excel «пакет аналізу»І включити його командою сервіс-надбудови.

3. Заповнимо всі аргументи функції за допомогою майстри функцій.

  A B C D E F G H I
 завдання 1.2.4
 Дата угоди  Дата вступу в силу  ставка  Ціна  погашення  частота  базис  Дохід  Формула
 01.03.2003  01.12.2007  8,8%  96,545  9,72%  = Дохід (А3; B3; C3; D3; E3; F3; G3)

Приклад 1.2. Визначити початковий розмір вкладу, якщо при ставці 6,7% річних з щоквартальним нарахуванням відсотків через 8 років на рахунку стало 15 542 рублів.

Рішення:

1. Введемо вихідні дані в осередку аркуша Excel.

2. Для визначення початкового розміру вкладу використовуємо функцію ПС.

3. Заповнимо всі аргументи функції за допомогою майстри функцій:

У цьому завданню періодом нарахування відсотків є квартал, тому річна процентна ставка ділиться на 4, а кількість років множиться на 4. Аргументи «ПЛТ» і «Тип» не заповнено, тому що за умовою задачі немає періодичних платежів і не вказано на початку або в наприкінці періоду нараховуються відсотки. Результат обчислень вийшов негативним, тому що внесення грошей на рахунок є виплатою.

  A B C D E F
 завдання 2.2.3
   накопичено  Кількість років  ставка  початковий внесок  Формула
   6,7%  -9 133,83р.  = Пс (D9 / 4; C9 * 4 ;; B9)

Інструменти аналізу «що-якщо»

Аналіз «що-якщо» включає чотири інструменти: сценарії, таблиця підстановки, підбір параметра і пошук рішення. Однойменні команди для використання цих інструментів розташовані в меню сервіс.

Підбір параметра використовується, якщо відомий кінцевий результат, а значення, від якого він залежить, немає. Механізм підбору параметра полягає в зміні значення впливає осередки (параметра) до тих пір, поки формула, залежна від цієї осередки, які не поверне задане значення. В основу пошуку рішення покладено алгоритм чисельного рішення рівняння однієї або декількох змінних. За замовчуванням для знаходження параметра Excel проводить максимум 100 ітерацій (проміжних обчислень) з похибкою 0,001. Змінити ці значення можна в діалозі сервіс-параметри-закладка обчислення.

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

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

Можливе створення таблиці підстановки з однією змінною і декількома формулами або таблиці з двома змінними та однією формулою.

Приклад 1.3. Використовуючи підбір параметра, визначити для прикладу 1.1 значення ставки, при якому прибутковість буде дорівнює 8,31%. Значення інших аргументів не змінюються.

Рішення:

1. Скопіюємо приклад 1.1 на інший аркуш в осередку з такими ж адресами.

2. Виконаємо команду Сервіс-Підбір параметра і заповнимо діалог:

В даному прикладі в клітинку Н3 введена функція = дохід (А3; B3; C3; D3; E3; F3; G3). За допомогою підбору параметра необхідно встановити в цьому осередку значення 8,31%, змінюючи при цьому осередок С3, в якій зберігається шукана ставка.

3. Після натискання ОК в осередку С3 буде знайдено значення ставки a 7,42%.

Приклад 1.4. Побудувати три сценарії для прикладу 1.2 з різними значеннями накопиченої суми, кількості років і процентної ставки. Вивести звіт за сценаріями у вигляді структури c результатами обчислення початкового розміру вкладу для кожного сценарію.

 накопичено  Кількість років  ставка
 17946,0  7,4%
 12455,5  5,8%
 10336,7  8,3%

Рішення:

1. Скопіюємо приклад 1.2 на інший аркуш в осередку з такими ж адресами.

2. Виконаємо команду Сервіс-Сценарії і натиснемо кнопку Додати. У діалозі вкажемо назву сценарію і змінювані комірки. після натискання ОК в діалозі Значення осередків сценарію введемо значення накопиченої суми, кількості років і процентної ставки з першого рядка таблиці з вихідними даними:

3. При створенні другого і третього сценарію буде змінюватися його назву і значення змінних осередків. Адреси змінюваних осередків будуть ті ж (B9: D9). Для зміни вже створених сценаріїв в диспетчері сценаріїв є кнопка змінити.

4. За допомогою кнопки звіт виведемо структуру створених сценаріїв, вказавши осередок результату a Е9. після натискання ОК буде створено новий лист із структурою сценарію:

Приклад 1.5. Проаналізувати величини позики, яку планується взяти на 10 років з щоквартальною виплатою по 100000, для різних процентних ставок (8% -20%).

Рішення:

1. Ввести вихідні дані, формулу і діапазон зміни ставки:

2. Виділити діапазон В1: С14 і виконати команду Дані-Таблиця підстановки;

3. У діалозі в поле Підставляти значення по рядках в вказати посилання на осередок А2. Після натискання ОК осередку С2: С14 будуть заповнені значеннями позики.

Приклад 1.6.У приклад 1.5 додається змінна. Виплата змінюється від 100000 до 500000 з кроком 50000.

Рішення:

1. Перемістити формулу в В1, осередки С1: К1 заповнити поруч виплат.

2. Виділити діапазон В1: К14 і виконати команду Дані-Таблиця підстановки;

3. У діалозі в поле Підставляти значення по рядках в вказати посилання на осередок А2, а в поле Підставляти значення за стовпцями в вказати посилання на комірки А4. Після натискання ОК осередку С2: К14 будуть заповнені значеннями позики.

завдання:

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

 Дата угоди  Дата вступу в силу  ставка  Ціна  погашення  частота  базис
 01.09.2006  01.11.2011  11,50%  96,2725

2.Визначити суму, отриману до терміну погашення повністю забезпечених цінних паперів.

 Дата угоди  Дата вступу в силу  інвестиція  знижка  базис
 01.08.2003  01.02.2011  6,75%

3.Ви збираєтеся вкладати по 164 руб. на початку кожного кварталу протягом 13 років при річній ставці 2%. Скільки грошей буде на рахунку через 13 років?

4.Визначити початковий розмір вкладу, якщо при ставці 5% річних з щоквартальним нарахуванням відсотків через 4 роки на рахунку стало 10979,01 рублів.

5.Ви берете в борг 220000 руб. під річну ставку 5% та збираєтеся виплачувати по 3300 руб. в місяць. Скільки років займуть ці виплати?

6.Визначте річну відсоткову ставку для 12-річної позики 3100 руб. з виплатою в 30 рублів в кінці кожного місяця.

7.Розрахуйте величину періодичних виплат, які повинні проводиться на початку кожного місяця, для 11-річної позики розміром 350000 руб. з річною ставкою 9%.

8.Побудувати три сценарії для завдання 6 з різними значеннями позики (P), кількості років його виплати (n) і величини періодичних виплат (А). Вивести звіт за сценаріями у вигляді структури c результатами обчислення ставки для кожного сценарію.

P n A

9.Використовуючи підбір параметра, визначити в завданню 1значення ціни, при якому прибутковість буде дорівнює 13,70%. Значення інших аргументів не змінюються.

10.Використовуючи підбір параметра, визначити в завданню 2 значення інвестиції, при якому отримується сума буде дорівнює 16200. Значення інших аргументів не змінюються.

11.На базі вихідних даних завдання 3 за допомогою таблиці підстановки проаналізувати значення накопиченої суми при різних процентних ставках (2% a 2,5% a ... a 7%).

12.На базі вихідних даних завдання 7 за допомогою таблиці підстановки проаналізувати значення періодичних виплат при різних процентних ставках (9% a 9,5% a ... a 14%) і різних значеннях позики (350000 a 400000 a ... a 800000).

Контрольні питання:

1. Перерахуйте основні функції для розрахунку за вкладами, позиками, позиками. Що повертає кожна з цих функцій?

2. Назвіть правила заповнення аргументів функцій для розрахунку за вкладами, позиках і позиками.

3. Які інструменти включає в себе аналіз «що-якщо»?

4. Дайте характеристику одного з інструментів аналізу «що-якщо» (за вказівкою викладача).

Лабораторна робота № 2

Пошук оптимальних рішень в економічних задачах

Мета роботи: вивчити методи вирішення завдань з використанням надбудови «Пошук рішення», навчитися проводити аналіз оптимального рішення.

Методичні рекомендації

процедура пошуку рішення, Реалізована в Excel, дозволяє вирішити систему рівнянь і нерівностей з заданим критерієм оптимізації.

Завдання має оптимальне рішення, якщо: 1) вона має безліч допустимих рішень, т. Е. Рішень, що задовольняють всім обмеженням і граничним умовам, і 2) критерій, за яким з допустимих вибирається оптимальне рішення.

Загальний випадок математичної моделі для задачі оптимізації можна записати в такий спосіб:

 ЦФ: F = f (xj) a max (min, Const)

ОГР: ai ? gi (xj) ? bi

ГРУ: dj ? xj ? Dj

i = 1, m; j = 1, n

У даній системі:

- х1, х2, ... хj - шукані змінні, n - Кількість змінних, m - Кількість обмежень.

- ЦФ - цільова функція або критерій оптимізації, показує в якому сенсі рішення повинно бути оптимальним. Можливі 3 види призначення цільової функції: максимізація, мінімізація, призначення заданого значення.

- ОГР - обмеження встановлюють залежності між змінними.

- ГРУ - граничні умови показують, в яких межах можуть бути значення шуканих змінних в оптимальному рішенні.

Без критерію система, в якій n > m, Має безліч допустимих рішень. З них вибирається оптимальне рішення, яке задовольняє заданому критерію.

Існують різні класи задач оптимізації, які вимагають різних методів вирішення. Класифікація проводиться по різних елементів математичної моделі: за вихідними даними (детерміновані і випадкові), по шуканим змінним (безперервні і дискретні), за видами залежностей між ними. Якщо в завданні залежності між змінними в цільової функції і обмежень лінійні, т. Е. Змінні входять в першого ступеня і з ними виконуються дії додавання і віднімання, то модель такої задачі є лінійною. Якщо є хоча б одна нелінійна залежність, то модель - нелінійна.

Приклад 2.1. Підприємство випускає 3 види продукції, витрачаючи при цьому 4 типи ресурсів. Витрата кожного ресурсу на виробництво одиниці продукції і питома прибуток становлять:

   Продукція 1  Продукція 2  Продукція 3
 ресурс 1  4,5  2,6  2,4
 ресурс 2  6,2  4,2  4,0
 ресурс 3  5,3  3,8  3,5
 ресурс 4  3,1  2,0  1,8
 Питомий прибуток  93,0  55,4  58,0

У розпорядженні підприємства є 5100 ресурсу 1, 8200 ресурсу 2, 7000 ресурсу 3, 3250 ресурсу 4.

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

Рішення:

  1. Внесемо вихідні дані і формули в комірки робочого листа:

У цьому завданню шуканими змінними будуть обсяги випуску трьох видів продукції і додаткові ресурси чотирьох видів, тому резервуємо 7 змінюваних осередків В2: Н2. В осередку I2 записана формула загальної суми додаткових ресурсів, яка буде минимизируемой цільовою функцією. В осередку I3 - формула прибутку. В осередках I5: I8 - формули, що обчислюють витрата ресурсів. Для зручності введення формул і наочності результату в осередку E5, F6, G7, H8 внесені -1, т. Е. Додатковий ресурс віднімається з наявного.

2. Заповнимо діалог пошук рішення:

Обмеження додаються за допомогою діалогового вікна, що викликається кнопкою Додати:

Щоб змінити або видалити вже наявне обмеження, потрібно його виділити і натиснути відповідну кнопку в вікні пошук рішення.

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

  1. Після натискання кнопки виконати отримаємо оптимальне рішення:

З отриманого рішення видно, що продукцію 2 випускати не вигідно, продукції 1 і 3 повинні бути випущені в обсязі 206,3 і 1738,1. При цьому необхідно додатково мати 31,7 ресурсу 2, 177 ресурсу 3 і 518,3 ресурсу 4.

Приклад 2.2. Потрібно виготовити усічений конус обсягом V ? 35 л. причому r може змінюватися в межах [1; 2].

Визначити оптимальні розміри R, r, h, При яких довжина зварного шва L буде мінімальною.

Рішення:

  1. Внесемо вихідні дані і формули в комірки робочого листа:
  A B
R
r
h
V  = (Пі () / 3) * B3 * (B1 ^ 2 + B1 * B2 + B2 ^ 2)
L  = 2 * Пі () * B1 + 2 * Пі () * B2 + корінь (B3 ^ 2 + (B1-B2) ^ 2)

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

  1. заповнимо діалог пошук рішенняі після натискання кнопки виконати отримаємо оптимальне рішення:

завдання:

I. Завдання оптимізації розподілу ресурсів і її варіантний аналіз

Завдання 1. Комбінат освоює випуск нових артикулів тканин.

У розпорядженні є наступні ресурси:

 сировина
 устаткування
 трудові

На виробництво одного метра тканини кожного артикулу витрачається (питома витрата):

   Артикул А  Артикул В  Артикул C  Артикул D
 сировина
 устаткування  165,5
 трудові

Прибуток від реалізації одного метра тканини становить (питома прибуток):

 Артикул А  Артикул В  Артикул C  Артикул D
 87,5

Знайти оптимальний план випуску тканин кожного артикулу, при якому прибуток комбінату буде максимальною.

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

Завдання 2. Зробити копію листа з рішенням завдання 1. На скопійованому аркуші відкрити діалог пошук рішення, Додати обмеження целочисленности змінних, провести пошук рішення, вивести всі доступні звіти. Порівняти отримані результати з результатами першого завдання і зробити висновок.

Завдання 3. Зробити копію листа з рішенням завдання 1. На скопійованому аркуші знайти оптимальний план випуску тканин кожного артикулу, при якому прибуток комбінату буде рівною 40 000.

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

Завдання 4. Зробити копію листа з рішенням завдання 3. На скопійованому аркуші знайти оптимальний план випуску тканин кожного артикулу і мінімальні додаткові ресурси, необхідні для отримання прибутку 40 000. Порівняти результати рішення задач 3 и 4.

II. Оптимізація виробничого плану

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

 Верстати  Деталь А, шт / год  Деталь В, шт / год  Вартість години верстатного часу  Робочий фонд часу
 токарний
 свердлильний
 шліфувальний  17,5

Вартість матеріалу, що витрачається для деталі А становить 2 у. е., для деталі В - 3 у. е. Відпускна ціна деталі А дорівнює 5 у. е., деталі В - 6 у. е.

Знайти план випуску продукції, що дає максимальний прибуток.

Вказівки щодо виконання:

1. Визначити час обробки однієї деталі на кожному верстаті

2. Обчислити витрати на обробку одиниці деталі кожного типу

3. Обчислити питомий прибуток для кожного типу деталей

4. Зарезервувати осередки для кількості штук деталей кожного типу

5. Ввести формулу загального прибутку фірми

6. Ввести формули для обчислення фактичного часу роботи кожного верстата

7. Заповнити діалог Сервіс aПоіск рішення і виконати пошук.

III. транспортна задача

Підприємство має філії в декількох містах. Витрати на перевезення одиниці продукції з філій споживачам складають:

 ПотребітеліФіліали  Мінськ  Брест  Гродно  Ліда
 Вітебськ
 Могильов
 Гомель
 Полоцьк
 Мозир

Для задоволення потреб споживачів необхідно доставити таку кількість продукції:

 Мінськ  Брест  Гродно  Ліда

Виробничі потужності філій дозволяють виробляти не більше зазначеної кількості продукції:

 Вітебськ
 Могильов
 Гомель
 Полоцьк
 Мозир

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

Вказівки щодо виконання:

  1. Зарезервувати осередки для обсягів перевезень. Так як кожна філія може поставляти продукцію декільком споживачам, то для плану перевезень потрібен діапазон 4ст х5Стор.
  2. Ввести формули підсумовування обсягів перевезень в кожному рядку. Сенс цих формул - кількість продукції в кожній філії.
  3. Ввести формули підсумовування обсягів перевезень в кожному стовпці. Сенс цих формул - потреби споживачів.
  4. Ввести формулу витрат на перевезення необхідного споживачам обсягу продукції з філій підприємства.
  5. Заповнити діалог пошук рішення і виконати пошук. Проаналізувати отримані результати.

 



Мета роботи: віконаті дослідження математичної Функції, что опісує стан складного об'єкта або системи. | IV. Управління оборотним капіталом
© um.co.ua - учбові матеріали та реферати