Практическое занятие 2. Создание диаграмм и графиков.

Задание 1.

1.Ввести исходные данные об объёме строительных работ (рис. 115).

 

Рис. 115

 

2.Создать круговую и точечную диаграммы.

3.Создать график, добавить линию тренда и уравнение.

 

Задание 2.

 

Составить расчетную таблицу для определения прибыли, дохода от продаж и суммарных расходов фирмы за месяц, используя следующие формулы.

Прибыль определяется по формуле

 

(1)

где - доход от продаж (валовый доход),

- суммарные затраты.

Доход от продаж рассчитывается по формуле

 

, (2)

 

где - цена изделия,

- количество проданных изделий (объем продаж).

Суммарные затраты рассчитываются по формуле

 

, (3)

 

где - постоянные затраты (не зависят от объема продаж), =200000р.

- переменные затраты (пропорциональны объему продаж).

Переменные затраты рассчитываются по формуле

 

, (4)

 

где - затраты на покупку материалов,

- затраты на сдельную оплату труда.

Считаем, что затраты на материалы рассчитываются по формуле

 

, (5)

 

где - коэффициент, учитывающий долю затрат на материалы в цене изделия,

Считаем, что затраты на сдельную зарплату рассчитываются по формуле

 

, (6)

 

где - коэффициент, учитывающий долю затрат на зарплату в цене изделия.

 

Составить таблицу расчета (рис. 115а), в ячейки В9-В14 ввести расчетные формулы.

 

Рис. 115а

 

Выделить диапазон ячеек В5-В14 и скопировать их в диапазон ячеек С5-Н14. Выполнить расчеты для заданной серии значений объема продаж. Результаты расчетов приведены на рис. 115б.

 

 

Рис. 115б

 

3. Выделить диапазон ячеек А4-Н14 и для него, используя мастер диаграмм и выбирая точечную диаграмму, построить графики.

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

Диаграмма приведена на рис. 115в

 

Рис. 115в

 

5. Определить по графикам объем продаж, при котором валовый доход равен суммарным затратам (точка безубыточности).

Данный лист с именем Лист1 переименовать в Анализ безубыточности.

 

Задание 3.

После построения расчетной модели и проведения расчетов часто возникает необходимость в анализе влияния исходных данных на выходные результаты. Такие расчеты введены в качестве самостоятельных этапов в программах для разработки бизнес-планов (например, ProjectExpert) и программах анализа финансового состояния предприятия (напрмер, AuditExpert). Эти этапы называют обычно анализом чуствительности.

Изменяя поочередно в некоторых пределах, например, от -15% до +15%, исходные данные относительно их базового значения, рассчитывают соответствующие отклонения выходных величин от их базового значения. Для сравнительного анализа влияния исходных данных на выходную величину необходимо расположить все графики на одном рисунке, чтобы можно было выделить величины слабо или сильно влияющие на выходную величину.

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

 

 

 

 

Лист Анализ безубыточности (2) переименуем в лист Анализ чувствительности и изменим заголовок таблицы. В качестве опорной (базовой) точки выберем точку с объемом продаж, равном 3000 единиц. Скопируем содержимое ячейки F4 (q=3000) в ячейки B4:H4 и выделим желтым цветом столбец Е (точнее, диапазон E4:E15)(см. рис. 115г)

 

 

Рис. 115г

 

Будем изменять сначала объем продаж q в пределах от -15% до +15% от базового значения в 3000 единиц и посмотрим, в каких пределах будет изменяться прибыль. Расчеты приведены на рис. 115д.

 

 

Рис. 115д

 

В ячейках D4, C4, B4, F4, G4, H4 введены соответственно формулы E4*0,95, E4*0,9, E4*0,85, E4*1,05, E4*1,1, E4*1,15. Изменения прибыли в таблице приведены в абсолютных единицах, а нам необходимо получить результаты в относительных единицах, в % от базового значения прибыли. Для этого присвоим ячейке Е14 имя - базовое_значение - и в ячейку D15 введем формулу (D14-базовое_значение)/базовое_значение*100. Скопируем эту формулу в ячейки B15, C15, F15, G15, H15. Результаты приведены на рис. 115е.

 

 

Рис. 115е

 

То есть, изменение входной величины q в пределах от -15% до +15% приводит к изменению выходной величины – прибыли - в пределах от -30% до +30 %.

Такой же анализ необходимо провести для входных величин p, Км, Кзп. При изменении одного параметра все остальные входные параметры остаются неизменными и равными базовым значениям (базовые значения приведены в желтом столбике).

Для того, чтобы сохранить результаты расчетов, соответствующие изменению величины q, необходимо выделить диапазон ячеек B15:H15 с результатами расчетов, выбрать команду Копировать, затем выделить ячейки В17:Н17, выбрать команду Специальная вставка и вставить Значения. (см. Рис. 115ж).

 

 

Рис. 115ж

 

Поскольку эти результаты соответствуют изменению объема продаж q скопируем содержимое ячейки А4 в ячейку А17 (рис. 115з)

 

 

Рис. 115з

 

Для дальнейших действий скопируем диапазон базовых значений исходных данных в ячейки J4:J14.

 

 

Затем выполним следующие действия:

Выделим и скопируем содержимое ячеек с формулами B4:H4 в ячейки B5:H5.

В ячейку Е5 введем базовое значение р =500 (из ячейки J5).

Восстановим базовые значения объема продаж q = 3000 в ячейках В4:Н4.

Скопируем результаты расчетов в ячейки В18:Н18 ( только значения ).

Скопируем содержимое ячейки А5 в ячейку А18.

Эти действия необходимо повторить для величин Км и Кзп. Результаты этих действий показаны на рис. 115и.

 

 

 

Рис. 115и

 

В ячейки В16:Н16 вставлены значения отклонений.

По результатам расчетов, представленных в ячейках А16:Н20, построим графики (рис. 115к)

 

Рис. 115к

Из графиков следует, что наиболее сильно влияет на прибыль коэффициент затрат на материалы Км, затем идут величины р и q инаименьшее влияние на прибыль оказывает величина Кзп.