Задача, решаемая с использованием пакета MSExcel

Введите информацию в две таблицы, имеющие следующие поля:

Таблица 1 (30-40 строк) · Наименование товара (не более 5-7 различных) · Дата продажи · Стоимость закупки · Стоимость продажи · Дилер · Изготовитель

Таблица 2 (7-10 строк) · Дилер · Процент оплаты

Выполните следующие задания:

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

2. Рассчитайте величину налога (для российских товаров 10%, а для импортных 18%). Вычислите чистый доход как разницу между валовым доходом, оплатой дилеру и налогом.

3. Создайте отчет о доходе, полученному фирмой, по каждому дилеру

4. Сколько в среднем зарабатывает указанный Вами дилер на продаже товаров заданного изготовителя?

5. Что было продано в заданный календарный период?

6. Постройте диаграмму распределения итоговых выплат по дилерам.

7. Постройте диаграмму распределения доходов по товарам и месяцам года.

Отчет о решении задачи с использованием пакета MSExcel

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

Справочная таблица имеет вид:

После щелчка по ярлыку листа в контекстном меню выбран пункт «Переименовать». Лист, содержащий основную таблицу, переименован в «Сделки», а справочный – в «Спр».

1. Для расчета валового дохода (ячейка G4) использована формула

=D4-C4

Для расчета стоимости услуг дилера (ячейка H4) использована формула

= G4*ВПР(E4;Спр!$B$3:$C$6;2;0).

2. Для расчета величины налога (ячейка I4) использована формула

=G4*ЕСЛИ(F3="Россия";10%;18%).

Для расчета чистого дохода (ячейка J 4) использована формула

=G4-H4-I4

Формулы ячеек G4:J4 распространены на все строки таблицы с помощью циклического копирования

3. Создайте отчет о доходе, полученному фирмой, по каждому дилеру

Для расчета суммарного дохода по каждому дилеру На листе Спр! использована формула: =СУММЕСЛИ(сделки!$E$4:$E$29; Спр!B4;сделки!$J$4:$J$29)

Итоговое значение (ячейка С6) рассчитанапо формуле=СУММ(С4:С7).

4. Сколько в среднем зарабатывает указанный Вами дилер на продаже товаров заданного изготовителя?

В ячейки L5:M6 записан критерий отбора. Далее применена формула: =БДСУММ(E3:H29;H3;L5:M6)

5.Что было продано в заданный календарный период?

Данные о товарах и датах скопированы в новый диапазон (для наглядности), применен фильтр по условию (с 1.05.12 по 31.07.12).

6. Постройте диаграмму распределения максимальных выплат по дилерам.

На листе «Спр»» с помощью функции СУММЕСЛИ в ячейке Е4:

СУММЕСЛИ(Сделки!E$4:E$100; Спр!F4; Сделки!H$4:H$100) с последующим копированием формулы для остальных дилеров. Результат будет иметь вид:

Выделить диапазоны A1: A5: E1:E5 (через Ctrl), Вставка → Круговая диаграмма.

Двойной щелчок по сектору – в контекстном меню «Формат точки данных» → изменить заливку. Работа с диаграммами → Макет → Подписи данных→ Расположение и параметры.