Задания и порядок их выполнения. Задание 1. Рассмотрите порядок прогнозирования искомого результата при изменении любого параметра, входящего в расчёты

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

транспортные затраты на доставку приобретённых изделий составляют 400 руб. на машину (объём загрузки £ 6 изделий);

зарплата фиксированная (40 000 руб.);

налог на фонд оплаты труда составляет 30%;

налог на прибыль – 20%.

Порядок выполнения:

1. Запустите табличный процессор Excel: Пуск ñ Программы ñ Microsoft Office ñ Microsoft Office Excel 2010.

2. Введите данные и оформите таблицу по образцу, представленному на рис. 10.11.

Рис. 10.11. Начальный вид таблицы

3. Для ячеек В3, В4, В7:В9 и Е3:Е7 установите: формат числа – денежный; число десятичных знаков – 0; обозначение – р.

4. В ячейки рабочего листа введите следующие формулы:

Ячейка	Формула	Ячейка	Формула
В4	=В2*В3	Е4	=B7+B8+B9
В7	=ОКРУГЛВВЕРХ((B2/6);0)*400	Е5	=E3-E4
В9	=0.3*B8	Е6	=0.2*E5
Е3	=E2*B4	Е7	=$E$5-$E$6

5. В ячейку В3 введите значение 12000, в В8 – 40000. Убедитесь, что в результате вычислений чистая прибыль составила 14 400 р.

6. Осуществите автоподбор ширины столбцов А, В, …, Е (выделите столбцы А, …, Е; на вкладке Главная в группе Ячейки нажмите кнопку Формат () ñ Автоподбор ширины столбца).

7. Для проверки правильности решения задачи введите в ячейку В2 число 32, в ячейку Е2 число 0.25 и убедитесь, что программа произвела пересчёт по формулам с новыми данными и в ячейке Е7 возник результат – 33 280 р. Верните прежнее значение ячейки Е2, равное 0.2.

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

Задание 2. Рассмотрите общий алгоритм прогнозирования результата в табличном виде при изменении одного параметра.

Порядок выполнения:

1. Подготовьте таблицу для наглядного представления результата прогнозирования. Для этого:

1.1. В ячейку А13 скопируйте содержимое ячейки А2, в ячейку В13 – ячейки D7.

1.2. В ячейки А15 и А16 введите числа 10 и 20 соответственно.

1.3. При помощи автозаполнения введите в ячейки А17:А24 последовательность чисел от 30 до 100 (выделите диапазон ячеек А15:А16, ухватите курсором маркер в правом нижнем углу выделенного фрагмента и, удерживая нажатой левую кнопку мыши, протяните вниз его до ячейки А24).

1.4. Для ячеек В14:В24 установите формат: денежный; число десятичных знаков – 0; обозначение – р.

1.5. Измените (по своему усмотрению) цвет шрифта заголовка и установите обрамление таблицы.

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

2.1. В ячейку В14 скопируйте содержимое ячейки Е7.

2.2. Выделите диапазон ячеек А14:В24 и на вкладке Данные (в группе Работа с данными) нажмите кнопку Анализ «что если» (), а затем выберите в списке пункт Таблица данных. Возникнет окно диалога «Таблица данных» (рис. 10.12).

2.3. В текстовое поле «Подставлять значения по строкам в:» окна диалога «Таблица данных» укажите абсолютный адрес ячейки $В$2 и щёлкните кнопку ОК. В ячейках В15:В24 возникнут значения величины прибыли в зависимости от количества проданных изделий.

Рис. 10.12. Окно «Таблица данных

2.4. Проверьте правильность Ваших действий: для 40 изделий прибыль должна составлять 32 960 р. При ошибке всю таблицу данных необходимо очистить и повторить действия заново.

Задание 3. Рассмотрите общий алгоритм прогнозирования результата в табличном виде при изменении двух параметров.

Порядок выполнения:

1. Подготовьте таблицу для наглядного представления результата прогнозирования. Для этого:

1.1. В ячейки D15:D24 скопируйте данные из ячеек А15:А24.

1.2. В ячейки Е14 и F14 введите числа 0,1 и 0,15 соответственно. Установите для них процентный формат (число десятичных знаков 0) и при помощи автозаполнения заполните ячейки G14:K14 значениями наценки (до 40% с шагом 5%).

1.3. Для ячеек Е15:К24 установите денежный формат (число десятичных знаков – 2; обозначение – р.).

1.4. Установите обрамление созданной таблицы данных.

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

2.1. В ячейку D14 скопируйте формулу из ячейки Е7.

2.2. Выделите диапазон ячеек D14:K24 и на вкладке Данные (в группе Работа с данными) исполните команду Анализ «что если» (), а затем выберите в списке пункт Таблица данных. Возникнет окно диалога «Таблица данных» (рис. 10.12).

2.3. В текстовое поле «Подставлять значения по столбцам в:» окна «Таблица данных» введите абсолютный адрес ячейки $Е$2, а в текстовое поле «Подставлять значения по строкам в:» – абсолютный адрес ячейки $В$2 и щёлкните кнопку ОК. В ячейках Е15:К24 возникнут значения величины прибыли в зависимости от количества проданных изделий и величины наценки.

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

2.5. Проверьте правильность вычислений: для 30 изделий при наценке 15% прибыль должна составлять 0 р. При несовпадении всю таблицу данных необходимо очистить и повторить действия заново.

3. Представьте в графическом виде результаты расчётов прибыли при изменении двух параметров. Для чего:

3.1. Выделите диапазон ячеек Е15:K24 и на вкладке Вставка выберите График (крайний левый в первой строке – кнопка ).

3.2. Откройте легенду диаграммы (щелчком мыши по надписи Ряд1) и, используя левую часть окна «Выбор источника данных» (рис. 10.13), присвойте (установите курсор на соответствующий ряд, нажмите на кнопку Изменить в окне «Элементы легенды (ряды)» и щёлкнете по ячейке в окне «Изменение ряда») рядам Ряд1, Ряд2, …, Ряд7 имена ячеек E14, F14, …, K14 соответственно.

Рис. 10.13. Окно «Выбор источника данных»

3.3. Используя правую часть окна «Выбор источника данных» (рис. 10.13), присвойте (установите курсор на соответствующую категорию, нажмите на кнопку Изменить в окне «Подписи горизонтальной оси (категории)» и в окне «Подписи осей» укажите диапазон ячеек D15: D24 и щёлками по кнопкам ОК закройте окна.

3.4. В группе Макеты диаграмм выберите Макет10 () и во вкладке «Заголовки» в текстовое поле «Название диаграммы:» введите текст Прогноз прибыли в зависимости от наценки, в текстовое поле «Название оси (горизонтальной оси)» – Количество изделий, в текстовое поле «Название оси (вертикальной оси)» – Прибыль.

3.5. Вызовом контекстного меню (щелчком правой клавиши) для оси значений прибыли установите формат оси: денежный, число десятичных знаков равно 0.

3.6. Сравните полученную диаграмму с диаграммой, представленной на рис. 10.14.

Рис. 10.14. Ожидаемый вид диаграммы

Задание 4. Рассчитайте количество проданных изделий, обеспечивающее заданную прибыль. Порядок выполнения:

1. В ячейку Е2 введите величину наценки, равную 20%.

2. Для установленной величины наценки определите количество изделий, обеспечивающее чистую прибыль, равную 100 000 р. Для чего сделайте активной ячейку Е7 и выполните команду Анализ «что если» () ñ Подбор параметра. Откроется диалоговое окно «Подбор параметра».

3. Убедитесь, что в окне диалога «Подбор параметра» в текстовом поле «Установить в ячейке» выведен абсолютный адрес ячейки Е7 ($Е$7).

4. Введите в поле «Значение» число 100000.

5. Укажите в поле «Изменяя значение в ячейке» абсолютный адрес ячейки В2 ($В$2) и щёлкните кнопку ОК. Осуществится подбор величины количества изделий указанных в ячейке В2, обеспечивающее при надбавке 20% прибыль в 100000 р. Возникнет диалоговое окно «Результат подбора параметра».

6. Проверьте правильность решения: в ячейке В2 количество изделий равно 75,916…. В диалоговом окне «Результат подбора параметра» щёлкните кнопку ОК.

Задание 5. Найдите план перевозки продукции от трёх поставщиков к четырём потребителям, обеспечивающий полное удовлетворение потребностей потребителей при минимальной суммарной стоимости всех перевозок, если цена перевозок единицы продукции от каждого поставщика конкретному потребителю задана в таблице, представленной на рис. 10.15 (ячейки В2:Е6), объём запасов продукции у поставщиков – в ячейках F2:F4, а потребностей потребителей – в ячейках В6:Е6, соответственно.

Порядок выполнения:

1. Добавьте новый рабочий лист, введите исходные данные и соответствующие пояснения (рис. 10.15).

Рис. 10.15. Начальное оформление рабочего листа

2. В ячейку D12 введите формулу для расчёта целевой функции

=СУММПРОИЗВ(B2:E4;B8:E10)

3. В ячейку В14 введите формулу для расчёта левой части ограничения на потребности первого потребителя

=СУММ(B8:B10)

и скопируйте её (для остальных потребителей) в ячейки C14, D14 и E14.

4. В ячейку F8 введите формулу для расчёта левой части ограничения на потребности первого поставщика

=СУММ(B8:E8)

и скопируйте её (для остальных поставщиков) в ячейки F9 и F10.

5. Осуществите поиск решения. Для этого:

5.1. Вызовите на вкладке Данные (группа Анализ) команду Поиск решения (). Раскроется окно «Параметры поиска решения» (рис. 10.16).

5.2. В окне «Параметры поиска решения»:

· в поле «Оптимизировать целевую функцию:» укажите ячейку D12;

· установите флажок «Минимум»;

· в поле «Изменяя значения переменных:» укажите блок ячеек B8:E10;

· задайте ограничения на решаемую задачу (последовательно щёлкая курсором по кнопке Добавить и пользуясь окном «Добавление ограничения» (рис. 10.17); по окончании ввода последнего ограничения щёлкнуть курсором по кнопке ОК):

B14 = B5,

C14 = C5,

D14 = D5,

E14 = E5,

F8 = F2,

F9 = F3,

F10 = F4;

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

5.3. Появится окно «Результаты поиска решения», а в ячейках B8:E10 отобразятся сами результаты (план перевозок) (рис. 10.18). В них содержатся значения переменных, при которых достигается минимум целевой функции (значение ячейки D12). Для сохранения найденного решения щёлкните курсором по кнопке ОК.

Рис. 10.16. Окно «Параметры поиска решения»

Рис. 10.17. Окно «Добавление ограничения»

Рис. 10.18. Результаты решения задачи

5.4. Прокомментируйте содержание найденного плана перевозки продукции от поставщиков к потребителям.

Продемонстрируйте результаты работы преподавателю.

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

1. Постановка задачи прогнозирования.

2. Перечислите типы средств Анализа «что если» в Excel.

3. Порядок создания таблицы данных при изменении одного параметра.

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

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

6. Возможности Excel по поиску оптимальных решений (на примере транспортной задачи).