Расчет эффективности капиталовложений с помощью функции ПС

Рассмотрим следующую задачу: у вас просят в долг 10000 руб. и обещают возвращать по 2000 руб. в течение 6 лет. Банк принимает вклад под 7% годовых. Что выгоднее, дать деньги в долг или положить в банк?

В приводимом на рисунке расчете в ячейке В 5 введена формула: =HQB4;B2;-B3);

в ячейке С2: =ЕСЛИ(B2=1;"год";ЕСЛИ(И(B2>=2;B2<=4);"года";"лет")); в ячейке В6:

=ЕСЛИ(B1 н\пс/ |< | Готово NUM > Рисунок 20

В рассмотренной задаче две результирующие функции: числовая - чистый текущий объем вклада и качественная, оценивающая выгодна ли сделка.

Эту ситуацию удобно проанализировать для нескольких возможных вариантов параметра. Команда Сервис^Сценарии предоставляет та- кую возможность с одновременным автоматизированным предоставлением отчета.

Рассмотрим 3 комбинации срока и суммы ежегодно возвращаемых денег: 6, 2000; 12, 1500; 7, 1500. Для этого выполните:

Сервис^Сценарии^Добавить.

В диалоговом окне Добавление сценария в поле Название сценария введите, например, ПС1, в поле Изменяемые ячейки - ссылку на ячейки В2 и ВЗ (срок и сумма возвращаемых денег):

После нажатия кнопки ОК появится диалоговое окно Значение ячеек сценария, в поля которого введите значения параметров для первого сценария: Значе ния ячеек сце нария | X J Введите значения каждой изменяемой ячейки. 1 ОК I 1: $В$2 6 1 Отмена 1 1 2: $В$3 2000 [ Добавить j С помощью кнопки Добавить последовательно создайте нужное число сценариев.

Нажмите ОК, после этого диалоговое окно Диспетчер сценариев будет иметь вид:

3. Нажмите Отчет. Укажите тип отчета Структура или Сводная таблица, в поле Ячейки результата дайте ссылки на ячейки В5 и В6, в которых вычисляются значения результирующих функций. ОК. Отчет по сценариям типа Структура представлен на рисунке 21.

Ш Microsoft Excel - Расчет эффективности с помощью функции ПЗ wmm т Файл Правка Вид Вставка Формат Сервис Данные Окно Справка Введите вопрос - _ S X 1:j jd^ji^Lii^taui -Ji- J - ЧД г - ы ит „•]> 100% - ® И |; Arial Cyr .10 . ж к ч ¦= « = И_Ш 1 ^ % Ш TsS & HF iF E - -а*, А J_B G11 fx Л * в с D E F L G Н 1 2 Структура сценария 3 Текущие значения: П31 П32 пзз 5 Изменяемые: ¦ 6 $В$2 6 6 12 7 1 ¦ 7 $в$з 2 000,00р. 2 000,00р. 1 500,00р. 1 500,00 р. 0 В Результат: Э $ В $5 9 533,08р. Э 533,08р. 11 914,Юр. 8 083,93р. J Выгоднее деньги Выгоднее деньги Выгодно деньги Выгоднее деньги 10 $В$6 положить в банк положить в банк дать в долг положить в банк 11 Примечания: столбец 'Текущие значения" представляет значения изменяемых|ячеек в I 12 момент создания Итогового отчета по Сценарию. Изменяемые ячейки для каждого 13 сценария выделены серым цветом. 14 V \\i 4 i \ Структура сценария / ПЗ / | < > I ^OTOB^^ ыим Рисунок 21

Примеры отчетных ведомостей

Ведомость о результатах работы сети магазинов ? Microsoft Excel - Выручка сети ма газ и но в Ц0И Гн] файп Правка Вид Вставка Формат Сервис Данные Окно Справка Введите вопрос - - в 1: j сз a j, иЗНе< ai^i &I Д -4а J\ -П - - Ч& ? - : &J ?1 Ш 5> юо% ¦ - «'В 1 ; Arial Cyr . ю . | ж К Ч s HI llllll и I Ш % Ш й I - - В ] - - Д в J3 - fx {-4ACT0TA(E3:E8,I3:I5)} А В С D Е F G н I j I к УЧ 1 Выручка сети магазинов е t млн. Р/О _ Магазин Июнь Июль Август Суммарная Место Средняя Процент Диапазоны Количество 2 выручка выручка 3 1 225 455 534 1214 2 ' 404,67 18% 1000 1 4 2 342 356 345 1043 5 ' 347,67 16% 1100 1 5 3 432 357 454 1243 1 ' 414,33 19% 1200 2 6 4 324 243 248 815 6 ' 271,67 12% >1200 2 7 5 352 423 392 1167 3 ' 389,00 18% 8 6 421 354 351 ' 1126 4 ' 375,33 17% 9 Итого: 2096 2188 2324 6608 10 11 H <4 > н \ Отчет сети магазинов Г~ l< и =_И > I \ Готово Сумма=6 ыим ? J Рисунок 22

В ячейку Е3 введите формулу =СУММ(В3:Б3), которую с помощью маркера заполнения протащите на диапазон Е4:Е8.

В ячейку В9 введите формулу =СУММ(В3:В8), которую протащите на диапазон В9:Е9.

В ячейку G3 введите формулу =СРЗНАЧ(В3:Б3), которую протащите на диапазон G4:G8.

В ячейку Н3 введите формулу =Е3/$Е$9, которую протащите на диапазон Н4:Н8.

После чего диапазону Н3:Н8 назначьте процентный формат с помощью кнопки 0.

Если ячейке Е9 присвоить имя Итого, то формула приняла бы вид: =Е4/Итого.

Для нахождения места магазина по объему продаж введите в ячейку F3 формулу {=РАНГ(Е3;$Е$3:$Е$8)}, которую протащите на диапазон F3:F8.

Фигурные скобки в начале и конце формулы являются признаком массива и вводятся нажатием Ctrl+Shift+Enter либо после завершения ввода формулы, либо в процессе ее редактирования.

6. Высчитайте для данного множества суммарных выручек магазинов, сколько значений попадает в интервалы от 0 до 1000, от 1001 до 1100, от 1101 до 1200 и свыше 1201 млн. руб. Для этого в диапазон ячеек J3:J6 введите формулу {=ЧАСТОТА(Е3:Е8; I3:I5)}.

Частоты можно также вычислить с помощью команды Сер- вис^Анализ данных. Средство анализа данных является одной из надстроек Excel. Если в меню Сервис отсутствует команда Анализ данных, то для ее установки необходимо выполнить команду Сер- вис^Надстройки^Пакет анализа.

После выбора пункта Гистограмма откроется окно Гистограмма |Xj 1 Руплныр ЛДННЫЙ , S 1 Входной интервал: |$Е$3:$Е$8 [^в] 1 <* 1 Интервал карманов: |$1$3:$1$5 [^г] [ Отмена ] 1 1 Метки

Параметры вывода

® Выходной интервал: |$К$3 [5bsJ О Новый рабочий лист:

Новая рабочая книга

1 Парето (отсортированная гистограмма) 1 1 Интегральный процент

0 [Вывод [рафика] [ Справка ] В поле Входной интервал введите диапазон Е3:Е8, по которому строим диаграмму. В поле Интервал карманов введите диапазон I3:I5 со значениями верхних границ интервалов. В поле выходной интервал укажите $К$3. На рисунке 23 приведен результат построения гистограммы: И0®

? Microsoft Excel - Выручка сети ма газ и но в Файл Правка Вид Вставка Формат Сервис Диаграмма Окно Справка

jLga^Jl^jJ^&U-J -о- - I - S - 1 1 I Й У и : Arial Суг

ч Ж А- Ч I Ш Ж т ^ | -J -Й ООО .у | щ , & , А , (j Диагр. 1

Карман Частота .ал

? Частота

Еще

2,5

я 2 о 1,5 ? 1 га 1

0,5 0

Гистограмма

1000 1100 1200

1000