Реферат
Решение экономических задач программными методами
2010
Введение
Целью данной работы является углубленное изучение возможностей Excel и встроенного языка VisualBasicforApplication.
Электронные таблицы Excel - одна из составных частей пакета прикладных программ MicrosoftOffice, работающего в среде Windows. Она предназначена для обработки числовых данных, проведения математического моделирования различных процессов, изготовления различных документов и форм.
Excel предлагает широкий набор функциональных средств по обработке табличных данных:
- создание и редактирование электронных таблиц с применением богатого набора встроенных функций;
- оформление и печать электронных таблиц;
- построение диаграмм и графиков разной степени наглядности и детализации;
- работа с электронными таблицами как с базами данных: фильтрация, сортировка, создание итоговых и сводных таблиц, консолидация данных из разных таблиц и т.д.;
- решение экономических задач путем подбора параметров;
- решение оптимизационных задач;
- численное решение разнообразных математических задач;
- статистическая обработка данных;
Практическая часть
1 Работа с Microsoft Excel
1.1 Консолидация данных (связь таблиц)
2. Создана таблица и заполнены столбцы «Выручка» и «Итог» (таб. 1.1.1).
Таблица 1.1.1 – Торговая фирма «Шмидт и сыновья»
| Исходные данные за январь | |||
| Наименование продукции | Цена в У.Е. | Продано | Выручка |
| Телевизоры | 350 | 20 | 7000 |
| Видеомагнитофоны | 320 | 65 | 20800 |
| Музыкальные центры | 750 | 15 | 11250 |
| Видеокамеры | 970 | 30 | 29100 |
| Видеоплейеры | 200 | 58 | 11600 |
| Аудиоплейеры | 40 | 18 | 720 |
| Радиотелефоны | 390 | 11 | 4290 |
| Итого | 84760 | ||
Выручка посчитана путем умножения цены на количество проданного товара. Этот лист переименован в «Январь».
3. Два других листа названы «Февраль» и «Март». Сформированная таблица скопирована на эти рабочие листы. Изменены данные второго и третьего столбцов.
4. Создан макет таблицы сводных показателей на четвертом листе («Квартал. Способ 1
»). Связь между листами задана путем введения в один лист формулы связи со ссылкой на ячейку в другом листе =НазвЛиста!АдрЯч
(например: =СУММ(Январь!C4;Февраль!C4;Март!C4)). Заполнена данная таблица путем суммирования данных, находящихся в соответствующих ячейках листов показателей за январь – март.
5. На пятом листе («Квартал. Способ 2
») получена таблица путем консолидации. Выбраны Данные | Консолидация
(рис. 1.1.1). В поле Ссылка
определены источники данных, добавляя в Список диапазонов
. Обязательно нужно поставить флажок напротив опции Создать связи с исходными данными
.
Рисунок 1.1.1 – Диалоговое окно «Консолидация»
1. Создана таблица исходных данных на листе «Данные».
2. Созданы макеты таблиц для квартальных данных на четырех разных листам одной книги.
3. Из исходной таблицы разнесены данные по таблицам.
4. Определены средние цены производителей по каждой позиции, используя формулу СРЗНАЧ()
(например: =СРЗНАЧ(C5:C7)).
5. Найдено соотношение цен каждого вида энергоресурсов с ценой на нефть в процентах. Для этого цены отдельного вида поделены на цену нефти, тип приведен к процентам.
6. В другом файле сформирована таблица «Среднегодовые цены» с помощью консолидации. Для переноса название строк и столбцов использована опция
Использовать в качестве имен
.
1.2 Составление штатного расписания больницы. Создание простых макросов
1. Заполнена таблица (таб. 1.2.1). Основным считается оклад санитарки. Коэффициенты назначаются следующим образом:
- медсестра должна получать в 1,5 раза больше санитарки;
- врач – в 3 раза больше санитарки;
- заведующий отделением – на $30 больше, чем врач;
- заведующий аптекой – в 2 раза больше санитарки;
- заведующий хозяйством – на $40 больше медсестры;
- главный врач – в 4 раза больше санитарки;
- заведующий больницей – на $20 больше главного врача.
Оклад находится по формуле: Оклад=А*(Оклад санитарки)+В
.
Таблица 1.2.1 – Штатное расписание больницы
| Должность | Количество сотрудников | Коэффициенты оклада | Оклад | Итого | |
| А | В | ||||
| Санитарка | 7 | 0 | 0 | 133,11 | 931,76 |
| Медсестра | 9 | 1,5 | 0 | 199,66 | 1796,96 |
| Врач | 11 | 3 | 0 | 399,32 | 4392,57 |
| Заведующий аптекой | 1 | 2 | 0 | 266,22 | 266,22 |
| Заведующий отделением | 3 | 3 | 30 | 429,32 | 1287,97 |
| Главный врач | 1 | 4 | 0 | 532,43 | 532,43 |
| Заведующий хозяйством | 1 | 1,5 | 40 | 239,66 | 239,66 |
| Заведующий больницей | 1 | 4 | 20 | 552,43 | 552,43 |
| 10000,00 | |||||
2. Подбор оклада санитарки произведен при помощи функции «Подбор параметра»: Сервис | Подбор параметра
(рис. 1.2.1).
Рисунок 1.2.1 – Диалоговое окно «Подбор параметра»
В поле Установить в ячейке
введен адрес ячейки, где вычисляется зарплата всех сотрудников больницы ($F$12). В поле Значение
введено значение месячного фонда (10000). В поле Изменяя значение ячейки
введен адрес ячейки с окладом санитарки ($E$4).
3. Данная таблица скопирована на второй лист. Чтобы записать макрос выбрана команда Сервис | Макрос | Начать запись
. В диалоговом окне Запись макроса
(рис 1.2.2) задано имя макроса «Staff», в поле Сохранить в:
Эта книга. После нажатия кнопки ОК
все действия над ячейками записываются.
4.
Рисунок 1.2.2 – Диалоговое окно «Запись макроса»
4. Проведена операция «Подбор параметра».
5. Остановлена запись макроса.
6. Для просмотра макроса выполнено Сервис | Макрос | Макросы |
, выбран записанный макрос «Staff» и нажат кнопка Изменить
. Открывается окно редактора VBA с текстом макроса.
Записанный макрос имеет следующий вид:
Sub Staff()
Range("F12").GoalSeek Goal:=10000, ChangingCell:=Range("E4")
End
Sub
В данном окне можно редактировать макрос.
7. Размещена кнопка на рабочем листе, для этого вынесена панель инструментов Формы
при помощи Сервис | Настройка | Панель инструментов | Формы
. На панели выбрана форма Кнопка
и расположена на листе щелчком мыши. Одновременно с этим открывается диалоговое окно Назначить макрос объекту
(рис. 1.2.3) и в поле Имя макроса
выбрано имя данного макроса «Staff».
1.3 Создание отчетно-аналитической ведомости о работе сети магазинов
2. Создана и заполнена таблица выручки за первый квартал шести магазинов сети фирмы «Шмидт и сыновья», внеся данные от 500 до 2000 тыс. руб.
3. Использована функция СУММ
(=СУММ(B4:D4)) для подсчета суммарной выручки каждого магазина и суммарной выручки в каждом месяце.
4. С помощью функции СЧЕТЕСЛИ
(=СЧЁТЕСЛИ(F4:F9;">4000")) подсчитано, сколько магазинов перевыполнили план, приняв число 4000 тыс. руб. за плановую квартальную выручку.
5. Функцию СРЗНАЧ
(=СРЗНАЧ(B4:B9)) использована для подсчета ежемесячной средней и средней выручки каждого магазина.
6. Используя функцию РАНГ
(=РАНГ($F$4:$F$9;$F$4:$F$9)), найдено место каждого магазина по объему продаж.
7. Определено для данного множества суммарных выручек, сколько значений попадает в интервалы от 0 до 1000, от 1001 до 2000, от 2001 до 3000, от 3001 до 4000 и свыше 4000 тыс. руб., используя функцию ЧАСТОТА
(=ЧАСТОТА(F4:F9;L4:L7)).
Данная функция возвращает распределение частот в виде вертикального массива. Исходными данными является массив данных, для которого вычисляются частоты (F4:F9), и множество интервалов (массив карманов), в которых группируются значения из массива данных (L4:L7). Для начала выделена область, куда попадут результаты вычисления, а потом заданы исходные данные. Выход из функции осуществлен путем нажатия клавиш Ctrl
+
Shift
+
Enter
.
8. Вычислены эти же частоты с помощью диалогового окна Анализ данных
(Сервис | Анализ данных | Гистограмма
) (рис. 1.3.1, 1.3.2). В меню Сервис отсутствовала команда Анализ данных,
для установки было выполнено: Сервис | Надстройки | Пакет анализа
.
|
Рисунок 1.3.1 - Диалоговое окно «Анализ данных» |
Рисунок 1.3.2 - Диалоговое окно «Гистограмма» |
Построены гистограммы «Частота» (Объем реализации) и диаграмма «Выручка магазинов» (в процентах) с помощью Мастера диаграмм
(рис. 1.3.3, 1.3.4). Для гистограммы и диаграммы исходными данными являются столбцы «Итого» (F4:F9) и «Название магазина» (А4:А9). В параметрах диаграммы отмечено Включить в подписи доли
. Изменен внешней вид диаграмм.
Рисунок 1.3.3 – «Объем реализации» Рисунок 1.3.4 – «Выручка магазинов»
1.4 Некоторые финансовые функции
Excel
1. Внесены исходные данные в таблицу (таб. 1.4.1).
Таблица 1.4.1 – Исходные данные
| А | р1
|
р2
|
р3
|
р4
|
р5
|
i |
| 10 000,00р. | 12% | 13% | 14% | 15% | 16% | 6% |
2. Для расчета суммы на счету в банке в конце пятого года использована финансовая функция, кот возвращает будущее значение единовременного вложения при переменной процентной ставке
БЗРАСПИС
(Первичное; План),
где Первичное
– числовое значение, представляющее собой исходную сумму средств;
План
– массив процентных ставок, используемых за рассматриваемый период.
В данной задаче она принимает вид: БЗРАСПИС(B2;С2:G2).
3. Просчитано сумма, которая могла получиться при постоянной процентной ставке i
%
при помощи функции
БС(Норма; Кпер; Выплата;Нз; Тип),
где Норма
– процентная ставка за период (в процентном формате или в долях);
Кпер
– общее число платежных периодов, по истечению которых вы хотите определить объем имеющихся средств;
Выплата
– величина постоянных периодических платежей;
Нз
– начальное значение (текущая стоимость) вклада;
Тип
– параметр, определяющий, когда вносят платежи: в начале (=0) или в конце (=1). По умолчанию =0.
4. Найдена сумма, которую надо было бы поместить на счет при постоянной процентной ставке, чтобы обеспечить сумму на счету к концу пятого года, с помощью сервисной функции Подбор параметра
(рис 1.4.1).
Рисунок 1.4.1 – Диалоговое окно «Подбор параметра»
1. Внесены исходные данные в таблицу (таб. 1.4.2).
Таблица 1.4.2 – Исходные данные
| N, год | P, тыс. руб. | A, тыс. руб. | Ставка |
| 11 | 360,00 | 41,00 | 7,00% |
2. Подсчитана прибыль в случае, когда деньги берутся в долг (91 тыс. руб.).
3. Подсчитана прибыль, полученная от вложения денег в банк под 7% годовых, с помощью функции
ПС (Ставка; Кпер; Выплата; Бс; Тип),
где Ставка
– процентная ставка за период (в процентном формате или в долях);
Кпер
– общее число периодов выплат;
Выплата
– величина постоянных периодических платежей;
Бс
– баланс наличности, который нужно достичь после последней выпл
Тип
– 0 или 1, Если 0 – оплата производится в конце периода, если 1, то в начале.
Данная функция возвращает текущий объем вклада на основе постоянных периодических платежей (или, иными словами, сумму всех будущих платежей) В данной задаче он составляет 355,57 тыс. руб.
4. Сделан вывод на основе полученных результатов, какой из данных случаев более выгодный. В этом случае выгоднее положить в банк.
5. С помощью Диспетчера сценариев
проанализирована ситуация для нескольких возможных вариантов изменения параметров A и N (рис. 1.4.2).
Рисунок 1.4.2 – Диалоговое окно «Диспетчер сценариев»
Создание сценариев произведено с помощью последовательности команд Сервис | Сценарии | Диспетчер сценариев | Добавить
(рис 1.4.3).
Рисунок 1.4.3 – Диалоговое окно «Добавление сценария»
В поле Изменяемые ячейки
указаны те ячейки, в которых находятся параметры задачи.
После нажатия кнопки ОК,
в диалоговом окне Значения ячеек сценария
введены значения параметров для сценариев. С помощью диалогового окна Диспетчер сценариев
создано три сценария.
С помощью кнопки Отчет
открывается диалоговое окно Отчет по сценарию
, где определен тип отчета (Структура
) и заданы ячейки, где вычисляется результат.
1. Внесены исходные данные в таблицу (таб. 1.4.3).
Таблица 1.4.3 – Исходные данные
| N | A | P1
|
P2
|
P3
|
P4
|
P5
|
| 3 | 36000 | 10000 | 15000 | 21000 | 0 | 0 |
2. Выручка от инвестиций посчитана с помощью функции, возвращающей чистую текущую величину вклада (инвестиции), вычисленного на основе ряда последовательных (неравномерных) поступлений денежных средств.
ЧПС (Ставка; Значение1; Значение 2; …Значение
N
),
где Ставка
– процентная ставка за период;
Значения
– до 29 аргументов (могут быть массивы), представляющих поступления (доходы со знаком "+", расходы со знаком "-").
3. С помощью функции считается выручка от вложения денег в банк
БС (Ставка; Кпер; Плт; Пс; Тип),
где Ставка
– процентная ставка за период;
Кпер
– общее число выплат;
Плт
– выплатаю производимая в каждый период и не меняющаяся за все время выплаты;
Пс
– приведенная (нынешняя) стоимость или общая сумма, которая на настоящий равноценна серии будущих выплат;
Тип
- 0 или 1, Если 0 – оплата производится в конце периода, если 1, то в начале.
В данной задаче функции приобретают вид ЧПС(0;D2;E2;F2) и БС(I2;B2;;-C2).
4. С помощью функции Подбор параметра
определена ставка, при которой выгоднее деньги вложить в инвестиционный проект 8,5%.
1. Внесены исходные данные в таблицу (таб. 1.4.4).
Таблица 1.4.4 – Исходные данные
| N, год | A, тыс. руб. | p, % | i, % |
| 11 | 360 | 25% | 14% |
2. Сумма ссуды для ежегодных выплат найдена по формуле, функция в которой вычисляет величину постоянной периодической выплаты ренты, регулярных платежей по займу при постоянной процентной ставке.
ПЛТ (Ставка; Кпер; Пс; Бс; Тип)*
N
,
где Ставка
– процентная ставка за период (в процентном формате или в долях);
Кпер
– общее число периодов выплат;
Пс
– общая сумма всех будущих платежей с настоящего момента.
Выплата
– величина постоянных периодических платежей;
Бс
– баланс наличности, который нужно достичь после последней выплаты (если опущен, то 0);
Тип
– 0 или 1, Если 0 – оплата производится в конце периода, если 1, то в начале;
N
– количество лет.
3. Сумма для ежемесячных выплат ссуды найдена
ПЛТ
(i/12; N*12; -(A-A*p)) * N * 12
4. Комиссионные найдены как разность ссуды и стоимостью квартиры.
5. Результаты оформлены в виде таблицы (таб. 1.4.5)
Таблица 1.4.5 – Результаты
| Ежемесячные выплаты | Ежегодные выплаты | |
| Ссуда | 530,56 | 544,68 |
| Комиссионные | 170,56 | 184,68 |
1. Внесены исходные данные в таблицу (таб. 1.4.6).
Таблица 1.4.6 – Исходные данные
| N, год | A, млн. руб. | P, млн. руб. |
| 30 | 30,0 | 1,7 |
2. Процентная ставка определена по формуле
СТАВКА (Кпер; Плт; Пс; Бс; Тип)
1.5 Моделирование развития финансовой пирамиды
1. Занесены исходные данные (таб. 1.5.1)
Таблица 1.5.1 – Исходные данные
| Число жителей в городе | M | 1000000 | |
| Коэффициент ажиотажа | KA
|
0,0000001 | |
| Ежедневные расходы (руб.) | R | 300 | |
| Среднее время между покупкой и продажей акции (дни) | T | 50 | |
| Норма прибыли (ежедневный процент от суммы в кассе) | S | 3 | |
| Состояние на первый день: | |||
| начальный капитал (руб.) | П1
|
70000 | |
| число купивших акции в первый день | SNK1
|
7 | |
2. Сформирована таблица из граф: День; Курс продаж; Продано в день; Продано всего; Курс покупки; Куплено в день; Куплено всего; Сумма в кассе; Доход в день; Доход всего
. Ссылки на исходные данные осуществлены в виде абсолютных ссылок.
3. Заполнены графы
- День
с помощью авто заполнения с 1 до 365.
- Курс продажи
с 1,05 до 8,33 с шагом 0,02.
- Курс покупки
с 1,00 до 8,28 с шагом 0,02.
- Продано в день
акций:
-
,
где M
– число жителей в городе;
NKD
– общее число купивших акции на день D;
KA
– коэффициент ажиотажа.
Число акций не может быть дробным, поэтому к нему применяется функция ОКРУГЛВВЕРХ()
(ОКРУГЛВВЕРХ(C3+$H$3*($H$2-C3)*C3;0)).
- Продано всего
просчитана как сумма проданных акций.
- Куплено в день
заполнена путем: если D ≤ T (среднее время между покупкой и продажей акций жителями), то NPD
+1
= 0, в противном случае смещается на T дней от продажи. (СМЕЩ (NKD
; -50,0)).
- Количество купленных
акций также считается суммой.
- Сумма в кассе
в первый день равна 70000 (П1
). Следующие дни просчитаны:
,
где Σ
– норма прибыли;
R
– ежедневные расходы.
- Доход в день
просчитан как
- Доход всего
просчитан как сумма дохода предыдущих дней.
- Доход организации
равен произведению суммы в кассе на норму прибыли (=H4*'Исхидные данные'!$H$6/100).
4. Построен график изменения количества денег в кассе и доходов организаторов пирамиды за 50 дней и за год (Приложение А, Б).
5. Определен максимальный доход фирмы с помощью функции (3369519,74 руб.)
МАКС (Число1; Число 2; …)
6. Определен день достижения данного дохода с помощью функции (149 день)
ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив;
Тип_ сопоставления),
где Искомое_значение
– значение, используемое при поиске нужного значения в массиве;
Просматриваемый_массив
– непрерывный диапазон ячеек, просматриваемый в поиске искомого значения.
Тип_сопоставления
– число (1,0,-1), определяющее возвращаемое значение.
7. Определена минимальная сумма в кассе.
8. Подобрано такое минимальное значение начального капитала, которое бы позволило не уйти в отрицательную сумму в кассе на начальном этапе развития, с помощью функции Подбор параметра
(9866,89 руб.). При этом минимальная сумма в кассе должна стать равной 0,00р.
9. Прослежено изменение дохода организатора пирамиды в определенный день Х, изменяя исходные данные. День X задан самостоятельно (50).
10. Полученные данные занесены в таблицу (таб. 1.5.2).
Таблица 1.5.2 – Результаты исследования
| Изменяемый параметр | Увеличиваемый параметр | Уменьшаемый параметр | ||||
| Значение | День Х | Доходы на день Х | Значение | День Х | Доходы на день Х | |
| Исходное значение | 735,83 | 735,83 | ||||
| M | 2000000 | 50 | 17961,56 | 600000 | 50 | 377,35 |
| KA
|
0,00000017 | 50 | 6446,68 | 0,00000006 | 50 | 377,35 |
| R | 370 | 50 | 681,56 | 250 | 50 | 774,59 |
| T | 55 | 50 | 735,83 | 45 | 50 | 733,93 |
| S | 6% | 50 | 739,86 | 2,5% | 50 | 721,08 |
| П1
|
100000 | 50 | 938,16 | 65000 | 50 | 702,11 |
| SNK1
|
12 | 50 | 989,59 | 5 | 50 | 648,65 |
Процесс описывается дифференциальным уравнением.
Также найдены максимальный доход и день его достижения, минимальное значение первоначального капитала.
Часть 2. Создание приложения в VBA
Цель работы:
создать приложение в VBA, позволяющее определить и вывести в ячейки Excel количество лет, кварталов, месяцев, недель и дней, прошедших между двумя датами.
Ход работы:В ходе выполнения производственной практики было создано приложение в VBA, позволяющее посчитать количество лет, кварталов, месяцев, недель и дней, прошедшее между двумя заданными датами.
Текст
программы
Private Sub CommandButton1_Click()
d1 = DTPicker1.Value
d2 = DTPicker2.Value
TextBox1.Text = DateDiff("yyyy", d1, d2) / разность между двумя заданными годами
TextBox2.Text = DateDiff("m", d1, d2) / разность между двумя заданными месяцами
TextBox3.Text = DateDiff("q", d1, d2) / разность между двумя заданными кварталами
TextBox4.Text = DateDiff("d", d1, d2) / разность между двумя заданными днями
End Sub
Private Sub CommandButton2_Click()
UserForm1.Hide
End Sub
Заключение
В результате проделанной работы получены теоретические и практические знания о возможностях программного продукта Exсel. Для выполнения практических задач были использованы разнообразные функции, которые позволяют быстро и оптимально получить решение.
Проделано следующее:
- Закрепление навыков составления итоговых таблиц, связывая данные из других источников;
- Использован MacroRecorder для записи простого макроса;
- Освоена технологии бизнес-анализа данных в Excel, используя функции пакета Анализ данных и некоторые статистические функции;
- С помощью встроенных функций Excel решены финансовые задачи;
- Знакомство с возможностями Диспетчера сценариев;
- С помощью сервисной программы Excel Поиск решения решены экономические задачи и проведен анализ решения типа «что-если».
Список литературы
1. Миньков С.Л. Excel: Лабораторный практикум. – Томск: Томский медвузовский центр дистанционного образования, 2000.
2. Гарнаев А.Ю. Использование MSExcel и VBA в экономике и финансах. – С-Пб.: БХВ – Санкт-Петербург, 1999.
3. Овчаренко Е.К., Ильина О.П, Балыбердин Е.В., Финансово- экономические расчеты в Excel. Изд. 3-е – М.: Филинъ, 1999.
4. Матвеев Л.А. Компьютерная поддержка решений.- С-Пб., Специальная литература, 1998.
5. Гусева О.Л. Миронова Н.Н. Excel для Windows. Практические работы// Информатика и образование.- 1996.- №2-6