1. Для удобства построения графика безубыточности на том же листе организуйте таблицу с данными (см. рисунок 10). Ячейки C16, C18, C19 заполните нулями, ячейки Общих затратC20, D20 вычислите как сумму Переменных затрат и Постоянных расходов.
Рисунок 10
2. Шаг 1. Выделите ячейки С16:D20 и во вкладке Вставка выберите команду Точечная диаграмма с прямыми отрезками и маркерами.
Рисунок 11 – Построение диаграммы, шаг 1
3. Шаг 2. Затем в меню Работа с диаграммами во вкладке Конструктор выберите команду Строка/Столбец.
Рисунок 12 – Построение диаграммы, шаг 2
4. Шаг 3. Разместите легенду внизу – Работа с диаграммами/ Макет/ Легенда/ Легенда внизу (см. рисунок 13). Во вкладке Макет задайте единицы измерения по осям (млн. руб. и шт.) как Названия осей.
Рисунок 14 – Построение диаграммы, шаг 3
5. Шаг 4. В контекстном меню к вертикальной оси (щелчок правой кнопкой мыши наведенном на ось) в Формате оси задайте Цена деления: Миллионы.
Рисунок 14 – Построение диаграммы, шаг 4
6. Шаг 5. Удалите вручную подпись миллионы на диаграмме (щелкните на ней мышью и нажмите Delet). Чтобы добавить Точку безубыточности надо выбрать команду во вкладке Конструктор/ Выбрать данные/ Добавить/ и щелкая по соответствующим ячейкам задать Имя ряда, Значения Х и Значения Y (см. рисунок 15).
Рисунок 15 – Построение диаграммы, шаг 5
7. Шаг 6. Точка безубыточности находится на пересечении Общих затрат и Выручки, щелкните в это место левой а затем правой кнопкой мыши в контекстном меню задайте тип маркера (см. рисунок 16).
Рисунок 16 – Построение диаграммы, шаг 6
8. Шаг 7. В контекстном меню к точке безубыточности добавьте подписи данных, задайте в Формате подписей данных Значения X и Y. Диаграмма, которая должна получиться в итоге, представлена на рисунке 17.
Рисунок 17
Лабораторная работа 3
Анализ чувствительности
Анализ чувствительности определяет, как варьируются выходные данные при изменении входных. Для этих целей в MS Excel имеется такой инструмент как таблица подстановки. Таблица подстановки позволяет варьировать 1 или 2 параметрами.
Задание 1. Анализ чувствительности при варьировании одним параметром
Задача: Исследовать как прибыль, выручка и переменные издержки будут зависеть от цены товара.
Условие задачи. Входные предложения представлены в таблице 3.
Таблица 3
| Цена изделия, руб. | |
| Переменные затраты на единицу продукции, руб. | |
| Постоянные затраты, руб. | 3 400 000 |
Известна также зависимость спроса от цены: Спрос(Цена)= 1000000-800*Цена
Решение.
1. Создайте файл 3_1.xls. Заполните как на рисунке 18. Диапазону С1:С7 назначьте имена из ячеек В1:В7 (см. лабораторную работу 1).
Рисунок 18
2. Заполните ячейки С4:С7 следующими формулами см. таблицу 4 и рисунок 19.
Таблица 4
| Спрос | =1000000-800*Цена |
| Выручка | =Спрос*Цена |
| Переменные издержки | =Себестоимость_единицы*Спрос |
| Прибыль | =Доход-Постоянные_издержки-Переменные_издержки |
3. Задайте соответствующим ячейкам денежный формат см. рисунок 19.
Рисунок 19
4. В этом задании у нас изменяется только цена. Мы будем варьировать ценой товара в диапазоне от 150 до 800 рублей с шагом в 25 рублей. Для этого в ячейку В11 введите число 150, а в ячейку В12 число 175. Выделите обе ячейки и подведите мышку к нижнему правому углу ячейки В12 (курсор мышки должен стать черным крестиком) и протяните вниз автозаполняя ячейки до 800 (см. рисунок 20).
 |
Рисунок 20 – Автозаполнение ячеек
5. В ячейки B9:F9 введите названия как на рисунке 21. В ячейку С10 введите формулу =Прибыль. Аналогично заполните ячейки D10, E10, F10 (см. рисунок 21). Внимание – ячейки B10:F10 должны быть обязательно заполнены формулами, не вводите числа вручную, а ячейка В10 должна быть пустой.
Рисунок 21
6. Выделите диапазон ячеек B10:F37 Во вкладке Данные выберите Анализ «что-если» / Таблица данных, в появившемся диалоговом окне заполните поле Подставлять значения по строкам ссылкой на ячейку $C$1 (см. рисунок 22). У нас данные в столбцах, поэтому значения мы будем подставлять по строкам. В некоторых версиях Excel эта команда может называться Таблица подстановки.
Рисунок 22
7. Во вкладке Формулы / Параметры вычислений проверьте чтобы стояло вычисление формул Автоматически (см. рисунок 23).
Рисунок 23
После шагов 1-7 таблица должна выглядеть следующим образом см. рисунок 24.
Рисунок 24
8. Выделите ячейки С11:С37 и на вкладке Главная выберите команду Условное форматирование / Гистограмма. Тоже проделайте и с остальными столбцами (см. рисунок 25).
Рисунок 25
По полученным данным видим, что максимальную прибыль мы получаем при цене товара 700 руб.