Решение

Вычислим с помощью 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

• закладка Число – Процентный / ОК.

Результат приведен на рис.

Анализ графика позволяет предположить линейную зависимость рентабельности производства от выпуска продукции. Построим линию тренда линейного типа на нашем графике и вычислим величину достоверности аппроксимации R². Для построения линии тренда:

• выделим данные (после щелчка мышью на одной из точек графика появятся маркеры выделения)

• щелкните правой кнопкой мыши на любую точку диаграммы и выберите команду Добавить линию тренда, в открывшемся окне Линия тренда установите:

  • закладка Тип – выберите наилучшим образом подходящий тип кривой аппроксимации (в нашем случае - Линейная)

  • закладка Параметры установите флаги: Показывать уравнение на диаграмме, Поместить на диаграмме величину достоверности аппроксимации R² / ОК.

Результат приведен на рис.

Величина достоверности аппроксимации R² является параметром точности приближения построенной линии тренда к исходным данным (чем ближе R² к единице, тем выше точность аппроксимации). Значение R² = 0,9998, что позволяет сделать вывод о линейной зависимости рентабельности производства от выпуска продукции.

Ответ: прибыль5 = 516398 р.; рентабельность производства5 = 16,04 %;

рентабельность производства зависит линейно от выпуска продукции.