42402

ИНФОРМАЦИОННЫЕ СИСТЕМЫ В ЭКОНОМИКЕ

Книга

Информатика, кибернетика и программирование

Напротив каждого сектора появилось значение в рублях или значение в процентах от общей суммы товарооборота соответственно. Постройте круговую диаграмму показывающую удельный вес поступлений в рублях по каждому виду товара в общей сумме по складу.03 на сумму превышающую 2000000 руб. рублей.

Русский

2013-10-29

14.01 MB

120 чел.

ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ

ПЯТИГОРСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНОЛОГИЧЕСКИЙ

УНИВЕРСИТЕТ

ВАРТАНОВА Э.Р.

ИНФОРМАЦИОННЫЕ СИСТЕМЫ В ЭКОНОМИКЕ

Лабораторный практикум

Пятигорск

2006


ББК. 32.973

УДК. 681.3

Вартанова Э.Р. Информационные системы в экономике. Лабораторный практикум. Изд. ПГТУ. Пятигорск, 2006. – 136 с.: ил.  

Лабораторный практикум содержит практический материал по освоению программ Microsoft Excel и Microsoft Access. Учебное пособие адресовано преподавателям и студентам вузов, обучающимся по экономическим специальностям.

Рецензенты:

Кандидат технических наук, доцент Золотухина О.М. (ПГТУ)

Рекомендовано к печати кафедрой Прикладная информатика в экономике ПГТУ.

Протокол №1 от 29.08.2006 г.  

Компьютерная версия:

ассистент кафедры прикладная информатика в экономике Вартанова Э.Р.(ПГТУ)


СОДЕРЖАНИЕ:

 

ВВЕДЕНИЕ 4

РАЗДЕЛ 1. Использование возможностей программы

MICROSOFT EXCEL при решении финансово-экономических задач 6

Тема 1. Построение таблиц и графическое представление

данных в программе Microsoft Excel 6

Тема 2. Работа с базами данных в Microsoft Excel 13

Тема 3. Прогноз с использованием линии тренда 19

Тема 4. Режим Подбора параметров 24

Тема 5. Логические функции Microsoft Excel 32

Тема 6. Финансовые функции Microsoft Excel 42

Тема 7. Решение задач с использованием Таблицы

подстановок 56

Тема 8. Программа оптимизации «Поиск решения» 63

РАЗДЕЛ 2. Работа с базами данных в MICROSOFT ACCESS 75

Тема 1. Освоение функций программы Microsoft Access 75

Тема 2. Создание таблиц в Microsoft Access .77

  •  Создание таблиц в режиме «Мастер таблиц» 78
  •  Создание таблиц в режиме «Конструктор» 84
  •  Создание связей между таблицами 85
  •  Использование свойств поля для настройки

параметров ввода данных в таблицы .91

  •  Создание маски ввода 91
  •  Создание списка для поля таблицы 94

Тема 3. Создание запросов в Microsoft Access 98

РАЗДЕЛ 3. Создание макросов 102

Задания для проверки остаточных знаний
по пройденному материалу 106

Задания для контрольных работ для студентов заочной формы обучения 116

Примерные вопросы к зачету и экзамену 131

Список литературы 134

ВВЕДЕНИЕ

Владение методами современных финансовых вычислений в автоматизированных информационных системах становится одной из основных составляющих в профессиональной подготовке предпринимателя, менеджера, банковского работника, экономиста, бухгалтера.

Широкий ассортимент программных продуктов, представленный на информационном рынке, позволяет удовлетворить потребности практически любой сферы управленческой деятельности. Несмотря на это, многие специалисты отдают предпочтение проверенным программам пакета Microsoft Office, надежным, относительно недорогим, удобным и понятным, и, что очень важно, совместимым практически со всеми программами для финансово-экономической сферы деятельности.

Целью разработки настоящего учебного пособия является  организация лабораторного практикума по дисциплине «Информационные системы в экономике» на базе программ Microsoft Excel, Microsoft Access.

Предложенный курс рассматривает возможности применения пакета прикладных программ Microsoft Office в процессе осуществления управленческой деятельности. Владея основными навыками работы в Microsoft Office, экономисты имеют возможность совершенствовать личный аппарат анализа и прогнозирования ситуаций при принятии решений в процессе управления.

Курс освоения приемов работы в программах пакета Microsoft Office предусматривает подробный разбор примеров задач и команд, начиная от простейших и заканчивая специализированными, предназначенными исключительно для анализа экономической информации.  

Изучение программ Microsoft Excel и Microsoft Access начинается с простейших задач, в процессе решения которых студент знакомится с интерфейсом программ и с легкостью осваивает основные приемы работы в программах. Поэтапно задания приобретают экономическую направленность, это позволяет применять полученные знания для решения более сложных экономических задач, в разных областях управленческой сферы деятельности: бухгалтерской, банковской, налоговой, инвестиционной и др.

В лабораторном практикуме затронута тема создания и использования макросов. Рассмотренные примеры задач дают общее представление о предназначении макросов. Этой информации достаточно для того, чтобы специалист мог упростить свою роботу с программами и в тоже время не создал угрозу в виде вируса при программировании макросов.  

Изучение каждого раздела заканчивается индивидуальным самостоятельным заданием и контрольными вопросами по пройденной теме, что позволяет закрепить изученный материал.

В заключительной части представлены задачи для проверки остаточных знаний по всему пройденному материалу, которые предполагают работу с действующими налоговыми документами, и дают возможность студентам продемонстрировать владение приемами работы в Microsoft Excel и Microsoft Access.

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

Лабораторный практикум рекомендуется преподавателям и студентам вуза всех форм обучения, изучающих курс «Информационные системы в экономике».

РАЗДЕЛ 1. ИСПОЛЬЗОВАНИЕ ВОЗМОЖНОСТЕЙ

ПРОГРАММЫ MICROSOFT EXCEL ПРИ РЕШЕНИИ ФИНАНСОВО-ЭКОНОМИЧЕСКИХ ЗАДАЧ

ТЕМА 1. ПОСТРОЕНИЕ ТАБЛИЦ И ГРАФИЧЕСКОЕ ПРЕДСТАВЛЕНИЕ ДАННЫХ В ПРОГРАММЕ MICROSOFT EXCEL

Цель работы: освоить простейшие приемы работы в приложении Microsoft Excel и способы графического отображения полученных результатов.

Пример

Задание. Проанализируйте финансовую деятельность компании за 3 мес. 2004г. по округам в среде Microsoft Excel. Исходные данные  показаны на рис.1.1.

Рассчитайте значения столбцов Всего, В проц. (удельный вес товарооборота каждого округа в общей сумме товарооборота) и строки Итого.

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

Рис.1.1. исходные данные по финансовой деятельность компании

Решение. Запустите программу Microsoft Excel (ПУСК – ПРОГРАММЫ – EXCEL). На Листе 1 создайте таблицу по образцу, приведенном на рисунке 1.1. Для этого выделите ячейки В2:F2 и нажмите на панели инструментов кнопку - объединение ячеек. Если на панели отсутствует необходимая кнопка, проделайте следующее: в строке МЕНЮ выберите ВИД -  ПАНЕЛИ ИНСТРУМЕНТОВ – НАСТРОЙКА – КОМАНДЫ – категория: ФОРМАТ – в окне КОМАНДЫ найдите необходимую кнопку – нажмите на кнопку левой клавишей мыши и, не отпуская, перетащите на панель инструментов. После этого нажмете на эту кнопку. Произошло объединение выделенных ячеек в одну. Теперь необходимо ввести текст. Далее заполните таблицу в соответствие с приведенным образцом.

По столбцу Всего необходимо рассчитать сумму товарооборота по каждому округу за три месяца. Сделать это можно двумя способами: используя формулу для расчета суммы  или кнопку АВТОСУММИРОВАНИЯ - (активизируйте нужную ячейку, нажмите кнопку АВТОСУММЫ, мышкой выделите диапазон суммирования - ENTER).

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

Тоже самое необходимо проделать по строке Итого. Для того, чтобы проверить правильность расчетов, определите значение ячейки на пересечении строки Итого и столбца Всего, просуммировав сначала столбец, а затем строку, если получите одинаковые значения, значит расчеты выполнены верно.

Для того, чтобы определить значения ячеек столбца В проц. (удельный вес итоговых сумм по округам в общей сумме товарооборота) необходимо значение каждой ячейки столбца Всего разделить на итоговое значение по этому столбцу. Для этого в пустую ячейку по столбцу В проц. введите формулу: «=» ячейка с суммой всего по этой строке «/» ячейку на пересечении сроки Итого и столбца Всего – ENTER (=Е6/Е10). В результате получите значение в коэффициентном виде. Если необходимо получить значения в процентах, установите для всего столбца процентный формат ячейки. Для этого выделите столбец В проц. кроме ячейки заголовка, наведите курсор на выделенный диапазон, выберите в строке МЕНЮ ФОРМАТ - ЯЧЕЙКИ – вкладка ЧИСЛО, в поле ЧИСЛОВЫЕ ФОРМАТЫ выберите ПРОЦЕНТНЫЙ – ОК. Значение изменилось,  появился знак процента.

Теперь необходимо посчитать удельный вес других округов в итоговой сумме. Если «протянуть» значение, как делали это раньше, то получится неверный ответ, так как во введенной формуле использовались относительные ссылки на ячейки.

Для того, чтобы привести формулу к необходимому виду (=Е6/Е$10$), то есть записать с применением абсолютных ссылок, активизируйте ячейку, содержащую значение удельного веса по первому округу, проставьте знак доллара перед буквой и цифрой ячейки с итоговой суммой по округам. При этом, если поставить $ только перед буквой - при автозаполнении в ссылке на ячейку будет изменяться только строка, а столбец останется прежний, обратная ситуация в случае, когда знак $ стоит только перед цифрой. (Быстрый способ проставления знака $ - активизируйте ячейку с формулой, в строке формул установите курсор рядом с именем той ячейки, которую необходимо сделать абсолютной, нажмите на клавиатуре клавишу F4 - ОК). Теперь можно применить режим автозаполнения.

Графическое представление данных

Программа Microsoft Еxcel располагает большим количеством вариантов графического представления данных. Это диаграммы, гистограммы, графики и различные их модификации, предназначенные для более удобного восприятия информации пользователем.

Для того, чтобы построить диаграмму в строке МЕНЮ выберите ВСТАВКА, ДИАГРАММА или нажмите кнопку на панели инструментов.  В появившемся окне МАСТЕР ДИАГРАММ выберите наиболее подходящий вариант графического представления данных, в данном примере - круговую диаграмму (рис 1.2). Определите, какой вид должна иметь диаграмма. Нажмите кнопку ДАЛЕЕ.

Рис. 1.2. Окно мастера диаграмм

Следующий шаг предполагает определение диапазона построения диаграммы. В данной задаче необходимо построить диаграмму по итоговой строке. Для этого выделите ячейки по строке Итого, содержащие итоговые значения товарооборота по каждому месяцу. Затем выделите ячейки «шапки» таблицы, содержащие названия периодов. Для этого нажмите клавишу CTRL и, не отпуская ее, выделите необходимые ячейки. Таким образом, получите диапазон для построения диаграммы в виде двух выделенных строк, находящихся в разных частях таблицы. Нажмите ДАЛЕЕ.

Задайте имя диаграмме Удельный вес товарооборота предприятия за каждый месяц в общей сумме за квартал. Здесь же выберите вкладку Подписи данных. Поставьте галочки напротив Значения или Доли. Напротив каждого сектора появилось значение в рублях или значение в процентах от общей суммы товарооборота соответственно. Нажмите – ДАЛЕЕ, ГОТОВО (рис. 1.1). 

Аналогичным образом строятся гистограммы и графики.

 

Контрольные вопросы.

Как вывести на панель инструментов необходимую кнопку?

Что означает относительная ссылка на ячейку в формуле?

Что означает абсолютная ссылка на ячейку в формуле?

Для чего и каким образом используется функция автозаполнения ячеек?

Как установить необходимый формат ячейки?

Для чего используется кнопка ?

Каким образом выделяют диапазон, состоящий из ячеек находящихся в различных частях листа Excel?  

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО ТЕМЕ 1

Задача 1. Создайте электронную таблицу для расчета заработной платы сотрудников организации (см. рисунок 1.3).

Рис 1.3. Ведомость расчета заработной платы

Выровняйте столбцы по ширине текста.

Премия составляет 50% от начисленной суммы. Подоходный налог составляет 12% от начисленной суммы с премией. В пенсионный фонд удерживается 1% от начисленной суммы с премией.

Вычислите общую и среднюю (с помощью функции) сумму зарплаты по организации.

Постройте диаграмму, показывающую долю суммы заработной платы к выдаче каждого работника в общей сумме заработной платы к выдаче по организации.

Задача 2. Проанализируйте продажи макаронных изделий за 3 мес. с помощью программы Microsoft Excel. Исходные данные приведены в таблице на рис.1.4.

Рассчитайте значения пустых ячеек.

Постройте круговую диаграмму по данным строки Сумма.

Постройте гистограммы, показывающие изменение объема продаж макаронных изделий по каждому месяцу.

Сохраните файл в папке с вашим именем.

Рис. 1.4. Продажи макаронных изделий за первый квартал

Задача 3. Проанализируйте расходы на коммунальные услуги за 3 мес. с помощью программы Microsoft Excel. Исходные данные приведены в таблице на рис. 1.5.

Рассчитайте значения пустых ячеек.

Постройте круговую диаграмму по данным строки Сумма.

Постройте гистограммы по расходам на коммунальные услуги для каждого месяца. Сохраните файл в папке с вашим именем.

Наименование

Январь

февраль

март

апрель

Сумма

В проц. к итоговой сумме

Квартплата

123р.

123р.

138р.

138р.

Телефон

37р.

72р.

29р.

25р.

Электроэнергия

56р.

50р.

47р.

40р.

Транспорт

110р.

90р.

110р.

100р.

Прочие

50р.

150р.

100р.

200р.

Итого

Рис. 1.5. Перечень расходов на коммунальные услуги.

Задача 4. Определите товарооборот за год, рассчитайте сумму налога, которая равна 15% от суммы товарооборота. Определите чистую прибыль предприятия. Постройте диаграмму, отражающую долю каждого филиала в товарообороте всего предприятия. Исходные данные приведены в таблице на рис. 1.6.

Рис. 1.6. Товарооборот предприятия по филиалам за год

Задача 5. Рассчитайте поступление товара на склад за июнь. Постройте круговую диаграмму, показывающую удельный вес поступлений в рублях по каждому виду товара в общей сумме по складу.

Рис. 1.7. Поступления товара на склад №1 за июнь

ТЕМА 2. РАБОТА С БАЗАМИ ДАННЫХ В MICROSOFT EXCEL

Цель работы: освоить основные приемы работы с базами данных в Microsoft Excel: производить сортировку данных, использовать автофильтр и расширенный фильтр при поиске информации в базе данных.

Пример

Задание. Создайте в Microsoft Excel таблицу, как на рисунке 1.8. Рассчитайте стаж работы. Просмотрите данные о продавцах женского пола, работающих на предприятии больше 4-х лет. Отсортируйте данные таким образом, чтобы работники располагались по возрастанию разряда, а те, в свою очередь, по алфавиту. Выведите на экран список сотрудников, проживающих в Пятигорске. Сформируйте список сотрудников – женщин, проживающих в Пятигорске, имеющих 3-ий разряд.

Решение. Переименуйте лист. Для этого дважды щелкните мышью по названию текущего рабочего листа. Название листа выделится. Введите База данных – ОК.

В строке «2» наберите шапку таблицы как на рисунке 1.8.

Рис. 1.8. Сведения о сотрудниках

Установите в столбце Ф.И.О. формат ячейки текстовый (выделите столбец – нажмите левой кнопкой мыши на обозначение столбца  (например D), нажмите ФОРМАТ, выберите «ячейки» - текстовый), установите формат ячейки – текстовый – для столбцов «должность», «адрес», «пол». Для столбца «телефон» таким же образом установите тип «номер телефона» из формата дополнительный. Для столбцов содержащих даты – установите формат «дата», для столбцов №п/п, Разрядчисловой формат, 0 знаков после запятой.

Справа от столбца «стаж работы» вставьте новый столбец. Для этого выделите столбец справа от «стажа работы», наведите на выделенную область курсор, нажмите правую кнопку мыши и в МЕНЮ выберите ДОБАВИТЬ ЯЧЕЙКУ. Появился новый пустой столбец. Введите название «стаж работы – округленный».

Выделите ячейку А1 и введите текст «текущая дата», в ячейку рядом введите текущую дату, задайте формат ячейке «дата».

Рассчитайте стаж работы. Для этого в столбце «стаж работы» введите формулу «(текущая дата - дата найма)/365». Установите ячейку «текущая дата» в формуле - абсолютной (используйте знак $). «Протяните» формулу. В столбце «стаж работы округленный» необходимо округлить полученный результат так, чтобы учитывались только полные годы работы. Для этого выделите ячейку в столбце «стаж работы округленный», вызовите список функций, в Математических найдите формулу ОКРУГЛВНИЗ. Откройте окно формулы, в ячейку число введите ту ячейку, число в которой надо округлить, количество цифр – 0, ОК. Проделайте то же самое для всей таблицы.     

Поиск необходимых сведений в базе данных.

Выделите всю таблицу. Задайте ей имя «Штат_сотрудников». Для этого нажмите ВСТАВКА, ИМЯ, ПРИСВОИТЬ (рис. 1.9). Затем войдите в меню ДАННЫЕ, выберите ФОРМА. Появится окно, отражающее аргументы созданной таблицы. В появившемся окне просмотрите данные о продавцах женского пола работающих на предприятии больше 4-х лет. Для этого нажмите кнопку КРИТЕРИИ и введите в соответствующие ячейки заданные условия (в «стаж работы» - >4). Нажмите ДАЛЕЕ. Просмотрите список полученных результатов отбора (рис. 1.10). Закройте окно.

Рис. 1.9. Присвоение имени таблице, созданной в среде Microsoft Excel

Рис. 1.10. Просмотр данных с помощью приложения  Microsoft Excel ФОРМА

Для того, чтобы расположить информацию в таблице в определенном порядке используйте команду СОРТИРОВКА. Для этого выделите ячейку «Ф.И.О.», выберите в строке меню ДАННЫЕ, СОРТИРОВКА, в появившемся окне установите «Сортировать по» - разряду, «Затем по» Ф.И.О.по возрастанию. Просмотрите отсортированные данные.

При необходимости выделить из таблицы данные, отвечающие определенному условию, воспользуйтесь командой Фильтрация. Для этого активизируйте ячейку «Ф.И.О.». Выберите ДАННЫЕ, ФИЛЬТР, АВТОФИЛЬТР. В ячейках с названиями столбцов появились стрелочки. Нажмите на такую стрелку в столбце Адрес. В появившемся списке выберите УСЛОВИЕ, в окне Пользовательский автофильтр введите Пятигорск (рис. 1.11). На экране появится список работников проживающих в Пятигорске. Повторно нажмите стрелку в столбце Адрес, выберите ВСЕ. Самостоятельно сформируйте список сотрудников принятых на работу после 01.01.1999 года. Скопируйте полученный список сотрудников в нижнюю часть страницы. Первоначальную таблицу верните к исходному виду.

Рис. 1.11. Использование приложения АВТОФИЛЬТР для обработки данных

Если необходимо найти информацию, отвечающую двум и более условиям, используйте команду Расширенный фильтр. Для этого скопируйте шапку таблицы и вставьте ее в нижнюю свободную часть листа. В столбце Адрес запишите условие Пятигорск, в столбце Разряд - 3, в столбце Пол - жен. Затем в меню ДАННЫЕ выберите ФИЛЬТР - РАСШИРЕННЫЙ ФИЛЬТР, в появившемся окне задайте аргументы: Исходный диапазон – диапазон исходной таблицы, Диапазон условий – таблица с условиями, в ОБРАБОТКЕ выберите Скопировать результат в другое место (рис. 1.12), в строке Поместить результат в другое место укажите пустой диапазон ниже таблиц. Нажмите ОК.

Рис. 1.12. Использование приложения РАСШИРЕННЫЙ ФИЛЬТР для обработки данных

Появилась таблица с работниками, отвечающими заданным условиям.    

Контрольные вопросы

Какие способы существуют в программе Microsoft Excel для просмотра и редактирования данных?

В чем различие между приложением Microsoft Excel АВТОФИЛЬТР И РАСШИРЕННЫЙ ФИЛЬТР?

Что необходимо сделать, прежде чем воспользоваться РАСШИРЕННЫМ ФИЛЬТРОМ?

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО ТЕМЕ 2

Задача 1. Создайте таблицу, содержащую следующие сведения о сотрудниках организации (см.: рисунок 1.13).

Отсортируйте записи по: 1). алфавиту фамилий, 2). уменьшению разряда. Выдайте список сотрудников организации:

а). Проживающих в городе Пятигорске,

б). Чей телефон начинается на 34.

в). Проживающих в Пятигорске, старше 25 лет на момент осуществления поиска информации, принятых после 16.04.2000.

Рис. 1.13. Данные о сотрудниках

Задача 2. Предприятие «Альфа» осуществляет оптовую реализацию бытовой техники со складов. Имея сведения о количестве проданной продукции в феврале, определите суммы выручки предприятия за месяц. Создайте таблицу, отражающую реализацию кофеварок и миксеров за месяц. Создайте таблицу, показывающую все поставки предприятия «Бета» ООО «Авангард» с 15.02.03 на сумму превышающую 2000000 руб.

Рис. 1.14 Продажи предприятия «Альфа» за февраль

ТЕМА 3. ПРОГНОЗ С ИСПОЛЬЗОВАНИЕМ ЛИНИИ ТРЕНДА

Цель работы: освоить возможности приложения Microsoft Excel, которые применяются при анализе экономического развития хозяйствующих субъектов.

Программа Microsoft Excel позволяет сделать приблизительный прогноз при наличии данных за определенный промежуток времени с помощью линии тренда2.

Пример.

Задание. Используя данные о доходах бюджета г. Пятигорска за 1999 – 2002 годы рассчитайте темп прироста доходов, общую сумму доходов за каждый год и структуру налоговых доходов по периодам. Спрогнозируйте итоговую сумму налогов на 2004 год (постройте линию тренда). Исходные данные смотрите в таблице на рис. 1.15.

Рис. 1.15. Доходы бюджета г. Пятигорска за 1999 – 2002 годы

Решение. Для того чтобы построить линию тренда необходимо сначала построить график, отражающий динамику итоговой суммы налогов за каждый год. Для этого на панели инструментов нажмите кнопку Мастер диаграмм, в появившемся окне установите курсор в строке Диапазон,  нажмите клавишу CTRL и, удерживая ее, выделите ячейки, содержащие итоговые суммы налогов за каждый год и ячейки, содержащие обозначения годов. Нажмите ДАЛЕЕ, ГОТОВО. Получили график в виде восходящей кривой линии соединяющей четыре точки (рис. 1.16).

Рис. 1.16. Линия тренда

Для того, чтобы построить линию тренда, наведите курсор на кривую и нажмите правую клавишу мыши, в появившемся контекстном меню выберите. Добавить линию тренда. Выберите Линейный тип. Откройте закладку ПАРАМЕТРЫ. Установите Прогноз вперед на 2 периода, поставьте галочку напротив условия Поместить на диаграмму величину достоверности аппроксимации, ОК (рис. 1.17). 

Рис. 1.17. Параметры линии тренда

На графике появилась прямая линия, показывающая, что в 2004 году в бюджет поступит около 990 000 тыс. рублей. Коэффициент достоверности аппроксимации составляет 0,97. Это свидетельствует о том, что вероятность получения спрогнозированной суммы налогов составляет 97% (рис. 1.16).

Контрольные вопросы

Для чего используется линия тренда?

Что показывает коэффициент достоверности аппроксимации?

Что необходимо сделать, прежде чем строить линию тренда?

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО ТЕМЕ 3

Задача 1. Проанализируйте продажи макаронных изделий за 3 мес. с помощью программы Microsoft Excel. Исходные данные приведены в таблице на рис.1.18. Рассчитайте значения пустых ячеек. Определите объем продаж лапши в июне с помощью линии тренда, определите коэффициент достоверности прогноза. 

Продажи: январь - март

Наименование товара

январь

февраль

март

Сумма

Уд. вес

Лапша

25000

34000

66521

Рожки

20000

76548

87654

Ушки

14000

10876

11113

Вермишель

47651

57654

90870

Спагетти

164198

875432

870965

Ракушки

112233

165743

111654

Сумма

 

 

 

 

 

Рис. 1.18. Продажи макаронных изделий за первый квартал

Задача 2. Проанализируйте расходы на коммунальные услуги за 3 мес. с помощью программы Microsoft Excel. Исходные данные приведены в таблице на рис. 1.19.

Рассчитайте значения пустых ячеек. Определите расходы на оплату за пользование телефоном в мае, рассчитайте коэффициент достоверности прогноза.

Рис. 1.19. Перечень расходов на коммунальные услуги

.

Задача 3. Определите товарооборот за год, рассчитайте сумму налога, которая равна 15% от суммы товарооборота. Определите чистую прибыль. Спрогнозируйте товарооборот предприятия за май 2004 года. Исходные данные смотрите в таблице на рис. 1.20.

В тыс. руб.

Товарооборот за 2003 год

Сумма

налога

Филиал

Январь

Февраль

Март

Апрель

Май

Июнь

Июль

Август

Сентябрь

Октябрь

Ноябрь

Декабрь

1

2250

2354

2587

2459

2545

2354

2201

2502

2498

2526

2654

2547

2

125

354

541

541

654

654

320

302

425

489

347

410

3

845

785

859

941

1105

747

754

706

902

899

821

854

6

654

458

687

645

510

741

321

548

654

507

651

700

7

1154

1472

1587

1345

1341

1852

1747

1856

1759

1801

1954

1820

Итого

        Чистая прибыль _________

Рис. 1.20. Товарооборот предприятия по филиалам за год

Задача 4. Рассчитайте темпы прироста (фактический год/предыдущий) и структуру доходов (удельный вес каждого вида налога в общей сумме) для каждого года. Постройте график, показывающий изменения общей суммы налоговых доходов. Постройте линию тренда и определите, сколько поступит средств в бюджет в 2005 году.

Вид дохода

1999 год

темп прироста

структура доходов

2000 год

темп прироста

структура доходов

2001 год

темп прироста

Структура доходов

Налоговые доходы всего

 

 

Налоги на прибыль, доход, в т.ч.:

97559

 

 

143838

 

 

340290

 

 

Налоги на товары, услуги, сборы:

38131

 

 

49425

 

 

33601

 

 

Налоги на совокупный доход

18801

 

 

26807

 

 

33187

 

 

Рис. 1.21. Налоговые поступления в бюджет

ТЕМА 4. РЕЖИМ ПОДБОРА ПАРАМЕТРОВ

Цель работы: освоить методы решения линейных уравнений с одной переменой с использованием режима подбора параметров и применять полученные знания при решении экономических задач.

Примеры.

Задание 1. Используя режим Подбора параметра, определите, при каком значении процента премии общая сумма месячной заработной платы всех сотрудников организации, предназначенная к выдаче, будет равна 250 000 руб.

Исходные данные этого примера приведены на рис. 1.22, где знаком «?» отмечены ячейки с расчетными данными.

Решение. Создайте таблицу расчета заработной платы по образцу (рис. 1.22).

Произведите расчёты во всех столбцах таблицы.

При расчёте столбца "Премия" используйте формулу Премия = Оклад * % Премии, (=$D$5*С6), обратите внимание, что ячейка D5 используется в виде абсолютной адресации и строки и столбца. После набора формулу следует скопировать вниз по столбцу автозаполнением.

Рис. 1.22. Ведомость начисления заработной платы

Формула для расчёта столбца «Всего начислено»: Всего начислено = Оклад + Премия.

Формула для расчёта столбца «Удержания»: Удержания = Всего начислено * % Удержаний, в ячейке F6 наберите =$F$5*Е6. Далее эта формула копируется вниз по столбцу F автозаполнением.

Формула для расчёта столбца «К выдаче»: К выдаче = Всего начислено — Удержания.

          По столбцу К выдаче рассчитайте среднее значение, найдите максимальное значение и минимальное значение. Для этого воспользуйтесь функциями «СРЗНАЧ», «МАКС», «МИН» соответственно. Откройте окно Мастер функций, выберите категорию – СТАТИСТИЧЕСКИЕ, функцию СРЗНАЧ. В строку 1 введите диапазон - столбец К выдаче (без итоговой суммы). Аналогично работают функции «МАКС», «МИН».

Результаты работы и итоговый вид таблицы для расчёта заработной платы представлены на рис. 1.23.

Из расчетов видно, что общая сумма к выдаче при указанных окладах и премии в размере 27 % составляет 104 799,77 руб. Теперь осуществите подбор параметра командой, для чего установите курсор в ячейке общей суммы К выдаче и воспользуйтесь меню СЕРВИС - командой Подбор параметра. Откроется окно, как на рис. 1.24.

Рис.1.23. Ведомость начисления заработной платы

                

Рис. 1.24.  Подбор параметра      Рис. 1.25. Подбор параметра

В диалоговом окне «Подбор параметра» на первой строке Установить в ячейке в качестве подбираемого параметра должен находиться адрес той ячейки, в которой необходимо получить заданное значение. В данном случае это общая итоговая сумма зарплаты к выдаче (ячейка G20, на которой установлен курсор). На второй строке у поля «Значение» диалогового окна «Подбор параметра» наберите желаемое значение параметра, в данном примере это число равно 250 000, на третьей строке в поле «Изменяя значение ячейки» укажите адрес подбираемого значения «% Премии» (ячейка D5), после чего нажмите кнопку ОК (рис. 1.25).

Произойдёт почти моментальный пересчёт всей таблицы, и откроется окно «Результат подбора параметра» (рис. 1.26), в котором дайте подтверждение подобранному параметру нажатием ОК.

Итак, произошёл обратный пересчёт «% Премии». Результаты подбора выглядят так: если сумма к выдаче равна 250 000 руб., то процент премии должен быть 203%.

Рис 1.26. Таблица расчета заработной платы

Задание 2. Используя режим подбора параметра, определите штатное расписание фирмы. Исходные данные приведены на рисунке 1.27.

Известно, что в штате фирмы состоят:

6 курьеров;

8 младших менеджеров:

10 менеджеров;

3 заведующих отделами:

1 главный бухгалтер;

1 программист;

1 системный аналитик:

1 генеральный директор фирмы.

Общий месячный фонд зарплаты составляет 100 000 руб. Необходимо узнать, какими должны быть оклады сотрудников фирмы.

Решение. Каждый оклад является линейной функцией от оклада курьера, а именно: зарплата равна А * Z + В,

где Z оклад курьера;

А и В — коэффициенты, показывающие соответственно, во сколько раз превышается значение Z и на сколько превышается значение Z.

Создайте таблицу штатного расписания фирмы по приведённому на рис. 1.27 образцу. Введите исходные данные в рабочий лист электронной книги.

Рис. 1.27. Штатное расписание фирмы (таблица исходных данных)

В столбце Коэф. В указаны надбавки к окладам.

Выделите отдельную ячейку D17 для зарплаты курьера (переменная Z) и с учётом этого задайте все расчёты. В ячейку D17 временно введите произвольное число, например 500.

В столбце Зарплата сотрудника введите формулу для расчёта заработной платы по каждой должности. Далее скопируйте формулу из ячейки E4 вниз по столбцу автозаполнением.

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

Произведите подбор зарплат сотрудников фирмы для суммарной заработной платы в размере 100 000 руб. Для этого в строке МЕНЮ выберите СЕРВИС - ПОДБОР ПАРАМЕТРА.

В поле Установить в ячейке появившегося окна введите ссылку на ячейку G12, содержащую формулу расчёта фонда заработной платы; в поле Значение наберите искомый результат — 100000; в поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D17, в которой находится значение зарплаты курьера, и щелкните на кнопке ОК. Произойдёт обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100 000 руб. (рис. 1.28).

Рис. 1.28. Штатное расписание фирмы

Итог обработанного пересчета таков: при фонде зарплаты, равном 100 000 руб., зарплата курьера должна составлять 1289,66 руб.

Следует заметить, что если возникает необходимость копировать результаты, полученные подбором параметра, то следует производить копирование полученных данных в виде значений с использованием СПЕЦИАЛЬНОЙ ВСТАВКИ.

Для этого необходимо выделить копируемые данные, произвести запись в буфер памяти (ПРАВКА > КОПИРОВАТЬ), установить курсор в соответствующую ячейку, задать режим специальной вставки (ПРАВКА > СПЕЦИАЛЬНАЯ ВСТАВКА), отметив в качестве объекта вставки "значения" (ПРАВКА — СПЕЦИАЛЬНАЯ ВСТАВКА — ЗНАЧЕНИЯ) (рис. 1.29).

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

Рис. 1.29. Специальная вставка

Контрольные вопросы

Для решения каких задач используется режим ПОДБОРА ПАРАМЕТРОВ?

Адрес какой ячейки должен находиться в диалоговом окне «Подбор параметра» в первой строке?

В каких случаях следует производить копирование данных с использованием специальной вставки?

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО ТЕМЕ 4

Задача 1. Имея исходные данные, рассчитайте сумму премии и сумму к выдаче. При каком проценте премии общая сумма к выдаче будет равна 100000 руб. Рассчитайте средний доход за месяц.

Рис. 1.30. Ведомость заработной платы

Задача 2. Рассчитайте сумму оклада (МРОТ*коэф*разряд), сумму премии (50% от оклада), удержаний в пенсионный фонд (28% от оклада + премия) и сумму к выдаче. Определите, каким должен быть коэффициент, чтобы итоговая сумма к выдаче составила 200000 руб. Исходные данные приведены в таблице на рис. 1.31.

Рис. 1.31. Штат сотрудников фирмы

ТЕМА 5. ЛОГИЧЕСКИЕ ФУНКЦИИ MICROSOFT EXCEL

Цель работы: освоить методы построения и заполнения бухгалтерских документов в программе Microsoft Excel с использованием функций ЕСЛИ и СУММЕСЛИ.

  •  Функция ЕСЛИ

Пример.

Задание. Предположим, что в первый отчетный период деятельности предприятия учредители внесли деньги в уставный капитал в размере 50 %. Для обеспечения деятельности предприятия были приобретены канцтовары и материалы, оплачена аренда помещения. Для получения дохода был заключен договор с заказчиком на выполнение работ (оказание услуг) и получен аванс. Необходимо отразить все перечисленное в журнале операций.

Решение. В Microsoft Excel создайте журнал операций, который будет выглядеть следующим образом (рис. 1.32):

Рис. 1.32. Журнал хозяйственных операций

Далее рассчитайте суммарные обороты по счетам. Для этого необходимо автоматизировать занесение сумм из столбца С в ячейки по дебету или по кредиту каждого счета, в случае, если этот счет используется в проводке (в столбцах D и Е). Для этого примените формулу, в основе которой лежит логическая функция ЕСЛИ(). Функции можно заносить вручную или с помощью Мастера функций. Делается это следующим образом.   

Выберете ячейку в первой строке дебета 51 счета — это ячейка FЗ — и вызовите окно Мастер функций - в категории Логические выберете функцию ЕСЛИ.

В строке Логическое_выражение необходимо записать выражение, выполнение или не выполнение которого приведет к получению определенного результата. Выражение представляет собой сравнение содержащее (обязательно) один из знаков равенства - неравенства – «=», «>=», «<=», «>», «<».

Для того, чтобы разнести суммы по счету 51, по каждой операции необходимо с помощью функции ЕСЛИ записать формулу, которая, в случае использования в операции счета «Касса», автоматически будет заносить сумму этой операции в соответствующую строку по дебету или кредиту счета 51. Формула для дебета 51 счета (ячейки FЗ) будет иметь вид:  

=ЕСЛИ(DЗ=F1;CЗ;"") - ЕСЛИ «номер счета в ячейке DЗ равен номеру счета столбца F» истинно, функция возвращает значение суммы из ячейки CЗ. В противном случае функция возвращает *пустое значение, так как в поле Значение_если_ложь введены две кавычки, означающее пустую текстовую строку.

Такую же операцию выполните со всеми ячейками журнала операций. Конечно, вручную для каждой операции это выполнять не имеет смысла, поэтому воспользуйтесь функцией автозаполнения. При этом следует помнить об относительных и абсолютных ссылках на ячейку. В данном случае необходимо воспользоваться абсолютной ссылкой на ячейку F1(рис 1.33).

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

=ЕСЛИ($DЗ=F$1;$CЗ;"").

Аналогично выглядит формула для кредита в соседней ячейке:

=ЕСЛИ($E3=F$1;$CЗ;"").

Рис. 1.33. Функция ЕСЛИ

Теперь для заполнения остальной части таблицы скопируйте только что созданные формулы.

Далее рассчитайте суммы в столбцах Дебет и Кредит каждого счета. Для этого в результирующую строку каждого столбца поместите суммирующую формулу.

  •  Функция СУММЕСЛИ

Пример.

Задание 1. Используя данные из таблицы на рис. 1.32 рассчитайте сумму оборотов по каждому счету по дебету и по кредиту.

Для этого занесите в столбец С, ниже первоначального журнала операций, изображенного на рис. 1.32, перечень всех используемых в примере счетов (рис. 1.34).

Рис 1.34. Функция СУММЕСЛИ

Далее воспользуйтесь функцией СУММЕСЛИ*.

В первую строку этого перечня справа от значения счета (то есть в столбец D) занесите формулу:

=СУММЕСЛИ(D$3:D$9;$C14;$C$3:$C$9)

Такая запись означает, что необходимо   просуммировать значения тех операций, где дебетуется счет 51, и таким образом в ячейке D14 получится оборот по дебету счета 51.

Рис. 1.35. Учетный регистр

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

Скопируйте формулу автозаполнением для всех счетов по дебету и по кредиту.

Проверьте правильность расчетов, просуммировав вручную оборот по дебету 60 счета (рис. 1.35).

Задание 2. Предприятие занимается мелкооптовыми поставками бытовой техники. На рис. 1.36 приведена электронная таблица, содержащая базу данных по заключенным договорам и конкретной номенклатуре товаров. Необходимо определить на какую сумму каждый покупатель требует товара и сколько составляет сумма продаж товаров по каждому из видов.

Решение. Для решения создайте два новых листа. Один назовите Покупатели, другой - Товары. Лист с исходными данными назовите Продажи.

На лист Покупатели (рис. 1.37) поместите в левый столбец наименования покупателей, скопировав их с листа Продажи (рис. 1.36), а в столбец «Сумма», справа от него, функцию СУММЕСЛИ. В данном случае она будет выглядеть так:

=СУММЕСЛИ(продажи!A2:A17;A2;продажи!F2:F17)

Формула приведена для ячейки В2. Формулы в последующих ячейках будут отличаться только номером строки критерия.

Рис. 1.36. Лист «Продажи»

Рис. 1.37. Лист «Покупатели»

Лист Товары организован аналогично, но по товарам. Кроме их общей стоимости интересует их количество, поэтому в эту таблицу добавлено поле Количество (рис. 1.38.)

Рис. 1.38. Лист товары

Формулы в столбцах В и С выглядят аналогично предыдущему случаю.

В ячейке В2:

=СУММЕСЛИ(продажи!C2:C17;A2;продажи!E2:E17),

В ячейке С2:

=СУММЕСЛИ(продажи!C2:C17;A2;продажи!F2:F17).

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

Контрольные вопросы

Какие аргументы содержит функция ЕСЛИ?

Какими двумя способами можно записывать функцию ЕСЛИ в ячейку?

Что означает термин «возвращает» применительно к функциям рабочего листа электронных таблиц?

В каких случаях используется функция ЕСЛИ?

В каких случаях используется функция СУММЕСЛИ?

В чем отличие между возможностями функции ЕСЛИ и СУММЕСЛИ?

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО ТЕМЕ 5

Задача 1. Определите товарооборот за год, рассчитайте сумму налога учитывая, что, если сумма товарооборота не превышает или равна 15 000 000 руб. налоговая процентная ставка равна 10%, иначе - 24%. Рассчитайте сумму налогов и определите чистую прибыль.

    Чистая прибыль предприятия _________

Рис. 1.39. Товарооборот предприятия

Задача 2. Рассчитайте сумму оклада (оклад = номер разряда * МРОТ * коэффициент), рассчитайте сумму премии (50% от оклада),  сумму удержанного налога на доходы физических лиц (ПН) равного:

12%, если сумма оклада и премии равна или меньше 20-кратного размера МРОТ и

15%, если сумма оклада и премии больше 20-кратного размера МРОТ, но меньше или равна30-кратному размеру МРОТ,

если больше – 20%.

МРОТ

300

 

Коэффициент        

5

Nп/п

Ф.И.О.

Должность

Разряд

Оклад

Премия

Удержано ПН

К выдаче

1

Аванесян

менеджер

5

2

Алахвердова

экономист

5

3

Бабаян

продавец

3

Рис. 1.40. Ведомость начисления заработной платы

Задача 3. Рассчитайте сумму премии:

50% от оклада, если сумма оклада не превышает 400 руб.,

40% от оклада – если равен или более 4000 руб.,

сумму удержанного налога на доходы физических лиц (ПН) равного 20% от премии и оклада, сумму к выдаче. Постройте круговую диаграмму, показывающую удельный вес з/п к выдаче по каждому работнику в общей сумме зарплаты.

МРОТ

300

Nп/п

Ф.И.О.

Должность

Разряд

Оклад

Премия

Удержано ПН

К выдаче

1

Кудилина

зам. Директора

7

6560

2

Легейда

продавец

3

3000

3

Малютина

бухгалтер

5

4000

Рис. 1.41. Ведомость начисления заработной платы

Задача 4. Рассчитайте сумму оклада (оклад = номер разряда * МРОТ * коэффициент), рассчитайте сумму премии (50% от оклада),  сумму удержанного налога на доходы физических лиц (ПН) равного:

20%, если сумма оклада и премии равна или меньше 20-кратного размера МРОТ,

25%, если сумма оклада и премии больше 20-кратного размера МРОТ.

МРОТ

300

Nп/п

Ф.И.О.

Должность

Разряд

Оклад

Премия

Удержано ПН

К выдаче

1

Коньков

Директор

6

2

Легина

продавец

3

3

Малютина

бухгалтер

5

Рис. 1.42. Ведомость начисления зар. платы

Задача 5. Фирма «Дельта М» занимается поставками бытовой техники. В марте  со склада по договорам был отпущен товар основным покупателям в количестве 15200 единиц. Создайте таблицу, отражающую общие суммы продаж магазина «Дельта +» в рублях за март по каждому из покупателей.  

Исходные данные смотрите в таблице на рис. 1.43.

Расчетная таблица представлена на рис 1.44.

Рис. 1.43. Продажи фирмы за март

Рис 1.44. Таблица для расчета сумм продаж за март по каждому покупателю

Задача 6. Используя данные таблицы на рисунке 1.43, определите, сколько всего единиц миксеров, СВЧ печей, кофеварок, чайников, аэрогрилей было продано в марте.

Задача 7. Фирма занимается реализацией бытовой техники. В феврале были заключены сделки и отпущена продукция четырем основным покупателям. Фирма имеет несколько складских помещений, с которых отпускается товар. Покупатели пользуются тем складом, который расположен ближе остальных. Так «Авангард» и «Дом» получают товар со склада №2, «Уют» - №3, «Крокодил» - №4.

Распределите с какого склада какому из покупателей был отпущен товар в феврале (функция ЕСЛИ). Рассчитайте полученные суммы денег по каждой сделке, предварительно записав цену каждого вида отпускаемого товара (функция ЕСЛИ).   

Рис 1.45. Продажи фирмы за февраль

ТЕМА 6. ФИНАНСОВЫЕ ФУНКЦИИ MICROSOFT EXCEL

Цель работы: научиться решать финансовые задачи, используя возможности программы Microsoft Excel.

Microsoft Excel содержит богатый набор финансовых функций: функции для расчета операций по кредитам и займам, для определения скорости оборота инвестиций, для расчета амортизации, для расчетовпоказателей эффективности  ценных бумаг.

Многие финансовые функции Microsoft Excel имеют одинаковые аргументы. В таблице 1 приведены аргументы, используемые в функциях.

Таблица 1

Аргументы финансовых функциц

Аргумент

Описание аргумента

бз бс

Будущая стоимость фиксированных периодических выплат или единой суммы

Нз, пз

начальное значение (текущая стоимость)

выплата

фиксированная периодическая выплата

Кпер,

Число периодов

общее число периодов выплат

Норма

норма дисконтирования

ставка

процентная ставка за период

Тип

Число 0 или 1, обозначающее, когда производится выплата (1 - в начале периода. 0-в конце периода), по умолчанию равно 0.

предположение

предполагаемое значение процентной ставки, по умолчанию равно 0.1

  •  Функция БЗ. Определение будущей стоимости на основе постоянной процентной ставки.

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

Синтаксис     БЗ (норма, число_периодов, выплата, нз, тип).

  1.  Будущую стоимость единой суммы вклада, по которой начисляются сложные проценты за определенное число периодов, можно рассчитать по формуле:

                                        fv=pv*(1+r)n                                              

где  fv - будущая стоимость вклада или займа,

pv - текущая стоимость вклада или займа;

п - общее число периодов начисления процентов,

r - процентная ставка по вкладу или займу.

Для вычисления будущего значения единой суммы функция БЗ используется в виде:

=БЗ(норма, число_периодов, , нз).

В качестве разделителя аргументов используется выбранный при настройке Windows разделитель, обычно это запятая (,) или точка с запятой (;).

  1.  Периодические платежи могут осуществляться в начале каждого расчетного периода (обязательные платежи или платежи пренумерандо) или в конце (обычные платежи постнумерандо) в течение n периодов. Отличие в расчете будущей совокупной стоимости таких вложений для обязательных и обычных платежей заключается в том, что во втором случае не происходит начисления процентов на последний вклад.

Для расчета будущей стоимости серии фиксированных периодических платежей пренумерандо используется формула:

fv = ртt * (1+r) + pmt * (1+r)2 + ... + pmt(1+r)n

= pmt * ((1+r)n –1)/r* (1+r)               

где fv - будущая стоимость фиксированных периодических платежей,

pmt - фиксированная периодическая сумма платежа,

n  - общее число периодов выплат;

r - постоянная процентная ставка .

Функция БЗ в данном случае используется в виде

=БЗ(норма, число_периодов, выплата, , 1).

Для расчета будущей стоимости серии фиксированных периодических платежей постнумерандо используется формула;

fv = ртt + ртt*(1+r) + ... + pmt (1+r)n-1 = pmt*((1+r)n –1)/r

Функция БЗ в данном случае используется в виде:

=БЗ(норма, число_периодов, выплата, , 0)

или

=БЗ(норма, число_периодов, выплата).

Примеры.

Задание 1. Рассчитать, какая сумма окажется на счете, если 27 тыс. руб. положены на 33 года под 13,5% годовых. Проценты начисляются каждые полгода.

Решение. Произведите расчеты двумя способами: используя финансовую формулу и с помощью финансовой функции БЗ. Для того чтобы осуществить расчет по формуле в ячейку А1 введите следующее выражение: = 27* (1+0,135/2)^(33*2), в результате получите 2012.07 тыс. руб. Для того чтобы решить задачу используя функцию БЗ, наведите курсор на ячейку А2, на панели инструментов нажмите кнопку , откроется окно Мастер функций (рис. 1.46).

Рис 1.46. Окно мастера функций

Выберите категорию Финансовые, далее в поле Выберите функцию – функцию БЗ или БС. В открывшемся окне введите в соответствующие строки имеющиеся данные следующим образом:

Рис. 1.47. Функции БС

По условию задачи сказано, что проценты начисляются каждые полгода, поэтому необходимо значение годового процента разделить на два, в случае с периодом вклада все наоборот, количество лет надо умножить на два (рис. 1.47). Сумма первоначального вклада записывается со знаком минус, так как эта сумма является исходящим денежным потоком. В результате получили ответ = 2012,07 тыс. руб. Таким образом решение задачи двумя способами дало результат равный 2012,07 тыс.руб.

Задание 2. Есть два варианта инвестирования средств в течение 4 лет: в начале каждого года под 26% годовых или в конце каждого года под 38% годовых. Ежегодно вносится 300 тыс. руб. Определить, сколько денег окажется на счете в конце 4-го года для каждого варианта.

Решение. Расчет ведется по формуле

fv = 300 *((1+0.26)^4-1)/0.26* (1+0,26) =2210.53 - для первого варианта и по формуле

fv = 300 *((1+0.38)^4-1)/0.38 =2073.74  - для второго

Используя функцию БЗ, получите:

Б3(26%, 4, -300, , 1) = 2210,53 тыс. руб. - для первого варианта,

Б3(38%,4,-300)=2073,74тыс.руб. - для второго варианта.

Расчеты показали, что первый вариант предпочтительнее.

  •  Определение текущей стоимости. Функция ПЗ.

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

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

Синтаксис       П3( норма, кпер, выплата, бс, тип).

Рассмотрим различные варианты использования этой функции при решении конкретных задач.

  1.  Допустим, известно будущее значение вклада (займа). Требуется определить текущее значение этого вклада, т.е. сумму, которую необходимо положить на счет сегодня, чтобы в конце n-го периода она достигла заданного значения. Это значение можно получить из формулы:

                                                    pv=fv/(1+r)n                                            

Такой же расчет при использовании функции ПЗ в общем виде запишется так

= П3(норма, кпер, , бс).

  1.  Расчет текущей стоимости серии будущих постоянных периодических платежей, производимых в начале каждого периода и дисконтированных нормой дохода r, ведется по формуле:

pv = ртt + ртt/(1+r) +... + pmt/(1+r)n-1 =pmt(1-1/(1+r)n)/r*(1+r)

где pv - текущая стоимость серии фиксированных периодических платежей,

pmt - фиксированная периодическая сумма платежа,

п   - общее число периодов выплат (поступлений),

r   - постоянная процентная ставка.

Для расчета этой величины функция ПЗ используется в виде:

=ПЗ(норма, кпер, выплата, , 1).

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

pv = ртt/(1+r) + ртt/(1+r)2 +...+ pmt/(1+r)n =pmt(1-1/(1+r)n)/r

Соответствующая этому расчету формула в EXCEL имеет вид:

=ПЗ(Hоpмa, кпер, выплата).

По умолчанию аргумент тип равен 0, поэтому его можно не указывать.

Примеры.  

Задание 1. Фирме потребуется 5000 руб. через 12 лет, В настоящее время фирма располагает деньгами и готова положить их на депозит единым вкладом, чтобы через 12 лет он достиг 5000 руб. Определить необходимую сумму текущего вклада, если ставка процента по нему составляет 12% в год.

Решение. Для расчета используем формулу (2.1) или следующую функцию:

П3( 12%, 12,,5000)= -1283,38руб.

Результат получился отрицательный, так как это сумма, которую необходимо вложить.

Задание 2. Рассматривается два варианта покупки дома: заплатить сразу 99000 руб. или в рассрочку - по 940 руб. ежемесячно в течение 15 лет. Определить, какой вариант предпочтительнее, если ставка процента - 8% годовых.

Решение. В задаче необходимо сравнить, что выгоднее: заплатить сегодня указанную сумму или растянуть платежи на определенный срок. Для сравнения следует привести эти денежные потоки к одному периоду времени, т.е. рассчитать текущую стоимость будущих фиксированных периодических выплат. Допустим, что выплаты происходят в конце каждого расчетного периода, Расчет можно вести по формуле (2.3) или используя функцию ПЗ:

П3(8%/12, 15*12, -940) = 98362,16 руб.

Запрашиваемая цена 99000 руб. больше рассчитанной текущей стоимости периодических выплат, следовательно, невыгодно покупать дом сразу, лучше растянуть платежи на 15 лет.

  •   Определение текущей стоимости. Функция НПЗ.

Функция НПЗ вычисляет чистую текущую стоимость (NPV) периодических платежей переменной величины как сумму ожидаемых расходов и доходов, дисконтированных нормой процента r. 

Метод определения чистой текущей стоимости часто применяется при оценке эффективности инвестиций. Он позволяет определить нижнюю границу прибыльности и использовать ее в качестве критерия при выборе наиболее эффективного проекта. Дисконтирование ожидаемых доходов и расходов позволяет учесть издержки привлечения капитала. Положительное значение NPV является показателем того, что проект приносит чистую прибыль своим инвесторам после покрытия всех связанных с ним расходов.

Синтаксис      НПЗ(норма, сумма1, сумма2,..., cyммaN).

Считается, что инвестиция, чистую текущую стоимость которой вычисляет функция НПЗ, начинается за один период до даты аргумента сумма1 и заканчивается с последним значением в списке. Если первый денежный взнос приходится на начало первого периода, то первое значение следует добавить (вычесть, если это затраты) к результату функции НПЗ, но не включать в список аргументов

Примеры

Задание 1. Инвестиции в проект к концу первого года его реализации составят 10000 руб. В последующие три года ожидаются годовые доходы по проекту 3000 руб., 4200 руб., 6800 руб. Издержки привлечения капитала 10%. Рассчитать чистую текущую стоимость проекта.

Решение. Так как инвестиция размером 10000 руб. относится не к начальному моменту, на который производится расчет, то это значение следует включить в список аргументов. Поскольку этот денежный поток движется "от нас", то сумма 10000 руб. записывается со знаком “-“. Остальные денежные потоки представляют доходы, поэтому имеют знак "+". Чистый текущий объем инвестиции составит:

НПЗ(10%, -10000, 3000, 4200, 6800) = 1188,44 руб

Вычисленное значение представляет абсолютную прибыль от вложения 10000 руб. через год с учетом издержек привлечения капитала.

Задача 2. Допустим, затраты на проект в начальный момент его реализации составляют 37000 руб., а ожидаемые доходы за первые пять лет: 8000 руб , 9200 руб., 10000 руб., 13900 руб. и 14500 руб. На шестой год ожидается убыток в 5000 руб. Цена капитала 8% годовых. Рассчитать чистую текущую стоимость проекта.

Решение. В этой задаче нет необходимости дисконтировать начальные затраты по проекту, так как они относятся к настоящему моменту, и их текущая стоимость равна 37000 руб. Для сравнения затрат с будущими доходами и убытками последние необходимо привести к начальному моменту. Если доходы ввести в ячейки В1:В5 соответственно, чистая текущая стоимость проекта составит:

НПЗ(8%, B1:B5, -5000) - 37000 = 3167.77 руб.

  •  Определение срока платежа. Функция КПЕР.

Функция КПЕР вычисляет общее число периодов выплат как для единой суммы вклада (займа), так и для периодических постоянных выплат на основе постоянной процентной ставки. Если платежи производятся несколько раз в год, найденное значение необходимо разделить на число расчетных периодов в году, чтобы найти число лет выплат.

Синтаксис.    КПЕР(норма, выплата, нз, бс, тип).

Функция может применяться в следующих расчетах.

  1.  Если рассчитывается общее число периодов начисления процентов, необходимых для того, чтобы начальная сумма размером НЗ достигла будущего значения БС, то функция используется в виде:

КПЕР(норма, , нз, бс).

  1.  Формула для расчета общего числа периодов, через которые совокупная величина фиксированных периодических выплат составит значение БС, получается в виде:

КПЕР(норма, выплата, , бс, 1)

если платежи производятся в начале каждого расчетного периода, и в виде:

КПЕР(норма, выплата, , бс,)

для выплат в конце периода.

  1.  При погашении займа размером НЗ равномерными постоянными платежами в конце каждого периода число периодов, через которое произойдет полное погашение, равно

КПЕР( норма, выплата, нз).

Полученное значение можно также использовать как показатель срока окупаемости при анализе инвестиционного проекта. При этом предполагается, что поступление доходов происходит периодически равными величинами в конце или в начале каждого расчетного периода. Рассчитанное значение будет представлять число расчетных периодов, через которое сумма доходов, дисконтированных на момент завершения инвестиций, будет равна величине инвестиций.

Примеры.

Задание 1. Рассчитать, через сколько лет вклад размером 1 тыс. руб. достигнет величины 1 млн. руб., если годовая ставка процента по вкладу 16.79% и начисление процентов производится ежеквартально.

Решение. Функция КПЕР дает следующий результат:

КПЕР(16,79% / 4, , -1, 1000) = 168 - это число кварталов.

Число лет составит 168 / 4=42.

Задание 2. Для обеспечения будущих расходов создается фонд. Средства в фонд поступают в виде постоянной годовой ренты постнумерандо. Размер разового платежа 16 тыс. рублей. На поступившие взносы начисляется 11,18% годовых. Необходимо определить, когда величина фонда будет равна 100 тыс. рублей.

Решение. Для решения задачи необходимо вычислить величину n. В EXCEL этот расчет выглядит так

КПЕР(11.18%,-16,, 100)=5, т. е, через 5 лет совокупная величина выплат составит 100 тыс. руб.

Задание 3. Ожидается, что ежегодные доходы от реализации проекта составят 33 тыс. руб. Необходимо рассчитать срок окупаемости проекта, если инвестиции к началу поступления доходов составят 100 тыс руб., а норма дисконтирования 12.11%

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

КПЕР(12.11%,33,-100)=4 года.

  •  Расчет процентной ставки. Функция НОРМА.

Функция НОРМА определяет значение процентной ставки за один расчетный период. Для нахождения годовой процентной ставки полученное значение следует умножить на число расчетных периодов, составляющих год.

Синтаксис.   НОРМА (кпер, выплата, нз, бс, тип, предположение).

Функция НОРМА вычисляется методом последовательного приближения и может не иметь решения или иметь несколько решений. Если после 20 итераций погрешность определения ставки превышает 0,0000001, то функция НОРМА возвращает значение ошибки #ЧИСЛО1. В этом случае можно попытаться задать другой аргумент предположение, по умолчанию равный 10%. В большинстве случаев не требуется задавать аргумент предположение.

Рассмотрим варианты практического применения этой функции

  1.  Допустим, необходимо рассчитать процентную ставку при известной текущей стоимости НЗ, будущей стоимости БС, числе периодов КПЕР. Тогда формула в EXCEL в общем виде записывается так:

НОРМА (кпер, , нз, бс, , предположение).

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

НОРМА(кпер, выплата,, бс, тип, предположение).

  1.  Расчет процентной ставки по займу размером НЗ при равномерном погашении обычными периодическими платежами, при условии, что заем полностью погашается, ведется по формуле:

НОРМА (кпер, выплата, нз, ,, предположение).

Примеры.

Задание 1. Предположим, что компании потребуется 100 тыс. руб. через 2 года. Компания готова вложить 5 тыс. руб. сразу и по 2,5 тыс. руб. каждый последующий месяц. Каким должен быть процент на инвестированные средства, чтобы получить необходимую сумму в конце второго года.

Решение. В этой задаче сумма 100 тыс. руб. (аргумент БС функции НОРМА) формируется за счет приведения к будущему моменту начального вклада размером 5 тыс. руб. (аргумент НЗ) и фиксированных ежемесячных выплат (аргумент выплата). Используем функцию:

НОРМА(24, -2,5, -5, 100)= 3,28%.

Ежемесячная процентная ставка составит 3,28%, годовая – 12*3,28%=39,36%

Задание 2. Рассчитайте процентную ставку для четырехлетнего займа в 7000 руб. с ежемесячным погашением по 250 руб. при условии, что заем полностью погашается.

Решение. Будущее значение ежемесячных выплат по 250 руб. должно составить через 4 года сумму займа с процентами. Ежемесячная ставка процента должна составлять

НОРМА(48, -250, 7000) =2.46%,

Годовая процентная ставка составит 2.46% * 12 = 29.5%,

  •  Расчет периодических платежей. Функция ППЛАТ.

Функция ППЛАТ вычисляет величину выплаты за один период на основе фиксированных периодических выплат и постоянной процентной ставки. Выплаты, рассчитанные функцией ППЛАТ, включают основные платежи и платежи по процентам.

Синтаксис   ППЛАТ (норма, кпер, нз, бс, тип).

Функция ППЛАТ применяется в следующих расчетах

1. Допустим, известна будущая стоимость фиксированных периодических выплат, производимых в начале или в конце каждого расчетного периода. Требуется рассчитать размер этих выплат Для этого можно использовать формулу

ППЛАТ(норма, кпер., бс, тип).

2. Предположим, рассчитываются равные периодические платежи по займу величиной НЗ, необходимые для полного погашения этого займа через КПЕР число периодов. Текущая стоимость этих выплат должна равняться текущей сумме займа. Расчет в EXCEL выполняется по формуле;

ППЛАТ(норма, кпер, нз,, тип).

Обычно погашение происходит в конце каждого расчетного периода. Для этого случая формула имеет вид:

ППЛАТ(норма, кпер, нз), так как аргумент тип - 0,

Если заем погашается не полностью, то есть его будущее значение не равно 0, то следует указать аргумент БС, который равен непогашенному остатку займа после всех выплат.

Примеры.

Задание 1. Предположим, что необходимо накопить 4000 руб. за 3 года, откладывая постоянную сумму в конце каждого месяца. Какой должна быть эта сумма, если норма процента по вкладу составляет 12% годовых.

Решение. Общее число периодов начисления процентов кпер =3*12, норма = 12% / 12. Аргумент тип = 0, т.к. это вклады постнумерандо. Величина ежемесячных выплат будет равна:

ППЛАТ(12%/12, 12*3„4000)=-92,86руб.

Задание 2. Допустим, банк выдал ссуду 200 тыс. руб. на 4 года под 18% годовых. Ссуда выдана в начале года, а погашение начинается в конце года одинаковыми платежами. Определить размер ежегодного погашения ссуды

Решение. Ежегодные платежи составят ППЛАТ(18%, 4, -200) = 74,35 тыс. руб.

Контрольные вопросы

Для расчета каких значений используется функция БЗ?

Какие данные необходимо иметь для того, чтобы произвести расчет по функции НЗ?

В чем различие между аргументами «Начальное значение» и «Выплата»?

Что означает «платеж пренумерандо»?

С помощью какой функции можно определить процент начислений по кредиту?

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО ТЕМЕ 6

Задача 1. Пользователь Сбербанка внес вклад 5 млн. рублей. До какой суммы возрастет вклад через 7 лет, если процент годовых начислений равен а) 6 %, б) 10 %, в) 15%.

Задача 2. Вскоре после рождения сына родители решили внести в Сбербанк вклад с тем, чтобы через 22 года вклад вырос до суммы 30 млн. руб. Каков должен быть вклад, если процент годовых начислений равен 5 %, 8 %, 10%.

Задача 3. Через сколько лет начальный вклад 1000 $ увеличится до суммы 3000$, 5000 $ или 8000 $, если процент годовых начислений равен 6 %.

Задача 4. Какой процент годовых начислений должен обеспечить банк, чтобы первоначальный вклад 5000 $ увеличился втрое за 15, 18, 20 лет.

Задача 5. Рассчитать процентную ставку для 3-летнего займа размером 5000 руб. с ежеквартальным погашением по 1500 руб.

Задача 6. Определить эффективность инвестиции размером 200 млн, руб. по NPV, если ожидаемые ежемесячные доходы за первые пять месяцев составят соответственно: 20, 40, 50, 80 и 100 млн. руб. Издержки привлечения капитала составляют 13,5% годовых.

Задача 7. Определить, какие ежемесячные выплаты необходимо вносить по ссуде размером 200 тыс. руб., выданной на три года, при разных процентных ставках. Использовать Таблицу подстановки Excel.

Задача 8. Вкладчик Сбербанка решил ежегодно вносить вклад 1000$ в течение 10 лет. Сколько денег он сможет получить, если процент годовых начислений равен 5%, 10%, 15%.

Задача 9. Пусть вы откладываете 500 $ в конце каждого года на сберегательный счет при ставке 15 % годовых. Определить сумму накоплений к концу 6-го года.

Задача 10. Семья решила накопить для покупки дома сумму 20 000 $, вкладывая ежегодно сумму 1000$ (1500$ или 2000$). Сколько лет на это потребуется, если процент годовых начислений равен 6 %.

Задача 11. Какую сумму должен ежегодно вносить вкладчик, чтобы через 5 (10 или 15) лет накопить сумму 15000 $ при проценте годовых 3 %.

Задача 12. Каким должен быть процент годовых начислений, чтобы при ежегодном вкладе 1000 $ (1 500 $ или 3000 $) накопить сумму 20 000 $ за 15 лет.

Задача 13. Известно, что за 4 месяца вклад одного из вкладчиков Сбербанка увеличился с 4 млн. рублей до 6 млн. рублей. До какой суммы возрастет этот вклад через один год и через два года.

Задача 14.  Вскоре после рождения сына родители решили внести в Сбербанк вклад с тем, чтобы через 22 года вклад вырос до суммы 30 млн. руб. Каков должен быть вклад, если процент годовых начислений равен 5 %, 8 %, 10%.

Задача 15. Рассматриваются два варианта покупки недвижимости: заплатить всю сумму сразу - 70 000 руб. или платить ежемесячно по 800 руб. в течение 12 лет при ставке 9% годовых. Какой вариант более выгоден (68 743)?

Задача 16. Вексель на 3 000 000 долл. с годовой учетной ставкой 10% с дисконтированием два раза в год выдан на два года. Найти исходную сумму, выданную под этот вексель.

Задача 17. На счет в банке вносится сумма 10 000 долл. в течение 10 лет равными долями в конце каждого года. Ежемесячная ставка 4%. Какая сумма будет на счете через 10 лет?

Задача 18. Сумма 2000 долл. размещена под 9% годовых на три года. Проценты начисляются раз в квартал. Какая сумма будет на счете на конец периода договора.

Задача 19. Банк принимает вклад на срок 3 месяца с объявленной годовой ставкой 100% (140 625) или на 6 месяцев под 110% (169 254). Как выгоднее будет вложить сумму в 100 000 руб. на полгода: дважды на три месяца (проценты начисляются в начале квартала) или один раз на 6 месяцев (проценты начисляются ежемесячно)?

Задача 20. Получена ссуда в 20 000 долл. дана на полтора года под ставку 28% годовых с ежеквартальным начислением. Определить какую сумму необходимо будет вернуть кредитору.

Задача 21. Выдан кредит в сумме 1 млн. долл. с 15.01.04 по 15.03.04 под 120% годовых. Рассчитать сумму погасительного платежа.

Задача 22. Под темпом инфляции понимают относительный прирост цен за период. Фраза «инфляция идет в темпе 10% в месяц» означает, что имеет место схема сложных процентов, этапом в которой является месяц, за каждый месяц цены увеличиваются на 10%. Рассчитать стоимость заданного товара через 1 год при темпе инфляции 10% в год, 3% в месяц и 1% в месяц, если в начале года цена товара составляла 50 000 рублей.

ТЕМА 7. РЕШЕНИЕ ЗАДАЧ С ИСПОЛЬЗОВАНИЕМ ТАБЛИЦЫ ПОДСТАНОВОК.

Цель работы: освоить возможности модуля Таблица подстановок приложения Microsoft Excel в процессе решения финансово-экономических задач, требующих вычисления по одним и тем же формулам, но для различных серий данных.

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

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

Пример

Задание 1. Необходимо рассчитать таблицу умножения с помощью Таблицы подстановок. 

Решение. В первую очередь определите формулу, по которой будут рассчитываться значения в таблице. Это формула хi*yi, где i обозначает расположение в таблице значения х и у. Далее задайте ячейки для хi и для yi пустые ячейки, которые позднее будут использованы в формуле. Для этого в ячейках А1 и А2 запишите хi и yi соответственно, ячейку В1 выкрасите в синий цвет, а В2 в желтый для наглядности (рис. 1.48). Эти ячейки будут использоваться при расчете в формуле в качестве хi и yi, так ячейки с текстом в расчетах использовать нельзя. Начальное содержимое данных ячеек может быть нулевым, так как они нужны для того, чтобы определить переменные, от которых будет зависеть целевая формула.

Далее создайте таблицу, содержащую серию данных для расчета как это показано на рис. 1.7.1. По столбцу значения у, по строке - х. В ячейку В4 введите формулу для расчета всей таблицы =хi*yi (введите =В1*В2). Нажмите ОК. В ячейке В4  получите значение 0, так как пока ячейки В1 и В2 не содержат данных.

Рис. 1.48. Ввод расчетной формулы и выделение диапазона для таблицы подстановки

Рис. 1.49. Окно ТАБЛИЦЫ ПОДСТАНОВОК для задания необходимых ячеек

Выделите область таблицы, как это показано на рис. 1.49: строку с данными, столбец с данными, расчетную область и ячейку, содержащую формулу. Выполните команду меню ДАННЫЕ > ТАБЛИЦА ПОДСТАНОВКИ. Заполните параметры в появившемся диалоговом окне (рис. 1.49). Первое значение — Подставлять значения по столбцам в — должно содержать адрес ячейки с той переменной, вместо которой в целевую формулу будут подставляться значения из верхней строки таблицы подстановки. В данном случае вместо переменной из ячейки В1, то есть хi, последовательно будут подставлены в формулу значения из интервала C4:L4 – х1, х2 … х10. Аналогично, второе значение — Подставлять значения по строкам в — задает адрес ячейки той переменной (уi), вместо которой в целевую формулу будут подставляться значения из крайнего левого столбца таблицы подстановки (у1, у2 … у10). В данном примере вместо переменной из ячейки В2 в формулу будут подставлены значения из интервала В5:В14. ОК.

Результат заполнения таблицы подстановки показан на рис. 1.50.

Рис. 1.50. Результат заполнения таблицы подстановки

Задание 2. Предполагается, что в конце года капиталовложения по проекту составят около 1280 тыс. руб. Ожидается, что за последующие 4 года проект принесет следующие доходы: 420, 490, 550, 590 тыс. руб. Рассчитайте чистую текущую стоимость проекта для различных норм дисконтирования и объемов капиталовложений.

Решение. На рабочем листе Excel представьте исходные данные следующим образом (рис. 1.51).

Рис. 1.51. Таблица расчета чистой текущей стоимости проекта.

В ячейку D3 поместите предполагаемую величину начальных затрат по проекту (1280 тыс. руб.) со знаком «минус». Это значение необходимо включить в список аргументов функции НПЗ, так как чистая текущая стоимость рассчитывается на начало года, а капиталовложения будут осуществлены в конце года. В ячейки C9:F9 поместите различные объемы капиталовложений. Для расчета чистой текущей стоимости возьмите значения процентных ставок 13%, 13,8%, 15%. В ячейку D9 с помощью мастера функций поместите формулу для расчета: =НПЗ (D2, D3, D4, D5, D6, D7).

Для построения Таблицы подстановки выделите диапазон ячеек D9:H12, в меню ДАННЫЕ выберите команду ТАБЛИЦА ПОДСТАНОВКИ, и заполните диалоговое окно следующим образом:

Рис. 1.52. Диалоговое окно Таблицы подстановок

После нажатия кнопки ОК в ячейках Е10:Н12 появятся результаты расчета (рис. 1.51). Полученные значения представьте в виде гистограммы (рис. 1.53).

Рис. 1.53. Чистая текущая стоимость проекта для различных объемов капиталовложений и процентных ставок.

Очевидно, что максимальная величина чистой текущей стоимости достигается при минимальных капиталовложениях и минимальной ставке дисконтирования. Анализируя полученные результаты, можно отметить, что некоторые варианты дают практически одинаковую величину чистой текущей стоимости, например, при капиталовложениях 1310 тыс. руб. и норме дисконтирования 13,8% достигается та же величина NPV, что и при инвестициях размером 1270 тыс. руб. и ставке 15%.

При изменении размера ожидаемых доходов, инвестиций, процентных ставок Microsoft Excel автоматически пересчитывает всю таблицу.

Контрольные вопросы

В каких случаях возникает необходимость использовать Таблицу подстановок?

Каким образом необходимо выстроить данные для того, чтобы воспользоваться таблицей подстановок?

В какой ячейке рабочего листа записывается расчетная формула при проведении вычислений с помощью таблицы подстановок?

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО ТЕМЕ 7

Задача 1.

Задание. Государство стремится увеличить налоги, чтобы пополнить бюджет для выполнения социально-экономических и оборонных функций. Налогоплательщики в свою очередь не могут обеспечить выживание бизнеса при непомерном росте налогов. Определите оптимальное сочетание уровня рентабельности и налоговой ставки, при котором поступления в бюджет будут максимальными. На основе полученных данных постройте график, отображающий зависимость поступлений в бюджет от ставки налога и уровня рентабельности.

Примечания. Прибыль предприятия зависит от его рентабельности и определяется как произведение капитала на процент рентабельности (таблица 3 на рисунке 1.54).

Рис 1.54. Расчет поступлений в бюджет

Прибыль, идущая в качестве налога в бюджет, равна произведению суммы прибыли на налоговую процентную ставку. Остаток прибыли - это прибыль, оставшаяся после уплаты налога. Капитал сальдо определяется путем суммирования капитала за прошлый период и прибыли, оставшейся после уплаты налога за фактический период. Аналогичным образом определяется сумма поступлений в бюджет в последующие  периоды.    В    таблице    1    необходимо    получить   суммы   налоговых  поступлений в бюджет при различных налоговых ставках и различном уровне рентабельности.

Задача 2. Создайте таблицу предполагаемых цен на основные продукты питания через 6, 12 и 18 месяцев (рис. 1.55). Инфляция составляет 10% в год. Цены на текущее число заданы. Цену товаров с учетом инфляции вычисляйте по формуле сложных процентов (функция БЗ). Расчет таблицы производите с помощью таблицы подстановок.

Рис. 1.55. Динамика цен на продукты питания

ТЕМА 8. ПРОГРАММА ОПТИМИЗАЦИИ «ПОИСК РЕШЕНИЯ»

Цель работы: используя возможности программы Microsoft Excel эффективно планировать экономическую деятельность (решать задачи оптимизации).

Пример

Задание. Завод выпускает аудио- и видеотехнику. Известно, что на складе имеется запас комплектующих изделий в количестве указанном в таблице. Для производства каждого вида изделий требуются определенные затраты комплектующих. Каждому типу изделий соответствует своя норма прибыли. При увеличении объемов производства происходит уменьшение удельной прибыли в связи с дополнительными затратами на сбыт.

Составьте бизнес-план производства продукции таким образом, чтобы получить максимальную прибыль.  

Исходные данные приведены в таблице на рисунке 1.56.

Рис. 1.56. Бизнес-план производства продукции

Решение. Создайте в приложении Microsoft Ехсеl таблицу, как показано на рис. 1.56. Заполните имеющимися данными.

Рассчитайте данные по столбцу «расход по плану». Для этого сложите произведения нормы расходов ресурсов по каждому наименованию продукции и плана производства этих видов продукции. Т.е. формула для ячейки D13 будет выглядеть следующим образом: =$E$11*E13+$F$11*F13+$G$11*G13. Остальные ячейки столбца заполните самостоятельно. В результате получите значение 0 во всех ячейках, т.к. ячейки Е11:G11 не содержат данных.        

Теперь необходимо записать формулы для расчета прибыли по каждому виду изделий и прибыли всего. По видам изделий прибыль рассчитывается путем умножения прибыли на единицу изделия на максимально возможное количество произведенной продукции, скорректированное на уменьшение коэффициента отдачи. Для ячейки Е19 формула будет выглядеть следующим образом: 75*МАКС(Е11;0)^$Н$17.

Прибыль всего равна сумме прибыли по каждому виду изделий.

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

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

Для вызова программы оптимизатора выберите команду меню Сервис, Поиск решения. Появилось Диалоговое окно Поиск решения. Поле Установить целевую ячейку служит для указания целевой ячейки, значение которой необходимо максимизировать, минимизировать или установить равным заданному числу. Эта ячейка должна содержать формулу. В данном примере это ячейка Е20.  

Кнопка Равной служит для выбора варианта оптимизации значения целевой ячейки. В данном примере для максимизации прибыли нажмите кнопку Максимальному значению.

Поле Изменяя ячейки служит для указания ячеек, значения которых изменяются в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимизации значения ячейки, указанном в поле Установить целевую ячейку. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Допускается установка до 200 изменяемых ячеек.

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

Поле Ограничения служит для отображения списка условий поставленной задачи. Заполните поле Ограничения как показано на рисунке 1.57. Неравенство $D$13:$D$17<=$C$13:$C$17 означает, что расход комплектующих на производство не может превышать запасы комплектующих на складе, $Е$11:$G$11>=0 значит, что количество произведенной продукции не может быть меньше нуля.   

Рис. 1.57. Диалоговое окно программы Поиск решений

После того, как все необходимые условия оговорены нажмите Выполнить. В окне Результаты поиска решения нажмите ОК (рис. 1.58).

Рис. 1.58. Результаты поиска решений

Рис. 1.59. Рассчитанный бизнес-план производства продукции

В таблице в пустых ячейках появились значения, показывающие, что для получения максимальной прибыли в размере 14 917 р. в условиях ограниченного количества ресурсов необходимо произвести 160 телевизоров, 200 стереосистем и 80 акустических систем, при этом максимально эффективно будут использованы имеющиеся на складе комплектующие (рис. 1.59).

Задание 2. Клиент банка имеет инвестиционный капитал и желает получить от него максимальную прибыль при минимальном риске потери средств. Необходимо сформировать оптимальный портфель акций клиента.

Решение. В однофакторной модели Шарпа доходность портфеля определяется по формуле Rp=Rf+(Rm-Rf)*Bp,

где Rp – доходность портфеля, %,

Rf – доходность безрисковых активов, %,

Rm – доходность рынка, %,

Bp – Бета портфеля – показатель системного, рыночного риска портфеля.

где Wi – доля актива I в портфеле,

Bi – Бета i-й акции,

i – номер бумаги в списке портфеля,

n – количество бумаг в портфеле.

Риск портфеля определяется дисперсией доходности портфеля:

где Vp – дисперсия доходности портфеля,

Vm - дисперсия доходности рынка,

Vi – дисперсия доходности i-й бумаги.

Исходными данными для расчета характеристик портфеля являются доходность безрисковых активов (Rf) = 6%, доходность рынка (Rm) = 15%, дисперсия (риск) доходности рынка (Vm) = 3%, Бета каждой акции (Bi) - акция А = 0,80, акция В = 1,00, акция С = 1,80, акция D = 2,20, казначейские векселя = 0,00, остаточная дисперсия каждой акции (Vi) - акция А = 0,04, акция В = 0,20, акция С = 0,12, акция D = 0,40, казначейские векселя = 0,00.

Необходимо максимизировать доходность портфеля при ограниченном риске (дисперсии доходности портфеля):

Rp → max,

Vp <= Vb,

,

Wi >= 0,

где Vb – заданное инвестором ограничение риска портфеля в долях или процентах.

Минимизировать риск при заданном ограничении уровня доходности портфеля:

Vp → min,

Rp <= Rb,

,

Wi >= 0,

где Rb – заданное инвестором ограничение по уровню доходности портфеля в долях или процентах.

Все необходимые для решения формулы представлены в таблице на рисунке 1.60.

Рис. 1.60. Исходные данные для формирования эффективного портфеля ценных бумаг

Создайте аналогичную таблицу в приложении Microsoft Excel. Попытайтесь решить задачу вручную, подставляя значения в столбец Доля. Решение таким способом займет много времени.

Решите задачу с использованием команды Поиск решения. Для этого вызовите команду МЕНЮ СЕРВИСПОИСК РЕШЕНИЯ. В появившемся диалоговом окне оптимизатора заполните все необходимые поля (рис. 1.61).

Рис. 1.61. Диалоговое окно программы Поиск решения

Нажмите ВЫПОЛНИТЬ, СОХРАНИТЬ НАЙДЕННОЕ РЕШЕНИЕ, ОК.  

Рис. 1.62. Рассчитанная таблица

В результате получили структуру портфеля ценных бумаг, при которой в условиях ограничения процента риска на уровне не более 7,1% процент доходности максимальный – 17% (рис. 1.62).

Теперь определите структуру портфеля, при которой в условиях ограничения доходности не ниже 16,4% риск будет наименьшим. Для этого вызовите команду ПОИСК РЕШЕНИЯ. Далее можно как в прошлом случае заполнить поля вручную, но можно воспользоваться заданной моделью. В окне поиск решения нажмите кнопку ПАРАМЕТРЫ. В новом окне – ЗАГРУЗИТЬ МОДЕЛЬ. 

 

Рис. 1.63. Окно Загрузка модели

Укажите область $С$21:$C$29. ОК, ОК.

Рис. 1.64. Диалоговое окно программы Поиск решения после загрузки модели

 Рис 1.65. Сформированный портфель ценных бумаг

Контрольные вопросы:

Для решения каких задач используется процедура Поиска решения?

Какую ячейку называют «целевой»?

Для чего применяются ограничения в процессе поиска решений?

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО ТЕМЕ 8

Задание. Необходимо сформировать портфель активов пенсионного фонда так, чтобы максимизировать прибыль и ликвидность и минимизировать риск (рис. 1.66).

В математической постановке задачи оптимального планирования портфеля активов требуется найти вектор активов (А), максимизирующий линейную форму прибыли портфеля:

Где Prf - прибыль системы портфелей как цель, критерий оптимизации (максимизации).

A - сумма фонда к размещению в портфеле активов,

n - количество типов активов в портфеле,

a - цифровое имя отдельного типа активов,

Aa - объем инвестиций в денежном выражении в отдельный тип активов в портфеле,

Da - доходность отдельного типа активов.

Ограничения Правил Инспекции НПФ:

А1>=30% - в государственные ценные бумаги инвестируется не менее 30% фонда,

Aa <=10% - в любой другой проект не более 10% средств фонда,

N1<=0,5 – для максимального значения норматива соответствия,

Норматив соответствия вычисляется как отношение риска потери активов к сумме активов:

   

Где Ra – коэффициент риска, Задаваемый правилами для каждой группы активов,

N2>=0,04 – для минимального значения норматива достаточности собственных средств,

Норматив достаточности собственных средств вычисляется по формуле:


        где
OwCp – собственный капитал компании,

N3>=1 – для минимального значения норматива соотношения  фонда и его обязательств,

N3 = А/L,

где L – современная стоимость обязательств пенсионного фонда.

Технологические ограничения:

Сумма процентных долей активов должна равняться 100%,

Аа >=0, запрет на отрицательные инвестиции, т.е. займы.

Примечания. Наименования возможных инструментов активов с лимитами, коэффициентами риска и доходности также вводятся как исходные данные. Предварительный отбор инструментов рынка активов как кандидатов в портфель осуществляется традиционными методами маркетинга и формирования портфелей на основе оценок доходности, затратности и надежности Инструментов. Инструменты включены в группы, по которым в Правилах определены коэффициенты риска потерь активов.

Нормативы Правил, устанавливающие нижние и верхние лимиты инвестиций в конкретные объекты или группы, вводятся в колонки Мин. и Макс.

В математическую модель эти значения вставляются программой как правые части ограничений-неравенств. Правила задают только два ограничения: в государственные ценные бумаги вложить не менее 30% средств портфеля и в каждый любой объект не более 10 % портфеля. Таким образом, установлены жесткие требования диверсификации активов в не менее, чем 10 объектов. Как правило, в колонки лимитов Фонды включают также собственные диверсификаторы или границы рынков и ресурсов.

Критерий оптимизации вычисляется в ячейке Итого по портфелю - Доход. Он подлежит максимизации.

Исходные данные таблицы по доходности финансовых инструментов (графа Доходность, %) можно считать условными, поскольку они изменяются даже в течение торговых сессий. Доходность инструментов должна быть сопоставима. Поэтому после прогноза номинальных ставок вычисляются годовые эффективные ставки, которые затем корректируются ставками налогообложения и темпами инфляции. Последние существенно отличаются для различных групп активов. Доходность валютных активов задается в рублевом эквиваленте.  В графе Доход вычисляется ожидаемый годовой рублевый прямой доход по каждому инструменту. Суммарный доход по портфелю (критерий оптимизации) вычисляется в последней строке. Исходные данные в колонку Коэф. риска, % вводятся по группам из таблицы Нормативы размещения пенсионных активов. В следующей колонке вычисляются риски в стоимостном выражении, т. е. возможные потери активов в миллиардах рублей.

В разделе Нормативы деятельности фонда в колонки Мин. и Макс, вводятся предельные значения нормативов: портфельного риска (N1), покрытия риска собственным капиталом (N2) и балансовой ликвидности Фонда (N3). В колонке План программа показывает значения этих нормативов для сформированного портфеля.

После составления плановой таблицы ее показатели связываются формулами для вычислений, представленными в математической модели.

Показатели экономики фонда, млрд. руб.

Лимиты, %

 

План, прогноз

Мин.

Макс.

Минимальная оплата труда, т.р. в месяц

 

 80000

 

 

 

 

 

Собственные средства компании

 

 9000

 

 

 

 

 

Уставной капитал компании (не менее 50000мин зарплат)



0,4

 5000

 

 

 

 

 

 

Обязательства фонда  (современная стоимость)

 

 1000

 

 

 

 

 

 

Активы к размещению

 

 10000

 

 

 

 

 

 

Группы активов

Мин.

Макс.

Структура портфеля, %

Портфель, план, млрд.р.

Коэф.риска

Риски, млрд.р.

Доходность,%

Доход, млрд.р.

Средства на р/с в банке (А7)

 

 

 

 =структура портфеля*активы к размещению

 

 =коэф.риска*потфель, план

 

 =коэф.доходности*портфель,план

Гос ц.б. (А1)

 

 

 

 

 

 

 

 

ГКО

 

 

 

 

 

 

 

 

КО

 

 

 

 

 

 

 

 

ОГСЗ

 

 

 

 

 

 

 

 

Казначейские векселя США (дисконт)

 

 

 

 

 

 

 

 

Местные ц.б. (А2)

 

 

 

 

 

 

 

 

МКО, Москва

 

 

 

 

 

 

 

 

ОКО, Челябинск

 

 

 

 

 

 

 

 

Банковские вклады (А3)

 

 

 

 

 

 

 

 

Депозиты в КБ Роскредит

 

 

 

 

 

 

 

 

Депозиты в КБ Автобанк

 

 

 

 

 

 

 

 

Другие ц.б. (А4)

 

 

 

 

 

 

 

 

Акции АКБ "Оптбанк"

 

 

 

 

 

 

 

 

Акции OLIVETTI,Италия

 

 

 

 

 

 

 

 

Недвижимость (А5)

 

 

 

 

 

 

 

 

Гаражи в Москве

 

 

 

 

 

 

 

 

Квартиры в Москве

 

 

 

 

 

 

 

 

Катера прогулочные, Крым

 

 

 

 

 

 

 

 

Валютные ценности (А6)

 

 

 

 

 

 

 

 

Доллар США

 

 

 

 

 

 

 

 

Золото

 

 

 

 

 

 

 

 

Итого по портфелю

 

 

 

 

 

 

 

 

Нормативы деятельности фонда и компании

Мин.

Макс.

 

План

 

 

 

 

Нормативы соответствия, N1 (риски активов/активы)

 

 

 

 

 

 

 

 

Достаточность собствен-ных средств, N2, (собств средства/риски активов)

 

 

 

 

 

 

 

 

Отношение активов Фонда к обяз-ствам, N3

 

 

 

 

 

 

 

 

Рис. 1.66. Оптимальный план  портфеля активов НПФ

РАЗДЕЛ 2. РАБОТА С БАЗАМИ ДАННЫХ В MICROSOFT ACCESS

 

ТЕМА 1. ЗНАКОМСТВО С ИНТЕРФЕЙСОМ И ОСНОВНЫМИ ФУНКЦИЯМИ ПРОГРАММЫ MICROSOFT ACCESS

Цель работы: ознакомиться с интерфейсом программы MICROSOFT ACCESS, основными элементами баз данных.

Запустите программу Microsoft Access.

После запуска программы Microsoft Access на экране монитора появляется Главное окно системы (рис 2.1.).

Рис. 2.1. Рабочее окно программы Microsoft Access.

Строка меню содержит ряд элементов (заголовков меню). Каждому элементу соответствует отдельное меню, содержащее команды или опции.

Программа Microsoft Access использует систему контекстно-зависимых меню. Это означает, что внешний вид меню зависит от текущей ситуации в системе, от типа обрабатываемого объекта и т.д.

В строке состояния Microsoft Access выводит сообщения о ходе выполнения команд и о текущем состоянии системы.  

Прежде чем начать работу с базами данных, вызовите справку в открытом окне программы (СПРАВКА – СПРАВКА ПО MICROSOFT ACCESS). Откройте в появившемся окне Создание и работа с базами данных – Базы данных, что это такое. Прочитайте. Затем откройте Создание и разработка таблиц – Таблицы, что это такое и как они работают. Прочитайте. Закройте окно Справки.

Контрольные вопросы:

Что такое контекстно-зависимое меню?

Что представляет собой база данных?

Что представляет собой таблица?

Каким способом можно создать и редактировать таблицу в Мicrosoft Access?

ТЕМА 2. СОЗДАНИЕ ТАБЛИЦ В MICROSOFT ACCESS

Цель работы: освоить основные приемы работы с базами данных на примере построения таблиц в приложении Microsoft Access.

Пример  

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

Рис.2.2. Таблица – «Сведения о работниках»

Решение.

Существует несколько способов создания таблиц в программе Microsoft Access: с помощью Мастера таблиц, с помощью Конструктора, путем экспорта из другого приложения офис и др.

Создание таблиц в режиме «Мастер таблиц»

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

Для этого воспользуйтесь режимом Мастер таблиц. Этот режим позволяет при создании новой таблицы, использовать имеющиеся типовые шаблоны таблиц, выбирая из списка необходимые поля таблицы.

Запустите программу Microsoft Access. В открывшемся окне выберите Новая база данных. Раскроется окно сохранения новой базы данных. Необходимо задать имя создаваемой базе данных. В отличие от других приложений пакета Microsoft Office в Microsoft Access необходимо сохранить файл до того, как с ним будет начата работа. Задайте следующее имя базе данных: «Отдел кадров, фамилия студента». Нажмите кнопку Создать. (рис. 2.3).

Рис. 2.3. Присвоение имени новой базе данных

Появилось окно Базы данных, в котором можно выбрать объекты для создания: таблицы, запросы, формы, отчеты, макросы, модули.

Для создания новой таблицы выберите вкладку Таблицы (окно открывается на этой вкладке автоматически), нажмите кнопку Создать. На экране появится диалоговое окно Новая таблица (рис. 2.4).

Рис. 2.4. Выбор режима создания таблицы

В появившемся окне выберите Мастер таблиц. Нажмите Ок. Появилось окно Создание таблицы, в котором предлагается выбрать необходимую таблицу из предложенных образцов таблиц (рис. 2.5).

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

Выберите поля Код сотрудника (выделите образец поля и нажмите на одиночную стрелку вправо, если нажмете на двойную стрелку вправо, то перенесете сразу все образцы полей в новую таблицу), ИНН (такого поля среди образцов полей нет, поэтому выберите любой образец поля, вставьте его в новую таблицу, а затем переименуйте),Фамилия, Дата рождения, Адрес, Телефон, Пол, Дата найма, Сегодняшняя дата. Нажмите ДАЛЕЕ.  Измените имя таблицы на Сведения о работниках.

Рис. 2.5. Создание таблицы в режиме «Мастер таблиц»

Далее необходимо определить: самостоятельно устанавливать ключевое поле или программа сделает это автоматически.

Ключевое поле однозначно определяет каждую запись таблицы.

Выделите - Пользователь определяет ключ самостоятельно. Нажмите - ДАЛЕЕ. Выберите поле с уникальными для каждой записи данными. В данном случае это будет поле ИНН, так как, например, фамилии могут совпадать и т.д. Нажмите ДАЛЕЕ, ГОТОВО (рис. 2.6).

Появилось окно таблицы Сведения о работниках. Таблица пустая. В первом поле таблицы - слово Счетчик означает, что в это поле будет автоматически вводится номер – код сотрудника (рис. 2.7).

Рис. 2.6. Выбор способа определения ключевого поля

Рис. 2.7. Таблица «Сведения о работниках» в режиме ввода данных

Прежде чем заполнять таблицу данными, необходимо убедиться, что все поля имеют соответствующий тип данных. Для этого воспользуйтесь Конструктором. С помощью Конструктора можно также добавлять или удалять поля из таблицы, менять порядок расположения полей, а также производить другие изменения в структуре таблицы.

В диалоговом окне базы данных «Отдел кадров, Иванов» выберите таблицу Сведения о работниках, пока это единственная таблица в созданной базе данных, нажмите кнопку Конструктор. Появилась рабочая область конструктора, состоящая из двух таблиц: верхней - содержит три столбца - имя поля, где указываются поля таблицы, тип данных – определяется тип данных для каждого поля в зависимости от того, какая информация будет содержаться в этом поле, описание - используется для описания особенностей или предназначения поля.

Нижняя часть отражает свойства поля, изменяется в зависимости от выбранного типа данных поля (рис. 2.8).

Рис. 2.8. Таблица «Сведения о работниках» в режиме конструктора

Просмотрите тип данных, установленный для каждого поля, при необходимости внесите изменения: Код сотрудника – счетчик, ИНН – текстовый (поле ИНН содержит числовые данные, но так как эти значения не используются в расчетах, целесообразнее установить тип поля не числовой, а текстовый), адрес, фамилия, телефон, пол – текстовый, поля: дата рождения, дата найма, сегодняшняя дата – формат – дата/время.   

Основные типы данных:

Текстовый – предполагает текст или числа, не требующие проведения расчётов.

МЕМО.  Поле этого типа предназначено для хранения небольших текстовых данных (до 64000 символов). Поле этого типа не может быть ключевым или проиндексированным.

Числовой тип данных содержит множество подтипов. От выбора подтипа (размера) зависит точность вычислений.

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

Логический тип данных определяет логические значения, а так же поля, которые могут содержать одно из двух возможных значений.

Денежный. Денежные значения и числовые данные, используемые в математических вычислениях.

Дата/Время. Дата и время, которые могут быть записаны в специальном фиксированном формате.

Поле объекта OLE предполагает звукозапись, рисунок и прочие типы данных. Поле этого типа не может быть ключевым или проиндексированным.

Гиперсвязь.  Содержит адреса Web-страниц.

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

Закройте таблицу в режиме конструктора, сохраните изменения. Откройте в режиме ввода данных.

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

Закройте таблицу Сведения о работниках.

Создание таблицы в режиме «Конструктор»

Создайте таблицу Штат сотрудников с помощью Конструктора. Выберите элемент Таблицы, нажмите кнопку СОЗДАТЬ, КОНСТРУКТОР, ОК. На экране появилось пустое окно конструирования таблицы. В верхней части окна введите следующие имена полей, и задайте необходимый тип данных (рис. 2.9):

Рис. 2.9. Окно Конструктора таблиц

Код сотрудника (формат поля – счетчик), ИНН, Фамилия, Должность – формат - текстовый, Разряд (формат – числовой, так как в дальнейшем значение разряда будет использоваться в расчетах), Оклад (формат – денежный), Количество детей (формат – числовой).

Задайте ключевое поле. В данной таблице в качестве ключевого могут быть выбраны поля: Код сотрудника или ИНН. Поле Код сотрудника однозначно идентифицирует запись в таблице, но не является определяющим для записи из таблицы вне ее, в то время как ИНН является информацией, однозначно определяющей сотрудника, вне зависимости от того, в какой таблице используется информация. Поэтому в данной таблице следует установить в качестве ключевого поле ИНН. Для этого наведите курсор на серую область слева от того поля, которое необходимо установить как ключевое. Нажмите правую клавишу мыши и в контекстном меню выберите ключевое поле.

Слева от поля появился значок в виде ключа. При необходимости изменить ключевое поле, проделайте то же самое с тем полем, которое требуется установить ключевым (рис. 2.10).

Рис 2.10. Определение ключевого поля

Закройте конструктор, задав имя таблице - Штатное расписание. Откройте таблицу в режиме ввода данных.

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

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

Создание связей между таблицами.

Прежде чем заполнять таблицы данными, необходимо создать связи между ними для обеспечения более удобной работы с информацией в дальнейшем.

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

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

В данном примере обе таблицы содержат три одинаковых поля: Код сотрудника, ИНН, Фамилия. Связывать таблицы по полю Код сотрудника или Фамилия нецелесообразно, так как один и тот же сотрудник будет иметь разный код в каждой из таблиц, а фамилия может повторяться, например, в штате может быть два сотрудника с фамилией Иванов. Поэтому в данном примере связь будет строиться по полю ИНН.

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

Рис. 2.11. Создание связи между таблицами.

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

Появилось окно Изменение связей. Здесь указаны имена таблиц и полей, связываемых друг с другом. Имеется возможность определить способ взаимодействия таблиц. Выберите Обеспечение целостности данных, затем Каскадное обновление связанных полей (рис 2.12).

Это означает, что таблица Сведения о сотрудниках определяется как доминирующая, и, прежде чем вносить записи в таблицу Штатное расписание, необходимо внести соответствующую запись в таблицу Сведения о сотрудниках. В противном случае программа выдаст сообщение о несоблюдение целостности данных.

Рис. 2.12. Изменение связей

В нижней части окна Изменение связей указан тип отношения один-к-одному. Это значит, что одной записи в первой таблице соответствует одна запись во второй таблице. Тип связи определяется автоматически в зависимости от структуры связываемых таблиц и полей, по которым осуществляется связь.

Нажмите кнопку СОЗДАТЬ. Между макетами таблиц появилась соединяющая линия с цифрами 1 на концах, что означает тип отношения один-к-одному (рис. 2.13).

Рис. 2.13. Связь один-к-одному

Теперь откройте таблицу Штатное расписание, заполните ее самостоятельно, учитывая, что между таблицами установлена связь, поэтому возможна запись информации только по тем работникам, которые первоначально были внесены в таблицу Сведения о работниках.

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

Рис. 2.14. Просмотр информации из таблицы штатное расписание в таблице сведения о сотрудниках

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

  

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

В случае отношения один ко многим - одной записи в первой таблице может соответствовать несколько записей во второй таблице. Для создания такого типа связи необходимо внести изменения в таблицу Штат сотрудников.

Предположим, что один и тот же сотрудник организации может занимать несколько должностей одновременно, в этом случае он будет записан в таблице несколько раз, это приведет к повторению записей в поле ИНН, которое является ключевыми и, соответственно, не может содержать одинаковые значения. Требуется внести изменения в таблицу Штат сотрудников, а именно изменить ключевое поле, которым в таблице с учетом изменений условия может быть только поле Код сотрудника.

После того, как ключевое поле изменено, закройте режим конструктора, сохранив изменения.

Откройте Схему данных. Необходимо изменить тип связи, для этого удалите существующую связь: путем нажатия правой клавиши мыши на линии связи в тонкой ее части вызовите контекстное меню, выберите УДАЛИТЬ, подтвердите удаление.

Создайте связь между таблицами по полю ИНН, как было описано ранее. В окне Изменение связей указан тип один-ко-многим. Как и ранее установите галочку напротив надписи Обеспечение целостности данных, Каскадное обновление связанных полей. Нажмите кнопку СОЗДАТЬ (рис. 2.15).

Рис. 2.15. Тип связи один-ко-многим

Для того, чтобы просмотреть, как на практике работает связь между таблицами, заполните их данными, таким образом, чтобы в таблице Сведения о сотрудниках сотрудник был записан один раз, а в таблице Штатное расписание несколько раз с разными должностями.

Рис. 2.16. Две записи из таблицы Штатное расписание соответствую одной записи из таблицы Сведения о работниках

Аналогично создается связь многие-ко-многим. При этом нескольким значениям из первой таблицы соответствует несколько значений из второй таблицы.

После просмотра данных, удалите все записи в таблицах, так как в процессе дальнейшей работы с таблицами это может вызвать ошибки. Таблицы следует заполнять данными только после того, как созданы связи между ними и установлены свойства полей.

Использование свойства поля для настройки параметров ввода данных в таблицы

Возможности программы Microsoft Access предусматривают настройку параметров ввода данных в таблицу в зависимости от типа данных и требований пользователя. Например, имеется возможность ограничить количество вводимых символов, или задать маску ввода данных в таблицу, или создать список, из которого можно будет выбирать необходимое значение, если таковых ограниченное количество.

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

в поле Пол вводилось не более трех знаков (например: муж, жен);

в поле ИНН записывались номера, начинающиеся на 2632, состоящие из 8 цифр, причем первые 4 цифры должны появляться автоматически, так как в Ставропольском крае все идентификационные номера налогоплательщика начинаются на 2632 (другими словами необходимо создать маску ввода для поля ИНН);

в поле Адрес был создан список городов и поселков Ставропольского края, из которого пользователь при заполнении таблицы сможет выбирать необходимый.

Для того, чтобы настроить таблицу Сведения о работниках в соответствии с требованиями, откройте ее в режиме конструктора. Выделите поле Пол, в Свойствах поля установите Размер поля – 3.Таким образом, при заполнении пользователь не сможет ввести в это поле более трех знаков.

Создание маски ввода

Чтобы создать для поля ИНН маску ввода, выделите это поле, в Свойствах поля нажмите правой клавишей мыши в строке Маска ввода. Справа от строки появилась кнопка, нажатие на которую раскроет окно Создание масок ввода (рис. 2.17). 

Мастер создания масок ввода предлагает выбрать маску ввода из имеющихся образцов или создать новую. В столбце Маска ввода указано, для каких данных она применяется, в столбце Вид данных – приводиться образец записи данных в поле таблицы в режиме ввода данных. В строке Проба пользователь может просмотреть, как будет выглядеть маска ввода в поле при заполнении таблицы. Например, если выбрать маску ввода Краткий формат даты, то в таблице в поле, для которого она установлена, будет появляться шаблон «__.__.____», это позволит пользователю вводить дату без точек – 12122006, а программа автоматически преобразует запись в 12.12.2006.   

Рис. 2.17. Создание масок ввода

Маски ввода для поля ИНН в образцах нет. Для того, чтобы создать ее самостоятельно, воспользуйтесь кнопкой СПИСОК. Откроется окно Настройка масок ввода (рис.2.18).

В строку Описание введите название данных, для которых она создается – идентификационный номер налогоплательщика.

В строке Маска ввода необходимо с помощью специальных символов записать маску, которая автоматически будет записывать цифры 2632, далее - пробел и далее - четыре цифры от 0 до 9.

Рис. 2.8. Окно настройки масок ввода

Изучите таблицу 2 и запишите с помощью знаков маску ввода для поля ИНН.

Таблица 2

Символы, использующиеся для создания маски ввода

Знак

Описание

0

Цифра (от 0 до 9, ввод обязателен; знаки плюс [+] и минус [-] не допускаются).

9

Цифра или пробел (ввод не обязателен; знаки плюс и минус не допускаются).

#

Цифра или пробел (ввод не обязателен; пустые знаки преобразуются в пробелы, допускаются знаки плюс и минус).

L

Буква (от A до Z или от А до Я, ввод обязателен).

?

Буква (от A до Z или от А до Я, ввод не обязателен).

A

Буква или цифра (ввод обязателен).

a

Буква или цифра (ввод необязателен).

&

Любой знак или пробел (ввод обязателен).

C

Любой знак или пробел (ввод необязателен).

. , : ; - /

Десятичный разделитель и разделители тысяч, значений дат и времени. (Отображаемый знак зависит от настроек языка и стандартов на панели управления Microsoft Windows.)

<

Указывает перевод всех следующих знаков на нижний регистр.

>

Указывает перевод всех следующих знаков на верхний регистр.

!

Указывает заполнение маски ввода справа налево, а не слева направо. Заполнение маски знаками всегда происходит слева направо. Восклицательный знак в маске ввода можно помещать в любую позицию.

\

Указывает ввод любого следующего знака в качестве текстовой константы. Используется для отображения всех перечисленных в данной таблице знаков как текстовых констант (например, \A выводится как знак «A»).

Пароль

Значение Пароль, заданное для свойства Маска ввода (InputMask), создает поле для ввода пароля. Любой знак, введенный в поле, сохраняется как знак, но отображается как звездочка (*).

Маска ввода будет иметь следующий вид: «\2\6\3\2\ 0000» (рис. 2.9).

Рис. 2.9. Маска ввода для поля ИНН

В строке заполнитель указывается знак, вместо которого при записи данных в поле таблицы будут вписаны символы. По умолчанию устанавливается знак «_», но можно выбрать любой другой.

В строке Образцы данных можно проверить, как будет работать маска ввода в таблице при записи данных. Наведите курсор на эту строку, появиться маска 2632 ____, попробуйте сделать запись – введите любые четыре цифры. Символы, кроме цифр, в это поле записываться не будут.

Нажмите кнопку закрыть. К списку имеющихся масок ввода добавилась еще одна – ИНН. Выберите ее и нажмите кнопку ГОТОВО.

Создание списка для поля таблицы

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

В режиме конструктора создайте таблицу под названием Дополнительные данные, состоящую из поля №п/п (тип данных – счетчик, ключевое) и поля Населенные пункты (тип данных – текстовый).

Откройте таблицу в режиме ввода данных, введите 6-7 названий населенных пунктов Ставропольского края (рис. 2.10).

Рис. 2.10. Таблица Дополнительные данные

Вернитесь к таблице Сведения о работниках в режиме конструктора. Выберите поле Адрес, в Свойствах поля выберите вкладку Подстановка.

В строке Тип элемента управления установите Поле со списком. Появились дополнительные строки (рис. 2.11). Вкладка подстановка позволяет определить таблицу, из которой при заполнении определенного поля будет формироваться всплывающий список данных.

В строке источник строк укажите таблицу Дополнительные данные. Вызовите Построитель запросов, нажатием на кнопку справа от строки, где укажите непосредственно то поле, которое содержит список данных -  поле Населенные пункты (рис. 2.12).

Рис 2.11. Настройка свойств поля

Рис. 2.12. Окно Построитель запросов

Закройте окно построителя запросов, подтвердив сохранение.

Настройка полей таблицы в соответствие с условием завершена. Закройте конструктор, сохранив изменения. Откройте таблицу в режиме ввода данных.

Заполните таблицу данными в соответствие с рисунком 2.2. Обратите внимание, как на практике работают установленные настройки свойств полей. Заполните таблицу Штатное расписание самостоятельно, учитывая созданные ранее связи между таблицами.    

Контрольные вопросы

Что представляет собой таблица Microsoft Access?

Какими различными способами можно построить таблицу?

Какое поле может быть задано ключевым?

Что означает тип данных Счетчик?

В каких случаях удобно использовать Маску ввода?

Какие способы используются при необходимости редактировать созданную таблицу?

ТЕМА 3. СОЗДАНИЕ ЗАПРОСОВ В MICROSOFT ACCESS

Цель работы: освоить основные приемы работы с базами данных на примере построения запросов* в приложении MICROSOFT ACCESS

Пример

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

Решение. Запустите программу Microsoft Access. На панели пиктограмм выберите Открыть. Откройте созданную ранее базу данных. В появившемся окне в Объектах выберите Запросы. Затем нажмите Создать. В появившемся окне Новый запрос выберите Конструктор и нажмите ОК. В окне Добавление таблицы поочередно выберите и добавьте обе созданные вами таблицы. Закройте окно Добавление таблицы.

В окне Запрос 1 появились выбранные таблицы. Теперь необходимо в нижней части окна указать интересующие поля. Для этого перетащите путем нажатия и удерживания правой клавиши мыши поле ИНН из любой таблицы, Фамилию, Адрес, Дата рождения, Должность, Разряд, Оклад. Дальше необходимо создать поля, в которых будет рассчитываться сумма премии, налогов и зарплата к выдаче. Для этого наведите курсор на ячейку справа от Оклада по строке поле, нажмите правой клавишей мыши, в открывшемся меню выберите ПОСТРОИТЬ. Открылось окно Построитель выражений (рис. 2.13).

Рис 2.13. Окно построения выражений

Премию рассчитайте по следующей методике: оклад умножьте на разряд и разделите на десять. Для этого откройте двойным нажатием левой клавиши мыши в нижнем левом окне папку Таблицы / Штат сотрудников. В среднем окне выберите Разряд и нажмите Вставить (можно быстро два раза нажать левой клавишей мыши на необходимом объекте). Затем кнопку «*», Оклад, «/», 100. ОК.

В следующей ячейке строки таким же образом введите формулу для расчета суммы налога равной 28% от суммы оклада.

Теперь необходимо переименовать поля с «Выражение1» на «Премия» и «Выражение2» на «Налог». Для этого подведите курсор к ячейке, содержащей Выражение1, нажмите правой клавишей мыши, выберите СВОЙСТВА, Формат поляОсновной. Закройте окно. Теперь удалите запись Выражение1 (до двоеточия) и введите Премия. Таким же образом измените Выражение2.

Вернитесь к полю Адрес и в строке Условие отбора укажите Пятигорск.

Закройте запрос, сохранив его с именем Зарплата.

Просмотрите созданный запрос. Если в открытом окна запроса Зарплата нет данных, то среди сотрудников нет проживающих в Пятигорске. В этом случае необходимо изменить условия запроса так, чтобы получить какие-либо данные. Для этого на панели пиктограмм нажмите значок . Если такого значка нет, откройте вид, выберите конструктор.

В открывшемся окне удалите условие Пятигорск и введите тот город, который есть в созданной вами ранее таблице. Кроме того, необходимо рассчитать сумму зарплаты к выдаче. Для этого в пустой ячейке справа от Налога введите формулу расчета зарплаты: оклад + премия – налог. Нажмите правой клавишей мыши, выберите построить и в левой нижней области окна откройте папку Запросы/Зарплата. Далее выберите поля Оклад и Премия и суммируйте их, а затем вычете премию. ОК. Переименуйте ячейку «Выражение1» в «Зарплата». Закройте Конструктор запроса, сохранив изменения. Просмотрите запрос «Зарплата».

Контрольные вопросы

Что такое Запрос?

Чем отличается Запрос  от Таблицы?

Каким образом можно изменить условия запроса?

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО РАЗДЕЛУ 2

Задача 1. В программе Microsoft Acсess создайте базу данных с именем «КАДРЫ», согласно данным в таблице на рис. 2.14. Для поля должность создайте список должностей. Создайте запрос для получения сведений о сотрудниках, проживающих в г. Москва.

Рис. 2.14.  Сведения о сотрудниках

Задача 2. Создайте в базе данных Microsoft Acсess таблицу с именем «Штат сотрудников», имеющую вид:

Nп/п

Ф.И.О.

Должность

Разряд

Оклад

1

Аванесян

менеджер

5

7500

2

Алахвердова

экономист

5

7500

3

Бабаян

продавец

3

4500

4

Иванов

бухгалтер

4

5000

5

Петров

продавец

3

4500

ИТОГО

Рис. 2.15.  Штат сотрудников.

Создайте запрос, содержащий Ф.И.О. и разряд сотрудников, у которых оклад выше среднего.

Задача 3.

Создайте базу данных, содержащую сведения о студентах, состоящую из двух таблиц (смотреть таблицы на рисунках 2.16, 2.17).

Создайте связь между таблицами. Определите тип связи, объясните, что повлияло на установление такого типа связи.

Создайте маску ввода для поля № зачетки (примерный номер зачетки – ОП-123456).  

Создайте список для поля факультет ( 3-4 факультета).

Самостоятельно заполните таблицы сведениями о десяти студентах.

Рис. 2.16. Личные сведения о студентах

Рис. 2.17. Сведения о группах

РАЗДЕЛ 3. СОЗДАНИЕ МАКРОСОВ

Цель работы: освоение основных приемов и способов создания макросов.

Макрос - это серия команд и функций. Если какое-то действие часто повторяется, его выполнение можно автоматизировать с помощью макроса.

Макросу может быть присвоены кнопка на панели инструментов, пункт меню или сочетание клавиш.

Макросы часто используются для следующих целей:

  •  для ускорения часто выполняемых операций редактирования или форматирования;
  •  для объединения сложных команд; для упрощения доступа к параметрам в окнах диалога;
  •  для автоматизации обработки сложных последовательных действий в задачах;

Перед тем как записать макрос, необходимо спланировать шаги и команды, которые он будет выполнять. Если при записи макроса была допущена ошибка, ее исправление будет также записано. Каждый раз при записи макроса, он сохраняется в новом модуле, присоединенном к книге.

Для создания макроса достаточно включить запись и записать последовательность действий. После этого макрос можно выполнять каждый раз, когда надо произвести тот же набор действий снова. Например, если часто возникает необходимость отобразить на экране как можно большую часть документа, то можно записать макрос, максимизирующий окно документа и убирающий горизонтальную линейку, панель инструментов и полосы прокрутки.

Действия, проделанные с помощью мыши, не записываются. Для записи действий (например, выделения текста или перемещения курсора) необходимо использовать клавиатуру. Мышь может быть использована для выбора нужных команд и параметров.

Для записи макроса используется команда СЕРВИС – МАКРОС -НАЧАТЬ ЗАПИСЬ. Макросу необходимо присвоить имя. Первым символом имени макроса должна быть буква. Остальные символы могут быть буквами, цифрами или знаками подчеркивания. В имени макроса не допускаются пробелы; в качестве разделителей слов следует использовать знаки подчеркивания. Макросу можно назначить сочетание клавиш, при нажатии на которые данный макрос будет выполняться.

По окончании записи макроса необходимо нажать кнопку Остановить запись.

После присвоения макросу кнопки панели инструментов, команды меню или сочетания клавиш для его выполнения будет достаточно выбрать команду в меню, нажать кнопку на панели инструментов или нажать сочетание клавиш. При этом не теряется возможность выполнения макроса с помощью команды МАКРОС (меню СЕРВИС).

Примеры

Задание 1. Необходимо повысить зарплату сотрудникам фирмы, получающим 5600 руб. в месяц до 6050 руб. в месяц (рис 3.1).

Рис 3.1. Ведомость заработной платы сотрудников фирмы.

Автоматизируйте данную операцию с помощью макроса.   

Решение. Создайте в Microsoft Excel таблицу по образцу, приведенному на рис. 3.1. Рядом с имеющейся таблицей введите число 5600. Теперь выберите в Меню – Сервис – Макрос - Начать запись. Появилось диалоговое окно. Запишите в Имени макроса – Замена_з/п. Присвойте макросу сочетание клавиш Ctrl+q, в описании – повышение зарплаты с 5600 до 6050. ОК. В появившемся маленьком окошке нажмите Относительную ссылку . Теперь в ячейку справа от «5600» введите функцию. Выделите ячейку, нажмите кнопку функций на панели инструментов, выберите функцию Если, и запишите в Логическое выражение адрес ячейки, содержащей число 5600 = 5600 (например С3=5600), в Значение если истина  число 6050, Значение если ложь ту ячейку где написано 5600. ОК. Остановите запись макроса.

Возвращаемся к таблице. Необходимо изменить оклад. Для этого правее столбца Оклад вставьте пустой столбец. Выделите ячейку этого столбца и нажмите сочетание клавиш Ctrl+q (убедитесь, что установлен английский язык). Появилось значение – скопируйте в остальные ячейки автозаполнением.

Задание 2. Предположим необходимо создать ряд отчетов, содержащих сведения о деятельности предпринимателя за год.

В базе данных имеются такие сведения за каждый месяц. Для того, чтобы привести имеющиеся данные в соответствие с требованиями, необходимо на каждую страницу отчета вставить фамилию и идентификационный номер предпринимателя:

ИНН 263206946756

Частный предприниматель С.А. Иванов

Автоматизируйте процесс вставки заголовка. Для этого создайте макрос с именем Шапка, задайте сочетание клавиш Ctrl+q, с помощью которого введите на каждую страницу необходимые данные.

Решение. Откройте рабочее окно Microsoft Excel, начните запись макроса (СЕРВИС – МАКРОС – НАЧАТЬ ЗАПИСЬ), задайте макросу имя, укажите сочетание клавиш. ОК. После того, как запись макроса будет начата, вставьте две новые строки в самом верху страницы (два раза проделайте: ВСТАВКА – СТРОКИ), затем выделите три ячейки первой строки и объедините их в одну (нажмите кнопку  ). Введите текст ИНН 263206946756 (шрифт – 16 пт, полужирный). Затем объедините пять ячеек второй строки и введите текст: Частный предприниматель С.А. Иванов (шрифт – 16 пт, полужирный, подчеркнутый). Остановите запись. Для того, чтобы проверить работу макроса, откройте новый лист, нажмите Ctrl+q (убедитесь, что включен английский язык).  

Контрольные вопросы

Что такое макрос?

Для каких целей используют макросы?

На что необходимо обратить внимание при присвоении имени макросу?

Какими способами можно запустить макрос на выполнение?

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО РАЗДЕЛУ 3

Задача. Создайте макрос, позволяющий автоматизировать процедуру вставки следующих строк в рабочую область листа Microsoft Excel:               

Юридический адрес: г. Пятигорск,

              ул. Ермолова,47.

              Магазин «Парус».

ЗАДАНИЯ ДЛЯ ПРОВЕРКИ ОСТАТОЧНЫХ ЗНАНИЙ ПО ПРОЙДЕННОМУ МАТЕРИАЛУ

Задача 1

Задание. Заполните декларацию на доходы физических лиц за год, зная, что гражданин РФ Иванов:

  •  является владельцем акций двух компаний: «Лукойл» и «Юкос».  От первой предприниматель получил дивиденды на сумму 20000 руб., от второй - 10000 руб. Предприятия уплатили в бюджет суммы налога на прибыль, относящиеся к части прибыли, распределенной в виде дивидендов  в размере 24% от прибыли.
  •  в течение 2002 года дважды участвовал в соревнованиях по плаванию, проводимых по решению правительства и получил призы на сумму 680р. и 1000 р.
  •  1 февраля приобрел в магазине Эльдорадо телевизор в кредит под 0% сроком на десять месяцев стоимостью в 20100 руб. При выплате суммы кредита налог не был начислен. Ставка рефинансирования на момент получения кредита равнялась 24% годовых.
  •  22 апреля 2002 года выиграл в лотерею 2000 рублей.
  •  в мае 2002 года продал квартиру за 35 тыс. долларов (курс на момент продажи – 29,2 руб. за 1 доллар), которую приобрел 4 года назад.
  •  в июне посетил США, где читал лекции в Калифорнийском университете, за что в конце июля (31.07.2002, курс валюты – 30 руб. за 1 доллар) получил вознаграждение в размере 3000 долларов. Из них в США был удержан налог в размере 330 долларов. Ставка налога на доходы такого рода в России равна 13%.
  •  в августе отдыхал в Анталии, где 12.08.2002 выиграл в лотерею 900 000 лир (курс валюты 1 руб. – 150 лир). Из них в Турции удержано в виде налога 100000 лир. Ставка процентов на доходы такого рода в России 35%.

Определите сумму налоговых обязательств Иванова перед бюджетом на конец года по всем видам доходов.

Примечания:

  1.  Доходы от продажи имущества подлежат налогообложению с учетом вычетов предоставляемых на определенных условиях:
    •  Из сумм, полученных от продажи жилья или земельных участков, находящихся в собственности менее 5 лет производится вычет в размере 1000000 руб.,
    •  Из сумм, полученных от продажи жилья и земельных участков, находившихся в собственности 5 лет и более – 3000000 руб.,
    •  Из сумм, полученных от продажи имущества, находившегося в собственности менее 3 лет – 125000 руб.,
    •  Из сумм, полученных от продажи им-ва, находившегося в собственности 3 года и более – 300000 руб.,
    •  Из сумм, полученных от продажи ценных бумаг – 110000 руб.
  2.  Налог по дивидендам взимается с получателя этих дивидендов в размере 30 % от суммы дивидендов, за вычетом  суммы уплаченного налога предприятием - плательщиком дивидендов.
  3.  К доходам частично не подлежащим налогообложению относят доходы в виде сумм материальной помощи, оказываемой работодателями  своим работникам, стоимость подарков, полученных  от организаций и не подлежащих обложению налогом на наследование или дарение, стоимость призов полученных на соревнованиях, проводимых в соответствии с решениями Правительства РФ, возмещение работодателями работникам стоимости приобретенных медикаментов. При этом не подлежит налогообложению сумма в размере 2000 руб. по каждому виду перечисленных доходов.  
  4.  К доходам, облагаемым по ставке 35%, относятся доходы, полученные в виде выигрыша и материальная выгода по заемным средствам. Если выигрыш не превышает 2000 руб. налог по месту выдачи денег не взимается. При обложении дохода по ставке 35% из общей суммы допускается вычет в размере не более 2000 руб.
  5.  Материальная выгода по кредиту, представляет собой разницу между суммой процента в размере ¾ ставки рефинансирования на дату получения средств от суммы кредита и суммы начисленных процентов по договору. Сумма процентов в размере ¾ действующей ставки рефинансирования на дату получения рублевого кредита представляет собой произведение суммы кредита, ¾ дневной ставки рефинансирования и количества дней использования кредита (если речь идет о валютном займе – 9% годовых).
  6.  К доходам, облагаемым по ставке 30%, относятся доходы, полученные в виде дивидендов.
  7.  Отдельно учитываются и облагаются налогом доходы, полученные в иностранной валюте от источников за пределами РФ. Такие доходы облагаются налогами по месту их получения. В РФ налогообложение доходов полученных за границей происходит по российскому законодательству с учетом суммы уплаченных налогов по данному доходу за границей. При этом производится пересчет суммы дохода и суммы уплаченного налога в рубли по курсу ЦБ на день получения дохода. К зачету принимается сумма налога, уплаченная в иностранной валюте за границей, если она меньше суммы налога подлежащей уплате по законодательству РФ, иначе к зачету принимается сумма налога, исчисленная в соответствие с российским законодательством.

Задача 2

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

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

Исходные данные:

Предприниматель получил свидетельство на осуществление предпринимательской деятельности 01.04.04.

01.05.04 предприниматель принял на должность продавца двух женщин. Одной из них на момент принятия на работу - 23 года, имеет двухлетнего ребенка, другой -  48 лет. Оклад продавцов – 700 руб. в месяц. Процент отчислений в пенсионный фонд – 14%. Для лиц 1967 года

рождения и моложе 10% идет на страховую часть пенсии и 4% - на накопительную.

За первый квартал деятельности предприниматель уплатил в бюджет 588 руб., из них 175 руб. ежемесячно на страховую часть трудовой пенсии, и 21 руб. ежемесячно на накопительную часть трудовой пенсии.

При подаче расчета по взносам на пенсионное страхование за второй квартал своей деятельности (3 квартал года) предприниматель произвел пересчет фактически уплаченных сумм и выявил ошибки. С учетом пересчета предприниматель за июль и август заплатил по 175 руб. на страховую часть трудовой пенсии и по 21 руб. на накопительную часть трудовой пенсии за каждый месяц, а в сентябре - 28 руб. и 70 руб. соответственно.

За последний квартал года предприниматель уплатил в бюджет страховые взносы на страховую часть трудовой пенсии по 168 руб. в месяц и на накопительную по 28 руб.

В течение 2004 года численность наемных работников не изменялась. Оклад не изменялся.

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

При заполнении таблиц используйте формулы из столбца Наименование показателя.

Задача 3.

Задание. Проанализируйте ликвидность баланса предприятия «Чеширский маслозавод» за 2002, 2003, 2004, 2005 года. В балансе рассчитайте недостающие данные. Для проведения анализа ликвидности используйте таблицу Анализ ликвидности баланса, постройте аналогичные таблицы для анализа данных за 2003, 2004, 2005 годы. Постарайтесь максимально автоматизировать ввод и обработку данных.

ЗАДАНИЯ ДЛЯ КОНТРОЛЬНОЙ РАБОТЫ ДЛЯ СТУДЕНТОВ ЗАОЧНОЙ ФОРМЫ ОБУЧЕНИЯ

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

ВАРИАНТ 1

Задание № 1

  1.  Проанализируйте финансовую деятельность условной компании за 2003 г. по филиалам в среде EXCEL. Вид исходной таблицы показан на рис.1;
  2.  Рассчитайте сумму товарооборота фирмы по месяцам;
  3.  Рассчитайте товарооборот фирмы за год по филиалам;
  4.  Рассчитайте удельный вес деятельности каждого филиала в общем объеме товарооборота;
  5.  Присвойте ставку налога каждому филиалу, учитывая, что, если доход равен или меньше 15000000 рублей в год, то взимается налог в размере 10% от суммы товарооборота, если больше – ставка налога равна 24% от суммы прибыли (функция ЕСЛИ),
  6.  Рассчитайте сумму расходов на приобретение и продажу продукции для каждого филиала, используя общую сумму расходов по предприятию и найденный удельный вес товарооборота каждого филиала в общем товарообороте;
  7.  Рассчитайте сумму непредвиденных расходов, зная, что она равна 4% от суммы расходов на приобретение и реализацию продукции;
  8.  Определите прибыль без учета налогов;
  9.  Рассчитайте сумму налогов по филиалам и в целом по предприятию, учитывая, что 10 %-я ставка рассчитывается от суммы товарооборота, а 24% от суммы прибыли;
  10.  Рассчитайте сумму чистой прибыли предприятия за год;
  11.  Постройте круговую диаграмму для итоговой деятельности предприятия по филиалам (по столбцу «Товарооборот за год»).

Финансовая деятельность компании по филиалам

в 2003 г. (тыс.руб)

Товарооборот за год

Уд вес деят-ти ф-                   

ла в общем т/об-те

% ставка

Расходы на прио-бретение и продажу продукции

Непредвиденные расходы 4%

Прибыль без учета налогов

Сумма налога

Филиал

Январь

Февраль

Март

Апрель

Май

Июнь

Июль

Август

Сентябрь

Октябрь

Ноябрь

Декабрь

1

2250

2354

2587

2459

2545

2354

2201

2502

2498

2526

2654

2547

2

125

354

541

541

654

654

320

302

425

489

347

410

3

845

785

859

941

1105

747

754

706

902

899

821

854

4

1287

1168

1397

1374

1320

1258

1489

1541

1259

1564

1523

1405

5

148

158

155

258

351

369

456

300

354

368

425

306

6

654

458

687

645

510

741

321

548

654

507

651

700

7

1154

1472

1587

1345

1341

1852

1747

1856

1759

1801

1954

1820

Итого по предприятию

-

-

73505,6

               Чистая прибыль предприятия за год

Рис.1 Финансовая деятельность компании по филиалам

Задание №2

Рассчитайте, какая сумма окажется на счете, если вклад размером 50600 руб. размещен на банковском счете под 13,5% годовых на 4 года, а проценты начисляются каждые

  •  полгода,
  •  ежеквартально,
  •  ежемесячно

Задание №3

Рассчитайте, через сколько лет произойдет полное погашение займа размером 500000 р., если выплаты по 100 тыс.р. производятся в конце каждого квартала, а ставка равна 15% годовых. Сколько лет понадобиться для погашения этого займа, если выплаты по 150 тыс. р. производятся в конце каждого полугодия?   

ВАРИАНТ 2

Задание №1.

Определите размеры периодических взносов в фонд размером 100 тыс.р., сформированный за 2 года ежеквартальными платежами, если процентная ставка равна 20% годовых. Какими должны быть размеры ежемесячных взносов?

Задание №2.

Есть два варианта инвестирования средств в течение 10 лет: в начале каждого года под 20% годовых или в конце каждого года под 30% годовых. Ежегодно вносится 100 тыс. руб. Определите, сколько денег окажется на счете в конце 10-го года для каждого варианта.

Задание №3.

  1.  Создайте в EXCEL таблицу (рис. 2).
  2.  Определите коэффициент для расчета суммы оклада, учитывая что:

если разряд равен 1, то коэффициент будет иметь значение 3,

если разряд равен 2, то коэффициент будет иметь значение 4,

если разряд равен 3, то коэффициент будет иметь значение 5,

если разряд равен 4, то коэффициент будет иметь значение 6,

если разряд равен 5, то коэффициент будет иметь значение 7,

если разряд равен или больше 6, то коэффициент будет иметь значение 8 (функция ЕСЛИ).

  1.  Определите оклад для каждого работника, если он равен произведению МРОТ (300), номера разряда и коэффициента.
  2.  Рассчитайте сумму премии, которая равна 26% от суммы оклада для каждого работника.
  3.  Определите отчисления в пенсионный фонд для каждого работника, зная, что, если общая сумма начисленной з/п (оклад + премия) меньше или равна 20-кратному размеру МРОТ, то процент отчисления будет равен 12, если больше – 15%.
  4.  Рассчитайте сумму подоходного налога в размере 1% от  общей суммы начисленной з/п.
  5.  Определите сумму з/п к выдаче.
  6.  Определите среднюю, максимальную и минимальную з/п по предприятию (с помощью соответствующих функций).
  7.  Определите каким должен быть процент премии, чтобы общая сумма з/п к выдаче была равна 300000 руб. (Подбор параметров).

Рис. 2. Ведомость расчета зарплаты

ВАРИАНТ 3

Задание №1

Создайте таблицу расчета заработной платы по образцу (рис. 3).

Рассчитайте премию (23% от оклада), всего начислено = оклад + премия. При осуществлении расчетов в формулах используйте относительные и абсолютные ссылки на ячейки, в зависимости от необходимости.

Используя режим Подбора параметра, определите, при каком значении процента премии общая сумма месячной заработной платы всех сотрудников организации, предназначенная к выдаче, будет равна 25000 руб. Рассчитайте средний доход за месяц.

Рис. 3. Ведомость расчета зарплаты

Определите максимальный, минимальный и средний доход (по столбцу к выдаче) с помощью соответствующих функций.

Задание №2

На сберегательный счет ежемесячно вносятся платежи по 200 долларов. Рассчитайте, какая сумма окажется на счете через 4 года при ставке 20% годовых: 1) в начале каждого месяца, 2) в конце каждого месяца.

Задание №3.

Рассчитайте, какая сумма окажется на счете, если 55 тыс. руб. положены на 3 года под 15% годовых. Проценты начисляются каждые полгода.

ВАРИАНТ 4

Задание №1.

Проанализируйте продажи макаронных изделий за 3 мес. с помощью программы EXCEL. Исходные данные приведены в таблице на рисунке 4. Рассчитайте значения пустых ячеек. Определите объем продаж спагетти в августе с помощью линии тренда, определите коэффициент достоверности прогноза. 

Артикул

Январь

февраль

март

Сумма

Уд. Вес

Лапша

256000

234000

266521

Рожки

240000

276548

287654

Ушки

414000

410876

411113

Вермишель

347651

357654

390870

Спагетти

164198

875432

870965

Ракушки

112233

165743

111654

Сумма

 

 

 

 

 

Рис. 4. Продажи макаронных изделий за первый квартал

Задание №2. 

Есть два варианта инвестирования средств в течение 5 лет: в начале каждого года под 16% годовых или в конце каждого года под 28% годовых. Ежегодно вносится 5600 тыс. руб. Определите, сколько денег окажется на счете в конце 5-го года для каждого варианта.

Задание №3. 

Займ размером в 40000 р. выдан на 5 лет под 29% годовых. Определите размер ежегодного погашения займа.

ВАРИАНТ 5

Задание №1

Создайте таблицу по образцу (рис. 5).

Определите стаж работы с использованием даты найма и текущей даты, учитывать только количество полных лет.

Отсортируйте записи по:

  •  алфавиту,
  •  по уменьшению разряда,
  •  по убыванию стажа работы.

Выдайте список сотрудников организации:

  •  у которых срок заключения контракта (5 лет) истекает в текущем году (Автофильтр),
  •  проживающих в городе Ессентуки (Автофильтр),
  •  женщин, занимающих должность - продавец, проживающих в Пятигорске, родившихся до 12.12.1980 (Расширенный фильтр).

Рис. 5. Сведения о сотрудниках

Задание №2

Предполагается путем ежеквартальных взносов постнумерандо по 3570 долларов в течении трех лет создать фонд размером 500500 долларов. Какой должна быть годовая процентная ставка? Какой должна быть ставка, если взносы осуществляются в начале года?

Задание №3

На счет в банке вносится сумма 10 000 долл. в течение 10 лет равными долями в конце каждого года. Годовая ставка 4%. Какая сумма будет на счете через 10 лет?

ВАРИАНТ 6

Задание №1

Создайте с помощью программы Microsoft Access базу данных, состоящую из двух взаимосвязанных таблиц (рис 6, 7), создайте маску ввода для поля ИНН. Постройте запрос по следующим условиям:  должность – продавец, с датой рождения до 1980 года, проживающих в Пятигорске.

Рис. 6. Сведения о сотрудниках

Рис. 7. Ведомость начисления зарплаты

Задание №2

Какой должна быть годовая процентная ставка по вкладу размером 67800 руб., для того чтобы к концу года его величина составила: 1) 120000 руб., 2) 150000 руб., 3) 200000 руб., проценты начисляются ежемесячно.

Задание №3

Банк принимает вклад на срок 3 месяца с объявленной годовой ставкой 100% или на 6 месяцев под 110%. Как выгоднее вложить сумму в размере 50000 руб. в течение полугода: дважды на три месяца или один раз на 6 месяцев?

ВАРИАНТ 7

Задание №1

Создайте в программе Microsoft Access базу данных для библиотеки, состоящую из двух взаимосвязанных таблиц. Первая должна содержать следующие поля: №п/п, № зачетной книжки, № студенческого билета, Фамилия студента, факультет, группа, наименование книги, автор, ISBN. Вторая таблица - наименование книги, автор, ISBN, год издания, тип издания (учебник, журнал, автореферат), количество страниц, краткое описание, количество экземпляров в библиотеке. Создайте для полей № зачетной книжки, № студенческого билета, ISBN (например – «А 5676») – маску ввода. Для поля Тип издания – список. Постройте запрос по собственным условиям.  

Задание №2

Какой должна быть годовая процентная ставка по вкладу размером 16200 руб., для того чтобы к концу года его величина составила: 1) 52000 руб., 2) 72500 руб., 3) 83000 руб., проценты начисляются ежегодно.

Задание №3

Семья решила накопить для покупки дома сумму 80 000 $, вкладывая ежегодно 11000 $ (15000 $ или 20000 $). Определите сколько лет на это потребуется, если процент годовых начислений равен 6 %.

ВАРИАНТ 8

Задание №1

Определить текущую стоимость фонда, сформированного за 6 лет обязательными ежемесячных платежами размером в 10000 р., если процентная ставка по этим платежам равна:

11,5% годовых,

12% годовых,

13,5% годовых.

Задание №2

Фирма «Дельта М» занимается поставками бытовой техники. В марте  со склада по договорам был отпущен товар основным покупателям. Создать таблицу, отражающую общие суммы продаж магазина «Дельта +» в рублях за март по каждому из покупателей (функция СУММЕСЛИ).  

Исходные данные смотреть в таблице на рисунке 8.

Расчетная таблица представлена на рисунке 9.

Рис. 8. Продажи фирмы за март

Рис 9. Таблица для расчета сумм продаж за март по каждому покупателю

Задание №3

Предположим, вы хотите купить машину за 12000 $. Продавец предлагает два варианта: заплатить всю сумму сразу или выплачивать 350 $ ежемесячно в течение пяти лет со ставкой 17 % годовых.

Какой из предложенных вариантов более выгодный.

ВАРИАНТ 9

Задание №1

Клиент банка имеет инвестиционный капитал и желает получить от него максимальную прибыль при минимальном риске потери средств. Необходимо сформировать оптимальный портфель акций клиента.

Доходность портфеля определяется по формуле

Rp=Rf+(Rm-Rf)*Bp,

где Rp – доходность портфеля, %,

Rf – доходность безрисковых активов, %,

Rm – доходность рынка, %,

Bp – Бета портфеля – показатель системного, рыночного риска портфеля.

где Wi – доля актива I в портфеле,

Bi – Бета i-й акции,

i – номер бумаги в списке портфеля,

n – количество бумаг в портфеле.

Риск портфеля определяется дисперсией доходности портфеля:

где Vp – дисперсия доходности портфеля,

Vm - дисперсия доходности рынка,

Vi – дисперсия доходности i-й бумаги.

Исходными данными для расчета характеристик портфеля являются доходность безрисковых активов (Rf) = 6%, доходность рынка (Rm) = 15%, дисперсия (риск) доходности рынка (Vm) = 3%, Бета каждой акции (Bi) - акция А = 0,60, акция В = 1,00, акция С = 1,90, акция D = 2,20, казначейские векселя = 0,00, остаточная дисперсия каждой акции (Vi) - акция А = 0,04, акция В = 0,20, акция С = 0,12, акция D = 0,40, казначейские векселя = 0,00.

Необходимо максимизировать доходность портфеля при ограниченном риске (дисперсии доходности портфеля):

Rp → max,

Vp <= Vb,

,

Wi >= 0,

где Vb – заданное инвестором ограничение риска портфеля в долях или процентах (не более 9,4%).

Рис. 10. Исходные данные для формирования эффективного портфеля ценных бумаг

Задание №2

Определите товарооборот за год, рассчитайте сумму налога, которая равна 15% от суммы товарооборота. Определите чистую прибыль. Какой должна быть процентная ставка налога, чтобы чистая прибыль составила 65000000 руб. (Подбор параметров). Спрогнозируйте товарооборот предприятия за май 2004 года (Линия тренда). Исходные данные смотрите в таблице на рис. 11.

В тыс. руб.

Товарооборот за 2003 год

Сумма

налога

Филиал

Январь

Февраль

Март

Апрель

Май

Июнь

Июль

Август

Сентябрь

Октябрь

Ноябрь

Декабрь

1

2250

2354

2587

2459

2545

2354

2201

2502

2498

2526

2654

2547

2

125

354

541

541

654

654

320

302

425

489

347

410

3

845

785

859

941

1105

747

754

706

902

899

821

854

6

654

458

687

645

510

741

321

548

654

507

651

700

7

1154

1472

1587

1345

1341

1852

1747

1856

1759

1801

1954

1820

Итого

Чистая прибыль _________

Рис. 11. Товарооборот предприятия по филиалам за год

ВАРИАНТ 10

Задание №1

Составьте отчет о движении товаров за январь месяц по образцу.

Определите объем продаж предприятия, учитывая, что он зависит от количества проданных товаров двум покупателям: ЧП Шахян и ООО «Черкесск».    

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

Определите, кому из покупателей принадлежит большая доля в объеме реализации товаров предприятием (круговая диаграмма).

Исходные данные и образцы отчета и бланков заказов представлены на рисунках ниже.

ВОПРОСЫ К ЭКЗАМЕНУ (ЗАЧЕТУ)

  1.  Объективная необходимость развития процесса информатизации
  2.  Понятие экономической информации
  3.  Информационные ресурсы
  4.  Понятие и свойства информационных систем
  5.  Понятие и история развития экономических информационных систем
  6.  Автоматизированные экономические информационные системы
  7.  Структура автоматизированной экономической информационной системы. Функциональные и обеспечивающие подсистемы
  8.  Автоматизированные информационные технологии
  9.  История и тенденции развития автоматизированных информационных технологий
  10.  Автоматизированное рабочее место - средство автоматизации работы конечного пользователя
  11.  Значение технологии электронной обработки экономической информации
  12.  Обработка текстовой информации
  13.  Табличные процессоры как средство обработки финансово - экономической и статистической информации
  14.  Системы управления базами данных и их применение для обработки экономической информации
  15.  Сущность и значение корпоративных информационных технологий
  16.  Роль корпоративных информационных технологий при обработке экономической информации
  17.  Проблемы и эффекты от использования корпоративных информационных систем на предприятии
  18.  Оценка эффективности использования информационных систем в процессе обработки экономической информации
  19.  Классификация программных продуктов представленных на рынке
  20.  Обзор финансово-экономических программных продуктов представленных на рынке
  21.  Система визуального бизнес-моделирования - BUSINESS STUDIO
  22.  Информационные объекты регионального уровня - АИС "Налог"
  23.  Информационная правовая система "КОДЕКС"
  24.  Технология    прогнозирования    продаж  FOREXSAL
  25.  Система анализа финансового состояния предприятия - Audit Expert
  26.  Система разработки бизнес-планов и анализа инвестиционных проектов Project Expert  
  27.  Корпоративная информационная система «Лагуна»
  28.  Комплексная автоматизация предприятия «Парус»
  29.  Программа «Клиенты предприятия»
  30.  Программа «Склад и Интернет магазин»
  31.  Комплексная автоматизация предприятия «Олимп»
  32.  Комплексная автоматизация предприятия «Галактика»
  33.  Комплексная автоматизация предприятия «NS 2000»
  34.  Комплексная автоматизация предприятия «БОСС-Компания»
  35.  Программы семейства «1С»
  36.  Цель и принципы создания экономических информационных систем
  37.  Методы проектирования экономических информационных систем  
  38.  Способы декомпозиции экономических информационных систем  
  39.  Жизненный цикл экономической информационной системы
  40.  Автоматизация проектирования экономических информационных систем
  41.  Роль и место специалиста экономического профиля на стадиях жизненного цикла создания, развития и эксплуатации информационной системы
  42.  Основные принципы построения и использования автоматизированных систем во внешнеэкономической деятельности
  43.  Понятие искусственного интеллекта
  44.  Системы поддержки принятия решений
  45.  Экспертные системы
  46.  Интернет - история создания
  47.  Современная структура сети Интернет
  48.  Интранет. Использование технологий Интернета в локальной сети
  49.  Основные протоколы сети Интернет
  50.  Система доменных имен DNS
  51.  Передача информации в сети
  52.  Электронная почта
  53.  Система World Wide Web
  54.  Поиск информации в Интернете
  55.  IP-телефония
  56.  Электронная коммерция
  57.  Системы электронных платежей

 

СПИСОК ЛИТЕРАТУРЫ

  1.  Бекаревич Ю.Б., Пушкина Н.В. Microsoft Access 2000. СПб.: БХВ - Санкт-Петербург, 2003.
  2.  Гарсиа-Молина Г., Ульман Дж., УидомДж. Системы баз данных. Полный курс. — М.: Издательский дом "Вильямс", 2002.
  3.  Дейт К.   Введение   в   системы   баз   данных,    1-е   издание. — М.: Издательский дом "Вильямс", 2001.
  4.  Додж М., Кината К., Стинсон К. Эффективная работа с Excel 2000. — СПб.: Питер, 2001.
  5.  Долженков В.А.,  Колесников Ю.В.  Microsoft Excel 2000. СПб.: БХВ - Санкт-Петербург, 2001.
  6.  Евдокимов В. В. и др. Экономическая информатика. Учебник для вузов/Под ред. д. э. и. проф. В. В. Евдокимова. СПб.: Питер, . — 2000.
  7.  Ильина О.П. Информационные технологии бухгалтерского учета. – СПб.:Питер, 2002.
  8.  Каймин В.А., Касаев Б.С. Информатика: Практикум на ЭВМ: Учебное пособие. - М., ИНФРА-М, 2001.
  9.  Карлберг Конрад. Бизнес-анализ с помощью Excel/Пер. с англ. К.: Диалектика, 2001.
  10.  Ковалев В. В. Финансовый анализ: Управление капиталом. Выбор инвестиций. Анализ отчетности. М.: Финансы и статистика, 2001.
  11.  КоноллиТ., Бегг К. и др. Базы данных: проектирование, реализация и сопровождение. — М.: Издательский дом "Вильямс", 2001.
  12.  Кузнецов С.Л. Делопроизводство на компьютере (Компьютерные технологии в делопроизводстве)/Изд. 3-е перераб. и дополн. - М.: ЗАО «Бизнес-школа «Интел-Синтез», 2000.
  13.  Лавренов СМ. Excel: Сборник примеров и задач. - М. Финансы и статистика, 2000.  
  14.  Новиков Ф. А., Яценко А. Д. Microsofr Office 2000 в целом. - СПб.: БХВ - Санкт-Петербург, 2000.
  15.  Овчаренко Е. К., Ильина О. П., Балыбердин Е. В. Финансово-экономические расчеты в EXCEL/Издание 3-е, переработанное и дополненное. М.: Информационно-издательский дом “Филинъ”, 2002.
  16.  Попов А.А. Excel: практическое руководство. М.: ДЕСС. 2000.
  17.  Палий В. В. Финансовый учет: Учебное пособие. — М.: ФБК-ПРЕСС. 4.1, 2002.
  18.  Риккарди Г. Системы баз данных. Теория и практика использования в Internet и среде Java. — М.: Издательский дом "Вильямс", 2001.
  19.  РолландФ.   Основные   концепции   баз   данных. ~    М.: Издательский дом "Вильяме", 2002.
  20.  Робинсон С. Microsoft Access 2000: учебный курс. — СПб.: Питер, 2002.
  21.  Рычков В. С. Самоучитель Excel 2000. - СПб.: Питер, 2003.
  22.  Рычков В. С. Microsoft Excel 2000: краткий курс. - СПб.: Питер. 2000.
  23.  Санкин А.В,, Антонов В.Ф., Золотухина О.М. Практикум по информатике: Базовый курс. Учеб.- метод, пособ. для вузов. -Изд. ПГТУ, Пятигорск, 2002.
  24.  Фигурнов В.Э. IBM PC для пользователя. Краткий курс. -М.: ИНФА-М, 2000.
  25.  Хэлворсон М., Янг М. Эффективная работа с Microsoft Office 2000. — СПб: Питер, 2000.
  26.  Автоматизированные информационные технологии в экономике: Учебник/ Под ред. проф. Г.А.Титоренко. – М.: Компьютер, ЮНИТИ, 2002.
  27.  Информатика. Базовый курс/Симонович СВ. и др. - СПб.: Питер, 2001.
  28.  Информатика/Практикум по технологии работы на компьютере/Под ред. проф. Н. В. Макаровой. М.: Финансы и статистика, 2000.
  29.  Тимошок Т.В. Microsoft  Access  2002- Самоучитель. — М.: Издательский дом "Вильямс", 2003.


ВАРТАНОВА Э.Р.

ИНФОРМАЦИОННЫЕ СИСТЕМЫ В ЭКОНОМИКЕ

Лабораторный практикум

_________________________________________________

Подписано в печать 30.08.06

Формат 60х90/32. Бумага офсетная.

Печать офсетная.

Тираж 200 экз. Заказ № 2909

Отпечатано в типографии
ООО «Рекламно-информационное агентство на КМВ»

Пятигорск, ул. Февральская, 54, тел.(8793) 33-36-56, 39-09-03 (факс)

Относительная ссылка в формуле - это адрес ячейки, определяемый на основе расположения этой ячейки относительно ячейки, содержащей ссылку. При копировании ячейки относительная ссылка автоматически изменяется.


Абсолютная ссылка ячейки в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, и для использования абсолютных ссылок надо выбрать соответствующий параметр. В формуле абсолютная ссылка на ячейку имеет вид - $A$1.

2 Линии тренда позволяют графически отображать тенденции данных и прогнозировать их дальнейшие изменения. Подобный анализ называется также регрессионным анализом. Регрессионный анализ - форма статистического анализа, используемого для прогнозов. Регрессионный анализ позволяет оценить степень связи между переменными, предлагая механизм вычисления предполагаемого значения переменной из нескольких уже известных значений. Используя регрессионный анализ, можно продлить линию тренда в диаграмме за пределы реальных данных для предсказания будущих значений.


Настройки программы Microsoft Excel позволяют дополнить линию тренда значением квадрата смешанной корреляции - R в квадрате. Коэффициент R в квадрате отражает близость значений линии тренда к фактическим данным и представляет собой значение в пределах от 0 до 1. Линия тренда в наибольшей степени приближается к представленной на диаграмме зависимости, если
значение равно или близко к 1. При аппроксимации данных с помощью линии тренда значение R-квадрат рассчитывается автоматически. Полученный результат можно вывести на диаграмме.

 Режим Подбор параметра реализует алгоритм численного решения уравнения, зависящего от одной или нескольких переменных.


Процесс решения с помощью данного метода распадается на два этапа:


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


2. Ввод адресов влияющих и целевой ячеек в диалоговое окно Подбор параметра и получение ответа (или сообщения о его отсутствии/Невозможности найти).


Подбор параметра является частью блока задач, который используется тогда, когда желаемый результат известен, но неизвестны значения, которые требуется ввести для получения этого результата.
Подбор параметра - способ поиска определенного значения ячейки путем изменения значения в другой ячейке. При подборе параметра значение в ячейке изменяется до тех пор, пока формула, зависящая от этой ячейки, не вернет требуемый результат.

* Термин «возвращает» применительно к функциям рабочего листа электронных таблиц означает результат действия функции.


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

* Функция СУММЕСЛИ складывает цифры, если они отвечают определенным условиям. Структура функции такова:


СУММЕСЛИ(интервал;критерий;сумм_интервал).


Значения параметров функции следующие:


•   Интервал — это массив ячеек, по которому проверяется условие суммирования;


• Критерий — это условие суммирования в форме числа, выражения или текста, — например, критерий может быть выражен как 32, «32», «>32», «яблоки»;


• Сумм_интервал — это массив ячеек, параллельный массиву Интервал, по которому осуществляется суммирование; ячейки в сумм_интервал суммируются, только если соответствующие им ячейки в аргументе интервал удовлетворяют критерию.

* Для поиска информации в базе данных, прежде всего, следует сформировать запрос, по которому будет произведен поиск необходимых сведений.


Запрос – это специально подготовленный вопрос об информации в базе данных. Запросом может быть как простейший поиск информации по значению в какой-либо одной таблице, так и сложный процесс преобразования и представления в определенном виде взаимосвязанных между собой данных из нескольких таблиц.


Составляя запрос, можно указать в нем:


интересующие таблицы,


интересующие поля,


интересующие записи,


необходимые преобразования данных.    

EMBED Equation.3  

EMBED Equation.3  


 

А также другие работы, которые могут Вас заинтересовать

32379. Основные принципы, задачи и направления работы психологической службы в образовании 13.98 KB
  Цель психологической службы в образовании – максимальное содействие психическому и личностному развитию всех участников образовательного процесса. Задачи психологической службы в образовании: Выявление причин различного рода трудностей в учебновоспитательной работе с учащимися различного возраста психоаналитический подход Преодоление в рамках профессионализма отклонений в интеллектуальном и личностном развитии школьников коррекционный подход Преодоление и помощь в решении конфликтных ситуаций и сложных вопросов. Структура психологической...
32380. Понятие выбора профессии. Ошибки и трудности выбора профессии. Классификация профессий 17.89 KB
  Понятие выбора профессии. Ошибки и трудности выбора профессии. Выбор профессии – проф. Самоопределение которое подразумевает самостоятельный выбор профессии на основе: Объективных и достаточно полных знаний о себе своих интересах склонностях особенностях мышления памяти внимания нервной системы.
32381. Учет индивидуальных особенностей при обучении. Специфика обучения одаренных детей. Психологические причины неуспеваемости 14.22 KB
  Специфика обучения одаренных детей. Для поиска успешных форм и методов обучения необходимо учитывать индивидуальные особенности: нервной системы физиологические уровень и темп психического и физического развития. Программы обучения: Образовательные – ускоренное обучение обогащенное обучением углубленное и расширенное изучение отдельных тем Образовательноразвивающие – развитие продуктивного мышления и личности ребенка Эльконин Давыдов Развивающие – мышление личность развитие высших мыслительных процессов – творческого критического...
32382. Общие представления о памяти 14.29 KB
  Никакое психическое или внешнее действие или процесс невозможны без участия процессов памяти. Виды памяти: По характеру психической активности: Двигательная – запоминание сохранение и воспроизведение различных движений и их систем служит основой для навыков ходьбы письма спортивных навыков. Образная – память на представления Словестнологическая – память на мысли специфически человеческий вид памяти в отличие от других ей принадлежит ведущая роль в усвоении знаний.
32383. Психология как наука 14.95 KB
  Все так называемые движения души: эмоции чувства мышление мотивы и другие процессы возможно зафиксировать лишь через их внешние проявления. Индивидуальные психологические явления Индивидуальные психические процессы: познавательные процессы ощущения восприятие внимание память воображение мышление; эмоциональные процессы чувственный тон эмоции аффекты чувства настроение эмоциональный стресс; волевые процессы воля принятие решений преодоление трудностей борьба мотивов управление своим поведением.
32384. Ощущения 13.15 KB
  Каждое из этих свойств отражается разными органами чувств по сути – это разные виды ощущений Психический образ каждого из этих свойств первоначально возникает в разных отделах мозга. Свойства ощущений: Качество – это качественная характеристика ощущений позволяющая отличать одни ощущения от других и осознавать их своеобразие в пределах одного вида. Качество ощущений очень тесно связано с их модальностью. Интенсивность – это количественная характеристика ощущений зависящая от силы действующего раздражителя и от функционального состояния...
32385. Развитие психики в филогенезе. Основные этапы развития поведения и психики животных 13.93 KB
  Основные этапы развития поведения и психики животных. Развитие психики в филогенезе качественное изменения психики происходящее в рамках эволюционного развития живых существ обусловлены осложнением их взаимодействия с окружающей средой. Происхождение психики: Раздражимость – избирательная реакция на воздействие внешней среды.
32386. Восприятие 14.1 KB
  Различие процессов восприятия и ощущения заканчиваются в том что ощущение – это отражение отдельных свойств предметов и явлений а восприятие – целостное отражение предметов в единстве и во взаимосвязи их свойств. Свойства восприятия Предметность – это способность отражать предметы и явления реального мира в соответствии с их функциональным значением. Она тесно связана с целостностью восприятия. В результате их взаимодействия и достраивания посредством памяти и мышления приобретает структурную целостность это не сумма ощущений это...
32387. Естественн-научные основы психологии. Физиологические механизмы психики 14.55 KB
  Физиологические механизмы психики. Изучая отдельные факты психической жизни человека выявляется закономерности их развития раскрываются механизмы лежащие в основе. 4 этап психологии наука изучает факты закономерности механизмы психики. Физиологические механизмы психики.