Розв’язок прикладу

1. В робочій книзі електронної таблиці створюємо чотири листи назвами “Баланс початок місяця”, “Господарські операції”, “Оборотна відомість”, “Баланс кінець місяця”.

2. Керуючись знаннями з курсу “Теорія бухгалтерського обліку”, в листі “Баланс початок місяця” створюємо відповідний документ:

3. В листі „Господарські операції” формуємо електронний журнал реєстрації операцій з вказанням кореспонденції рахунків:

Користуючись відомою методикою попереднього завдання, назвемо діапазон комірок А5:А30 - Дата, діапазон С5:С30 - Сума, діапазон D5:D30 - Дебет, а діапазон Е5:Е30 - Кредит.

4. Формуємо однойменний документ в листі „Оборотна відомість”.

а) Будуємо заголовок документу. У комірку НІ заносимо місяць розрахунку у форматі 02.2004.

б) Фіксуємо вказівник „миші” в комірці С6. Набираємо на клавіатурі знак „=”, клацаємо „мишкою” на назві листа „Баланс початок місяця” внизу робочого листа, фіксуємо вказівник „миші” в комірці С5 цього листа, натискуємо кнопку клавіатури Enter. В комірку С6 заноситься формула ='Баланс початок місяця'!С5 і переноситься з листа „Баланс початок місяця” в лист „Оборотна відомість” дебетовий залишок по 10-му рахунку. Копіюємо цю формулу з комірки С6 в діапазон С7:С11. Аналогічно, починаючи з комірки D12 переносимо кредитові залишки для пасивних рахунків.

Само собою розуміється, що студент знає, що в активних рахунках сальдо відображається за дебетом, а в пасивних - за кредитом.

в) Формуємо обороти за місяць на основі інформації листа „Господарські операції”. Для обороту по дебету в комірку Е6 заносимо формулу =SUМ(IF(МОNТН(Дата)=МОNТН($H$I);1;0)*IF(Дебет=А6;1;0)*Сума) натискуємо кнопки на клавіатурі < Сtrl+Shift > і, не відпускаючи їх, кнопку <Enter>.

Отримуємо дебетовий оборот по 10- му рахунку. Копіюємо формулу в діапазон Е7:Е16 і отримуємо дебетові обороти по всіх рахунках. Аналогічно в діапазоні F6:F16 формуємо кредитові обороти.

5. І, на кінець, користуючись відомою методикою роботи з листами, формуємо лист „Баланс кінець місяця”, переносячи залишки з пасивних та активних рахунків з відповідних комірок листа „Оборотна відомість”.

Лабораторна робота №7.

Тема: Прогнозування економічних показників в електронній таблиці MS EXCEL з використанням статистичних функцій на основі регресивного аналізу.

Завдання. З допомого статистичних функцій Microsoft Excel, виходячи із даних спостереження, спрогнозувати значення економічного показника.

Теоретичні основи.

Не вдаючись глибоко в математичний апарат, повідомимо основні моменти роботи із статистичними функціями Microsoft Excel, які побудовані на застосуванні регресивного аналізу - виду статистичного аналізу, що дозволяє оцінити міру залежності між змінними, пропонуючи механізм обчислення передбачуваного значення змінної на основі декількох вже відомих значень. Інакше кажучи, на основі статистичної вибірки відомих значень функції F(x) та аргументів х можна споогнозувати поведінку функції шляхом підстановки нових значень аргументів. Використовують наступні статистичні функції.

1. Якщо змінна Y, яку ми досліджуємо, залежить лінійно лише від одного аргументу X, тобто описується рівнянням Y=b+aX, то функція =FORECAST (новий Х; відомі Y; відомі X) (в російській версії ПРЕДСКАЗ) прогнозує одне нове значення функції на основі нового значення X та статистичної вибірки значень X та Y, а функція =TREND (відомі Y; відомі X; нові X) (ТЕНДЕНЦИЯ) прогнозує декілька нових значень функції на основі декількох нових значень X та статистичної вибірки значень X та Y.

2. Якщо змінна Y залежить нелінійно лише від одного аргументу X, тобто описується рівнянням Y=ba^x, то функція =GROWTH (відомі Y; відомі Х; нові X) (РОСТ) прогнозує декілька нових значень функції на основі декількох нових значень X та статистичної вибірки значень X та Y.

3. Якщо змінна Y залежить лінійно від декількох аргументів X₁, Х₂... Х_n, тобто описується рівнянням Y=b+a ₁X₁ +a ₂ X₂ + …+ a _n X_n, то функція =LINEST (відомі Y; відомі X) (ЛИНЕЙН) прогнозує значення коефіцієнтів a _n, a _{n -1}... a ₁, та b, які потрібно підставити в рівняння множинної лінійної регресії разом з новими X₁, X₂ ... X_n та отримати прогнозоване значення Y.

4. Якщо змінна Y залежить нелінійно від декількох аргументів X₁, Х₂... Х_n, тобто описується рівнянням Y=ba^X1а^X2... a _n ^Xn, то функція =LOGEST (відомі Y;відомі X) (ЛГРФПРИБЛ) прогнозує значення коефіцієнтів a _n, a _{n -1}... a ₁, та b, які потрібно підставити в рівняння множинної лінійної регресії разом з новими Х₁, Х₂... Х_n, та отримати прогнозоване значення Y.

Приклад 1. Задана статистична вибірка прибутку підприємства та витрат на рекламу за 8 місяців. Спрогнозувати очікуваний прибуток, якщо підприємство наступні 4 місяці планує затратити такі суми:

Маємо випадок, коли змінна Y (прибуток - діапазон СЗ:С10) залежить від однієї змінної X (затрат на рекламу - діапазон ВЗ:В10). Виділяємо діапазон комірок B3:С10, з допомогою команд Data/Sort... сортуємо дані по зростанню даних першого стовпця (затрат на рекламу), будуємо графік залежності прибутку від злтрат на рекламу „XY(Scatter)” (Точечная). Виходячи із зовнішнього виду отриманого графіка, приймаємо рішення про те, чи залежність близька до лінійної, чи ні. У випадку лінійної залежності для прогнозування прибутку у вересні-грудні занесемо в комірку C12 формулу =TREND(СЗ:C10;ВЗ:В10;В12:В15), виділяємо діапазон С12:С15, фіксуємо вказівник миші в рядку вводу формули, натискуємо кнопки на клавіатурі < Сtrl+Shift > і, не відпускаючи їх, кнопку <Еnter>. Аналогічно, у випадку нелінійної залежності, в діапазоні С12:С15 застосуємо функцію =GROWTH(СЗ:С10;ВЗ:В10;В12:В15).

Приклад 2. Фірма, яка починає свою діяльність на ринку торгівлі нерухомістю, спостерігає за цінами (Y) на квартири в залежності від площі (X1), поверху (Х2), кількості кімнат (ХЗ), строку експлуатації (Х4) та має потребу оцінити свою квартиру, яку готує до продажі (площею 90 м ² на 3-му поверсі, 4-х кімнатну, збудовану 25 років тому). Заносимо дані в електронну таблицю:

Для оцінки вартості використаємо обидві функції: = LINESТ (відомі Y; відомі X) та = LOGESТ (відомі Y; відомі X). Кожна з цих функцій визначає значення коефіцієнтів a_i та b, починаючи з старшого коефіцієнта а_n. Для визначення коефіцієнтів заносимо відповідні заголовки в комірки А15:Е15, заносимо в коміпку А16 функцію = LINESТ (ЕЗ:Е1З;АЗ:D1З) (для лінійної моделі), виділяємо діапазон А16:Е16, фіксуємо вказівник миші в рядку вводу формули, натискуємо кнопки на клавіатурі < Сtrl+Shift > і, не відпускаючи їх, кнопку < Еnter>. Отримуємо для нашого прикладу:

Дальше формуємо область для розміщення вхідної інформації для прогнозування (дивись умову) та результатів прогнозу:

Оскільки використовуємо лінійну модель, то прогнозована вартість Y обчислюється за формулою Y=b+al*Xl+a2*X2+a3*X3+a4*X4, тому в комірку Е20 заносимо формулу =E16+D16*A20+C16*B20+B16*C20+A16*D20.

Отримуємо, як результат число 167249,158, яке і є прогнозованою вартість 4-х кімнатної квартири площею 90 м² на 3-му поверсі 25-ти річного будинку.

При виборі нелінійної моделі аналогічно як в попередньому випадку заносимо в комірки А16:Е16 функцію = LОGEST(E3:E13;A3:D13), прогнозовану вартість обчислюємо за формулою Y=b*al^{^}Х1 *а2^{^}Х2*аЗ^{^}ХЗ*а4^{^}Х4, тому в комірку Е20 заносимо формулу =E16*D16^A20*C16^{^}B20*B16^{^}C20*A16^{^}D20. Отриманий результат і буде прогнозованою вартість нашої квартири при виборі моделі нелінійної множинної регресії.

Відмітимо, що якщо розглядаємо залежність Y лише від одного аргументу (одинична рефесія – приклад 1), то з побудовою графіка візуально бачимо характер залежності і приймаємо рішення про доцільність використання лінійної чи нелінійної моделі. У випадку ж множинної регресії (приклад 2) доцільно застосовувати як лінійну, так і нелінійну модель. Після порівняння результатів можна бачити певні відхилення і для прийняття рішення необхідно здійснити деякі додаткові дослідження.

Рекомендована література.

1. B.C. Пономаренко, О.І. Пушкар, І.В. Журавльова, C.B. Мінухін. Проектування інформаційних систем. Посібник. - Київ, видавничий центр „Академія”,2002.-486с.

2. Ф.Ф. Бутинець, С.В. Івахненков, Т.В. Давидюк, Т.В. Шахрайчук. Інформаційні системи бухгалтерського обліку. Підручник. - Житомир, ПП „Рута”,2002.-642 с.

3. Ситник В.Ф. та ін. Основи інформаційних систем. Навчальний посібник,- Вид.2-ге, перероб. і доп.-К.:КНЕУ, 2001.-420 с.

4. Г.П. Журавель, Є.К.Ковальчук, Н.Я. Микитюк, В.М. Панасюк. Теорія бухгалтерського обліку. Навчальний посібник.- Тернопіль, ’’Економічна думка”, 1999,-154 с.

5. Конрад Карлберг. Бизнес-анализ с помощью Excel 2000,- Издательский дом «Вильямс». Москва. Санкт-Петербург. Киев, 2000.-479 с.

6. Микола Недашковський, Петро Недашковський. Microsoft Office для економістів. - Тернопіль, видавничий дім „Цивілізація”, 1999,- 119 с.

7. С.В. Бабій, В.В. Муравський, І.В. Бондар, O.P. Цебрій, Л.М. Цебрій. Самостійна робота з програмою EXCEL. Тернопіль,2003.-53 с.

8. Ером’як P.C., Дуда М.О., Литвин І.C., Недашковський М.О., Телейко А.Б. Методичний посібник для розв’язку економічних завдань за допомогою програми Microsoft Excel. Тернопіль, 2000.-34 с.

9. Дуда М.О., Дуда О.М., Сирник О.Й. Експериментальний практикум з Microsoft Excel для студентів-економістів: 104 питання & 104 відповіді, зразки виконання 6 економічних Завдань (частина II).- Іернопшь, ІАНГ, 200/..-чи с.

10. Дуда М.О., Ером’як P.C., Дуда О.М. Новий експериментальний Практикум з Microsoft Excel для студент і в- економістів: 99 питань & 99 відповідей, зразки та поради щодо виконання 6 економічних завдань (частина III).- Тернопіль, ТАНЕ, 2002.-40 с.

Поиск по сайту: