Порядок отчета лабораторной работы

Второй пример

Первый пример

В книге ФИНАНСОВЫЙ АНАЛИЗ создайте новый лист (меню ВСТАВКА\ЛИСТ). Все упражнения в данного раздела лабораторной работе выполняйте на Листе 8. Лист 8 переименуйте в «Отчетная ведомость по магазинам».

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

В ячейку Е4 введем формулу:

=СУММ(В4:D4),

которую с помощью маркера заполнения протащим на диапазон Е4:Е9. В ячейку В 10 введем формулу:

=СУММ(В4:В9),

которую протащим на диапазон В10:Е10. В ячейку G4 введем формулу:

=СРЗНАЧ(В4:D4),

которую протащим на диапазон G4:G9. В ячейку Н4 введем формулу:

=Е4/$Е$10,

которую протащим на диапазон Н4:Н9. После чего диапазону ячеек H4:H9 назначим формат с помощью кнопки на панели инструментов:

Рис. 8.3. Отчетная ведомость о работе сети магазинов за июнь – август.

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

Для ввода в формулу абсолютного адреса ячейки достаточно после ввода ее относительного адреса нажать клавишу <F4>. Если бы в ячейку Н4 была введена формула =Е4/Е10, то ее протаскивание на ячейки Н5:Н9 привело бы к неверному результату. Присвоение ячейке имени с помощью команды Вставка / Имя / Присвоить (Inset, Name, Define) дает другой способ абсолютной адресации ячейки. Например, если бы ячейке Е10 было присвоено имя итого, то в ячейку Н4 можно было бы ввести формулу:

=Е4/Итого

которую затем протаскиваем на диапазон Н4:Н9. Для нахождения места магазина по объему продаж введем в ячейку F4 формулу:

=РАНГ(Е4;$Е$4:$Е$9),

которую протаскиваем на диапазон F4:F9.

С помощью функции ЧАСТОТА (FREQUENCY) подсчитаем для данного множества суммарных выручек магазинов, сколько значений попадает в интервалы от 0 до 1000, от 1001 до 1100, от 1101 до 1200 и свыше 1201 млн. руб. С этой целью в диапазон ячеек 14:16 введем верхние границы этих интервалов: 1000, 1100 и 1200, соответственно, а в диапазон ячеек J4:J7 введем формулу:

{=ЧАСТОТА(Е4:E9;I4:I6)}

Фигурные скобочки не вводите вручную. После того как Вы наберете функцию нажмите одновременно три клавиши: Ctrl+Shift+Enter и скобки появяться автоматически. Если в последующем Вы решите внести изменения в функцию, то после обязательно, нажмите эти же клавиши, иначе у Вас появиться сообщение об ошибке.

Функция ЧАСТОТА возвращает распределение частот в виде вертикального массива. Для данного множества значений и данного множества карманов (интервалов, в математическом смысле) частотное распределение подсчитывает, сколько исходных значений попадает в каждый интервал.

Синтаксис: ЧАСТОТА(массив_данных; массив_карманов).

Частоты можно также вычислить, воспользовавшись диалоговым окном Анализ данных (Data Analysis), которое открывается командой Сервис / Анализ данных (Tools, Data Analysis). Средство анализа данных является одной из надстроек Excel.После выбора пункта Гистограмма (Histogram) в диалоговом окне Анализ данных (Data Analysis) откроется диалоговое окно Гистограмма (Histogram) (рис. 8.4).

Рис. 8.4. Диалоговое окно Гистограмма.

В поле Входной интервал (Input Range) введем диапазон Е4:Е9, покоторому строим гистограмму. В поле Интервал карманов (Bin Range) введем диапазон I4:I6 со значениями верхних границ интервалов. Гистограмма строится на новом рабочем листе или на текущем листе с указанием диапазона ячеек для результата. В данном случае в поле ввода Выходной интервал (Output Range) введем диапазон L4:L7. На рис. 8.5 приведен результат построения гистограммы.

Рис. 8.5. Результат построения гистограммы.

В книге ФИНАНСОВЫЙ АНАЛИЗ создайте новый лист (меню ВСТАВКА\ЛИСТ). Все упражнения данного раздела лабораторной работы выполняйте на Листе 9. Лист 9 переименуйте в «Итоговая выручка».

Рассмотрим еще один пример составления отчетной ведомости, в которой по объему реализованных товаров рассчитывается итоговая выручка (рис. 8.6).

Рис. 8.6. Расчет итоговой выручки по объему реализации.

В ячейки А22:С22 введены стоимости трех различных товаров, а в ячейки B25:D27 – объемы их реализации по месяцам. Для того чтобы вычислить суммарную стоимость реализованных товаров по месяцам,

введем в ячейки Е25:Е27 формулу:

{=МУМНОЖ(В25:D27;ТРАНСП(А22:С22)}

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

=СУММПРОИЗВ(В25:D25;$А$22:$С$22)

и протащить ее на диапазон Е25:Е27. Функция СУММПРОИЗВ (SUMPRODUCT)

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

При построении гистограммы (см. рис. 8.6) в поле ввода первого диалогового окна Мастер диаграмм (Cart Wizard) введите диапазоны

А25:А27; Е25:Е27. Напоминаем, что для одновременного выделения диапазонов, которые не примыкают друг к другу, сначала необходимо выделить первый диапазон, а потом при нажатой клавише <Ctrl> – второй.

8.1.2. Пример отчетной ведомости по расчету

просроченных платежей

В книге ФИНАНСОВЫЙ АНАЛИЗ создайте новый лист (меню ВСТАВКА\ЛИСТ). Все упражнения данного раздела лабораторной работы выполняйте на Листе 10. Лист 10 переименуйте в «Расчет просроченных платежей».

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

Рис. 8.7. Расчет просроченных платежей.

Дата переучета введена в ячейку F2 с помощью формулы:

=ДАТА(98;7;31)

Функция ДАТА (DATE) возвращает дату в числовом формате.

Синтаксис: ДАТА(год; месяц; день).

Аргументы: год – число от 1900 до 2078; месяц – число, представляющее номер месяца в году. Если оно больше 12, то прибавляется к первому месяцу указанного года. Например, ДАТА (96; 14;2) возвращает числовой формат даты 2 февраля 1997 года; день – число, представляющее номер дня в месяце. Если оно больше числа дней в указанном месяце, то

прибавляется к первому дню указанного месяца

В ячейку Е2 введена формула, определяющая срок просрочки:

=ЕСЛИ(D2=0;$F$2-С2;""),

которая протаскивается на диапазон ЕЗ:Е20. В ячейки G8, G9 и G10 введены следующие формулы:

(=СУММ((Е2:Е20>0)*(Е2:Е20<=29)*(В2:В20))}

{=СУММ((Е2:Е20>=30)*(Е2:Е20<=39)*(В2:В20))}

{=СУММ((Е2:Е20>=40)*(В2:В20))},

вычисляющие суммарные стоимости просроченных оплат сроком до 29 дней, от 30 до 39 дней и свыше 40 дней.

Дадим пояснения к третьей из этих формул. Excel в формуле массива возвращает условие (Е2:Е20>=40) в виде массива, состоящего из 0 и 1, где 0 стоит на месте ячейки со значением меньше 40 и 1 – на месте ячейки со значением не меньше 40. Следовательно, данная формула вычисляет сумму произведений элементов массива (Е2:Е20>=40) (с единицами в случае просрочки на указанный срок и нулями – в противном случае) и массива В2:В20 (с ценами процессоров). Таким образом, третья формула возвращает суммарную стоимость заказов, просроченных не менее чем на 40 дней.

В ячейки G2, G3 и G4 введены формулы:

{=СУММ((Е2:Е20>0)*(Е2:Е20<=29))}

{=СУММ((Е2:Е20>=30)*(Е2:Е20<40))}

=СЧЁТЕСЛИ(Е2:Е20;">=40"),

вычисляющие количество просроченных оплат сроком до 29 дней, от 30 до 39 дней и свыше 40 дней.

Функция СЧЁТЕСЛИ (COUNTIF) возвращает количество ячеек внутри указанного интервала, удовлетворяющих заданному критерию.

Синтаксис: СЧЁТЕСЛИ(интервал; критерий).

8.1.3. Пример отчетной ведомости по расчету затрат на производство

В книге ФИНАНСОВЫЙ АНАЛИЗ создайте новый лист (меню ВСТАВКА\ЛИСТ). Все упражнения данного раздела лабораторной работе выполняйте на Листе 11. Лист 11 переименуйте в «Затраты на производство».

Рассмотрим пример составления отчетной ведомости по расчету затрат на производство товара (рис. 8.8).

Рис. 8.8. Расчет затрат на производство товара.

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

Рис. 8.9. Ввод имени в ячейку из диалогового окна Вставка имени.

8.2. Индивидуальное задание

Номер варианта определяется согласно списку группы в файле с рейтингом, если у Вас номер более 10, то от номера отнимаете число 10 и получаете номер своего варианта (табл. 9).

При выполнении индивидуального задания Вам необходимо:

1. Внимательно прочитать условие задачи.

2. Открыть книгу Задания.xls.

3. Создать новый лист.

4. Появившийся Лист 7 переименуйте в «Отчетные ведомости».

5. Сохранить полученные результаты.

Задание

Составить отчетную ведомость реализации товаров п магазинами с месяца А по месяц В, приведенную на рис. 8.8.

Таблица 9

Вариант А В п

1 май декабрь 3

2 июнь январь 4

3 июль октябрь 5

4 август январь 6

5 сентябрь декабрь 7

6 октябрь март 8

7 ноябрь март 9

8 декабрь июль 10

9 январь мюль 4

10 февраль август 5

В качестве стоимостей товаров введите произвольные трехзначные числа, а в качестве объемов их реализации – произвольные двузначные числа.

При отчете лабораторной работы необходимо:

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

2. Продемонстрировать выполненное индивидуальное задание, прокомментировать порядок его выполнения и объяснить полученные результаты.

3. Ответить на контрольные вопросы.

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

1. Как создать список?

2. Что находит функция СРЗНАЧ?

3. Что возвращает функция РАНГ?

4. Как присвоить имя ячейкам?

5. Что можно вычислить с помощью функции ЧАСТОТА?

6. Как установить команду Анализ данных?

7. Что возвращает функция ДАТА?

8. Как строятся диаграммы в Microsoft Excel?

9. Как осуществляются операции копирования и переноса в Microsoft Excel?

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

1. Гарнаев А. Ю. Использование MS Excel и VBA в экономике и финансах. – СПб.:

БХВ – Санкт-Петербург, 1999. – 336 с.

2. Информатика. Серия «Учебники, учебные пособия». И 57 // Под ред. П. П. Беленького. – Ростов н/Д.: Феникс, 2002. – 448 с.

ЛАБОРАТОРНАЯ РАБОТА № 9

ПЛАНИРОВАНИЕ РЕКЛАМНОЙ КАМПАНИИ

Цель лабораторной работы:

Научиться использовать средства табличного процессора Microsoft Excel для решения финансовой задачи.

Дата добавления: 2017-02-01