Расчет эффективности капиталовложений с помощью функции ПС
Рассмотрим следующую задачу: у вас просят в долг 10000 руб. и обещают возвращать по 2000 руб. в течение 6 лет. Банк принимает вклад под 7% годовых. Что выгоднее, дать деньги в долг или положить в банк?
В приводимом на рисунке расчете в ячейке В 5 введена формула: =HQB4;B2;-B3);
в ячейке С2: =ЕСЛИ(B2=1;"год";ЕСЛИ(И(B2>=2;B2<=4);"года";"лет")); в ячейке В6:
=ЕСЛИ(B1 В рассмотренной задаче две результирующие функции: числовая - чистый текущий объем вклада и качественная, оценивающая выгодна ли сделка. Рассмотрим 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. Если ячейке Е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 Еще 1100 1200 Карман
i > I и 4> м , Отчет сети магазинов / Готово
Рисунок 23