Учебное пособие: Методические указания по лабораторным работам По дисциплине

Повторите материал по дисциплине «Аппаратные и программные средства» по разделу «электронные таблицы».

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

1. Присвойте первому рабочему листу имя первого месяца – январь. В ячейку A1 введите название вашего примера: Личный бюджет (ФИО студента).

2. В ячейки A3 – A17 введите следующий текст: Доходы, Оклад, Премии, Комиссионные, Лотерея, Дополнительный заработок, Расходы, Аренда жилья, Автомобиль, Страховка, Питание, Развлечения, Отпуск, Прочие расходы

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

4. Введите в ячейку C3 заголовок Значения .

5. В соответствующие ячейки третьего столбца введите фактические значения для каждой статьи расходов и доходов в январе. Ведите учет доходов и расходов в национальной валюте, а в ячейках столбца D сделайте их перевод в денежную единицу США.

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

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

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

8. Оформите таблицу, используя панель инструментов Форматирование . Для оформления заголовка используйте один из кириллизованных шрифтов, увеличьте кегль шрифта, оформите его полужирным курсивным начертанием. Задайте параметры форматирования для текстовых меток столбцов, подзаголовков, а также для итоговых ячеек таблицы. Установите оптимальную ширину столбцов C и D.

9. Укажите для ячеек с числовыми значениями денежный формат.

10. Задайте обрамление для некоторых ячеек.

11. Установите определенный цвет фона для ячеек, в которые не следует вводить значения (ячейки с формулами). Пример полученной таблицы смотрите на рис. 1.1.

Рис. 1.1. Пример полученной таблицы

12. Сделайте копирование полученной таблицы, для создания таблиц для других месяцев года и одной итоговой. Заполните полученные таблицы.

13. Присвойте рабочим листам новые имена – названия месяцев. Последний тринадцатый лист назовите – За год. С помощью команды Удалить контекстного меню ярлычка листа удалите лишние листы.

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

15. Сделайте отчет по лабораторной работе, проанализировав полученный годовой бюджет.

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

1. Как создать новый лист в рабочей книге?

2. Как осуществляется форматирование ячеек?

3. Как создать собственный формат данных?

4. Как произвести копирование информации с листа на лист?

5. Как найти сумму значений, расположенных на разных листах рабочей книги?

Расчет амортизационных отчислений

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

Приобретенное имущество амортизируется на протяжении определенного периода, который называется сроком амортизации. В программе он должен быть указан в аргументе, для обозначения которого используются названия Время эксплуатации, Жизнь, Время_амортизации (Life) . Стоимость в конце срока амортизации – это остаточная (ликвидационная) стоимость. Хотя эта стоимость далеко не ликвидная, ее следует указывать в аргументе Ликвидная_стоимость, Остаточная_стоимость, Ост_стоимость (Salvage). Расчет амортизационных отчислений производится для определенного периода, задаваемого аргументом Период (Per). Использование амортизационных отчислений для регулирования налоговых платежей на протяжении времени эксплуатации привело к появлению различных методов начисления величины амортизации.

План амортизации – это план начисления износа, составляемый для каждого вида имущества при приобретении. В задаче, которую предстоит решить, построенной на опыте ФРГ, демонстрируется возможность использования различных методов начисления амортизации (метода линейного списания и геометрически дегрессивного метода) при составлении плана амортизации. Важным этапом при составлении плана является, безусловно, выбор метода начисления амортизации. Вам будет предоставлена возможность определить, какой метод предпочтительнее для того или иного вида имущества. Следует отметить, что различные дегрессивные методы начисления ускоренной амортизации имущества не являются таким уж исключительным средством, о чем свидетельствует отказ некоторых стран от практики их использования. Причиной этого является несоответствие начисляемой амортизации фактическому износу и слишком неравномерное распределение налогового бремени по периодам, в частности после ликвидации образованных при начислении ускоренной амортизации резервов. Однако возможность выбора все же не будет лишней. Следует только заметить, что в плане амортизации переход от одного метода начисления амортизации к другому допускается всего лишь один раз, и только от метода начисления ускоренной амортизации к методу линейного списания. При этом начисление амортизации с помощью дегрессивного метода разрешается в том случае, если это имеет экономический смысл, то есть если фактический износ оборудования протекает аналогично заложенной в выбранном методе схеме. В зависимости от дл ительности срока амортизации для оборудования устанавливается также максимальная норма списания за один период при использовании дегрессивного метода.

Ознакомьтесь с функциями электронных таблиц для проведения амортизационных отчислений (АМГД, АМОРУВ, АМОРУМ, АМР, ДДОБ, ДОБ, ПДОБ). Опишите функции для выполнения амортизации в отчете к лабораторной лаботе.

Составить план амортизации оборудования. Для составления плана используйте два метода: линейного списания и дегрессивный метод, а затем сделайте выбор более предпочтительного метода для того или иного вида имущества. Проиллюстрируйте с помощью линейного графика выбор метода амортизации. Предприятие приобрело оборудование стоимостью $120 000. Срок амортизации оборудования 15 лет, после чего его ликвидационная стоимость будет составлять $1000.

1. Укажите в первом рабочем листе общие данные. Введите в верхней части рабочего листа название таблиц ы – Начисление амортизации – и следующие текстовые метки строк в ячейках АЗ-А6:

Начальная стоим ость, Срок амортиз ации, Максимальная норма списания, Остаточная стоимость после списания.

2. Уменьшите ширину столбца B до 2,00, после чего задайте в ячейке В6 формулу: =$D $4 и введите в ячейке С 6 слово лет.

Вышеописанная операция позволяет "автоматизировать" вставку значения количества лет после указания цифры в ячейке D4.

3. Введите, согласно условию задачи, значения для началь ной стоимости, срока амортизации, максимальной нормы списания при использовании дегрессивного метода и остаточной стоимости. Максимальная норма списания для оборудования, срок амортизации которого составляет 15 лет, равняется 20%. Поэтому укажите в ячейке D5 значение 0,2 и сформатируйте ячейку проц ентным стилем. В заключение присвойте рабочему листу имя, например Данные. Постарайтесь немного приукрасить рабочий лист с помощью различных параметров форматирования и перейдите ко второму листу рабочей книги.

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

4. Второму рабочему листу сразу же присвойте имя Расчет. В столбце В будет находиться год, для которого следует определить амортизацию, поэтому в ячейке В5 укажите слово Год, в первых двух ячейках введите значения 1 и 2 для двух первых лет, выделите обе ячейки, поместите курсор мыши на маркере заполнения и используйте функцию автоматического заполнения для ввода остальных значений (до 15).

5. В ячейки D5, F5, Н5, J5 введите текстовые метки столбцов – Балансовая стоимость. Линейное списание, Дегрессивный метод и Износ. Столбцы С, Е, G и I будут служить в нашей таблице своеобразным "декоративным" оформлением, для этого уменьшите их ширину до 1,43, ориентируясь по содержимому поля в левой части строки формул (Поле имен ). Число в левой части строки формул во время перетаскивания границы столбца указывает среднее число символов стандартного шрифта (установленного по умолчанию), необходимое для заполнения ячейки текущего столбца.

6. Задайте значение балансовой стоимости оборудования. Значение в ячейке D6 соответствует первоначальной стоимости оборудования, поэтому в этой ячейке можно указать формулу:

=Данные! $D$3

Тем самым значение из ячейки D3 рабочего листа Данные будет представл ено в ячейке D6. В дальнейшем при изменении исходных данных примера необходимые изменения следует выполнять только в листе Данные.

7. Поместите указатель ячейки на ячейку F6 и активизируйте мастер функций (команда Функция меню Вставка или мастер функций панели инструментов Стандартная) (рис.2.1). Выберите функцию АМР и нажмите кнопку Дал е е для перехода в диалоговое окно задания аргументов.

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

AM P(D6; Данные !$D$6; Данные !$D$4-B6+1)

В качестве первого аргумента следует указать значение для соответствующего года из столбца Балансовая стоимость, остаточная стоимость будет взята из листа Данные. Срок амортизации оборудования, уменьшающийся каждый раз на один год по мере начисления износа, следует записать в виде: Данные !$В $4-В6+1.

Рис.2.1. Начальное окно мастера функций

8. В ячейке Н6 укажите формулу для расчета амортизации с помощью геометрически дегрессивного метода, при этом необходимо, чтобы программа одновременно проверяла, не превышает ли определенное значение 20% балансовой стоимости. Поэтому укажите в ячейке Н6 формулу:

= ЕСЛИ (ДОБ(D6; Данны е!$D$6; Данны е!$D$4 -В6+1; 1)/D6<Данные ! $D $5; ДОБ (D6 ; Данные !$D$6; Данные!$D $4-B6+1; 1); D6*Данные!$D$5)

Для этого после вызова мастера функц ий выберите из категории Логические функцию ЕСЛИ . Вам необходимо проверить на истинность выражение: превышает ли результат вычисления функц ии ДОБ 20% балансовой стоимости или нет. Используйте мастер функций для образования вложенной функции. Если выражение истинно, то в ячейке должен быть представлен результат вычисления функц ии ДОБ. В противном случае следует указать максимально возможное значение, которое равняется произведению балансовой стоимости и максимальной нормы списания. Первые три аргумента соответствуют трем аргументам функции АМР.

9. Представим в столбце Износ большую величину амортизации. Для этого следует воспользоваться логической функцией ЕСЛИ и вставить ее в ячейку J6 в виде:

= ЕСЛИ( F6>Н6; F6; H6)

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

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

= D6-J6

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

=Данные!$D$ 6

12. В ячейке J21 для проверки результата подсчитайте сумму начисленного износа, а также сформатируйте ячейки в столбцах D, F, Н, J с помощью денежного формата.

13. Посредством рамок, цвета и параметров форматирования шрифта придайте таблиц е более "презентабельный" вид.

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

15. Процесс начисления износа представьте графически на отдельном листе. Сохраните рабочий лист под названием Износ.

16. На новом рабочем листе представьте план амортизации при сроке эксплуатации данного оборудования 8 лет и остаточной стоимости 3000.

17. Сделайте отчет по лабораторной работе.

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

1. Что такое амортизация?

2. В чем отличие методов линейного списания и геометрически дегрессивного метода при расчете амортизационных отчислений?

3. Какие функции используются для расчета линейного списания и геометрически дегрессивного метода?

4. Для чего используется мастер функций?

5. Какой из методов выгоднее использовать для расчета амортизационных отчислений?

6. Какие данные нужно использовать при расчете амортизации?

Задача выбора кредита и составления плана его погашения

Задача кредитования относится к категории рентных платежей. Под рентными платежами понимают регулярные платежи одинакового размера. Синонимом к нему является понятие аннуитет (аннуитетные платежи), применяемое для обозначения регулярно получаемого дохода одинакового размера или ежегодного платежа процентов и части основного долга, остающегося неизменным в течение всего срока погашения. При работе с функциями рентных платежей встречаются аргументы: Норма, Ставка (Rate) – процентная ставка за период; Число периодов, Кнер (Nper) – количество периодов выплаты годовой ренты; Выплата, Плата (Pmt) – размер платежа, производимого в каждый период и не изменяющегося в течение всего периода выплаты ренты; Бс, Бз (Fv) – будущая стоимость, или баланс денежных потоков, достигаемый в конце периода; Нс, Нз (Pv) – текущая стоимость будущих платежей. При использовании функции денежных потоков в качестве аргументов следует указывать, как правило, массивы данных, в которых содержатся сведения о денежных потоках. Выплаты при этом должны быть обозначены как отрицательные значения, а поступления – как положительные значения.

Ознакомьтесь с функциями электронных таблиц для вычисления рентных платежей (КПЕР, НОРМА, ОБЩПЛАТ, ОСНПЛАТ, ПЛПРОЦ, ПЗ, ППЛАТ.) Опишите изученные функции в отчете к лабораторной работе.

Порядок выполнения работы

Представьте, что Вы решили взять кредит размером в $200 000 сроком на пять лет, погашать который (основной долг и проценты) собираетесь равномерными платежами в конц е каждого года. Запросы на финансирование Вы направили в три банка, из которых пришли ответы с соответствующими условиями. Теперь Вам предстоит сравнить условия, определив эффективную процентную ставку, а также составить план погашения кредита по годам.

1. Укажите в ячейке А1 название примера – Финансирование, а в ячейке A3 подзаголовок – Выбор кредита. Сравните предложения кредитов от различных кредитных институтов, данные для которых будут представлены по строкам. Для этого укажите в ячейках В7, В9, B11 соответственно Банк 1, Банк 2 и Банк 3 . В строке 5 у нас будут представлены текстовые метки столбцов. Введите в ячейках С5, D5, Е5, F5, G5, 15, J5, К5, L5 следующие текстовые метки столбцов: Объем кредита, Выдача (%), Плата за оформление, Ставка (%), Срок (лет), Получено, Дизажио, Выплата (год), Выплата/Получено.

2. В первом столбце будет представлен объем кредита. Укажите для всех трех случаев объем кредита в $200 000. Однако предоставление кредита еще отнюдь не означает, что Вам удастся получить всю сумму полностью. Кое-что (дизажио) необходимо оставить в банке как плату за обработку и за повышенный риск, на который идет банк. Укажите в ячейках D7, D9, D11 значения 0,95, 0,96 и 0,965 и сформатируйте ячейки процентным стилем.

3. Кроме того, следует оплатить издержки, возникающие при оформлении кредита, которые в нашем примере составляют $300, $250 и $350. В столбце Ставка следует ввести значения процентных ставок, по которым банки готовы предоставить кредит: 0,12, 0,135 и 0,142. А затем сформатировать ячейки столбца процентным стилем.

4. Столбец Срок должен содержать значение количества лет, на которые предоставляется кредит, – 5 лет. Таким образом, ввод основных данных для кредита можно считать завершенным.

5. Произведите расчеты во второй части таблицы.

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

=C7* (1-D7)

Для определения полученной суммы следует вычесть из объема кредита дизажио и плату за оформление, поэтому в ячейке I 7 следует задать формулу =C7-J7-E7

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

=ППЛАТ(F7; G7; -С7)

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

7. Определите в ячейке L 7 отношение годовой выплаты к полученной сумме с помощью формулы =К 7/I7

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

8. На следующем этапе скопируйте формулы из I7, J7, К7, L7 в расположенные ниже ячейки, в которых будут произведены вычисления для предложений кредита второго и третьего банков. Присвойте рабочему листу имя Кредит , сформатируйте таблицу с помощью панели инструментов Форматирование . При этом закрасьте ячейки, предназначенные для ввода данных, голубым цветом, а ячейки, в которых значения будут вычисляться на основе формул, – желтым. Ваша таблица должна приобрести вид, показанный на рис. 3.1.

9. Во втором рабочем листе составим таблицу для того, чтобы проследить, как будет протекать погашение кредита для первого банка. Перейдите во второй рабочий лист и присвойте ему имя Погашение. В ячейку А2 введите заголовок таблицы, например План погашения. Далее введите в ячейки В4, С4 и D4 и Е4 следующие текстовые метки столбцов: Год, Погашение долга , Проценты, Остаток.

10. В первом столбце в ячейках B6-B10 y нас будут представлены значения периодов выплаты – от 1 до 5. Ячейки C6-C10 должны содержать суммы-части годового платежа, которые будут идти на погашение основного долга, ячейки D6-D10 – значения выплачиваемых процентов, а ячейки Е6-Е10 – значения остатка основного долга. Первым делом определите размер выплачиваемых в первый год процентов. Поместите указатель ячейки на ячейку D6 и задайте в ней формулу

=Кредит!$С$7*Кредит!$F$7

Рис. 3.1. Таблица выбора кредита

11. Часть годового платежа, которая в первый год уйдет на погашение основного долга, составит (ячейка С6):

=Кре дит !$K$7-D6

12. Остаток долга в конце первого года рассчитаем по формуле

= Кредит ! $С$ 7 -$С$6

13. Выплачиваемые по долгу проценты для второго года определите в ячейке D7 с помощью формулы

=Е6 * Кредит !$F$7

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

14. В ячейке Е7 следует указать формулу определения остатка основного долга:

=Е6-С7

и скопировать эту формулу в ячейки E8-E10. Программа демонстрирует Вам, как будет протекать погашение основного долга. После пятого года остаток долга должен равняться, естественно, нулю. Отформатируйте таблицу по своему усмотрению.

15. На третьем и четвертом листах составьте таблицы погашения для банков 2 и 3.

16. На первом листе сделайте вывод и обоснуйте свой выбор в пользу одного из представленных проектов.

17. Составьте таблицу погашения для кредитования сроком на
7 лет. если объём кредита равен 150000$, дизажио составляет 5%, за оформление кредита банк взимает сумму, равную 320$, процентная ставка равна 12%.

18.Сделайте отчет по лабораторной работе.

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

1. В чем заключается процесс кредитования?

2. Какие выплаты банку нужно сделать для получения кредита?

3. Какие условия учитывают при рассмотрении предложений предоставления кредита?

4. Какие функции используются для расчета выплат по кредитованию?

Определение текущей стоимости инвестиции

Эта лабораторная работа посвящена инвестиционному процессу. Главной предпосылкой нормального развития инвестиционного процесса является даже не надежная и разумная законодательная база, а, как показывает опыт, состояние кредитно-денежной системы. И хотя при "дорогих" деньгах определенная часть инвестиций все-таки найдет свое место в экономике, высокие издержки инвестирования в последующем так или иначе войдут в ц ену товаров или услуг, а инфляция на основе издержек, в свою очередь, значительно подорвет стабильность кредитно-денежной системы.

Представленная задача показывает, как путем использования специальных функций программа Excel может помочь Вам в выборе наиболее выгодного инвестиционного проекта. С помощью метода чистой текущей стоимости (net present value method), который является одним из методов так называемого динамического расчета рентабельности инвестиции, сделать это не так уж сложно. Сущность метода заключается в том, что все будущие поступления и выплаты, происходящие в течение периода инвестиции, дисконтируются (сводятся к настоящему значению). При этом предполагается, что денежные потоки имеют место в конце каждого периода (то есть фактически учитывается сальдо денежных потоков за период). В качестве процентной ставки используется ставка финансирования (если для финансирования проекта используются заемные средства) или ставка альтернативной возможности вложения капитала (при использовании для финансирования проекта собственных средств). Если чистая текущая стоимость инвестиции больше нуля, то инвестиция рентабельна. В этом случае нас интересует только абсолютная величина чистой текущей стоимости инвестиции (поскольку будущие платежи дисконтированы и предполагается, что возможности финансирования при данной процентной ставке не ограничены, относительная рентабельность инвестиции не имеет значения). Таким образом, чем больше значение чистой текущей стоимости инвестиции, тем лучше.

Ознакомьтесь с функциями электронных таблиц для вычисления рентных платежей (БЗ, БЗРАСПИС, ВНДОХ, МВСД, НПЗ, ОБЩДОХОД, РУБЛЬДЕС, РУБЛЬДРОБЬ, ЧИСВНДОХОД, ЧИСТНЗ). Опишите изученные функции в отчете к лабораторной работе.

Условия задачи

Представьте, что Вы решили расширить поле своей деятельности и наладить производство товаров, на которые имеется спрос. При этом Вам предстоит выбрать один из трех альтернативных проектов. Первоначальные инвестиции при реализации первого проекта составят $500 000, второго – $400 000 и третьего – $700 000; при этом сроки службы оборудования (период инвестиции) также различаются. Все эти значения будут введены в таблицу с исходными данными. Задача заключается в том, чтобы определить, какой из инвестиционных проектов является наиболее выгодным.

1. Начнем решение задачи с создания таблицы для помещения исходных данных. Откройте новую рабочую книгу и введите в первом рабочем листе в ячейку А1 название таблицы – Выбор проекта. В ней будут представлены не только исходные значения, но и результаты расчетов, на основании которых Вы сможете принять решение о выгодности того или иного проекта. Для того чтобы у Вас создалось примерное представление о таблице, на рис. 4.1 приведен ее вид после заполнения.

2. Представьте по строкам исходные данные и итоги для отдельных проектов: укажите в ячейках В5, B 7 и В9 текстовые метки строк Проект 1, Проект 2 и Проект 3. В строке 3 будут отображены метки столбцов, поэтому введите в ячейках D3, Е3, F3, H3 и I3 соответственно:

Инвестиция, Срок (лет), Ставка (%), Текущая стоимость, Чистая стоимость.

3. В столбце D укажите размер первоначальной инвестиции. Введите значения 500 000, 400 000 и 700 000 для трех проектов и отформатируйте ячейки денежным стилем. Дополнительные инвестиции (в случае наличия таковых) будут учитываться в балансе денежных потоков.

4. Столбец Срок (лет) должен содержать данные о периоде инвестиции – 5, 5 и 8 лет соответственно. Поскольку банкиры считаются с падением процентных ставок в долгосрочной перспективе и на рынке долгосрочных кредитов уровень процентных ставок ниже, то, естественно, они готовы предоставить кредит на 8 лет под более низкий процент.

5. Задайте в столбце Ставка (%) для первых двух проектов процентную ставку 0,13, а для третьего – 0,11. Отформатируйте ячейки процентным стилем. Основные исходные данные введены.

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

7. Перейдите ко второму рабочему листу. Можете сразу же присвоить ему имя Проект 1. Одноименный заголовок таблицы введите и в ячейке А1. В строке 3 укажите в ячейках В3, D3, F3 и Е 3 текстовые метки столбцов: Год, Баланс денежных потоков, Текущая стоимость и Текущая стоимость (Итог). Следует заметить, что мы решили отдельно определить текущую стоимость инвестиции в первом и втором рабочем листах. Если в первом рабочем листе для расчета текущей стоимости мы использовали функцию НПЗ, то во втором рассчитаем текущую стоимость денежных потоков для каждого года с помощью формулы, а затем сложим полученные значения.

8. В ячейках В4-В8 укажите значения от 1 до 5. Далее введите предполагаемые значения для денежных потоков в ячейки D4-D8
(рис. 4.2). Не забывайте, что отрицательное сальдо денежных потоков (выплаты в течение периода превышают поступления) следует указывать со знаком минус.

Рис. 4.2. Данные для первого проекта

9. Теперь определим текущую стоимость денежных потоков для каждого года. Для этого в ячейку Е4 введите формулу

=D4* (1+Выбор проекта ! $F$5) ^ (-B4)

и скопируйте ее в ячейки Е5-Е8.

10. В столбце F значение текущей стоимости инвестиц ии будет представлено нарастающим итогом. В ячейке F4 с помощью формулы =Е4 задайте представление значения текущей стоимости сальдо денежных потоков для первого года.

11. В ячейке F5 укажите формулу =F4+E5 и скопируйте ее в ячейки F6-F8. В ячейке F8 у нас представлено значение текущей стоимости инвестиции, которое должно совпасть со значением в ячейке Н5 в листе Выбор проекта, рассчитанном с помощью функции НПЗ. Закрасьте ячейки D4-D8 желты м цветом (это будет сигналом того, что в них следует ввести данные)

12. Перейдем к третьему рабочему листу, введите данные для второго проекта . Аналогично первому проекту сделайте расчет для второго проекта. Вы можете просто скопировать таблицу из листа Проект 1 и вставить в третий рабочий лист. В этом случае Вам придется только немного "адаптировать" формулу в ячейке Е4 (ту ее часть, которая касается процентной ставки (Выбор проект а! $F$7)) и значения периода (если в ссылке на ячейку указано имя листа). Данные для второго проекта можно взять из рис. 4.3.

Рис. 4.3. Данные для второго проекта

13. Те же самые операции следует осуществить для третьего проекта. Здесь необходимо привести данные для трех дополнитель ных лет. Не забудьте также указать правильный адрес ячейки для значения процентной ставки и периода. Примерные данные для проекта 3 на рис. 4.4.

Рис. 4.4. Данные для третьего проекта

14. Нам осталось перейти к рабочему листу Выбор проекта для завершения расчетов и определения наиболее выгодного проекта. Как мы договаривались, текущее значение инвестиции определим здесь с помощью функции НПЗ. Поместите указатель ячейки на ячейке Н5 и активизируйте мастер функций. Выберите функц ию НПЗ (рис. 4.5) и задайте ее аргументы в следующем виде:

= НПЗ (F5; Проек т1 ! D4: D8)

Рис. 4.5. Функция НПЗ

15. В ячейках Н 7 и Н9 укажите формулы

= НПЗ(F7; Проект2! D4: D8),

=НПЗ(F9; Проект3! D4:D11).

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

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

=H5 –D5

и скопируйте ее в ячейки I7 и I9.

17. Проанализируйте полученные результаты и напишите отчет с обоснованием в пользу одного из проектов.

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

19. Напишите отчет по лабораторной работе.

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

1. 1. Что такое инвестирование?

2. 2. На основании каких данных принимают решение о выгодности одного из проектов для инвестирования?

3. 3. Что такое сальдо денежных потоков?

4. 4. Как определяется текущая стоимость денежных потоков?

5. 5. Какие функции используются для расчета прибыли при вложении инвестиций?

Доходность ценных бумаг

Решаемая в данной лабораторной работе задача объясняет, как разместить временно свободные деньги таким образом, чтобы они приносили максимальный доход. Пример показывает, как с помощью Excel можно всегда быть "в курсе" текущей доходности ценных бумаг и принимать нужные решения об осуществлении операций с ними. Для расчетов используются два вида ценных бумаг – твёрдопроцентные и дисконтные. При расчете рендиты (дохода) ценных бумаг наибольшее значение имеет, безусловно, курс ценных бумаг. Именно он и выступает своеобразным "регулятором" рентабельности инвестиций в ценные бумаги. Поскольку господствующая на рынке процентная ставка имеет прекрасное свойство довольно часто изменяться, давая возможность заработать на хлеб большому количеству спекулянтов и консультантов, то довольно сложно предвидеть развитие рыночного процента при эмиссии. Тем более, что ставка купона (исключение составляют рентные ценные бумаги с плавающим процентом), в отличие от рыночной процентной ставки, изменяться не может. Было бы довольно неплохо при сильно снизившейся рыночной процентной ставке иметь возможность купить по номинальной стоимости ценные бумаги с высоким купоном, выпущенные несколько лет тому назад. Однако поскольку курс ценных бумаг, по логике, должен быть выше номинала, то, естественно, годовая доходность таких ценных бумаг снижается. Тем не менее довольно часто курс твердопроцентных ценных бумаг определяется сугубо иррациональными факторами: ожиданием крупных участников рынка. Например, курс ценной бумаги с долгим сроком обращения может быть больше номинала в том случае, если участники рынка считаются в долгосрочной перспективе с установлением на рынке процентной ставки ниже ставки купона. И это при том, что в данный момент ставка купона соответствует рыночной процентной ставке. Обратное утверждение (курс ниже номинала), естественно, также имеет силу.

Однако твердопроцентные ценные бумаги, естественно, не являются единственной возможностью помещения свободных денежных средств. Оставим в стороне акции и объекты недвижимости и скажем несколько слов о дисконтных ценных бумагах. Тем более, что данный вид ценных бумаг представляет довольно большой интерес для институциональных инвесторов. Ведь достаточно часто для финансирования различных краткосрочных "кассовых разрывов" и т.п. заемщик номер один – государство – прибегает к выпуску именно дисконтных ценных бумаг: казначейских обязательств и чеков. Наиболее популярными и известными дисконтными ценными бумагами на сегодняшний день являются различного рода краткосрочные обязательства министерства финансов. К дисконтным ценным бумагам принадлежит и одна из наиболее "древних" и известных ценных бумаг – вексель.

Ознакомьтесь с функциями электронных таблиц для вычислений при операциях с ценными бумагами (ДАТАКУПОНДО, ДАТАКУПОНПОСЛЕ, ДНЕЙКУПОН, ДНЕЙКУПОНДО, ДНЕЙКУПОНПОСЛЕ, ДОХОД, ДОХОДКЧЕК, ДОХОДПЕРВНЕРЕГ, ДОХОДПОГАШ, ДОХОДПОСЛНЕРЕГ, ДОХОДСКИДКА, ИНОРМА, НАКОПДОХОД, МДЛИТ, НАКОПДОХОДПОГАШ, НОМИНАЛ, ПОЛУЧЕНО, РАВНОКЧЕК, СКИДКА, ЦЕНА, ЦЕНАКЧЕК, ЦЕНАПЕРВНЕРЕГ, ЦЕНАПОГАШ, ЦЕНАПОСЛНЕРЕГ, ЦЕНАСКИДКА, ЧИСЛОКУПОН, ЭФФЕКТ). Опишите пять из изученных функций в отчете к лабораторной работе.

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

1. Создайте новую рабочую книгу и введите в ячейке А1 первого рабочего листа название нашего примера, например Доходность ЦБ, а в ячейке A3 название нашей первой таблицы – Твердопроцентные ЦБ. Соответствующее имя, например ТПЦБ, следует присвоить и рабочему листу.

2. Укажите дату, на которую производится расчет доходности ценных бумаг. Введите в ячейке А2 значение Дата, а в ячейку В2 вставьте функцию, с помощью которой в этой ячейке всегда будет представлено текущее значение даты. Укажите в ячейке A2 формулу =СЕГОДНЯ(). Предпосылкой представления желаемого результата в ячейке является правильная установка "внутренних" часов компьютера.

3. Во второй строке в информативном плане укажите также рыночную ставку процента на текущую дату. Она, естественно, должна быть введена с клавиатуры. Укажите в ячейке D2 значение Ставка, а в ячейку Е2 введите значение текущей рыночной процентной ставки 6,5% и сформатируйте ячейку процентным стилем с двумя десятичными знаками. Довольно сложно давать советы, на основании которых определяется текущая рыночная процентная ставка: в каждом конкретном случае возможны различные варианты. Однако вполне логично указывать в качестве текущей процентной ставки дисконтную или ломбардную ставки центрального банка.

4. Теперь приступим к созданию самой таблицы. В первом столбце желательно указать код ценных бумаг. Расчет сделайте для 7 ценных бумаг. Если Вы иногда открывали солидные экономические газеты развитых стран с биржевой статистикой в том месте, где представлены курсы рентных ценных бумаг, то наверняка обращали внимание на то, что каждая ценная бумага имеет определенный код. Учитывая, что рынок рентных бумаг в странах с переходной экономикой отличается скудостью и плохой структурированностью, естественно, что информационное обеспечение функционирования этого рынка также оставляет желать лучшего. Поэтому в плане кодирования ценных бумаг Вы можете дать волю своей фантазии и создать свою систему кодирования. Код мог бы содержать, например, сведения об эмитенте ценных бумаг, номере, годе эмиссии и т.п.

5. В остальных столбцах будут представлены другие важные данные для ценных бумаг – данные, которые в последующем будут использованы при определении годовой доходности. Введите в ячейках А5-15 заголовки столбцов: Код, Курс, Дата выпуска, Дата погашения, Купон, Погашение (%), Периодичность, Базис, Доход (Рендита).

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

7. Ценные бумаги в нашей задаче будут расположены (как это часто практикуется) в порядке убывания номинальной доходности. Укажите в ячейке А6 код первой ценной бумаги, а в ячейке В6 – ее текущий курс. Данные в столбце В Вам придется обновлять каждый день (или хотя бы в те дни, когда меняется курс). В данном примере для нас не имеет значения номинал ценной бумаги, поскольку и текущий курс, и курс при погаш ении указываются в процентах от номинальной стоимости.

8. Введите в ячейки С6 и D6 даты выпуска и погашения ценных бумаг. Для простоты представим ценные бумаги со сроком обращения 5 и 10 лет.

9. Столбец Е будет содержать данные о номинальной процентной ставке.

Выделите этот столбец с помощью щелчка на его заголовке, выберите команду Ячейки меню Формат и в разделе Число выберите из списка Числовые форматы элемент Процентный, а также задайте отображение двух десятичных знаков (рис.5.1). Теперь для задания процентной ставки 7% достаточно ввести с клавиатуры просто 7, а не 0,07, что позволит немного сэкономить драгоценные ресурсы.

Рис. 5.1. Диалоговое окно форматирования таблицы

10. В ячейке F6 укажите число 100 (как правило, большинство рентных ценных бумаг выкупаются по их номинальной стоимости).

11. Столбец G будет содержать сведения о периодичности выплат процентов. Если выплата производится раз в год, то следует указать 1 , если раз в полгода – 2, если ежеквартально – 4.

12. Вот мы и подошли к самому интересному месту в данной задаче: вводу формулы для определения годового дохода (рендиты) по ценной бумаге для оставшегося срока обращения. Воспользуемся функцией ДОХОД (YIELD). Однако как и в предыдущих примерах, во избежание представления значений ошибки в ячейках предварительно зададим с помощью функции ЕСЛИ непредставление значения в ячейках столбца I в том случае, если комплект данных не введен полностью. Укажите в ячейке I6 формулу

=ЕСЛИ (Н6="";"";ДОХОД ($В$2; D6; Е6; В6; F6; G6; Н6)

13. Скопируйте формулу из ячейки I6 в остальные ячейки столбца I .

14. Введите данные для 7 ценных бумаг. Пример полученной таблицы представлен на рис.5.2. Сделайте для себя определенные выводы, для этого обратитесь к теории в начале лабораторной работы.

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

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

16. Третью таблицу для дисконтных ценных бумаг постройте по аналогии с первой. Перейдите в третий рабочий лист файла, присвойте ему имя, например ДЦБ, и введите в ячейке название примера – Доходность ЦБ, а в ячейке A3 название третьей таблицы – Дисконтные ЦБ. Затем Вы можете скопировать из первого рабочего листа заголовки столбцов таблицы и поместить их в строку 5 третьего рабочего листа. Однако во втором примере нам понадобятся не все столбцы, поэтому удалите с помощью контекстного меню столбцы, содержащие сведения о номинальной доходности (столбец Купон) и периодичности выплат по купону (столбец Периодичность). Отформатируйте ячейки, придав таблице презентабельный вид.

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

17. Во второй строке таблицы представьте те же данные, что и во второй строке предыдущей таблицы. Введите в ячейку А2 текст Дата:, а в ячейке В2 укажите функцию =СЕГОДНЯ(). Напоминаем, что данная таблица, как, впрочем, и первая, должна служить для проведения расчета доходности ценных бумаг на текущую дату, поэтому значение текущей даты выступает в качестве даты приобретения ценных бумаг (Дата_соглашения) .

18. В ячейку D2 введите значение Ставка, а в ячейке Е2 укажите значение текущей рыночной процентной ставки 6,50%.

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

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

20. Введите формулу в ячейку G6. Задайте, как и в предыдущей таблице, непредставление значений в ячейках столбца G в том случае, если курс ценной бумаги не указан. Для расчета годовой доходности дисконтных ценных бумаг мы будем использовать функцию ДОХОДСКИДКА (YIELDDISC). Укажите в ячейке G6 формулу

=ЕСЛИ (В6=" "; " "; ДОХОДСКИДКА($В$2; D6; В6; Е6; F6)

и скопируйте ее в остальные ячейки столбца G.

21. Введите данные для других ценных бумаг. Если Вы еще этого не сделали, закрасьте ячейки таблицы (как описано в предыдущих примерах), в которых должны быть введены данные (Курс , Ставка ) голубым цветом, а ячейки, значения в которых будут определены на основе формул (Доход , Дата ), – желтым. Пример полученной таблицы представлен на рис. 5.3.

Рис. 5.3. Вид таблицы после заполнения

22. Итак, Вы проделали основную часть работы. Хотя вряд ли ее можно назвать "основной", поскольку это всего лишь подготовка к более важному и ответственному этапу – осмыслению представленных в таблице данных и принятию решения.

23. Для того чтобы было легче принять решение, на четвертый лист скопируйте "сведения" из двух таблиц с первого и третьего листов в одну и отсортируйте комплекты данных для ценных бумаг в порядке убывающей доходности.

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

25. Напишите отчет по лабораторной работе.

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

1. Какие типы ценных бумаг вы знаете, в чем их отличие?

2. На основании чего формируется доход, приносимый ценной бумагой?

3. Как влияет дата погашения на доход от реализации ценных бумаг?

4. Как произвести сортировку данных в таблице?

5. На основании какого показателя выбирают ценные бумаги для приобретения?

Рентабельность рекламной кампании

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

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

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

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

1. Создание таблицы

1.1. Откройте новую рабочую книгу и введите в ячейку А1 название таблицы (например Расчет рентабельности рекламной кампании ), а также задайте соответствующее начертание и размер шрифта. Чтобы Вы имели представление об окончательном результате работы, представим на рисунке полностью заполненную и оформленную таблицу.

Рис. 6.1. Заполненная таблица расчета

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

1.3. В пятой строке таблицы укажите заголовки столбцов. Введите последовательно в ячейках А5-Н5 заголовки столбцов: Месяц, Расходы, Текущая стоимость, Расходы (Итог), Сумма покрытия, Текущая стоимость, Доходы (Итог), Сальдо.

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

1.5. Теперь можно приступать к вводу данных. Вместо названий месяцев в столбце А укажите числовые значения от 1 до 12 , что позволит впоследствии использовать эти значения в формуле. Для этого используйте механизм автозаполнения. Введите в ячейки А6 и А7 значения 1 и 2 , выделите диапазон из двух ячеек, поместите курсор вплоть до ячейки А17.

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

1.7. Поскольку расходы на рекламу осуществлялись в течение нескольких месяцев, то в этом случае целесообразно вести вместо статического динамический инвестиционный учет. А это предполагает сведение всех будущих платежей и поступлений путем дисконтирования на сумму рыночной процентной ставки к текущему значению. Первый платеж, естественно, не должен быть дисконтирован. Если предположить, что платежи осуществляются в разные промежутки времени, то платеж за февраль следует дисконтировать как платеж первого периода, поэтому в ячейку С6 вставьте следующую формулу расчета текущей стоимости расходов на рекламу для каждого месяца:

=В6*(1+$В$3/12)^(-$А6+1)

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

1.9. Сделайте вывод по полученному результату.

1.10. В заключении нужно представить общий объем расходов на рекламу за год нарастающим итогом. Первый платеж включим в общую сумму с помощью формулы =С6 в ячейке D6 . Задание формулы в этой ячейке в нужном нам виде приведет к появлению значения ошибки. В ячейке С7 укажите формулу

=D6+C7

1.11. Скопируйте вставленную формулу в ячейки D8–D17 . Поскольку после мая расходов на рекламу не было, то в ячейках определения нарастающего итога для мая-декабря представлено одно и то же значение. Таким образом, мы более-менее разобрались с расходами на рекламную кампанию и теперь попытаемся подсчитать, какой она принесла доход.

1.12. В качестве ключевого показателя целесообразности инвестиций в рекламу можно выбрать сумму покрытия, которая представляет собой разность между ценой товара и переменными издержками. Таким образом, она определяет, сколько приносит продажа единицы товара в копилку возврата инвестиций. В нашем примере мы укажем общее значение за месяц. Естественно, при расчете окупаемости рекламной кампании в реальной жизни данный пример должен опираться на солидный материал первичного учета. При этом в задаче мы учитываем только сумму покрытия, полученную при увеличении сбыта в результате проведения рекламной кампании. В январе реклама не дала еще результатов, и показатель объема продаж находился на уровне предыдущих месяцев. Однако уже в феврале можно констатировать увеличение сбыта. Поскольку рекламная кампания была уже практически завершена в мае, то в ноябре и декабре последствия ее проведения уже не ощущались. Введите желаемые значения в ячейки Е7–Е15 для остальных месяцев.

1.13. Затем задайте формулы для расчета текущей стоимости поступающих доходов. Однако нет необходимости снова вводить формулу. Достаточно скопировать формулу из ячейки С6 в ячейку F6 . Поскольку в формуле заданы смешанная и абсолютная ссылки, то сейчас нет необходимости производить в ней какие-либо изменения. Скопируйте с помощью функции автозаполнения формулу из ячейки F6 в ячейки F7–F17

1.14. В столбце G будет отображена сумма покрытия нарастающим итогом за год. Возможно, заголовок Доходы(Итог) не совсем соответствует нашему замыслу, однако оставьте его с целью использования в дальнейшем при построении диаграммы. Как и в случае с расходами на рекламу, значение из ячейки F6 представьте в ячейке G6 без изменений: в ячейке G6 укажите выражение =F6 , нажатием клавиши [Enter] переведите указатель ячейки на ячейку G7 и введите формулу

=G6+F7

1.15. Далее с помощью функции автозаполнения скопируйте формулу в ячейки G8-G17 . В последних трех ячейках столбца будет представлено одно и то же значение, так как результаты рекламной кампании за последние три месяца на сбыте продукции уже не сказывались.

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

=F6-C6

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

1.18. После этого необходимо перевести указатель ячейки в ячейку Н7 и ввести в нее формулу

=Н6+(F6-C6)

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

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

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

2. Создание диаграммы

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

Выделите в таблице диапазоны ячеек D5-D17 и G5-G17 и выберите в меню Вставка команду Диаграмма/На новом листе . Перед текущим рабочим листом будет вставлен отдельный лист диаграмм, а на экране появится первое диалоговое окно мастера диаграмм. Подтвердите в нем предлагаемый программой исходный диапазон нажатием кнопки Далее .

2.2. Во втором диалоговом окне выберите один из типов диаграммы. Советуем отдать предпочтение типу График , а затем, нажав кнопку Далее , продолжить работу. Среди предлагаемых в третьем диалоговом окне видов графика выберите вид под номером 4 и нажмите кнопку Далее.

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

2.4. В последнем диалоговом окне мастера диаграмм укажите название диаграммы, например Реклама: расходы и доходы , название оси Х – Месяц и название оси Y – Итог по месяцам. Не забудьте также задать в этом диалоговом окне добавление легенды. Через несколько мгновений после закрытия пятого диалогового окна нажатием кнопки Готово созданная диаграмма будет представлена в листе диаграмм (рис. 6.2).

Рис. 6.2. Созданная диаграмма

Созданная диаграмма дает наглядное представление об эффективности расходов на рекламу. Поэкспериментируйте с применением различных способов форматирования диаграммы.

2.5. Поэкспериментируйте с суммами вкладов на проведение рекламной кампании и выберите наиболее выгодный для фирмы.

2.6. На новом рабочем листе составьте отчет руководителю фирмы о рентабельности проведенной рекламной кампании, используя выполненные расчеты и построенные диаграммы.

2.7. Сделайте отчет по лабораторной работе.

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

1. В каких целях проводится рекламная кампания?

2. Как рассчитывается фирмой прибыль от проведения рекламной кампании?

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

Учет работы с клиентами в торговой фирме

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

Изучите функции для работы со списками (ВЫБОР,ПОИСКПОЗ, ПРОСМОТР, ГИПЕРССЫЛКА). Опишите изученные функции в отчете к лабораторной работе. Ознакомьтесь с возможностями Excel формирования сводных таблиц.

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

1. Создание списка клиентов

1.1. Для создания списка следует использовать обычный рабочий лист. Сразу же присвойте ему подходящее имя. Это может быть Клиенты . Введите в первую строку названия полей первого списка. Укажите в ячейках A1 -I1 следующие названия: Название фирмы, Код, Контактная персона, Индекс, Город, Улица, Телефакс, Телефон, Скидка(%). После ввода названий полей измените ширину столбцов и отформатируйте последнее поле процентным форматом. Выделите цветом строку заголовка.

1.2. Для ввода данных воспользуйтесь специальным диалоговым окном – формой данных . Для этого необходимо в меню Данные выбрать команду Форма. В диалоговом окне формы данных рядом с названиями полей создаваемого списка находятся поля ввода, в которые нужно вводить данные (см рис.7.1). Введите в соответствующие поля данные о клиентах, завершая ввод каждой записи нажатием кнопки Добавить . Переход между отдельными полями ввода осуществляется посредством щелчка мыши или нажатием клавиши Tab . После ввода последней записи щелкните на кнопке Закрыть.

Рис. 7.1. Диалоговое окно формы данных

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

2. Создание списка товаров

2.1. Второй список нашего примера будет содержать данные о предлагаемых фирмой товарах. Каждому товару следует присвоить определенный номер, что в последующем поможет нам автоматизировать выполнение определенных операций. Создаваемый список в рабочем листе с названием Товары состоит из полей: Номер, Наименование товара и Цена. Введите их в ячейки А1-С1 и сразу же присвойте имена ячейкам столбцов А, В, и С – Номер, Товар и Цена соответственно.

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

Рис. 7.3. Список товаров

3. Создание списка заказов

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

3.1. Создайте структуру списка. Для этого в ячейках A1-L1 укажите следующие названия полей: Месяц, Дата, Номер заказа, Номер товара, Наименование товара, Количество, Цена за ед., Код заказчика, Название фирмы, Сумма заказа, Скидка, Уплачено.

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

3.3. Как и в предыдущих рабочих листах, присвойте ячейкам некоторых столбцов имена. Выделите по очереди столбцы В, С, D, Е, F, G, Н, I, J, К, L и введите в поле имени имена: Дата, Заказ, Номер2, Товар2, Количество, Цена2, Код2, Фирма2, Сумма, Скидка2 и Оплата.

3.4. Сами данные в список вводить пока не будем, а только определим нужные форматы и то, какие значения в каких полях должны быть указаны. Впоследствии Вы можете вводить данные о заказах как с помощью формы данных, так и непосредственно в самом рабочем листе. В ячейках поля Месяц мы будем указывать названия месяца. Столбец В предполагается использовать для ввода даты выполнения заказов. С вводом дат повремените, а пока выделите столбец В с помощью команды Ячейки меню Формат , в открывшемся на экране одноименном диалоговом окне активизируйте раздел Число и выберите в категории Числовые форматы/Дата желаемый формат даты. Третий столбец должен содержать номер заказа.

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

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

=ЕСЛИ ($D2=””; ””; ПРОСМОТР ($D2; Номер; Товар)

Данная формула требует небольших пояснений. Функция ЕСЛИ проверяет содержимое ячейки D2. Если в ячейке D2 данные отсутствуют, то ячейка Е2 также останется незаполненной. Если же в ячейку D2 уже введен номер товара, то будет выполняться поиск номера товара в диапазоне Номер и в ячейку D2 возвратится соответствующее значение диапазона Товар. Для этого мы и используем функцию ПРОСМОТР . Вставьте приведенную формулу в ячейку E2 с помощью мастера функций, если Вы не уверены в том, что аргумент в ячейке указан верно. Обратите внимание, что для ячейки D2 задана комбинированная ссылка, при копировании формулы это приведет только к изменению номера строки.

В ячейки столбца F следует ввести заказываемое клиентом количество того или иного товара.

3.6. В столбце G (поле Цена за ед.) укажите цену единицы товара. Поскольку цена у нас уже встречалась, то ее вставку можно задать с помощью формулы, аналогичной вставленной в ячейку E2. Формула в ячейке F2 должна иметь вид

=ЕСЛИ($D2=""; ""; ПРОСМОТР($D2; Номер; Цена)

Вполне целесообразно скопировать формулу из ячейки E2 в ячейку G2 и затем только изменить имя диапазона. Значение "пробел", представленное в ячейке в качестве результата применения формулы, убедит Вас в ее правильности.

3.7. В ячейке Н2 следует указать код фирмы-заказчика. Код придется ввести с клавиатуры, поскольку нельзя заранее предположить очередность заказов и задать автоматическое (с помощью одной из формул Excel) заполнение ячеек этого столбца.

3.8. Задайте автоматическое заполнение ячеек полей Название фирмы и Скидка с помощью формулы, аналогичной той, которую уже использовали. Но теперь в качестве отправного пункта будет выступать значение в ячейке I2. Введите в ячейку I2 формулу

=ЕСЛИ($Н2=""; ""; ПРОСМОТР ($Н2; Код; Фирма)

3.9. В поле Сумма заказа укажите общую стоимость заказа без учета скидок. Для этого следует перемножить значения в полях Количество и Цена. Можно также с помощью логической функции ЕСЛИ задать незаполнение ячеек в том случае, когда запись не введена, что позволит избежать появления значений ошибки. Поэтому формула в ячейке J2 должна иметь вид

=ЕСЛИ(F2=""; ""; F2*G2)

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

3.10. Величину скидки (поле Скидка) также можно определять автоматически. Для этого достаточно ввести в ячейку К2 формулу

= ЕСЛИ ($H2=""; ""; ПРОСМОТР ($H2; Код; Скидка)

3.11. Определим сумму, подлежащую оплате. Для этого укажите в ячейке L2 следующую формулу

= ЕСЛИ (J2=""; ""; J2-J2*K2)

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

3.13. Выделите ячейки B2-L2 и выберите в меню Правка команду Заполнить/Вниз. Тем самым Вы зададите копирование значений ячеек строки 2 в остальные ячейки.

3.14. Теперь все готово к вводу записей. Введите 10 записей. После ввода записей для первых трех месяцев (дней, недель, лет) может возникнуть необходимость подвергнуть данные списка фильтрации, чтобы легко можно было определить, насколько популярным был тот или иной товар, какой клиент обращался в течение этих месяцев чаше всего или выделить заказы, сумма которых превышает определенное значение. Используйте для этих целей функцию автоматической фильтрации. Вставьте автофильтр в список.

4. Создание бланка заказа

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

4.1. Подготовьте свой вариант бланка, начав с выбора шрифта. В списке Шрифт панели инструментов Форматирование выберите новый вид шрифта.

4.2. Теперь можно приступить к созданию самого бланка. Обратите внимание, что все четные строки листа не заполняются. Поместите указатель ячейки на ячейку D3 и введите Заказ N. Номер заказа следует указать в ячейке ЕЗ, при желании его можно подчеркнуть. Для этого в списке Линии рамки установите обрамление ячейки рамкой снизу. Не забывайте во время работы при необходимости изменять ширину столбцов. В ячейку F3 введите от и уменьшите ширину столбца. В ячейке G3 будет представлена дата заказа, которую мы вставим с помощью формулы

=ЕСЛИ($Е$3=""; ""; ПРОСМОТР($Е$3; Заказ; Дата)

Подчеркните вставляемое с помощью формулы значение, проведя нижнюю линию обрамления. Значения в строке 3 должны иметь полужирное начертание и шрифт размером в 14 пунктов.

4.3. Перейдите к оформлению второй строки бланка. В ячейку С5 введите текст Название фирмы-заказчика. При этом старайтесь ввести текст таким образом, чтобы он заполнил ячейки С5 и D5. Для названия фирмы мы отвели ячейки Е5, F5, G5. Чтобы при заполнении заказа название фирмы вставлялось автоматически, помести те в яч ейку E5 формулу

=ЕСЛИ($Е$3=""; ""; ПРОСМОТР($Е$3; Заказ; Фирма2)

Если Вы помните, для удобства и упрощения работы диапазонам ячеек базы данных Заказы были присвоены имена. Подчеркните название фирмы и расположите его по центру диапазона из трех ячеек. В ячейку Н5 введите слово Код, а в ячейку I5 поместите формулу

=ЕСЛИ($Е$3=""; ""; ПРОСМОТР($E$3; Заказ; Код2)

4.4. Теперь займитесь оформлением третьей строки бланка. В ячейку С7 введите текст Наименование товара, а для ячеек Е7, F7 и G7 примените подчеркивание и центрирование. Ячейка E7 должна содержать формулу

=ЕСЛИ ($Е$3=""; "", ПРОСМОТР ($E$3; Заказ; Товар2)

В ячейку H7 введите N (с имвол номера), а в ячейке I7 укажите фор мул у

=ЕСЛИ($Е$3=""; ""; ПРОСМОТР ($Е$3; Заказ; Номер2)

и п римените подчеркивание для помещаемого в ячейку I7 значения.

4.5. Четвертая строка бланка должна содержать сведения о количестве и цене заказываемого товара. В ячейку С9 введите текст Заказываемое количество. Для самого значения следует з арезервировать ячейку Е9. Значение будет вставляться автоматическ и, е сли в ячейку E9 ввести формулу

= ЕСЛИ ($E$3=""; ""; ПРОСМОТР ($Е$3; Заказ; Количество)

Так же как и для всех полей бланка, предназначенных для ввода переменной информации, проведите под этой ячейкой нижнюю лини ю обрамления. В ячейку F9 введите с клавиатуры: ед. по цене, выровняйте введенный текст по центру столбц ов F и G . Я чейка Н9 должна содержать формулу

=ЕСЛИ ($E$3""; ""; ПРОСМОТР ($E$3; Заказ; Цена2)

К этой ячейке следует применить подчеркивание и денежный стиль. Над четвертой строкой блан ка в ячейку I9 поместите текст за ед.

4.6. В ячейку C11 введите текст: Общая стоимость заказа , а в ячейку Е11 по местите фор мулу

= ЕСЛИ ($E$3=""; ""; ПРОСМОТР($Е$3; Заказ; Сумма)

и задайте для ячейки параметры форматирования: нижн яя линия обрамления и де нежный стиль. В ячей ку F11 введите: Скидка (%), выделите ячейки F11, G11 и H11 и выполните щелчок на кнопке Центрировать по сто лбц а м. В ячейку I11 поместит е фор мулу

= ЕСЛИ ($E$3=""; ""; ПРОСМОТР ($E$3; Заказ; Скидка2)

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

4.7. Теперь приступим к работе над последней строкой бланка. Введите в ячейку С13 текст: К оплате , а в ячейку D13 поместит е формулу

= ЕСЛИ ($E$3=""; ""; ПРОСМОТР ($E$3; Заказ; Оплата)

и вновь задайте для ячейки следующие параметры форматирования: обрамление рамкой снизу и денежный стиль. В заключение укажите фамилию лица, оформлявшего заказ. В я чейк е Е13 введите: Оформил, выделите ячейки Е13, F13 , задайте центрирован ие текста по столбцам. Затем выделите ячейки G13, Н13 и I13, задайте для них центрирование текста по столбцам и обрамление рамкой снизу.

4.8. Закончите форматирование, улучшив внешний вид документа и обеспеч ив его однородное оформление . Установите ширину столбцов В и J, равную 1,75, выделите диапазон ячеек B2-J14 и задайте обрамление всего диапазона.

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

Рис. 7.5. Созданный бланк заказа

5. Анализ данных с помощью сводной таблицы

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

5.1. Перейдите в пятый рабочий лист и присвойте ему имя Таблица. В этом рабочем листе и будет разме щена сводная таблица.

5.2. Активизируйте меню Данные и выберите команду Сводн ая таблица . Поскольку после имени команды следует троеточие, то в результате ее активизации откроется диалоговое окно – первое окно мастера сводных таблиц. В этом окне Вам будет предложено выбрать один из четырех источников данных для сводной таблицы. Одобрите выбор, сделанный мастером: опцию В списке или базе данных Microsoft Excel и нажмите кнопку Далее.

Рис. 7.6. Окно мастера сводных таблиц – выбор источника

5.3. Укажите диапазон ячеек выбранного источника данных, который будет использован в сводной таблице. Если бы в момент вызова мастера сводных таблиц указатель ячейки находился внутри списка заказов (в рабочем листе Заказы), то Excel автоматически поместил бы нужный диапазон в поле ввода Диапазон. Однако поскольку мы уже перешли в рабочий лист Таблица, то теперь, поместив курсор ввода в поле Диапазон, нам следует возвратиться в рабочий лист Заказы и выделить диапазон A1-L21.

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

5.5. В область страниц поместите поле данных Месяц, а в область строк – поля Наименование товара, Количество и Сумма заказа. В результате в строках после наименования товара будут отображены количество проданных единиц и общая сумма заказа на данный товар в указанном месяце (рис. 7.7). Теперь следует определить, по какому полю подсчитывать сумму. Поместите в область данных поле Сумма заказа, после этого в области данных появится кнопка Сумма по полю, Сумма заказа. Если Вы захотите задать выполнение другой операции обработки данных в текущем поле, следует выполнить двойной щелчок мышью на имени поля в окне мастера сводных таблиц. На экране появится диалоговое окно Вычисление поля сводной таблицы, в котором следует выбрать желаемую операцию.

Рис. 7.7. Диалоговое окно создания сводной таблицы

В этом же окне Вы можете задать параметры форматирования ячеек (для этого следует нажать кнопку Формат), а также выполнение дополнительных операций обр аботки данных в текущем поле (нажав кнопку Дополнительно и указав нужные параметры). С помощью двойного щел чка на имени поля в диалоговом окне мастера таблиц можно изменить параметры для любого поля. В частности, после двойного щелчка на поле Количество выберите в появившемся диалоговом окне переключатель Нет, чтобы в таблице не отображались промежуточные итоги. Обращайте внимание на то, что при задании операций обработки для полей данных в области строки или столбца сводная таблица может получиться слишком громоздкой. Если структура таблицы Вас устраивает, нажмите кнопку Далее и перейдите в последнее окно мастера сводных таблиц. Изображение флага в этом окне информирует о том, что близок конец работы над созданием сводной таблицы. Выберите ячейку рабочего листа Таблица, с которой должна начинаться сводная таблица, присвойте таблице соответствующее имя и подтвердите установленные здесь остальные параметры нажатием кнопки Готово. Через несколько минут сводная таблица появит ся в рабочем листе.

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

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

5.6. Выбирая из списка Месяц (который выступает в качестве фильтра) нужное значение, можно отобразить данные о продажах для каждого отдельного месяца. В качестве фильтра можно использовать и несколько полей, для чего следует лишь поместить нужное поле в область страницы. Переместите поле Название в область страницы непосредственно под полем Месяц, таким образом, можно использовать значения двух этих полей в качестве фильтра показа.

5.7. Комбинирование полей, задание дополнительных операций обработки данных – все это и многое другое делает сводные таблицы, безусловно, незаменимыми помощниками при анализе данных списка. Просмотрите таблицу и определите, какой товар принес самый большой оборот. Поле Наименование товара вновь поместите в область строк, а из списка поля Меся ц выберите значение Все. Определите, какой из товаров является лидером продаж. Сводные таблицы предоставляют больш ие возможности для творчества, поэкспериментируйте со сводной таблицей, представ ляя данные сп иска в самых неож иданных ракурс ах.

5.8. На отдельном листе сделайте выводы на основе анализа сводных таблиц о работе с клиентами фирмы.

5.9. Напишите отчет по лабораторной работе.

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

1. Какие операции обработки списков возможно выполнить с помощью электронной таблицы?

2. Для чего используются сводные таблицы?

3. Опишите технологию построения и редактирования сводной таблицы.

4. Какие функции используются для выборки информации из списка?

5. Как сводная таблица помогает в анализе данных?

Анализ деятельности филиалов фирмы на основе консолидации данных и сводных таблиц

Любое предприятие со временем расширяется и распространяет свою деятельность на другие "города и веси". Если с умом поставить управление филиалами и наладить определенный контроль, то дело может стать очень полезным и прибыльным. Это должно подвести Вас к мысли о необходимости разумного "воспитания" и управления детьми – дочерними фирмами. Как осуществлять контроль за их деятельностью, какую помощь при этом может предложить Excel – этому вопросу посвящена данная лабораторная работа. Ранее Вы уже имели возможность частично соприкоснуться с предметом нашего разговора – сводными таблицами. Второй темой, рассматриваемой в этой работе, является консолидация данных. Именно с помощью этих двух механизмов можно свести данные воедино, проанализировать деятельность филиалов, определить скользкие места и на основе полученной информации предложить пути их устранения. Изложение в данной работе будет построено на основе лабораторной работы № 7.

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

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

3. Сводная таблица на основе диапазонов консолидации

1. Ввод данных

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

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

1.3. Введите название филиала в ячей ку А1 и сформатируйте ее соответствующим образом.

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

1.5. В строке 3 укажите также другие метки столбцов, а именно: в ячейках D3, ЕЗ, F3 введите соответственно Количество заказов, Проданное количество и Объем продаж. Задайте также центрирование текста в ячейках и разрешите перенос текста по строкам.

1.6. Поместите в ячейку F4 формулу =С4 *Е4 и скопируйте ее в ячейки F5-F14. Тем самым создание структуры таблицы будет завершено.

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

1.8. Введите данные для каждого филиала. Представьте данные для пяти филиалов, расположенных в разных городах.

1.9. В завершение нужно подбить итог по объему про даж каждого филиала, для этого введите в ячейку В15 слово Всего, а в ячейку F15 вставьте формулу суммы.

2. Консолидация данных

2.1. Перейдите в шестой лист рабочей книги и поместите указатель ячейки на крайнюю левую ячейку таблицы, которая будет содержать консолидированные данные – ячейку A3. Поскольку номер и название товара у нас остаются неизменными и, естественно, не должны быть подвергнуты операции сложения, то можно скопировать эту часть таблицы из рабочего листа, например Киев. Для этого выделите в рабочем листе Киев диапазон ячеек АЗ-В14, выберите команду Копировать меню Правка, перейдите в шестой рабочий лист (присвойте ему имя, например Итог), поместите указатель ячейки на ячейке A3 и выберите команду Вставка меню Правка. Скопированный фрагмент таблицы будет вставлен в новом месте. При желании можете сразу же адаптировать параметры ячеек к потребностям вставленных текстовых данных.

2.2. Приступим непосредственно к операции консолидац ии данных.

2.2.1. Поместите указатель ячейки на ячейку СЗ и выберите в меню Данные команду Консолидация. Через несколько мгновений на экране появится одноименное диалоговое окно.

2.2.2. В списке Функция выберите операцию, которая будет выполняться над консолидируемыми данными. Выберите операцию сложения (элемент Сумма) .

2.2.3. Перейдите на поле ввода Ссылка. В нем следует указать диапазоны ячеек, данные из которых должны быть подвергнуты процессу консолидации. Диапазон ячеек проще всего можно указать, используя манипулятор. Поместите курсор ввода в поле Ссылка , выполните щелчок мышью на ярлычке листа Киев, выделите диапазон яче ек D3-F14 и нажмите в диалоговом окне Консолидация кнопку Добавить. Указанный диапазон тотчас же будет представлен в поле Список диапазонов. Нажатием кнопки Удалить ненужный диапазон может быть из этой области удален (перед нажатием кнопки ненужный диапазон следует выделить в поле Список диапазонов).

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

2.2.5. Установите необходимые опции, находящиеся в диалоговом окне Консолидация . Если верхняя строка и/или левый столбец содержат заголовки столбцов или строк, которые необходимо скопировать в итоговую таблицу, следует включить соответствующие опции в группе Использовать метки . Поскольку в нашем примере верхняя строка содержит заголовки столбцов, то нужно активизировать опцию В верхней строке .

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

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

Рис. 8.1. Консолидированная таблица

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

2.4. Определим сумму по всем филиалам. Для этого укажите в ячейке В70 значение Всего, а в ячейке Е70 поместите формулу вычисления суммы. Excel сам выделит нужный диапазон и после нажатия клавиши Enter выдаст сумму значений выделенного диапазона.

2.5. Для более наглядного представления данных в столбце F уместно было бы привести значения долей отдельных продуктов в общем объеме продаж. В ячейку F9 введите формулу =Е9/$Е$70 и скопируйте ее в остальные ячейки столбца F (вплоть до ячейки F70). Полученные результаты позволят сделать недвузначные выводы о популярности того или иного товара.

2.6. При определении долей Вы уже, наверное, обратили внимание на странные адреса ячеек. Все дело в том, что при консолидации данных программа записывает в итоговой таблице каждый элемент и автоматически создает структуру документа. Структурирование (создание структуры) документа позволяет добиться представления на экране только необходимой информации и скрыть ненужные детали. Слева от таблицы представлены так называемые символы структуры. Цифрами обозначены уровни структуры. Нажатие кнопки со знаком плюс позволит расшифровать данные высшего уровня структуры. Нажмите, например, кнопку для ячейки А9, чтобы получить информацию об отдельных заказах. В качестве альтернатив ы для выполнения этой операции можно воспользоваться командой Структура/Показать детали меню Данные .

2.7. Поскольку ячейки с данными отдельных заказов для первого товара у нас не были учтены при определении долей , то имеет смысл после расшифровки с помощью функции автоматического запо лне ния скопировать формулу из ячейки F9 в ячейки F4-F8. Скрыть детали можно посредством щелчка на кнопке с изобра жением знака минус или путем выбора команды Структура/Скрыть детали меню Данные. Эта команда предназначена для создания структуры из выделенных строк или столбцов.

2.8. Структурирование листа пользователь может задать и сам, предварительно разбив данные рабочего листа на группы. Для создания вертикальной структуры (которая имеется и в нашем примере) следует выделить несколько строк (речь может идти только о смежных выделениях), а затем актив изировать команду Структура/Сгруппировать меню Данные . Целесообразно сгруппировать данные о продажах товаров одного типа, например: компьютеров, принтеров и мониторов. Путем выделения столбцов можно создать горизонтальную структуру. Разгруппировываются данные с помощью выбора одноименной команды в меню Данные после выделения ст рок (столбцов), которые подлежат разгруппированию. Самым простым способом создания структуры является выбор команды Структура/Автоструктура меню Данные . В этом случае программа сама создаст структуру таблицы.

На отдельном листе создайте горизонтальную структуру, объединив группы товаров по типу, например: техника, продукты, галантерея и т.д.

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

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

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

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

3.3. После нажатия кнопки Далее на экране появится окно Мастер сводных таблиц шаг 2а из 4 . В нем выберите элемент Создать поля страницы , что позволит присвоить имя диапазонам для каждого филиала.

3.4. После нажатия кнопки Далее перейдите во вторую часть второго диалогового окна мастера сводных таблиц. Перед указанием диапазонов консолидации активизируйте опцию 1 , расположенную под полем Список диапазонов . Эта опция задает число полей страницы сводной таблицы. После выполнения эт ой операции становится доступным поле ввода Первое поле .

3.5. Затем переведите курсор ввода в поле Диа п азон и с помощью мыши выделите диапазон ячеек B3-F14 в первом рабочем листе (лист Киев). Нажмите кнопку Добавить и аналогичным образом представьте в поле Список диапазонов адреса диапазонов с данными из других рабочих листов.

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

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

3.8. Нажмите кнопку Далее, чтобы добавить несколько последних штрихов в создаваемую Вами сводную таблицу. Эти штрихи заключаются в отключении опции Общие итоги по строкам в группе Параметры сводной таблицы и вводе (при желании) названия таблицы и нажатии кнопки Готово .

3.9. Подождите несколько мгновений и, если все выполнено правильно, на экране появится результат Вашего труда (рис. 8.2).

Рис. 8.2. Сводная таблица на основе консолидации

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

3.10. Однако сводная таблица не была бы сводной таблицей, если бы на этом ее возможности исчерпывались. Главной характеристикой сводной таблицы является ее интерактивность. И именно это свойство можно сейчас проверить. Если Вы все еще находитесь в сводной таблице, переместите поле Строка в область страницы, а поле Страница 1 – в область строк. Проанализируйте, какой филиал достиг лучших результатов работы и (при выборе соответствующего наименования товара из списка в области страницы) какой товар в каком городе пользовался самым большим спросом. Изменяя структуру с водной таблицы, Вы будете иметь под рукой информацию, которая послужит хорошей основой для принятия решений.

3.11. Запишите все сделанные выводы в отчете по лабораторной работе.