Вычисление экономических показателей

Задача 1

Применение функций =МАКС, =МИН и пользовательского, денежного и процентного формата. Создать таблицу — Список товаров на складе, подсчитать стоимость товаров, Стоимость с наценкой, выяснить максимальную и минимальную цену товаров, подвести итог по стоимости, использовать абсолютную адресацию. Применить условное форматирование: если стоимость с наценкой больше 10 000 руб. — ячейку залить красным цветом (формат> условное форматирование).

Решение

Исходные данные для задачи представлены в таблице 1.

Таблица 1 — Исходные данные

Для создания собственного пользовательского формата необходимо: Формат Ячейки — вкладка Число — раздел Числовые форматы — выбрать Все форматы. В окошке Тип вводим собственный шаблон 0шт. В текущей клетке числовые данные будут отображаться целыми числами и с текстом шт.

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

Для определения максимальной и минимальной цены товара использовали функции МАКС и МИН соответственно. В аргументы этих функций записывается диапазон ячеек, из которых выбирается максимальное или минимальное значения.

Среднюю стоимость с наценкой определили с помощью функции СРЗНАЧ, в аргументы которой записали диапазон ячеек.

Для закрашивания ячеек воспользуемся Условным форматированием. В полях задаем значения, которые будут закрашиваться. Если стоимость с наценкой больше 10 000 руб. — ячейку залить красным цветом. Формулы расчета представлены на рисунке 1.

Рисунок 1 — Формулы для расчета параметров Результат выполнения представлен на рисунке 2.

Рисунок 2 — Результат выполнения задачи

Задача 2

В таблице имеются данные о деятельности предприятия за четыре квартала прошлого года. Заполните следующую таблицу: исходные данные (черный шрифт) — квартал, цена, выпуск продукции, постоянные и переменные расходы; остальные данные (бледно-серый шрифт) вычислить, используя ниже представленные формулы; использовать функции МАКС, МИН; применить процентный формат. Выделить ячейку с максимальной прибыли, используя условное форматирование. Построить три диаграммы, отражающие:

а) Себестоимость на единицу продукции (гистограмму);

б) Выручку от реализации (круговую);

в) Динамику общей прибыли или убытка (график).

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

ВР = Ц * ОР, где ВР — выручка от реализации, руб.

Ц — цена единицы продукции, руб./шт.

ОР — объем выпуска и реализации продукции, шт.

Ус/с = Упост + Упер, где Ус/с — себестоимость единицы продукции (удельная себестоимость), руб./шт.

Упост — постоянные расходы на единицу продукции (удельные постоянные расходы), руб./шт.

Упер — переменные расходы на единицу продукции (удельные переменные расходы), руб./шт.

Пост = Упост*ОР, где Пост — общие постоянные расходы, руб.

Перем = Упер*ОР, где Перем — общие переменные расходы, руб.

С = Пост + Перем, где С — общая себестоимость продукции, руб.

Упр = Ц — У с/с, где Упр — прибыль на единицу продукции (удельная прибыль), руб.

Пр = ВР — С или Пр = Упр*ОР, где Пр — прибыль (убыток), руб.

Решение

Формулы для расчета параметров представлены на рисунке 3.

Рисунок 3 — Формулы для расчета параметров

Таблица с рассчитанными данными представлена на рисунке 4.

Рисунок 4 — Рассчитанные значения таблицы а) Себестоимость на единицу продукции (гистограмма);

Рисунок 5 — Себестоимость на единицу продукции б)

Выручка от реализации (круговая);

Рисунок 6 — Выручка от реализации продукции в) Динамика общей прибыли или убытка (график).

Рисунок 7 — Динамика общей прибыли

Задача 3

Рассчитать заработную плату. Столбец Разряд вычислить при помощи функции ВПР, Начислено вычислить, используя функцию ВПР и ЕСЛИ (Если отработано больше 40 часов, то Оплату увеличить на 60%). Применить условное форматирование для выделения ячеек с максимальной и минимальной заработной платой. Применить денежный формат ячеек, функцию МАКС, МИН, СРЗНАЧ.

Решение

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

Рисунок 8 — Формулы для расчета заработной платы Полученный результат представлен на рисунке 9.

Рисунок 9 — Расчет заработной платы

Задача 4

Сформировать следующую таблицу:

Выполнить:

Отсортировать таблицу по полю ФИО, ПРОДАННЫЙ ТОВАР (по возрастанию).

Отобрать товар, проданный в мае (пункт Условие), используя автофильтр.

Отобрать товар, от продажи которого выручка превышает 10 000 руб.

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

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

Решение

а) Выбираем все данные таблицы. Нажимаем Данные — Сортировка, в появившемся окне выбираем данные, по которым хотим провести сортировку (рисунок 10).

Рисунок 10 — Диалоговое окно Сортировка диапазона Нажимаем ОК и получаем отсортированную таблицу 2.

Таблица 2 — Отсортированная таблица

b) Для отбора товара проданного в мае воспользуемся Автофильтрацией, в которой в ячейке «Дата» выбираем «Условие».

Рисунок 11 — Условие отбора

Результат представлен в таблице 3.

Таблицы 3 — Товар проданный в мае

с) Для отбора товара, от продажи которого выручка превышает 10 000 руб. также воспользуемся Автофильтрацией. Выбираем поле Выручка и проставляем условие отбора, которое представлено на рисунке 12.

Рисунок 12 — Параметры условия отбора Результат фильтрации представлен в таблице 4.

Таблица 4 — Отбор выручки превышающей 10 000 р.

d) Используем функцию ИТОГИ из меню ДАННЫЕ.

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

Рисунок 13 — Диалоговое окно для промежуточного итога по количеству проданного товара по каждому продавцу Для определения промежуточных итогов по суммарной выручке и максимальной выручке каждого продавца в диалоговом окне Промежуточных итогов, в строке Операции выбираем Сумма и Максимум соответственно. Результат представлен в таблице 5.

Таблица 5 — Расчет промежуточных итогов

е) Сформируем сводную таблицу, отражающую сумму выручки по каждой дате и по виду товара; на основе сводной таблицы создадим сводную диаграмму.

Создадим сводную таблицу с помощью функции Сводная таблица меню Данные. Последовательность шагов представлена на рисунках 14 — 16.

Рисунок 14 — Первое окно мастера сводных таблиц

Затем указываем диапазон, содержащий исходные данные.

Рисунок 15 — Второе окно мастера Сводных таблиц В третьем окне выбираем месторасположение сводной таблицы.

Рисунок 16 — Третье окно мастера Сводных таблиц В появившейся сводной таблице в строки перемещаем Проданные товары, в столбцы — Дата, в область значений — Сумма выручки. Сводная таблица представлена на рисунке 17.

Рисунок 17 — Сводная таблица

На рисунке 18 показана диаграмма к сводной таблице.

Рисунок 18 — Диаграмма к сводной таблице

Задача 5

себестоимость процентный прибыль выручка а) Кредит в размере 8 500 000 руб. берется на 30 лет с максимальными ежемесячными платежами 52 500 руб. На какую максимальную процентную ставку можно согласиться при таких условиях. Определить, используя средство Подбор параметра. Ответ для самопроверки: 6,23%

b) За какой срок в годах сумма, равная 75 000 долл., достигнет 200 000 долл. при начислении 15% один раз в год. (Используется функция БС или ПЛТ и средство Подбор параметра). Ответ для самопроверки: 7 лет.

Решение

а) Для определения процентной ставки используем Встроенную функцию ПЛТ (Проценты; Срок; Размер ссуды). И используем Подбор параметра. Вызываем встроенную функцию Подбор параметра (рисунок 19)

Рисунок 19 — Параметры Подбора параметра Решение задачи представлено на рисунке 20.

Рисунок 20 — Определение срока погашения кредита

b) Для решения задачи воспользуемся встроенной функцией БС. В ячейку записываем параметры функции БС (Процентная ставка (0,15); Срок (F3);; Размер ссуды (-25 000)).

Решение представлено на рисунке 21

Рисунок 21 — Решение задачи

Задача 6

Создайте таблицу подстановки с одним и с двумя входами для подсчета дивидендов. Исходная таблица.

Здесь в ячейках содержатся следующие начальные данные: в ячейке В1 —цена акции; В2 — количество акций; ВЗ — процентная ставка; В4 — сумма дивидендов, которая рассчитывается по формуле: = В1*В2*В3.

а) Представьте в виде таблицы суммы дивидендов, если количество акций изменяется от 25 000 до 300 000 с шагом 25 000.

b) Представьте в виде таблицы суммы дивидендов, если количество акций изменяется от 25 000 до 300 000 с шагом 25 000, а цена одной акции от 750 до 1000 с шагом 50 р.

Ответ для самопроверки: а) при количестве 300 000 акций сумма дивидендов составит 2 970 000 р.; b) при количестве 300 000 акций и цене одной акции в 1000 р сумма дивидендов составит 3 960 000 р.

Решение

Вводим число 25 000 в ячейку В6, и выделяем диапазон ячеек В6: В17.

Выбираем команду Правка > Заполнить > Прогрессия. Откроется диалоговое окно Прогрессия.

В этом окне в поле Шаг введите значение 1, щелкните на ОК. В диапазоне А5: А15 будет создана последовательность входных значений (числа от 25 000 до 300 000 с шагом 25 000).

Выделим диапазон ячеек В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 с.

2. Конюховский П. В. Математические методы исследования операций в экономике: учебное пособие. — СПб. — Москва — Харьков — Минск, 2005.

3. Кулян В. Р. и др. Математическое программирование. — К.: МАУП, 2005.

4. Тах Х. А.

Введение

в исследование операций 7-е издание.: Пер. с англ. — М.: Издательский дом «Вильяме», 2005. — 912 с: ил. — Парал. тит. англ.