Список товаров на складе
|
Наименование
|
Цена
|
Кол-во
|
Стоимость
|
Стоимость с наценкой, 15%
|
стеллаж
|
1 200,00р.
|
5шт
|
|
|
стол рабочий
|
900,00р.
|
4шт
|
|
|
шкаф офисный
|
1 650,00р.
|
9шт
|
|
|
кресло рабочее
|
725,00р.
|
12шт
|
|
|
Итого
|
|
|
|
|
наценка
|
15,00%
|
|
макс. цена
|
|
|
мин.цена
|
|
|
Средняя стоимость с наценкой
|
|
|
|
|
Для создания
собственного пользовательского формата необходимо: Формат Ячейки - вкладка
Число - раздел Числовые форматы - выбрать Все форматы. В окошке Тип вводим собственный
шаблон 0шт. В текущей клетке числовые данные будут отображаться целыми числами
и с текстом шт.
Стоимость товара рассчитаем путем
произведения Цены товара на Количество. Стоимость с наценкой рассчитывается как
произведение Стоимости на Наценку.
Для определения максимальной и
минимальной цены товара использовали функции МАКС и МИН соответственно. В
аргументы этих функций записывается диапазон ячеек, из которых выбирается
максимальное или минимальное значения.
Среднюю стоимость с наценкой
определили с помощью функции СРЗНАЧ, в аргументы которой записали диапазон
ячеек.
Для закрашивания ячеек воспользуемся
Условным форматированием. В полях задаем значения, которые будут закрашиваться.
Если стоимость с наценкой больше 10000 руб. - ячейку залить красным цветом.
Формулы расчета представлены на рисунке 1.
Рисунок 1 - Формулы для расчета
параметров
Результат выполнения представлен на
рисунке 2.
Рисунок 2 - Результат выполнения
задачи
Задача 2
В таблице имеются
данные о деятельности предприятия за четыре квартала прошлого года. Заполните
следующую таблицу: исходные данные (черный шрифт) - квартал, цена, выпуск
продукции, постоянные и переменные расходы; остальные данные (бледно-серый
шрифт) вычислить, используя ниже представленные формулы; использовать функции
МАКС, МИН; применить процентный формат. Выделить ячейку с максимальной прибыли,
используя условное форматирование. Построить три диаграммы, отражающие:
а) Себестоимость
на единицу продукции (гистограмму);
б) Выручку от
реализации (круговую);
в) Динамику
общей прибыли или убытка (график).
Взаимосвязь между
показателями, представленными в таблице можно отразить следующими формулами:
ВР = Ц * ОР,
где ВР - выручка от
реализации, руб.
Ц - цена единицы
продукции, руб./шт.
ОР - объем выпуска
и реализации продукции, шт.
Ус/с = Упост +
Упер,
где Ус/с -
себестоимость единицы продукции (удельная себестоимость), руб./шт.
Упост - постоянные
расходы на единицу продукции (удельные постоянные расходы), руб./шт.
Упер - переменные
расходы на единицу продукции (удельные переменные расходы), руб./шт.
Пост = Упост*ОР,
где Пост - общие
постоянные расходы, руб.
Перем = Упер*ОР,
где Перем - общие
переменные расходы, руб.
С = Пост + Перем,
где С - общая
себестоимость продукции, руб.
Упр = Ц - У с/с,
где Упр - прибыль
на единицу продукции (удельная прибыль), руб.
Пр = ВР - С или Пр
= Упр*ОР,
где Пр - прибыль
(убыток), руб.
Решение
Формулы для расчета
параметров представлены на рисунке 3.
Рисунок 3 - Формулы для расчета
параметров
Таблица с рассчитанными данными
представлена на рисунке 4.
Рисунок 4 - Рассчитанные значения
таблицы
а) Себестоимость
на единицу продукции (гистограмма);
Рисунок 5 -
Себестоимость на единицу продукции
Выручка от
реализации (круговая);
Рисунок 6 - Выручка
от реализации продукции
в) Динамика
общей прибыли или убытка (график).
Рисунок 7 -
Динамика общей прибыли
Задача
3
Рассчитать
заработную плату. Столбец Разряд вычислить при помощи функции ВПР, Начислено
вычислить, используя функцию ВПР и ЕСЛИ (Если отработано больше 40 часов, то
Оплату увеличить на 60%). Применить условное форматирование для выделения ячеек
с максимальной и минимальной заработной платой. Применить денежный формат
ячеек, функцию МАКС, МИН, СРЗНАЧ.
Решение
Формулы для расчета
заработной платы представлены на рисунке 8.
Рисунок 8 - Формулы
для расчета заработной платы
Полученный
результат представлен на рисунке 9.
Рисунок 9 - Расчет
заработной платы
Задача
4
Сформировать
следующую таблицу:
ФИО
|
Проданный товар
|
Выручка
|
Дата продажи
|
Иванов
|
принтер
|
6 700.00р.
|
28.05.2006
|
Петров
|
компьютер
|
10 000.00р.
|
28.05.2006
|
Иванов
|
принтер
|
6 700,00р.
|
24.04.2006
|
Барыбин
|
компьютер
|
12 000.00р.
|
24.04.2006
|
Николаев
|
клавиатура
|
300.00р.
|
24.04.2006
|
Иванов
|
сканер
|
3 500,00р.
|
12.05.2006
|
Николаев
|
клавиатура
|
350.00р.
|
12.05.2006
|
Петров
|
сканер
|
2 300.00р.
|
12.05.2006
|
Сидоров
|
компьютер
|
12 000:00р.
|
15.05.2006
|
Барыбин
|
компьютер
|
11 300:00р.
|
15.05.2006
|
Выполнить:
a)Отсортировать таблицу по полю ФИО, ПРОДАННЫЙ ТОВАР (по
возрастанию).
b)Отобрать товар, проданный в мае (пункт Условие), используя
автофильтр.
c)Отобрать товар, от продажи которого выручка превышает 10000 руб.
d)Сформировать 3 промежуточных итога в одной таблице: количество
проданного товара по каждому продавцу, суммарную выручку и максимальную выручку
каждого продавца.
e)Сформировать сводную таблицу, отражающую сумму выручки по каждой
дате и по виду товара; на основе сводной таблицы создать сводную диаграмму.
Решение
а) Выбираем все
данные таблицы. Нажимаем Данные - Сортировка, в появившемся окне выбираем
данные, по которым хотим провести сортировку (рисунок 10).
Рисунок 10 - Диалоговое окно
Сортировка диапазона
Нажимаем ОК и получаем
отсортированную таблицу 2.
Таблица 2 - Отсортированная таблица
ФИО
|
Проданный товар
|
Выручка
|
Дата продажи
|
Барыбин
|
компьютер
|
12 000.00р.
|
24.04.2006
|
Барыбин
|
компьютер
|
11 300:00р.
|
15.05.2006
|
Иванов
|
принтер
|
6 700.00р.
|
28.05.2006
|
Иванов
|
принтер
|
6 700,00р.
|
24.04.2006
|
Иванов
|
сканер
|
3 500,00р.
|
12.05.2006
|
Николаев
|
клавиатура
|
300.00р.
|
24.04.2006
|
Николаев
|
клавиатура
|
350.00р.
|
12.05.2006
|
Петров
|
компьютер
|
10 000.00р.
|
28.05.2006
|
Петров
|
сканер
|
2 300.00р.
|
12.05.2006
|
Сидоров
|
компьютер
|
12 000:00р.
|
15.05.2006
|
b) Для отбора товара проданного в мае воспользуемся
Автофильтрацией, в которой в ячейке «Дата» выбираем «Условие».
Рисунок 11 - Условие отбора
Результат представлен в таблице 3.
Таблицы 3 - Товар проданный в мае
ФИО
|
Проданный товар
|
Выручка
|
Дата продажи
|
Барыбин
|
компьютер
|
11 300:00р.
|
15.05.2006
|
Иванов
|
принтер
|
6 700.00р.
|
28.05.2006
|
Иванов
|
сканер
|
3 500,00р.
|
12.05.2006
|
Николаев
|
клавиатура
|
350.00р.
|
12.05.2006
|
Петров
|
компьютер
|
10 000.00р.
|
28.05.2006
|
Петров
|
сканер
|
2 300.00р.
|
12.05.2006
|
Сидоров
|
компьютер
|
12 000:00р.
|
15.05.2006
|
с) Для отбора товара, от продажи
которого выручка превышает 10000 руб. также воспользуемся Автофильтрацией.
Выбираем поле Выручка и проставляем условие отбора, которое представлено на
рисунке 12.
Рисунок 12 - Параметры условия
отбора
Результат фильтрации представлен в
таблице 4.
Таблица 4 - Отбор выручки
превышающей 10000 р.
ФИО
|
Проданный товар
|
Выручка
|
Дата продажи
|
Барыбин
|
компьютер
|
12 000,00р.
|
24.04.2006
|
Барыбин
|
компьютер
|
11 300,00р.
|
15.05.2006
|
Петров
|
компьютер
|
10 000,00р.
|
28.05.2006
|
Сидоров
|
компьютер
|
12 000,00р.
|
15.05.2006
|
d) Используем функцию ИТОГИ из меню ДАННЫЕ.
Для создания промежуточного итога по
количеству проданного товара по каждому продавцу диалоговое окно будет
выглядеть следующим образом (рисунок 13).
Рисунок 13 - Диалоговое окно для
промежуточного итога по количеству проданного товара по каждому продавцу
Для определения промежуточных итогов
по суммарной выручке и максимальной выручке каждого продавца в диалоговом окне
Промежуточных итогов, в строке Операции выбираем Сумма и Максимум
соответственно. Результат представлен в таблице 5.
Таблица 5 - Расчет промежуточных
итогов
ФИО
|
Проданный товар
|
Выручка
|
Дата продажи
|
Барыбин
|
компьютер
|
12 000,00р.
|
24.04.2006
|
Барыбин
|
компьютер
|
11 300,00р.
|
15.05.2006
|
Барыбин Количество
|
2
|
|
|
Барыбин Итог
|
|
23 300,00р.
|
|
Барыбин Максимум
|
|
12 000,00р.
|
|
Иванов
|
принтер
|
6 700,00р.
|
28.05.2006
|
Иванов
|
принтер
|
6 700,00р.
|
24.04.2006
|
Иванов
|
сканер
|
3 500,00р.
|
12.05.2006
|
Иванов Количество
|
3
|
|
|
Иванов Итог
|
|
16 900,00р.
|
|
Иванов Максимум
|
|
6 700,00р.
|
|
Николаев
|
клавиатура
|
300,00р.
|
24.04.2006
|
Николаев
|
клавиатура
|
350,00р.
|
12.05.2006
|
Николаев Количество
|
2
|
|
|
Николаев Итог
|
|
650,00р.
|
|
Николаев Максимум
|
|
350,00р.
|
|
Петров
|
компьютер
|
10 000,00р.
|
Петров
|
сканер
|
2 300,00р.
|
12.05.2006
|
Петров Количество
|
2
|
|
|
Петров Итог
|
|
12 300,00р.
|
|
Петров Максимум
|
|
10 000,00р.
|
|
Сидоров
|
компьютер
|
12 000,00р.
|
15.05.2006
|
Сидоров Количество
|
1
|
|
|
Сидоров Итог
|
|
12 000,00р.
|
|
Сидоров Максимум
|
|
12 000,00р.
|
|
Общее количество
|
10
|
|
|
Общий итог
|
|
65 150,00р.
|
|
Общий максимум
|
|
12 000,00р.
|
|
е) Сформируем сводную таблицу,
отражающую сумму выручки по каждой дате и по виду товара; на основе сводной
таблицы создадим сводную диаграмму.
Создадим сводную таблицу с помощью
функции Сводная таблица меню Данные. Последовательность шагов представлена на
рисунках 14 - 16.
Рисунок 14 - Первое окно мастера
сводных таблиц
Затем указываем диапазон, содержащий
исходные данные.
Рисунок 15 - Второе окно мастера
Сводных таблиц
В третьем окне выбираем
месторасположение сводной таблицы.
Рисунок 16 - Третье окно мастера
Сводных таблиц
В появившейся сводной таблице в
строки перемещаем Проданные товары, в столбцы - Дата, в область значений -
Сумма выручки. Сводная таблица представлена на рисунке 17.
|
|
|
|
|
|
Сумма по полю Выручка
|
Дата продажи
|
|
|
|
|
Проданный товар
|
24.04.2006
|
12.05.2006
|
15.05.2006
|
28.05.2006
|
Общий итог
|
клавиатура
|
300
|
350
|
|
|
650
|
компьютер
|
12000
|
|
23300
|
10000
|
45300
|
принтер
|
6700
|
|
|
6700
|
13400
|
сканер
|
|
5800
|
|
|
5800
|
Общий итог
|
19000
|
6150
|
23300
|
16700
|
65150
|
Рисунок 17 - Сводная таблица
На рисунке 18 показана диаграмма к
сводной таблице.
Рисунок 18 - Диаграмма к сводной
таблице
Задача
5
себестоимость процентный прибыль
выручка
а) Кредит в размере
8 500 000 руб. берется на 30 лет с максимальными ежемесячными платежами 52500
руб. На какую максимальную процентную ставку можно согласиться при таких
условиях. Определить, используя средство Подбор параметра. Ответ для
самопроверки: 6,23%
b) За какой срок в годах сумма, равная 75000 долл., достигнет 200
000 долл. при начислении 15% один раз в год. (Используется функция БС или ПЛТ и
средство Подбор параметра). Ответ для самопроверки: 7 лет.
Решение
а) Для определения
процентной ставки используем Встроенную функцию ПЛТ(Проценты; Срок; Размер
ссуды). И используем Подбор параметра. Вызываем встроенную функцию Подбор
параметра (рисунок 19)
Рисунок 19 - Параметры Подбора
параметра
Решение задачи представлено на
рисунке 20.
Рисунок 20 - Определение срока
погашения кредита
b) Для решения задачи воспользуемся встроенной функцией БС. В
ячейку записываем параметры функции БС(Процентная ставка (0,15); Срок (F3);; Размер ссуды (-25000)).
Решение
представлено на рисунке 21
Рисунок 21 -
Решение задачи
Задача
6
Создайте таблицу
подстановки с одним и с двумя входами для подсчета дивидендов. Исходная
таблица.
|
А
|
В
|
1
|
Цена акции
|
750,00р.
|
2
|
Количество акций
|
1250
|
3
|
Процентная ставка
|
1,32%
|
4
|
Сумма дивидендов
|
12 375,00р.
|
Здесь в ячейках
содержатся следующие начальные данные: в ячейке В1 -цена акции; В2 - количество
акций; ВЗ - процентная ставка; В4 - сумма дивидендов, которая рассчитывается по
формуле: = В1*В2*В3.
а) Представьте в
виде таблицы суммы дивидендов, если количество акций изменяется от 25000 до
300000 с шагом 25000.
b) Представьте в виде таблицы суммы дивидендов, если количество
акций изменяется от 25000 до 300000 с шагом 25000, а цена одной акции от 750 до
1000 с шагом 50 р.
Ответ для
самопроверки: а) при количестве 300 000 акций сумма дивидендов составит 2970000
р.; b)при количестве 300 000 акций и цене одной акции в 1000 р сумма дивидендов
составит 3960000 р.
Решение
Вводим число 25000
в ячейку В6, и выделяем диапазон ячеек В6:В17.
Выбираем команду
Правка > Заполнить > Прогрессия. Откроется диалоговое окно
Прогрессия.
В этом окне в поле
Шаг введите значение 1, щелкните на ОК. В диапазоне А5:А15 будет создана
последовательность входных значений (числа от 25000 до 300000 с шагом 25000).
Выделим диапазон
ячеек В5:С17, в котором содержаться и диапазон значений и формула.
Выбираем команду
Данные -> Таблица подстановки. Откроется диалоговое окно Таблица
подстановки.
В этом окне в поле
ввода Подставлять значения по строкам в вводим С3. Щелкаем ОК.
Выбираем команду
Формат ->Ячейки. Откроется диалоговое окно Формат ячеек. Щелкаем на вкладке
Число. Выбираем - Финансовый. Щелкаем ОК. Таблица подстановки с одним входом
представлена на рисунке 22.
Рисунок 22 - Таблица подстановки с
одним входом
b) Алгоритм ввода данных по количеству акций аналогичен предыдущему
примеру.
В ячейку D5 вводим число 750 и выделяем
диапазон ячеек D5:I5.
Выбираем команду
Правка > 3аполнить > Прогрессия. Откроется диалоговое окно
Прогрессия.
В этом окне в поле
Шаг вводим значение 50, щелкаем на ОК. В диапазоне D5:I5 появилась последовательность
входных, значений (числа от 750 р. до 1000 р. с шагом 50 тыс.р.).
Выделяем диапазон
ячеек B5:I17 и выбираем команду Данные >Таблица подстановки. Открывается
диалоговое окно Таблица подстановки.
В этом окне в поле
ввода Подставлять значения по строкам в вводим С3, а в поле ввода Подставлять
значения по столбцам в вводим С2. Нажимаем ОК.
Таблица подстановки с двумя входами
представлена на рисунке 23.
Рисунок 23 - Таблица подстановки с
двумя входами
Задача
7
Покупатели магазина
пользуются 10% скидками, если покупка состоит более, чем из 5 наименований
товаров или стоимость покупки превышает k рублей. Составить ведомость,
учитывающую скидки: покупатель (не менее 15 человек), количество наименований
купленных товаров, стоимость покупки, стоимость покупки с учетом скидки.
Выяснить, сколько покупателей сделало покупок, стоимость которых превышает k
рублей.
Решение
Ведомость
покупателей представлена на рисунке 24.
Рисунок 24 -
Ведомость покупателей
Стоимость товаров
со скидкой определили с помощью функции ЕСЛИ, аргументы которой представлены на
рисунке 25. Если количество покупок больше 4, то стоимость товара умножаем на
0,9 (минус 10%), в противном случае, стоимость остается неизменной.
Рисунок 25 - Аргументы функции ЕСЛИ
Количество покупателей сделавших
покупки на сумму более 50 р. определили при помощи функции СЧЕТЕСЛИ, аргументы
которой представлены на рисунке 26. В диапазоне стоимости товаров подсчитываем
ячейки со стоимостью более 49 р.
Рисунок 26 - Аргументы функции
СЧЕТЕСЛИ
Список использованной
литературы
1. Кутузов А.Л.
Математические методы и модели исследования операций. Линейная оптимизация с
помощью WinQSB и Exel: Учеб. Пособие. СПб.: Изд - во Политехн. ун - та, 2007. -
88 с.
. Конюховский П.В.
Математические методы исследования операций в экономике: учебное пособие. -
СПб. - Москва - Харьков - Минск, 2005.
. Кулян В.Р. и др.
Математическое программирование. - К.: МАУП, 2005.
. Тах Х.А. Введение в
исследование операций 7-е издание.: Пер. с англ. - М.: Издательский дом
"Вильяме", 2005. - 912 с: ил. - Парал. тит. англ.