Задача «Количественный анализ хозяйственной деятельности»

Условие задачи:

Показатели деятельности промышленного предприятия за 2 года представлены в табл. 26.

Таблица 26

Показатели	1–год	2–год
Продукция, т.р. (N)	79700	83610
Численность производственного персонала, чел. (K)	381	382
Оплата труда, т.р. (U)	9628	9894
Материальные затраты, т.р. (M)	52228	54434
Основные производственные фонды, т.р. (F)	74350	78581
Амортизация, т.р. (A)	8311	8463
Оборотные средства, т.р. (E)	16007	16241
Производительность труда (N/К)
Продукция на 1 руб. от оплаты труда (N/U)
Материалоотдача (N/M)
Фондоотдача (N/F)
Амортизациоотдача (N/F)
Оборачиваемость оборотных средств (N/E)

Требуется:

1. Вычислить показатели 8–13 согласно указанным формулам.

2. Добавить столбец «Темп роста, %», в котором следует вычислить темпы роста показателей, как отношение значения показателя для 2–го года к значению показателя для 1–го года.

3. Добавить столбец «Прирост на 1% продукции», в котором вычислить отношение прироста ресурса к приросту объема производства продукции.

4. Построить график для показателей «Темп роста, %» и «Прирост на 1%продукции», тип диаграммы – График, ряды данных – в столбцах. Для графика «Прирост на 1%продукции» указать вспомогательную ось.

Задача «Анализ рентабельности»

Условие задачи:

Вычислить рентабельность выпуска продукции для каждого года (табл. 26), пользуясь формулой расчета:

R– рентабельность;

P – прибыль.

1. Подготовить исходные данные для расчета рентабельности выпуска по годам, все промежуточные расчеты сохранить.

2. Построить график для сравнения уровней рентабельности по годам.

Задача «Анализ производственной программы»

Условие задачи:

Предприятие осуществляет выпуск продукции, на производство которой расходуются различного вида ресурсы (материалы, электроэнергия, зарплата, оборудование и т.п.). Известны нормативы переменных затрат на единицу продукции, а также накладные расходы (постоянные затраты), которые зависят от объема выпуска продукции – табл. 27.

Продукция продается на рынке, в зависимости от объема реализации изменяется цена и реализационные расходы – табл. 28.

Требуется:

1. Вычислить переменные и постоянные издержки на выпуск продукции в диапазоне 200–5000 шт.

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

Таблица 27

Нормативы переменных затрат, руб./шт.	Объем выпуска, шт.
	0	1500	5000
Материалы	6	4,5	4
Тара	5	5	6
Зарплата	1	0,85	0,7
Эл/энергия	0,02	0,05	0,06
Общие переменные расходы, руб./шт.
Нормативы постоянных затрат, руб.	Объем выпуска, шт.
	0	1500	5000
Арендная плата	1000	2000	3000
Зарплата управленческого персонала	1500	2000	3500
Реклама	300	500	700
Общие постоянные расходы, руб.

Таблица 28

Нормативы реализации продукции, руб.	Объем продаж, шт.
	1000	2000	5000
Цена, руб./шт.	14	13,5	12
Реализационные расходы, руб.	500	600	1000

Информационная технология:

1. Вставить новый лист – команда меню ПравкаЛист.

2. Переименовать лист в лист Производство и реализация – команда меню ФорматЛистПереименовать.

3. Разместить исходные данные для решения задач – табл. 27, 28.

4. Рассчитать нормативы общих переменных расходов, руб./шт. на выпуск единицы продукции для различных масштабов производства. Например, формула для общих переменных расходов, если объем выпуска не превышает 1500 шт., вводится в ячейку В7: =СУММ(B3:B6). Формулу размножить по ячейкам C7: D7.

5. Рассчитать нормативы общих постоянных расходов для различных масштабов производства.

Например, формула для общих постоянных расходов, если объем выпуска не превышает 2000 шт., вводится в ячейку В13: =СУММ(B10:B12). Формулу размножить по ячейкам C13: D13.

6. Разместить данные о предполагаемом выпуске и реализации продукции согласно табл. 29. Заполнить начальное значение Выпуска продукции – 200. С помощью команды меню ПравкаЗаполнитьПрогрессия заполнить все ячейки строки, прогрессия – Арифметическая, шаг – 200, предельное значение – 5000.

Таблица 29

	Объем выпуска
Выпуск продукции	200	400	600	800	и т.д. с шагом 200 до 5000
Переменные расходы
Постоянные расходы
Себестоимость выпуска
Выручка
Реализационные расходы
Доход
Прибыль
Рентабельность выпуска

7. Вычислить переменные расходы для каждого уровня производства с учетом норматива общих переменных расходов.  Этот норматив следует выбирать, используя встроенную функцию ГПР. В качестве искомого значения выступает «Выпуск продукции» (табл. 17). Для вычисления переменных расходов в ГПР таблица определяется как блок ячеек, содержащий нормативный объем выпуска, составляющие переменных затрат и общие переменные расходы (табл. 27). Адреса ячеек таблицы задаются как абсолютные. Номер строки для параметра «Общие переменные расходы» – 6. Диапазон просмотра задается как Истина, так как требуется найти не точное, а приближенное значение, не превосходящее искомое значение.

Например, формула для переменных расходов для объема выпуска 200 шт. вводится в ячейку В23: =B22*ГПР(B22;$B$2:$D$7;6;ИСТИНА). В22 – объем выпуска, результат функции ГПР – норматив общих переменных расходов на единицу продукции, соответствующий объему выпуска. Формулу размножить по ячейкам строки.

8. Вычислить постоянные расходы для каждого уровня производства с учетом норматива постоянных расходов.

 Норматив общих постоянных расходов следует выбирать, используя встроенную функцию ГПР. В качестве искомого значения выступает Выпуск продукции (табл. 17). Для вычисления постоянных расходов таблица для функции ГПР определяется как блок ячеек, содержащий нормативный объем выпуска, составляющие постоянных затрат и общие постоянные расходы (табл. 17). Адреса ячеек таблицы задаются как абсолютные. Номер строки для параметра Общие постоянные расходы – 5. Диапазон просмотра задается как Истина, так как требуется найти не точное, а приближенное значение, не превосходящее искомое значение.

Например, формула для постоянных расходов для объема выпуска 200 шт. вводится в ячейку В24: =ГПР(B22;$B$9:$D$13;5;ИСТИНА) В22 – объем выпуска, результат функции ГПР – норматив общих постоянных расходов на единицу продукции, соответствующий объему выпуска. Формулу размножить по ячейкам строки.

9. Вычислить себестоимость выпуска продукции для каждого уровня производства (объема выпуска).

Например, формула для расчета себестоимости объема выпуска в 200 шт. вводится в ячейку В25: = СУММ(B23:B24). Формулу размножить по ячейкам строки.

10. Изобразить графически изменение себестоимости выпуска для различных объемов выпуска продукции.

11. Вычислить выручку от реализации для каждого уровня объемов продаж с учетом зависимости цен от объема продаж.

12. Вычислить реализационные расходы для каждого уровня объемов продаж продукции.

 Нормативы цен и реализационных расходов выбирать согласно объему производства продукции, используя встроенную функцию ГПР. В качестве искомого значения выступает соответствующий объем выпуска (табл. 28). Таблица в функции ГПР определяется как блок ячеек, содержащий объемы продаж, цены и реализационные расходы, адреса ячеек задаются как абсолютные. Номер строки для параметра Цена – 2, Реализационные расходы – 3. Диапазон просмотра задается как Истина, так как требуется найти не точное, а приближенное значение, не превосходящее искомое значение.

Например, формула для вычисления выручки от реализации объема выпуска 200 шт. вводится в ячейку В26: =B22*ГПР(B22;$B$16:$D$18;2), В22 – объем выпуска, результат функции ГПР – цена продукции указанного объема продаж.

Формула для вычисления реализационных расходов для объема выпуска 200 шт. вводится в ячейку В27: =ГПР(B22;$B$16:$D$18;3;ИСТИНА), В22 – объем выпуска, результат функции ГПР – реализационные расходы для указанного объема продаж. Формулы размножить по ячейкам строк.

13. Вычислить доход для каждого уровня объемов производства продукции как разницу выручки и реализационных расходов.

Например, формула для вычисления дохода от реализации объема выпуска 200 шт. вводится в ячейку В28: =B26–B27. Формулу размножить по ячейкам строки.

14. Вычислить прибыль для каждого уровня объемов производства продукции как разницу дохода и общих затрат (себестоимости продукции).

Например, формула для вычисления прибыли от реализации объема выпуска 200 шт. вводится в ячейку В29: =B26-B27-B25. Формулу размножить по ячейкам строки.

15. Вычислить рентабельность выпуска как отношение прибыли к общим затратам.

Например, формула для вычисления рентабельности объема выпуска в 200 шт. вводится в ячейку В30: =B29/B25. Формулу размножить по ячейкам строки.

16. Изобразить графически доход для различных объемов выпуска продукции. Определить точки безубыточности.

17. Изобразить графически уровень рентабельности выпуска продукции, ориентировав ряд на другую ось.

18. Проанализировать график рентабельности, определить минимальный и максимальный уровень рентабельности.

19. Построить линии линейного тренда для кривых себестоимости и дохода. Вывести уравнение линий тренда и коэффициенты детерминации.

20. Решить уравнение для определения точки безубыточности.

21. Подготовить сценарии, в качестве изменяемых ячеек использовать ограничения объемов выпуска и продаж (табл. 17 и 18). Проанализировать изменение графиков.

22. Построить отчет по сценариям. Ячейки результата – ячейки себестоимости, дохода и рентабельности для двух уровней объема выпуска продукции: 2000 и 5000 шт.