Задания и порядок их выполнения. Задание 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. Осуществите Автоподбор ширины столбцов А, …, Е (выделить А, …, Е; Формат – Столбец).

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

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

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

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

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

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

1.3. При помощи автозаполнения введите в ячейки А17:А24 последовательность чисел от 30 до 100 через 10 (выделите диапазон ячеек А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.

Последовательность действий.

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).

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

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

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

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

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

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

B14 = B5, C14 = C5, D14 = D5, E14 = E5,

F8 = F2, F9 = F3, F10 = F4;

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

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

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

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