Варіант №1

Щойно заснована компанія «АЛЕКС» спеціалізується на тому, що виготовляє з двох компонентів (фруктів та тіста, що заморожено) яблучні пироги, обробляє їх (випікає, запаковує, доставляє) та продає місцевим бакалійним магазинам. Засновник компанії, Алекс Булкін, бажає побудувати модель у електронній таблиці Excel з метою дослідити можливості подальшого розширення своєї компанії.

Оскільки Булкіну необхідно негайно одержувати прибуток, вибір показника ефективності не викликає сумнівів, це – тижневий прибуток. Обмірковуючи ситуацію, Булкін робить висновок, що найбільш важливим його рішенням є визначення оптової ціни на пироги. Маркетинговий план Булкіна не передбачає зміну розмірів або якості його пирогів, а бакалійні магазини безпосередньо встановлюють свою надбавку до оптової ціни пирога. Таким чином, кількість та вартість пирогів, що продані, визначаються оптовою ціною, яку задав Булкін.

Завдання 1. Побудувати (використовуючи прості бухгалтерські залежності) модель задачі, що викладена, в Excel враховуючи наступні вхідні параметри

Ціна пирога 8,00 грн.

Попит 16 тис. шт.

Питомі витрати на виробництво 2,05 грн.

Питомі витрати на закупівлю начинки 3,48 грн.

Питомі витрати на закупівлю тіста 0,30 грн.

Постійні тижневі витрати 12 тис. грн.

та визначити тижневий прибуток.

Аналізуючи модель, Булкін помічає, що в його моделі ціна пирога та попит вважаються незалежними змінними. Він розуміє, що таке припущення нереалістичне: при інших однакових умовах зависока ціна приведе до зниження попиту, а в його моделі зв'язок між цими змінними не відображено.

Завдання 2. Визначити лінійну залежність попиту від ціни, якщо при ціні, що дорівнює 12 грн. за пиріг, попит буде дорівнювати нулю, а при ціні нижче 12 грн. кожне зниження ціни на 1 грн. приведе до зростання попиту на 4 тис. шт. щотижня. Уточнити модель задачі, тобто попит повинен обчислюватися за формулою, що здобута.

Далі Булкін вирішив провести аналіз чуйності, у ході якого дослідити вплив змін вхідних параметрів на показник ефективності. У даному випадку його цікавить вплив невеликих змін ціни на прибуток, яка є критерієм ефективності.

Завдання 3. Додати до моделі додаткові значення ціни: 6 грн., 7 грн., 9 грн., 10 грн. та 11 грн. Побудувати точковий графік залежності ціни пирога від тижневого прибутку. Аналізуючи графік визначити приблизно базову ціну, яка відповідає максимальному прибутку та точку беззбитковості.

Завдання 4. Використовуючи «Подбор параметра» визначити точне значення точки беззбитковості. Додати до моделі значення ціни, що знайдена.

Завдання 5. Використовуючи «Поиск решения» визначити точне значення базової ціни. Додати до моделі значення ціни, що знайдена.

Компанія є новою, і потребує завоювання споживчого ринку та залучення клієнтів. Булкін погодився б знизити ціну нижче базової та втратити частину прибутку, щоб добитися збільшення тижневого об’єму продажів (аналіз компромісів).

Завдання 6. Додати до моделі розрахунки, що відображають відсоткові зміни ціни, попиту та прибутку, по відношенню до базового рівня. Якщо Булкін вирішить зменшити ціну на 10%, то скільки відсотків він втратить у прибутку та на скільки відсотків збільшиться попит при цьому. Вкажіть точні значення, використовуючи вбудовані функції прогнозування.

Спочатку Булкін був задоволений своєю моделлю, але потім у нього виникли певні сумніви. Виявилося, що дійсний тижневий прибуток трохи менше, ніж той, що передбачався моделлю. Вивчивши модель, Булкін знайшов, що формула витрат на виробництво (=2,05*Попит), яка пропонується моделлю, не спрацьовує:

Таблиця 1. Дані по витратах на виробництво

Кількість пирогів, тис. шт.	Витрати на обробку, реальні, тис. грн.	Витрати на обробку, модельні, тис. грн.
	10,10	12,30
	11,20	16,40
	16,50	20,50
	24,60	24,60
	32,40	28,70
	41,10	32,80
	45,30	36,90
	55,10	41,00
	59,90	45,10

Завдання 7. Побудувати по даним таблиці графік, застосувати засіб «Линия тренда» та уточнити рівняння витрат на виробництво.

Завдання 8. Використовуючи групування, скрити технічні параметри моделі – відсотки та статті витрат.

Бізнес пішов угору. Булкін зібрався його розширити – узятися за випікання та продаж пирогів з іншими начинками: лимонною, полуничною та вишневою. Постійні витрати тепер не зв’язані з конкретним видом продукції, а рахуються загальними та складають 33 тис. грн. Вхідні параметри:

Вид Лим. Пол. Вишн.

Ціна пирога, грн. 8,32 9,57 9,32

Питомі витрати на купівлю начинки, грн. 2,20 3,90 3,10

Питомі витрати на купівлю тіста, грн. 0,28 0,35 0,33

Завдання 9. Додати три нових різновиди продукції, тим самим розширивши модель. Від старої моделі слід залишити лише базові дані. Питомі витрати на обробку та попит розрахувати по аналогії з яблучним пирогом. Додатково вирахувати підсумкові значення деяких параметрів (наприклад, попит) та загальні параметри для усіх різновидів продукції (наприклад, постійні витрати).

Завдання 10. Побудувати нормовану гістограму, де відобразити відсотковий розподіл витрат для різних видів пирогів.

Аналізуючи результати розрахунків моделі, Булкін прийшов до висновку, що не в змозі виробляти кількість пирогів, що запланована, якщо не введе на своєму підприємстві другу зміну. Це потребує додаткових витрат на сплату понаднормових, що відобразиться на виробничих витратах. Після консультації з управляючим підприємства Булкін зрозумів, що зможе випікати в одну зміну лише 20 тис. пирогів щотижня. Для кожного пирога понад цієї кількості до виробничих витрат додаються виробничі витрати у розмірі 0,80 грн., які пов’язані з введенням другої зміни.

Завдання 11. Модифікувати модель, додаючи нові параметри: граничний об’єм виробництва для однієї зміни та додаткові витрати на кожний пиріг, вироблений у другу зміну. До структури витрат додати нову статтю для понаднормових виплат. Визначити загальні витрати (у грн.) на «понаднормові» за тиждень, місяць та рік.

Булкін вирішив змінити свою модель для зимового сезону з низьким рівнем продажів. На протязі зими тижневого попиту на пироги, який задано в моделі, можна добитися лише з дуже активною рекламною компанією. При менших витратах на рекламу об’єм замовлень буде складати лише малий відсоток від вказаної у моделі кількості. Залежність між витратами на рекламу та об’ємом замовлень складає:

Витрати на рекламу, тис. грн. Об’єм замовлення, %

5 40

7 65

10 78

12 88

Об’єм замовлення 100% означає, що кількість проданих пирогів буде дорівнювати кількості, яка передбачена моделлю. Тижневі витрати на рекламу складають від 5 до 12 тис. грн.

Завдання 12. Додайте до структури витрат статтю витрат на рекламу. Дослідіть чуйність тижневого прибутку до витрат на рекламу. Дайте рекомендації щодо ціни та витратам на рекламу.

Завдання 13. Для автоматизації діяльності компанії спроектуйте базу даних за допомогою СУБД MS Access. При проектуванні слід врахувати, що сировина надходить від декількох постачальників (додаток 2), а готова продукція реалізується торгівельними точками - клієнтами (додаток 3).

Дата добавления: 2015-10-16