Создайте таблицу подстановки с двумя переменными – процентной ставкой и сроком вклада.

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

1. Скопируйте таблицу подстановки и вставьте ее правее первой (через столбец).

2. Удалите значения столбцов Коэффициент и Сумма выплат.

3. Введите формулу исследуемой зависимости (Сумма выплат) в ячейку с названием Процент. В ячейки следующих столбцов этой же строки введите значения срока вклада – 5, 10, 15, 20.

4. Выделите диапазон данных всей новой таблицы и возьмите команду Данные – Анализ «что-если» – Таблица данных.

5. В поле Подставлять значения по строкам в введите ссылку на ячейку со значением процентной ставки (5%) в исходной таблице, в поле Подставлять значения по столбцам в введите ссылку на ячейку со значением срока вклада (20 лет) в исходной таблице. ОК. В результирующей таблице получены суммы выплат при различных процентных ставках и сроках вклада.

Задание 4

Поиск решения

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

 

1. Создайте таблицу:

 

	Издержки	Количество товара (шт.)	Всего издержек	Маржа	Прибыль	Объем продаж
Товар 1	456,23р.			5%
Товар 2	23,60р.			7%
Товар 3	1 699,00р.			13%
Товар 4	366,00р.			10%
Товар 5	96,00р.			9%
Товар 6	3,70р.			10%
Суммы

 

Значения в столбце Всего издержек получаются путем умножения Издержек на Количестве товара. Значения в столбце Прибыль являются произведением столбцов Всего издержек и Маржа. Объем продаж получается путем суммирования Всего издержек и Прибыли. Значения в строке Суммы являются суммами значений данных по столбцам (вычисляйте их с помощью функции Автосумма). Вычислять значение Суммы для столбца Маржа нет необходимости.

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

· На используемом оборудовании можно производить не более 35000 единиц товара.

· Общие издержки не должны превышать 4,5 млн. руб.

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

 

2. Запустите процедуру поиска решения, команда Данные - Поиск решения (если такой команды нет, включите ее через Параметры Excel - Надстройки — Поиск решения - Перейти - Выбрать необходимые надстройки - ОК).

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

4. Задайте значение для целевой ячейке - активизируйте переключатель Значению и введите в поле ввода значение 4500000.

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

6. Теперь необходимо указать два ограничения. Щелкните на кнопке Добавить, откроется окно Добавление ограничения. В поле Ссылка на ячейку введите адрес ячейки, содержимое которой должно удовлетворять заданному ограничению (общее количество товара). В поле Ограничение укажите значение, которое будет выступать в качестве ограничения (35000) Установите оператор, определяющий отношение между значением ячейки и ограничением (<=). Это для первого ограничения. Щелкните на кнопке Добавить. Диалоговое окно Добавление ограничений останется открытым.

7. Добавьте следующее ограничение, которое заключается в том, что искомое количество единиц товара должно быть целым числом. Установите ссылки на ячейки, где лежат данные с количестве товара и в поле Оператора выберите ЦЕЛ. Щелкните кнопку Добавить.

8. Добавьте ограничение по сумме издержек.

9. В окне Поиск решения в поле Ограничения вы увидите все три ограничения. Чтобы процесс вычисления начался нажмите кнопку Выполнить.

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

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

 

Задача для самостоятельного решения:

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

	Телевизор	Стерео	Ак. сист.
Количество->
Наименование изделий	Склад	Использовано
Шасси
Кинескоп
Динамик
Блок питания
Элек. плата
Прибыль:
По видам изделий
Всего
Уменьшение коэффициента отдачи К	0,9

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

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

Телевизор: = 75*количество Телевизоров ^{^} К

Стерео: =50* количество Стерео ^{^} К

Ак. система: =35*количество Ак. Сист. ^{^} К

В строке Всего - сумма прибыли по видам изделий (функция Автосуммирования)

 

Параметры задачи:

Результат - Общая прибыль. Цель - получение наибольшей прибыли.

Изменяемые данные - Количество выпускаемых изделий каждого вида.

Ограничения:

· Количество использованных комплектующих не должно превышать их запаса на складе.

· Количество выпускаемых изделий должно быть больше 1 и целым.

В формулу прибыли на изделие входит коэффициент ^{^}К (в степени K), учитывающий уменьшение прибыли с ростом объема. В ячейке содержится 0,9, что делает задачу нелинейной. Если изменить значение коэффициента на 1,0 (если прибыль не зависит от объема производства) и повторно запустить процесс поиска решения, найденное ранее оптимальное решение будет другим. Данное изменение делает задачу линейной.

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

 

Задание 5

Подведение итогов

1. Создайте аналитический документ о работе автосалона «Счастливое колесо», содержащий следующие данные:

Продавец	Марка автомобиля	Дата выпуска автомобиля	Оборот	Дата

 

Продавцов в салоне 7 (введите 7 фамилий), продаются автомобили 5 марок (введите 5 названий, не используйте цифры), 2000-2003 года выпуска (введите 4 года), временной период - первый квартал (заполните ячейки датами с 01.01.2010 по 31.03.2010). Скопируйте данные первых 4 столбцов на весь временной период. Для этого выделите введённые данные и растяните их с помощью правого нижнего уголка выделения. При копировании года выпуска автомобилей держите зажатой клавишу Ctrl.

3. Присвойте листу с таблицей имя Данные.

4. Скопируйте таблицу на второй лист и присвойте ему имя Итоги.

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

1. упорядочите таблицу по полю Продавец, затем по полю Марка (действия производите одновременно, команда Данные - Сортировка).

2. Выделите любую ячейку таблицы, возьмите команду Данные - Промежуточные итоги.

3. Задайте параметры: при каждом изменении в поле Продавец выполнить операцию Суммы и добавить итоги по полю Оборот.

4. Установите галочку в окошке "Заменить текущие итоги" и "Итоги под данными", ОК.

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

1. Откройте окно Промежуточные итоги.

2. Задайте параметры: при каждом изменении в поле Продавец выполнить операцию Количества и добавить итоги по полю Марка.

3. Снимите галочку c "Заменить текущие итоги" ОК.

4. Просмотрите все уровни структуры, оперируя со знаками «+» и «-» в левой части окна.

12	Поделиться:

Воспользуйтесь поиском по сайту: