Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций Инвестиции

Тема 6. финансовые функции microsoft excel

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

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

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

Таблица 1

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

Аргумент Описание аргумента
бз бсБудущая стоимость фиксированных периодических выплат или единой суммы
Нз, пзначальное значение (текущая стоимость)
выплатафиксированная периодическая выплата
Кпер, Число периодовобщее число периодов выплат
Норманорма дисконтирования
ставкапроцентная ставка за период
ТипЧисло 0 или 1, обозначающее, когда производится выплата (1 — в начале периода. 0-в конце периода), по умолчанию равно 0.
предположениепредполагаемое значение процентной ставки, по умолчанию равно 0.1

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

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

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

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

fv=pv*(1 r)n

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

или

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

Примеры.

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

pv=fv/(1 r)n

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примеры.

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

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

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

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

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

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

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

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

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

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

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

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

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

Примеры

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примеры.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примеры.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примеры.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

§

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

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

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

Пример

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

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

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

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

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

Задача 1.

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

Пример

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

где 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,

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций ,

Wi >= 0,

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

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

Vp → min,

Rp <= Rb,

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций ,

Wi >= 0,

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестицийПремия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестицийРис 1.65. Сформированный портфель ценных бумаг

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

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

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций В математической постановке задачи оптимального планирования портфеля активов требуется найти вектор активов (А), максимизирующий линейную форму прибыли портфеля:

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

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

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

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

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций
где OwCp – собственный капитал компании,

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

N3 = А/L,

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

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

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

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

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

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

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

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

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

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

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

§

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

§

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

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

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

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

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

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

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

Про бизнес:  Дисконтированный срок окупаемости

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

§

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

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

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

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

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

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

Таблица 2

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

Знак Описание
Цифра (от 0 до 9, ввод обязателен; знаки плюс [ ] и минус [-] не допускаются).
Цифра или пробел (ввод не обязателен; знаки плюс и минус не допускаются).
# Цифра или пробел (ввод не обязателен; пустые знаки преобразуются в пробелы, допускаются знаки плюс и минус).
L Буква (от A до Z или от А до Я, ввод обязателен).
? Буква (от A до Z или от А до Я, ввод не обязателен).
A Буква или цифра (ввод обязателен).
a Буква или цифра (ввод необязателен).
& Любой знак или пробел (ввод обязателен).
C Любой знак или пробел (ввод необязателен).
. , : ; — / Десятичный разделитель и разделители тысяч, значений дат и времени. (Отображаемый знак зависит от настроек языка и стандартов на панели управления Microsoft Windows.)
< Указывает перевод всех следующих знаков на нижний регистр.
> Указывает перевод всех следующих знаков на верхний регистр.
! Указывает заполнение маски ввода справа налево, а не слева направо. Заполнение маски знаками всегда происходит слева направо. Восклицательный знак в маске ввода можно помещать в любую позицию.
Указывает ввод любого следующего знака в качестве текстовой константы. Используется для отображения всех перечисленных в данной таблице знаков как текстовых констант (например, A выводится как знак «A»).
Пароль Значение Пароль, заданное для свойства Маска ввода (InputMask), создает поле для ввода пароля. Любой знак, введенный в поле, сохраняется как знак, но отображается как звездочка (*).

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

§

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

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

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

Какими различными способами можно построить таблицу?

Какое поле может быть задано ключевым?

Что означает тип данных Счетчик?

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

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

ТЕМА 3. СОЗДАНИЕ ЗАПРОСОВ В MICROSOFT ACCESS

Цель работы: освоить основные приемы работы с базами данных на примере построения запросов* в приложении MICROSOFT ACCESS

Пример

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

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

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

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

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

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

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

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

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

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

Задача 1. В программе Microsoft Acсess создайте базу данных с именем «КАДРЫ», согласно данным в таблице на рис. 2.14. Для поля должность создайте список должностей. Создайте запрос для получения сведений о сотрудниках, проживающих в г. Москва.

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

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

Задача 3.

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

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

РАЗДЕЛ 3. СОЗДАНИЕ МАКРОСОВ

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

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

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

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

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

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

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

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

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

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

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

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

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

Примеры

Задание 1. Необходимо повысить зарплату сотрудникам фирмы, получающим 5600 руб. в месяц до 6050 руб. в месяц (рис 3.1).

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

Рис 3.1. Ведомость заработной платы сотрудников фирмы.

Автоматизируйте данную операцию с помощью макроса.

Решение. Создайте в Microsoft Excel таблицу по образцу, приведенному на рис. 3.1. Рядом с имеющейся таблицей введите число 5600. Теперь выберите в Меню – Сервис – Макрос — Начать запись. Появилось диалоговое окно. Запишите в Имени макроса – Замена_з/п. Присвойте макросу сочетание клавиш Ctrl q, в описании – повышение зарплаты с 5600 до 6050. ОК. В появившемся маленьком окошке нажмите Относительную ссылку Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций . Теперь в ячейку справа от «5600» введите функцию. Выделите ячейку, нажмите кнопку функций на панели инструментов, выберите функцию Если, и запишите в Логическое выражение адрес ячейки, содержащей число 5600 = 5600 (например С3=5600), в Значение если истина число 6050, Значение если ложь ту ячейку где написано 5600. ОК. Остановите запись макроса.

Возвращаемся к таблице. Необходимо изменить оклад. Для этого правее столбца Оклад вставьте пустой столбец. Выделите ячейку этого столбца и нажмите сочетание клавиш Ctrl q (убедитесь, что установлен английский язык). Появилось значение – скопируйте в остальные ячейки автозаполнением.

Задание 2. Предположим необходимо создать ряд отчетов, содержащих сведения о деятельности предпринимателя за год.

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

ИНН 263206946756

§

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

Решение. Откройте рабочее окно Microsoft Excel, начните запись макроса (СЕРВИС – МАКРОС – НАЧАТЬ ЗАПИСЬ), задайте макросу имя, укажите сочетание клавиш. ОК. После того, как запись макроса будет начата, вставьте две новые строки в самом верху страницы (два раза проделайте: ВСТАВКА – СТРОКИ), затем выделите три ячейки первой строки и объедините их в одну (нажмите кнопку Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций ). Введите текст ИНН 263206946756 (шрифт – 16 пт, полужирный). Затем объедините пять ячеек второй строки и введите текст: Частный предприниматель С.А. Иванов (шрифт – 16 пт, полужирный, подчеркнутый). Остановите запись. Для того, чтобы проверить работу макроса, откройте новый лист, нажмите Ctrl q (убедитесь, что включен английский язык).

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

Что такое макрос?

Для каких целей используют макросы?

На что необходимо обратить внимание при присвоении имени макросу?

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

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

Задача. Создайте макрос, позволяющий автоматизировать процедуру вставки следующих строк в рабочую область листа Microsoft Excel:

Юридический адрес: г. Пятигорск,

ул. Ермолова,47.

Магазин «Парус».

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

Задача 1

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

Задача 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 года численность наемных работников не изменялась. Оклад не изменялся.

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

При заполнении таблиц используйте формулы из столбца Наименование показателя.

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

Задача 3.

Задание. Проанализируйте ликвидность баланса предприятия «Чеширский маслозавод» за 2002, 2003, 2004, 2005 года. В балансе рассчитайте недостающие данные. Для проведения анализа ликвидности используйте таблицу Анализ ликвидности баланса, постройте аналогичные таблицы для анализа данных за 2003, 2004, 2005 годы. Постарайтесь максимально автоматизировать ввод и обработку данных.

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

ЗАДАНИЯ ДЛЯ КОНТРОЛЬНОЙ РАБОТЫ ДЛЯ СТУДЕНТОВ ЗАОЧНОЙ ФОРМЫ ОБУЧЕНИЯ

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

ВАРИАНТ 1

Задание № 1

1. Проанализируйте финансовую деятельность условной компании за 2003 г. по филиалам в среде EXCEL. Вид исходной таблицы показан на рис.1;

2. Рассчитайте сумму товарооборота фирмы по месяцам;

3. Рассчитайте товарооборот фирмы за год по филиалам;

4. Рассчитайте удельный вес деятельности каждого филиала в общем объеме товарооборота;

5. Присвойте ставку налога каждому филиалу, учитывая, что, если доход равен или меньше 15000000 рублей в год, то взимается налог в размере 10% от суммы товарооборота, если больше – ставка налога равна 24% от суммы прибыли (функция ЕСЛИ),

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

7. Рассчитайте сумму непредвиденных расходов, зная, что она равна 4% от суммы расходов на приобретение и реализацию продукции;

8. Определите прибыль без учета налогов;

9. Рассчитайте сумму налогов по филиалам и в целом по предприятию, учитывая, что 10 %-я ставка рассчитывается от суммы товарооборота, а 24% от суммы прибыли;

10. Рассчитайте сумму чистой прибыли предприятия за год;

11. Постройте круговую диаграмму для итоговой деятельности предприятия по филиалам (по столбцу «Товарооборот за год»).

Финансовая деятельность компании по филиалам
 
 
в 2003 г. (тыс.руб)
 
 
 
 
 
 
 
 
       
 
  Товарооборот за год Уд вес деят-ти ф-
ла в общем т/об-те
% ставка Расходы на прио-бретение и продажу продукции Непредвиденные расходы 4% Прибыль без учета налогов Сумма налога 
Филиал
 
Январь Февраль Март Апрель Май Июнь Июль Август Сентябрь Октябрь Ноябрь Декабрь 
                
                
                
                
              
              
              
Итого по предприятию
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

 

 
73505,6  
 
 
 
 
 
 
                  Чистая прибыль предприятия за год   

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

Задание №2

Рассчитайте, какая сумма окажется на счете, если вклад размером 50600 руб. размещен на банковском счете под 13,5% годовых на 4 года, а проценты начисляются каждые

· полгода,

· ежеквартально,

· ежемесячно

Задание №3

Рассчитайте, через сколько лет произойдет полное погашение займа размером 500000 р., если выплаты по 100 тыс.р. производятся в конце каждого квартала, а ставка равна 15% годовых. Сколько лет понадобиться для погашения этого займа, если выплаты по 150 тыс. р. производятся в конце каждого полугодия?

ВАРИАНТ 2

Задание №1.

Определите размеры периодических взносов в фонд размером 100 тыс.р., сформированный за 2 года ежеквартальными платежами, если процентная ставка равна 20% годовых. Какими должны быть размеры ежемесячных взносов?

Задание №2.

Есть два варианта инвестирования средств в течение 10 лет: в начале каж­дого года под 20% годовых или в конце каждого года под 30% годовых. Ежегодно вносит­ся 100 тыс. руб. Определите, сколько денег окажется на счете в конце 10-го года для каж­дого варианта.

Задание №3.

1. Создайте в EXCEL таблицу (рис. 2).

2. Определите коэффициент для расчета суммы оклада, учитывая что:

если разряд равен 1, то коэффициент будет иметь значение 3,

если разряд равен 2, то коэффициент будет иметь значение 4,

если разряд равен 3, то коэффициент будет иметь значение 5,

если разряд равен 4, то коэффициент будет иметь значение 6,

если разряд равен 5, то коэффициент будет иметь значение 7,

если разряд равен или больше 6, то коэффициент будет иметь значение 8 (функция ЕСЛИ).

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

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

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

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

ВАРИАНТ 3

Задание №1

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

Рис. 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 (Расширенный фильтр).

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

Задание №2

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

Задание №3

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

ВАРИАНТ 6

Задание №1

Создайте с помощью программы Microsoft Access базу данных, состоящую из двух взаимосвязанных таблиц (рис 6, 7), создайте маску ввода для поля ИНН. Постройте запрос по следующим условиям: должность – продавец, с датой рождения до 1980 года, проживающих в Пятигорске.

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

Задание №3

Предположим, вы хотите купить машину за 12000 $. Продавец предлагает два варианта: заплатить всю сумму сразу или выплачивать 350 $ ежемесячно в течение пяти лет со ставкой 17 % годовых.

Какой из предложенных вариантов более выгодный.

ВАРИАНТ 9

Задание №1

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

Доходность портфеля определяется по формуле

Rp=Rf (Rm-Rf)*Bp,

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

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

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

где 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,

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций ,

Wi >= 0,

где Vb – заданное инвестором ограничение риска портфеля в долях или процентах (не более 9,4%).

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

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

Задание №2

Определите товарооборот за год, рассчитайте сумму налога, которая равна 15% от суммы товарооборота. Определите чистую прибыль. Какой должна быть процентная ставка налога, чтобы чистая прибыль составила 65000000 руб. (Подбор параметров). Спрогнозируйте товарооборот предприятия за май 2004 года (Линия тренда). Исходные данные смотрите в таблице на рис. 11.

В тыс. руб. Товарооборот за 2003 год Сумма
налога
 
Филиал Январь Февраль Март Апрель Май Июнь Июль Август Сентябрь Октябрь Ноябрь Декабрь 
 
    
    
    
    
    
Итого                             

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

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

ВАРИАНТ 10

Задание №1

Составьте отчет о движении товаров за январь месяц по образцу.

Определите объем продаж предприятия, учитывая, что он зависит от количества проданных товаров двум покупателям: ЧП Шахян и ООО «Черкесск».

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

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

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

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

Премия «Рекорды Рынка Недвижимости 2021»: лучшие проекты для жизни и инвестиций

ВОПРОСЫ К ЭКЗАМЕНУ (ЗАЧЕТУ)

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.

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

Оцените статью
Бизнес Болика
Добавить комментарий