Работа с табличным процессором MS Excel

Комитет по образованию Санкт-Петербурга

Государственное образовательное учреждение СПО
Санкт-Петербургский колледж управления и экономики
«Александровский лицей»

, ,

Работа с табличным процессором MS EXCEL

Учебное пособие. Часть 2. Финансовые вычисления.

Санкт-Петербург 2009

Часть 2. пособия представляет собой курс по использованию финансовых функций MS EXCEL и рекомендовано для студентов экономических специальностей, изучающих информационные технологии применительно к профессиональной деятельности.

Пособие содержит много примеров.

Подробно излагаются теоретические аспекты использования финансовых расчетов для выполнения несложных финансовых задач пользователя.

Пособие предназначено для студентов колледжа.

Лабораторная работа №7[1]

Тема: Финансовые вычисления в MS Excel

Время в финансовых расчетах

Измерение времени в финансовых расчетах имеет свои особенности. При предоставлении краткосрочного кредита оплата за его использование пропорциональна времени, на которое он предоставлен. Здесь мы и сталкиваемся с некоторым несовершенством нашего календаря.

Например, пусть один кредит предоставлен на первое полугодии 2004 года, а другой – на второе полугодие 2004 года. На первый взгляд сроки кредита одинаковы – полгода, но первое полугодие длится 182 день, а второе – 184. Поэтому измерение времени в финансовых расчетах сопровождается условными соглашениями.

Фактически можно выделить два основных подхода:

1.Придерживаться точного числа дней в году (365, 366) и точного числа дней в месяцах.

2.Считать, что год состоит из 12 месяцев по 30 дней в каждом.

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

ДНЕЙ360(нач_дата; кон_дата; метод) - вычисляет количество дней между двумя датами: нач_дата и кон_дата, считая, что в году 360 дней: 12 месяцев по 30 дней каждый. Метод - это логическое значение (Истина – 1, или ЛОЖЬ – 0), которое определяет, какой метод, Европейский (1) или Американский (0, US, NASD[2]), должен использоваться при вычислениях. Европейский метод: начальная и конечная даты, которые приходятся на 31 число месяца, полагаются равными 30-тому числу того же месяца. При этом вместо 28 или 29 февраля всегда используется 30 –ое.

è Например, вычислить разность между датами 31 августа и 28 июля 2004 г.

	А	В	С	D	В ячейках столбца D показаны формулы, по которым произведены вычисления в соответствующих ячейках столбца С
1	28.07.01	31.08.01	34	=B1-A1
2			33	=ДНЕЙ360(A1;B1)
3			32	=ДНЕЙ360(A1;B1;1)

Для американского метода конечная дата превратилась в первое сентября, поэтому разность, вычисленная по американскому методу (строка 2) на один день больше.

ДОЛЯГОДА(нач_дата, кон_дата, базис). Эта функция возвращает частное от деления количества дней между начальной датой и конечной датой на количество дней в году. Здесь может быть несколько вариантов, полученных сочетанием методик вычисления разности между двумя датами и определением количества дней в году. Эти варианты определяют выбор базиса. В таблице 1 приведены различные варианты методик вычисления разности дат и соответствующие им базисы.

Таблица 1. Базис для расчетов в функции ДОЛЯГОДА

0 или опущен	US NASD 30/360
1	Фактический/фактический
2	Фактический/360
3	Фактический /365
4	Европейский 30/360

è Например, в продолжение предыдущего примера вычислить долю года между датами 28 июля и 31 августа. Год 2004 выбран не случайно, он високосный.

А	В	С	В ячейках столбца С показаны формулы, по которым произведены вычисления в соответствующих ячейках столбца В
6	0,092896	=С1/366
7	0,091667	=ДОЛЯГОДА(А1;В1;0)
8	0,092896	=ДОЛЯГОДА(А1;В1;1)
9	0,094444	=ДОЛЯГОДА(А1;В1;2)
10	0,093151	=ДОЛЯГОДА(А1;В1;3)
11	0,088889	=ДОЛЯГОДА(А1;В1;4)

Функции для анализа инвестиций

Существуют различные способы начисления процентов от предоставления денег в долг в любой форме.

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

При постоянной базе используют простые проценты, при переменной – сложные процентные ставки.

Рассмотрим схему однократного представления некоторой суммы P в кредит на время t. За использование кредита надо платить. Возврат кредита составит S=P+I. Плата I (Interest) носит название «процент».

Какой выбрать величину процента? Логичнее всего положить процент пропорциональным сумме кредита Р. С другой стороны, чем больше время, на которое предоставлен кредит, тем больше процент. Опять таки естественно предположить, что плата за кредит пропорциональна времени его использования. В результате приходим к формуле:

I=rPt, где r – коэффициент пропорциональности – носит название «процентная ставка».

В каких единицах измерять величины, входящие в формулу? Понятно, что I и P измеряются в денежных единицах: рублях, долларах, евро …. А вот время измеряется, как правило, в годах. Причем количество лет может быть и не целым. Тогда используют доли года. Размерность процентной ставки r (rate of interest) – 1/год. Однако никто не говорит, что ставка составляет 0, 06 в год. Принято говорить: ставка составляет 6% годовых в рублях.

Величина наращенной суммы (accumulated value) определяется по формуле S=P(1+rt). Если t равен году, то формула упрощается S=P(1+r). Отношение S/P называется коэффициентом наращения.

Что означает 50 % годовых? S=P(1+0,5) =1,5P. Таким образом, наращенная сумма в полтора раза больше первоначальной. Во сколько раз вырастет исходная сумма при 500% годовых? В шесть раз, т. е. можно сказать: коэффициент наращения равен 6.

Рассмотрим, как проводить вычисления для простых процентов. Задана начальная сумма P, задана ставка процента r, причем надо проследить корректность размерности: ставка должна быть отнесена к году. Время нужно выразить в долях года. Заметим, что день выдачи ссуды и день погашения ссуды считаются одним днем. Поэтому, если нужно вычислить точное число дней ссуды, нужно просто вычесть дату выдачи из даты погашения и не корректировать результат прибавлением единицы.

Доля года вычисляется по формуле: , где t число дней ссуды, К – число дней в году, или временная база. Из предыдущего материала известно, что измерение времени в финансовых расчетах дело непростое. Здесь возможны различные соглашения.

è Пример. Ссуда в размере 1 выдана 20 января по 5 октября включительно под 18% годовых. Какую сумму должен заплатить должник в конце года?

Формулировка задачи нуждается в уточнении: в каком году производятся операции: в обычном или високосном. Рассмотрим варианты использования различного базиса (см. таблицу 1). Для лучшего понимания процессом манипулирования данными рекомендуется оформлять задачу на рабочем листе, как показано на рис.1. Ячейкам В2, В3, В4, В5 рекомендуется дать имена, соответствующие наименованиям ячеек, приведенным в ячейках столбца А[3] (см. рис.1)

Рис.1. Расчет суммы возврата кредита

ПРИМЕЧАНИЕ. Использование для ячеек с данными имен, вводимых пользователем в соответствии с наименованием аргументов функции, позволяет лучше понять и усвоить синтаксис функции, однако, с точки зрения реализации в MS Excel в формулах нагляднее использовать ссылки на ячейки (т. е. адреса типа В1, В2 и т. д.)

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

 

Рис.2. Схемы финансовых операций: а) схема с точки зрения дебитора,
б) схема с точки зрения кредитора

На рис.2 а) дебитор получил в свое распоряжение сумму Р (знак положительный), а в конце периода Т должен вернуть эту сумму с процентами. Он лишается этой суммы, поэтому знак отрицательный. На рис.2 б) та же схема, но с точки зрения кредитора. В начале периода Т он лишается суммы Р, поэтому знак отрицательный, а в конце возвращает ее с процентами.

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

Поэтому настоящее и будущее значение связаны соотношением: S+P(1+rt)=0, из которого ясно, что S и P должны иметь разные знаки. Время в финансовых функциях измеряется периодами: год, квартал, месяц, день.

Тогда r=I/P – процентная ставка (interest rate), а d=I/S – ставка дисконта (discount rate). Легко получить между ними соотношение: r=d/(1-d)

Обычно процентную ставку относят к фиксированному периоду, обычно году.

Начисление по схеме простых процентов (simple interest) производится по формуле S+P(1+rT)=0, а по схеме сложных процентов (сompound interest):

S+P(1+r)T=0 , где r – процентная ставка за период, T- время, измеряемое в периодах.

Функция БС

Рассмотрим вычисление по схеме простых процентов. Для вычисления наращенной суммы будем использовать функцию БС – будущая сумма (или БЗ - будущее значение, для версий MS OFFICE ниже XP). Сразу оговоримся, что эта функция предназначена для вычисления по схеме сложных процентов, но при Т=1 формулы для вычисления S совпадают.

Синтаксис функции:

БС(ставка;число_периодов; выплата;нач_знач;тип).

Функция вычисляет для некоторого будущего момента времени величину вложения. Краткое описание аргументов:

Ставка – проценты по вкладам и займам, начисляемые за один период. Например, если выплаты производятся ежемесячно, нужно разделить годовую ставку на 12.

Число периодов (функция КПЕР)–общее количество периодов выплат.

Выплата – фиксированная выплата за каждый период займа или вклада.

Начальное значение (функция ПЗ)– необязательный аргумент, если он не указан, используется нуль, начальное вложение, текущий объем вклада, взятый кредит или ссуда.

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

è Пример

Выдан кредит в сумме 1 руб. с 15.01.02 по 15.03.03 под 20% годовых. Нужно рассчитать будущее значение исходной суммы.

Прежде, чем воспользоваться функцией БС, произведем некоторые расчеты. Число периодов для простых процентов равно 1. Проценты даны годовые. Поэтому предварительно вычислим процентную ставку за указанный период (см. рис 3).

Рис.3. Пример выполнения функции БС для расчета возвращаемой
суммы кредита

На рис.2 в столбце D показаны формулы вычисления значений соответствующих ячеек столбца С. Рекомендуется использовать имена ячеек В2, В3, В4, В5, как описано выше. В примере использованы имена для ячеек В2, В3, В4, В5, соответствующие наименованиям, приведенным в соответствующих ячейках столбца А.

Поясним третий пропущенный аргумент функции. Под выплатами подразумеваются промежуточные равные выплаты в начале (тип=1) или в конце (тип=0 или опущен) периода. В нашем примере выплат нет.

Понятие будущей стоимости основано на принципе неравноценности денег, относящихся к разным моментам времени. Вложения, сделанные сегодня, в будущем составят большую величину. Будущую или наращенную стоимость серии фиксированных периодических платежей, а также будущую стоимость текущего значения вклада или займа при постоянной процентной ставке рассчитывают с помощью функции БС.

Для вычислений по схеме сложных процентов в договорах указывается годовая ставка r и количество начислений процентов m в течение года. Это означает, что базовый период составляет год деленный на m, а ставка сложных процентов приобретает вид: r/m. Формула для сложных процентов приобретает вид:

. Напомним, что Т измеряется периодами. Если начисление происходит k лет, то формула приобретает вид:

.

Рассмотрим различные варианты использования функции БС при решении конкретных задач:

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

è ПРИМЕР. Ссуда вруб. дана на полтора года под ставку 28% годовых с ежеквартальным начислением. Определить сумму конечного платежа.

Формула для расчета: , где P= 20000 руб., r=28%, m=4 квартала в году, Т= 6 кварталов. Аргументы выплата и тип отсутствуют. Решение задачи приведено на рабочем листе рис.4. Определить с какой позиции - дебитора или кредитора рассмотрена задача. В примере используются ссылки на ячейки с данными.

Рис.4. Пример выполнения функции БС для расчета возвращаемой суммы по схеме сложных процентов

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

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

=БС(ставка; число_периодов;выплата;;1)

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

=БС(ставка;число_периодов;выплата;;0)

Аргумент тип = 0 можно опустить и записать:

=БС(ставка;число_периодов;выплата), подставив вместо аргументов соответствующие числа.

è ПРИМЕР. Предположим, есть два варианта инвестирования средств в течение 4 лет: в начале каждого года под 26% годовых или в конце каждого года под 38% годовых. Пусть ежегодно вносится руб. Определить, сколько денег окажется на счете в конце 4-го года для каждого варианта.

Использование формул опустим, перейдем сразу же к функции БС. Процесс решения показан на рис.5.

Рис.5. Пример для систематических платежей в начале и конце периода

Расчеты показали, что первый вариант предпочтительнее. Определить с какой позиции - дебитора или кредитора рассмотрена задача.

Задания для самостоятельной работы с функцией БС

1.  Рассчитайте, какая сумма будет на счете, если сумма размером 5 руб. размещена под 12% годовых на 3 года, а проценты начисляются каждые полгода.

2.  На сберегательный счет вносятся платежи по руб. в начале каждого месяца. Рассчитайте, какая сумма окажется на счете через 4 года при ставке процента 13,5% годовых. Сравните будущее значение счета, если платежи вносятся в конце каждого месяца.

Функция ПЗ

Теперь рассмотрим задачу: как по будущему значению определить текущее (начальное) значение. Для этого воспользуемся функцией ПЗ – приведенное (современное) значение. Синтаксис функции:

ПЗ(ставка;число_периодов;выплата;бс;тип)

Эта функция может быть полезна в следующих расчетах:

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

è ПРИМЕР. Фирме потребуется 5 руб. через 12 лет. В настоящее время фирма располагает деньгами и готова положить их на депозит единым вкладом, чтобы через 12 лет он достиг 5 руб. Определим необходимую сумму текущего вклада, если ставка процента по нему составляет 12% в год. Решение представлено на рис.6.

Рис.6. Определение суммы текущего вклада с помощью функции ПЗ

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

2. Предположим теперь, что требуется найти текущую стоимость будущих периодических постоянных платежей, которые производятся в начале или в конце каждого расчетного периода. Согласно концепции временной стоимости, чем дальше отстоит от настоящего момента поступление или расходование средств, тем меньшую текущую ценность оно представляет. Таким образом, при прочих равных условиях текущая стоимость вкладов пренумерандо больше, чем текущая стоимость вкладов постнумерандо.

è ПРИМЕР. Предположим, рассматриваются два варианта покупки дома: платить сразу руб. или в рассрочку - по 9400 руб. ежемесячно в течение 15 лет. Определить, какой вариант предппочтительнее, если ставка процента - 8% годовых.

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

Допустим, что выплаты происходят в конце каждого расчетного периода. По условию период начисления процентов равен месяцу.

Число периодов определим: число_периодов=15*12, ставку процента за период начисления ставка =8%/12. Выплата = -9400 руб. Расчет необходимо провести, используя функцию ПЗ. Решение приведено на рис.7:

Рис.7. Расчет текущей стоимости будущих фиксированных периодических
выплат

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

Задания для самостоятельной работы с функцией ПЗ

1. Определите текущую стоимость обязательных ежемесячных платежей размером руб. в течение 5 лет, если процентная ставка составляет 12% годовых.

2. Рассчитайте, какую сумму необходимо положить на счет, чтобы через 4 года она достигла значенияруб. при начислении 9% годовых.

Функция КПЕР

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

Общее число периодов постоянных выплат, необходимых для достижения заданного будущего значения и число периодов, через которое начальная сумма займа (вклада) достигнет заданного значения подсчитывается с помощью функции КПЕР

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

Синтаксис:

КПЕР(ставка;выплата;нач_знач;будущая_сумма;тип).

Функция может применяться в следующих расчетах:

1. Если рассчитывается общее число периодов начисления процентов, необходимых для того, чтобы начальная сумма размером нач_знач достигла указанного будущего значения буд_сумма, то формула примет вид:

=КПЕР(ставка;;нач_знач;буд_сумма).

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

=КПЕР(ставка;выплата;;буд_сумма;1)

Если выплаты производятся в конце расчетного периода, то применяется формула:

=КПЕР(ставка;выплата;;буд_сумма)

3. При погашении займа размером нач_знач равномерными постоянными платежами в конце каждого расчетного периода число периодов, через которое произойдет полное погашение, равно:

=КПЕР(ставка;выплата;нач_знач).

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

è ПРИМЕР_1. За какой срок в годах сумма, равнаяруб. достигнет руб. при начислении процентов по сложной ставке 15% раз в году и поквартально. Решение приведено на рис.8.

Обратите внимание, что во втором случае КПЕР возвращает количество кварталов (26,64285), поэтому, чтобы пересчитать их в годы, нужно поделить возвращаемый результат на 4 (получится 6,66071).

Рис.8. Расчеты с помощью функции КПЕР

è  ПРИМЕР_2. Для обеспечения будущих расходов создается фонд. Средства в фонд поступают в виде постоянной годовой ренты в конце периода (постнумерандо). Размер разового платежаруб. На посту-пившие взносы начисляется 11,18% годовых. Необходимо определить, когда величина фонда будет равна руб. решение приведено на рис.9. Через 5 лет совокупная величина выплат составит руб.

è 

Рис.9. Расчеты с помощью функции КПЕР

è  ПРИМЕР_3. Ссуда размеромруб., выданная под 36% годовых, погашается обычными ежемесячными платежами по 6 630 руб. Рассчитаем срок погашения ссуды. Решение приведено на рис.10. Так как по условию заем полностью погашается, его будущая стоимость равна 0. Срок, за который заем полностью погашается, равен 1 году.

Рис.10. Расчеты с помощью функции КПЕР

Задания для самостоятельной работы с функцией КПЕР

1.  Ссударуб., выданная под 32% годовых, погашается ежеквартальными платежами по 8400 руб. Рассчитайте срок погашения ссуды.

2.  Рассчитайте, через сколько лет произойдет полное погашение займа размером руб., если выплаты по руб. производятся в конце каждого квартала, а ставка процента – 15% годовых.

Функция ППЛАТ

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

Синтаксис: ППЛАТ(ставка;кол_периодов; нач_знач;буд_сумма;тип)

Выплаты, рассчитанные этой функцией, включают основные платежи и платежи по процентам. С учетом ранее изученных функций, функцию ППЛАТ можно представить: ППЛАТ(ставка;кпер;нач_знач;бс;тип).

Замечание по предпоследнему параметру: будущая сумма – это баланс наличности, который нужно достичь после последней выплаты. Если этот параметр опущен, будущее значение полагается равным нулю, т. е. задолженность погашена.

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

Функция ППЛАТ применяется в следующих расчетах.

1. Допустим, известна будущая стоимость фиксированных периодических выплат, производимых в начале или в конце каждого расчетного периода. Требуется рассчитать размер этих вы­плат. Соответствующая запись в Excel имеет вид: =ППЛАТ(ставка; кпер;;бс, тип).

2. Предположим, рассчитываются равные периодические платежи по займу величиной нач_знач, необходимые для полного по­гашения этого займа через КПЕР число периодов. Текущая сто­имость этих выплат должна равняться текущей сумме займа. Соответствующий расчет в EX­CEL выполняется по формуле: =ППЛАТ(ставка;кпер;нач_знач;;тип).

Обычно погашение происходит в конце каждого расчет­ного периода. Для этого случая формула имеет вид: =ППЛАТ(ставка, кпер, нач_знач), так как аргумент тип - 0.

Если заем погашается не полностью, то есть его будущее значение не равно 0, то следует указать аргумент БС, который будет равен непогашенному остатку займа после всех выплат.

Все примеры, приведенные в этом разделе, рекомендуется самостоятельно решить на рабочем листе MS Excel, используя примеры оформления расчетов, показанные в предыдущих разделах.

è ПРИМЕР_1. Предположим, что необходимо накопить 4 руб. за 3 года, откладывая постоянную сумму в конце каждого месяца. Какой должна быть эта сумма, если норма процента по вкладу составляет 12% годовых.

Определим общее число периодов начисления процентов и ставку процента за период. Эти величины составят соответственно 3*12 (аргумент кпер) и 12%/12 (аргумент ставка). Аргумент тип =0, так как по условию это вклады постнумерандо (выплаты в конце периода). Рассчитаем величину ежемесячных выплат.

ППЛАТ(12%/12;3*12;;4000000) = ,24 руб.

è ПРИМЕР_2. Допустим, банк выдал ссуду руб. на 4 года под 18% годовых. Ссуда выдана в начале года, а погашение начинается в конце года одинаковыми платежами. Определите размер ежегодного погашения ссуды.

Ежегодные платежи составят: ППЛАТ(18%;4;-200)= -,19 руб.

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

Задание для самостоятельной работы с функцией ППЛАТ

1. Определите размеры периодических взносов в фонд размером руб., сформированный за два года ежемесячными платежами, если процентная ставка составляет 20% годовых.

2. Определите размер ежегодного погашения займа размеромруб., выданного на 3 года под 38% годовых.

3. Рассчитайте колонку «Общая сумма платежа» приведенной ниже таблицы. В таблице приведена схема погашения займа вруб., выданного сроком на 3 года под 17% годовых.

Год	Сумма займа на начало года	Общая сумма платежа (ППЛАТ)	Платежи по процентам (ПЛПРОЦ)	Сумма основного платежа по займу (ОСНПЛАТ)	Сумма займа на конец года
1	2	3	4	5	6
1	70
2
3					0
ИТОГО

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

Функция ПЛПРОЦ

Часть выплат для обслуживания процентов по основному долгу вычисляется с помощью функции ПЛПРОЦ.

Функция вычисляет платежи по процентам за заданный период на основе периодических постоянных выплат и посто­янной процентной ставки.

Синтаксис: ПЛПРОЦ(ставка;период;кол_периодов;нач_знач;бс;тип)

Функция предназначена для следующих расчетов.

1. При равномерном погашении займа постоянная перио­дическая выплата включает в себя платежи по процентам по непогашенной части займа и выплату задолженности. Так как непогашенная часть займа уменьшается по мере его погаше­ния, то уменьшается и доля платежей по процентам в общей сумме выплаты, и увеличивается доля выплаты задолженности. Чтобы найти размер платежа по процентам на конкретный пе­риод, следует использовать формулу: ПЛПРОЦ(ставка;период;кпер;нач_знач), если погашение займа производится равными платежами в конце каждого расчетного периода.

3.  Допустим, необходимо вычислить доход, который приносят постоянные периодические выплаты за конкретный пе­риод. Этот доход представляет собой сумму процентов, начис­ленных на накопленную (с процентами) к данному моменту со­вокупную величину вложений. Расчет ведется по формуле: ПЛПРОЦ(ставка;период;кпер;;бс;тип).

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

è ПРИМЕР_1. Вычислите платежи по процентам за первый месяц от трех­годичного займа в руб. из расчета 10% годовых.

Определяем число периодов и ставку за период: ставка=10%/12, кпер=12*3. Расчет производим за первый период: ПЛПРОЦ(10%/12;1;12*3;800) =,67 руб.

è ПРИМЕР_2. Предположим, что за счет ежегодных отчислений в течение 6 лет был сформирован фонд в 5 руб. Определим, какой доход приносили вложения владельцу за последний год, если годовая ставка составляла 17,5%.

Доход за последний год (6 период) составил:

ПЛПРОЦ(17,5%;6;6;;5 = - ,03 руб.

Ежегодно отчислялось

ППЛАТ(17,5%;6;;5000000) = -,82 руб.

Задание для самостоятельной работы с функцией ПЛПРОЦ

Рассчитайте колонку «Платежи по процентам» приведенной ниже таблицы. Рассчитайте сумму займа на конец 1-го года и начало 2-го, а также сумму займа на конец 2-го года и начало 3-его. Подвести итог по рассчитываемой колонке (исходные данные предыдущего задания для функции (ППЛАТ).

Год	Сумма займа на начало года	Общая сумма платежа (ППЛАТ)	Платежи по процентам (ПЛПРОЦ)	Сумма основного платежа по займу (ОСНПЛАТ) Ст.3-Ст.4	Сумма займа на конец года Ст.2+Ст.4-Ст.3
1	2	3	4	5	6
1	70	31
2		31
3		31			0
ИТОГО	95

Столбец 3 (функция ППЛАТ) заполнен на основании решенного задания предыдущего раздела.

Функция ОСНПЛАТ

Если мы хотим узнать, какая часть выплат идет на погашение основной задолженности, воспользуемся функцией ОСНПЛАТ.

Функция вычисляет величину основного платежа по займу (выплаты задолженности) по займу, который погашается равными платежами в конце или начале каждого расчетного периода, на указанный период.

Синтаксис:ОСНПЛАТ(ставка;период;кол_перидов;нач_знач;бс;тип), или

ОСНПЛАТ(ставка;период;кпер;нач_знач;бс;тип)

è ПРИМЕР. Рассчитаем при помощи функции ОСНПЛАТ колонку «Сумма основного платежа по займу» приведенной ниже таблицы.

Год	Сумма займа на начало года	Общая сумма платежа (ППЛАТ)	Платежи по процентам (ПЛПРОЦ)	Сумма основного платежа по займу (ОСНПЛАТ) Ст.3-Ст.4	Сумма займа на конец года Ст.2+Ст.4-Ст.3
1	2	3	4	5	6
1	70	31	11		50
2	50	31	8		27
3	27	31	4		0
ИТОГО	95	25

Сумма основного платежа по займу получается как разность между фиксированной периодической выплатой и процентами по непогашенной части займа: например, для первого года: это разность между значением столбца 3 и значением столбца 4 строки первого года. Столбцы заполнены на основании решения предыдущих заданий для функций ППЛАТ и ПЛПРОЦ. Заполните ст.5, используя или формулу или функцию (см. ниже) и убедитесь, в правильном результате.

При помощи функции:

ОСНПЛАТ(17%;1;3;)=,16 руб.

ОСНПЛАТ(17%;2;3;)=,78 руб.

ОСНПЛАТ(17%;3;3;)=,06 руб.

Итоговая таблица получится, как показано ниже.

Год	Сумма займа на начало года	Общая сумма платежа (ППЛАТ)	Платежи по процентам (ПЛПРОЦ)	Сумма основного платежа по займу (ОСНПЛАТ) Ст.3-Ст.4	Сумма займа на конец года Ст.2+Ст.4-Ст.3
1	2	3	4	5	6
1	70	31	11	19	50
2	50	31	8	23	27
3	27	31	4	27	0
ИТОГО	95	25	70

Задание для самостоятельной работы с функцией ОСНПЛАТ

Определите основные платежи по займу вруб., выданному на 3 года под 21% годовых за третий год.

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

Тема: Анализ «что-если». Таблицы подстановки

Одной из очень привлекательных сторон MS Excel является возиожность использования формул для построения динамических моделей, которые немедленно реагируют на изменение исходных данных. Анализ «что – если» - это процесс поиска ответов, например, на следующие вопросы: «Что будет, если цену на продукцию поднимем на 5%?» и т. д.

Типы анализа «что – если»:

1.  Анализ «что – если» вручную.

2.  Анализ «что – если» с использованием макросов.

3.  Таблицы подстановки.

4.  Диспетчер сценариев.

О первом методе много не скажешь. Он очень прост: есть одна или несколько ячеек для ввода информации, ссылки на которые используются в нескольких ячейках с формулами. Меняется информация входных ячеек – меняется результат.

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

Создание таблиц подстановки. Этот тип анализа используется тогда, когда нужно сравнить результаты нескольких вариантов решения.

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

1. Имеется один набор данных для одной ячейки (одной переменной), на которую ссылаются несколько формул. В этом случае создается таблица подстановки с одним входом.

2. Имеется два набора данных для двух ячеек (две переменные), на которые ссылается одна формула. Создаваемая в этом случае таблица подстановки называется таблицей подстановки с двумя входами.

Таблица подстановки с одним входом

Макет таблицы подстановки с одним входом представлен на рис.1. Таблицу можно расположить в любом месте рабочего листа. Левый столбец содержит различные значения входного параметра. Верхняя строка содержит формулы или (чаще всего) ссылки на ячейки с формулами, по которым рассчитывается результат. Верхняя левая ячейка не используется.

Рис.1. Составные части таблицы подстановки с одним входом

è ПРИМЕР. Требуется определить, какие ежемесячные выплаты необходимо вносить по ссуде размером 200 млн. Руб., выданной на 3 года при разных процентных ставках.

Предварительно подготовим исходные данные на рабочем листе Excel, как показано на рис.2.

Для заполнения таблицы необходимо выполнить следующую последовательность действий.

1.Ввести в ячейку D7 формулу для расчета периодических постоянных выплат по займу, при условии, что он полностью погашается в течение срока займа.

=пплат(С4/12;С3*12;С2)

2.Выделить диапазон ячеек, содержащий исходные значения процентных ставок и формулу для расчета - С7:D13. исходные данные в примере расположены в столбце С8:С13, поэтому результаты подстановки будут расположены в столбце D8:D13.

Рис.2. Подготовка исходных данных для использования Таблицы подстановки

3.Выбрать в меню Данные - Таблица подстановки. На экране появится диалоговое окно Таблицы подстановки. Это окно используется для задания рабочей ячейки, на которую ссылается формула расчета. В примере это ячейка С4, которую необходимо указать в поле Подставлять значения по строкам в диалогового окна в абсолютных координатах. Если исходные данные расположены в строке, то ссылку на рабочую ячейку необходимо ввести в поле Подставлять значения по столбцам в.

4. При нажатии кнопки ОК заполнится столбец результатов, как показано на рис. 3.

ПРИМЕЧАНИЕ. Обратите внимание, что полученные периодические выплаты имеют отрицательный знак, так как сумма займа в функции ППЛАТ была введена как положительное значение.

Рис. 3. Ежемесячные выплаты по займу для разных процентных ставок

Если в Таблицу подстановки требуется включить больше формул, использующих исходные значения процентных ставок, то дополнительные формулы вставляются справа от существующей формулы в той же строке. Затем необходимо выделить всю таблицу, включая полученные ранее значения, и заполнить диалоговое окно команды таблица подстановки.

è ПРИМЕР. В примере для расчета платежей по процентам за первый период для каждого значения процентной ставки в ячейку Е7 необходимо ввести формулу =ПЛПРОЦ(С4/12;1;С3*12;С2) и повторить все описанные выше шаги. Результат представлен на рис.4.

Рис.4. Ежемесячные выплаты и платежи по процентам за первый месяц для разных процентных ставок

Для расчета выплат по процентам для остальных периодов (со 2 по 36) необходимо подставить формулы в следующие ячейки справа от последней.

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

Задание

Составить электронную таблицу, которая обрабатывает информацию об ипотечной ссуде при изменении процентных ставок от 7% до 9% с шагом 0,25%, используя таблицу подстановки с одним входом.

Исходные данные: Цена - $ первый взнос – 20%, срок погашения ссуды 360, процентная ставка – 8%. Рассчитать размер ссуды, месячную плату, Общую сумму и сумму комиссионных. Все данные и результаты расчета приведены на рис.5. Допустим, необходимо ответить на вопросы:

Что, если смогу договориться о более низкой цене на имущество?

Что, если кредитор потребует 20% выплаты?

Что, если я смогу получить 40-летнюю ссуду?

Что, если процентная ставка снизится до 7,5%?

Рис.5. Данные для расчета ипотечной ссуды

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

Таблица подстановки с двумя входами имеет макет, представленный на рис.6.

Рис.6. Макет Таблицы подстановки с двумя входами.

èПРИМЕР. Необходимо найти ежемесячные выплаты по займу размером 300 млн. руб. для различных сроков погашения и процентных ставок (рис.7).

Чтобы создать Таблицу подстановки для этой задачи, необходимо выполнить следующие действия.

1.Ввести первое множество входных значений (процентные ставки) в ячейки В8:В13 (см. рис.7).

2.Ввести второе множество входных значений (сроки погашения) в строку, расположенную выше и правее на одну ячейку, т. е. в ячейки С7:F7 (рис.7).

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

=пплат(С4/12;С3*12;С2)

4.Выделить диапазон таблицы данных, включающий все входные значения и формулу расчета, т. е. B7:F13.

5.Выбрать в меню Данные – Таблица подстановки и заполнить диалоговое окно: по столбцам - $С$3, по строкам – $С$4

6. Нажать кнопку ОК и получить результат, представленный на рис.8

Рис.8. Результат расчета таблицы подстановки с двумя переменными

Задание

Рассчитать эффективность проведения рекламной кампании с помощью рассылки материалов по почте путем вычисления чистой прибыли после продажи. Использовать для расчета инструмент Таблицы подстановки с двумя входами.

В модели будем использовать две ячейки для ввода информации: количество разосланных рекламных материалов и предполагаемый процент ответов. Область результата включает следующее(см. рис.9):

Стоимость печатных материалов. Изменяется в зависимости от количества:

0,20 – если количество экземпляров не превышает ;

0,15 – если количество экземпляров превышает , но не более экземпляров,

0,10 –если количество экземпляров превышает экземпляров.

Стоимость определяется по следующей формуле:

=если(разослано_материалов<200000;0,20;если(разослано_материалов<300000; 0,15;0,10))

Почтовые расходы. Их стоимость фиксирована и составляет 0,32 за одно почтовое отправление.

Число респондентов. Количество ответов, которые предполагается получить. Оно определяется в зависимости от процента предполагаемых ответов и количества разосланных материалов. Формула для ячейки следующая:

=процент_ответивших*разослано_материалов

Доход на одного респондента. Это фиксированное значение. Компании известно, что за каждый заказ она получит прибыль 22.

Суммарный доход. Вычисляется по формуле:

=доход_на_одного_респондента*число респондентов

Суммарные расходы. В них входит стоимость печатных материалов и почтовых услуг:

=разослано_материалов*(стоимость_печатных_материалов+почтовые_расходы)

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

Требуется создать таблицу подстановки с двумя входами, которая позволит вычислять чистую прибыль при разных комбинациях количества разосланных материалов и предполагаемого процента полученных ответов. Параметр Разослано_материалов задавать от до с шагом, процент_ответивших задавать от 1,50% до 3,00% с шагом 0,25%.

Рис.8. Расчет чистой прибыли после проведения рекламной кампании

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

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

Тема: Анализ «что-если». Диспетчер сценариев

Анализ с помощью таблиц подстановки является весьма эффективным, однако он имеет недостатки:

1. Одновременно можно анализировать расчетные данные только при изменении одного или двух исходных параметров.

2. Процесс создания таблицы подстановки интуитивно не всегда понятен.

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

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

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

èПРИМЕР. Чтобы понять, как работает диспетчер сценариев, рассмотрим задачу: прибыль магазина моделируется на рабочем листе (см. рис.1). Числа, представленные в ячейках D2:D3, D5, E8:E13 – это средние данные за последний год работы магазина. Нужно проверить влияние изменений в этих ячейках на прибыль, значение которой представлено в ячейке Е15. Девять переменных – это слишком много для таблицы подстановки, а Диспетчер сценариев с этим легко справиться. В ячейке D4 формула:=D2-D3, В ячейке D6 - формула =D4*D5. В ячейке Е6 формула =D6*52 (в году 52 недели). В ячейке Е14 формула =СУММ(Е8:Е13). В ячейке Е15 формула =Е6-Е14.

Рис.1. Рабочий лист для моделирования результатов

Доступ к средству Диспетчер сценариев можно получить с помощью команды Сервис – Сценарии. После выбора команды появляется диалоговое окно, как показано на рис.2.

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

Создание сценария

Чтобы добавить сценарий, нужно щелкнуть на кнопке Добавить. Появится диалоговое окно Изменение сценария (см. рис. 3). Окно разделено на 4 части:

1.  Название сценария. Ввести любое имя, но желательно, чтобы оно что-то обозначало.

2.  Изменяемые ячейки. Ячейки, в которых находятся исходные данные для сценария. В это поле можно ввести абсолютный адрес ячейки или ее имя. Можно выбрать несколько ячеек, не обязательно смежных. В каждом сценарии можно использовать одни и те же наборы изменяемых ячеек или разные. Количество изменяемых ячеек для одного сценария не должно превышать 32.

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

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

Рис. 3. Заполнение полей окна Изменение сценария

В поле Название сценария введите, например, Прошедший год.

В поле Изменяемые ячейки введены ссылки на соответствующие ячейки рабочего листа. Отдельные ячейки или группы ячеек разделяются знаком «;» (точка с запятой).

Нажмите кнопку ОК для создания первого сценария.

Откроется окно диалога Значения ячеек сценария (см. рис.4).

Рис. 4. Окно ввода значений изменяемых ячеек.

Если на рабочем листе изменяемым ячейкам присвоены соответствующие имена (рекомендуется!), эти имена отображаются рядом с полями, в противном случае отображаются ссылки на изменяемые ячейки (как показано на рис. 12.). Поля содержат значения, которые в настоящий момент введены на рабочем листе. Для завершения создания сценария, можно ввести другие значения или оставить прежние (рекомендуется!). После чего нажмите кнопку ОК. В каждое поле можно ввести константу или формулу. Окно диалога Значения ячеек сценария выводит только пять переменных за один раз, для вывода остальных полей используйте полосу прокрутки.

Для создания другого сценария нажмите кнопку Добавить для возврата в окно Изменение сценария. Так можно создать сколько угодно сценариев. Добавьте сценарии, например, Ожидаемый, Пессимистичный, Оптимистичный, введя соответствующие значения в изменяемые ячейки. После чего в окне диалога Диспетчер сценариев нажмите кнопку Закрыть.

Просмотр сценариев

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

ПРИМЕЧАНИЕ. Прежде, чем экспериментировать с кнопкой Вывести рекомендуется сохранять рабочий лист для сохранения первоначальных значений. Особенно, если количество сценариев велико.

Сценарии сохраняются вместе с другими данными рабочего листа. Каждый лист может содержать свой собственный набор сценариев, который загружается вместе с рабочим листом книги.

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

Объединение сценариев

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

Рис.5. Окно для объединения сценариев

Прежде, чем объединять сценарии, нужно убедиться, что рабочая книга, из которой берете сценарий, открыта. Из раскрывшегося списка в поле Книга выберите рабочую книгу, в которой находится рабочий лист с нужным сценарием. Затем из списка Лист выберите рабочий лист.

Предположим, что вы хотите передать модель «что-если» нескольким коллегам: Петрова хорошо знает работу с покупателями, Уткина знает нюансы накладных расходов, Андреев занимается рекламой. Можно объединить их вклады в модель тремя способами.

1.  Опросить каждого из них по порядку и добавить их сценарии к модели в первоначальном листе.

2.  Можно создать книгу с дубликатами исходной модели на четырех отдельных листах: один лист для себя и по одному для каждого из коллег. Чтобы создать эту книгу присвойте имя каждому листу и выделите листы как группу. Выделите копируемые данные и выберите в меню Правка – Заполнить, затем По листам, чтобы скопировать все данные, формулы и форматы (MS OFFICE 2000) из исходной модели в другие три листа, как показано на рис.6. Если необходимо, на каждом заполненном листе коллег скорректировать ширину столбцов В и С. Затем эта книга распространяется между коллег с требованием, чтобы они создавали сценарии только на своем листе.

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

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

Рис.6. Книга для передачи нескольким коллегам

ПРИМЕЧАНИЕ. Объединение сценариев проходит гладко, если все листы имеют идентичную базовую структуру.

В окне диалога Объединение сценариев выберите книгу, а затем лист, из которого вы хотите объединить сценарии. При выборе листа в нижней строке появляется сообщение о том, сколько сценариев на листе. При нажатии кнопки ОК сценарии выбранного листа копируются на активный лист.

После объединения всех сценариев для нашего примера окно диалога диспетчера сценариев может выглядеть примерно так, как показано на рис. 7.

Рис.7.Окно диалога с объединенными сценариями.

Обратите внимание, что список Сценарии содержит сценарии со схожими именами. В данном примере все сотрудники использовали для своих сценариев два одинаковых имени: ожидаемый, оптимистический, MS Excel во избежание конфликта имен добавляет к имени сценария имена авторов или даты. Если воспользоваться кнопкой Изменить, можно, при желании, переименовать сценарии.

В каждой группе сценариев используются различные изменяемые ячейки:

Сценарии Петровой изменяют значения в ячейках D2, D3, D5, сценарии Уткиной –изменяют значение в ячейке Е8, а сценарии Андреева – только в Е11.

Можно вывести разные сценарии одновременно и наблюдать за тем, как комбинации переменных влияют на прибыль в ячейке Е15. Возможности, возникающие при объединении сценариев – безграничны.

Создание отчета по сценарию

После объединения сценариев модель «что-если» в книге Модель магазина стала достаточно сложной. Диспетчер сценариев имеет средства построения итогового отчета.

1.Отчет Структура- итоговый отчет имеет форму структурированного списка, позволяет отслеживать все возможности модели.

2. Отчет Сводная таблица – итоговый отчет имеет форму сводной таблицы, предоставляет дополнительные средства анализа «что-если».

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

В диалоговом окне Отчет по сценарию (см. рис.8) нужно указать ячейки результата, в которых содержатся интересующие вас формулы.

Рис.8. Диалоговое окно Отчета

MS Excel создаст новый лист с именем Структура сценария и поместит туда итоговую таблицу. На рис.9 показан отчет, созданный в результате выбора опции Структура. Если вы присвоили имена изменяемым ячейкам и ячейкам результата (рекомендуется!), то в таблице будут использованы эти названия. Иначе будут показаны только адреса ячеек. Имена сценариев выводятся в качестве заголовков столбцов, а столбцы появляются в порядке определения сценариев.

Рис.9. Пример отчета объединенных сценариев

Можно добавить описание сценария в каждом столбце, введя текст в новые строки, вставленные ниже заголовков столбцов, либо создав поля надписей, либо использовав команду Вставка – Примечание.

Обратите внимание, все значения изменяемых ячеек в столбцах Е и F на рис.9 выделены серым цветом, но некоторые значения в столбце G остались невыделенными. Таким образом, можно сразу определить, какими сценариями контролируются конкретные ячейки в модели «что-если».

Обратите внимание, что выше и левее итогового отчета выводятся символы структуры, позволяющие показывать и скрывать детальные данные. Использовать символы можно аналогично тому, как вы рассматривали промежуточные итоги в лабораторной работе №6.

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

[1] При выполнении лабораторных работ № 7, 8,9 обязательно проделать все примеры по тексту работы и выполнить все задания. Не забывайте, что номер лаб. работы является именем файла, а листы книги – могут носить имена функций, примеров и заданий. Рекомендуется продумать оформление для большей наглядности.

[2] Если начальная дата является 31-м числом месяца, то она полагается равной 30-тому числу того же месяца. Если конечная дата является 31-м числом месяца и начальная дата меньше, чем 30-е число, то конечная дата полагается равной первому числу следующего месяца, в противном случае конечная дата полагается равной 30-му числу того же месяца.

[3] Напомним, что имя ячейки присваивается в поле Имя строки формул: курсор мыши поместить в поле, ввести имя и нажать <Enter>