Московский государственный университет

путей сообщения (МИИТ)

Кафедра «Экономика и управление на транспорте»

А.В. Шобанов, И.А. Епишкин, Е.В. Струкова

ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ

В ЭКОНОМИКЕ

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

для практических занятий

Москва – 2005

Московский государственный университет

путей сообщения (МИИТ)

Кафедра «Экономика и управление на транспорте»

А.В. Шобанов, И.А. Епишкин, Е.В. Струкова

Утверждено

редакционно-издательским

Советом университета

ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ В ЭКОНОМИКЕ

Методические указания и задания для практических занятий

для студентов экономических специальностей

Москва – 2005

УДК 330

Ш - 78

Информационные технологии в экономике. Методические указания и задания для практических занятий для студентов экономических специальностей / Шобанов А.В., Епишкин И.А., Струкова Е.В. – М.: МИИТ, 2005. - 36 с.

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

 Московский государственный университет путей сообщения (МИИТ), 2005

СОДЕРЖАНИЕ

ВВЕДЕНИЕ..............................................................................................................4

1. ЗАДАНИЯ ДЛЯ ПРАКТИЧЕСКИХ ЗАНЯТИЙ..............................................5

1.1. Задание № 1………………...………………………………………….......5

1.2. Задание № 2……..........................................................................................6

1.3. Задание № 3..................................................................................................8

1.4. Задание № 4……………………..................................................................9

1.5. Задание № 5……………………….............................................................10

1.6. Задание № 6.................................................................................................12

1.7. Задание № 7.................................................................................................13

1.8. Задание № 8………………………………….……………………………15

1.9. Задание № 9…………………………….…………………………............16

1.10. Задание № 10……………………………..………………………...........19

1.11. Задание № 11………………………………….…………………………22

2. МЕТОДИЧЕСКИЕ УКАЗАНИЯ К ВЫПОЛНЕНИЮ

ЗАДАНИЙ В СРЕДЕ MS EXCEL………………………………………………27

ВВЕДЕНИЕ

Данные методические указания предназначены для студентов, обучающихся на кафедре «Экономика и управление на транспорте» института экономики и финансов (ИЭФ) МИИТа.

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

Предлагаемые задания ориентированы на использование средств электронных таблиц Microsoft Excel.

1. Задания для практических занЯтий

1.1.Задание № 1

1. Рассчитать структуру привлечённых средств коммерческого банка. В результате решения задачи необходимо сформировать следующий выходной документ:

Таблица 1

Структура привлечённых средств коммерческого банка

Привлеченные средства коммерческого банка

Сумма млн. руб.

Уд. вес, %

Депозиты государственных предприятий

2 000

Вклады населения

4 000

Депозиты СП

700

Депозиты внебюджетных фондов

1 000

Депозиты фермерских хозяйств

850

Депозиты АО и ООО

1 200

Депозиты ИЧП

900

Остатки на расчётных и текущих счетах клиентов

8 000

Депозиты юридических лиц в валюте

5 000

ИТОГО

2. Выполнить сортировку документа:

- по убыванию объёмов привлеченных средств коммерческого банка;

- по возрастанию наименований привлеченных средств.

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

4. С помощью средства "Автофильтр" на отдельном листе выполнить фильтрацию сформированного документа, оставив в нём:

- только те привлеченные средства коммерческого банка, объём которых больше 1 млрд. руб. Вернуть документ в исходный вид.

- только депозиты коммерческого банка. Вернуть документ в исходный вид;

5. На основании исходного документа "Структура привлечённых средств КБ" рассчитать и сформировать следующий документ только для депозитных средств банка:

Таблица 2

Анализ привлеченных средств коммерческого банка

Расчётная величина

Значение

Средняя величина всех депозитных средств

Количество всех привлеченных средств банка

Максимальная величина депозитных средств

Минимальная величина всех привлеченных средств банка

1.2.Задание № 2

1. На ЛИСТЕ 1 сформировать и заполнить ведомость (табл. 3).

2. Скопировать данную ведомость на ЛИСТ2-ЛИСТ3 и в "групповом режиме" рассчитать:

ОС=БС - И

ВС=БС * k

где ОС – остаточная стоимость основных фондов, млн.руб.;

БС – балансовая стоимость основных фондов, млн.руб.;

И – износ основных фондов, млн.руб.;

ВС – восстановительная стоимость основных фондов, млн.руб.;

k – коэффициент переоценки стоимости основных фондов.

Если балансовая стоимость объекта превышает 500 млн. руб. (БС>500), то коэффициент переоценки равен трем (k = 3). Если БС<500, то k = 2.

3. Переименовать ЛИСТ 1 в ВЕДОМОСТЬ; ЛИСТ 2 в ИТОГИ; ЛИСТ 3 в РАСШ_ФИЛЬТР.

4. В групповом режиме добавить в таблицу (во все листы) новую графу (после графы "наименование объекта") "Вид объекта" и присвоить всем объектам Цех №1-Цех №4 вид основной, а всем остальным объектам – вспомогательный.

Таблица 3

Переоценка основных средств, млн. руб.

Наименование объекта

Балансовая стоимость

Износ

Остаточная стоимость

Восстановительная стоимость

Заводоуправление

1576,2

568,0

Диспетчерская

176,0

45,4

Цех № 1

710,2

120,3

Цех № 2

804,6

240,0

Цех № 3

933,0

150,2

Цех № 4

474,4

174,5

Склад № 1

570,5

221,2

Склад № 2

430,4

92,2

Склад № 3

564,9

118,0

Склад № 4

320,5

87,5

Итого

5. На ЛИСТЕ ИТОГИ рассчитать общую (суммарную) балансовую и остаточную стоимость всех основных и всех вспомогательных объектов с помощью команды "Итоги" меню "Данные" (сначала отсортировать таблицу по возрастанию видов объекта).

6. На ЛИСТЕ РАСШ_ФИЛЬТР с помощью команды расширенный фильтр сформировать накопительную ведомость по тем объектам, балансовая стоимость которых более 500 млн. руб. Поместить в новый документ графы "Наименование объекта", "Балансовая стоимость", "Остаточная стоимость".

1.3.Задание № 3

1. Составить балльные оценки критериев, используемых при составлении рейтинга пяти транспортных компаний в соответствии со шкалой: меньше 400 - 4, от 400 до 600 - 6, от 600 до 800 - 8, больше 800 - 10. В результате решения задачи необходимо сформировать следующий выходной документ:

Таблица 4

Результаты рейтинга транспортных компаний

Наименование показателя

Рейтинговые оценки критериев

Балльные оценки критериев

1

2

3

4

5

1

2

3

4

5

Перечень работ, услуг

900

700

700

800

650

Стоимость работ, услуг

856

479

454

520

300

Объемы работ, услуг

880

469

660

555

420

Квалификация

856

618

422

720

620

Техническая, технологическая и информационная оснащенность

635

638

479

440

643

Финансовое состояние

866

612

764

860

654

Репутация

800

91

164

700

753

Прочие показатели

816

220

291

668

247

2. Закрепить область шапки таблицы.

3. Добавить строку "Итоговый рейтинг", являющийся суммой баллов каждой компании.

4. По критерию "Репутация" определить ранг каждой из компаний, заполнив табл. 5. Значение критерия скопировать из табл. 4, используя команду СПЕЦИАЛЬНАЯ ВСТАВКА меню ПРАВКА.

Таблица 5

Репутация транспортных компаний

Компания

Значение критерия

Ранг

1

2

3

4

5

5. Скопировать таблицу 5 в Microsoft Word и установить связь с данными Microsoft Excel так, чтобы при изменениях в исходной книге Excel обновлялись данные в документе Microsoft Word.

6. Построить на отдельном рабочем листе смешанную диаграмму, в которой значения критерия «Репутация» были бы представлены в виде гистограмм, а ранг – в виде линейного графика на той же диаграмме. Вывести название графика "Репутация транспортных компаний" и легенду.

1.4.Задание № 4

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

   • ликвидные активы - LA(i);

   • суммы остатков на расчётных, текущих счетах, вкладов и депозитов - C(i);

   • общая сумма активов банка - A(i);

   • обязательства банка по счетам до востребования - OB(i);

где i - количество рассматриваемых периодов.

Формулы для расчёта:

H5(i)=LA(i)/C(i); H6(i)=LA(i)/A(i); H7(i)=LA(i)/OB(i).

В результате решения задачи необходимо сформировать следующий документ:

Таблица 6

Показатели ликвидности баланса коммерческого банка, тыс. руб.

Показатель

1 января

1 февраля

1 марта

1 апреля

1 мая

LA(i)

1618170

3313380

976490

316790

3587380

C(i)

1597240

3174570

4275760

3754220

6988760

A(i)

3379790

14367540

14237870

9812660

13063760

OB(i)

669210

4412760

9023420

7856240

5876750

H5(i)

H6(i)

H7(i)

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

3. На отдельном листе выполнить фильтрацию таблицы, оставив в ней только показатели ликвидности и выходные показатели баланса банка на 1 мая.

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

1.5.Задание № 5

Используя финансовые функции для коммерческих расчётов решить следующие задачи:

1. Какая сумма окажется на счёте при внутригодовом учёте процентов, если 10 000 руб. были размещены в банке на 6 (n) лет под 10% (r) годовых. Результаты расчетов представить в форме табл. 7.

2. То же, но вкладчику выплачивают сумму в 800 руб.

3. Какая сумма должна быть выплачена, если шесть лет назад была выдана ссуда 1500 тыс. руб. под 15 % годовых с ежемесячным начислением процентов.

Таблица 7

Определение будущего значения вклада

Метод начисления процентов

Общее число периодов начисления процентов

Ставка процента за период начисления, %

Будущее значение вклада, руб.

ежегодный

=n

=r

полугодовой

=n*2

=r/2

квартальный

=n*4

=r/4

месячный

=n*12

=r/12

ежедневный

=n*365

=r/365

4. Есть два варианта инвестирования средств в течение 4 лет: в начале каждого года под 26% годовых или в конце каждого года под 38% годовых. Пусть ежегодно вносится 30 тыс. руб. Определите, сколько денег окажется на счёте в конце 4-го года для каждого варианта.

5. Фирме потребуется 5 млн. руб. через 12 лет. В настоящее время фирма располагает деньгами и готова положить их на депозит единым вкладом, чтобы через 12 лет он достиг 5 млн. руб. Определите необходимую сумму текущего вклада, если ставка процента по нему составляет 12% в год.

6. Предположим, рассматриваются два варианта покупки дома: заплатить сразу 9,9 млн. руб. или в рассрочку - по 94 тыс. руб. ежемесячно в течение 15 лет. Определить, какой вариант предпочтительнее, если ставка процента- 8 % годовых.

7. Инвестиции в проект к концу первого года его реализации составят 10000 руб. В последующие три года ожидаются годовые доходы по проекту 3000 руб., 4200 руб., 6800 руб. Издержки привлечения капитала 10%. Рассчитать чистую текущую стоимость проекта.

1.6.Задание № 6

1. Выполните группировку трех новых листов. Введите и заполните табл. 8.

2. Добавьте новый рабочий лист и скопируйте в него таблицу с любого листа, очистите содержимое полей 2, 3, 4. Выполните консолидацию таблиц по полю 2, 3, 4 на основе данных трех предыдущих таблиц, используя функцию "Сумма" команды КОНСОЛИДАЦИЯ (меню ДАННЫЕ).

3. На основе консолидированной таблицы постройте сводную таблицу (меню ДАННЫЕ), выделив диапазон таблицы без «шапки», и разместите:

   • графу 1 в строках СВОДНОЙ ТАБЛИЦЫ;

   • графу 6 в столбцах СВОДНОЙ ТАБЛИЦЫ;

   • графу 2, 3, 4 в данных СВОДНОЙ ТАБЛИЦЫ;

   • графу 5 в странице СВОДНОЙ ТАБЛИЦЫ.

4. Выполните фильтрацию данных в СВОДНОЙ ТАБЛИЦЕ для разных значений графы 5.

Таблица 8

Ведомость выдачи зарплаты

Ф.И.О.

Всего начислено, руб.

Всего удержано, руб.

Сумма

к выдаче, руб.

Отдел

Период

1

2

3

4

5

6

Иванов В.А.

1500

14% от

1

1 кв.

Петров А.А.

1800

всего

2

1 кв.

Сидоров П.Р.

1350

начислено

2

1 кв.

Морозов К.Н.

1650

1

1 кв.

Черкасов К.Г.

1900

1

1 кв.

Захаров Л.Д.

1770

1

1 кв.

Ветров А.Р.

1400

2

1 кв.

Иванов В.А.

1200

1

2 кв.

Петров А.А.

3000

2

2 кв.

Сидоров П.Р.

4700

2

2 кв.

Морозов К.Н.

6500

1

2 кв.

Черкасов К.Г.

2400

1

2 кв.

Захаров Л.Д.

7700

1

2 кв.

Ветров А.Р.

4440

2

2 кв.

5. Изменить имя поля данных в сводной таблице по исходной таблице (сумма по полю 2 изменить на «Всего начислено», сумма по полю 3 на « Всего удержано», сумма по полю 4 на «Сумма к выдаче»).

6. Используя кнопку "Поле" сводной таблицы на панели инструментов сводной таблицы, вычислите:

   • максимум по полю "Всего начислено", установив денежный формат (р.);

   • минимум по полю "Всего удержано";

   • среднее значение по полю "Сумма к выдаче".

1.7.Задание № 7

1. В табл. 9 приведены данные результатов сдачи экзаменационной сессии студентов. На новый рабочий лист скопируйте шаблон таблицы (названия строк и граф), содержащий фамилии первых десяти студентов. Используя функцию ПРОСМОТР категории ССЫЛКИ И МАССИВЫ, заполните таблицу результатами сдачи.

2. На основе данных табл. 9 подсчитайте количество студентов, сдавших сессию на «отлично», «хорошо» и «удовлетворительно», с помощью функции СЧЕТЕСЛИ категории СТАТИСТИЧЕСКИЕ. Сформировать документ в виде табл. 10.

3. Создайте с использованием УСЛОВНОГО ФОРМАТИРОВАНИЯ для всех значений табл. 10 следующие форматы:

• если значение меньше или равно 10, то шрифт полужирный курсив и отобразить на жёлтом фоне;

• если значение больше 10, то шрифт полужирный и отобразить на зелёном фоне.

Сводная ведомость результатов экзаменационной сессии

№ п/п

ФИО

Оценки

Информатика

Экономика

Статистика

Менеджмент

1

Быкова А.А.

отлично

отлично

хорошо

отлично

2

Якунина Ю.Ф.

хорошо

удовл.

удовл.

хорошо

3

Тарасова А.М.

удовл.

удовл.

удовл.

хорошо

4

Скородумова В.С.

хорошо

удовл.

удовл.

хорошо

5

Каренина Е.О.

хорошо

отлично

хорошо

хорошо

6

Хлебников М.А.

удовл.

удовл.

удовл.

удовл.

7

Александров Н.К.

хорошо

хорошо

хорошо

удовл.

8

Михайлова Т.А.

отлично

отлично

отлично

отлично

9

Хитров Ю.А.

отлично

отлично

отлично

хорошо

10

Афонина А.А.

хорошо

отлично

отлично

хорошо

11

Бородина А.А.

удовл.

удовл.

удовл.

удовл.

12

Воробьева С.В.

хорошо

отлично

удовл.

хорошо

13

Кустовая М.Н.

хорошо

хорошо

удовл.

хорошо

14

Петрова И.М.

отлично

хорошо

отлично

отлично

15

Соколова Е.В.

отлично

отлично

хорошо

отлично

16

Гречкина А.А.

отлично

хорошо

отлично

отлично

17

Воронцова Е.А.

отлично

хорошо

отлично

отлично

18

Долина А.Л.

хорошо

отлично

отлично

отлично

19

Алексеева Р.А.

отлично

отлично

отлично

отлично

20

Аистов Д.В.

удовл.

удовл.

хорошо

хорошо

21

Шариков К.Н.

отлично

хорошо

хорошо

хорошо

22

Смирнов А.Н.

отлично

отлично

удовл.

хорошо

23

Власова О.А.

отлично

хорошо

хорошо

отлично

24

Сорокина Ю.В.

хорошо

отлично

удовл.

отлично

Таблица 9

Таблица 10

Ведомость результатов сдачи сессии

 

Информатика

Экономика

Статистика

Менеджмент

Количество студентов, сдавших на "отлично"

Количество студентов, сдавших на "хорошо"

Количество студентов, сдавших на "удовлетворительно"

1.8.Задание № 8

Используя команду Подбор параметра меню СЕРВИС, выполните следующие задания:

1. На рынке телекоммуникационных услуг конкурируют две компании. Тарификация услуг компаний приведена в табл. 11. Определить, при каком количестве минут разговора в месяц стоимость услуг двух компаний одинакова. Для этого В табл. 11 добавить столбец «Количество минут» и объединяем две ячейки этого столбца (Компания 1 и Компания 2). В любой пустой ячейке этого листа вводим условие равноценности стоимости услуг компании:

,

где Х – количество минут разговора.

Таблица 11

Тарификация услуг

Компании

Абонентская плата за месяц (А), у.е.

Тариф по кредитной системе (Т^к), у.е./мин.

Тариф по авансовой системе (Т^а), у.е./мин.

Компания 1

10

0,10

0,20

Компания 2

14

0,075

0,20

2. Для Компании 1 определите, при каком количестве используемых минут разговора в месяц стоимость кредитной и авансовой системы оплаты услуг одинакова. Условие равноценности систем оплаты услуг компании 1:

.

3. Подберите такое значение Х, при котором тождество имело бы смысл.

1.9.Задание № 9

1. Выполнить ABC-классификацию и XYZ-классификацию материально-технических ресурсов, закупленных железной дорогой в 2005 году. Для ABC-классификации необходимо заполнить табл. 12.

Таблица 12

№ п/п

Вид закупаемой продукции

Стоимость продукции, руб

Удельный вес, %

Нарастающий итог

Группа

1

ЗАПАСНЫЕ ЧАСТИ ВАГОНОВ

6209754

2

ЗАПАСНЫЕ ЧАСТИ ЛОКОМОТИВОВ

3401395

3

ЗАПАСНЫЕ ЧАСТИ ПАССАЖИРСКИХ ВАГОНОВ

800822

4

ЗАПАСНЫЕ ЧАСТИ ПУТЕВОЙ ТЕХНИКИ

1340768

5

ИЗДЕЛИЯ ЛЕГКОЙ ПРОМЫШЛЕННОСТИ

1142375

6

ЛЕСОПИЛОМАТЕРИАЛЫ

372634

7

ЛОКОМОТИВНЫЕ БАНДАЖИ

345450

8

МАСЛА И СМАЗКА

1713324

9

МАТЕРИАЛЫ ВЕРХНЕГО СТРОЕНИЯ ПУТИ

17522662

10

МЕТАЛЛОПРОКАТ

2712934

11

МОСТОКОНСТРУКЦИИ

652396

12

ПОДШИПНИКИ ВАГОННЫЕ

2267295

13

ПОДШИПНИКИ ЛОКОМОТИВНЫЕ

319539

14

ПОСТЕЛЬНЫЕ ПРИНАДЛЕЖНОСТИ

412278

15

ПРОДУКЦИЯ МАШИНОСТРОЕНИЯ

664035

16

ПРОЧИЕ МАТЕРИАЛЫ

952054

17

ТОПЛИВО

37718932

18

ХИМИЧЕСКАЯ ПРОДУКЦИЯ

808228

19

ЦЕЛЬНОКАТАННЫЕ КОЛЕСА

8411294

20

ЭЛЕКТРОТЕХНИЧЕСКАЯ ПРОДУКЦИЯ

2946249

 

Общий итог

 

 

2. Рассчитать нарастающий итог значения критерия классификации (стоимости закупок) по номенклатурным позициям. Для этого необходимо отсортировать таблицу по убыванию стоимости продукции. Затем следует определить удельный вес значения критерия классификации. Нарастающий итог рассчитывается следующим образом. Значения удельного веса для первой позиции номенклатуры переписывается в столбец нарастающего итога. Для последующих номенклатурных позиций производится суммирование значения нарастающего итога предыдущей позиции со значением удельного веса текущей позиции.

3. Выделить группы классификации. В классическом АВС-методе классификация проводится на основе закона Парето, утверждающего, что 80% значений качественного критерия определяется 20% количества выбранной совокупности объектов.

Группа А – объекты, сумма долей с накопительным итогом которых, составляет первые 80 % от общей суммы параметров.

Группа В – следующие за группой А объекты, сумма долей с накопительным итогом которых, составляет от 80 % до 95 % от общей суммы параметров.

Группа С – оставшиеся объекты, сумма долей с накопительным итогом которых, составляет от 95 % до 100 % от общей суммы параметров.

4. На основе полученных результатов построить кумулятивную кривую (линию нарастающего удельного веса). Она строится на базе таблицы АВС-классификации в виде графика кривой взаимосвязи качественных и количественных значений. Показать на графике группы классификации.

5. Сформировать результаты анализа в виде табл. 13.

Таблица 13

Группа

Объем закупок, руб.

Доля,%

Количество видов закупаемой продукции

Доля,%

А

В

С

Итого

6. Выполнить XYZ-классификацию материально-технических ресурсов, закупленных железной дорогой в 2004 году. Для XYZ-классификации необходимо заполнить табл. 14.

Таблица 14

№ п/п

Вид закупаемой продукции

1 квартал

2 квартал

3 квартал

4 квартал

Коэффициент относительной вариации

XYZ-группа

1

МАСЛА И СМАЗКА

409980

404259

442632

456453

2

ЛОКОМОТИВНЫЕ БАНДАЖИ

87 418

76 094

95 042

86 896

3

ПОДШИПНИКИ ВАГОННЫЕ

470 149

603 033

597 086

597 027

4

ЗАПАСНЫЕ ЧАСТИ ПУТЕВОЙ ТЕХНИКИ

246 773

347 973

360 452

385 571

5

ЗАПАСНЫЕ ЧАСТИ ПАССАЖИРСКИХ ВАГОНОВ

180 536

205 385

249 473

165 427

6

ЦЕЛЬНОКАТАННЫЕ КОЛЕСА

1768403

1724509

2404502

2513879

7

ПОДШИПНИКИ ЛОКОМОТИВНЫЕ

55 635

76 948

97 078

89 878

8

ПРОДУКЦИЯ МАШИНОСТРОЕНИЯ

118 154

196 999

208 134

140 747

9

МАТЕРИАЛЫ ВЕРХНЕГО СТРОЕНИЯ ПУТИ

3236792

5268273

5581613

3435984

10

ТОПЛИВО

7945976

7703236

8743383

13326337

11

МОСТОКОНСТРУКЦИИ

223604

153514

162729

112550

12

ЗАПАСНЫЕ ЧАСТИ ЛОКОМОТИВОВ

550615

730386

1067203

1053190

13

ЛЕСОПИЛОМАТЕРИАЛЫ

109 492

120 881

90 562

51 699

14

ИЗДЕЛИЯ ЛЕГКОЙ ПРОМЫШЛЕННОСТИ

247 313

371 446

357 505

166 112

15

ХИМИЧЕСКАЯ ПРОДУКЦИЯ

162 405

272 025

247 647

126 151

16

ЗАПАСНЫЕ ЧАСТИ ВАГОНОВ

1170788

1830429

2197208

1011329

17

ЭЛЕКТРОТЕХНИЧЕСКАЯ ПРОДУКЦИЯ

626 619

908 694

1 034 359

376 578

18

МЕТАЛЛОПРОКАТ

463 337

899 139

953 430

397 028

19

ПРОЧИЕ МАТЕРИАЛЫ

87 384

307 973

350 532

206 165

20

ПОСТЕЛЬНЫЕ ПРИНАДЛЕЖНОСТИ

83 342

182 801

107 373

38 762

7. Определить коэффициент вариации для каждого объекта анализа – вида закупаемой продукции. Формула для расчета коэффициента вариации:

где х_i — значение параметра по оцениваемому объекту за i-тый период,

— среднее значение параметра по оцениваемому объекту анализа,

п — число периодов.

8. Определить группы X, Y и Z. Для этого сначала необходимо отсортировать объекты анализа по возрастанию значения коэффициента вариации. Рекомендуемое (классическое) распределение:

Группа X – объекты, коэффициент вариации по которым не превышает 10%.

Группа Y – объекты, коэффициент вариации по которым составляет 10% - 25%.

Группа Z – объекты, коэффициент вариации по которым превышает 25%.

1.10.Задание № 10

Определить финансово-экономическое состояние клиентов железнодорожного транспорта на основе анализа бухгалтерского баланса. Основные строки бухгалтерского баланса приведены в табл. 15.

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

Коэффициент текущей ликвидности Kтл:

где A_II- итоговая строка 2 – го раздела актива бухгалтерского баланса «Оборотные активы»,

П_V - итоговая строка 5 – го раздела пассива бухгалтерского баланса «Краткосрочные обязательства».

Таблица 15

Основные строки бухгалтерского баланса, руб.

Наименование строки

Код строки

Значение, тыс.руб

Клиент 1

Клиент 2

Клиент 3

АКТИВ

1.Внеоборотные активы

190

520024

1518280

104373

2.Оборотные активы

 

Запасы

210

368882

1432099

3555

Дебиторская задолженность

230, 240

1526894

1005818

19970

Краткосрочные финансовые вложения

250

0

0

672

Денежные средства

260

1762283

19433

2034

Прочие оборотные активы

270

0

0

0

Итог по разделу 2

290

3658059

2457350

26231

Баланс (сумма строк 190+290)

300

4178083

3975630

130604

ПАССИВ

3.Капитал и резервы

 

Итог по разделу 3

490

778173

3291282

117075

4. Долгосрочные обязательства

 

Итог по разделу 4

590

0

0

1949

5.Краткосрочные обязательства

 

Займы и кредиты

610

72256

370000

1100

Кредиторская задолженность

620

3327654

285648

10224

Задолженность участникам (учредителям) по выплате доходов

630

0

28700

256

Итог по разделу 5

690

3399910

684348

11580

Баланс (сумма строк 490+590+690)

700

4178083

3975630

130604

Коэффициент обеспеченности собственными средствами Ko:

где A_I- итоговая строка 1 – го раздела актива бухгалтерского баланса «Внеоборотные активы»,

П_III - итоговая строка 3 – го раздела пассива бухгалтерского баланса «Капитал и резервы».

Коэффициент абсолютной ликвидности Kал:

Коэффициент критической (срочной) ликвидности Kкл:

Коэффициент автономии Kа:

Каждый рассчитанный коэффициент имеет нормативное значение (табл. 16).

Рисунок 3.

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

Таблица 16

Нормативные значения коэффициентов платежеспособности

№ п/п

Коэффициент

Нормативное значение (интервал)

1

Коэффициент текущей ликвидности

1-2

2

Коэффициент обеспеченности собственными средствами

0,1-0,5

3

Коэффициент абсолютной ликвидности

0,2-0,5

4

Коэффициент критической (срочной) ликвидности

0,5-1

5

Коэффициент автономии

0,1-0,5

Допустим, если значения 4-х или 5-ти из рассчитанных коэффициентов попадают в установленные для них интервалы, то финансово-экономическое состояние клиента можно считать устойчивым и клиента платежеспособным. Если значения 3-х и более коэффициентов выходят за интервал нормативных значений, то клиент неплатежеспособен.

Порядок решения поставленной задачи с помощью средств Excel по данным табл. 14 (сначала по клиенту 1):

1. Рассчитать коэффициенты платежеспособности с использованием стандартных средств программы.

2. Определить платежеспособность клиента по каждому коэффициенту с помощью функции «ЕСЛИ».

3. Определить платежеспособность клиента в целом с помощью функции «ЕСЛИ».

Результаты решения необходимо представить в форме таблицы 16.

Таблица 17

Оценка финансового состояния клиента железнодорожного транспорта

№ п/п

Коэффициент

Нормативное значение

Расчетное значение

Финансовое состояние клиента по каждому коэффициенту

Финансовое состояние клиента в целом

1

Коэффициент текущей ликвидности

1-2

2

Коэффициент обеспеченности собственными средствами

0,1-0,5

3

Коэффициент абсолютной ликвидности

0,2-0,5

4

Коэффициент критической (срочной) ликвидности

0,5-1

5

Коэффициент автономии

0,1-0,5

Примечание: если расчетное значение коэффициента попадает в интервал нормативных значений, то в 5-том столбце ставиться 1, если не попадает, то ставиться 0. Если значения 4-х или 5-ти из рассчитанных коэффициентов попадают в установленные для них интервалы, то в 6-том столбце пишется «Устойчивое», в противном случае пишется «Неустойчивое». Для оценки финансового состояния остальных клиентов необходимо поменять ссылки в столбце "Расчетное значение" табл.17 с помощью команды "Найти и заменить".

1.11.Задание № 11

Определить наиболее «популярные» марки иностранных легковых автомобилей среди жителей Московского региона.

По специально разработанной анкете был проведен опрос 10 экспертов. В анкете опроса экспертам предлагалось проранжировать различные марки иностранных легковых автомобилей по уровню их популярности среди жителей Московского региона. Ранжирование - это процедура установления относительной популярности марок автомобиля на основе их упорядочения.

Таблица 18

Шкала ранжирования популярности марок авто

Уровень популярности марки авто

Оценка (ранг)

Максимальный

6

Самый высокий

5

Высокий

4

Средний

3

Малый

2

Минимальный

1

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

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

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

Таблица 19

Экспертная оценка популярности иностранных легковых автомобилей среди жителей Московского региона

Эксперты

Марки автомобилей

Хонда

Порше

БМВ

Митсубиси

Мерседес

Тойтота

Лексус

Ауди

1

3

4

4

4

5

5

5

6

2

5

4

3

3

5

5

5

5

3

4

4

2

4

5

6

6

6

4

2

4

3

4

6

5

5

5

5

3

3

2

4

4

5

5

6

6

4

4

3

4

5

5

5

6

7

4

4

2

4

5

5

6

5

8

4

4

3

3

6

5

5

6

9

3

3

3

4

6

5

5

5

10

5

3

3

3

6

4

5

5

(1)

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

где b_i – суммарная оценка для i-ой марки;

b_ij – оценка, присваиваемая i-ой марке автомобиля j-м экспертом.

n – количество экспертов.

Марка, у которой наибольшее значение b_i, является наиболее популярной среди жителей Московского региона.

2. Находится среднее арифметическое значение популярности каждой марки автомобиля:

(2)

где Х_срi – среднее арифметическое значение i-ой марки автомобиля,

3. Определяется дисперсия (D), которая характеризует меру отклонения фактической оценки данной марки автомобиля от среднего значения:

4. Более точно степень отклонения популярности определенной марки от среднего значения показывает среднеквадратичное отклонение (), которое представляет собой квадратный корень из дисперсии:

(4)

5. Показателем колеблемости, оценивающим типичность средних величин является коэффициент вариации ():

(5)

Значение коэффициента вариации более 40% свидетельствует о большой колеблемости популярности определенной марки автомобиля, то есть мнения экспертов относительно этой марки автомобиля значительно расходятся.

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

(6)

где: ²_f – фактическая дисперсия суммарных (упорядоченных) оценок, данных экспертами;

²_max – дисперсия суммарных (упорядоченных) оценок в случае полной согласованной мнений экспертов;

m – количество оцениваемых марок автомобилей;

n – количество экспертов.

Значение коэффициента конкордации изменяется в диапазоне от 0 до 1. Если коэффициент конкордации равен нулю, то согласованность отсутствует. При коэффициенте конкордации равном единице связь между оценками экспертов полная. Для использования полученных от экспертов оценок необходимо, чтобы коэффициент конкордации был больше 0,5, то есть при W=0,5 считается, что мнения экспертов согласованы.

Порядок решения поставленной задачи с помощью средств Excel по данным табл. 19:

1. Суммарный ранг популярности марки автомобиля определяется с помощью функции «СУММ».

2. Среднее арифметическое значение популярности каждой марки автомобиля определяется с помощью функции «СРЗНАЧ».

3. Дисперсия определяется с помощью функции «ДИСП».

4. Среднеквадратичное отклонение определяется с помощью функции «СТАНДОТКЛОН».

5. Ввод формул для определения коэффициентов вариации и конкордации производится с использованием стандартных средств.

6. Место в рейтинге популярности определяется по значению суммарного ранга с помощью команды "РАНГ".

Результаты обработки экспертного опроса необходимо представить в форме табл. 20.

Таблица 20

Результаты обработки экспертного опроса

Показатели

Марки автомобилей

Хонда

Порше

БМВ

Митсубиси

Мерседес

Тойота

Лексус

Ауди

Суммарный ранг

 

 

 

 

 

 

 

 

Средний ранг

 

 

 

 

 

 

 

 

Дисперсия

 

 

 

 

 

 

 

 

Среднеквадратическое отклонение

 

 

 

 

 

 

 

 

Коэффициент вариации

 

 

 

 

 

 

 

 

Коэффициент конкордации

Место в рейтинге популярности

 

 

 

 

 

 

 

 

2. Методические указания к выполнению заданий в среде ms Excel

Использование абсолютных и относительных ссылок

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

Относительные ссылки.   Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейку, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании относительной ссылки из ячейки B2 в ячейку B3, она автоматически изменяется с =A1 на =A2.

Абсолютные ссылки.   Абсолютная ссылка ячейки в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, и для использования абсолютных ссылок надо выбрать соответствующий параметр. Например, при копировании абсолютной ссылки из ячейки B2 в ячейку B3, она остается прежней =$A$1.

Смешанные ссылки.   Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании смешанной ссылки из ячейки A2 в ячейку B3, она изменяется с =A$1 на =B$1.

Таким образом, используются следующие виды ссылок.

$A$1 (абсолютный столбец и абсолютная строка)

$A$1

A$1 (относительный столбец и абсолютная строка)

C$1

$A1 (абсолютный столбец и относительная строка)

$A3

A1 (относительный столбец и относительная строка)

C3

Для преобразования типа ссылки может быть использована клавиша F4.

Работа с листами в групповом режиме

Групповой режим работы с листами представляет собой процедуру одновременного внесения одинаковых изменений в нескольких листах одной книги. Для выполнения данной процедуры необходимо выделить листы, удерживая нажатой клавишу CTRL или SHIFT.

Основные функции используемы для выполнения заданий

Функция ЕСЛИ

Функция ЕСЛИ используется при проверке условий для значений и формул. Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

Лог_выражение  — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 — это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае — ЛОЖЬ. Этот аргумент может быть использован в любом операторе сравнения.

Значение_если_истина   — это значение, которое возвращается, если лог_выражение равно ИСТИНА. Например, если этот аргумент — строка «В пределах бюджета» и лог_выражение равно ИСТИНА, тогда функция ЕСЛИ отобразит текст «В пределах бюджета». Если лог_выражение равно ИСТИНА, а значение_если_истина пусто, то возвращается значение 0. Чтобы отобразить слово ИСТИНА, необходимо использовать логическое значение ИСТИНА для этого аргумента. Значение_если_истина может быть формулой.

Значение_если_ложь   — это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. Например, если этот аргумент — строка «Превышение бюджета» и лог_выражение равно ЛОЖЬ, то функция ЕСЛИ отобразит текст «Превышение бюджета». Если лог_выражение равно ЛОЖЬ, а значение_если_ложь опущено (то есть после значение_если_истина нет точки с запятой), то возвращается логическое значение ЛОЖЬ. Если лог_выражение равно ЛОЖЬ, а значение_если_ложь пусто (то есть после значение_если_истина стоит точка с запятой с последующей закрывающей скобкой), то возвращается значение 0. Значение_если_ложь может быть формулой.

Функция СЧЁТЕСЛИ

Функция СЧЁТЕСЛИ подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.

СЧЁТЕСЛИ(диапазон;критерий)

Диапазон — диапазон, в котором нужно подсчитать ячейки.

Критерий  — критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки".

Функция СУММЕСЛИ

Функция СУММЕСЛИ суммирует ячейки, заданные критерием.

СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)

Диапазон — диапазон вычисляемых ячеек.

Критерий — критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 32, "32", ">32", "яблоки".

Диапазон_суммирования — фактические ячейки для суммирования.

Функция СРЗНАЧ

СРЗНАЧ(число1; число2; ...)

Возвращает среднее (арифметическое) своих аргументов.

Число1, число2, ...   — это от 1 до 30 аргументов, для которых вычисляется среднее.

Финансовые функции

Финансовая функция БЗ

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

БЗ (норма, число_периодов, выплата, нз, тип)

Существуют различные варианты использования этой функции при решении конкретных задач.

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

,

где - будущая стоимость вклада или займа; - текущая стоимость вклада (займа); n – общее число периодов начисления процентов; r – процентная ставка по вкладу (займу).

В этом случае на рабочем листе формула примет вид:

БЗ (норма, число_периодов, нз)

Бывают ситуации, когда платежи производятся систематически, а не один раз, как в предыдущем варианте. Эти платежи могут осуществляться в начале каждого расчётного периода (так называемые «обязательные платежи» или пренумерандо) или в конце («обычные платежи» или постнумерандо) в течение n периодов. В каждом периоде вносится одинаковая сумма. Необходимо найти их будущую стоимость в конце n-го периода для обоих случаев. Отличие в расчёте при этом заключается в том, что во втором случае не происходит начисления процентов на последний вклад, т.е. все вклады пренумерандо увеличиваются на сложные проценты на один расчётный период больше, чем вклады постнумерандо.

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

,

где - будущая стоимость серии фиксированных периодических платежей; - фиксированная периодическая сумма платежа; n– общее число периодов выплат; r– постоянная процентная ставка.

В этом случае на рабочем листе формула примет вид:

БЗ (норма, число_периодов, выплата, ,1)

Здесь тип=1, т.к. он определяет время начисления процентов: в конце/начале (0/1) периода. Если тип опущен, он считается равным 0.

Для расчёта будущей стоимости серии фиксированных периодических платежей, если выплаты происходят в конце периода, то формула модифицируется:

В этом случае на рабочем листе Excel формула примет вид:

БЗ (норма, число_периодов, выплата, ,0) или

БЗ (норма, число_периодов, выплата)

При решении конкретной задачи вместо названий аргументов следует записать соответствующие числа. Результаты должны совпадать с расчётом формул.

Функция ПЗ

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

Данная функция используется в следующих расчётах:

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

В этом случае на рабочем листе формула примет вид:

ПЗ(норма, кпер, , бс)

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

В этом случае на рабочем листе формула примет вид:

ПЗ(норма, кпер, выплата, , 1)

Для расчёта текущей стоимости постоянных периодических выплат, если они происходят в конце периода (обычные платежи) формула модифицируется

Соответствующая этому расчёту формула примет вид:

ПЗ(норма, кпер, выплата)

Функция НПЗ

Функция вычисляет чистую текущую стоимость (NPV) периодических платежей переменной величины как сумму ожидаемых доходов и расходов, дисконтированных нормой процента r:

,

где value_i – значения выплат и поступлений.

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

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

Функция ПРПЛТ

Функция ПРПЛТ возвращает сумму платежей процентов по инвестиции за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки. Для получения более полного описания смысла аргументов функции ПРПЛТ и более подробной информации о функциях, связанных с ежегодными выплатами, см. справку по функции ПС.

ПРПЛТ(ставка;период;кпер;пс;бс;тип)

Ставка — процентная ставка за период.

Период — это период, для которого требуется найти платежи по процентам; должен находиться в интервале от 1 до «кпер».

Кпер — общее число периодов выплат годовой ренты.

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

Бс — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (например, бзс для займа равно 0).

Тип — число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент «тип» опущен, то он полагается равным 0.

Функция ПЛТ

Функция ПЛТ возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.

ПЛТ(ставка;кпер;пс;бс;тип)

Функция ОБЩПЛАТ

Функция ОБЩПЛАТ возвращает кумулятивную (нарастающим итогом) величину процентов, выплачиваемых по займу в промежутке между двумя периодами выплат.

ОБЩПЛАТ(ставка;кол_пер;нз;нач_период;кон_период;тип)

Кол_пер  — это общее количество периодов выплат.

Нз  — это стоимость инвестиции на текущий момент.

Нач_период  — это номер первого периода, включаемого в вычисления. Периоды выплат нумеруются, начиная с 1.

Кон_период  — это номер последнего периода, включаемого в вычисления.

Тип  — это выбор времени платежа.

Функция ОСПЛТ

Функция ОСПЛТ возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки. Более подробное описание аргументов функции ОСПЛТ см. в описании функции ПС.

ОСПЛТ(ставка;период;кпер;пс;бс;тип)

Функция ОБЩДОХОД

Функция ОБЩДОХОД возвращает кумулятивную (нарастающим итогом) сумму, выплачиваемую в погашение основной суммы займа в промежутке между двумя периодами.

ОБЩДОХОД(ставка;кол_пер;нз;нач_период;кон_период;тип)

Статистические функции

Функция ДИСП

Функция ДИСП оценивает дисперсию по выборке.

ДИСП(число1;число2; ...)

Число1, число2, ...  — это от 1 до 30 числовых аргументов, соответствующих выборке из генеральной совокупности.

Функция СТАНДОТКЛОН

Функция СТАНДОТКЛОН оценивает стандартное отклонение по выборке. Стандартное отклонение — это мера того, насколько широко разбросаны точки данных относительно их среднего.

СТАНДОТКЛОН(число1; число2; ...)

Число1, число2, ... — от 1 до 30 числовых аргументов, соответствующих выборке из генеральной совокупности. Вместо аргументов, разделенных точкой с запятой, можно также использовать массив или ссылку на массив.

Учебно-методическое издание

Шобанов Андрей Витальевич

Епишкин Илья Анатольевич

Струкова Елена Викторовна

ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ В ЭКОНОМИКЕ

Методические указания и задания для практических занятий

для студентов экономических специальностей

Подписано в печать Тираж экз.

Усл. печ. л. Заказ Изд. № Формат

127994, Москва, ул. Образцова, 15. Типография МИИТа