Решение. Практическая работа

Практическая работа

Тема: Анализ прибыли (таблица подстановки)

Задание

1. Рассчитайте прибыль по данным и формулам Таблицы 1

2. Проанализируйте, как изменится прибыль при изменении· одного параметра - количества продукции в диапазоне от 50 до 500 (шт.) с шагом 50 шт.; · двух параметров - количества продукции в диапазоне от 50 до 500 с шагом 50 (шт.) и налога в диапазоне от 5000 до 30000 с шагом 5000 (р.).

3.. Постройте диаграммы, отражающие изменение прибыли.

Таблица 1. Исходные данные

Цена	700,00р.
Количество
Доход	Цена * Количество
Расходы	0,2 * Доход + (Количество)^2 * 1р.
Налог
Прибыль	Доход – Расход - Налог

Решение

1. Введите данные на Лист Excel и выполните необходимые вычисления по формулам Таблицы 1. Рассчитанная прибыль – это в данном случае – ЦФ.

2. Подготовьте таблицы подстановки векторную и матричную (рис. 1.).

Рис 1. Фрагмент рабочего листа с расчетом прибыли и подготовленными ТП

3. Для переноса целевой функции расчета прибыли из ячейки B6 в ячейки B9 и D9 подготовленных таблиц подстановки используйте формулу адресной ссылки на ячейку с ЦФ – =B6 (Рис.2).

Рис 2. Фрагмент рабочего листа с расчетом прибыли в режиме формул

4. Выделите векторную ТП и примените команду ДАННЫЕ-ТАБЛИЦА ПОДСТАНОВКИ

5. Заполните открывшееся диалоговое окно: подстановка выполняется по строкам

– по одному параметру – «Количество», выполните ссылку на ячейку подстановки, в которой содержится первоначальная информация о количестве продукции – 150 – ячейку B2 (Рис.3).

6. Введите команду (OK) и получите результаты. (Рис.4).

Рис. 3. Заполнение диалогового окна ТП для векторной ТП

7. Повторите пункты 3 - 5 для матричной ТП и сделайте ссылки на ячейки подстановки – по строкам – на ячейку «Налог» – B5, а по строкам – «Количество» – B2 (Рис. 4).

Рис. 4. Заполнение диалогового окна для матричной ТП и результаты для

векторной ТП

8. Окончательные результаты представлены на Рис.5.

Рис.5. Фрагмент рабочего листа с результатами.

Полученные результаты позволяют оценить влияние изменения параметров «Количество» и «Налог» на прибыль. Таким же образом в нашем примере можно с помощью ТП проанализировать зависимость прибыли от изменения цены. Кроме того, задав процентную ставку налога и рассчитав налог, можно далее произвести анализ прибыли и от процентной ставки налога.

9. Постройте две диаграммы, используя матричную ТП и взяв два отдельных диапазона данных:

Для построения диаграммы «Прибыль - Налог» выделите диапазон данных $E$9:$J$19, запустите мастер диаграмм. На первом шаге выберите тип диаграммы – «Точечная со значениями, соединенными сглаживающими линиями», на втором шаге - диапазон данных уже выбран, установите переключатель «в строках», а на закладке РЯД в поле «Имя» укажите имена каждого ряда данных по строкам, ссылаясь каждый раз на ячейку с количеством (Рис. 6)

Рис 6. Построение диаграммы – шаг третий

Для построения диаграммы «Прибыль - Количество» выделите диапазон данных $D$10:$J$19, запустите мастер диаграмм. На первом шаге выберите тот же тип диаграммы, на втором шаге - «Диапазон данных» - установите переключатель «в столбцах», а на закладке РЯД в поле «Имя» укажите имена каждого ряда данных по столбцам, ссылаясь каждый раз на ячейку с налогом (Рис. 7).

Рис 7. Построение диаграммы – шаг третий

Диаграммы наглядно отражают зависимость прибыли как от количества, так и от налога. (Рис.8.)

10. Сохраните результаты расчета в книге Excel с именем «Расчет прибыли»

Рис 8. Диаграммы по результатам вычислений

Дата добавления: 2015-09-06