5355

Информационная технология таблицы подстановки

Лабораторная работа

Исторические личности и представители мировой культуры

Информационная технология таблицы подстановки Цель работы: приобретение навыков решения задач анализа с ограниченным числом одновременно изменяемых параметров модели средствами MSExcel. Краткая теория На практике часто возникает необходимость ...

Русский

2012-12-08

104 KB

60 чел.

Информационная технология таблицы подстановки

Цель работы: приобретение навыков решения задач анализа с ограниченным числом одновременно изменяемых параметров модели средствами MS Excel.

Краткая теория

На практике часто возникает необходимость оценить насколько велики должны быть изменения в исходных данных, чтобы возникли ощутимые изменения в результате, т.е. провести  так называемый анализ чувствительности.

Для решения задач анализа чувствительности типа «ЧТО ЕСЛИ» используется информационная технология таблицы подстановки.

Таблица подстановки используется для определения степени влияния значений одного параметра на результат одной или нескольких функций, либо двух параметров – на результат одной функции. Анализ чувствительности производится с использованием таблиц подстановки (меню Данные/ Таблица подстановки).

Таблица подстановок для одного параметра может использоваться для оценки степени влияния одного параметра для различных функций. В этом случае на рабочем листе  MS Excel создается массив значений параметра в виде вектора – строки или вектора – столбца. Для вычислений значений функций создается массив формул, использующих этот параметр.

Для оценки влияния двух параметров на результат, возвращаемый функцией, используется таблица подстановок для двух параметров. На листе рабочей книги создают два массива значений параметра, один из которых – вектор–строка, а другой, смежный с ним, -вектор-столбец. В свободной ячейке на пересечении строки и столбца с данными вводится формула функции, использующей эти параметры.

Задание 1

Порядок выполнения работы

Рассчитайте размер возвращаемой суммы для краткосрочной ссуды 30000 руб. сроком на один год под 18% годовых. Для расчета воспользуйтесь формулой наращения по сложным процентам

S =P(1+i)n

где S – наращенная сумма;

Р –размер ссуды;

i – годовая ставка;

n – срок ссуды.

Определите степень влияния процентной ставки на размер возвращаемой суммы.

Порядок выполнения задания:

  1.  Введите исходные данные и формулу для расчета возвращаемой суммы на рабочем листе MS Excel (рисунок 1).

A

B

C

1

2

Расчет краткосрочной ссуды

3

4

Ссуда

30000

5

Дата выдачи ссуды

01.01.2004

6

Дата возврата ссуды

01.01.2005

7

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

18,00%

8

Возвращаемая сумма

=B4*(1+B7/100)^((B6-B5)/365)

9

Рисунок 1– Расчет краткосрочной ссуды

  1.  Теперь рассчитайте то же значение, но при разных процентных ставках.

Для этого необходимо провести анализ чувствительности размера суммы, подлежащей выплаты в зависимости от процентной ставки.

Создайте вектор – столбец, содержащий значения изменяемого параметра (рисунок 2). В  смежном столбце на ячейку выше начала вектора- столбца процентных ставок (клетка  С10) введите адрес ячейки, содержащей формулу  =В8.

  1.  Выделите диапазон с вектором исходных значений процентных ставок и вектором анализируемых функций. В нашем примере это одна функция. При этом формула должна попадать в область выделения  (B10:C20).

A

B

C

10

 

=B8

11

10,00%

33000

12

11,00%

33300

13

12,00%

33600

14

13,00%

33900

15

14,00%

34200

16

15,00%

34500

17

16,00%

34800

18

17,00%

35100

19

18,00%

35400

20

19,00%

35700

21

Рисунок 2 – Таблица чувствительности с одной переменной

  1.  Воспользуйтесь таблицей подстановки для анализа чувствительности. В поле Подставлять значения по строкам укажите ячейку (щелчком мыши), содержащую процентную ставку, по которой получено рассчитанное значение. При этом появится абсолютная ссылка $B$7. Ячейка B7 – это ячейка, в которую Excel сам подставит значение, рассчитает формулу функции и поместит под ней в столбце C список результатов.
  2.  Нажмите кнопку ОК и ознакомьтесь с результатами.

Задание 2

Определите степень влияния процентной ставки и величины размера ссуды на размер возвращаемой суммы. Для этого постройте таблицу чувствительности с двумя переменными, в которой качестве вектора- столбца введите процентные ставки, а в качестве  вектора- строки – величину выплат.

Порядок выполнения задания:

Для решения задачи используем те же исходные данные, что и в предыдущем примере.  

  1.  Создайте вектор– столбец, содержащий значения первого изменяемого параметра (Ставка) и вектор –строку для второго параметра (Ссуда).В клетку  F10 введите ссылку на ячейку, содержащую формулу  =В8 (Рисунок 3)
  2.  Выделите диапазон таблицы чувствительности вместе с формулой (F10:K20).
  3.  Воспользуйтесь таблицей подстановки для анализа чувствительности. В поле Подставлять значения по строкам укажите ячейку, содержащую процентную ставку, при которой рассчитывалась формула. При этом вводится абсолютная ссылка $B$7.
  4.  В поле Подставлять значения по столбцам укажите ячейку, содержащую размер ссуды, по которой получено рассчитанное значение. При этом вводится абсолютная ссылка $B$4.
  5.  Нажмите кнопку ОК и ознакомьтесь с результатами.

E

F

G

H

I

J

K

10

 =B8

25000

35000

40000

45000

50000

11

10,00%

12

11,00%

13

12,00%

14

13,00%

15

14,00%

16

15,00%

17

16,00%

18

17,00%

19

18,00%

20

19,00%

21

Рисунок 3– Таблица чувствительности с двумя переменными

Задание к лабораторной работе

Задание 1. Необходимо накопить 40 000 рублей за три года, откладывая постоянную сумму в конце каждого месяца. Какой должна быть эта сумма, если норма процента по вкладу составляет 12% годовых. Провести анализ чувствительности размера ежемесячного вклада в зависимости от нормы процента. Рассмотреть диапазон от 10% до 14% с шагом 0,5%.

Порядок выполнения работы

  1.  Изучить краткую теорию
  2.  Создать на рабочем листе диапазон с исходными данными для решения задачи. Обратите внимание на формат ячеек. Для расчета следует использовать встроенную финансовую функцию ППЛАТ(). (См. Лаб. раб. №3).
  3.  Создать таблицу подстановки с одной переменной для вычисления размера ежемесячных платежей при норме процента, изменяющейся от 10% годовых до 14% с шагом 0,5%. Рабочий лист после выполнения этого пункта может выглядеть следующим образом:

Будущая стоимость

40 000,00р.

Норма процента

12%

годовых

Количество периодов

3

года

Ежемесячные платежи

-928,57р.

Текущая сумма вклада

0

Тип

0

-928,57р.

10%

-957,35р.

10,50%

-950,10р.

11%

-942,88р.

11,50%

-935,71р.

12%

-928,57р.

12,50%

-921,48р.

13%

-914,42р.

13,50%

-907,41р.

14%

-900,44р.

Рисунок 4– Пример решения

  1.  Провести форматирование таблицы
  2.  Объяснить полученные результаты
  3.  Построить диаграмму зависимости размера ежемесячных платежей от нормы процента.

Задание 2. Необходимо накопить 40 000 рублей за пять лет, откладывая постоянную сумму в конце каждого месяца. Какой должна быть эта сумма, если норма процента по вкладу составляет 12% годовых. Провести анализ чувствительности размера ежемесячного вклада в зависимости от нормы процента (диапазон от 10% до 14% с шагом 0,5%) и количества периодов (диапазон от 2 до 7 лет).

Порядок выполнения работы

  1.  Скопировать рабочий лист из первой части на другой лист.
  2.  Создать таблицу подстановки с двумя переменными для вычисления размера ежемесячных платежей при изменении нормы процента и количества периодов.
  3.  Построить графики зависимостей размера ежемесячных платежей от нормы процента для периодов 1 год, 3 года и 5 лет.

Содержание отчета

  1.  Титульный лист
  2.  Таблица с результатами расчета задачи из первой части работы
  3.  Таблица с формулами, использованными при решении задачи из первой части работы. Пример таблицы с формулами.

Для отображения в ячейках вместо значений формул Выберите пункт меню Сервис/ Параметры /Вид. В параметрах окна установите флажок «Формулы».

Будущая стоимость

40000

Норма процента

0,12

годовых

Количество периодов

3

года

Ежемесячные платежи

=ППЛАТ(C2/12;C3*12;0;40000)

=C4

0,1

=ТАБЛИЦА(;C2)

0,105

=ТАБЛИЦА(;C2)

0,11

=ТАБЛИЦА(;C2)

0,115

=ТАБЛИЦА(;C2)

0,12

=ТАБЛИЦА(;C2)

0,125

=ТАБЛИЦА(;C2)

0,13

=ТАБЛИЦА(;C2)

0,135

=ТАБЛИЦА(;C2)

0,14

=ТАБЛИЦА(;C2)

  1.  Диаграмма, построенная по результатам расчета задачи из первой части работы
  2.  Таблица с результатами расчета задачи из второй части работы
  3.  Таблица с формулами, использованными при решении задачи из второй части работы
  4.  Диаграмма, построенная по результатам расчета задачи из второй части работы


 

А также другие работы, которые могут Вас заинтересовать

74204. Жер жұмыстарына арналған машиналар туралы жалпы мағлұматтар 147.63 KB
  Жұмысшы органдары мен топырақпен өзара әсерлесуі. Топырақтардың физикамеханикалық сипаттамасы Жоспар: Жер жұмыстарына арналған машиналар туралы жалпы мағлұматтар. Жұмысшы органдары мен топырақпен өзара әсерлесуі. Топырақтардың физикамеханикалық сипаттамасы.
74205. Жер қазу-тасымалдау машиналары. Қызметі, қолданылу облысы. Негізгі техника-экономикалық көрсеткіштері 659.49 KB
  Жер қазутасымалдау машиналары ЖҚТМ деп топырақты массивтен тарту күші арқылы ажыратып оны түсіру орнына өз жүрісімен жеткізетін құрылыс машиналарын атайды. Негізгі атқаратын жұмысшы операциялары: топырақты қабаттап өңдеу оны тасымалдау құрылыс объектісі негізіне төсеу немесе төгу топырақ беттерін жоспарлау. Негізгі қызметі: топырақты жер бетімен сүргіш органы арқылы азғана арақашықтыққа 150м жылжыту арқылы қабаттап өңдеу. Мына жағдайларда қолданылады: құрылыс алаңын дайындау барысында топырақтың беткі құнарлы қабатын алу;...
74206. Экскаваторлар. Жіктелуі, қолданылу облысы. Жұмысшы органының негізгі түрлері, параметрлері және құрылыс экскаваторларының индексациясы 885.5 KB
  Біршөмішті экскаватордың жұмыс циклі рет-ретімен орындалатын топырақ қазу, оны шөмішпен төсеу орнына тасымалдау, топырақты үйме мен көлік құралына аудару арқылы шөмішті босату және келесі циклді бастау үшін шөміштің алғашқы позициясына қайтып оралу операцияларынан тұрады
74207. Бұрғылау машиналары және жабдықтары. Бұрғылау құралы. Шпурлар бұрғылауға арналған машиналар. Бұрғылау-кранды машиналар 1.45 MB
  Бұрғылау – бұл топырақ массивінде қирау заттарын сыртқа шығара отырып, цилиндрлік жазықтықтар түзу арқылы топырақты қирату процесі. Егер диаметрі 75 мм дейін және тереңдігі 9 м болса жазықтықтар шпурлар деп, ал өлшемдері үлкен болса бұрғы деп аталынады.
74208. Тиеп-түсіру машиналары. Тиегіштер түрлері. Жұмыс процесі 455.42 KB
  Жұмысшы жабдық нұсқаларының көптігі және жұмыс органдарының ауыспалылығы құрылыс тиегіштерінің жұмыс жасау облысын кеңейтіп оларды құрылыс тасымалының барлық этаптарында қолданылатын универсалды машинаға айналдырады. БФПТтердің жұмысшы жабдығы жебе коромысло тартқыш гидроцилиндрлер құратын рычагты механизмнен тұрады. Сонымен қатар түсіру биіктігін ондаған сантиметрге жоғарылатытын машинаның универсалдылығын арттыратын жақты шөміштер де қолданылады бірақ олар жұмысшы жабдықтың күрделенуіне қосымша гидравликалық контурлар орнату...
74209. Машиналардың ұсақтау типтері жәнеұсақталатын материал беріктігі мен ұсақталу дәрежесіне қарай оларды таңд. 1.43 MB
  Грохоттардың қолданылуы принциптік схемалары жұмыс процестері негізгі параметрлері мен жұмыс өнімділігі Жоспар: Машиналардың ұсақтау типтері және ұсақталатын материал беріктігі мен ұсақталу дәрежесіне қарай оларды таңдап алу. Тас жыныстарды бұзу мен уатудың механикалық процесі ұсақтау деп аталады және тас ұсақтағыш машиналар –тас ұсақтағыштарды қолдана отырып ұсақтау жаншу сындыру және үйкеу көмегімен жүзеге асырылады. Ұсақтау машиналарында ұсақталатын жыныстың қасиеттеріне және ірілігіне қарай әртүрлі әдістер бірге қолданылады.
74210. Бетон қоспалары мен сылақтарын дайындауға арналған машиналар мен жабдықтар. Араластырғыш машиналардың қызметі мен құрамы. Араластырғыш машиналардың жіктелуі 1.46 MB
  Бетон араластырғыштар мен циклді және үздіксіз жұмыс жасайтын сылақ араластырғыштардың типтері негізгі параметрлері мен конструктивтік схемалары. Бетон қоспаларын тығыздау қажеттіліктері мен тәсілдері Жоспар: Араластырғыш машиналардың қызметі мен құрамы. Бетон араластырғыштар мен циклді және үздіксіз жұмыс жасайтын сылақ араластырғыштардың типтері негізгі параметрлері мен конструктивтік схемалары.
74211. Топырақ тығыздағыш машналар. Топырақты домалату, таптау және вибротаптау арқылы тығыздау 930.22 KB
  Топырақты домалату таптау және вибротаптау арқылы тығыздау. Жұмыс процесі негізгі параметрлері техникалық және эксплуатациялық көрсеткіштері Жоспар: Топырақ тығыздағыш машиналар. Топырақты домалату таптау және вибротаптау арқылы тығыздау.
74212. Қол машиналары. Жалпы мәліметтер, қол машиналарын атқаратын қызметіне, жұмыс жасау принципіне, жұмыс органының қозғалыс сипаттамасына, жұмыс режиміне, жетек түрі мен қорғаныс класына қарай жіктеу 1.34 MB
  Жалпы мәліметтер қол машиналарын атқаратын қызметіне жұмыс жасау принципіне жұмыс органының қозғалыс сипаттамасына жұмыс режиміне жетек түрі мен қорғаныс класына қарай жіктеу. Қол машиналарын атқаратын қызметіне жұмыс жасау принципіне жұмыс органының қозғалыс сипаттамасына жұмыс режиміне жетек түрі мен қорғаныс класына қарай жіктеу. Қол машиналары дегеніміз жұмыс органы қозғалысқа қозғалтқыш арқылы ал қосымша қозғалыстары жеткізу оператордың көмегімен қолдан қозғалысқа келтірілетін машиналар аталынады. Жеңіл түріне тескіш...