Дата конвертації12.04.2017
Розмір42.29 Kb.
Типконтрольна робота

Скачати 42.29 Kb.

Використання електронних таблиць MS EXCEL для вирішення економічних завдань. Фінансовий аналіз в Excel

2

Лист-завдання

Частина І. Використання електронних таблиць MS EXCEL для вирішення економічних завдань

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

Порядок виконання:

1. Відповідно до варіанту створити на робочому аркуші таблицю

2. Ввести необхідні в завданні дані для розрахунку

3. Відформатувати таблицю (виділити напівжирним шрифтом заголовок таблиці, підсумкові показники і т.д.)

4. Провести розрахунки:

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

ь за допомогою копіювання заповнити наступні елементи таблиці;

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

5. Побудувати діаграми:

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

ь кругову за даними одного стовпчика (або одного рядка)

У пояснювальній записці до Завданням №1 контрольної роботи вказати:

1. Таблицю з вихідними даними (варіант завдання)

2. Формули excel з посиланнями на комірки, за якими проводиться розрахунок

3. Таблицю з результатами розрахунків

4. Етапи побудови діаграм

5. Побудовані діаграми

Завдання № 2. Підведення динамічних підсумків з використанням зведених таблиць

Порядок виконання

1. Відповідно до варіанту створити на робочому аркуші вихідну таблицю

2. Побудувати зведену таблицю (виділити вихідну таблицю і вибрати пункт меню «данниесводная таблиця»)

3. Змінити структуру зведеної таблиці (вибравши пункт «макет» майстра створення зведених таблиць)

У пояснювальній записці до Завданням №2 контрольної роботи вказати:

1. Вихідну таблицю (варіант завдання)

2. Етапи побудови зведеної таблиці (в обов'язковому порядку макет зведеної таблиці)

3. Отриману зведену таблицю (в обов'язковому порядку макет зміненої зведеної таблиці)

4. Порядок зміни структури зведеної таблиці

5. Змінену зведену таблицю

Завдання № 3. Використання процедури «Пошук рішення»

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

Порядок виконання:

1. На робочому аркуші створити таблицю з даними про товари, відповідно до варіанту, і провести розрахунок за наведеними формулами

2. Запустити на комп'ютері процедуру «Пошук рішення» (меню сервіс Пошук рішення)

3. У діалоговому вікні вказати необхідні посилання:

ь цільова осередок - сумарний прибуток (максимальне значення). Цей осередок повинна містити формулу

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

ь ПРИМІТКА: цей діапазон використовується для отримання формул в цільовій комірці і осередках обмежень;

ь обмеження - кількість товарів цілі і позитивні, сумарні витрати і загальна кількість товарів не повинні перевищувати граничних значень.

4. Виконати процедуру «Пошук рішення»

У пояснювальній записці до Завданням №3 контрольної роботи вказати:

таблицю з даними про товари

провести розрахунок за формулами

вказати параметри процедури «Пошук рішення» з адресами осередків (діалогове вікно процедури Пошук рішення)

таблицю з результатами виконаної процедури «Пошук рішення»

Завдання № 4. Регресійний аналіз даних

Знайти рівняння емпіричної залежності у вигляді: Y = a X + b і побудувати її графік за даними таблиці

Порядок виконання:

1. На робочому аркуші створити таблицю відповідно до варіанту

2. Побудувати точкову діаграму

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

У пояснювальній записці до Завданням №4 контрольної роботи вказати:

1. Вихідну таблицю (варіант завдання)

2. діаграму і етапи її побудови

3. Додавання лінії тренду

4. Рівняння лінії тренду

Частина ІІ. Фінансовий аналіз в Excel

завдання №1

Відомий розмір вкладу, який поміщений на певний термін під заданий відсоток. Обчислити коефіцієнт нарощення і суму виплат в кінці періоду.

Завдання № 2

Використовуючи функцію "Підбір параметра" створити систему для нарахування складних відсотків при заданій сумі виплати.

Завдання №3. Таблиці підстановки з однією змінною

Використовуючи функцію "Таблиці підстановки" створити систему для прогнозування суми виплати при варіації процентної ставки.

Завдання № 4. Таблиці даних з двома змінними

Використовуючи функцію "Таблиці підстановки" створити систему для прогнозування суми виплати при варіації процентної ставки і суми вкладу.

Пояснювальна записка

Частина І. Використання електронних таблиць MS EXCEL для вирішення економічних завдань

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

1. Варіант 8. Облік витрат на допоміжні матеріали

найменування матеріалу

Ціна (Ц)

кількість

Сума на кінець місяця (С)

Частка в загальному обсязі (Д)

На початок місяця (НМ)

На кінець місяця (КМ)

Витрачено (І)

фарба

12,50

90

50

лак

28,30

60

10

бензин

2,00

140

60

гас

1,80

90

30

емаль

16,30

75

80

ацетон

11,80

60

15

Разом

Х

Х

Х

Х

Середня сума витрат

Х

Мінімальна частка в загальному обсязі

І = КМ-НМ С = Ц * КМ Д = С / (Разом З) * 100

2. Розрахунки в таблиці проводилися за такими формулами Еxcel з посиланнями на комірки:

- Витрачено (І): Е6 = D6-C6;

- Сума на кінець місяця (С): F6 = B6 * D6;

- Частка в загальному обсязі (Д): G6 = (B6 / F6) * 100;

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

- виберемо пункт меню Вставка - Функція, відкриється вікно «Майстер функцій» (рис.1).

Малюнок 1. - Майстер функцій

- зі списку функцій виберемо СРЗНАЧ і натиснемо кнопку «Ок». В поле «Число 1» вкажемо діапазон комірок, серед значень яких потрібно знайти середнє. Формула буде мати вигляд: G13 = СРЗНАЧ (F6: F11)

Аналогічно розрахуємо мінімальну частку в загальному обсязі, скористайтесь функцією МІН. Формула буде мати вигляд: G14 = = МІН (G6: G11)

3. Таблиця з результатами розрахунків:

найменування матеріалу

Ціна (Ц)

кількість

Сума на кінець місяця (С)

Частка в загальному обсязі (Д)

На початок місяця (НМ)

На кінець місяця (КМ)

Витрачено (І)

фарба

12,5

90

50

-40

625

2,00

лак

28,3

60

10

-50

283

10,00

бензин

2

140

60

-80

120

1,67

гас

1,8

90

30

-60

54

3,33

емаль

16,3

75

80

5

1304

1,25

ацетон

11,8

60

15

-45

177

6,67

Разом

Х

Х

Х

Х

2563

Середня сума витрат

427,17

Мінімальна частка в загальному обсязі

1,25

4.Етапи побудови діаграм

Для побудови діаграми виконаємо наступні дії:

- вкажемо діапазон комірок (F5: G10), за якими потрібно побудувати діаграму, виберемо пункт Вставка - Діаграма Відкриється вікно «Майстер діаграм» (рис.2);

Малюнок 2. - Майстер діаграм

- виберемо тип Гістограма і натиснемо кнопку Далі. У закладці Ряд у полі Ряд напишемо назви рядів 1 і 2 і натиснемо кнопку Готово;

- натиснемо праву кнопку миші, утримуючи курсор миші наведеним на простроенной діаграму;

- з пунктів меню виберемо Параметри діаграми, відкриється вікно (рис.3);

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

Малюнок 3. - Параметри діаграми

5. Побудована гістограма буде мати вигляд:

Малюнок 4. - Гістограма

Для побудови кругової гістограми виберемо пункт меню Вставка - Діаграма і тип Кругова, після чого натиснемо кнопку Готово. Аналогічним чином вкажемо назву кругової діаграми (рис.5).

Малюнок 5. - Кругова діаграма

Завдання №2. Підведення динамічних підсумків з використанням зведених таблиць

1. Варіант 8.

Обладнання, що поставляється виріб

Тип транспорту

відстань

Вартість перевезення

нафта

морський

1000-5000

14000

Зерно

ж / д

до 1000

7000

агрегат

повітря

понад 5000

3100

нафта

ж / д

до 1000

10000

Зерно

морський

1000-5000

5400

агрегат

повітря

понад 5000

15600

2. Етапи побудови зведеної таблиці:

- виберемо пункт Дані - Зведена таблиця. З'явиться вікно Майстер зведених таблиць;

- вкажемо діапазон, що містить вихідні дані з таблиці (B4: D9);

- вкажемо «Помістити таблицю в новий лист» і натиснемо кнопку Макет.

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

Малюнок 6. - Макет зведеної таблиці

3. Макет зміненої зведеної таблиці (рис.7):

Малюнок 7. - Макет зміненої зведеної таблиці

4. Структуру зведеної таблиці змінимо шляхом перетягування мишкою полів таблиці, які розташовуються праворуч, в потрібні області діаграми. Потім натиснемо кнопку «Ок».

5. Змінена зведена таблиця

Сума по полю Вартість перевезення

Обладнання, що поставляється виріб

відстань

агрегат

агрегат

всього

Зерно

Зерно Всього

нафта

нафта Всього

Загальний підсумок

Тип транспорту

понад 5000

1000-5000

до 1000

1000-5000

до 1000

повітря

18700

18700

18700

ж / д

7000

7000

10000

10000

17000

морський

5400

5400

14000

14000

19400

Загальний підсумок

18700

18700

5400

7000

12400

14000

10000

24000

55100

Завдання № 3. Використання процедури «Пошук рішення»

1. Варіант 8.

Найменування

Витрати, гр / шт. (Р)

Кількість, шт. (К)

Всього витрат, гр. (ВР)

Відсоток прибутку (ПП)

Прибуток (П)

товар 1

780

6200

9%

товар 2

3200

500

22%

товар 3

160

3800

15%

товар 4

1100

9100

13%

товар 5

4500

800

33%

товар 6

200

5600

23%

Разом

Х

Х

граничні значення

36000

2700000

Х

Х

ВР = В * К П = ПП * ВР

2. Для розрахунку в таблиці значень «Всього витрат» використовувалася формула: = B4 * C4 і далі аналогічно по іншим видам товарів. Для розрахунку в таблиці значень «Прибуток» використовувалася формула: = E4 * D4 і далі аналогічно по іншим видам товарів. Для розрахунку сумарних значень кількості товарів, витрат і прибутку використовувалася функція СУММ (): Загальна кількість товарів: = СУММ (C4: C9).

3. Для визначення оптимального плану виробництва виберемо пункт Сервіс - Пошук рішення і в діалоговому вікні вкажемо необхідні посилання (рис.8).

Малюнок 8. - Пошук рішення

4. Таблиця з результатами виконаної процедури «Пошук рішення»

Найменування

Витрати, гр / шт. (Р)

Кількість, шт. (К)

Всього витрат, гр. (ВР)

Відсоток прибутку (ПП)

Прибуток (П)

товар 1

780

0

0,00

9%

0,00

товар 2

3200

0

0,00

22%

0,00

товар 3

160

0

0,00

15%

0,00

товар 4

1100

0

0,00

13%

0,00

товар 5

4500

600

2700000,00

33%

891000,00

товар 6

200

0

0,00

23%

0,00

Разом

Х

600

2700000

Х

891000

граничні значення

36000

2700000

Х

Х

Завдання № 4. Регресійний аналіз даних

1. Варіант 8. Вид функції: z1 = f (x1)

вихідна таблиця

X 1

70

72

75

68

68

71

69

71

69

68

68

69

75

83

73

71

82

69

73

73

72

Z 1

471

492

506

464

457

478

475

490

480

457

470

468

515

578

508

493

556

463

497

502

498

Рівняння емпіричної залежності виду y = ax + b для функції z1 = f (x1) вирішимо методом найменших квадратів.

Формули для оцінок параметрів мають такий вигляд:

; де; ;

,

заповнимо таблицю

i

X 1

Z 1

2

1

70

471

+221841

32970

2

72

492

242064

35424

3

75

506

256036

37950

4

68

464

215296

31552

5

68

457

208849

31076

6

71

478

228484

33938

7

69

475

225625

32775

8

71

490

240100

34790

9

69

480

230400

33120

10

68

457

208849

31076

11

68

470

220900

31960

12

69

468

219024

32292

13

75

515

265225

38625

14

83

578

334084

47974

15

73

508

258064

37084

16

71

493

243049

35003

17

82

556

309136

45592

18

69

463

214369

31947

19

73

497

247009

36281

20

73

502

252004

36646

21

72

498

248004

35856

1509

10318

5088412

743931

;

;

Таким чином, шукана емпірична формула має вигляд z = 7,99x + 0,13.

2. Побудуємо діаграму для функції z = 7,99x + 0,13:

- виділимо діапазон значень функції (G4: p4) виберемо пункт меню Вставка - Діаграма;

- виберемо тип Точкова і натиснемо кнопку Готово

3. Додамо лінію тренда:

- виберемо курсором миші лінію точок функції і натиснемо праву кнопку і виберемо пункт Додати лінію тренда (рис. 9);

Малюнок 9. - Додавання лінії тренду

- виберемо закладку Параметри і встановимо прапорець на полі «Показати рівняння на діаграмі».

4. Діаграма має вигляд:

Малюнок 10. - Додавання лінії тренду

Частина ІІ. Фінансовий аналіз в Excel

Завдання №1.

варіант 8

Розмір внеску

термін вкладу

Процентна ставка

8

212600

6

6,5

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

Записуємо заголовки стовпців в осередку А1, А2 і А3. У осередок В1 записуємо розмір суми вкладу, в клітинку В2 - термін вкладу, в клітинку В3 - процентна ставка, в клітинку В4 - формулу для розрахунку коефіцієнта нарощення, в комірку В5 формулу для розрахунку суми виплат через 6 років: = БЗ (B3; B2 ; 0; -B1; 0).

Коефіцієнт нарощення можна розрахувати так: = В5 / В1, де в комірці В1 - початкова сума, в осередку В5 - формула = БЗ (B3; B2; 0; -B1; 0).

Таблиця з даними і з формулами:

значення:

Вид формул:

Розмір внеску

212600

212600

термін вкладу

6

6

Процентна ставка

6,5%

0,065

коефіцієнт нарощення

1,459142

= B5 / B1

сума виплати

310 213,65 грн.

= БЗ (B3; B2; 0; -B1; 0)

Завдання № 2. Варіант 8

Розмір внеску

сума вкладу

Процентна ставка

8

21500

368

9,8%

Для побудови системи можна використовувати функцію ППЛАТ (PMT).

Потрібно накопичити 21500 грн., Накопичуючи постійну суму щомісяця, за допомогою цієї функції можна визначити розмір відкладаються сум. Змінна осередок - осередок з кількістю років, використовуємо функцію ППЛАТ, щоб визначити при процентній ставці 9,8% при певній сумі виплат - в кінці якого періоду буде підсумкова сума - 21500. За щомісячні відрахування - візьмемо 368 грн.

Для вирішення даного завдання можна скористатися фінансовою функцією ППЛАТ (PMT). Створюємо таблицю з наступною структурою:

Розмір внеску

21500

Розмір внеску

21500

термін вкладу

3

термін вкладу

3

Процентна ставка

9,80%

процентна ставка

0,098

сума виплати

516,14 грн.

сума виплати

= ППЛАТ (B3 / 12; B2 * 12; 0; -B1)

Запускаємо програму Підбір параметра через меню Сервіс. Змінна осередок - термін вкладу, тобто В2, в комірці В4 повинні отримати результат - 200.

Виникає повідомлення:

Шукане значення терміну вкладу - 6 років - при щомісячному відрахування 200 грн через 6 років на рахунку буде 21500 грн.

Таблиця після виконання програми Підбір параметра:

Розмір внеску

21500

термін вкладу

6,4564557

Процентна ставка

9,8%

сума виплати

200,00 грн.

Завдання № 3. Таблиці підстановки з однією змінною. варіант 8

Розмір внеску

термін вкладу

Процентна ставка

8

180800

6

6,0%

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

"Таблиця даних" оперує однієї або двома величинами одночасно.

Запишемо вихідні дані і формулу для розрахунку суми виплат, як і в першому завданні. Потім створюємо таблицю з даними. Для цього пишемо заголовки стовпців, потім - в комірці С2 записуємо формулу = В3, в клітинку В8-формули = В4, в клітинку С8 - формулу = В5. Для розрахунку відсотка від 6% до 11% з кроком 0,5% записуємо формулу = A8 + 0,005 і копіюємо її вниз по стовпчику, поки не отримаємо значення 11%. Потім виділяємо діапазон комірок A8: С10 клацаємо на пункті меню Дані> Таблиця підстановки і в віконці Підставляти значення по рядках в записуємо адреса комірки з процентною ставкою:

Після натискання на ОК отримаємо наступну таблицю:

Розмір

180 800,00 грн.

180800

термін вкладу

6

6%

відсоток. ставка

6%

0,06

коефіцієнт нарощення

1,418519112

= B5 / B1

сума виплати

256 468,26 грн.

= БЗ (B3; B2; 0; -B1; 0)

відсоток. ставка

Переходи.

сума виплати

6,0%

1,42

256 468,26 грн.

6,5%

1,46

263 812,93 грн.

7,0%

1,50

271 332,05 грн.

7,5%

1,54

279 028,92 грн.

8,0%

1,59

286 906,88 грн.

8,5%

1,63

294 969,33 грн.

9,0%

1,68

303 219,70 грн.

9,5%

1,72

311 661,49 грн.

10,0%

1,77

320 298,23 грн.

10,5%

1,82

329 133,50 грн.

11,0%

1,87

338 170,95 грн.

Завдання № 4. Таблиці даних з двома змінними

варіант 8

Розмір внеску

термін вкладу

Процентна ставка

8

152567

6

6,0%

Для створення процентних ставок, тобто для створення таблиці даних для розрахунку суми виплат по заданому відсотку і заданого терміну вкладу потрібно по стовпцю проставити значення відсотків в потрібному діапазоні, а по рядку - значення термінів вкладу - це будуть змінні величини, потім на перетині рядка і стовпця записати = В5 де в осередку В5 записана формула для розрахунку Суми виплати:

Виділяємо прямокутну область починаючи з формули розрахунку суми виплат - А7: Е18 і клацаємо на пункті меню Дані> таблиця підстановки.

Проставляємо адреси осередків і натискаємо ОК.

Отримаємо результат у вигляді таблиці даних:

Розмір

152567

152567

термін

6

6

відсоток

6%

0,06

коефіцієнт нарощення

1,42 грн.

= B5 / B1

сума виплати

216 419,21 грн.

= БЗ (B3; B2; 0; -B1; 0)

216 419,21 грн.

2

3

5

7

6,0%

171424,3

181709,7

204169,1

229404,4

6,1%

171747,9

182224,5

205133,9

230923,6

6,2%

172071,8

182740,2

206102,5

232451,4

6,3%

172396,0

183256,9

207074,6

233987,9

6,4%

172720,5

183774,6

208050,5

235533,1

6,5%

173045,3

184293,3

209030,0

237087,1

6,6%

173370,4

184812,9

210013,2

238649,8

6,7%

173695,9

185333,5

211000,1

240221,3

6,8%

174021,6

185855,0

211990,7

241801,7

6,9%

174347,6

186377,6

212985,1

243391,0



Головна сторінка


    Головна сторінка



Використання електронних таблиць MS EXCEL для вирішення економічних завдань. Фінансовий аналіз в Excel

Скачати 42.29 Kb.