ТЕМА 6. ФИНАНСОВЫЕ ФУНКЦИИ MICROSOFT EXCEL — МегаЛекции

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

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

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

Пример

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

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

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

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

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

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

Воспользуйтесь поиском по сайту:

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

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

Запустите программу 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

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

Маска ввода будет иметь следующий вид: «2632 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» в «Зарплата». Закройте Конструктор запроса, сохранив изменения. Просмотрите запрос «Зарплата».

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

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

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

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

Воспользуйтесь поиском по сайту:

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

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

Макросу может быть присвоены кнопка на панели инструментов, пункт меню или сочетание клавиш.

Макросы часто используются для следующих целей:

· для ускорения часто выполняемых операций редактирования или форматирования;

· для объединения сложных команд; для упрощения доступа к параметрам в окнах диалога;

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

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

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

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

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

По окончании записи макроса необходимо нажать кнопку Остановить запись.

После присвоения макросу кнопки панели инструментов, команды меню или сочетания клавиш для его выполнения будет достаточно выбрать команду в меню, нажать кнопку на панели инструментов или нажать сочетание клавиш. При этом не теряется возможность выполнения макроса с помощью команды МАКРОС (меню СЕРВИС).

Примеры

Задание 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

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

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

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

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

Задача 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. Постройте круговую диаграмму для итоговой деятельности предприятия по филиалам (по столбцу «Товарооборот за год»).

Рис.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 (функция ЕСЛИ).

3. Определите оклад для каждого работника, если он равен произведению МРОТ (300), номера разряда и коэффициента.

4. Рассчитайте сумму премии, которая равна 26% от суммы оклада для каждого работника.

5. Определите отчисления в пенсионный фонд для каждого работника, зная, что, если общая сумма начисленной з/п (оклад премия) меньше или равна 20-кратному размеру МРОТ, то процент отчисления будет равен 12, если больше – 15%.

6. Рассчитайте сумму подоходного налога в размере 1% от общей суммы начисленной з/п.

7. Определите сумму з/п к выдаче.

8. Определите среднюю, максимальную и минимальную з/п по предприятию (с помощью соответствующих функций).

9. Определите каким должен быть процент премии, чтобы общая сумма з/п к выдаче была равна 300000 руб. (Подбор параметров).

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

ВАРИАНТ 3

Задание №1

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

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

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

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

Определите максимальный, минимальный и средний доход (по столбцу к выдаче) с помощью соответствующих функций.

Задание №2

На сберегательный счет ежемесячно вносятся платежи по 200 долларов. Рассчитайте, какая сумма окажется на счете через 4 года при ставке 20% годовых: 1) в начале каждого месяца, 2) в конце каждого месяца.

Задание №3.

Рассчитайте, какая сумма окажется на счете, если 55 тыс. руб. положены на 3 года под 15% годовых. Проценты начисляются каждые полгода.

ВАРИАНТ 4

Задание №1.

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

Рис. 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.

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

Рис. 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.

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

Воспользуйтесь поиском по сайту: