Практикум по информатике для студентов экономических специальностей

Практикум

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Наименование	Январь	февраль	март	апрель	Сумма	В проц. к итоговой сумме
Квартплата	123р.	123р.	138р.	138р.
Телефон	37р.	72р.	29р.	25р.
Электроэнергия	56р.	50р.	47р.	40р.
Транспорт	110р.	90р.	110р.	100р.
Прочие	50р.	150р.	100р.	200р.
Итого

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

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

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                

Рис. 24.  Подбор параметра      Рис. 25. Подбор параметра

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

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

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

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

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

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

6 курьеров;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В ячей­ке В2:

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

В ячейке С2:

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

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

 

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

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

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

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

МРОТ	300
Nп/п	Ф.И.О.	Должность	Разряд	Оклад	Премия	Удержано ПН	К выдаче
1	Кудилина	зам. Директора	7	6560
2	Легейда	продавец	3	3000
3	Малютина	бухгалтер	5	4000

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

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

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

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

МРОТ	300
Nп/п	Ф.И.О.	Должность	Разряд	Оклад	Премия	Удержано ПН	К выдаче
1	Коньков	Директор	6
2	Легина	продавец	3
3	Малютина	бухгалтер	5

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Задание18. Есть два варианта инвестирования средств в течение 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тыс.руб. - для второго варианта.

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

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

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

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

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

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

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

                                                     pv=fv/(1+r)ⁿ                                           

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

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

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

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

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

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

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

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

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

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

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

pv = ртt/(1+r) + ртt/(1+r)² +...+ pmt/(1+r)ⁿ =pmt(1-1/(1+r)ⁿ)/r

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Задание 24. Рассчитать, через сколько лет вклад размером 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 тыс. руб.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Задание 26. Предположим, что компании потребуется 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%,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 Исходные данные приведены в таблице на рисунке 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 значит, что количество произведенной продукции не может быть меньше нуля.  

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

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

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

Рис. 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. Сформированный портфель ценных бумаг

Задание 26. Создайте базу данных для отдела кадров, состоящую из двух взаимосвязанных таблиц. Первая таблица должна содержать сведения о сотрудниках, вторая – сведения о штатном расписании. Данные для первой таблицы приведены на рис 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. Таблица «Сведения о работниках» в режиме конструктора

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

Для того, чтобы изменить тип данных поля таблицы, наведите курсор на ячейку с этим типом данных, нажмите правую клавишу мыши, разверните список типов данных, путем нажатия на стрелку справа от ячейки и затем выберите необходимый тип данных (рис. 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.9. Маска ввода для поля ИНН

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Решение. Запустите программу Microsoft Access. На панели пиктограмм выберите Открыть. Откройте созданную ранее базу данных. В появившемся окне в Объектах выберите Запросы. Затем нажмите Создать. В появившемся окне Новый запрос выберите Конструктор и нажмите ОК. В окне Добавление таблицы поочередно выберите и добавьте обе созданные вами таблицы. Закройте окно Добавление таблицы.

В окне Запрос 1 появились выбранные таблицы. Теперь необходимо в нижней части окна указать интересующие поля. Для этого перетащите путем нажатия и удерживания правой клавиши мыши поле ИНН из любой таблицы, Фамилию, Адрес, Дата рождения, Должность, Разряд, Оклад. Дальше необходимо создать поля, в которых будет рассчитываться сумма премии, налогов и зарплата к выдаче. Для этого наведите курсор на ячейку справа от Оклада по строке поле, нажмите правой клавишей мыши, в открывшемся меню выберите ПОСТРОИТЬ. Открылось окно Построитель выражений (рис. 2.13).

Рис 2.13. Окно построения выражений

Премию рассчитайте по следующей методике: оклад умножьте на разряд и разделите на десять. Для этого откройте двойным нажатием левой клавиши мыши в нижнем левом окне папку Таблицы / Штат сотрудников. В среднем окне выберите Разряд и нажмите Вставить (можно быстро два раза нажать левой клавишей мыши на необходимом объекте). Затем кнопку «*», Оклад, «/», 100. ОК.

В следующей ячейке строки таким же образом введите формулу для расчета суммы налога равной 28% от суммы оклада.

Теперь необходимо переименовать поля с «Выражение1» на «Премия» и «Выражение2» на «Налог». Для этого подведите курсор к ячейке, содержащей Выражение1, нажмите правой клавишей мыши, выберите СВОЙСТВА, Формат поля – Основной. Закройте окно. Теперь удалите запись Выражение1 (до двоеточия) и введите Премия. Таким же образом измените Выражение2.

Вернитесь к полю Адрес и в строке Условие отбора укажите Пятигорск.

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

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

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

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

Что такое Запрос?

Чем отличается Запрос  от Таблицы?

Каким образом можно изменить условия запроса?

Задача 28. Создайте в базе данных Microsoft Acсess таблицу с именем «Штат сотрудников», имеющую вид:

Nп/п	Ф.И.О.	Должность	Разряд	Оклад
1	Аванесян	менеджер	5	7500
2	Алахвердова	экономист	5	7500
3	Бабаян	продавец	3	4500
4	Иванов	бухгалтер	4	5000
5	Петров	продавец	3	4500
	ИТОГО

Рис. 2.15.  Штат сотрудников.

Создайте запрос, содержащий Ф.И.О. и разряд сотрудников, у которых оклад выше среднего.

Задача 29.

Создайте базу данных, содержащую сведения о студентах, состоящую из двух таблиц (смотреть таблицы на рисунках 2.16, 2.17).

Создайте связь между таблицами. Определите тип связи, объясните, что повлияло на установление такого типа связи.

Создайте маску ввода для поля № зачетки (примерный номер зачетки – ОП-123456). 

Создайте список для поля факультет ( 3-4 факультета).

Самостоятельно заполните таблицы сведениями о десяти студентах.

Рис. 2.16. Личные сведения о студентах

Рис. 2.17. Сведения о группах

Задание 30. Заполните декларацию на доходы физических лиц за год, зная, что гражданин РФ Иванов:

q является владельцем акций двух компаний: «Лукойл» и «Юкос».  От первой предприниматель получил дивиденды на сумму 20000 руб., от второй - 10000 руб. Предприятия уплатили в бюджет суммы налога на прибыль, относящиеся к части прибыли, распределенной в виде дивидендов  в размере 24% от прибыли.

q в течение 2002 года дважды участвовал в соревнованиях по плаванию, проводимых по решению правительства и получил призы на сумму 680р. и 1000 р.

q 1 февраля приобрел в магазине Эльдорадо телевизор в кредит под 0% сроком на десять месяцев стоимостью в 20100 руб. При выплате суммы кредита налог не был начислен. Ставка рефинансирования на момент получения кредита равнялась 24% годовых.

q 22 апреля 2002 года выиграл в лотерею 2000 рублей.

q в мае 2002 года продал квартиру за 35 тыс. долларов (курс на момент продажи – 29,2 руб. за 1 доллар), которую приобрел 4 года назад.

q в июне посетил США, где читал лекции в Калифорнийском университете, за что в конце июля (31.07.2002, курс валюты – 30 руб. за 1 доллар) получил вознаграждение в размере 3000 долларов. Из них в США был удержан налог в размере 330 долларов. Ставка налога на доходы такого рода в России равна 13%.

q в августе отдыхал в Анталии, где 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)   Отдельно учитываются и облагаются налогом доходы, полученные в иностранной валюте от источников за пределами РФ. Такие доходы облагаются налогами по месту их получения. В РФ налогообложение доходов полученных за границей происходит по российскому законодательству с учетом суммы уплаченных налогов по данному доходу за границей. При этом производится пересчет суммы дохода и суммы уплаченного налога в рубли по курсу ЦБ на день получения дохода. К зачету принимается сумма налога, уплаченная в иностранной валюте за границей, если она меньше суммы налога подлежащей уплате по законодательству РФ, иначе к зачету принимается сумма налога, исчисленная в соответствие с российским законодательством.

---

ссылка на ячейку имеет вид - $A$1.

* Функция СУММЕСЛИ складывает цифры, если они отвечают опре­деленным условиям. Структура функции такова:

СУММЕСЛИ(интервал;критерий;сумм_интервал).

 Значения параметров функции следующие:

•   Интервал — это массив ячеек, по которому проверяется условие суммирова­ния;

• Критерий — это условие суммирования в форме числа, выражения или текста, — например, критерий может быть выражен как 32, «32», «>32», «яблоки»;

• Сумм_интервал — это массив ячеек, параллельный массиву Интервал, по которо­му осуществляется суммирование; ячейки в сумм_интервал суммируются, толь­ко если соответствующие им ячейки в аргументе интервал удовлетворяют кри­терию.