Практическая работа №18

«Формулы в Excel»

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

Задание № 1. Торговая фирма имеет в своем ассортименте следующий товар: телевизоры стоимостью $300, видеомагнитофоны стоимостью $320, музыкальные центры стоимостью $550, видеокамеры стоимостью $700, видеоплееры стоимостью $198, аудиоплееры стоимостью $40. В январе было продано телевизоров — 10, видеомагнитофонов— 5, музыкальных центров — 6, видеокамер — 2, видеоплееров — 7, аудиоплееров — 4. Используя возможности Excel, найти сумму выручки от продаж в рублях и долларах.

Ход работы:

1. Создайте таблицу, внесите в нее исходные данные задачи.

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

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

4. Подсчитайте сумму выручки от продажи всех видов товаров.

Выделить столбец и нажать кнопку Автосумма на стандартной панели инструментов или установить курсор в последнюю ячейку столбца Е в строку «Итого сумма выручки» и воспользоваться кнопкой Вставка функции, расположенной также на стандартной панели, в окне Мастера функций следует выбрать СУММ из категории Математические.

Таблица 6.3

А

В

С

D

Е

F

G

1

Наименование продукции

Цена за ед.,

долл.

Продано,

шт.

Выручка от продажи, долл.

Выручка от продажи, руб.

Курс

долл.

2

Телевизоры

300

10

=C3*D3

=$E3*$G$3

27.1

3

Видеомагнитофоны

320

5

=С4 + D4

=$E4*$G$3

4

Музыкальные центры

550

6

=C5*D5

=$E5*$G$3

5

Видеокамеры

700

2

=С6 + D6

=$E6*$G$3

6

Видеоплееры

198

7

=C7*D7

=$E7*$G$3

7

Аудиоплееры

40

4

=С8 + D8

=$E8*$G$3

8

Итого сумма выручки

=СУММ(ЕЗ:Е8)

=CУMM(F3:F8)

Задание № 2.

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

2. Сопоставьте доходность акции по уровню дивидендов за 1999 г. по отдельным эмитентам. Исходные данные задачи представлены в табл. № 6.4:

(NA) — номинал акции;

(СР) — цена продажи;

(Div) — дивиденды, объявленные в расчете на год.

Таблица 6.4

Эмитент

Номинал

акции, руб.

Цена продажи,

руб.

Дивиденды, объявленные

в расчете на год

Доходность по дивидендам

NA

СР

% Div

руб. DivR

К номиналу

DN

Фактическая

DF

Сибирьгазбанк

10000

17780

400%

Инкомбанк

10000

22900

400%

Сургутнефтегазбанк

5000

5600

320%

Нефтехимбанк

1000

2015

653%

Сбербанк

1000

2482

736%

КБ Аккобанк

1000

1000

325%

СКБ банк

50000

27050

360%

Промстройбанк

1000

1200

1535%

3. Визуально проанализируйте полученные результаты.

Ход работы:

1. В соответствующие столбцы введите формулы для расчета выходных

показателей:

DivR(i) = NA(i)*Div(i);

DN(i) = Div(i);

DF(i) = DivR(i)/CP(i),

где i = [1,N], N— число рассматриваемых эмитентов.

2. На основании исходного документа «Доходность акций по отдельным дивидендам» рассчитайте следующие значения:

• средняя цена продажи акций по всем эмитентам (выделить столбец «Цена продажи» без заголовка, вызвать из стандартной панели Мастер функций > категория Статистическая > функция = СРЗНАЧ;

• максимальная цена продажи акций по всем эмитентам (выделить столбец «Цена продажи» без заголовка, вызвать из стандартной панели Мастер функций > категория Статистическая > функция = МАКС;

• минимальная цена продажи акций (выделить столбец «Цена продажи» без заголовка, вызвать из стандартной панели Мастер функций > категория Статистическая > функция = МИН;

• максимальная фактическая доходность акций по уровню дивидендов (выделить столбец «Фактическая доходность» без заголовка, вызвать Мастер функций > категория Статистическая > функция = МАКС;

• минимальная фактическая доходность акций по уровню дивидендов (выделить столбец «Фактическая доходность» без заголовка, вызвать Мастер функций > категория Статистическая > функция = МАКС.

3. Результаты расчетов оформите в виде табл. 6.5.

Таблица 6.5

Расчетная величина

Значение

Средняя цена продажи акций

Максимальная цена продажи акций

Минимальная цена продажи акций

Максимальная фактическая доходность акций

Минимальная фактическая доходность акций

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

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

Алгоритм фильтрации следующий:

• выделить данные таблицы с прилегающей одной строкой заголовка;

• выполнить команду Фильтр — Автофильтр меню Данные;

• в заголовке столбца «Фактическая доходность» нажать кнопку раскрывающегося списка и выбрать Условие;

• в окне пользовательского автофильтра задать условие >«среднее значение».

6. Результаты фильтрации поместите на новый рабочий лист,

включив в него следующие графы:

• эмитент;

• номинал акции;

• цена продажи;

• доходность по дивидендам фактическая.

7. Постройте на отдельном рабочем листе Excel круговую диаграмму, отражающую фактическую доходность по дивидендам каждого эмитента в виде соответствующего сектора (выделить столбцы «Эмитент» и «Фактическая доходность», выполнить команду меню Вставка > Диаграмма). На графике показать значения доходности, вывести легенду и название графика «Анализ фактической доходности акций по уровню дивидендов».

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

Алгоритм построения смешанного графика следующий:

• выделить столбцы «Эмитент», «Номинал акции» и «Цена продажи»;

• выполнить команду меню Вставка > Диаграмма > тип диаграммы Гистограмма;

• для добавления линейного графика «Фактическая доходность по дивидендам» правой клавишей мыши активизировать меню Диаграмма > Исходные данные -> во вкладке Ряд, выбрать кнопку Добавить, в поле Имя ввести название ряда «Доходность», в поле Значения ввести числовой интервал, соответствующий фактической доходности по дивидендам;

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