ВИКОРИСТАННЯ СЕРЕДОВИЩА МОДЕЛЮВАННЯ MICROSOFT OFFICE EXCEL 2007 ДЛЯ РОЗВ’ЯЗУВАННЯ ЗАДАЧ БУХГАЛТЕРСЬКОГО ОБЛІКУ

ЗМІСТ

Вступ    4

Інформаційні технології використання вбудованих функцій    8

Функції аналізу інвестиційної діяльності    8

Функції для розрахунку амортизації    11

Функції для роботи з цінними паперами    12

Інформаційна технологія підбору параметра    15

Інформаційна технологія сценарного підходу    21

Інформаційна технологія таблиці підстановки    24

Таблиця підстановок для одного параметра.    24

Таблиця підстановок для двох параметрів    25

Висновки    27

Література    27

 

 

План

  1. Вступ.
  2. Інформаційні технології використання вбудованих функцій:
    1. Функції аналізу інвестиційної діяльності
    2. Функції для розрахунку амортизації
    3. Функції для роботи з цінними паперами
  3. Інформаційна технологія підбору параметра.
  4. Інформаційна технологія сценарного підходу.
  5. Інформаційна технологія таблиці підстановки:
    1. З одним параметром
    2. З двома параметрами

6    Висновки.

7    Література.

 

Вступ

Комп’ютерна система бухгалтерського обліку (КСБО) повинна забезпечувати виконання всіх функцій і вимог бухгалтерського обліку. Основою бухгалтерського обліку є облікова політика організації — сукупність правил ведення бухгалтерського обліку, первинного спостереження, вартісного виміру, угрупування і підсумкового узагальнення фактів господарської діяльності. У обліковій політиці організацій затверджуються:

  • Робочий план рахунків бухгалтерського обліку.
  • Форми документів, у тому числі для внутрішньої бухгалтерської звітності.
  • Порядок проведення інвентаризації активів і зобов’язань організації.
  • Методи оцінки активів і зобов’язань.
  • Правила документообігу і технологія обробки облікової інформації.
  • Порядок контролю здійснення господарських операцій і ін.

Моделі обліку

План рахунків бухгалтерського обліку — систематизований перелік синтетичних рахунків бухгалтерського обліку. Стандартний план рахунків бухгалтерського обліку є основою для формування робочого плану рахунків підприємства. Для програмної реалізації алгоритмів бухгалтерського обліку використовуються наступні ознаки рахунків:

  • Тип сальдо для балансових рахунків.
  • Періодичність, алгоритм закриття рахунку (щомісячно, щокварталу, один раз на рік).
  • Валютний облік на рахунку.
  • Структура кода рахунку.
  • Приналежність до групи, підгрупи рахунків або розділу плану і тому подібне.

Комп’ютерні системи бухгалтерського обліку дозволяють використовувати різні робочі плани рахунків, програмним способом встановлюючи між рахунками різних планів відповідність. У бухгалтерські проводки включається ознака «Вигляд плану рахунків», відповідний певним моделям обліку. Бухгалтерський облік забезпечує синтетичний і аналітичний облік.

Синтетичний облік — це узагальнення даних про майно, зобов’язання і господарські операції у вартісному вираженні. Деталізація об’єктів обліку на синтетичних рахівницях здійснюється шляхом відкриття субрахунків до них. Субрахунки забезпечують можливість додаткової класифікації і аналізу господарських операцій. Можна вводити ієрархію субрахунків для одного синтетичного рахунку.

Аналітичний облік в ручному варіанті ведеться в ліцевих, матеріальних, особистих і інших аналітичних рахунках бухгалтерського обліку. Аналітичний облік забезпечує угрупування інформації усередині кожного синтетичного рахунку у вартісному або в натуральному вираженні. Об’єктами аналітичного обліку є:

  • Матеріали, товари, основні засоби, нематеріальні активи, малоцінні і швидкозношувальні предмети, виробничі або зовнішні замовлення.
  • Організації (постачальники, покупці, клієнти, замовники, дебітори, кредитори), з якими ведуться розрахунки.
  • Матеріально-відповідальні і підзвітні особи.
  • Акціонери і засновники.
  • Документи-підстави.
  • Структурні підрозділи (бізнес-одиниці).
  • Статті і елементи витрат, витрати звернення.

У комп’ютерних бухгалтерських системах позначення рахунку — код рахунку визначає види обробки облікової інформації. У код рахунку може бути включена зазначена інформація про рахунок:

  • Код плану рахунків.
  • Розділ плану.
  • Тип сальдо рахунку.
  • Ознака валютного обліку.
  • Класифікаційний код рахунку.
  • Ідентифікатор синтетичного рахунку.
  • Ідентифікатор синтетичного субрахунку 1-го рівня.
  • Ідентифікатор синтетичного субрахунку 2-го рівня і т.п
  • Ідентифікатор аналітичного рахунку 1.
  • Ідентифікатор аналітичного рахунку 2 і так далі.

     

Таким чином, в структурі кода рахунку можна відобразити всю необхідну інформацію. Розрядність кода чимала, як правило, обмежена 255 символами, що цілком достатньо для представлення будь-якого набору зазначеної інформації. Комп’ютерні технології дозволяють використовувати структуру кода для відбору і угрупування інформації. В комп’ютерних системах бухгалтерського обліку аналітичним рахункам відповідають довідники, картотеки, реєстри документів, облікові регістри. Вся облікова інформація про господарські операції у вигляді бухгалтерських проводок представлена в базі даних. Для кожного синтетичного рахунку, субрахунку, аналітичного рахунку вводиться на початок першого облікового періоду початкове сальдо.

Класифікація і кодування інформації

Основу інформаційної системи бухгалтерського обліку складають класифікатори і кодифікатори техніко-економічної інформації. Система класифікації — сукупність правил і результат розподілу заданої безлічі об’єктів (М) на підмножини (Мij) відповідно до ознак схожості або відмінності. Розрізняють два методи класифікації:

  • Ієрархічний метод — між класифікаційними угрупуваннями встановлюються стосунки підпорядкування, послідовній деталізації властивостей типу: клас — підклас — група — підгрупа — вигляд і так далі В ієрархічній класифікації кожен об’єкт потрапляє лише в одне класифікаційне угрупування, об’єднання угрупувань одного ієрархічного рівня дає вихідна безліч об’єктів. Глибина ієрархії визначається класифікаційними ознаками.
  • .Фасетний метод — вихідна безліч об’єктів розбивається на підмножини відповідно до значень окремих фасетів. Фасет — набір значень однієї ознаки класифікації. Фасети взаємно незалежні. Кожен об’єкт може одночасно входити в різні класифікаційні угрупування.

Кодування призначене для привласнення об’єктам або класифікаційним угрупуванням умовних позначень — кода. Характеристикою кодів є:

  • використовуваний алфавіт (цифри, букви, штрихи, кольори);
  • довжина і структура позначень кода;
  • метод кодування: класифікаційний і реєстраційний (ідентифікаційний).

Фінансовий аналіз

В ЕхсеІ існують функції, за допомогою яких можна проаналізувати баланс підприємства, показники прибутковості, заборгованості, ліквідності або ставку дисконту; дослідити такі критерії прийняття рішень, як період окупності інвестицій, оцінка доходу, додаткові податки, диверсифікація (поширення її на нові сфери) продукції та ін.

Умовно всі фінансові функції поділяють на такі групи:

  • функції аналізу інвестиційної діяльності;
  • функції для розрахунку амортизації;
  • функції для роботи з цінними паперами.

     

    Інформаційні технології використання вбудованих функцій

    Функції аналізу інвестиційної діяльності

    Фінансова діяльність будь-якого підприємства потребує періодичного аналізу інвестування власних фінансових ресурсів, зокрема на оновлення наявної матеріально-технічної бази, на розширення обсягу виробництва, а також інвестування коштів на ринку капіталів, у цінні папери тощо. Усі інвестиції поділяють на капітальні та фінансові.

    Капітальні інвестиції — вкладення грошей в основні засоби (будинки, споруди тощо) та в нематеріальні активи (освіту, медицину та ін.).

    Фінансові інвестиції — вкладення грошей у придбання цінних паперів (облігацій та акцій). Головним критерієм оцінювання інвестицій є їх окупність, тобто швидкість повернення інвестору вкладених коштів через грошові потоки, зокрема дивіденди і відсотки на вкладений в акції капітал, прибутки від реалізації продукції та ін.

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

    До функцій аналізу інвестиційної діяльності належать:

  1. Функція ПЛТ.

З її допомогою розраховують величину постійної періодичної виплати позики при постійній відсотковій ставці. Вона має такий вигляд:

ПЛТ (Ставка; Кпер; Пс; [Бс]; [Тип]), де

Ставка — відсоткова ставка за один період;

Кпер — кількість періодів виплат (наприклад, місяці, роки тощо);

Пс — загальна сума, яку необхідно виплатити;

Бс — розмір майбутньої суми або залишку після закінчення виплат. Якщо цього аргументу немає, майбутня вартість позики дорівнює 0;

Тип — аргумент, який визначає час виплати. Він може мати значення 0 (за замовчуванням) і означає, що виплата відбувається в кінці періоду, або 1 — виплата відбувається на початку періоду.

Наприклад, необхідно розрахувати щорічний розмір платежу за позику 15 000 грн. терміном на 5 років та відсотковою ставкою 12%. За цієї умови функція буде: =ПЛТ(12%;5;15 000), а результат розрахунку — 4161,15 грн. (рис. 2.1)

Рис.2.1. Використання функції ПЛТ

В іншому випадку необхідно розрахувати, яку суму щомісячно слід переводити на рахунок банку, щоб при відсотковій ставці 20% річних за 3 роки вклад досягнув суми 5000 грн. Функція матиме вигляд: =ПЛТ(20%/12;3 *12; 5000). Результат розрахунку — 185,82 грн.

Розраховуючи цю функцію, необхідно уважно стежити за відповідністю одиниць виміру періодів: якщо період — місяць, то аргумент «Ставка» має вигляд «Ставка»/12 і аргумент Кпер — «число періодів» *12. Якщо йдеться про річні виплати, то відповідно «Ставка» та «число періодів».

Функція СТАВКА. Дає змогу розрахувати відсоткову ставку за один період, яка потрібна для отримання певної суми протягом заданого терміну і при постійній сумі виплат. Загальний вигляд функції такий; СТАВКА (Кпер; Плт; Пс; Бс; Тип; [Предположение]), де Кпер — кількість періодів виплати (наприклад, місяці, роки тощо); Плт — сума, яку необхідно сплачувати в кожному періоді. Це значення постійне для всього періоду платежів, його вводять зі знаком «-». Якщо цього аргументу немає, обов’язковим є аргумент Бс; Пс — загальна сума, яку необхідно виплатити; Бс — розмір майбутньої суми або залишку після закінчення виплат. У разі відсутності аргументу майбутня вартість позики дорівнює 0; Тип — аргумент, який визначає час виплати. Він може мати значення 0 (за замовчуванням) і означає, що виплату здійснюють у кінці періоду, або 1 — виплату здійснюють на початку періоду; Предположение — величина пропонованої відсоткової ставки. Якщо цей аргумент опущено, значення дорівнює 10%. У випадку, коли функція СТАВКА не має значень, можна відкоригувати значення аргументу Предположение.

Наприклад, необхідно розрахувати відсоткову ставку для трирічної позики розміром 5000 грн. при щомісячному вкладі 185 грн. За цієї умови функція матиме вигляд =Ставка(3*12;185,82;5000), а результат розрахунку 1,6667% для річної відсоткової ставки 20%. Результат збігається зі значенням попереднього прикладу.

Функція ПС. З її допомогою можна повернути поточний обсяг вкладу, тобто суму, яку складають майбутні платежі. Функція є такою:

ПС (Ставка, Кпер; Плт; [Бс]; [Тип]),

де Ставка — відсоткова ставка за період;

Кпер — кількість періодів виплат (наприклад, місяці, роки тощо); Плт — сума, яку необхідно сплачувати в кожному періоді. Це значення є постійним для всього періоду платежів.

Бс — розмір майбутньої суми або залишку після закінчення виплат. Якщо цей аргумент відсутній, майбутня вартість позики дорівнює 0; Тип — аргумент, який визначає час виплати і має значення 0 або 1.

Наприклад, куплено облігацію, номінальна вартість якої становить 1000 грн. Виплата за нею — 150 грн. на рік, відсоткова ставка — 22%, термін дії облігації — 10 років. Якщо розрахувати вартість облігації на третій рік, то функція буде такою =ПС(22%;3;150;1000), а розраховане значення — 857,04 грн.

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

Функція ВСД. Дає змогу повернути відсоткову ставку доходу від інвестицій (внутрішню швидкість обороту), яку розраховують на основі значень майбутніх платежів та майбутніх прибутків (або збитків). Обсяги проведених операцій обов’язково розраховують через однакові проміжки часу (місяць, рік тощо).

Функція БС. З її допомогою повертають майбутнє значення вкладу, яке розраховують на основі значень періодичних постійних платежів і постійної відсоткової ставки.

Функція КПЕР. Вона дає змогу повернути кількість періодів виплат для отриманого вкладу, яку розраховують на основі постійних періодичних виплат і постійної відсоткової ставки.

Функція МВСД. Дає змогу повернути модифіковану відсоткову ставку після реінвестування (лат. ге… — префікс, що означає зворотну або повторну дію) отриманого вкладу, яку розраховують на основі значень майбутніх платежів та майбутніх прибутків (або збитків), значення відсоткової ставки за отриманий вклад і при реінвестуванні.

Функція ПРПЛТ.З її допомогою можна розрахувати суму платежів за відсотками за певний період, які розраховують на основі постійних періодичних виплат та постійної відсоткової ставки.

Функція ПРОЦПЛАТ. Дає змогу розраховувати розмір суми для сплати за певний період виплат.

Функція БЗРАСПИС. З її допомогою повертають майбутнє значення інвестиції після нарахування складних відсотків, при цьому відсоткова ставка має змінні значення.

Функції для розрахунку амортизації

Амортизація (лат. amortisatio — погашення, від mortis — смерть) — процес поступового зменшення вартості устаткування.

До функцій для розрахунку амортизації належать функція АПЛі функція АСЧ.

Функція АПЛ. З її допомогою розраховують величину зменшення вартості устаткування за рахунок амортизації для певного періоду. Вона має такий вигляд:

АПЛ (Нач_стоимость;Ост_стоимость; Время_зксплуатации),

де Нач_cтоимость — початкова вартість устаткування; Ост_стоимость — остаточна вартість устаткування в кінці амортизації; Времязксплуатации — кількість періодів, під час яких вартість амортизується.

Розраховуючи значення цієї функції, використовують метод рівномірного нарахування амортизації устаткування.

Наприклад, необхідно розрахувати річні амортизаційні витрати, якщо початкова вартість устаткування — 50 000 грн., остаточна — 0 грн., термін амортизації — 10 років. Тоді функція АПЛ матиме вигляд

=АПЛ (50000;0;10), а результат розрахунку буде 5000.

Функція АСЧ. Дає змогу повернути величину річної амортизації устаткування для певного періоду. Вона є такою:

АСЧ (Нач_стоимость; Ост_стоимость; Время_зкс-плуатации; Период),де Нач_стоимость — початкова вартість устаткування; Ост_стоимость — остаточна вартість устаткування в кінці амортизації; Время_зксплуатации — кількість періодів, під час яких вартість амортизується; Период — рік, для якого розраховується величина амортизації.

Наприклад, слід розрахувати амортизаційні витрати для третього року експлуатації устаткування, причому початкова вартість устаткування становитиме 50 000 грн., остаточна — 0 грн., термін амортизації — 10 років. За цієї умови функція АСЧ буде =АМГД (50000;0;10;3), результат розрахунку — 7272,73.

Функції для роботи з цінними паперами

Невід’ємним елементом фінансового ринку єринок цінних паперівсукупність економічних відносин між різними суб’єктами цього ринку (банками, біржами та ін.) щодо організації та купівлі-продажу акцій (облігацій, депозитних сертифікатів, казначейських векселів тощо), які приносять фіксований дохід. Зокрема, облігація — один з найпоширеніших видів цінних паперів, який підтверджує зобов’язання виплатити власнику в певний термін номінальну вартість облігації та дохід від неї; має такі показники: номінальну вартість, купонну ставку дохідності, дату випуску і погашення, суму погашення. Номінальна вартість — це сума, яку вказують на бланку облігації. Якщо ціна, що заплачена за облігацію, нижча номіналу, це означає, що облігацію продано зі знижкою або з дисконтом, а якщо вище номіналу — з премією. Залежно від форми виплати доходу облігації поділяють на купонні (з фіксованою або плаваючою ставкою) та дисконтні (без періодичних виплат доходів). Купонна ставка дохідності облігації — відсоткова ставка, за якою власнику облігації виплачують періодичний дохід і яка визначається ставкою купона, вираженою у відсотках до номіналу. Купонні виплати здійснюють 1, 2 або 4 рази за рік.Для розрахунку цих параметрів у Ехсеl використовують функції ДОХОД і ЦЕНА.

Функція ДОХОД. З її допомогою розраховують дохід від цінних паперів, який становлять періодичні відсотки від виплати. Вона має такий вигляд:

ДОХОД(Дата_согл; Дата_вступл_в_силу ;Ставка; Цена;Погашение; Частота; Базис),

де Дата_согл — дата оформлення купівлі облігації;

Дата_вступл_в_силу — термін погашення цінних паперів; Ставка — річна відсоткова ставка для купонів за цінними паперами; Цена — ціна, за якою куплено облігацію; Погашение — ціна, за якою продається облігація; Частота — кількість виплат за купонами протягом року; Базис — спосіб розрахунку дати (за замовчуванням 0).

Наприклад, номінальна ціна акції становить 300 гри., ціна для покупців — 270 грн., термін облігації — З роки; річна відсоткова ставка — 32%, періодичність виплат відсотків — 2 рази на рік. Врахувавши ці дані, отримують функцію:

=ДОХОД(«01.01.1999»; «01.01.2002»; 16%;270; 300; 2; 0)

ізначення — 0,09, або 9% .

Функція ЦЕНА. Вона дає змогу повернути вартість облігації. її записують так:

ЦЕНА (Дата_согл;Дата_вступл_в_силу;Ставка;До-ход;Погашение; Частота; Базис),де Дата_согл — дата оформлення купівлі цінних паперів; Дата_вступл_в_силу — термін погашення цінних паперів; Ставка — річна відсоткова ставка для купонів за цінними паперами; Доход — дохід за цінними паперами (норма дохідності); Погашение — ціна, за якою продається облігація; Частота — кількість виплат за купонами протягом року; Базис — спосіб розрахунку дати (за замовчуванням 0).

Наприклад, необхідно розрахувати ціну облігації, дата купівлі якої 15 грудня 1998 року, термін погашення — 20 січня 2005 року, ставка купона, який виплачують раз на рік, — 4%, норма дохідності — 10, ціна облігації при продажу — 100 грн. Функція матиме розраховане значення 73,54 грн. і відповідно вигляд

=ЦЕНА(« 15.12.98»;« 20.01.05 »;4%; 10%; 100; 1).

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

Інформаційна технологія підбору параметра

Команда меню Сервіс ► Підбір параметра забезпечує обчислення значення аргументу (параметра) для заданого значення функції методом послідовних ітерацій. Граничне число ітерацій і відносна погрішність результату підбору встановлюються на вкладці Обчислення діалогового вікна Параметри, що викликається за допомогою команди Сервіс ► Параметри.

Підбір параметра є основним методом дослідження області допустимих значень для параметрів моделі. Якщо функціонал має декілька параметрів, подібний аналіз виконується послідовно для кожного параметра окремо, значення функції, що при цьому задається, залишається незмінним. Після підбору можна порівняти отримані результати підбору з погляду їх реалістичності. Наприклад, набуте негативного значення числа періодів амортизації або ставку відсотків істотно перевищує гранично допустиму.

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

За допомогою команди меню Сервіс ► Залежності ► Впливаючі Осередки для осередка, що містить формулу, можна встановити склад початкових осередків, які безпосередньо або через інші осередки впливають на кінцевий результат.

Інформаційна технологія підбору параметра

Інформаційна технологія підбору параметра розглядається на прикладі вбудованих функцій за розрахунком кредитів і позик. Типові розрахунки по ставці складних відсотків засновані на наступній формулі:

(3.1)

 

де pmt.- сума періодичного платежу; r – процентна ставка за один обліковий період; tуре – тип виплат: 1 – на початку, 0 – в кінці облікового періоду; h — число даних облікових періодів; рv – «справжня» вартість внеску; fv – «майбутня» вартість внеску.

Величину pmt і r, схема платежів tуре залишаються незмінними протягом терміну дії договору. У розрахунках враховується тривалість періоду – місяць, квартал, півроку, рік. Періоду певної тривалості відповідає процентна ставка. Якщо процентна ставка дається за рік, а обліковий період – частка року, то в розрахунках використовується пропорційна величина річної процентної ставки. У фінансових функціях за розрахунком грошових потоків важливе направлення потоку: «до нас» – із знаком плюс, «від нас» – із знаком мінус.

Використовуються моделі завдань двох видів.

1. Накопичення капіталу, розміщеного на депозиті у вигляді внеску (або відданого у вигляді позики «в зростання»), величина рv негативна, величина pmt може приймати будь-яке значення, у тому числі і 0. В кінці терміну дії договору п повертається позитивна сума fvі рівна абсолютному значенюрv, або що перевершує його;

2. Користування позиковими засобами, величина рv – позитивна, величина pmt – негативна (регулярні виплати), сума fv повинна бути рівна 0 ;(повне погашення позики).

Існує ряд взаємозв’язаних вбудованих функцій категорії Фінансові Microsoft Ехсеl:

□ – БЗ – розрахунок нарощеної суми капиталаfv,якщо незмінні параметри: pmt, r, п, tуре, рv.

□ – ПЗ – розрахунок еквівалента майбутньої вартості серії послідовних платежів в даний час – величина рv, якщо незмінні параметри: pmt, r, п, tуре, fv.

□ – КПЕР – кількість періодів п, за які початкова сума рv. досягне величини fv;, якщо незмінні параметри: pmt, r, tуре.

□ – НОРМА – величина процентної ставки r при якій початкова сума рv досягне величини fv, якщо незмінні параметри: pmt, п, tуре.

□ – ППЛАТ — величина pmt, яка забезпечує збільшення суми рv до величини fv, якщо незмінні параметри: r,п tуре.

Функція ППЛАТ обчислює загальну суму періодичного платежу pmt,, в якій є дві складові, що обчислюються за допомогою вбудованих функцій:

□ – ОСНПЛАТ – сума, складова накопичення (погашення довга) за обліковий період;

□ – ПЛПРОЦ – відсотки (дохід або до сплати) за обліковий період.

При цьому величина pmt залишається незмінною протягом всього терміну п, а для різних облікових періодів міняється співвідношення складових ОСНПЛАТ і ПЛПРОЦ:

ППЛАТ =0СНПЛАТ+ПЛПР0Ц

Приклад 1

Обчислити суму накопичення на депозитному внеску, якщо розміщені 1000грнр. під 18 % річних на термін 3 роки із зобов’язанням додаткових вкладень в сумі 100 грн. в кінці кожного місяця.

Послідовність дій.

1. Відкрити робочу книгу Анализ.хls за допомогою команди меню Файл ► Відкрити.

2. Вставити, якщо необхідно, новий лист за допомогою команди меню Вставка ► Лист.

3. Перейменувати новий лист за допомогою команди меню Формат ► Лист ► Перейменувати, лист – Аналіз.

4. Розмістити параметри для обчислення функцій:(табл.3.1)

 

1 норма

 

2число періодів

 

3 виплати

 

4 НЗ

 

5 тип

 

6 БЗ

 

 

табл. 3.1 Параметри для обчислень функцій БЗ, ПЗ, КПЕР, НОРМА, ППЛАТ

5. Ввести в осередки В1:В5 початкові дані.

6. Обчислити функцію БЗ за формулою: =БЗ(В1/12;В2*12;ВЗ;В4;В5). Результат – 6436,74 грн..

7. Закрити файл із збереженням за допомогою команди меню Файл ► Закрити.

Приклад 2

Визначити, за який час сума на рахунку може зрости до 10 000 грн. при зберіганні процентної ставки, умов і суми щомісячних виплат.

Послідовність дій.

1. Відкрити робочу книгу Анализ.хls за допомогою команди меню Файл ► Відкрити.

2. Встановити курсор в осередок з формулою БЗ (осередок В6) на листі Аналіз.

3. Виконати команду меню Сервіс ► Підбір параметра.

(3.2)

4. Ввести значення функції, для якого здійснюється підбір параметра – 10000, вказати адресу осередку, відповідного параметру, для якого підбираеться значення — $В$2, натиснути кнопку ОК.

В процесі ітераційного підбору на екрані виводиться вікно повідомлення. Для зупинки процесу підбору натискається кнопка Пауза, після чого підбір можна відновити, натиснувши кнопку Крок для отримання наступної операції або кнопку Продовжити – для відновлення роботи в автоматичному режимі.

Якщо підбір можливий, в осередку параметра з’являється значення і виводиться повідомлення Результат підбору параметра про бажане і поточне значення функції. При натисненні кнопки ОК значення параметра записується в осередок. При натисненні кнопки Відміна в осередку відновлюється значення параметра. .Якщо підбір значення аргументу для заданого значення функції неможливий, выводится повідомлення про те, що рішення не знайдене. В даному випадку результат підбору – 4,346 року.

1. Перевірити результат розрахунку за допомогою функції КПЕР.

2. Відновити умови розрахунку.

3. Закрити файл із збереженням за допомогою команди меню Файл ► Закрити.

Приклад 3

Визначити, як слід змінити суму щомісячних виплат, щоб за 3 роки збільшити внесок до 10000 грн. при збереженні процентної ставки і умов платежу;

Послідовність дій.

1. Відкрити робочу книгу Анализ, хls за допомогою команди меню Файл ► Відкрити.

2. Встановити курсор в осередок з формулою БЗ (осередок В6) на листі Аналіз.

3. Виконати команду меню Сервіс ► Підбір параметра.

4. Ввести значення функції, для якого здійснюється підбір параметра – 10000, вказати адресу осередку, відповідного параметру, для якого, підбирається значення – $В$3, натиснути кнопку ОК.

5. В даному випадку результат підбору: -175,37, що відповідає результату функції ППЛАТ.

6. Перевірити результат розрахунку за допомогою функції ППЛАТ.

7. Відновити умови розрахунку.

8. Закрити файл із збереженням за допомогою команди меню Файл ► Закрити.

Приклад 4

Визначити, яка процентна ставка забезпечить за 3 роки зростання внеску до 10 000 грн. при збереженні умов і суми щомісячних платежів.

Послідовність дій.

1. Відкрити робочу книгу Анализ. хls за допомогою команди меню Файл ► Відкрити

2. Встановити курсор в осередок з формулою БЗ (осередок В6) на листі Аналіз.

3. Виконати команду меню Сервіс ► Підбір параметра.

4. Ввести значення функції, для якого здійснюється підбір параметра, 10000, вказати адресу осередку, відповідного параметру, для якого підбирається значення – $В$1, натиснути кнопку ОК

В даному випадку результат підбору 40%. Це відповідає функції НОРМА.

5. Перевірити результат розрахунку за допомогою функції НОРМА.

6. Відновити умови розрахунку.

7. Закрити файл із збереженням за допомогою команди меню Файл ► Закрити.

Приклад 5

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

Послідовність дій.

1. Відкрити робочу книгу Анализ, хls за допомогою команди меню Файл ► Відкрити.

2. Встановити курсор в осередок з формулою БЗ (осередок В6) на листі Аналіз.

3. Виконати команду меню Сервіс ► Підбір параметра.

4. Ввести значення функції, для якого здійснюється підбір параметра, – 10000, вказати адресу осередку, відповідного параметру, для якого підбирається значення – $В$4, натиснути кнопку ОК.

5. В даному випадку результат підбору – 3084,83 грн. Це відповідає функції ПЗ.

6. Перевірити результат розрахунку за допомогою функції ПЗ.

7. Відновити умови розрахунку.

8. Закрити файл із збереженням за допомогою команди меню Файл ► Закрити.

 

 

 

 

Інформаційна технологія сценарного підходу

У електронній таблиці Мicrosoft Ехсе1 можна зберігати набори значень параметрів моделей у вигляді сценаріїв. Сценарій — іменований набір значень вказаних осередків листа робочої книги. Сценарний підхід забезпечує рішення задач типу «ЩО ЯКЩО», не обмежуючи число одночасне змінних параметрів для кожного такого набору. Сценарії використовуються для підстановки значень параметрів в елементи таблиці і обчислення залежних від них формул.

Обмежень на загальне число сценаріїв на одному листі робочої книги немає. Сценарії містять лише осередки одного листа робочої книги, при цьому допускається об’єднання сценаріїв декількох листів і навіть сценаріїв інших робочих книг при підготовці звітів. У звіті може бути виведено максимум 251 сценарій.

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

Команда меню Сервіс ► Сценарії виводить діалогове вікно Диспетчер сценарії.

Вікно містить ряд текстових кнопок:

□ Вивести – підстановка значень параметрів вибраного сценарію;

□ Закрити – закриття діалогового вікна і припинення роботи з сценаріями;

□ Додати – додавання нового сценарію для листа робочої книги;

□ Видалити – видалення вказаного сценарію;

□ Змінити – зміна значень параметрів вибраного сценарію;

□ Об’єднати – об’єднання з сценаріями інших листів або книг.

□ Звіт – виведення звіту за сценаріями.

Для нового сценарію виводиться діалогове вікно Додавання сценарію

у яке слід ввести унікальну назву сценарію, виділити блок змінних осередків.

УВАГА!

Якщо осередки, що входять в сценарій, не суміжні, вони виділяються при натиснутій клавіші СМ, максимальне число осередків в одному сценарії — 32.

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

Після натиснення кнопки ОК з’являється діалогове вікно Значення осередків сценаріїв, в якому і задаються значення осередків, відповідні параметрам моделі. Натиснення кнопки ОК завершує введення сценаріїв, натиснення кнопки Додати забезпечує введення чергового сценарію.

На підставі сценаріїв формуються підсумкові звіти:зведена таблиця і структурний звіт.

Приклад 1

Створити сценарії для варіантних розрахунків функції БЗ. Зберегти варіанти і сформувати по ним звіт.

Послідовність дій.

1. Відкрити робочу книгу Анализ, хls за допомогою команди меню Файл ► Відкрити.

2. Встановити курсор на листі Аналіз.

3. Виконати команду меню Сервіс ► Сценарії.

4. Підготувати сценарії для варіантних розрахунків згідно табл. 4.1.

 

 

 

Параметри

Вар1

Вар2

Вар3

Вар4

Вар5

Вар6

Вар7

Норма

0,18

0,18

0,18

0,20

0,20

0,20

0,20

Кількість періодів

3,0

3,5

4,0

3,0

3,0

3,5

4,0

НЗ

-1000

-1000

-1000

-1000

-1000

-1000

-800

 

табл. 4.1. Варіанти сценаріїв

 

5. Як змінні осередки вибрати осередки параметрів; НЗ, Норма і Число періодів, що відповідає адресам: $В$1:$В$2;$В$4. Встановити прапорець Захист від змін.

6. Послідовно вивести варіанти сценаріїв для ознайомлення.

7. Внести зміни до сценарію з ім’ям Вар 7: нове значення параметра НЗ:-750.

8. Підготувати звіт у формі структурної таблиці. Для виведення звіту вказати адресу осередку, що містить функцію, залежну від змінних параметрів сценаріїв – осередок В6. Звіт виводиться на окремий лист – Структура сценарію.

9. Підготувати зведену таблицю за сценаріями. Вказати адресу осередку, що містить функцію, залежну від змінних параметрів сценарію, – осередок В6. Зведена таблиця виводиться на окремий лист – Зведена таблиця за сценарієм.

10. Закрити файл із збереженням за допомогою команди меню Файл ► Закрити.

 

 

 

Інформаційна технологія таблиці підстановки

Інформаційна технологія таблиці підстановки використовується для вивчення впливу значень параметра. На результат декілька функцій або значень двох параметрів на результат однієї функції. Ця інформаційна технологія забезпечує рішення задач аналізу типу «ЩО ЯКЩО», обмежуючи число одночасно змінних параметрів моделі. Значення параметра (параметрів) і результуючої функції представлені на листі в явному вигляді. Можна просто змінювати значення параметра (параметрів) і автоматично обчислювати значення функції.

Таблиця підстановок для одного параметра.

Параметр одночасно може використовуватися для обчислення різних функцій. На листі робочої книги готується масив значень параметра у вигляді вектор-рядка або вектор-стовбця. Вводяться формули різних функцій, які використовують даний параметр. Для обчислення цих функцій створюється масив формул.

Приклад 1

Потрібно прослідкувати за допомогою таблиці підстановки, як зміну параметра Виплата відбивається на значенні функцій: БЗ, ПЗ, НОРМА.

Послідовність дій.

1. Відкрити робочу книгу Анализ, хls за допомогою команди меню Файл ► Відкрити.

2. Встановити курсор на листі Аналіз в стовбець L.

3. Підготувати значення параметра Виплати у вигляді блоку осередків одного рядка.

4. Ввести формули (БЗ, ПЗ, НОРМА) в стовпець Функції.

Наприклад, в осередки введені формули вигляду:

КЗ:=БЗ(18%/12;3*12;К2;-1000:0)

К4: =НОРМА(3*12;К2;-1000;1000;0)

К5:=ПЗ(18%/12:3*12;К2;10000;0)

ПРИМІТКА!

Число формул не обмежене, в кожній з них є посилання на параметр Виплати – адресу осередку постановки (в даному випадку К2).

5. Виділити блок осередків, починаючи з осередку підстановки, що включає всі рядки з формулами і стовпці із значеннями параметра осередків затінений — осередки К2:В5).

6. Виконати команду меню Дані ► Таблиця підстановки. У діалоговому вікні Таблиця підстановки вказати адресу осередку підстановки – Підставляти значення по стовпцях в $К$2.

7. Натиснути кнопку ОК.

В результаті формується масив формул вигляду {=ТАБЛИЦЯ(К2)}; Осередок К2 – осередок підстановки.

8. Закрити файл із збереженням за допомогою команди меню Файл ► Закрити.

Таблиця підстановок для двох параметрів

На листі робочої книги готуються два масиви значень параметра, один з яких – вектор-рядок, інший – суміжний вектор-стовпець. Вводиться формула функції, яка використовує параметри. Для обчислення цієї функції створюється масив формул.

Приклад 8

Потрібно за допомогою таблиці підстановки проаналізувати вплив параметрів Норма (Параметр 1) і Виплата (Параметр 2) на функцію БЗ.

Послідовність дій.

1. Відкрити робочу книгу Анализ, хls за допомогою команди меню Файл ► Відкрити.

2. Встановити курсор на листі Аналіз.

3. Підготувати значення параметрів Норма і Виплата у вигляді блоків осередків ,осередки М13.М16 і N3:Т3, відповідно.

ПРИМІТКА!

Осередок 1 відповідає осередку підстановки для Параметра 1 (підстановка по рядках), осередок 2 відповідно для Параметра 2 (підстановка по стовпцях). Осередки можуть розташовуватися в довільному місці на тому ж самому або навіть в іншому місці. В даному випадку використовуються осередки М10 і М11 відповідно для Параметра1 і Параметра 2.

4. На перетині рядка і стовпця, що містять значення параметрів, – осередок М12, ввести формулу для обчислення БЗ.

5. Використовувати посилання іа осередку підстановки: =БЗ(М10/12;3*12:М11;-1000:0)

6. Виділити блок осередків, починаючи з осередку з формулою. Блок осередків включає всі рядки і стовпці із значеннями параметрів.

7. Виконати команду меню Дані ► Таблиця підстановки.

З’являється діалогове вікно Таблиця підстановки для вказівки адреси осередку підстановки – Підставляти значення по стовпцях в М11, Підставляти значення по рядках в М10. Натиснути кнопку ОК.

8. В результаті формується масив формул вигляду {=ТАБЛИЦЯ(М11: М10)}.

9. Закрити файл із збереженням за допомогою команди меню Файл ► Закрити.

МicrosoftЕхсеl забезпечує рішення задач лінійного і нелінійного програмування обмеженої розмірності. Для реалізації оптимізаційних розрахунків в Мicrosoft Ехсе1 необхідно встановити надбудову Пошук рішення за допомогою команди меню Сервіс ► Надбудови і правильно підготувати дані оптимізаційній моделі на листі. Модель завдання задається в діалоговому вікні. Пошук рішення. Модель використовує цільову функцію, яка записується в виді формули в окремому осередку. Для цільової функції указується:, максимізація, мінімізація або рівність фіксованому значенню. У процесі пошуку рішення змінюються значення у вказаних осередках, відповідних змінним, при дотриманні обмежень.

Висновки

 

Список використаних джерел

  1. Дибкова Л.М. Інформатика і комп’ютерна техніка: Навч. посібник. Видання 2-ге, перероблене, доповнене. – К.: Академвидав, 2005. – 416с.
  2. Інформатика: Комп’ютерна техніка. Комп’ютерні технології: Підручник для студентів вищих навч. закладів / За ред. О.І. Пушкаря. – К.: Академія, 2003. – 704с.
  3. Ильина О.П. Информационные технологии бухгалтерского учета. – СПб .: Питер, 2001. – 688с.
  4. Гарнаев А.Ю. Использование MS Exel и VBA в економике и финансах.- СПб.: БХВ – Санкт-Петербург, 2000. – 336с.
  5. Клименко О.Ф., Головко Н.Р., Шарапов О.Д. Інформатика та комп’ютерна техніка: Навч.-метод. посібник / За заг. ред. О.Д. Шарапова. – К.: КНЕУ, 2005. – 543с.
ЗАВАНТАЖИТИ

Для скачування файлів необхідно або Зареєструватись

Курсова (101.8 KiB, Завантажень: 1)

завантаження...
WordPress: 23.14MB | MySQL:26 | 0,353sec