Решение
Вычислим с помощью MS Excel для первых четырех лет выручку, затраты, прибыль общую и от 1 единицы продукции, рентабельность продукции и производства, фондоотдачу, выработку, коэффициент оборачиваемости, материальные затраты и материалоемкость (строки 8 – 18) по формулам в колонке 4 таблицы
Результаты выполнения второй части задания по прогнозу прибыли и рентабельности производства на 5-ий год находятся в ячейках I91 и I94 соответственно.
Для построения прогноза необходимо знать линию тренда изменения параметров. В данном примере при построении прогноза использовались следующие соображения. Анализ значений ячеек E91:H91 и E94:H94 позволяет установить увеличение темпа возрастания параметров от года к году, что характерно для экспоненциального вида тренда изменения параметров во времени.
Вычислить прогноз для экспоненциального тренда можно с помощью статистической функции РОСТ() из Мастер функций MS Excel.
Например, прогноз рентабельности производства на 5-ый год вычисляется в ячейке I94 как
=РОСТ(E94:H94;E80:H80;I80).
При этом окно задания параметров имеет вид
Аналогично вычисляется прогноз прибыли в ячейке I91
=РОСТ(E91:H91;E80:H80;I80).
При выполнении третьей части задания для анализа влияния параметров друг на друга построим график зависимости рентабельности производства как функции от выпуска продукции.
Это можно сделать с помощью Мастера диаграмм в MS Excel. Выделим ячейки аргумента E82:H82, удерживая нажатой клавишу Ctrl, выделяем протаскиванием ячейки функции E94:H94. Вызываем Мастер диаграмм: Вставка/Диаграмма. Выполняем следующую последовательность действий:
-
закладка Стандартные – Точечная / Далее
-
закладка Диапазон данных – Ряды в – строках / Далее
-
закладка Заголовки –
Название диаграммы – Зависимость рентабельности производства от выпуска продукции
Ось X – Выпуск продукции
Ось Y – Рентабельность производства
-
закладка Линии сетки – Ось Y – основные линии / Далее
-
Поместить диаграмму на листе – имеющемся / Готово.
Для более полного использования данными пространства диаграммы отредактируем оси. Окно Формат оси вызывается двойным щелчком при установке курсора на нужной оси:
Ось X
-
закладка Шкала
минимальное значение: 15000
максимальное значение: 45000
цена основных делений: 5000
цена промежуточных делений: 1000
ось Y пересекает в значении: 15000
Цена деления: Тысячи / ОК.
Ось Y
-
закладка Шкала
минимальное значение: 0.04
максимальное значение: 0.14
цена основных делений: 0.02
цена промежуточных делений: 0.004
ось Y пересекает в значении: 0.04
-
закладка Число – Процентный / ОК.
Результат приведен на рис.
Анализ графика позволяет предположить линейную зависимость рентабельности производства от выпуска продукции. Построим линию тренда линейного типа на нашем графике и вычислим величину достоверности аппроксимации R2. Для построения линии тренда:
-
выделим данные (после щелчка мышью на одной из точек графика появятся маркеры выделения)
-
щелкните правой кнопкой мыши на любую точку диаграммы и выберите команду Добавить линию тренда, в открывшемся окне Линия тренда установите:
-
закладка Тип – выберите наилучшим образом подходящий тип кривой аппроксимации (в нашем случае - Линейная)
-
закладка Параметры установите флаги: Показывать уравнение на диаграмме, Поместить на диаграмме величину достоверности аппроксимации R2 / ОК.
-
Результат приведен на рис.
Величина достоверности аппроксимации R2 является параметром точности приближения построенной линии тренда к исходным данным (чем ближе R2 к единице, тем выше точность аппроксимации). Значение R2 = 0,9998, что позволяет сделать вывод о линейной зависимости рентабельности производства от выпуска продукции.
Ответ: прибыль5 = 516398 р.; рентабельность производства5 = 16,04 %;
рентабельность производства зависит линейно от выпуска продукции.