Создайте таблицу данных, которая вычисляет ежегодные выплаты по ссуде под закладную в 100 000 руб. для процентной ставки 5, 10, 15, 20 и 25 процента при сроках 10,15, 20 и 25 лет

Гр.730101,Потапова Е.М.

Отчет по лабораторной работе № 5 «Анализ «Что-Если» в Excel».

1. Какие типы таблиц данных можно создать с помощью команды Данные / Работа с данными / Анализ «что-если» / Таблица данных… и в чем их отличие?

Таблицы данных для одной переменной -проверяет воздействие этой переменной на несколько формул

Таблицы данных для двух переменных - проверяет их влияние на одну формулу

Предприятие оценивает возможность покупки станка. Имеются предложения по цене 200, 210, 220, 245 и 250 тыс. руб. Срок службы всех станков 10 лет, остаточная стоимость 20 тыс. руб. Определите суммы амортизационных отчислений для станков с различной стоимостью при использовании метода равномерного списания.

a) Ввести исходные данные

b) В ячейку С2 формулу АПЛ ()

c) Выполнить команду Данные/Работа с данными/ Анализ «что-если» / Таблица данных

d) Задать значение ячейки

3. У вас есть возможность вложить 10 000 руб. или 20 000 руб. в банк на 5 лет. Каждый год вы планируете помещать в банк 1000 руб. Определите накопленную сумму в конце пятого года при ставках банковского процента 5, 10, 15, 20, 25, 30 и 35%, если выплаты в конце года не производятся.

Создайте таблицу данных, которая вычисляет ежегодные выплаты по ссуде под закладную в 100 000 руб. для процентной ставки 5, 10, 15, 20 и 25 процента при сроках 10,15, 20 и 25 лет.

a) Ввести исходные данные в строки столбцы

b) В ячейку В2 ввести формулу для расчета

c) Выделить диапазон значений и выполнить команду Данные/Работа с данными/ Анализ «что-если» / Таблица данных

d) Ввести значения ячеек

5. Создайте пессимистический, оптимистический и средний вариант сценариев при следующих условиях. Предприятию необходимо составить финансовый план на год, но годовой доход точно не известен. В «худшем» варианте сценария цена одной единицы прогнозируется в размере 1 000, 1 050, 1 200, 1 300 руб. для I, II, III и IV кварталов соответственно. В «лучшем» варианте сценария – 1 100, 1 200, 1 300, 1 400 руб. для I, II, III и IV кварталов соответственно. Для среднего варианта сценария цена составит 1 050, 1 100, 1 250 и 1 350 руб. для I, II, III и IV кварталов соответственно. Объем реализации составит 1 000, 1 100, 1 200, 1 400 ед. продукции для I, II, III и IV кварталов соответственно. Необходимо рассчитать объемы финансирования подразделений предприятия и прибыль. Издержки подразделяются на постоянные и переменные. Заводоуправление требует постоянных затрат в размере 400 тыс. руб. ежеквартально, цех №1 требует постоянных затрат в размере 100 тыс. руб. ежеквартально и переменных затрат 25 руб. с каждой ед. продукции, цех №2 требует постоянных затрат в размере 200 тыс. руб. ежеквартально и переменных затрат 15 руб. с каждой ед. продукции. Ставка налога на прибыль составляет 35%.

Итоговая таблица должна выглядеть следующим образом:

Сравнение сценариев финансового плана	Текущие значения:	Наихудший вариант	Наилучший вариант	Средний вариант
Квартал




Результат:
	Валовый доход, тыс. руб.
	Итого издержек
	Валовая прибыль, руб.
	Налог на прибыль, руб.
	Чистая прибыль, руб.

Для решения задачи выполните следующие действия:

1. Создайте пессимистичный сценарий для четырех ячеек (B2:E2) (как создать сценарий показано выше). Присвойте ячейкам (B2:E2) значения 200, 205, 201, 210 соответственно.

2. Создайте оптимистичный сценарий для тех же ячеек (B2:E2). Присвойте ячейкам значения 220, 235, 250, 270 соответственно.

3. Внесите в ячейки (B3:E3) значения 10; 11; 12,1; 17 соответственно.

4. Внесите в ячейки (B5:E5) значения 600, 600, 600, 600 соответственно.

5. Внесите в ячейки формулы для расчета необходимых значений:

Формула для расчета	Ячейки	Формула
валового дохода	B4:E4	=(объем продаж)*(цена ед.)
Затрат на цех №1	B6:E6	=100+3*(объем продаж)
Затрат на цех №2	B7:E7	=350+2*(объем продаж)
итого издержек	B8:E8	=(затраты заводоуправления)+(затраты на цех №1)+(затраты на цех №2)
прибыли	B9:E9	=(валовой доход)–(итого издержек)
итого за год: объем продаж валовой доход затраты на заводоуправление затрат на цех №1 затрат на цех №2 итого издержек прибыли	F2 F4 F5 F6 F7 F8 F9	=СУММ(B2:E2) =СУММ(B4:E4) =СУММ(B5:E5) =СУММ(B6:E6) =СУММ(B7:E7) =СУММ(B8:E8) =СУММ(B9:E9)

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

При оптимистичном варианте окно будет выглядеть следующим образом.