Прогнозирование с помощью анализа «что-если».

Анализ «что-если» позволяет прогнозировать значение какой-либо функции (математической, финансовой, статистической и др.) при изменении её аргументов. Существует четыре способа прогнозирования значений: с помощью таблиц подстановки данных, с помощью сценариев и с помощью подбора параметров и поиска решения.

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

Пример 2 Для примера 1 определить:

- ежемесячные выплаты при процентных ставках 7%, 8% и 9% годовых,

- ежемесячные выплаты при процентной ставке 5%, 6% и 7% и сумме заема 100 000, 110 000, 120 000 и 130 000р.

 

1 Для определения выплат при ставках 7%, 8% и 9% годовых введём таблицу данных в виде (рисунок 5.2):

 

Рисунок 5.2 – Таблица данных

для изменения процентов

 

 

2 В ячейке А9 сделаем ссылку на ячейку с формулой ПЛТ, т.е. введём знак " =" и щёлкнем на ячейке В6. В строке формул запишется =В6, а в ячейке А9 появится результат –3 006, 65р.

3 Выделим блок ячеек А9: В12 и запустим инструмент Таблица данных на вкладке Данные, группа Работа с данными, кнопка Анализ " что-если", пункт меню Таблица данных.

Рисунок 5.3 Окно Таблица данных

 

4 В появившемся окне (рисунок 5.3) введём в строке для столбцов адрес ячейки В2 (щелкнем по ячейке В2 ), а в строке для столбцов щелкнем по В4 и нажмём ОК. Таблица данных заполнится числами – рисунок 5.4.

 

 

Рисунок 5.4 – Заполненная Таблица данных

 

5 Заготовим другую таблицу подстановок – введём столбцы для изменения суммы кредита (рисунок 5.5). В ячейке А14 также сделаем ссылку на ячейку с формулой, т.е. введём =В6, затем выделим таблицу (блок А15: Е18) и запустим инструмент Таблица данных на вкладке Данные, группа Работа с данными, кнопка Анализ " что-если", пункт меню Таблица данных. Введя В2 для столбцов и В4 для строк, получим:

 

Рисунок 5.5 – Таблица данных для варьирования ставки и кредита

 

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

2 способ. Сценарий – это набор значений подстановки, используемый для прогнозирования поведения модели. Если в Примере 1 изменить процентную ставку с 8, 5% на 9%, то формула ПЛТ в ячейке В6 автоматически пересчитается и покажет результат -3038, 75р. При этом прежний результат -3006, 65р. для 8, 5% пропадает. Чтобы его сохранить, применяется сценарий, в котором приводятся расчёты ПЛТ с новыми значениями и сохраняется исходный (текущий) вариант. На одном листе Excel можно создать и сохранить несколько различных сценариев и переключаться на любой из них для просмотра результатов и выбора наилучшего.

Пример 3 Оформим в виде сценария вариант подстановки данных из примера 1.

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

1 Из вкладки Данные выберете команду Анализ «что-если», выбрать Диспетчер сценариев.

2 В открывшемся окне Диспетчер сценариев нажмите кнопку Добавить.

3 Введите имя сценария., например « Кредит 200 000, 10% ».

4 В поле Изменяемые ячейки задайте те ячейки (если они не смежные, то через Ctrl), которые Вы собираетесь изменить, в данном случае – ячейки В2 и В4.

5 Введите новые значения этих ячеек (рисунок 5.6). Нажмите кнопку ОК.

 

 

Рисунок 5.6 Новые значения кредита и ставки

 

6 Нажмите кнопку Отчёт, выберите переключатель Структура, задайте ячейки для вывода результата В2: В6 (те, которые используются в формуле ПЛТ) и нажмите ОК.

В результате на отдельном листе MS Excel Структура сценария появится сценарий с текущими и новыми значениями функции ПЛТ – рисунок 5.7.

Для просмотра сценария необходимо воспользоваться кнопкой Вывести в окне Диспетчер сценариев. Знаки «+»(«-«) слева и сверху позволяют разворачивать (сворачивать) отдельные разделы отчёта. Серым выделены изменяемые поля.

Рисунок 5.7 – Сценарий Кредит 20 000, 10%

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

3 способ. Подбор параметра. Подбор параметра – это обратная задача решения уравнений. Если в прямой задаче для функции y = f(x) по известному аргументу х вычисляется значение функции у, то в обратной задаче значение функции у задаётся числом, а величина х подбирается под заданное значение у.

При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки не возвратит заданное значение.

Пример 4 Условие примера 1. Компания может ежемесячно выплачивать не более 2500р. Определить, каким должен для этого быть последний платёж.

1.Выделим ячейку В6:

2. На вкладке Данные, группа Работа с данными, кнопка Анализ " что-если" щелкнуть по пункту меню Подбор параметра – рисунок 5.8. Появится окно Подбор параметра:

 

 

Рисунок 5.8 – Окно Подбор параметра

В окне Подбор параметра:

-в поле Установить в ячейке – введено В6,

-в поле Значение - ввести -2500

-в поле Изменяя значение ячейки – ввести В3 (ячейка последнего платежа),

-нажать ОК.

В итоге появится окно Результат подбора параметра – рисунок 5.9:

 

 

Рисунок 5.9 – Окно Результат подбора параметра

 

Таким образом, если выплачивать по 2500 р. В месяц, то последний платёж составит -27716 р.

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

4 способ. Команда Поиск решения из вкладки Данные \ Работа с данными \ Анализ " что-если" используется для подбора одновременно нескольких параметров с целью максимизации или минимизации содержимого целевой ячейки и подробно рассматривается в лабораторной работе №7 (excel-7).

 

Контрольные вопросы

1 Как вывести на экран список функций MS Excel и запустить Мастер функций?

2 Какую операцию выполняет функция ПЛТ, что вводится в её поля Ставка, Кпер, ПС, Бс, Тип?

3 Назначение и способы анализа «что-если»?

4 Что такое «Таблица данных», как её создать для одно- и двухмерного массива?

5 Что такое сценарий, как его создать, просмотреть, получить итоговый отчет на отдельном листе?

6 Сущность операции Подбор параметра, как она выполняется?

 

Задания

1. Выполнить задание примера 1, изменив сумму кредита на 140000· n, где n - номер студента в журнале преподавателя. Выполнить то же для новой суммы кредита, изменив годовой процент с 8, 5% на 5%, а срок кредита с 5 на 10 лет.

2. Создать две таблицы данных как в примере 2, изменив сумму заёма на 80000· n, где n- номер студента в журнале преподавателя.

3. Оформить в виде сценариев все операции из п.1 (два сценария + Текущие значения) задания.

4. Выполнить задание примера 4, изменив сумму ежемесячной выплаты на n· 100.

 

Содержание отчёта

1Название, цель, содержание работы

2 Задание своего варианта

3 Письменные ответы на контрольные вопросы

4 Выводы по работе

На своём носителе должны быть сохранены результаты работы

 

 

Практическое занятие №6 (excel-6)

 

Создание макросов в MS Excel 2007

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

выполняемых задач

Содержание работы:

1 Общие сведения

2 Запуск макроса с клавиатуры сочетанием клавиш.

3Запуск макроса из окна Макрос.

Общие сведения

Если какое-то действие часто повторяется, его выполнение можно автоматизировать с помощью макроса. Макрос — это подпрограмма, которая содержит серию команд и функций, реализующих действия пользователя при выполнении операций в MS Excel. Их можно запускать всякий раз, когда необходимо выполнить данную задачу. Программа макроса при этом записывается на языке Visual Basic автоматически и хранится в модуле Visual Basic.

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

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

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

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

Макрос можно записать из вкладки Вид \ Макросы или вкладки Разработчик \ Код. Если вкладка Разработчик недоступна, выполните следующие действия для ее отображения.

1. Щелкните вкладку Файл (значок Кнопка Office) , а затем щелкните Параметры Excel.

2. В левой области окна Параметры Excel нажмите Основные, в правой в группе Основные параметры работы с Excel установите флажок Показывать вкладку " Разработчик" на ленте, а затем нажмите кнопку ОК.

 

 

⇐ Предыдущая234567891011Следующая ⇒

Поделиться:

Популярное:

1. III. Задачи, решаемые организацией с помощью ИСУ и ИТУ.
2. VI. Выберите подчинительный союз, с помощью которого стиль и смысл высказывания передается точнее других.
3. VIII. Проделки с помощью прута и колокольчика
4. А. Сделайте свое лицо красивым с помощью массажа рта
5. Анализ временных рядов и прогнозирование
6. Анализ временных рядов и прогнозирование в системе STATGRAFICS
7. Анализ данных с помощью команд Подбор параметра и Поиск решения
8. Анализ — это такой логический приём, с помощью которого мы мысленно расчленяем приметы, явления, выделяя отдельные их части, свойства.
9. Билет №35 .Вопрос 3 Создание защищенных сетей VPN с помощью IPSec
10. В нормальном мозге полушарие, которое ошиблось, обращается за помощью к другому.
11. Ввод формулы с помощью программы MS Equation.
12. Владимир Путин: «Нарастить туристический потенциал можно только с помощью комплексного подхода»