Последовательность выполнения заданий

Задания выполняются в строго указанной последовательности.

Краткие теоретические сведения

При решении различных задач часто приходится заниматься проблемой подбора одного значения путем изменения другого. Для этой цели весьма эффективно средство MS Excel Подбор параметра (ПП) MS Excel.

Этот инструмент анализа «что-если» MS Excel обеспечивает вычисление значения аргумента (параметра) для заданного значения функции методом последовательных итераций. Вызов Подбор параметра для MS Excel 2007 производится на закладке Данные из меню Анализ "что-если".

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

Определение параметров модели, соответствующих заданной реакции, является обратной задачей, которая как раз и решается с помощью Подбор параметра/

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

Для применения Подбор параметра

Необходимо выполнить следующие действия:

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

2) В поле ввода Значение введите число, которое вы хотите увидеть в ячейке, указанной в поле Установить в ячейке.

3) В поле ввода Изменяя значение ячейки введите адрес или просто щелкните на ячейке, содержащей числовое значение, которое вы хотите определить.

Формула в ячейке, указанная в поле Установить в ячейке, обязательно должна прямо или опосредованно ссылаться на ячейку, которую вы указали в поле Изменяя значение ячейки.

Заполнив все три поля ввода, для начала работы данного средства щелкните на кнопку ОК.

В результате появится диалоговое окно Результат подбора параметра, которое сообщит, что решение найдено. Отображаемые в этом окне Подбираемое значение – это то значение, которое вы указали в поле Значение диалогового окна Подбор параметра, а Текущее значение – то значение, которое MS Excel смогла добиться от формулы при подборе параметра. Если числа совпадают, то действительно найдено решение задачи.

Задания и инструкции по выполнению

Задание 1 Изучение постановки задачи для расчета суммы кредит для закупки деталей.

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

ü Какой платеж следует осуществлять ежемесячно при покупке 15 единиц товара?

ü Что будет, если потребуется приобретать не 15, а 19 единиц товара при тех же условиях?

ü Как изменится платеж по кредиту, если изменится стоимость товара при том же количестве?

ü Какие возможности есть у коммерсанта, если стоимость единицы товара будет колебаться в некоторых пределах?

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

ü Снизить размер кредита за счет уменьшения количества закупаемого товара

ü Продлить срок выплаты кредитной задолженности.

ü Добиться снижения кредитной ставки.

Для решение проблемы использовать финансовую функцию ПЛТ.

Примечание: Функция ПЛТ возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.

Синтаксис ПЛТ(ставка;кпер;пс;бс;тип)

Ø Ставка — процентная ставка по ссуде.

Ø Кпер — общее число выплат по ссуде.

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

Ø Бс — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (нолю), т. е. для займа, например, значение бс равно 0.

Ø Тип — число 0 (ноль) или 1, обозначающее, когда должна производиться выплата.

Задание 2 Расчет суммы кредит для закупки деталей.

1. Построить таблицы по образцу:

2. Записать исходные данные в таблицу Потребности деталей.

3. Рассчитать значение ячейки Сумма в ячейке B 6.

4. Задать условия выплаты кредита в таблице Платежи по формулам:

5. Занести данные (аргументы) функции ПЛТ

 

6. Провести анализ закупаемого товара, если известно, что ежемесячная выплата составляет 1700 руб. Для этого вызвать надстройку Подбор параметра. Данные-Анализ «что-если»-Подбор параметра:

ü ввести в диалоговое окно условия, которые удовлетворяют заемщика;

ü проанализировать результат, который отображается на диалоговом окне;

ü записать в отчет полученный результат.

7. Провести анализ суммы кредита, если известно, что необходимо закупить 19 единиц товара и срок выплаты составляет 13 месяцев. Для этого:

ü скопировать исходные расчетные таблицы на Лист 2;

ü в ячейку В5 занести значение 19;

ü ввести в диалоговое окно условия;

ü проанализировать результат, который отображается на диалоговом окне;

ü записать в отчет полученный результат.

Задание 3 Изучение постановки задачи для расчета штатного расписания.

Используя режим подбора параметра, определить штатное расписание фирмы.

Известно, что в штате фирмы состоят: 6 курьеров, 8 менеджеров, 10 экспедиторов, 3 заведующих отделами, 1 гл. бухгалтер, 1 программист, 1 системный аналитик, 1 генеральный директор.

Общий месячный фонд заработной платы составляет 100000 рублей.

Определить, какими должны быть оклады сотрудников.

Каждый оклад является линейной функцией от оклада курьера, а именно:

зарплата = Ai * x + Bi, где х – оклад курьера;

Ai и Bi – коэффициенты, показывающие:

Ai – во сколько раз превышается значение х;

Bi – на сколько превышается значение х.   

Задание 4. Расчет штатного расписания.

1. Открыть редактор электронных таблиц MS Excel.

2. Создать таблицу штатного расписания ООО «Сибиряк» по приведенному

ниже образцу.

 

3. В ячейку Е3 занести произвольное число (это переменная х).

4. Рассчитать зарплату сотрудников. В ячейку D6 внесите формулу

 = B6*$Е$3+С6 (адрес ячейки Е3 задан в виде абсолютной ссылки). Скопировать

формулу в диапазон D7:D13.

5. Рассчитать заработную плату всех сотрудников, работающих в данной должности (столбец F).

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

ПРАКТИЧЕСКАЯ РАБОТА

« Использование финансовых функций в экономических расчетах »

          

Цель работы

1. Приобрести навыки работы использования электрон­ных таблиц как хранилищ информации на основе обработки списков и организации выборки данных в Microsoft Excel.

        

2 Формируемые образовательные результаты

    Образовательные результаты У2, У3, З4, ОК5, ПК1.2

Обеспеченность занятия

ü Персональный компьютер с установленными программами Microsoft Office.

ü Принтер.

4 Вопросы для самоподготовки

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

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

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

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

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

Литература

Мочула В.Г. Excel 2007. Расчеты и анализ: учебник / В.Г. Мочула. - Москва, ФЕНИКС, 2010.

Содержание заданий

Задание 1 Создание списка Клиенты

Задание 2 Создание списка Товары

Задание 3 Создание бланка заказа