Задание 3.

После построения расчетной модели и проведения расчетов часто возникает необходимость в анализе влияния исходных данных на выходные результаты. Такие расчеты введены в качестве самостоятельных этапов в программах для разработки бизнес-планов (например, ProjectExpert) и программах анализа финансового состояния предприятия (напрмер,AuditExpert). Эти этапы называют обычно анализом чуствительности.

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

Лист Анализ безубыточности (2) переименуем в листАнализ чувствительности. В качестве опорной (базовой) точки выберем точку с объемом продаж, равном 2000 единиц. Этот столбец выделим желтым цветом (см. рис. 115г)

Рис. 115г

Скопируем ячейки F4:F14 в ячейкиB4:H14 (см. рис. 115д)

Рис. 115д

Будем изменять сначала объем продаж в пределах от -15% до +15% от базового значения в 2000 единиц и посмотрим, в каких пределах будет изменяться прибыль. Расчеты приведены на рис. 115е.

Рис. 115е

В ячейках D4, C4, B4, F4, G4, H4введены соответственно формулыE4*0.95, E4*0.9, E4*0.85, E4*1.05, E4*1.1, E4*1.15.Изменения прибыли в таблице приведены в абсолютных единицах, а нам необходимо получить результаты в относительных единицах, в % от базового значения прибыли. Для этого присвоим ячейкеЕ14 имябазовое_значениеи в ячейкуD15 введем формулу(D14-базовое_значение)/базовое_значение*100. Скопируем эту формулу в ячейкиB15, C15, F15, G15, H15.Результаты приведены на рис. 115ж.

Рис. 115ж

То есть, изменение входной величины q в пределах от -15% до +15% приводит к изменению выходной величины – прибыли- в пределах от -65% до +65 %.

Такой же анализ необходимо провести для входных величин p, Км, Кзп.

Практическое занятие 3. Работа с встроенными функциями.

Задание 1.

Ввести расчетную таблицу (рис. 116) и рассчитать комиссионные от продаж. При продаже до 150 т.р. комиссионные составляют 5%, а более 150 т.р. –6%. При выполнении задания присвоить имена ячейкам В2, В3, В4 и ввести их в соответствующие формулы.

Рис. 116

Задание 2.

Ввести расчетную таблицу (рис. 117) и определить оценку при решении теста при условии, что более 25 правильных ответов оценивается в 5 баллов, более 20 – 4 балла, менее 20 – 3 балла.

Рис. 117

Скорректировать расчетную формулу, дополнив ее условием для оценки в 2 балла, если количество правильных ответов меньше 10.

Задание 3.

Ввести таблицу расчета (рис. 118) и выполнить указания, приведенные в задании

Рис. 118

Добавить справа столбец с именем Количествои столбец с именемСтоимость. В столбецКоличествоввести произвольные значения как больше 5, так и менее 5 шт. Для расчета стоимости как произведения цены на количество использовать функцию ЕСЛИ.

Задание 4.

Создать в Excel исходную таблицу (рис. 119) и таблицу результатов ее обработки (рис. 120).

Рис. 119.

Рис. 120.

В ячейку G4ввести формулу для расчета максимального количества осадков (функция МАКС)за три года. В ячейкуG5ввести формулу для расчета минимального количества осадков за три года (функцияМИН). ФункцииМАКС и МИН содержатся в разделе статистических функций. В ячейкуG6ввести формулу для расчета суммарного количества осадков за три года (функцияСУММ). ФункцияСУММ содержится в разделе математических функций. В ячейкуG7 ввести формулу для расчета среднего количества осадков за три года (суммарное количество осадков разделить на общее число месяцев).

В ячейку G8ввести формулу для расчета количества засушливых месяцев за три года (в засушливом месяце осадков меньше 10 мм). Для расчета количества засушливых месяцев за три года использовать формулуСЧЕТЕСЛИ (интервал, критерий).Критерием является условие:< 10.

Функция СЧЕТЕСЛИ содержится в разделе математических функций.

Для расчета количества месяцев в пределах >20 и <80 и вне нормы <10 и >100 нужно создать две вспомогательные таблицы.

Рис. 121.

Первая таблица показана на рис. 121. В ячейку таблицы В18 ввести логическую функцию ЕСЛИ (И(B6>20;B6<80);1;0).

Функция имеет следующий смысл: Логическое условие И объединяет два неравенства(B6>20 и B6<80), которые должны выполняться одновременно. Если они оба выполняются, то в ячейку заносится значение 1. В противном случае - 0.

Скопировать содержимое ячейки В18 в остальные ячейки области В18:D29. В ячейкуG9 ввести формулуСЧЁТЕСЛИ(B18:D29;"=1"). Будет подсчитываться количество ячеек, в которых значение равно 1 (т.е. количество месяцев, когда осадки в пределах >20 и <80).

Самостоятельно создать вторую вспомогательную таблицу, ввести в нее необходимую логическую функцию для определения месяцев вне нормы (условие <10 и >100). Эти условия должны объединяться логическим условием или. В ячейку G10 ввести формулу аналогично ячейкеG9 с ссылкой на вторую вспомогательную таблицу.