Создание сводных таблиц в ТП Excel

Данные о совершенных продажах в кредит представлены в таблице 1.

Таблица 1

Данные о совершенных продажах в кредит

Покупатели	Сумма продаж	Процент	Выручка
Корпорация А	73000.00	3,25
Корпорация В	37000.00	3,0
Корпорация С	33000.00	3,7

Ежегодный рост инфляции составляет в среднем 12% (т.е. 1% в месяц).

Необходимо:

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

Решить задачу с помощью табличного процессора Excel.  Осуществить графическую поддержку решения.

Построим в Excel таблицу и введем данные о совершенных продажах в кредит (рис.12).

 

Рис.12 Копия 1 экрана Excel

При заполнении таблицы, показанной на рисунке 12, учитываем форматирование ячеек (формат денежный в ячейках В2, В3, В4 и процент - С2, С3, С4, С6).

В столбец D в ячейки D2, D3 и D4 вводим формулы для расчёта выручки фирмы «К» по каждой корпорации:

Выручка = сумма продаж + сумма продаж*процент (D2=B2+D2*C2)

далее, используя автозапонение запоняем ячейки D3 и D4.

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

Строим круговую диаграмму структуры денежной выручки фирмы «К».

 

В ячейку D6 вносим формулу, которая учитывает, сколько денег из полученной выручки при продаже в кредит «пропало» из-за инфляции, а именно,

Размер месячной инфляции = суммарная выручка по кредиту*процент инфляции

Тогда в ячейке D7

Суммарная выручка с учётом инфляции= суммарная выручка по кредиту- размер месячной инфляции

Для определения критического уровня инфляции понадобится некоторый критерий оптимальности в качестве основы для принятия решения. Для упрощения будем полагать, что таким критерием является величина разности между итоговыми суммами продаж с учётом начисленных процентов и инфляции и без них. Эту разность =D7-B5 разместили в ячейке D8. Тогда, если эта разность равна нулю, то процент инфляции - критичный. А если в ячейке D8 будет отрицательное число, то условия кредитования покупателей необходимо менять.

В нашем случае, когда взят средний процент инфляции за месяц, чистая прибыль фирмы «К» от продажи своей продукции в кредит составила 3 226,47 рублей (рис. 14)

Рис.14 Копия 2 экрана Excel

Рис.15 Копия 3 экрана Excel в режиме отображения формул

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

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

Рис.16 Копия 4 экрана Excel

Рис.17 Копия 5 экрана Excel в режиме отображения формул

По получившейся таблице строим график зависимости рис.18

Рис.18 График для нахождения критического уровня инфляции

Из графика видно, что критический уровень инфляции составляет около 3,2%.

Попробуем определить вторым способом, применив технологии подбора параметра в Excel (Меню Данные – Анализ «что если» - Подбор параметра…). В появившемся диалоговом окне заполнили поля рис. 9.

 

Рис.19 Диалоговое окно для подбора критического уровня инфляции

 

После нажатия клавиши ОК в таблице появилось значение критического уровня инфляции

Рис.20 Копия 6 экрана Excel

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

Значит, сумма выручки фирмы «К» от продажи продукции в кредит с учетом приведенных выше условий составляет 146 226,47 руб.

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

1. Безручко, В. Т. Информатика (курс лекций) [Текст]: учебное пособие для вузов по дисциплине "Информатика" по гуманитарным и экономическим направлениям и специальностям / В. Т. Безручко – М.: Форум: ИНФРА-М, 2012 . – 432 с. – (Высшее образование) - ISBN 978-5-8199-0285-1
2. Волков, В.Б.  Понятный самоучитель Excel 2010 [Текст] / В.Б. Волков. — СПб: Питер, 2010. — 252 с. : ил. - ISBN: 978-5-49807-771-0
3. Леонтьев, В.П. Новейшая энциклопедия персонального компьютера 2011. Компьютерный справочник [Текст] / В.П. Леонтьев. — М. : ОЛМА Медиа Групп, 2010. — 960 с. : ил. ISBN: 978-5-373-03920-8
4. Свиридова, М.Ю. Электронные таблицы Excel [Текст] : учеб. пособие для начал. проф. образования / М.Ю. Свиридова. — 3-е изд., стер. — М. : Академия, 2009. — 144 с. - ISBN: 978-5-7695-6397-3
5. Викиучебник по табличному процессору Microsoft Excel [Электронный ресурс] — Режим доступа: http://ru.wikibooks.org/wiki/Microsoft_Excel, свободный. — Загл. с экрана.
6. Excel — это не сложно! [Электронный ресурс] — Режим доступа: http://www.excel-vba.ru, свободный. — Загл. с экрана.
7. Как создать сводную таблицу в Exsel 2010 [Электронный ресурс] — Режим доступа: http://advanceduser.ru/microsoft-excel/svodnuju-tablicu-excel.html, свободный. — Загл. с экрана.
8. Сводные таблицы Exsel 2010 [Электронный ресурс] — Режим доступа: http://pivot-table.ru/, свободный. — Загл. с экрана.

Вариант 73, Коурова Ю.В., № зачетной книжки 13973, группа 4