Использование средств MS Excel для финансово – экономических расчетов

Дисциплина:

Раздел 5. Информационные технологии в профессиональной деятельности

ПМ.03. Организация деятельности структурных подразделений аптеки и руководство аптечной организацией при отсутствии специалиста с высшим образованием

МДК 03.01. Организация деятельности аптеки и её структурных подразделений

Специальность: 33.02.01 (060301.51) Фармация

Методическая разработка для проведения практического занятия по теме «Использование средств MS Excel для финансово - экономических расчетов»

Автор работы: Ельдецова Ирина Викторовна, преподаватель

колледж ГБОУ ВПО ОмГМУ Минздрава России

Тема занятия: Использование средств MS Excel для финансово - экономических расчетов.

Цели занятия:

1. Учебные.

Расширить знания по ведению первичной учетной документации, проведению экономического анализа отдельных производственных показателей деятельности аптечных организаций средствами Microsoft Office и специализированного программного обеспечения.

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

Знания: Основных задач и направлений информатизации аптечных организаций. Виды и тенденции развития программного обеспечения профессиональной направленности. Интерфейс ПО.

2. Развивающие.

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

3. Воспитательные.

Выработать умение мыслить. Побудить к познавательной, научной, творческой, трудовой деятельности. Способствовать развитию умений преодолевать трудности, закалки воли, развитию познавательных интересов, убеждений, привитию взглядов, привычек.

Тип занятия: практическое

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

Формы и методы: метод анализа конкретных ситуаций (АКС), групповая работа, групповая дискуссия, самостоятельная работа по алгоритмам действий, машинно - программированный контроль.

Оснащение занятий.

Методические материалы: дидактический материал обучающего характера, задания с алгоритмами действий, вопросы для дискуссии, тестовые задания для машинно - программируемого контроля по разделу.

Материально - техническое оснащение: компьютеры с лицензионным программным обеспечением, мультимедийный проектор, экран, интерактивная приставка.

Перечень литературы:

Основная литература:

п/№

Наименование

Автор (ы)

Год, место издания

Кол-во экземпляров

Информационные технологии : учебник

Г. С. Гохберг, А. В. Зафиевский, А. А. Короткин

Москва : Академия, 2012

350

Дополнительная литература:

п/№

Наименование

Автор (ы)

Год, место издания

Кол-во экземпляров

2.

Информационные технологии [Электронный ресурс] : учеб. пособие. - Режим доступа: knigafund.ru/books/106847

Г. Н. Исаев

Москва : Омега-Л, 2012

доступ по индивидуальным картам для каждого обучающегося

Электронные образовательные ресурсы

Базы данных, информационно-справочные и поисковые системы:

1. Электронная библиотека ОмГМА: weblib.omsk-osma.ru/;

2. Электронно-библиотечная система «КнигаФонд»: knigafund.ru;

3. «Электронно-библиотечная система «Консультант студента. Электронная библиотека медицинского колледжа»: medcollegelib.ru;

СТРУКТУРА И СОДЕРЖАНИЕ ПРАКТИЧЕСКОГО ЗАНЯТИЯ

Этапы занятия

Код формируемых компетенций

Вре-мя

(мин)

1.

Организационный момент.

Проверка подготовленности кабинета к занятию, создание рабочей обстановки, формулировка целей занятия

ОК 1,2,4,5,9

ПК 1.8,3.1,

3.2, 3.3, 3.6

5

2.

Целевая установка.

Мотивация учебной деятельности:

Программа Excel имеет две отличительные особенности - эффективные вычислительные возможности и мощные визуальные средства для передачи цифровой информации. Именно такая комбинация открывает Excel особенно широкие перспективы для использования в деловой сфере.

С помощью электронных таблиц Microsoft Excel автоматизируется решение ряда типовых задач экономического анализа. Это, в частности, анализ безубыточности, эффективности сегментов бизнеса, планирование продаж коммерческой организации и анализ краткосрочных управленческих решений, возникающих в практике работы многих предприятий.

5

3.

Тестовый контроль для определения исходного уровня знаний по теме «Обработка информации средствами Microsoft Excel». В форме машинно - программированного контроля MTesting.

15

4.

Организация работы студентов с использованием метода анализа конкретных ситуаций (АКС).

Метод анализа конкретных ситуаций заключается в том, что в процессе обучения преподавателем создаются проблемные ситуации, взятые из профессиональной практики. От обучаемых требуется глубокий анализ ситуации и принятие соответствующего оптимального решения в данных условиях. АКС выполняет множество различных функций, служит инструментом исследования, изучения, оценки и выбора, обучения, воспитания, развития. Достоинство метода состоит в том, что в процессе решения конкретной ситуации участники обычно действуют по аналогии с реальной практикой, то есть используют свой опыт, применяют в учебной ситуации те способы, средства и критерии анализа, которые были ими приобретены в процессе обучения. Студенты не только получают нужные знания, но и учатся применять их на практике.

Этапы работы:

1-й этап. Введение в изучаемую проблему.

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

2-й этап. Постановка задачи.

Учебная группа делится на несколько подгрупп (по 2 человека в подгруппе - руководитель аптечной организации и его заместитель)

Преподаватель определяет круг задач для подгрупп:

1. Проанализировать примеры финансово - экономических ситуации.

2. Реализовать математические компьютерные модели их решений с помощью средств электронных таблиц.

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

Преподаватель устанавливает время и режим самостоятельной работы:

Самостоятельная работа - 100 минут с перерывами через 30 мин на выполнение комплекса упражнений физкультурной паузы общего воздействия по 5 мин.

3-й этап. Групповая работа над финансово - экономическими ситуациями.

Преподаватель наблюдает за работой подгрупп, отвечает на возникшие вопросы, дает рекомендации.

4-й этап. Групповая дискуссия.

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

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

Примерные вопросы для дискуссии:

1. Возможно, ли математическую модель ситуации №1 использовать для планирования и контроля правильности начисления налогов?

2. Как вы думаете, в математической модели ситуации №1 при изменении суммы реализации и издержек будет ли сразу виден фактический результат проведения будущей и прошедших операций?

3. На сколько универсальны предложенные Вам математические модели?

4. Возможно, ли с помощью математической модели ситуации №2 проанализировать влияние изменения выручки от реализации на сумму прибыли?

5. Если Вы как руководитель аптечной организации планируете арендовать дополнительную торговую точку, то какими моделями вы воспользуетесь и для чего?

6. Насколько на Ваш взгляд математическая модель «О назначении (assignment problem)» непосредственно исходить от практической кадровой ситуации?

7. При поиске наиболее удобного расположения центра обслуживания клиентов воспользовались бы вы математической моделью ситуации № 5?

5-й этап. Итоговая беседа.

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

5

10

100

25

10

5.

Домашнее задание.

5

Время -180 мин

МЕТОДИЧЕСКИЕ УКАЗАНИЯ ДЛЯ СТУДЕНТОВ ПО ВЫПОЛНЕНИЮ 3-ГО ЭТАПА «ГРУППОВАЯ РАБОТА НАД ФИНАНСОВО - ЭКОНОМИЧЕСКИМИ СИТУАЦИЯМИ»

№ п/п

Этап

Ориентир. время

Методические указания

Форма отчетности

1

Групповая работа над финансово - экономическими ситуациями.

100

Вашей группе необходимо:

1. Проанализировать примеры финансово - экономических ситуации.

2. Реализовать математические компьютерные модели их решений с помощью средств электронных таблиц.

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

Демонстрация результатов решения своих финансово - экономических ситуаций средствами электронных таблиц.

Выступление с сообщением о результатах работы

ФИНАНСОВО - ЭКОНОМИЧЕСКИЕ СИТУАЦИИ ДЛЯ ГРУППОВОЙ РАБОТЫ СТУДЕНТОВ (3-Й ЭТАП)

Подбор ситуаций осуществляется по принципу от простого к сложному.

Пример финансово - экономической ситуации №1

Очень часто руководителю аптеки приходится производить расчет эффективности будущих операций. Для автоматизации подобных расчетов используют электронную таблицу. Пример упрощенной таблицы представлен на рис. 1.

Рис 1. Таблица расчета налогов и прибыли (с числовыми данными)

В данной таблице имеются четыре области:

-Ставки налогов (область А1:ВЗ).
- Суммы реализованных товаров, издержек и дебетового НДС (область А5:В7).
- Расчет НДС, подлежащего уплате в бюджет (область А9:В10).
- Расчет прибыли и налогов на прибыль и с оборота (область А12:В17).

Заметим, что первые две области предназначены для ввода информации, а последние - для ее дальнейшей обработки. Рассмотрим формулы, введенные в область расчета (рис. 2.)

Формула для расчета налогового обязательства по НДС показана также на рис. 2.

Табличный вид этой формулы следующий:

=ОКРУГЛ(В5*(B1/(1+B1));2)

Формула расчета НДС, подлежащего уплате в бюджет, определяется вычитанием суммы налогового кредита из суммы налоговых обязательств (находится в ячейке В10):

=В9-В7

Валовая прибыль без НДС (формула в ячейке В12) равна разности суммы реализации и суммы налоговых обязательств:

=В5-В9

Рис. 2. Таблица расчета налогов и прибыли (с формулами)

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

=В12-В6

Налог с оборота рассчитывается умножением полученной валовой прибыли на ставку налога с оборота:

=ОКРУГЛ(В12*ВЗ;2)

Налогооблагаемая прибыль вычисляется как разность полученной прибыли и суммы налога с оборота:

=В13-В14

Налог на прибыль равен округленному до второго разряда (до копеек) произведению налогооблагаемой прибыли на действующую ставку налога на прибыль:

=ОКРУГЛ(В15*В2;2)

Чистая прибыль в результате реализации равняется разности налогооблагаемой прибыли и суммы налога на прибыль:

=В15-В16

Пример финансово - экономической ситуации №2

Необходимо провести расчёты по исследованию влияния различных вариантов (сценариев) изменения выручки от реализации, постоянных и переменных затрат на изменение прибыли и финансовой прочности аптечной организации.

Базовая компьютерная модель анализа «Затраты - Объём - Прибыль». Модель основана на предположении, что для каждого сценария, представленного отдельным столбцом электронной таблицы, может быть задана произвольная комбинация изменений параметров R, C и v. Изменения задаются как положительный или отрицательный прирост указанных параметров и рассчитываются по формулам:

1. R_j = R₀(1 + r_j⁽¹⁾) + R_j⁽²⁾;

2. C_j = C₀(1 + c_j⁽¹⁾) + C_j⁽²⁾;

3. v_j = v₀(1 + v_j⁽¹⁾) + v_j⁽²⁾,

где R_j - сумма выручки от реализации в j-м сценарии; C_j - сумма постоянных затрат в j-м сценарии; v_j - переменные затраты на рубль реализации в j-м сценарии; R₀ - выручка от реализации базового периода; C₀ - постоянные затраты базового периода; v₀ - переменные затраты базового периода; r_j⁽¹⁾ - коэффициент относительного прироста выручки от реализации в j-м сценарии; c_j⁽¹⁾ - коэффициент относительного прироста постоянных затрат в j-м сценарии; v_j⁽¹⁾ - коэффициент относительного прироста переменных затрат на рубль реализации в j-м сценарии; R_j⁽²⁾ - абсолютная величина прироста выручки от реализации в j-м сценарии; C_j⁽²⁾ - абсолютная величина прироста постоянных затрат в j-м сценарии; v_j⁽²⁾ - абсолютная величина прироста переменных затрат на рубль реализации в j-м сценарии.

В соответствии с формулами 1-3 для удобства пользователя изменения базовых параметров могут задаваться в виде как относительных, так и абсолютных величин.

По заданным параметрам изменений, характеризующим отдельные сценарии, в модели рассчитываются все остальные зависимые показатели модели.

Общий вид компьютерной модели в формате электронной таблицы Excel представлен на рис. 3.

Рис. 3. Общий вид базовой модели
анализа «Затраты - Объём - Прибыль»

В столбце A перечислены названия исследуемых показателей. В столбце B представлены базисные значения. Сценарии изменения суммы выручки, постоянных затрат и переменных затрат на рубль выручки могут задаваться в процентах от базисных значений или в виде абсолютного значения изменения по отношению к ним. Жирным шрифтом выделены ячейки, содержащие расчётные формулы, а обычным - ячейки, значения в которых задаются вручную. В соответствующих строках столбцов C-I содержатся значения показателей различных сценариев изменения затрат и выручки. В таблице на рис. 4 заданы семь сценариев. Однако формулы расчёта показателей для всех сценариев полностью идентичны. Их можно копировать для создания произвольного числа сценариев.

Основные формулы компьютерной модели приведены в таблице, изображенной на рис. 4.

Рис. 4. Основные формулы компьютерной модели

Поскольку в столбцах расчёта показателей сценариев формулы однотипны, они приводятся только для столбца C. В остальные столбцы их можно скопировать стандартными средствами электронных таблиц.

Порядок работы с таблицей таков. Вручную задаются базовые (текущие) показатели выручки от реализации (B3), переменных (B6) и постоянных (B11) затрат. На их основе вычисляются маржинальная прибыль (B10), прибыль (B14), порог рентабельности (B16) и запас финансовой прочности в процентах

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

Выручка в ячейке C3 рассчитывается по формуле $B$3*(1+C4)+С5. Расчёт производится путём умножения выручки в базовом периоде ($B$3) на коэффициент изменения, получаемый путём сложения 100 % базового уровня с отклонением (положительным или отрицательным), заданным в ячейке C4, и к этой величине прибавляется абсолютное значение заданного пользователем в ячейке C5 изменения суммы выручки.

Таким образом, изменение выручки можно задать как в процентах (ячейка C4), так и в виде конкретного значения. Можно одновременно задать и то и другое.

Поскольку все вычисления привязываются к базовому уровню выручки, используется абсолютная адресация. Процентный формат в электронной таблице подразумевает хранение в ячейке долей единицы. Например, вводится величина 30 %, а хранится и используется в расчётах коэффициент 0,3. Иными словами, в ячейке процентного отклонения хранится видимая на экране величина, делённая на 100 %. Это отличается от традиционной «бумажной» записи, где вместо выражения в скобках надо было бы записать (100 % - С4) / 100 %.

Переменные затраты в ячейке C6 рассчитываются как произведение суммы выручки по данному сценарию (ячейка C3) на изменённый в нём по отношению к базе коэффициент соотношения переменных затрат и выручки. В представленных на рис. 3 данных этот коэффициент неизменен для всех сценариев, потому что ни в одном из них не задано изменяющих его данных. Однако в дальнейшем мы рассмотрим ситуации, когда коэффициент переменных затрат может измениться.

Расчёт коэффициента переменных затрат осуществляется путём умножения его базового значения на величину коэффициента изменения, к которой добавляется предусмотренное сценарием изменение на абсолютную величину (ячейка C9).

Маржинальная прибыль рассчитывается как разность выручки от реализации (ячейка C3) и переменных затрат (ячейка C6).

Постоянные затраты в ячейке C7 рассчитываются по тем же принципам, что и сценарная сумма выручки от реализации.

Прибыль рассчитывается как разница между маржинальной прибылью (ячейка C10) и постоянными затратами (ячейка C11). В ячейке C15 вычисляется процент изменения прибыли в данном сценарии по отношению к её базовой величине. Порог рентабельности для данного сценария определяется как отношение постоянных затрат к разнице между единицей и коэффициентом переменных затрат.

Для большей наглядности представления данных в модель встроена диаграмма, характеризующая изменение прибыли в исследуемых сценариях. Кроме того, для диапазона С14-I14 задано условное форматирование, позволяющее выделять иным цветом ячейки расчёта прибыли в том случае, если достигнутая в данном сценарии прибыль превосходит базовую величину.

Базовая модель анализа «Затраты - Объём - Прибыль» позволяет решать широкий круг задач.

Прежде всего благодаря ей можно проанализировать влияние изменения выручки от реализации на сумму прибыли. В данных рис. 3 сценарии 1, 2, 4 и 5 определяют изменение выручки от реализации на -10 %, -20 %, 10 % и 20 % соответственно при сохранении прочих параметров модели неизменными. Из приведённых данных видно, что снижение выручки от реализации на 20 % (сценарий 1) приведёт к уменьшению прибыли на 50 % по сравнению с базовым уровнем, а увеличение выручки на 20 % (сценарий 5) увеличит прибыль на те же 50 %. Поскольку в сценариях 1-5 никакие другие параметры не изменяются, порог рентабельности остаётся неизменным - 6000.

Сценарии 6 и 7 характеризуются одновременным изменением выручки от реализации и постоянных затрат. Такой анализ может быть нужен, например, в том случае, когда предприятие планирует арендовать дополнительную торговую точку. Тогда постоянные затраты возрастают, но можно ожидать возрастания выручки от реализации. В соответствии с приведёнными на рис. 3 сведениями постоянные затраты возрастают на сумму 1500. Если при этом выручка от реализации увеличится на 30 %, то прибыль в целом останется неизменной. Если же при том же увеличении постоянных затрат удастся добиться увеличения выручки на 40 %, то по сравнению с базовым уровнем прибыль вырастет на 25 %. Однако в любом случае порог рентабельности вырастет, а финансовая прочность предприятия снизится по сравнению с базовым уровнем.

Построенная модель позволяет сравнивать одновременно несколько альтернативных управленческих решений, так или иначе комплексно влияющих на изменения в структуре и объёме затрат, а также выручки от реализации.

Пример финансово - экономической ситуации № 3

Инвестор, рискуя, планирует наилучшим образом определить части сумм денег общим объемом S д. ед., которые будут вложены у n предприятий путем покупки акций с целью получения прибыли у определенном последующем периоде.
Условие оптимально означает, что существует два альтернативные критерии оптимизации:

• минимизация риска при фиксированном доходе (аккуратный подход) или

• максимизация дохода при фиксированном риске.

В инвестиционном менеджменте набор частей сумм инвестирования называется портфель, оптимальным портфелем есть такой набор, который инвестор признает для себя наилучшим относительно дохода и риска. Чем больший доход, тем больший риск.
Наш инвестор хочет вложить деньги в суме 30000 д. ед. в акции 6 предприятий, для каждого с которых известны доходность акций (%), срок действия, оценка риска, максимальная величина инвестиций на одно предприятие не больше 7500 в предприятия с уровнем риска больше 3 вложить не больше 1/3 сумы всех денег, а в предприятия со сроком больше чем 5 лет не меньше ? сумы денег.
Инвестор должен выбрать на основе фиксированных предприятиями рисков оптимальный вариант - куда и сколько вложить денег, что бы получить максимальный доход.
Экономико-математическая модель.

• Найти план инвестирования при котором,

• Об_Доход=План*Доходность(%) - mах

• При ограничениях: План=30000; План_предприятие<=7500; План>=Мин_взнос Предприятия(риск>=3) <=10000; Предприятия(срок>5)>=15000.

Пример финансово - экономической ситуации № 4

Название задачи и соответственной модели - о назначении (assignment problem) - непосредственно исходить от практической кадровой ситуации, когда претендентов нужно назначить на вакантные должности наилучшим образом.
Критерий максимизации общего аффекта используется тогда, когда известны о каждом претенденте заданные оценки способности исполнения определенной функции (чем выше, тем лучше), минимизации - когда, скажем, для каждого претендента заданная величина затрат на его подготовку для определенной деятельности (чем выше, тем хуже).
Аптечная организация объявила набор работников для нового отдела - указало названия семи должностей и количество вакансий по каждой из них. Кадровая служба собрала от 18 претендентов на эти должности, провела тестирование по каждой из них и за результатами определила 5 групп с одинаковыми оценками и возможностями, получивши соответственную таблицу средних оценок их умений и знаний.
Нужно назначить на вакантные должности претендентов таким образом, что бы общая эффективность исполнения ними соответствующих обязанностей была максимальной.
Особенность - наличие высокой оценки еще не гарантирует успех, поскольку целью оптимизации о назначении есть максимальный общий эффект.
Экономико-математическая модель.

1. Найти такую матрицу назначений, чтобы

2. Общий эффект=Матрица_оценок*Матрица_назначений - mах

3. При ограничениях: Назначено<=Всего_претендентов; Занято=Всего_вакансий.
   
   
   

Пример финансово - экономической ситуации №5

Современный маркетинг все чаще использует количественные методы планирования соответствующих исследований. Эта особенность проявилась в появлении и активном развитии научного направления под названием Marketing Engineering, где для качественной деятельности маркетологов используются методы прогнозирования и оптимизации, развитые в сфере математического программирования и исследования операций.
В области маркетинговых исследований довольно типичной есть задачи размещения (location problem), скажем, сервисных центров (клиник, полицейских служб, учебных заведений), складов или производительных мощностей. Целью таких задач есть определения такого размещения, для которого минимизируется, например, расстояние между центром предоставления услуг та их потребителем. Задачи такого типа появляются в практике коммуникационного обеспечения (пути, железная дорога, кабельная связь), где нужно исполнить определенные работы с минимальной стоимостью.
Начальными данными для этой задачи есть координаты та весомые коэффициенты (количество населения, число школьников, пенсионеров или больных) клиентов, дополнительно учитываются определенные ограничения. «Клиентами» в данном примере есть 25 городов Европы. Нужно найти координаты оптимального центра, чтобы минимизировать суму расстояний от центра к всем другим городам.
Экономико-математическая модель.

• Найти координаты центра, чтобы

• Сума расстояний была минимальной

• При ограничениях: