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

В книге ФИНАНСОВЫЙ АНАЛИЗ создайте новый лист (меню ВСТАВКА\ЛИСТ). Все упражнения данного раздела лабораторной работы выполняйте на Листе 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. Сохранить полученные результаты.