Построение и анализ финансовой модели по предварительному описанию бизнеса

Министерство  образования и науки РФ

Федеральное государственное  бюджетное образовательное

учреждение  высшего профессионального образования

«Тульский государственный  университет»

 

 

 

Кафедра «Финансы и менеджмент»

 

 

КУРСОВАЯ РАБОТА

 

по дисциплине

«Финансовый менеджмент»

на тему:

 

«Построение и анализ финансовой модели по предварительному описанию бизнеса»

 

 

 

 

Выполнил:   Синицын  Д.И.___________                          ст. гр.  730782

                     

 

 

Проверил: _____________                               доц., к.э.н. Ушакова Н.В.

 

 

 

 

 

 

Тула 2012

Содержание

 

1. Описание ситуации и исходные данные……………………………………...3    

2. Модель отчета  о прибыли (убытках) компании  «Дайле Групп»……………4      

3. Результаты расчетов по модели при различных ограничениях……………..5   

4. Диаграммы объема производства компании «Дайле Групп» при различных ограничениях……………………………………………………………………..10

5. Анализ чувствительности прибыли компании «Дайле Групп» к изменению объема используемых ресурсов………………………………………………...12    

6. Анализ граничных условий прибыльности производства компании «Дайле Групп» …………………………………………………………………………....14    

    

Список использованной литературы…………………………………………15     

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1. Описание ситуации и исходные данные

 

Компания «Дайле Групп»  планирует объем производства на ближайший год. В распоряжении правления имеются следующие данные по каждому из выпускаемых изделий:

 

 

 

Требуется:

1. С учетом всех  данных определить оптимальный,  с точки зрения максимизации  прибыли, состав продукции, если:

•   А.  Имеются  ограничения по рабочему времени - 50, 000 час

•   В.   Имеются  ограничения по сырью - 50, 000 кг

•   Имеются ограничения  А и В по обоим видам ресурсов одновременно.

 

2. Какие дополнительные  факторы следует принять во  внимание?

 

 

2. Модель отчета о прибыли (убытках) компании «Дайле Групп»

 

 

Таблица 1. Модель отчета о прибыли (убытках) компании «Дайле Групп»

3. Результаты расчетов по модели при различных ограничениях

 

Результаты  расчетов по модели при ограничении только по рабочему времени выглядят следующим образом:

 

Таблица 2. Планируемый  объем производства компании «Дайле Групп» при ограничении по рабочему времени 50000 ч.

 

Таким образом, оптимальный состав продукции с  точки зрения максимизации прибыли  при ограничении только по рабочему времени включает 5000 шт. изд. А, 5000 шт. изд. В, и 3571 шт. изд. С.

 

Чтобы учесть ограничения  по любому виду ресурсов при определении оптимального объема производства продукции с точки зрения максимизации прибыли, следует вычислить используемые ресурсы при данном объеме производства и сопоставить их с ограничениями, т.е. чтобы количество используемого ресурса при данном объеме производства не превышало ограничения по нему.

Чтобы вычислить  количество используемого ресурса, следует расход данного вида ресурса на одно изделие умножить на объем производства каждого изделия. Для данных расчетов в MO Excel используется функция СУММПРОИЗВ. Эта функция умножает соответствующие значения диапазона ячеек и возвращает сумму этих значений.

При определении  оптимальной номенклатуры продукции использование рабочего времени (ячейка D23) определяется как: =СУММПРОИЗВ(D15:F15;D18:F18). Аналогичным образом определяется и использование сырья. Далее, чтобы учесть ограничения по рабочему времени и сырью, оптимизируя целевую функцию (маржинальную прибыль), устанавливается, что использование рабочего времени и сырья должно быть меньше или равно соответствующих им ограничений. Также следует учесть, что планируемый объем производства изделий не должен превышать их спроса, и количество производимых изделий не должно быть отрицательным.

Чтобы оптимизировать целевую функцию, т.е. найти ту оптимальную номенклатуру продукции, при которой компания получит максимальную прибыль при данных ограничениях, в MO Excel используется симплекс – метод. Его использование для данной модели выглядит следующим образом:

1. оптимизируется целевая функция (маржинальная прибыль) в ячейке Q13 до максимума;
2. изменяются ячейки переменных с объемом производства изделий (диапазон D18:F18), т.е. определяется оптимальная номенклатура продукции, при которой компания получит максимальную прибыль;

 

3. прописываются ограничения:

• компания не может использовать рабочего времени и сырья больше, чем доступно (D23<=G23; D24<=G24); если ограничение имеется только по рабочему времени или только по сырью, следует удалить другое ограничение;

• объем производства изделий не должен превышать спроса на них (D18:F18<=D17:F17);
• количество производимых изделий не должно быть отрицательно (D18:F18>=0);
• количество производимых изделий должно быть целым числом (D18:F18=целое); т.к. в процессе итераций при использовании симплекс-метода числа в ячейках изменяемых переменных часто остаются дробными, следует прописать это ограничение.

 

Далее используется команда «Поиск решения», в процессе которого целевая функция (маржинальная прибыль) оптимизируется до максимума, определяется оптимальная номенклатура продукции при соответствующих  ограничениях.

Рассмотрим  результаты расчетов по модели при ограничении только по сырью. Соответствующие данные представим на следующей странице.

 

 

 

 

 

 

 

 

 

 

 

Результаты  расчетов по модели при ограничении только по сырью выглядят следующим образом:

 

Таблица 3. Планируемый  объем производства компании «Дайле Групп» при ограничении по сырью 50000 кг

 

Таким образом, оптимальный состав продукции с  точки зрения максимизации прибыли  при ограничении только по сырью включает 5000 шт. изд. А, 1111 шт. изд. В, и 5000 шт. изд. С.

Рассмотрим  результаты расчетов по модели при ограничении по рабочему времени и сырью одновременно. Результаты расчетов по модели при ограничении по рабочему времени и сырью одновременно выглядят следующим образом:

 

Таблица 4. Планируемый  объем производства компании «Дайле Групп» при ограничении по рабочему времени 50000 ч и сырью 50000 кг

Таким образом, оптимальный состав продукции с  точки зрения максимизации прибыли  при ограничении по рабочему времени и сырью одновременно включает 5000 шт. изд. А, 1320 шт. изд. В, и 4622 шт. изд. С.

 

4. Диаграммы объема производства компании «Дайле Групп» при различных ограничениях

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5. Анализ чувствительности прибыли компании «Дайле Групп» к изменению объема используемых ресурсов

 

1. Анализ чувствительности прибыли к изменению используемого рабочего времени.

При ограничении  только по рабочему времени используемое рабочее время составляло 49997 ч., увеличим это значение на 10%. Чтобы  это сделать, необходимо увеличить  объем производства изделия С (т.к. обьем производства изд. А и В  максимален и не может превышать спроса на них) на такую величину, чтобы значение используемого рабочего времени стало бы на 10% больше, т.е. составило бы: 49997*0,1+49997 = 54995 ч. Для этого используем функцию «Подбор параметра», установив в ячейке используемого рабочего времени значение 54995 ч., и изменяя при этом значение ячейки с объемом производства изделия С. Тогда объем производства изделия С составит 4285 изд., а маржинальная прибыль при данном объеме производства составит 610665 руб.

Т.о., коэффициент  чувствительности прибыли к изменению используемого рабочего времени составит:

Крв = ((610665-561399)/561399)*100/((54995-49997)/49997)*100= =8,78%/10% = 0,9.

 

То есть, при  изменении используемого рабочего времени на 1% прибыль изменится на 0,9%.

 

2. Анализ чувствительности прибыли к изменению используемого сырья.

При ограничении  только по сырью объем используемого  сырья составлял 49999 кг, увеличим это значение на 10%. Чтобы это сделать, необходимо увеличить объем производства изделия В (т.к. объем производства изд. А и с максимален и не может превышать спроса на них) на такую величину, чтобы значение используемого сырья стало бы на 10% больше, т.е. составило бы: 49999*0,1+49999 = 55003 кг. Для этого используем функцию «Подбор параметра», установив в ячейке используемого сырья значение 55003 кг, и изменяя при этом значение ячейки с объемом производства изделия В. Тогда объем производства изделия В составит 1667 изд., а маржинальная прибыль при данном объеме производства составит 1050030 руб.

Т.о., коэффициент  чувствительности прибыли к изменению используемого сырья составит:

Кс = ((560010-543330)/543330)*100/((55003-49999)/49999)*100= =3,07%/10% = 0,3.

 

То есть, при  изменении используемого сырья  на 1% прибыль изменится на 0,3%.

 

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

 

 

 

 

 

 

 

 

 

 

 

6. Анализ граничных условий прибыльности производства компании «Дайле Групп»

 

 

1. Минимальная цена продажи изделий, при которой бизнес остается прибыльным, составляет:

Ц_А = 27;

Ц_В = 60;

Ц_С = 51.

 

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

 

2. Максимальная цена закупки сырья для каждого изделия, при которой бизнес остается прибыльным, составляет:

З_сА = 51;

З_сВ = 84;

З_сС = 99.

 

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

 

 

 

 

 

 

 

Список  использованной литературы

 

1. Ушакова Н.В. Учебное пособие по финансовому моделированию. – Тула, 2008. – 166 с.

2. Карасева И.М. Финансовый менеджмент: Учебное пособие по специализации «Менеджмент организации». - М.: Омега-Л, 2007. - 335 с.

3. Ковалев В.В. Финансовый менеджмент. Теория и практика - М.: Финансы и статистика, 2007. – 267 с.