Окремі показники виробничої діяльності фірми по виробу а

Ціна одиниці продукції, грн.	Величина попиту на один тиждень, тис. шт.	Витрати на обробку, тис. грн.
10,5	6+(N+P)/100	10,1 + N/(100-Р/2)*
	8+(N+P)/100	11,2 + N/(100-Р/2)
9,5	10+(N+P)/100	16,5 + N/(100-Р/2)
	12+(N+P)/100	24,6 + N/(100-Р/2)
8,5	14+(N+P)/100	32,4 + N/(100-Р/2)
	16+(N+P)/100	41,1 + N/(100-Р/2)
7,5	18+(N+P)/100	45,3 + N/(100-Р/2)
	20+(N+P)/100	55,1 + N/(100-Р/2)
6,5	22+(N+P)/100	59,9 + N/(100-Р/2)

* для студентів використовувати знак „+” за умови, якщо номер групи в якій вони навчаються є парним числом. У іншому випадку використовувати знак „–”.

 

Порядок виконання роботи:

1. За допомогою графічних засобів Ms Excel, методом лінійного однофакторного кореляційно-регресійного аналізу на підставі даних таблиці 4.1, визначити вплив:

· зміни ціни на величину тижневого попиту;

· зміни обсягу виробництва продукції на тижневу величину витрат.

2. За допомогою “що, якщо” (what-if) аналізу необхідно визначити приблизне значення точки беззбитковості для виробу А в діапазоні цін від 7 до 10 грн., з кроком 0,25 грн., створюючи сценарії шляхом копіювання всієї моделі (яка включає один стовпчик).

3. За допомогою “що, якщо” (what-if) аналізу та аналізу чутливості визначити оптимальну оптову ціну продажу виробу А з точністю до 0,5 % (в діапазоні + 3,5%), створюючи сценарії шляхом копіювання всієї моделі (яка включає один стовпчик).

4. Зробити висновки з графічним відображенням тенденцій зміни всіх досліджуваних характеристик в діапазоні цін від 7 до 10 грн., з кроком 0,25 грн.

 

Теоретичні відомості:

1. Для проведення лінійного однофакторного кореляційно-регресивного аналізу графічним методом засобами Ms Excel необхідно виділивши потрібний діапазон даних викликати команду Вставка/Діаграма... Наприклад:

	або
Примітка. Дані, що використовуються при поясненні належать умовному студенту групи 22 з варіантом 1

В меню, що з’явилося на вкладці Стандартные вибрати Тип: точечная і натиснути кнопку Далее.

В наступному контекстному меню оформляються параметри графіку: його назву, назви осей, одиниці виміру, та натиснути кнопку Готово. На графіку, що з’явився підводимо курсор миші до будь-якої точки з даними і натискаємо праву клавішу миші (викликаєте контекстне меню). В переліку команд меню вибираєте Добавить линию тренда...

В новому меню на вкладці Тип вибираємо Линейная, а на вкладці Параметры курсором миші ставите відмітку навпроти Показать уровнение на диаграмме. Натискаєте ОК.

 

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

 

Де * ** ***	– Довільне число в межах від 7 до 10; – Даний параметр Моделі вводиться згідно особистого варіанта студента; – Даний параметр є результатом лінійного однофакторного кореляційно-регресійного аналізу.

 

Для проведення “що, якщо” аналізу в клітки С3, С4,..., N3 вводимо значення 7,25 7,50... 10 грн. Далі виділяємо діапазон клітинок В5:В23 і копіюємо їх значення в діапазон С5:N23. В результаті отримуємо наступний діапазон даних.

 

З отриманих даних можна зробити висновок, що точка беззбитковості знаходиться у діапазоні цін від 7 до 7,25 грн., з об’ємом продаж менше 20 тисяч штук виробів. На це вказує додатній прибуток в 3,78 тис. грн. за умови коли ціна нашої продукції буде 7,25 грн.

 

3. З вище наведених даних також видно, що найбільш оптимальною ціною, яка дозволяє отримати максимальний прибуток є 9,25 чи 9,50 грн. За допомогою аналізу чутливості проведемо уточнення наших розрахунків. Для цього за 100 % приймемо, наприклад, ціну 9,25 грн. і у визначеному завданням даної лабораторної роботи діапазоні проведемо додаткове дослідження. Як наслідок отримаємо наступний перелік даних:

 

Дані додаткового дослідження не дають остаточної відповіді з точністю до 1 копійки на питання про оптимальну ціну, хоча звузили діапазон пошуку з 25 до 5 копійок. В результаті отримано новий ціновий діапазон від 9,34 до 9,39 грн., що дає змогу отримувати 21,86 тис. грн. прибутку до сплати податків.

 

4. Для здійснення остаточних розрахунків необхідно відобразити результати отримані внаслідок виконання завдання 2 в графічній формі. Для цього виділяємо діапазон клітинок А18:N23 і в меню Вставка\Диаграма... на вкладці Стандартные вибираємо тип діаграми під назвою График. В наступних контекстних меню оформляються параметри графіку: його назву, назви осей, одиниці виміру.

 

 

 

Як наслідок необхідно отримати схожий рисунок, що наведено нижче та на підставі розрахункових і графічних даних зробити необхідні висновки.

 

Лабораторна робота № 5