Работа с финансови функции. Какво-ако анализ

Обективен: Научете се да работите с финансови функции на MS Excel и да извършвате анализ „какво-ако“

Финансови функции в икономическите изчисления

Предсказване с анализ какво-ако.

1 Финансови функции в икономическите изчисления

MS Excel има 50 финансови функции, които ви позволяват да извършвате най-типичните финансови транзакции.

1 Функция plt. Изчисляване на размера на месечното изплащане на заема

Пример 1 Определете месечното плащане, ако банката предоставя заем от 140 000 рубли. с погасителен план от 5 години при 8,5% годишно с месечно плащане. Последното плащане трябва да бъде 10000r.

Нека попълним таблицата на MS Excel с данни:

Фигура 5.1 - Въвеждане на функцията PMT

1 Изберете клетка B6 и щракнете върху бутона ех (Вмъкване на функция) в лентата с формули. Ще се появи Съветник за функции (фигура 5.1).

Поле Оценете - това е процентът на месец, въвеждаме B4/12, защото функцията PMT изисква месечна, а не годишна ставка.

Nper - броят на периодите на плащане, т.е. 5 години * 12 месеца, въвеждаме B5 * 12

Пс - намалената (текуща) стойност - сумата от всички плащания от текущия момент, въвеждаме B2,

Bs - бъдеща стойност, парично салдо преди последното плащане, т.е. 10000, така че въвеждаме B4,

Тип - плащане в края на месеца, така че въвеждаме 0 или нищо.

Резултат: - 3 006, 65 стр. трябва да плащате месечно, за да изплатите 130 000 рубли. за 5 години (в края на срока последното плащане е още 10 000 рубли). Знакът "-" означава, че ние плащаме, а не банката.

2 Прогнозиране с анализ какво-ако.

Анализът какво-ако позволява да се предскаже стойността на дадена функция (математическа, финансова, статистическа и т.н.), когато аргументите й се променят. Има четири начина за прогнозиране на стойности: чрез използване на таблици за търсене на данни, чрез скриптове и чрез отгатване и намиране на решение.

1 начин. Таблица за заместване на данни - това е диапазон от клетки, който показва как промяната на справочните стойности влияе върху резултата, върнат от формулата. Ако във всяка клетка е написана формула, която съдържа елементи от други клетки, промяната на стойността в която и да е или няколко клетки ще промени резултата в клетката, съдържаща формулата.

Пример 2 Например 1, дефинирайте:

- месечни плащания при лихвени проценти от 7%, 8% и 9% годишно,

- месечни плащания при лихва от 5%, 6% и 7% и размер на заема от 100 000, 110 000, 120 000 и 130 000 рубли .

За да определим плащания при ставки от 7%, 8% и 9% годишно, въвеждаме таблица с данни във формуляра (Фигура 5.2):

Фигура 5.2 - Таблица с данни

за промяна на лихвата

В клетка A9 нека направим препратка към клетката с формулата PMT, т.е. въведете знака "=" и кликнете върху клетка B6. Лентата с формули ще пише = В6, и в клетката A9 резултатът ще се появи –3 006,65 рубли.

Изберете блок от клетки A9: B12 и стартирайте инструмента за таблица с данни в раздела Данни, Група Работа с данни, бутон Какво-ако анализ, елемент от менюто Таблица с данни.

Фигура 5.3 Прозорец на таблицата с данни

Фигура 5.4 - Таблица с попълнени данни

5 Да подготвим друга справочна таблица - въведете колоните, за да промените сумата на заема (Фигура 5.5). В клетка A14 също ще направим препратка към клетката с формулата, т.е. въведете = В6, след това изберете таблицата (блок A15: E18) и стартирайте инструмента за таблица с данни в раздела Данни, Група Работа с данни, бутон Какво-ако анализ, елемент от менюто Таблица с данни. Представяме ви ВЪВ 2 за колони и В 4 за низове получаваме:

Фигура 5.5 - Таблица с данни за варирането на лихвения процент и заема

Таблицата за търсене трябва да съдържа една от клетките формула.

Пример 3 Нека форматираме варианта за заместване на данни от пример 1 под формата на скрипт.

За да създадете скрипт, изпълнете следните стъпки:

1 От раздел Данни изберете екип Какво-ако анализ, избирам Мениджър на скриптове.

2 В прозореца, който се отваря Мениджър на скриптове Натисни бутона Добави към.

3 Въведете име за скрипта. Например "Заем 200 000, 10%".

4 На полето Модифицируеми клетки задайте онези клетки (ако не са съседни, след това чрез Ctrl), които ще промените, в този случай - клетки ВЪВ 2 и В 4.

5 Въведете новите стойности за тези клетки (Фигура 5.6). Щракнете върху бутона Добре.

Фигура 5.6 Нови стойности и лихви по кредита

6 Натиснете бутона Доклад, изберете радио бутона Структура, задайте клетки за извеждане на резултата B2: B6 (използваните във формулата PMT) и натиснете Добре.

В резултат на отделен лист на MS Excel Структура на скрипта ще се появи скрипт с текущите и новите стойности на функцията PMT - Фигура 5.7.

Фигура 5.7 - Сценарий Заем 20 000, 10%

По същия начин, чрез ключа Добави към можете да създадете няколко скрипта, за да различавате различни параметри.

Метод 3. Избор на параметър. Изборът на параметър е обратната задача за решаване на уравнения. Ако е в директния проблем за функцията у = f (x) от добре познатия аргумент х се изчислява стойността на функцията в, тогава в обратната задача стойността на функцията в попита номер и количество х е избрано под зададената стойност в.

Когато е избран параметър, стойността на влияещата клетка (параметър) се променя, докато формулата в зависимост от тази клетка върне посочената стойност.

Пример 4 Състояние на пример 1. Компанията може да плаща не повече от 2500 рубли месечно. Определете какво трябва да бъде последното плащане за това.

1. Изберете клетката В 6:

2. В раздела Данни, Група Работа с данни, бутон Какво-ако анализ щракнете върху елемента от менюто Избор на параметър - Фигура 5.8. Ще се появи прозорец Избор на параметър:

Фигура 5.8 - Прозорец за избор на параметър

В прозореца Избор на параметър:

-в полето Задайте в клетка - въведе B6,

-в полето Стойност - въведете -2500

-в полето Чрез промяна на стойността на клетка - въведете В 3 (последно поле за плащане),

В резултат на това ще се появи прозорецът Резултат от избора на параметри - Фигура 5.9:

Фигура 5.9 - Резултат от прозореца при избор на параметър

По този начин, ако платите 2500 рубли. На месец последното плащане ще бъде -27716 r.

Когато избирате параметър, една от клетките трябва да съдържа формула.

4 начин. Екип Намиране на решение от раздела Данни \ Работа с данни \ Анализ какво се случва се използва за избор на няколко параметъра едновременно, за да се максимизира или минимизира съдържанието на целевата клетка и се обсъжда подробно в лабораторна работа № 7 (excel-7).

1 Как да покажа списък с функциите на MS Excel и да стартирам Съветник за функции?

2 Каква операция изпълнява функцията PMT, какво се въвежда в нейните полета Rate, Nper, PS, Bs, Type?

3 Цел и методи за анализ "какво-ако"?

4 Какво представлява „таблица с данни“, как да я създадете за едно- и двуизмерен масив?

6 Същност на операцията Избор на параметър, как се извършва?

1. Изпълнете задачата от пример 1, като промените сумата на заема на 140 000 ·н, Където н- студентски номер в дневника на учителя. Направете същото за новия размер на кредита, като промените годишната лихва от 8,5% на 5% и срока на кредита от 5 на 10 години.

2. Създайте две таблици с данни, както в пример 2, като промените размера на заема на 80 000 · n, където n е номерът на ученика в дневника на учителя.

3. Подредете под формата на скриптове всички операции от т. 1 (два скрипта + Текущи стойности) на задачата.

4. Изпълнете задачата в пример 4, като промените месечната сума на плащане на n 100.

2 Задаване на вашата опция

3 Писмени отговори на въпроси за сигурност

4 Заключения относно работата

Резултатите от работата трябва да бъдат запазени на вашия оператор