Информационный анализ в менеджменте; электронные таблицы EXCEL

«МЕТОДЫ ИНФОРМАЦИОННОГО АНАЛИЗА В МЕНЕДЖМЕНТЕ» (часть 1) СОДЕРЖАНИЕ ВВЕДЕНИЕ 3 3.2.1. ЭЛЕКТРОННЫЕ ТАБЛИЦЫ EXCEL 4 Общие сведения 4 Ввод данных 9 Форматирование ячеек 12 Вставка диаграмм 14 Вопросы для самоконтроля 15 3.2.2. КОМАНДЫ ВКЛАДОК EXCEL 17 Команды вкладки «Файл» 17 Команды вкладки «Главная» 18 Команды вкладки «Вставка» 22 Команды вкладки «Разметка страницы» 24 Команды вкладки «Формулы» 27 Команды вкладки «Данные» 29 Команды вкладки «Рецензирование» 30 Команды вкладки «Вид» 32 Вопросы для самоконтроля 33 3.2.3. ФИНАНСОВЫЙ АНАЛИЗ В EXCEL 35 Виды финансовых функций 35 Функции для расчетов амортизационных отчислений 35 Функции для анализа обыкновенных аннуитетов 42 Функции для анализа эффективности инвестиционных проектов 48 Вопросы для самоконтроля 52 3.2.4. СТАТИСТИЧЕСКИЙ АНАЛИЗ В EXCEL 55 Назначение и возможности пакета анализа 55 Установка пакета анализа 55 Вызов пакета анализа 56 Корреляция 56 Скользящее среднее 58 Экспоненциальное сглаживание 59 Линейный регрессионный анализ 60 Вопросы для самоконтроля 62 3.2.5. АНАЛИЗ «ЧТО-ЕСЛИ» В EXCEL 64 Таблицы данных 64 Таблицы данных для одной переменной 64 Таблицы данных с одной переменной и несколькими формулами 66 Таблицы данных для двух переменных 66 Редактирование таблиц данных 68 Диспетчер сценариев 68 Применение сценариев 69 Вопросы для самоконтроля 71 СПИСОК РЕКОМЕНДУЕМОЙ ЛИТЕРАТУРЫ 117 ВВЕДЕНИЕ Управление – это воздействие на работу системы для достижения ею намеченной цели. Системы могут быть техническими, биологическими, организационно-экономическими и т.д. Управление системами основывается на анализе и переработке информации. Способы представления, обработки и анализа информации составляют суть информационных технологий. Учитывая, что объемы информации в современном мире удваиваются примерно каждые пять лет, способы обработки информации должны использовать мощные и эффективные средства представления, обработки и анализа информации. В качестве таковых сегодня выступают программно-технические комплексы на базе компьютеров для автоматизированной обработки и представления результатов анализа в виде цифровой, текстовой, графической, звуковой и видео информации. Цель и задачи курса «Методы информационного анализа в менеджменте» – изучение средств анализа информации. 3.2.1. ЭЛЕКТРОННЫЕ ТАБЛИЦЫ EXCEL Общие сведения Назначение. Excel – программа для расчетов и представления информации с помощью электронных таблиц. Вызов и выход такой же, как и любого другого приложения в Windows. При вызове на экране появляется окно Excel с одним или несколькими вторичными окнами, в каждом из которых находится документ Excel, называемый книгой. Открыть другие книги можно командой Файл/ Открыть, а создать новую - командой Файл/ Создать/ Новая книга. По умолчанию Excel присваивает новым книгам имена Книга1, Книга2, Книга3 и т.д. При сохранении книги командой Файл/ Сохранить как… ее можно переименовать. В каждой книге находится одна или несколько электронных таблиц (листов). По умолчанию в новой книге Excel 2010 содержится 3 пустых листа с именами Лист1, Лист2, Лист3, отображаемых на вкладках в нижней части вторичного окна. Листы можно добавлять, удалять, переименовывать командами контекстного меню. Лист в Excel 2010 состоит из 16384 столбцов и более 1 миллиона строк. Пересечение строки со столбцом образует ячейку со своим адресом. Существуют два стиля адресов: буквенно-цифровой формата «столбец-строка» (например, А1 для первой ячейки листа), или цифровой формата «строка-столбец» (например, R1C1 для первой ячейки листа). Выбор стиля осуществляется командой Файл/ Параметры/ Формулы/ флажок Стиль ссылок R1C1. Ячейкам могут присваиваться имена С Панелью быстрого доступа и Лентой Вы уже знакомы. Строка формул содержит: - Палитру формул – поле, выполняющее в зависимости от обстоятельств различные функции: • содержит информацию о положении курсора (адрес выделенной ячейки, например, А2) ; • показывает размер выделенной области, например, 5Rx2C – пять строк на два столбца ; • позволяет вводить адрес ячейки для перехода к ней при нажатии клавиши Enter; • позволяет присваивать имена ячейкам или областям ячеек, например, ячейке А2 присвоить имя Начало . В дальнейшем эти имена можно использовать для перехода к ячейкам или областям ячеек путем выбора нужного имени из раскрывающегося списка Палитры формул ; • при вводе в ячейку формулы из раскрывающегося списка Палитры формул можно выбирать ранее использованные функции, например, функцию ПИ при вводе формулы =COS(ПИ) ; - три кнопки – отказ от введенной информации, завершение ввода, запуск Мастера функций для вставки в формулу нужной встроенной функции; - информацию, вводимую в ячейку . Чтобы показать или скрыть строку формул используется флажок , который располагается в группе Показать вкладки Вид. Строка состояния содержит информацию согласно выбранным в контекстном меню параметрам: Разделители табличного окна позволяют разделить окно на две или четыре части с просмотром в каждой из них разных участков таблицы. Разделить окно и снять его разделение можно также командой Вид / Окно / Разделить. Двойной щелчок по границе разделения также снимает разделение окна. После разделения окна на две части можно «заморозить» верхнюю или левую часть (постоянно присутствует на экране), а при разделении на четыре части – верхние две части и левые две части командой Вид / Окно / Закрепить области / Закрепить области. «Разморозить» их можно командой Вид / Окно / Закрепить области / Снять закрепление областей. Примером использования закрепления может быть работа с большими таблицами, когда в окне необходимо постоянное присутствие заголовков столбцов или строк. Каждая ячейка может содержать тест, число, формулу и имеет адрес (например, В4 или R4C2, что то же самое, но в другом стиле), с помощью которого осуществляется ссылка на нее. По умолчанию в Excel используются ссылки стиля A1, в которых столбцы обозначаются буквами от А до XFD, а строки числами от 1 до 1 048 576. Другой стиль ссылок R1C1 устанавливается командой Файл / Параметры/ Формулы. После буквы R указывается номер строки ячейки, после буквы C – номер столбца. Ссылки на ячейки используются в формулах и подразделяются на несколько видов: Абсолютные ссылки. Абсолютная ссылка на ячейку в формуле, например $A$1, всегда ссылается на одну и ту же ячейку. При перемещении или копировании формулы, содержащей абсолютную ссылку, из одной ячейки в другую абсолютная ссылка не изменяется. Например, при копировании формулы =$A$1 с абсолютной ссылкой $A$1 из ячейки B2 в ячейку B3, ссылка остается прежней =$A$1. По умолчанию при вводе формул используются относительные ссылки. Поэтому для задания абсолютной ссылки надо использовать символ $. Относительные ссылки. Относительная ссылка на ячейку в формуле, например A1, учитывает относительное положение ячейки, содержащей формулу, и ячейки, на которую указывает ссылка. При копировании формулы из одной ячейки в другую относительная ссылка в формуле автоматически корректируется. Например, при копировании формулы =А1 из ячейки B2 в ячейку B3 относительная ссылка А1 автоматически изменится на A2 и в ячейке В3 уже будет формула =А2. Смешанные ссылки. Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При копировании формулы, содержащей смешанную ссылку, относительная часть ссылки изменяется, а абсолютная часть не изменяется. При перемещении формулы ничего не изменяется. Например, при копировании формулы =A$1 со смешанной ссылкой А$1 из ячейки A2 в ячейку B3, формула изменяется с =A$1 на =B$1. В стиле R1C1 пронумерованы и строки, и столбцы, и поэтому он удобен для отображения относительных ссылок. Например, если ячейка A1 является текущей, то относительная ссылка R[1]C[1] указывает на ячейку, расположенную на одну строку ниже и на один столбец правее, т.е. на ячейку B2, R[-2]С (относительная ссылка на ячейку, расположенную на две строки выше текущей и в том же столбце, R[2]C[2] (относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее, R[-1] (относительная ссылка на строку, расположенную выше текущей ячейки, R (абсолютная ссылка на текущую строку). Передвижение по таблице осуществляется щелчком мыши по нужной ячейке, используя полосы прокрутки, либо с помощью клавиатуры. Клавиша Перемещение Стрелка влево На ячейку влево Стрелка вправо На ячейку вправо Стрелка вверх На ячейку вверх Стрелка вниз На ячейку вниз Tab На ячейку вправо Shift+Tab На ячейку влево Home В первую колонку строки Ctrl+Home В начало таблицы Ctrl+End В последнюю использованную ячейку таблицы Alt+PgDn Одна страница вправо Alt+PgUp Одна страница влево PgDn Одна страница вниз PgUp Одна страница вверх Выделение ячеек и областей осуществляется следующим образом. Выделяется Действие Ячейка Щелкнуть по ячейке или перейти к ней, используя клавиши перемещения Текст в ячейке Двойным щелчком войти в режим редактирования содержимого ячейки, а затем выделить буксировкой Строка Щелчок по номеру строки Столбец Щелчок по номеру столбца Соседние строки или столбцы Буксировка мыши по номерам строк или столбцов Вся таблица Щелчок по кнопке в левом верхнем углу таблицы (пересечение номеров строк и номеров столбцов) Прямоугольный фрагмент таблицы Буксировка курсора по диагонали выделяемой области из текущей ячейки Несмежные области Выше описанные способы при нажатой клавише Ctrl Ввод данных Для ввода данных нужно перейти к требуемой ячейке, щелкнув по ней или используя Палитру формул. Ввод числа заканчивается нажатием на кнопку строки формул, клавишей Enter или переходом к другой ячейке. Допускаются целые и вещественные числа как с фиксированной точкой, так и в экспоненциальном формате. В качестве разделителя целой и дробной частей по умолчанию используется запятая. Если число не умещается в колонке, то отображается ########. Число в выделенной ячейке может быть представлено в различных форматах посредством выбора из раскрывающегося списка, расположенного в группе Число вкладки Главная. Выбор из раскрывающегося списка команды Другие числовые форматы… открывает диалоговое окно Формат ячеек (запуск диалогового окна Формат ячеек возможен также командой Главная / Ячейки / Формат / Формат ячеек… либо щелчком по кнопке группы Число). Ввод текста также заканчивается переходом к другой ячейке. При этом весь текст помещается в ячейку, но не весь может быть виден, если соседние справа ячейки заняты. Ввод формул начинается со знака равенства, например, =1+2 это формула, которая видна в строке формул, а в ячейке будет виден результат 3. В отличие от формулы запись 1+2 является текстом. Формулы могут включать обращение к одной или нескольким встроенным в Excel функциям. После имени каждой функции в скобках задаются аргументы; если функция не использует аргументов, то за ее именем следуют пустые скобки () без пробела между ними, например, функция ПИ(). Аргументы отделяются точкой с запятой. В качестве элемента формулы может выступать имя ячейки, адрес ячейки, т.е. в вычислении участвует содержимое этой ячейки. Например, =СУММ(А1;В2;С3). Функции (а их несколько сотен) вводятся с помощью Мастера функций, который вызывается кнопкой строки формул или кнопкой , расположенной в группе Библиотека функций на вкладке Формулы. В формулах используются: • арифметические операции «+», «–», «*», «/», «^» (возведение в степень), • операции сравнения «>», «<», «>=», «<=», «<>», «=», например, в ячейке В1 может быть введена формула: =ЕСЛИ(A1>=40;"зачет";"не зачет") или =ЕСЛИ(A1>=80;"отлично";ЕСЛИ(A1>=60;"хорошо";ЕСЛИ(A1>=40;"удовлетворительно";"не удовлетворительно")))], • операции адресации «;» (объединяет два адреса, т.е. создает объединение нескольких областей, возможно несмежных, например (А1;В5)), «:» (задает координаты левого верхнего и правого нижнего углов прямоугольной области, например (А1:С3), «пробел» (задает пересечение двух областей, например (А1:С3 С3:Е5), что соответствует ячейке С3). Все тексты (элементы формул и аргументы функций) должны быть заключены в кавычки. В формуле не должны присутствовать пробелы (за исключением содержащихся в текстах). Все символы латинские (за исключением содержащихся в текстах). При вводе формулы в левой части строки формул появляется Палитра формул, которая позволяет вводить вложенные функции. Например, =TAN(COS(ПИ())). Допускается до семи уровней вложения. Ячейкам или областям ячеек можно присвоить имена командой Формулы / Определенные имена / Присвоить имя / Присвоить имя… либо руководствуясь следующим алгоритмом: 1) Выбрать ячейку, диапазон ячеек или несмежный диапазон, которому нужно присвоить имя. 2) Щелкнуть по Палитре формул. 3) Ввести имя без пробелов (!), которое будет использоваться для ссылки на выделенный фрагмент (длина имени не может превышать 255 знаков). 4) Нажать клавишу Enter. Одним и тем же объектам можно присвоить несколько альтернативных имен. Эти имена можно использовать в формулах. Текст как формулу, например, ="начало" полезно вводить для использования в дальнейшем оператора конкатенации (объединение текстовых фрагментов в один фрагмент). Например, =СЦЕПИТЬ(A2;B2). Длинный текст отображается поверх соседних ячеек, если они свободны. Число как текст вводится в кавычках после знака равенства, например, ="4117". Ячейки могут сопровождаться комментариями, которые вводятся командой Рецензирование / Примечания / Создать примечание (также можно использовать команду Вставить примечание контекстного меню ячейки), а отображаются при попадании курсора в поле ячейки. Ячейки с комментариями отмечены в правом верхнем углу цветным треугольником. Отобразить все примечания можно командой Рецензирование / Примечания / Показать все примечания. Режим ввода включается автоматически, как только выбрана ячейка и начат ввод. Здесь для редактирования используется только клавиша Backspace. Выход из режима происходит при нажатии клавиши Enter или клавиш управления курсором, либо при переходе к новой ячейке. Режим редактирования включается двойным щелчком по ячейке, или клавишей F2, или щелчком по правой области строки формул. Здесь можно использовать все клавиши для корректировки либо в самой ячейке, либо в строке формул. Форматирование ячеек Стиль оформления выделенной ячейки или группы ячеек определяется форматом числа, способом выравнивания информации в ячейке, видом шрифта, наличием рамки ячейки, цветовым фоном или узором и может быть изменен нажатием на кнопку Стили ячеек , расположенную в группе Стили вкладки Главная. Создать новый стиль ячейки и задать для него собственные настройки можно с помощью диалогового окна Стиль, вызываемого командой Главная / Стили / Стили ячеек / Создать стиль ячейки… Изменить размеры строк и столбцов можно либо командами Главная / Ячейки / Формат / Высота строки… и Главная / Ячейки / Формат / Ширина столбца…, либо мышью, перетаскивая границы строк между номерами строк или границы столбцов между номерами столбцов. Команда Главная / Ячейки / Вставить / Вставить ячейки… позволяет вставлять ячейки, строки и столбцы. Копирование и перенос информации отдельных ячеек или областей последовательных ячеек с помощью мыши: Операция Результат Буксировка рамки выделения Перенос информации Буксировка рамки выделения при нажатой клавише Ctrl Копирование информации Буксировка углового квадратика рамки выделения в правом нижнем углу Заполнение ячеек однотипной информацией Копирование и перенос можно также выполнить через Буфер Обмена. Печать выделенной области таблицы производится командой Файл / Печать при выборе опции . Если таблица не умещается на одной странице, то ее части печатаются постранично сверху вниз и слева направо. Диалоговое окно Параметры страницы позволяет установить ориентацию страницы, масштабирование, качество печати, размеры полей, настройки колонтитулов, сквозные строки (столбцы) и другие параметры. Вставка диаграмм Если в таблице присутствует область с числовыми данными, например, то она может быть представлена в виде диаграммы. Диалоговое окно Вставка диаграммы вызывается щелчком по кнопке группы Диаграммы, расположенной на вкладке Вставка. Редактировать диаграмму можно командами контекстного меню. Вопросы для самоконтроля 1. Для чего применяется каждая из кнопок (кроме аналогичных кнопок в MS Word), расположенных на вкладке Главная ленты MS Excel? 2. Для чего применяется строка формул? 3. Назначение строки состояния. Возможно ли скрыть строку состояния? 4. Как произвести разделение табличного окна и снять разделение? 5. Как произвести закрепление областей табличного окна и снять закрепление? Продемонстрируйте разделение окна на 2 и 4 части. 6. Для чего применяется абсолютная и относительная адресация? 7. Как изменить стиль отображения ссылок? Измените стиль отображения на R1C1. 8. Вызовите контекстное меню с помощью мыши и с клавиатуры. 9. Покажите способы перемещения по таблице. 10. Покажите способы выделения ячеек и областей. 11. Сформируйте список из двадцати элементов, начиная с 5 с шагом 2. 12. Каждый элемент сформированного списка просуммируйте с числом «10», используя относительные ссылки. 13. Добавьте в произвольную ячейку листа число «10» и, используя абсолютные и относительные ссылки, проведите операцию суммирования этой ячейки с каждым элементом сформированного на шаге 11 списка. 14. Вычислить ln и третью степень ln для каждого элемента из сформированного списка. 15. Для каждого элемента из начального списка в соседнем столбце сделать надпись "Значение в строке [№ строки соседней ячейки] столбца [№столбца соседней ячейки] = [число из соседней ячейки]". При переносе содержимого из столбца А или его изменении, в столбце B должно автоматически меняться и значение и адрес ячейки. 16. Как присвоить ячейке имя? 17. Как вставить примечание? 18. Отформатируйте ячейку следующим образом: шрифт Times New Roman, размер 14, цвет фона – желтый. Отформатируйте ячейку стилем Обычный. 19. Как изменить размеры строк и столбцов? 20. Покажите способы копирования и переноса информации отдельных ячеек или областей последовательных ячеек. 21. Вставьте диаграмму, отображающую графики LN и третьей степени LN. 22. Используя Шаблон NumTranslate, подготовьте следующий документ: 23. Составьте программу решения квадратного уравнения . 3.2.2. КОМАНДЫ ВКЛАДОК EXCEL Команды вкладки «Файл» Команда «Сохранить» Сохранение текущего документа без изменения его имени, местоположения и формата. Команда «Сохранить как» Сохранение текущего документа под другим именем, в другом месте или формате. В Microsoft Access, Microsoft Excel, Word и PowerPoint эта команда дает возможность сохранения файла с парольной защитой или с запретом его изменения другими пользователями. Для этого необходимо нажать кнопку Сервис, выбрать Общие параметры и ввести пароль, который будет использоваться для открытия файла. Команда «Открыть» Открытие или поиск существующего документа. Команда «Закрыть» Закрытие текущего документа без выхода из приложения. Если в документе имеются не сохраненные изменения, на экран будет выведен запрос на сохранение изменений перед закрытием документа. Команда «Сведения» Отображение свойств активного документа, а также возможность защитить документ или восстановить несохраненные версии файла. Команда «Последние» Список последних файлов, открывавшихся в данной программе. Для повторного открытия файла следует выбрать его имя из списка. Команда «Создать» Создание нового документа на основе обычного или специальных шаблонов. Команда «Печать» Печать текущего документа или заданных страниц с указанием дополнительных параметров печати. Команда «Сохранить и отправить» Сохранение и отправка активного документа другим пользователям различными способами. Команда «Справка» Вывод справки, а также получение последних обновлений по программе. Команда «Параметры» Изменение параметров настройки программ Microsoft Office, таких, как расположение текста на экране, параметры печати, режимы редактирования и проверки орфографии и других. Команда «Выход» Выход из программы с запросом на сохранение изменений в файлах, если оно требуется. Команды вкладки «Главная» Группа «Буфер обмена» Команда «Вставить» Вставка фрагмента из буфера обмена в текущую позицию документа с заменой выделенного фрагмента текста. Команда доступна только в том случае, если буфер обмена содержит данные. Нажатие на стрелочку показывает дополнительные параметры вставки текста. Команда (Сохранить исходное форматирование) Вставка фрагмента из буфера обмена с сохранением форматирования скопированного фрагмента. Команда (Объединить форматирование) Вставка фрагмента из буфера обмена с изменением стиля форматирования на стиль, примененный в месте вставки. Команда (Сохранить только текст) Вставка только текста из буфера обмена без сохранения рисунков, диаграмм, таблиц. Команда Вставка, создание связи или внедрение содержимого буфера обмена в текущий документ в нужном формате. Эта команда реализует OLE-технологию. Команда Открытие вкладки Дополнительно диалогового окна «Параметры Word». Команда Удаление выделенного фрагмента из текущего документа и помещение его в буфер обмена. Команда Копирование выделенного фрагмента в буфер обмена. Команда Копирование оформления текста с одного его участка на другой. Группа «Шрифт» Команда «Шрифт» Изменение шрифта выделенного текста. Нажатие на стрелочку показывает список недавно использовавшихся шрифтов и список всех шрифтов. Команда «Размер шрифта» Изменение размера шрифта выделенного текста. Нажатие на стрелочку показывает список всех доступных размеров шрифтов. Команда «Увеличить размер» Увеличение размера шрифта выделенного текста. Команда «Уменьшить размер» Уменьшение размера шрифта выделенного текста. Команда «Полужирный» Применение полужирного начертания к выделенному тексту. Команда «Курсив» Применение курсивного начертания к выделенному тексту. Команда «Подчеркнутый» Подчеркивание выделенного текста. Нажатие на стрелочку показывает другие виды подчеркиваний. Команда «Цвет заливки» Закрашивание выделенной ячейки. Нажатие на стрелочку показывает доступные цвета выделения текста. Команда «Цвет текста» Изменение цвета текста. Нажатие на стрелочку показывает различные цвета текста. Группа «Выравнивание» Команда «Уменьшить отступ» Уменьшение уровня отступа, а также увеличение или уменьшение уровня списка. Команда «Увеличить отступ» Увеличение уровня отступа, а также увеличение или уменьшение уровня списка. Команда «Выровнять по левому краю» Выравнивание текста по левому краю. Команда «Выровнять по центру» Выравнивание текста по центу. Команда «Выровнять по правому краю» Выравнивание текста по правому краю. Команда «Выровнять по верхнему краю» Выравнивание текста по верхнему краю. Команда «Выровнять посередине» Выравнивание текста по центру между верхним и нижним краем ячейки. Команда «Выровнять по нижнему краю» Выравнивание текста по нижнему краю. Команда «Ориентация» Поворот текста таким образом, чтобы он располагался по диагонали или по вертикали. Команда «Перенос текста» Отображение всего содержимого ячейки на нескольких строках. Команда «Объединить и поместить в центре» Объединение выделенных ячеек в одну и выравнивание содержимого новой ячейки по центру. Группа «Число» Команда «Числовой формат» Выбор формата отображения значения в ячейке. Команда «Финансовый числовой формат» Выбор альтернативного формата денежной еденицы для выделенной ячейки. Команда «Процентный формат» Отображание значение ячейки как процента. Команда «Формат с разделителями» Использование разделителя тысяч при отображении значения ячейки. Команда «Увеличить разрядность» Отображение более точный значений путем увеличения числа знаков после запятой Команда «Уменьшить разрядность» Отображение менее точных значений путем уменьмения числа знаков после запятой. Группа «Стили» Команда «Условное форматирование» Выделение ячеек с важной информацией и нестандартных значений, а также улучшение восприятия данных с помощью гистограмм, шкалы цветов и набора значков, применяемым согласно некоторым условиям. Команда «Форматировать как таблицу» Быстрое форматирование диапозона ячеек и преоьразование его в таблицу с помощью выбора предварительно определенного стиля таблицы. Команда «Стили ячеек» Быстрое форматирование ячеек с помощью выбора одного и предварительно определенных стилей. Можно также определить пользовательские стили ячеек. Группа «Ячейки» Команда «Вставить» Вставка ячейки, строк или столбцов на лист или в таблицу. Команда «Удалить» Удаление ячейки, строк или столбцов с листа или из таблицы. Команда «Формат» Изменение высоты строки или ширины столбца, организация листов, защита или скрытие ячеек. Группа «Редактирование» Команда «Сумма» Вывод суммы выделенных ячеек непосредственно после этих ячеек. Команда «Заполнить» Распространение узора на одну или несколько соседних ячеек. Команда «Очистить» Удаление всего, что содержится в ячейке, либо выборочное удаление форматирования, содержимого или примечаний. Команда «Сортировка и фильтр» Упорядочение данных с целью упрощения их анализа. Можно отсортировать выделенные данные по возрастанию или убыванию либо временно отфильтровать отдельные значения. Команда «Найти и выделить» Поиск и выделение нужного текста, форматирования или типа сведений в документе. Можно также заменить найденное новым текстом или форматированием. Команды вкладки «Вставка» Группа «Таблицы» Команда «Вставить сводную таблицу» Сведение данных с помощью сводной таблицы. Сводные таблицы упрощают представление, сведение и детализацию сложных данных. Команда «Таблица» Создание таблицы для управления связанными данными и их анализа. Таблицы упрощают сортировку, фильтрацию и формирование данных на листе. Группа «Иллюстрации» Команда «Рисунок» Вставка рисунка из файла. Команда «Картинка» Вставка в документ рисунков, фильмов, звуков и фотографий. Команда «Фигуры» Вставка готовых фигур в документ. По умолчанию фигуры рисуются перед текстом. Команда «SmartArt» Вставка графического объекта SmartArt для визуального представления информации. Команда «Снимок» Вставка рисунка любой программы, не свернутой в кнопку панели задач. Группа «Диаграммы» Команда «Гистограмма» Вставка гистограммы. Гистограммы используют для сравнения значений по категориям. Команда «График» Вставка графика. График используют, чтобы показать развитие процесса во времени. Команда «Круговая» Вставка круговой диаграммы. Круговые диаграммы показывают вклад каждого значения в общую сумму. Команда «Линейчатая» Вставка линейчатой диаграммы. Линейчатые диаграммы – это лучший тип диаграмм для сравнения нескольких значений. Команда «С областями» Вставка диаграммы с областями. Диаграммы с областями подчеркивает различия между несколькими наборами данных за некоторый период времени. Команда «Точечная» Вставка точечной диаграммы. Этот тип диаграммы позволяет сохранить пары значений. Команда «Другие» Вставка биржевой, поверхностной, кольцевой, пузырьковой или лепестковой диаграмм. Группа «Спарклайны» Команда «График» Вставка графика в отдельной ячейке. Команда «Столбец» Вставка гистограммы в отдельной ячейке. Команда «Выигрыш/проигрыш» Вставка диаграммы выигрыша/проигрыша в отдельной ячейке. Группа «Фильтр» Команда «Срез» Вставка среза для интерактивной фильтрации данных. Группа «Ссылки» Команда «Гиперссылка» Создание гиперссылки на веб-страницу, рисунок, адрес электронной почты или программу. Группа «Текст» Команда «Надпись» Вставка надписи, которую можно разместить в любом месте страницы. Команда «Колонтитулы» Изменение колонтитулов документа. Содержание колонтитулов будет отображаться вверху или внизу каждой напечатанной страницы. Команда «WordArt» Вставка декоративного текста в документ Команда «Строка подписи» Вставка строки подписи с указанием лица, которое должно подписаться. Команда «Объект» Вставка внедренного объекта. Группа «Символы» Команда «Формула» Вставка стандартных математических формул или построение собственных формул с помощью библиотеки математических символов. Команда «Символ» Вставка символов, отсутствующих на клавиатуре, например, символов авторского права, товарного знака, абзаца, а также символов в кодировке Юникод. Команды вкладки «Разметка страницы» Группа «Темы» Команда «Темы» Изменение общего вида всего документа. По умолчанию в документе выбрана тема «Стандартная» со шрифтом Calibri, определенным набором эффектов и цветов для объектов (для фигур – синий, для гиперссылок – темно-синий, для просмотренных гиперссылок – темно-фиолетовый и т.д.). Команда «Цвета темы» Изменение цветов для текущей темы. Команда «Шрифты темы» Изменение шрифтов для текущей темы. Команда «Эффекты темы» Изменение эффектов для текущей темы. Группа «Параметры страницы» Команда «Поля» Выбор размеров полей для всего документа или текущего раздела. • Команда Открытие диалогового окна «Параметры страницы» (вкладка «Поля») в котором можно вручную настроить размеры полей. Команда «Ориентация» Выбор книжной или альбомной ориентации страниц. Команда «Размер» Выбор размера бумаги для текущего раздела. Команда Открытие диалогового окна «Параметры страницы» (вкладка «Размер бумаги») в котором можно вручную настроить размер бумаги. Команда «Область печати» Пометка области листа для печати. Команда «Разрывы» Добавление в документ разрыва страницы, раздела или колонки. Команда «Подложка» Выбор изображения которое будет использовано как фон листа. Команда «Печатать заголовки» Выбор строк и столбцов, которые будут повторяться на каждой печатной странице. Группа «Вписать» Команда «Ширина» Уменьшение ширины напечатанного текста так, чтобы на листе поместилось максимальное число страниц. Команда «Высота» Уменьшение высоты напечатанного текста так, чтобы на листе поместилось максимальное число страниц. Команда «Масштаб» Уменьшение масштаба напечатанного документа. Чтобы воспользоваться этой возможностью, необходимо установить значение «Авто» для максимальной высоты и ширины. Группа «Параметры листа» Команда «Отобразить сетку» Отображение линий между строками и столбцами листа, что упрощает редактирование и просмотр. Команда «Печать сетки» Печать линий между сроками и столбцами листа для улучшений восприятия. Команда «Просмотр заголовков» Заголовки строк – номера строк в левой или правой части листа. Заголовки столбцов – это буквы или цифры, которые отображаются над столбцами листа. Команда «Печать заголовки строк и столбцов» Заголовки строк – номера строк в левой или правой части листа. Заголовки столбцов – это буквы или цифры, которые отображаются над столбцами листа. Группа «Упорядочить» Команда «Поместить вперед» Перемещение выделенного объекта на один уровень вверх или расположение поверх всех объекта. Команда «Переместить назад» Перемещение выделенного объекта на один уровень вниз или расположение позади всех объекта. Команда «Область выделения» Отображение области выделения, которая позволяет выделить отдельные объекты, изменить их порядок. А также показать или скрыть их. Команда «Выровнять Выравнивание границ нескольких выделенных объектов. Команда «Группировать» Группирование объектов таким образом, чтобы их можно было обрабатывать как один объект. Команда «Повернуть» Поворот или отображение выделенного объекта. Команды вкладки «Формулы» Группа «Библиотека функций» Команда «Вставить формулу» Изменение формулы в текущей ячейке посредством выбора функций и изменения аргументов. Команда «Автосумма» Вывод суммы этих ячеек непосредственно после этих ячеек. Команда «Недавно использованные» Вывод списка недавно использовавшихся формул. Команда «Финансовые» Финансовые функции осуществляют такие расчеты, как вычисление суммы платежа по ссуде, величину выплаты прибыли на вложения и др. Команда «Логические» Логические функции предназначены для проверки одного или нескольких условий. Например, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие истинно, и другое, если оно ложно. Команда «Текстовые» Текстовые функции предоставляют пользователю возможность обработки текста. Например, можно объединить несколько строк с помощью функции СЦЕПИТЬ. Команда «Дата и время» выполнение формул из списка дата и время. Команда «Ссылки и массивы» Функции Ссылки и массивы позволяют осуществить поиск данных в списках или таблицах, найти ссылку на ячейку в массиве. Например, для поиска значения в строке таблицы используется функция ГПР. Команда «Математические» Математические функции выполняют простые и сложные математические вычисления, например вычисление суммы диапазона ячеек, абсолютной величины числа, округление чисел и др. Команда «Другие функции» К ним относятся статистические, инженерные, аналитические, информационные функции, а также функции совместимости. Группа «Определенные имена» Команда «Диспетчер имен» Создание, изменение, удаление, а также посик всех имен, используемых в данной книге. Команда «Присвоить имя» Присвоение имени отдельным ячейкам и диапазоном ячеек. В дальнейшем в формулах можно будет на них ссылаться по данному имени. Команда «Использовать в формуле» Выбор имени. Используемого в данной книге и вставка этого имени в текущую формулу. Команда «Создать из выделенного» Автоматическое создание имени для выделенных ячеек. Группа «Зависимости формул» Команда «Влияющие ячейки» Отображение стрелок, которые показывают значение каких ячеек влияет на значение текущей ячейки. Команда «Зависимые ячейки» Отображение стрелок, которые показывают значение каких ячеек зависит от значения текущей ячейки. Команда «Убрать стрелки» Удаление стрелок влияющих и зависимых ячеек. Команда «Показать формулы» Отображение в каждой ячейке формулы, а не значения результата. Команда «Проверка наличия ошибок» Поиск ошибок наиболее часто встречающихся в формулах. Команда «Вычислить формулу» Открытие диалогового окна «Вычисление формулы». Предназначенного для отладки формулы путем вычисления каждого из ее компонентов по отдельности. Команда «Окно контрольного значения» Отслеживание значения конкретных ячеек путем внесения изменения в лист. Эти значения показываются в отдельном окне, которое остается видимым вне зависимости от того какая область книги отображается. Группа «Вычисление» Команда «Параметры вычисления» Выбор режима вычисления формул. По умолчанию пересчет выполняется немедленно при изменении каждого значения , от которого зависят другие значения. Команда «Пересчет» Пересчет всей книги. Это необходимо только в том случае, если автоматическое выполнение вычислений отключено. Команда «Произвести вычисления» Пересчет текущего листа. Это необходимо только в том случае, если автоматическое выполнение вычислений отключено. Команды вкладки «Данные» Группа «Получение внешних данных» Команда «Получить внешние данные из Access» Импорт данных из базы данных Microsoft Access. Команда «Получить внешние данные из Интернет» Импорт данных с веб-страницы. Команда «Получить внешние данные из текста» Импорт данных из текстового файла. Команда «Получить внешние данные из других источников» Импорт данных из других источников данных. Команда «Получить внешние данные используя другие подключения» Подключение внешних источников данных, выбранных из списка часто используемых источников. Группа «Подключения» Команда «Обновить все» Обновление всех данных полученных из источника данных. Команда «Подключения» Отображение всех подключений в данной книге. Команда «Свойства диапазона данных» Задание способа обновления подключенных к источнику данных ячеек, отображаемого содержимого источника, а также способа обработки в книге изменения количества сток или столбцов источника данных. Команда «Изменить связи» Просмотр всех файлов с которыми связана данная электронная таблица. Затем можно обновить или удалить обнаруженные связи. Группа «Сортировка и фильтр» Команда «Сортировка» Открытие диалогового окна сортировки. Сортировка выделенного диапазона, так что бы наименьшие значения оказались вверху столбца. Сортировка выделенного диапазона, так что бы наибольшие значения оказались вверху столбца. Команда «Фильтр» Разрешение фильтрации выделенных ячеек. Команда «Очистить» Очистка фильтра и состояния сортировки для текущего диапазона данных. Команда «Повторить» Повторное применение фильтра и сортировки к текущему диапазону. Команда «Дополнительно» Задание сложных условий и набора записей для набора результатов запроса. Группа «Работа с данными» Команда «Текст по столбцам» Распределение содержимого одной ячейки по нескольким столбцам. Команда «Удалить дубликаты» Удаление повторяющихся строк с листа. Команда «Проверка данных» Защита ячейки от ввода неверных данных. Команда «Консолидация» Объединение значений из нескольких диапазонов в один новый диапазон. Команда «Анализ что если» Проверка различных значений для формул на листе. Группа «Структура» Команда «Группировать» Объединение диапазона ячеек таким образом, чтобы все эти ячейки можно было одновременно свернуть или расширить. Команда «Разгруппировать» Разгруппирование ранее сгруппированного диапазона ячеек. Команда «Промежуточный итог» Расчет совместного итога для нескольких строк взаимосвязанных данных посредствам автоматической вставки промежуточных и общих итогов для выделенных ячеек. Команда «Отобразить детали» Развертывание свернутой группы ячеек. Команда «Скрыть детали» Свертывание группы ячеек. Команды вкладки «Рецензирование» Группа «Правописание» Команда «Орфография» Проверка орфографии и грамматики в тексте документа. Команда «Справочники» Открытие области задач «Справочные материалы» для поиска по справочным материалам и перевода. Команда «Тезаурус» Подбор синонимов для выделенного слова. Группа «Язык» Команда «Перевод» Перевод слов или абзацев на другой язык с использованием двуязычных словарей или машинного перевода. Группа «Примечания» Команда «Создать примечание» Добавление примечания к выделенному фрагменту. Команда «Удалить примечание» Удаление выделенного примечания. Команда «Предыдущее» Переход к предыдущему примечанию в документе. Команда «Следующее» Переход к следующему примечанию в документе. Команда «Показать или скрыть все примечания» Отображение или скрытие примечания присоединенного к данной ячейке. Команда «Показать все исправления» Вывод всех примечаний на данном листе. Команда «Показать все рукописные исправления» Отображение или скрытие рукописный примечаний на листе. Группа «Изменения» Команда «Защитить лист» Запрет на внесение нежелательных изменений в данные листа. Необходимо указать какие данные можно изменить. Команда «Защитить книгу» Запрет на внесение нежелательных изменений в структуру книги таких как, перемещение, удаление или добавление листов. Команда «Доступ к книге» Организация одновременной работы нескольких пользователей с одной и той же книгой. Команда «Защитить книгу и дать общий доступ» открытие общего доступа к книге с одновременной защитой этой книги паролем. Команда «Разрешить изменения диапазона» Разрешение изменения диапазонов защищенной книги или листа определенными лицами. Команда «Исправления» Отслеживание всех изменений, внесенных в документ, включая вставки, удаление и изменение форматирования. Команды вкладки «Вид» Группа «Режим просмотра книги» Команда «Обычный» Просмотр документа в обычном режиме. Команда «Разметка страницы» Просмотр документа в том виде, в котором он будет напечатан. Команда «Страничный режим» Предварительный просмотр разрывов страниц перед печатью документа. Команда «Представления» Сохранение набора параметров отображения и печати как настраиваемого представления. Команда «Во весь экран» Просмотр документа в полноэкранном режиме. Группа «Показать» Команда «Линейка» Отображение линеек, которые используются для изменения и выравнивания объектов в документе. Команда «Сетка» Включение линий сетки, по которым можно выравнивать объекты в документе. Команда «Строка формул» Отображение строки формул, предназначенных для ввода текста и формул в ячейки. Команда «Заголовки» Отображение заголовках строк и столбцов. Группа «Масштаб» Команда «Масштаб» Открытие диалогового окна «Масштаб», в котором можно указать масштаб документа. Команда «100%» Отображение документа в масштабе 1:1. Команда «Масштаб по выделенному» Изменение масштаба листа так. Чтобы выделенный диапазон ячеек заполнил окно целиком. Группа «Окно» Команда «Новое окно» Открытие нового окна с представлением текущего документа. Команда «Упорядочить все» Расположение окон всех открытых программ на экране рядом. Команда «Закрепить области» Закрепление на экране части листа при прокрутке остальной его части. Команда «Разделить» Разделение текущего окна на две части для одновременного просмотра разных разделов документа. Команда «Скрыть» Скрытие текущего окна. Команда «Отобразить» Отображение всех скрытых окон. Команда «Рядом» Просмотр двух документов рядом для сравнения их содержимого. Команда «Синхронная прокрутка» Синхронизация прокрутки двух документов таким образом, чтобы они прокручивались вместе. Команда «Восстановить расположение окна» Изменение положения окон сравниваемых рядом документов таким образом, чтобы каждое из них занимало половину экрана. Команда «Сохранить рабочую область» Сохранение текущего положения всех окон на рабочей области. Чтобы впоследствии можно было легко восстановить его расположение. Команда «Перейти в другое окна» Перейти в другое открытое окно. Группа «Макросы» Команда «Макросы» Вывод списка макросов для выполнения, создания или удаления макросов. Вопросы для самоконтроля 1. Создайте / откройте / закройте / сохраните файл. 2. Сохраните рабочую область. Сделайте так, чтобы при каждом запуске Microsoft Excel открывались книги рабочей области. 3. Установите параметры страницы: верхнее и нижнее поле 2 см, левое – 2,5 см., правое – 1 см. 4. Как обеспечить при печати вывод первой строки таблицы на каждой странице? 5. Как вывести на печать фрагмент таблицы? 6. Как узнать размер, расположение, состав, автора и дату создания файла? 7. Добавьте гиперссылку на документ Word. 8. Сформируйте список из ста элементов, начиная с 10 с шагом 1,5. 9. Вычислите COS и шестую степень COS для каждого элемента из сформированного списка. 10. Как отобразить данную таблицу на всех листах книги? 11. Отформатируйте таблицу с помощью команды Главная / Стили / Форматировать как таблицу, а затем удалите форматирование. 12. Как скопировать активный лист в другую книгу? 13. Скройте строку формул, отобразите примечания. Добавьте примечания к 5 ячейкам. 14. Вставьте разрыв страницы за таблицей. 15. Построить на листе со списками графики для COS и степени на одной диаграмме. По оси Х на графике должны отражаться значения от 10 до 158,5. Что необходимо сделать, чтобы полученный график был похож на график COS? Как добавить на график линии обозначающие область значений COS (область значений COS от –1 до 1)? 16. Изменить диапазон вывода значений косинуса. Должны выводится значения с 50,5 до 100. Добавить к диаграмме заголовок и линии сетки. Разместить получившуюся диаграмму на отдельном листе. 17. Как присвоить имя ячейке? Как найти в формулах из выделенных ячеек ссылки на эту ячейку и заменить их ссылками на её имя? 18. Как вставить автофигуру правильной формы? Вставьте любую автофигуру правильной формы на лист. 19. Добавить / удалить / скрыть / переименовать лист. 20. Добавить / удалить / скрыть строку / столбец / ячейку. 21. Защитить / снять защиту листа от исправлений. 22. Создать графический фон для текущего листа. 23. Воспользуйтесь командами группы Зависимости формул. Для чего применяется каждая из этих команд. 24. Как сделать так, чтобы число файлов, открытых последними, было равно 6? 25. Автоматически отформатировать все значения COS < 0,5 красным цветом. 26. Отфильтровать данные, скрыв все значения COS < 0 и COS > 0,8; затем снова вывести полный список. 27. Определить количество элементов удовлетворяющих условиям COS < -0,7 и COS > 0.8. 28. Отсортировать значения строк списка по убыванию значения COS. 3.2.3. ФИНАНСОВЫЙ АНАЛИЗ В EXCEL Назначение. Финансовые вычисления представляют собой один из разделов экономической науки, состоящий из совокупности специальных приемов и методов, направленных на решение прикладных задач в процессе принятия управленческих решений, и проведение количественного анализа эффективности финансово-экономических операций, позволяющих получить оптимальные характеристики коммерческих сделок в зависимости от различных условий их проведения. Финансовые расчеты в табличном процессоре Excel осуществляется при помощи как встроенных, так и дополнительных финансовых функций, которые предназначены для автоматизации процесса количественного анализа финансовых операций и расчетов соответствующих экономических показателей (таких как сумма амортизации, сумма платежа по ссуде, стоимость вложения или ссуды, процентные ставки по вкладам и пр.). Примечание: Дополнительные функции, которые требуют установки дополнения «Пакет анализа» (Analysis ToolPak) с помощью диспетчера надстроек, входящего в комплект поставки Microsoft Excel, здесь не рассматриваются. Виды финансовых функций По типу решаемых задач все финансовые функции в Excel можно разбить на следующие условные группы: 1.Функции для расчетов амортизационных отчислений. 2.Функции для анализа обыкновенных аннуитетов. 3.Функции для анализа эффективности инвестиционных проектов. Кроме того, в Excel возможно использование в расчетах функций для анализа ценных бумаг. Все функции данной группы являются дополнительными и здесь не рассматриваются. Функции для расчетов амортизационных отчислений В Excel реализована отдельная группа финансовых функций, позволяющих автоматизировать процесс составления планов амортизации долгосрочных активов, которые имеют практически одинаковый набор обязательных аргументов: • Балансовая (начальная) стоимость актива на начало периода эксплуатации; • остаточная (ликвидационная) стоимость актива; • срок (время) эксплуатации (полезной жизни) актива; • период – порядковый номер периода начисления амортизации. Функции для расчета амортизационных отчислений используют различные методы списания амортизации: Название метода амортизации Функция Excel, реализующая метод и ее синтаксис 1. Метод равномерного списания износа актива АПЛ (начальная стоимость актива; ликвидационная стоимость актива; время эксплуатации) 2. Метод суммы лет полезного использования АСЧ (начальная стоимость актива; ликвидационная стоимость актива; время эксплуатации; период для расчета величины отчислений) 3. Метод снижающегося остатка ФУО (начальная стоимость актива; ликвидационная стоимость актива; время эксплуатации; период для расчета величины отчислений; кол-во месяцев эксплуатации в первом году) 4. Метод двойного списания ДДОБ (начальная стоимость актива; ликвидационная стоимость актива; время эксплуатации; понижающий коэффициент) Если коэффициент не задан, то полагается, что он равен 2 Основные методы амортизации активов и функции их расчетов в MS Excel Функция АПЛ( ) вычисляет размер ежегодных отчислений при использовании метода равномерного списания износа актива. Функции АСЧ( ), ФУО( ), ДДОБ( ) реализуют применение методов ускоренной амортизации, которые позволяют списать основную часть стоимости активов в начальные периоды их эксплуатации, когда они используются с максимальной интенсивностью, создавая тем самым резерв для их своевременной замены в случае физического износа или морального устаревания. Методы ускоренной амортизации позволяют также снизить налогооблагаемую базу предприятия. В качестве описания практического применения вышеперечисленных функций приведем следующий пример. Предположим, для обеспечения производственной деятельности своего предприятия Вы приобрели некоторое оборудование. На момент ввода данного актива в эксплуатацию его первоначальная стоимость составила 10000 тыс. руб. Полезный срок эксплуатации оборудования составляет 6 лет. Любой вид долгосрочного актива (функционирующего более 1 года) имеет такие свойства, как физическое и моральное устаревание. Таким образом, в конце срока эксплуатации (срока полезной службы) данного актива его ликвидационная стоимость предположительно составит 1000 тыс. руб. Необходимо определить величину амортизационных отчислений по каждому периоду (году), используя различные варианты списания износа актива, наиболее распространенных в российской практике, и оценить полученные результаты с точки зрения эффективности применения того или иного метода на предприятии. Для решения поставленной задачи в табличном процессоре Excel выполните следующие действия: 1) Сначала введите Ваши исходные данные на рабочий лист: начальную стоимость, остаточную стоимость и срок службы актива в виде таблицы. Для нашего примера в диапазон ячеек С3:С5, как показано на рисунке: Для нашего примера необходимо создать таблицу, позволяющую рассчитать сумму амортизации, используя несколько функций и различные методы списания износа. 2) Введите в ячейку C6 формулу для расчета суммы амортизации при равномерном списании износа – функцию АПЛ( ). Финансовые функции в Excel вводятся с помощью команды Формулы / Библиотека функций / Финансовые . Также финансовые функции можно ввести посредством Мастера функций, который вызывается нажатием на на кнопку Строки формул. Для ввода функции в ячейку необходимо в списке Категория: выбрать пункт Финансовые, а затем в списке Выберите функцию: выбрать нужную функцию. 3) Нажмите кнопку ОК. На рабочем листе появится диалоговое окно с названием выбранной функции в строке формул и описанием обязательных и необязательных аргументов. После имени каждой функции в скобках задаются аргументы. Если функция не использует аргументов, то за ее именем следуют пустые скобки () без пробела между ними. Аргументы отделяются точкой с запятой (;). В качестве элемента формулы может выступать адрес ячейки в виде абсолютной или относительной ссылки, т.е. в вычислении участвует содержимое данной ячейки. Синтаксис каждой из функций можно просмотреть там же, в мастере функций. Например, синтаксис функции АПЛ () следующий: =АПЛ(нач_стоимост; ост_стоимость; время_эксплуатации), где: нач_стоимость – начальная стоимость актива; ост_стоимость – стоимость в конце периода амортизации (иногда называется ликвидационной стоимостью актива); время_эксплуатации – число периодов амортизации актива (часто называется сроком полезного использования актива). 4) Введите в соответствующие поля диалогового окна ссылки на исходные данные рабочего листа в виде адресов ячеек. Воспользуйтесь кнопкой для того, чтобы свернуть окно ввода аргументов выбранной функции. 5) Нажмите ОК. В результате на рабочем листе в ячейке ввода функции появится возвращаемый ею результат: При вычислении суммы амортизации по методу равномерного списания амортизации используется соотношение вида: . Для любого i-го периода срока эксплуатации актива сумма начисления амортизации АПЛi одинакова. Формула расчета амортизации в соответствии с синтаксисом Excel: =АПЛ(10000;1000;6) (Возвращаемый результат: 1500,00). Результаты вычисления амортизационных отчислений при ускоренном списании износа активов приведены ниже. Функция АСЧ( ) использует метод суммы лет полезного использования при начислении величины амортизации, рассчитываемой как отношение остатка срока эксплуатации актива к сумме лет, умноженная на разность начальной и остаточной стоимости. Синтаксис функции АСЧ( ) следующий: =АСЧ(нач_стоимост; ост_стоимость; время_эксплуатации; период), где: период – период, измеряемый в тех же единицах, что и аргумент время_эксплуатации. Алгебраически формула расчета амортизации актива за конкретный период выглядит следующим образом: , где: начальная стоимость – начальная стоимость актива; ликвидационная стоимость – ликвидационная стоимость актива; срок – срок эксплуатации актива; период – порядковый номер периода начисления амортизации; Таким образом, за два последовательных периода (например, за 1-й и 2-й) сумма амортизации составит соответственно: = АСЧ(10000;1000;6;1) (Результат: 2571,43); = АСЧ(10000;1000;6;2) (Результат: 2142,86). Функция ФУО( ) реализует метод уменьшающегося остатка, согласно которому износ определяется с помощью заданной (фиксированной) нормы амортизации, применяемой к чистой балансовой стоимости (начальная стоимость за вычетом накопленной амортизации). Функция ФУО( ) имеет следующий синтаксис: =ФУО(нач_стоимость; ост_стоимость; время_эксплуатации; период; [месяцы]), где: месяцы – необязательный аргумент. Количество месяцев в первом году. Если аргумент опущен, то предполагается, что он равен 12. При расчете амортизации актива за конкретный период функция использует следующую формулу: , где: накопленная амортизацияi – накопленная амортизация за предшествующие периоды эксплуатации актива; ставка – фиксированная процентная ставка, рассчитываемая Excel по следующей формуле: . При расчете процентной ставки ее значение округляется до трех десятичных знаков после запятой. Особым случаем в использовании функции ФУО( ) является расчет амортизации за первый и последний периоды эксплуатации актива. За первый период эксплуатации актива величина амортизации рассчитывается по следующей формуле: . Для последнего периода функция ФУО( ) использует иную формулу: Необязательный аргумент месяцы используется в случае, когда необходимо более точно рассчитать сумму износа (если актив был принят на баланс в определенном месяце года). Таким образом, за 1-й и 2-й периоды амортизация составит: = ФУО(10000;1000;6;1) (Результат: 3190,00); = ФУО(10000;1000;6;2) (Результат: 2172,39). Метод двойного списания основан на применении ускоренной нормы ежегодной амортизации. В качестве последней обычно принимается норма, используемая при равномерном списании, умноженная на некоторый коэффициент. В Excel данный метод реализует функция ДДОБ( ), позволяющая использовать в качестве коэффициента любое положительное число. По умолчанию значение коэффициента принимается равным 2.). Функция ДДОБ( )имеет следующий синтаксис: =ДДОБ(нач_стоимость; ост_стоимость; время_эксплуатации; период; [коэффициент]), где: коэффициент – необязательный аргумент. Процентная ставка снижающегося остатка. Если коэффициент опущен, он полагается равным 2 (метод удвоенного процента со снижающегося остатка). Сумма износа за i-й период определяется из следующего соотношения: . При исчислении износа используя функцию ДДОБ( ), величина амортизации максимальна в первый период и снижается в последующие периоды. В случае если не обязательно применять в расчетах двукратное списание износа, то можно варьировать значением аргумента коэффициент. Для нашего примера амортизация за 1-й и 2-й периоды составит: = ДДОБ(10000;1000;6;1) (Результат: 3333,33); = ДДОБ(10000;1000;6;2) (Результат: 2222,22). Окончательные результаты расчета сумм амортизационных отчислений по периодам, используя различные варианты списания износа, выглядят так: Функция ПУО( ) возвращает величину амортизации актива для любого выбранного периода, в том числе для частичных периодов, с использованием метода двойного уменьшения остатка или иного указанного метода. При этом границы периода и срок эксплуатации должны быть заданы в одних и тех же единицах (днях, месяцах, годах). Синтаксис функции ПУО( ): =ПУО(нач_стоимость; ост_стоимость; срок_эксплуатации; нач_период; кон_период; [коэффициент]; [без_переключения]), где: нач_период – начальный период, для которого вычисляется амортизация; кон_период – конечный период, для которого вычисляется амортизация (начальный и конечный период должны быть выражены в тех же единицах, что и аргумент срок_эксплуатации); коэффициент – необязательный аргумент. Коэффициент уменьшения остатка. Если коэффициент опущен, то он полагается равным 2 (метод двойного уменьшения остатка). Если нужно использовать другой метод вычисления амортизации, измените значение аргумента коэффициент; без_переключения – необязательный аргумент. Логическое значение, определяющее, следует ли использовать линейную амортизацию в том случае, когда амортизация превышает величину, рассчитанную методом уменьшающегося остатка. Если аргумент без_переключения имеет значение ИСТИНА, то MS Excel не переключается на линейный метод вычисления амортизации, даже если амортизация больше величины, рассчитанной методом уменьшающегося остатка. Если аргумент без_переключения имеет значение ЛОЖЬ или опущен, то MS Excel переключается на линейный метод вычисления амортизации, если амортизация больше величины, рассчитанной методом уменьшающегося остатка. Например: а) за период с 6 по 12 месяцы эксплуатации: =ПУО(10000;1000;6*12;6;12) (Результат: 1313,28), где: 6 лет * 12 месяцев – общее количество месяцев эксплуатации данного актива; 6,12 – порядковые номера периода начисления износа. б) с 1 по 200 день эксплуатации (с точным числом дней в году): =ПУО(10000;1000;6*365;1;200) (Результат: 1660,95). Функции для анализа обыкновенных аннуитетов В финансовой практике часто встречаются операции, характеризующиеся возникновением потоков платежей (денежных поступлений и выплат), распределенных во времени. Потоки платежей, при которых выплаты (поступления) денежных средств осуществляются равными суммами через одинаковые интервалы времени, принято называть обыкновенным или регулярным аннуитетом. Как правило, подобные потоки платежей возникают при проведении кредитно-депозитных операций, формировании различных фондов, долгосрочной аренде и т.п. Используемые при этом методы базируются на технике исчисления сложных процентов. Табличный процессор Excel предоставляет широкие возможности по моделированию подобных расчетов при помощи соответствующих встроенных в Excel финансовых функций: БC(), КПЕР(), СТАВКА(), ПЛТ(), ПС(). Все функции данной группы имеют одинаковый набор базовых аргументов: • процентная ставка (норма доходности или цена заемных средств); • срок (число периодов) проведения операции; • величина периодического платежа; • начальная сумма; • будущая стоимость (величина) денежных средств; • тип начисления процентов (1- начало периода, 0 – конец периода). Рассмотрим применение вышеперечисленных функций в проведении финансовых расчетов и анализе обыкновенных аннуитетов на конкретном примере. Предположим, Ваша фирма решила создать специальный фонд для погашения своих долгосрочных обязательств (кредитов, займов), срок погашения которых наступит, например, через 5 лет, путем периодического (ежегодного) пополнения депозита в банке. Начальная сумма депозита составляет 10000 тыс. руб. Размер ежегодных платежей – 1000 тыс. руб. Процентная ставка по банковскому депозиту – 15%. Необходимо определить величину фонда к концу 5-го года. Для решения поставленной задачи выполните следующие действия: 1) Введите исходные данные на рабочий лист. Диапазон таблицы С3:С6 содержит входные данные для расчета величины денежного фонда, т.е. будущей стоимости вложений (инвестиций). 2) Установите курсор в ячейку рабочего листа, где будет рассчитана будущая стоимость вложений (депозита), в данном случае в ячейку C7. 3) Выполните команду Формулы / Библиотека функций / Вставить функцию или нажмите кнопку Строки формул. В категории Финансовые из списка выберите функцию БC( ). Нажмите ОК. 4) Excel выведет окно ввода аргументов выбранной функции. Введите в каждое поле запроса ссылку на ячейку, содержащую требуемое значение. Функция БС( ) – позволяет определить будущую величину вклада (Future Value – FV) на основе периодических постоянных платежей при заданных величинах процентной ставки, числа периодов выплат и начальной суммы вклада. Функция имеет следующий синтаксис: =БС(норма; число периодов; выплата; нз; тип), где: норма – процентная ставка (норма доходности по депозиту); число периодов – срок (число периодов) проведения операции; выплата – величина периодического платежа; нз – начальная стоимость вложений (депозита); тип – тип начисления процентов, является необязательным аргументом. (0 – в конце периода; 1 – в начале периода). По умолчанию начисление процентов осуществляется в конце периода. Следует обратить внимание на особенности задания аргументов: Если процентная ставка задается как абсолютная величина, она должна иметь вид десятичной дроби, например как в примере: 15% – 0,15. Периодический платеж и начальная сумма задаются со знаком минус, т.к. в данной операции для фирмы они означают выплаты (расходование) денежных средств. Такие правила применимы для всех финансовых функций. В зависимости от условия поставленной задачи, значения периодического платежа и начальной стоимости могут быть введены в функцию как в виде положительных, так и отрицательных величин. Это зависит от того, какой экономический субъект проводит подобные расчеты. Кроме того, можно на этапе ввода аргументов определить конечный результат, возвращаемый функцией, который отображается внизу диалогового окна ввода аргументов. MS Excel при расчетах характеристик денежных аннуитетов выражает каждый показатель исходя из следующего соотношения: , где: НC – начальная (текущая) стоимость вклада; БСi – будущая стоимость вклада через число периодов i ; норма – процентная ставка (норма доходности); выплата – периодический платеж; i – порядковый номер периода поведения финансовой операции; тип – тип начисления процентов. Таким образом, будущая стоимость вложений определяется по формуле: , Для нашего предприятия будущее значение банковского депозита в конце 5-го года будет следующим: =БС(0,15;5;-1000;-10000) (Возвращаемый результат: 26855,95 тыс. руб.). Для банка, определяющего будущую сумму возврата средств по данному депозиту, функция имела бы следующий вид: =БС(0,15;5;1000;10000) (Возвращаемый результат: -26855,95 тыс. руб.). Функция КПЕР( ) – позволяет определить число выплат (поступлений) денежных средств, если известны процентная ставка, периодический платеж, начальная и будущая величины потоков платежей. Предположим, необходимо определить количество периодов платежей (в данном примере – число лет). Функция будет выглядеть следующим образом: =КПЕР(0,15;1000;10000;26855,95) (Возвращаемый результат: 5), где: 0,15 – процентная ставка по депозиту; 1000 – периодический платеж; 10000 – первоначальная сумма депозита; 26855,95 – будущая величина депозита. Функция СТАВКА( ) – вычисляет процентную ставку, которая в зависимости от условий операции может выступать либо в качестве цены, либо в качестве нормы рентабельности данной операции. Имеет аргументы: СТАВКА(кпер; плт; пс; [бс]; [тип]; [прогноз]), где: кпер – обязательный. Общее число периодов платежей для ежегодного платежа; плт – обязательный. Выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно аргумент "плт" состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если он опущен, аргумент "пс" является обязательным. пс – обязательный. Приведенная (текущая) стоимость, т. е. общая сумма, которая на данный момент равноценна ряду будущих платежей; бс – необязательный. Значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если аргумент "бс" опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0). тип – необязательный. Число 0 или 1, обозначающее, когда должна производиться выплата (0 или опущен – в конце периода, 1 – в начале периода; прогноз – необязательный. Предполагаемая величина ставки. Если аргумент "прогноз" опущен, предполагается, что его значение равно 10 %. Пусть в нашем примере будет неизвестна процентная ставка по банковскому депозиту. Тогда для ее расчета воспользуемся функцией СТАВКА( ): =СТАВКА(5;-1000;-10000;26855,95) (Возвращаемый результат: 15%). Для корректной работы функции платеж и сумма вклада должны быть заданы в виде отрицательных величин. Функция ПЛТ( ) – применяется в том случае, если необходимо определить величину периодического платежа по ссуде при заданных величинах будущей стоимости вложений на основе постоянных выплат, срока, процентной ставки и настоящей стоимости вложений. Имеет следующие аргументы: ПЛТ(ставка; кпер; плт; [бс]; [тип]), где: ставка – обязательный аргумент. Процентная ставка по ссуде; кпер – обязательный аргумент. Общее число выплат по ссуде; пс – обязательный аргумент. Приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой; бс – необязательный аргумент. Требуемое значение будущей стоимости, т. е. желаемого остатка средств после последнего платежа. Если этот аргумент опущен, предполагается, что он равен 0 (будущая стоимость для займа равна 0); тип – необязательный аргумент. Число 0 (нуль) или 1, обозначающее, когда должна производиться выплата. Формула расчета периодического платежа, исходя из вышеприведенного соотношения, будет иметь следующий вид: . Предположим, в нашем примере необходимо определить величину периодического платежа при заданных входных параметрах. Функция будет иметь следующий вид: =ПЛТ(0,15;5;-10000;26855,95) (Возвращаемый результат: -1000). Полученный отрицательный результат для фирмы означает отток денежных средств. Для банка, соответственно, наоборот. Функция ПС( ) – позволяет определить текущую (т.е. на момент начала операции – present value) стоимость аннуитета, если известны 4 обязательных параметра (процентная ставка; число периодов; начальная стоимость; будущая стоимость денежных средств). Имеет следующие аргументы: ПС(ставка; клер; плт; [бс]; [тип]), где: ставка - обязательный. Процентная ставка за период. Например, если получен кредит на автомобиль под 10 процентов годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 10%/12 (0,83%). В качестве значения аргумента "ставка" нужно ввести в формулу 10%/12, 0,83% или 0,0083. кпер – обязательный. Общее число периодов платежей для ежегодного платежа. Например, если получен кредит на 4 года на покупку автомобиля и платежи производятся ежемесячно, то кредит имеет 4*12 (или 48) периодов. В качестве значения аргумента "кпер" в формулу нужно ввести число 48. плт – обязательный. Выплата, производимая в каждый период и не меняющаяся на протяжении всего периода ежегодного платежа. Обычно аргумент "плт" состоит из выплат в счет основной суммы и платежей по процентам, но не включает в себя другие сборы или налоги. Например, ежемесячная выплата по кредиту в размере 10 000 р. под 12 процентов годовых на 4 года составит 263,33р. В качестве значения аргумента "плт" нужно ввести в формулу число -263,33. бс – необязательный. Значение будущей стоимости, т.е. желаемого остатка средств после последнего платежа. Если аргумент "бс" опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0). Предположим, что для определенной цели требуется накопить 50 000 р. за 18 лет: в этом случае будущая стоимость равна 50 000 р. Предположив, что заданная процентная ставка останется без изменений, можно определить, какую сумму необходимо откладывать каждый месяц. Если аргумент "бс" опущен, необходимо использовать аргумент "плт". тип – необязательный. Число 0 или 1, обозначающее, когда должна производиться выплата. Для условия нашей задачи применение данной функции позволяет получить ответ на вопрос: «Какую сумму необходимо вложить в банк на депозит, чтобы получить через 5 лет величину вклада 26855,95 тыс. руб. при ежегодном пополнении вклада на 1000 тыс. руб., если годовая банковская ставка составляет 15%?». Формула для определения текущей (настоящей) стоимости вклада (ПС): , Для нашего примера синтаксис функции будет следующим: =ПС(0,15;5;-1000;26855,95) (Возвращаемый результат: -10000). В случае, если периодичность выплат (поступлений) отлична от годовой, для любой из рассмотренных функций в данном разделе достаточно скорректировать соответствующим образом аргументы норма, и число периодов (i). Допустим, при пополнении вклада и начислении процентов дважды в год, функция ПС( ) примет следующий вид: =ПС(0,15/2;5*2;-1000;26855,95) (Возвращаемый результат: -6166,26). Функции для анализа эффективности инвестиционных проектов Данная группа включает финансовые функции, позволяющие быстро и эффективно осуществить расчет наиболее широко применяемых в мировой практике критериев эффективности инвестиционных проектов: чистой настоящей величины дохода (net present value – NPV) и внутренней нормы рентабельности (internal rate of return – IRR). В табличном процессоре Excel реализованы три встроенные функции для проведения расчета данных критериев: а) ЧПС() – функция для расчета чистой (приведенной) текущей стоимости капитала; б) ВСД() – функция для расчета внутренней нормы рентабельности; в) МВСД() – функция для расчета модифицированной внутренней нормы рентабельности; Функции для анализа эффективности инвестиционных проектов используют сложные итерационные алгоритмы для реализации дисконтных методов исчисления соответствующих показателей. При этом делаются два основных допущения: • потоки платежей на конец (начало) каждого периода известны; • для всего срока проведения операции определена оценка в виде процентной ставки, в соответствии с которой, полученные средства могут быть реинвестированы. В качестве такой оценки наиболее часто используют: среднюю или предельную стоимость капитала, банковские ставки по долгосрочным кредитам (депозитам), требуемую норму рентабельности и пр. а) Функция ЧПС( ). Допустим, Ваша фирма собирается вложить денежные средства в новое оборудование, приобретение, доставка и установка которого оценивается в 100000 тыс. руб. Ожидается, что его эксплуатация обеспечит на протяжении 6 лет получение чистой прибыли в 10000, 25000, 30000, 35000, 40000, 45000 тыс. руб. соответственно. Приемлемая норма рентабельности составляет 10%. Необходимо определить целесообразность осуществления данного проекта, т.е. необходимо определить чистый приведенный доход в конце периода в зависимости от входных параметров. Для решения задачи выполните следующие действия: 1) Введите исходные данные на рабочий лист. В данном случае диапазон ячеек А9:А15 содержит порядковые номера периодов эксплуатации актива. В ячейке B9 введено значение первоначальных инвестиций (причем в виде отрицательной величины), соответствующее нулевому периоду. В диапазоне B10:B15 содержатся значения ожидаемой чистой прибыли (денежных потоков). 2) Установите курсор в ячейку C9, выполните команду Формулы / Библиотека функций / Вставить функцию или нажмите кнопку Строки формул. В категории Финансовые из списка выберите функцию ЧПС( ). Нажмите ОК. 3) Введите необходимые аргументы в соответствии с синтаксисом данной функции: =ЧПС (ставка; значение1; значение2;…; значениеN), где: ставка – процентная ставка (норма прибыли или цена капитала); значение1, значение2,…, значениеN – денежные потоки из N платежей произвольной величины. Функция ЧПС( ) позволяет определить текущую, т.е. приведенную к настоящему моменту времени, величину потока будущих платежей (present value) с учетом заданной оценки, исходя из следующего соотношения: где: PV – текущая величина потока; Pi – сумма платежей за i-й период; r – процентная ставка (норма дисконтирования); n – число периодов. Следует обратить внимание на то, что данное соотношение не учитывает величину первоначальных инвестиций I0, т.е. инвестиций, сделанных на момент времени i=0. Поэтому для определения показателя чистый приведенный доход (net present value) из полученного результата (PV) следует вычесть величину первоначальных вложений I0. Если разность PV – I0 > 0, то проект возмещает первоначальные затраты, обеспечивает получение прибыли согласно заданному стандарту – процентной ставки r, а также некоторый финансовый резерв, равный: NPV = PV – I0. Отрицательная разность показывает убыточность проекта. На практике при проведении расчетов удобно задавать I0 как отрицательную величину, а NPV рассчитывать по формуле: NPV = I0 + PV, где I0 < 0. Проиллюстрируем все вышеизложенное на нашем примере. Функция будет выглядеть следующим образом: =ЧПС(0,1;10000;25000;30000;35000;40000;45000), (Возвращаемый результат: 126435,16), где: 0,1 – процентная ставка; 10000, 25000, 30000, 35000, 40000, 45000 – денежные потоки поступлений в виде чистой прибыли за соответствующие периоды. Чистый приведенный доход, таким образом, составит: NPV = I0 + PV = -100000+126435,16 = 26435,16. Поскольку NPV = 26435,16 >0, то проект обеспечивает возмещение первоначальных затрат и заданную норму рентабельности, а также дополнительную (сверх установленной нормы) прибыль в размере 26435,16. Определив показатель PV, не трудно рассчитать еще один важный критерий оценки эффективности инвестиций – индекс рентабельности (PI), используя формулу: PI = PV / I0. Для нашего примера он может быть рассчитан следующим образом: =ЧПС(0,1;10000;25000;30000;35000;40000;45000) / 100000 (Результат: 1,26). б) Другим широко используемым на практике критерием оценки эффективности долгосрочных инвестиций является показатель внутренней нормы рентабельности (internal rate of return – IRR). В экономическом смысле данная величина является процентной ставкой, при которой уровень капитализации регулярного дохода (т.е. будущая стоимость вложений) даст сумму, равную первоначальным инвестициям. Другими словами, это процентная ставка, при которой PV=I0, а NPV=0. Таким образом, если величина IRR больше заданной процентной ставки r, то проект следует считать эффективным, т.е. приносящий доход, иначе его следует отклонить, как убыточный. Критерий внутренней нормы рентабельности предполагает реинвестирование получаемых доходов по ставке IRR. Для вычисления этого показателя в Excel реализована функция ВСД(). Синтаксис функции: = ВСД(значения;предположение), где: значения – массив данных или диапазон ячеек, содержащих числовые величины, для которых вычисляется внутренняя скорость оборота денежных средств; предположение – величина, о которой предполагается, что она близка к результату, возвращаемому функцией ВСД(). Табличный процессор Excel использует метод итераций для вычисления внутренней нормы рентабельности начиная со значения предположение. Функция ВСД() выполняет циклические вычисления пока не возвратит результат с точностью 0,00001 процента. Если функция ВСД() не может получить результат после 20-ти попыток, то возвращается значение ошибки #ЧИСЛО!. Рассмотрим ее применение в нашей задаче. Введите в ячейку рабочего листа, например D15, формулу ВСД() в соответствии с ее синтаксисом и нажмите ОК: =ВСД (начальное значение: конечное значение; заданная норма рентабельности). =ВСД (B9:B15;0,1) (Возвращаемый результат: 17%). Поскольку полученная внутренняя норма рентабельности (17%) выше заданной (10%), то проект следует принять и посчитать его прибыльным. Показатель IRR, рассчитываемый в процентах, является более удобным критерием в оценке инвестиционной привлекательности проекта и для принятия решений, чем показатель NPV, так как абсолютные величины труднее поддаются интерпретации. в) Для более корректного учета предположения о реинвестировании денежных средств в Excel реализована функция МВСД( ), вычисляющая модифицированную внутреннюю норму рентабельности (MIRR) для ряда последовательных операций с денежными средствами. Функция МВСД( ) учитывает как стоимость инвестиций, так и доход получаемый от реинвестирования средств. Поэтому данная функция имеет специальный аргумент - предполагаемую ставку реинвестирования. Синтаксис функции: = МВСД(значения; ставка финанс; ставка реинвест), где: значения – это массив данных или ссылка на ячейки, содержащие числовые величины. Эти числа представляют ряд денежных выплат (отрицательные значения) и поступлений (положительные значения), происходящие в регулярные периоды времени. Аргумент значения должен содержать, по крайней мере, одно положительное и одно отрицательное значение для того, чтобы можно было вычислить модифицированную внутреннюю скорость оборота (норму рентабельности). В противном случае функция МВСД() возвращает значение ошибки #ДЕЛ/0!; ставка_финанс – это норма прибыли вложений денежных средств, находящихся в наличном обороте; ставка_реинвест – это норма прибыли вложений денежных средств, находящихся в наличном обороте при их реинвестировании. Функция МВСД() использует порядок расположения чисел в аргументе значения для определения порядка выплат и поступлений. Убедитесь, что значения выплат и поступлений введены в нужной последовательности и с правильными знаками (положительные значения для получаемых денег и отрицательные значения для выплачиваемых). Предположим, что в нашем примере предоставляется реальная возможность реинвестирования получаемых доходов по ставке 12%, т.е. выше заданной (10%). Для проведения расчета выполните следующие действия: 1) Пусть на рабочем листе (например, диапазон B9:B15) уже введены значения первоначальных инвестиций (ссуд) и потоков платежей (выплат) с соответствующими знаками. 2) Установите курсор в ячейку E10, выполните команду Формулы / Библиотека функций / Вставить функцию или нажмите кнопку Строки формул. В категории Финансовые из списка выберите функцию МВСД( ). Нажмите ОК. 3) Введите необходимые аргументы: в поле аргумента Значения: массив исходных данных, выделив диапазон B9:B10 для расчета модифицированной нормы рентабельности за первый год реализации проекта; введите также значения процентных ставок в соответствующие поля аргументов функции. Функция, в соответствии с синтаксисом, будет выглядеть следующим образом: = МВСД(B9:B10;0,1;0,12). 4) Нажмите ОК. В ячейке ввода E10 функция возвратит полученный результат – модифицированную внутреннюю норму рентабельности за первый год, равную минус 90%. Отрицательный знак означает, что получаемая прибыль не обеспечивает окупаемость первоначальных инвестиций к концу первого года. В конце 4-го года проекта значение модифицированной нормы рентабельности становится положительным и равняется 3%. По завершении всего срока эксплуатации актива, т.е. в конце 6-го года, модифицированная внутренняя норма рентабельности составит 15%. Полученный результат ниже предыдущего значения (IRR=17%), однако выше заданной (10%), поэтому даже при более пессимистических прогнозах реальных условий, которые могут сложиться на рынке, проект можно считать прибыльным. Вопросы для самоконтроля 1. Назовите виды финансовых функций. 2. Что такое амортизация и функции, использующиеся для ее вычисления? 3. Чем определяется использование метода равномерного списания и метода быстрого износа активов? 4. На момент ввода актива в эксплуатацию его первоначальная стоимость составила 20000 тыс. руб. Полезный срок эксплуатации оборудования составляет 5 лет. В конце срока эксплуатации данного актива его ликвидационная стоимость предположительно составит 4000 тыс. руб. Определите величину амортизационных отчислений по каждому году, используя метод равномерного списания износа актива. 5. Определите величину амортизационных отчислений по каждому году для этой задачи, используя метод суммы лет. 6. Определите величину амортизационных отчислений по каждому году, используя метод снижающегося остатка. 7. Определите величину амортизационных отчислений по каждому году, используя метод двойного списания. 8. Постройте график амортизации активов по годам. 9. Определите величину амортизационных отчислений при использовании метода двойного процента со снижающегося остатка за период с 3 по 18 месяцы и с 1 по 160 день. 10. Перечислите финансовые функции Excel для анализа обыкновенных аннуитетов. 11. Назовите базовые аргументы финансовых функции Excel для анализа обыкновенных аннуитетов. 12. Фирма решила создать специальный фонд для погашения своих долгосрочных обязательств (кредитов, займов), срок погашения которых наступит, через 6 лет, путем периодического (ежегодного) пополнения депозита в банке. Начальная сумма депозита составляет 15000 тыс. руб. Размер ежегодных платежей – 2000 тыс. руб. Процентная ставка по банковскому депозиту – 12%. Необходимо определить величину фонда к концу 6-го года. 13. Определите число выплат (поступлений) денежных средств, если процентная ставка 0,12, периодический платеж 2000 тыс. руб., начальная и будущая величины потоков платежей составляют соответственно 15000 и 39141 тыс. руб. 14. Определите процентную ставку, если известно, что срок погашения обязательств составляет 6 лет, периодический платеж 1800 тыс. руб., начальная сумма депозита составляет 15000 тыс. руб., будущая стоимость 44215 тыс. руб. 15. Определите величину периодического платежа по ссуде при следующих условиях: величина будущей стоимости вложений 45026 тыс. руб., срок 6 лет, процентная ставка 12%, настоящая стоимость вложений 15000 тыс. руб. 16. Рассчитайте, какую сумму необходимо вложить в банк на депозит, чтобы получить через 6 лет величину вклада 49785 тыс. руб. при ежегодном пополнении вклада на 2000 тыс. руб., если годовая банковская ставка составляет 12%. 17. Какую сумму необходимо вложить в банк при начислении процентов и пополнении вклада по полугодиям? 18. Перечислите финансовые функции Excel для анализа эффективности инвестиционных проектов. 19. Фирма собирается вложить денежные средства в новое оборудование, приобретение, доставка и установка которого оценивается в 130000 тыс. руб. Ожидается, что его эксплуатация обеспечит на протяжении 6 лет получение чистой прибыли в 10000, 25000, 35000, 40000, 45000, 50000 тыс. руб. соответственно. Приемлемая норма рентабельности составляет 10%. Определите приведенную к настоящему моменту времени, величину потока будущих платежей с учетом заданной оценки. 20. Определите для задания 19 чистый приведенный доход в конце периода. 21. Определите для задания 19 индексы рентабельности для каждого года, начиная с 1-го. 22. Рассчитайте для задания 19 внутреннюю норму рентабельности для всех периодов, начиная с 3-го. 23. Рассчитайте для задания 19 модифицированную внутреннюю норму рентабельности (MIRR) для всех периодов, начиная с 1-го. 24. Результаты расчетов в п. 19 – 21 должны выглядеть следующим образом: 25. Сделать общий вывод по инвестиционному проекту. 3.2.4. СТАТИСТИЧЕСКИЙ АНАЛИЗ В EXCEL Назначение и возможности пакета анализа В состав Microsoft Excel входит пакет анализа, который позволяет осуществлять статистическую обработку данных в таблицах. В состав этого пакета входят разнообразные статистические методы. Способы применения их всех аналогичны, поэтому мы рассмотрим лишь некоторые из них: корреляцию, скользящее среднее, экспоненциальное сглаживание и регрессию. Корреляция используется для количественной оценки взаимосвязи двух наборов данных, представленных в безразмерном виде. Корреляционный анализ дает возможность установить ассоциированы ли наборы данных по величине, то есть, большие значения из одного набора данных связаны с большими значениями другого набора (положительная корреляция), или, наоборот, малые значения одного набора связаны с большими значениями другого (отрицательная корреляция), или данные двух диапазонов никак не связаны (корреляция близка к нулю). Скользящее среднее (англ. moving average, MA) — общее название для семейства функций, значения которых в каждой точке определения равны среднему значению исходной функции за предыдущий период. Процедура может использоваться для прогноза сбыта, инвентаризации и других процессов. Мы спрогнозируем курс доллара США на основе данных за июль 1999 года. Экспоненциальное сглаживание предназначается для предсказания значения на основе прогноза для предыдущего периода, скорректированного с учетом погрешностей в этом прогнозе. Использует константу сглаживания, по величине которой определяет, насколько сильно влияют на прогнозы погрешности в предыдущем прогнозе. Для константы сглаживания наиболее подходящими являются значения от 0,2 до 0,3. Эти значения показывают, что ошибка текущего прогноза установлена на уровне от 20 до 30 процентов ошибки предыдущего прогноза. Более высокие значения константы ускоряют отклик, но могут привести к непредсказуемым выбросам. Низкие значения константы могут привести к сдвигу аргумента для предсказанных значений. Линейный регрессионный анализ заключается в подборе графика для набора наблюдений с помощью метода наименьших квадратов. Регрессия используется для анализа воздействия на отдельную зависимую переменную значений одной или более независимых переменных. Мы рассмотрим, как влиял на курс ЕВРО по отношению к рублю курс доллара США в июле 1999 года. Установка пакета анализа Если в MS Excel на вкладке Данные в группе Анализ отсутствует команда Анализ данных, то необходимо установить статистический пакет анализа данных. Чтобы установить пакет анализа данных необходимо: 1. Во вкладке Файл выберете команду Параметры, а затем – категорию Надстройки; 2. В поле Управление выберете значение Надстройки Excel и нажмите на кнопку Перейти; 3. В окне диалога Надстройки в поле Доступные надстройки установите флажок рядом с пунктом Пакет анализа и нажмите на кнопку ОК; 4. Во вкладке Данные в группе Анализ будет добавлена новая кнопка . Вызов пакета анализа Чтобы запустить пакет анализа: 1. Выполните команду Данные / Анализ / Анализ данных. 2. В списке Инструменты анализа выберите нужную строку. Корреляция При выборе строки Корреляция в диалоговом запросе Анализ данных появляется следующее окно. Входной интервал. Введите ссылку на ячейки, содержащие анализируемые данные. Ссылка должна состоять как минимум из двух смежных диапазонов данных, организованных в виде столбцов или строк. (Для этого нужно мышью щелкнуть по кнопке в правом конце строки, установить мышь в правый верхний угол диапазона анализируемых данных и, удерживая нажатой левую кнопку мыши, отбуксировать мышь в левый нижний угол диапазона, нажать клавишу Enter). Группирование. Установите переключатель в положение По столбцам или По строкам в зависимости от расположения данных во входном диапазоне. Метки в первой строке/Метки в первом столбце. Установите переключатель в положение Метки в первой строке, если первая строка во входном диапазоне содержит названия столбцов. Установите переключатель в положение Метки в первом столбце, если названия строк находятся в первом столбце входного диапазона. Если входной диапазон не содержит меток, то необходимые заголовки в выходном диапазоне будут созданы автоматически. (В других видах анализа этот флажок выполняет аналогичную функцию). Выходной интервал. Введите ссылку на левую верхнюю ячейку выходного диапазона. Поскольку коэффициент корреляции двух наборов данных не зависит от последовательности их обработки, то выходная область занимает только половину предназначенного для нее места. Ячейки выходного диапазона, имеющие совпадающие координаты строк и столбцов, содержат значение 1, так как каждая строка или столбец во входном диапазоне полностью коррелирует с самим собой. Новый рабочий лист. Установите переключатель, чтобы открыть новый лист в книге и вставить результаты анализа, начиная с ячейки A1. Если в этом есть необходимость, введите имя нового листа в поле, расположенном напротив соответствующего положения переключателя. Новая рабочая книга. Установите переключатель, чтобы открыть новую книгу и вставить результаты анализа в ячейку A1 на первом листе в этой книге. В результате программа сформирует таблицу с коэффициентами корреляции между выбранными совокупностями. Скользящее среднее При выборе строки Скользящее среднее в диалоговом запросе Анализ данных появляется следующее окно. Входной интервал. Введите ссылку на диапазон исследуемых данных. Входной диапазон должен состоять из одного столбца или одной строки, содержащих не менее четырех ячеек с данными. Метки в первой строке. То же, что и в окне ввода данных для корреляции. Интервал. Введите число значений, необходимое для расчета скользящего среднего. Значение по умолчанию равно 3. Выходной интервал. Введите ссылку на левую верхнюю ячейку выходного диапазона. Если установлен флажок Стандартные погрешности, то выводится состоящий из двух столбцов диапазон, содержащий значения стандартных погрешностей в правом столбце. Если предшествующих данных недостаточно для построения прогноза, MS Excel возвратит ошибочное значение #Н/Д. Выходной диапазон и исходные данные должны находиться на одном листе. По этой причине параметры Новый лист и Новая книга недоступны. Вывод графика. Установите флажок для автоматического создания встроенной диаграммы на листе, содержащем выходной диапазон. Стандартные погрешности. Установите флажок, чтобы включить в выходной диапазон столбец, содержащий стандартные погрешности. Снимите флажок для вывода одного столбца, не содержащего значения стандартных погрешностей. В результате будут рассчитаны средние значения, на основании которых можно сделать прогноз роста курса доллара США, а также стандартные погрешности расчета (в столбце G), будет выведен график. Экспоненциальное сглаживание При выборе Экспоненциальное сглаживание в диалоговом запросе Анализ данных появляется следующее окно: Входной интервал. Введите ссылку на ячейки, содержащие анализируемые данные. Входной диапазон должен состоять из одного столбца или одной строки, содержащих данные как минимум в четырех ячейках. Фактор затухания. Введите фактор затухания, который будет использоваться в качестве константы экспоненциального сглаживания. Фактором затухания называется корректировочный фактор, минимизирующий нестабильность данных генеральной совокупности. Значение фактора по умолчанию равно 0,3. Метки. Установите флажок, если первая строка или первый столбец входного интервала содержит заголовки. Снимите флажок, если заголовки отсутствуют; в этом случае подходящие названия для данных выходного диапазона будут созданы автоматически. Выходной интервал. Введите ссылку на левую верхнюю ячейку выходного диапазона. Если установлен флажок Стандартные погрешности, то выходной диапазон состоит из двух столбцов, и значения стандартных погрешностей содержатся в правом столбце. Если недостаточно исходных значений для построения прогноза или для вычисления стандартной ошибки, MS Excel возвратит ошибочное значение #Н/Д. Выходной диапазон и данные входного диапазона должны быть расположены на одном листе. По этой причине параметры Новый лист и Новая книга недоступны. Вывод графика. Установите флажок, чтобы построить встроенную диаграмму для фактических и прогнозируемых значений. Стандартные погрешности. Установите флажок, чтобы включить в выходной диапазон столбец стандартных погрешностей. Снимите флажок, чтобы получить выходной диапазон в виде одного столбца без значений стандартных погрешностей. В результате будут рассчитаны значения, на основании которых можно сделать прогноз роста курса доллара США, а также стандартные погрешности расчета (в столбце G), будет выведен график. Линейный регрессионный анализ Для проведения регрессионного анализа необходимо выбрать Регрессия. На экране появится запрос Регрессия со следующими полями. Входной интервал Y. Введите ссылку на диапазон зависимых данных. Диапазон должен состоять из одного столбца. Входной интервал X. Введите ссылку на диапазон независимых данных. MS Excel располагает независимые переменные этого диапазона слева направо в порядке возрастания. Максимальное число входных диапазонов равно 16. Метки. Установите флажок, если первая строка или первый столбец входного интервала содержит заголовки. Снимите флажок, если заголовки отсутствуют; в этом случае подходящие названия для данных выходного диапазона будут созданы автоматически. Уровень надежности. Установите флажок, чтобы включить в выходной диапазон дополнительный уровень. В соответствующее поле введите уровень надежности, который будет использован дополнительно к уровню 95%, применяемому по умолчанию. Константа – ноль. Установите флажок, чтобы линия регрессии прошла через начало координат. Выходной интервал. Введите ссылку на левую верхнюю ячейку выходного диапазона. Отведите, по крайней мере, семь столбцов для итогового диапазона, который будет включать в себя: результаты дисперсионного анализа, коэффициенты регрессии, стандартную погрешность вычисления Y, среднеквадратичные отклонения, число наблюдений, стандартные погрешности для коэффициентов. Новый лист. Установите переключатель, чтобы открыть новый лист в книге и вставить результаты анализа, начиная с ячейки A1. Если в этом есть необходимость, введите имя нового листа в поле, расположенном напротив соответствующего положения переключателя. Новая книга. Установите переключатель, чтобы открыть новую книгу и вставить результаты анализа в ячейку A1 на первом листе в этой книге. Остатки. Установите флажок, чтобы включить остатки в выходной диапазон. Стандартизированные остатки. Установите флажок, чтобы включить стандартизированные остатки в выходной диапазон. График остатков. Установите флажок, чтобы построить диаграмму остатков для каждой независимой переменной. График подбора. Установите флажок, чтобы построить диаграммы наблюдаемых и предсказанных значений для каждой независимой переменной. График нормальной вероятности. Установите флажок, чтобы построить диаграмму нормальной вероятности. При анализе влияния курса доллара США (Х) на курс ЕВРО (Y) получим: Вопросы для самоконтроля Исходные данные для анализа. № заводов Стоимость ОПФ, млрд. руб., x Выработано сахара, тыс. руб., y 1 4,99 130 2 6,93 158 3 6,90 161 4 8,09 120 5 4,88 171 6 7,05 101 7 7,88 188 8 4,85 244 9 4,35 134 10 3,43 122 11 6,06 169 12 6,11 165 13 9,79 139 14 3,85 133 15 6,69 172 16 6,63 145 17 6,47 142 18 6,08 144 19 8,11 109 20 9,47 206 1. Продемонстрируйте, как устанавливается и запускается пакет статистического анализа в MS Excel. 2. Что такое корреляция, регрессия? 3. Продемонстрируйте, как вводятся входные и выходные диапазоны данных. 4. Что дает использование флажка «Метки в первой строке»? 5. Укажите, что создание итогов работы пакета анализа будет происходить на новом листе «Результаты». 6. Сформируйте итоговые данные. 7. Укажите, что создание итогов работы пакета анализа будет происходить в новой книге «Результаты». 8. Сформируйте итоговые данные. 9. Что означается «#Н/Д» в выходном диапазоне расчета Скользящего среднего? 10. Выведите график для скользящего среднего. 11. На новом листе сформируйте результаты работы без вывода графика. 12. Для чего предназначается экспоненциальное сглаживание? 13. Укажите цель линейно регрессионного анализа? 14. Что такое линейный регрессионный анализ? Почему он назван линейным? 15. Как сказывается на анализе наличие «экстремальных» значений функции? 16. Что нужно сделать с «экстремальными» значениями (экстремумы), если они встречаются в исходных данных? 17. Проведите регрессионный анализ предложенных данных при уровне надежности: 99%. 18. Выведите результаты в новую книгу «Результаты регрессионного анализа». 19. Отобразите графики «остатков» и «нормальной вероятности». 3.2.5. АНАЛИЗ «ЧТО-ЕСЛИ» В EXCEL Анализ «что-если» позволяет изменять основные переменные таблицы данных и сразу же видеть результаты этих изменений. Предположим, вы используете анализ, чтобы решить покупать машину или взять ее в прокат. В этом случае можно проверить финансовую модель при различных предположениях о процентных ставках и периодических выплатах и выбрать оптимальное решение. Таблицы данных Таблица данных позволяет представить результаты формул в зависимости от значений одной или двух переменных, которые используются в этих формулах. С помощью команды Данные / Работа с данными / Анализ «что-если» / Таблица данных… можно создать два типа таблиц данных: таблицу для одной переменной, которая проверяет влияние этой переменной на несколько формул, или таблицу для двух переменных, которая проверяет их влияние на одну формулу. Таблицы данных для одной переменной Предположим, что вы рассматриваете возможность покупки дома, для чего вам придется взять ссуду под закладную в $200 000 на 30 лет, и вы хотите вычислить месячные выплаты по этой ссуде для нескольких процентных ставок. Эту информацию может предоставить таблица данных для одной переменной. Чтобы создать такую таблицу, выполните следующие действия: 1. На новом рабочем листе введите интересующие вас процентные ставки. Для этого примера введите 6, 6,5, 7, 7,5, 8 и 8,5 процентов в ячейки ВЗ:В8 (мы называем этот диапазон входным диапазоном, т.к. он содержит входные значения, которые мы хотим проверить). 2. Затем введите формулу, которая использует входную переменную. В данном случае введите в ячейку С2 формулу: =ПЛТ(А2/12;360;200000), где А2/12 – месячная процентная ставка, 360 – срок ссуды в месяцах и 200000 – размер ссуды. Обратите внимание, что эта формула ссылается на ячейку А2, которая в данный момент пустая (при расчете числовых формул MS Excel присваивает пустым ячейкам значение 0). Как вы можете заметить, поскольку А2 пустая, то функция возвращает величину ежемесячных выплат, необходимую для погашения ссуды при нулевой процентной ставке. Ячейка А2 является только меткой, через которую Excel будет подставлять значения из входного диапазона. На самом деле Excel не изменяет хранимое значение в этой ячейке, поэтому такой меткой может быть любая ячейка рабочего листа вне диапазона таблицы данных. 3. Выделите диапазон таблицы данных – минимальный прямоугольный блок ячеек, включающий в себя формулу и все значения входного диапазона. В данном случае выделите диапазон В2:С8. 4. Выполните команду Данные / Работа с данными / Анализ «что-если» / Таблица данных… В окне диалога Таблица данных задайте местонахождение входной ячейки в поле Подставлять значения по строкам в или в поле Подставлять значения по столбцам в. Входная ячейка – это ячейка-метка, на которую ссылается формула таблицы данных, в данном случае, А2. Чтобы таблица данных заполнялась правильно, вы должны ввести ссылку на входную ячейку в нужное поле. Если входные значения расположены в строке, введите ссылку на входную ячейку в поле Подставлять значения по столбцам в. Если значения во входном диапазоне расположены в столбце, используйте поле Подставлять значения по строкам в. В данном примере входные значения расположены в столбце, поэтому введите $А$2 в поле Подставлять значения по строкам в. 5. Нажмите кнопку ОК. Excel выведет значения формулы для каждого входного значения в ячейках диапазона таблицы данных. В нашем примере Excel выведет шесть результатов в диапазоне СЗ:С8. При создании этой таблицы данных Excel ввел формулу массива {=ТАБЛИЦА(;А2)} в каждую ячейку в диапазоне СЗ:С8 (диапазон результатов). В нашей таблице формула ТАБЛИЦА вычисляет значения функции ПЛТ для каждой процентной ставки в столбце В. Например, формула в ячейке С5 вычисляет размер выплаты при ставке, равной 7 процентам. Функция ТАБЛИЦА, используемая в формуле, имеет следующий синтаксис: =ТАБЛИЦА(входная ячейка для строки ;входная ячейка для столбца). Поскольку в нашем примере входные значения расположены в столбце, Excel использует ссылку на входную ячейку для столбца А2 в качестве второго аргумента функции и оставляет первый аргумент пустым (на что указывает точка с запятой). После построения таблицы можно изменить формулу таблицы данных или любые значения во входном диапазоне для создания другого множества результатов. Например, предположим, что для покупки дома вы решили занять только $185 000. Если вы измените формулу в ячейке С2 на =ПЛТ(А2/12;360; 185000) значения в выходном диапазоне изменятся. Таблицы данных с одной переменной и несколькими формулами Вы можете включить любое количество выходных формул при создании таблицы данных с одной переменной. Если входной диапазон является столбцом, введите вторую выходную формулу непосредственно справа от первой, третью справа от второй и т. д. Можно использовать разные формулы для различных столбцов, но все они должны использовать одну и ту же входную ячейку. Предположим, что вы рассматриваете возможность покупки дома, и для этого вам придется взять ссуду под закладную в размере $180 000 на 30 лет. Вы хотите узнать, какие будут месячные выплаты по этой ссуде для нескольких процентных ставок во входном диапазоне, и хотите сравнить их с выплатами для ссуды в $200 000. Вы можете расширить таблицу и включить в нее обе формулы. Чтобы добавить новую формулу в существующую таблицу данных, выполните следующие действия: 1. В ячейку справа от существующей формулы (в данном случае в ячейку D2) введите новую формулу. Для нашего примера введите: =ПЛТ(А2/12;360;180000). Обратите внимание, что, как и первая формула, эта формула ссылается на ячейку А2, ту же самую входную ячейку. (Не забудьте восстановить размер ссуды в первоначальной формуле, указав $200 000.) 2. Выделите диапазон таблицы, в данном случае B2:D8. 3. Выберите команду Таблица подстановки и введите ссылку на входную ячейку $А$2 в поле Подставлять значения по строкам в. В результате получим: Как и раньше, каждая ячейка в диапазоне C3:D8 содержит формулу =ТАБЛИЦА(;А2)}. Эти формулы вычисляют значения формул в ячейках С2 и D2 для каждой процентной ставки во входном диапазоне. Например, формула в ячейке D4 вычисляет значение формулы, содержащейся в ячейке D2 при ставке, равной 6,5 процента (ячейка В4). Таблицы данных для двух переменных Таблицы данных, рассматриваемые до сих пор, вычисляют значения одной или нескольких формул для значений одной переменной. Вы также можете создать таблицы, которые вычисляют воздействие двух переменных на одну формулу. Предположим, что вы хотите создать таблицу данных, которая вычисляет месячные выплаты по ссуде под закладную в $200 000, но на этот раз нужно изменять не только процентную ставку, а также и срок ссуды. Требуется узнать месячные выплаты для процентной ставки 6, 6,5, 7, 7,5, 8 и 8,5 процента при сроках 15, 20, 25 и 30 лет (180, 240, 300 и 360 месяцев). Чтобы создать такую таблицу, выполните следующие действия: 1. Введите первое множество входных значений в столбец. Как и раньше, введите шесть процентных ставок в ячейки ВЗ:В8. 2. Введите второе множество входных значений в строке выше и правее на одну ячейку от начала первого диапазона. В данном случае введите сроки ссуды в ячейках C2:F2. 3. Теперь можно создать формулу для таблицы. Поскольку это таблица с двумя переменными, формула должна быть введена в ячейку на пересечении строки и столбца, содержащих два множества входных значений, то есть в ячейку В2. Хотя в таблицу данных для одной переменной можно включить любое число формул, в таблице с двумя переменными допускается использование только одной формулы. Формула для таблицы в нашем примере будет иметь следующий вид: =ПЛТ(А2/12;В1;200000). Здесь представлен результат, полученный на данный момент. Формула возвращает ошибочное значение #ЧИСЛО!, так как в вычислениях используются две пустые ячейки, и в результате получается или слишком большое или очень малое число, которое Excel не может представить. Как вы увидите, этот результат не оказывает никакого воздействия на работу таблицы. 4. Выделите диапазон таблицы данных – минимальный прямоугольный блок, включающий в себя все входные значения и формулу. В данном случае выделите диапазон B2:F8. 5. Выполните команду Данные / Работа с данными / Анализ «что-если» / Таблица данных…и задайте входные ячейки. Поскольку это таблица с двумя переменными, вы должны задать две входные ячейки: одну для первого множества входных значений и одну для второго. В этом примере введите ссылку для первой входной ячейки $В$1 в поле Подставлять значения по столбцам в и затем введите ссылку для второй входной ячейки $А$2 в поле Подставлять значения по строкам в. 6. Нажмите Enter или кнопку OK для вычисления таблицы. Как и в предыдущих примерах, Excel вводит формулы массива ТАБЛИЦА в диапазоне результатов C3:F8. Поскольку эта таблица имеет две переменные, формула ТАБЛИЦА содержит две ссылки: {=ТАБЛИЦА(В1;А2)}. Значения в диапазоне результатов – это месячные выплаты по ссуде для каждой комбинации процентных ставок и сроков. Например, значение в ячейке D6 ($-1611,19) – это выплата, необходимая для погашения ссуды в $200 000 за 240 месяцев при процентной ставке 7,5 процентов. Редактирование таблиц данных Хотя можно редактировать формулы или входные значения в левом столбце или в верхней строке таблицы, изменять содержимое ячеек в диапазоне результатов не позволяется, так как таблица данных является массивом. Например, при попытке очистить ячейку D7 в последней таблице, Excel выведет сообщение Невозможно изменить часть таблицы данных. Если вы допустили ошибку при задании таблицы данных, то должны выделить все результаты, использовать команду Главная / Редактирование / Очистить (можно использовать клавишу DEL) и затем заново пересчитать таблицу. Вы можете копировать диапазон результатов в другую часть листа. Таким образом, можно сохранить текущие результаты таблицы перед изменением вводных значений переменных или формул. При копировании диапазона результатов таблицы данных Excel заменяет формулы массива их числовыми значениями. Диспетчер сценариев Таблицы данных хороши, когда используются только одна или две переменные. Для более сложных задач (до 32 переменных) можно использовать диспетчер сценариев, вызывая его командой Данные / Работа с данными / Анализ «что-если» / Диспетчер сценариев… Сценарием называется набор значений подстановки, используемый для прогнозирования поведения модели. Существует возможность создания и сохранения в листе различных сценариев и переключения на любой из этих сценариев для просмотра результатов. Например, пусть существуют два варианта развития событий – оптимистичный и пессимистичный. Каждый из вариантов представляет собой набор 10 чисел. Необходимо просчитать 5 формул. Чтобы не переделывать формулы, можно просто запомнить наборы чисел как сценарии, и меняя их, получать разные значения формул. Применение сценариев Чтобы создать новый сценарий, в диалоговом окне Диспетчер сценариев нажмите кнопку Добавить. Появится окно Добавление сценария. Введите необходимое имя в поле Название сценария. Введите ссылки на ячейки, которые необходимо изменить, в поле Изменяемые ячейки. Установите необходимые флажки в наборе флажков Защита. Нажмите кнопку OK. Введите необходимые значения в диалоговом окне Значения ячеек сценария. Чтобы создать сценарий, нажмите кнопку OK. Для создания дополнительных сценариев нажмите кнопку Добавить, а затем повторите описанные действия. После завершения создания сценариев нажмите кнопку OK, а затем – кнопку Закрыть в диалоговом окне Диспетчер сценариев. Флажок запретить изменения позволяет установить защиту сценария от изменений. При этом необходимо также защитить лист командой Рецензирование / Изменение / Защитить лист. Прежде чем вносить изменения в сценарий, необходимо снять флажок запретить изменения. С помощью флажка скрыть можно скрыть сценарии. При этом необходимо также защитить лист командой Рецензирование/ Изменение/ Защитить лист. Чтобы изменить существующий сценарий, выберите команду Данные / Работа с данными / Анализ «что-если» / Диспетчер сценариев…, установите курсор на нужный сценарий и нажмите кнопку Изменить… (изменение аналогично добавлению, только при этом не будет создан новый сценарий, а будет изменен старый). Чтобы удалить существующий сценарий, выполните команду Данные / Работа с данными / Анализ «что-если» / Диспетчер сценариев…, установите курсор на нужный сценарий и нажмите кнопку Удалить. Чтобы воспользоваться сценарием (чтобы в указанные ячейки были помещены определенные в сценарии значения), выполните команду Данные / Работа с данными / Анализ «что-если» / Диспетчер сценариев…, установите курсор на нужный сценарий и нажмите кнопку Вывести. Обратите внимание! При использовании команды Вывести Excel подставляет в ячейки на листе те значения, которые были занесены в сам сценарий. Если до этого в этих ячейках были значения из другого сценария, то старый сценарий НЕ удаляется, а просто заменяются значения ячеек на листе. В этом и состоит главное назначение сценариев. Сценарии автоматически сохраняются при записи файла на носитель. Рассмотрим применение сценариев на конкретном примере. Предприятию необходимо составить финансовый план на год, но годовой доход точно не известен. Для дохода определяются различные значения, а затем для каждого сценария выполняется анализ «что-если». В «худшем» варианте сценария объем реализации составит 200, 205, 201, 210 ед. продукции для I, II, III и IV кварталов соответственно. В «лучшем» варианте сценария – 220, 235, 250, 270 ед. продукции для I, II, III и IV кварталов соответственно. Цена одной единицы прогнозируется в размере 10 000, 11 000, 12 100, 17 000 руб. для I, II, III и IV кварталов соответственно. Необходимо рассчитать объемы финансирования подразделений предприятия и прибыль. Издержки подразделяются на постоянные и переменные. Заводоуправление требует постоянных затрат в размере 600 тыс. руб. ежеквартально, цех №1 требует постоянных затрат в размере 100 тыс. руб. ежеквартально и переменных затрат 3000 руб. с каждой ед. продукции, цех №2 требует постоянных затрат в размере 350 тыс. руб. ежеквартально и переменных затрат 2000 руб. с каждой ед. продукции. Для решения задачи выполните следующие действия: 1. Создайте пессимистичный сценарий для четырех ячеек (B2:E2) (как создать сценарий показано выше). Присвойте ячейкам (B2:E2) значения 200, 205, 201, 210 соответственно. 2. Создайте оптимистичный сценарий для тех же ячеек (B2:E2). Присвойте ячейкам значения 220, 235, 250, 270 соответственно. 3. Внесите в ячейки (B3:E3) значения 10; 11; 12,1; 17 соответственно. 4. Внесите в ячейки (B5:E5) значения 600, 600, 600, 600 соответственно. 5. Внесите в ячейки формулы для расчета необходимых значений: Формула для расчета Ячейки Формула валового дохода B4:E4 =(объем продаж)*(цена ед.) Затрат на цех №1 B6:E6 =100+3*(объем продаж) Затрат на цех №2 B7:E7 =350+2*(объем продаж) итого издержек B8:E8 =(затраты заводоуправления)+(затраты на цех №1)+(затраты на цех №2) прибыли B9:E9 =(валовой доход)–(итого издержек) итого за год: объем продаж валовой доход затраты на заводоуправление затрат на цех №1 затрат на цех №2 итого издержек прибыли F2 F4 F5 F6 F7 F8 F9 =СУММ(B2:E2) =СУММ(B4:E4) =СУММ(B5:E5) =СУММ(B6:E6) =СУММ(B7:E7) =СУММ(B8:E8) =СУММ(B9:E9) 6. Используя кнопку Вывести в диалоговом окне Диспетчер сценариев, просмотрите различные варианты расчетных значений прибыли и других показателей. При оптимистичном варианте окно будет выглядеть следующим образом. При пессимистичном варианте окно будет выглядеть следующим образом. Вопросы для самоконтроля 1. Какие типы таблиц данных можно создать с помощью команды Данные / Работа с данными / Анализ «что-если» / Таблица данных… и в чем их отличие? 2. Предприятие оценивает возможность покупки станка. Имеются предложения по цене 200, 210, 220, 245 и 250 тыс. руб. Срок службы всех станков 10 лет, остаточная стоимость 20 тыс. руб. Определите суммы амортизационных отчислений для станков с различной стоимостью при использовании метода равномерного списания. 3. У вас есть возможность вложить 10 000 руб. или 20 000 руб. в банк на 5 лет. Каждый год вы планируете помещать в банк 1000 руб. Определите накопленную сумму в конце пятого года при ставках банковского процента 5, 10, 15, 20, 25, 30 и 35%, если выплаты в конце года не производятся. 4. Создайте таблицу данных, которая вычисляет ежегодные выплаты по ссуде под закладную в 100 000 руб. для процентной ставки 5, 10, 15, 20 и 25 процента при сроках 10,15, 20 и 25 лет. 5. Создайте пессимистический, оптимистический и средний вариант сценариев при следующих условиях. Предприятию необходимо составить финансовый план на год, но годовой доход точно не известен. В «худшем» варианте сценария цена одной единицы прогнозируется в размере 1 000, 1 050, 1 200, 1 300 руб. для I, II, III и IV кварталов соответственно. В «лучшем» варианте сценария – 1 100, 1 200, 1 300, 1 400 руб. для I, II, III и IV кварталов соответственно. Для среднего варианта сценария цена составит 1 050, 1 100, 1 250 и 1 350 руб. для I, II, III и IV кварталов соответственно. Объем реализации составит 1 000, 1 100, 1 200, 1 400 ед. продукции для I, II, III и IV кварталов соответственно. Необходимо рассчитать объемы финансирования подразделений предприятия и прибыль. Издержки подразделяются на постоянные и переменные. Заводоуправление требует постоянных затрат в размере 400 тыс. руб. ежеквартально, цех №1 требует постоянных затрат в размере 100 тыс. руб. ежеквартально и переменных затрат 25 руб. с каждой ед. продукции, цех №2 требует постоянных затрат в размере 200 тыс. руб. ежеквартально и переменных затрат 15 руб. с каждой ед. продукции. Ставка налога на прибыль составляет 35%. Итоговая таблица должна выглядеть следующим образом: Сравнение сценариев финансового плана Текущие значения: Наихудший вариант Наилучший вариант Средний вариант Квартал 1 1000 1000 1100 1050 2 1050 1050 1200 1100 3 1200 1200 1300 1250 4 1300 1300 1400 1350 Результат: Валовый доход, тыс. руб. 5415000 5415000 5940000 5650000 Итого издержек 2988000 2988000 2988000 2988000 Валовая прибыль, руб. 2427000 2427000 2952000 2662000 Налог на прибыль, руб. 849450 849450 1033200 931700 Чистая прибыль, руб. 1577550 1577550 1918800 1730300 СПИСОК РЕКОМЕНДУЕМОЙ ЛИТЕРАТУРЫ 1. Волков В.Б. Понятный самоучитель Excel 2010: Питер, 2010. – 252 с. 2. Зудилова Т.В. Работа пользователя в Microsoft Excel 2010: Санкт-Петербург, 2012. – 87 с. 3. Иванов И. Microsoft Excel 2010 для квалифицированного пользователя: Академия АЙТИ, 2012. – 244 с. 4. Руководство по продукту Microsoft Excel 2010: Корпорация Майкрософт, 2010. – 83 с. 5. Уокенбах Д. Microsoft Excel 2010. Библия пользователя: Диалектика, 2011. – 910 с. 6. Леонтьев В.П. Новейший самоучитель Windows 7 + Office 2010 – М.: ОЛМА Медиа Групп, 2011. - 325с. 7. Стоцкий Ю., Васильев А., Телина И. Office 2010. Самоучитель. — СПб.: Питер, 2011. — 432 с.