Организация вычислений в среде MS Excel. Формулы и функции

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

- функции  для анализа аннуитетов и инвестиционных  проектов;

- функции  для анализа ценных бумаг;

- функции  для расчета амортизационных  платежей;

- вспомогательные  функции.

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

.

Рис.6 Получение дополнительной справки по функции

 

 

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

Количественный  анализ таких операций сводится к  исчислению следующих основных характеристик:

- текущей  величины потока платежей (Present value - Pv);

- будущей  величины потока платежей (Future value - Fv);

- величины  отдельного платежа (payment - R);

- нормы  доходности в виде процентной  ставки (interest rate ~ r);

- числа  периодов проведения операции (число  лет, месяцев).

К этой группе также относятся и функции, которые  позволяют эффективно осуществить  расчет наиболее широко применяемых  критериев эффективности инвестиционных проектов, - NPV, IRR и т. п. 
Функции этой группы используют сложные итерационные алгоритмы для исчисления соответствующих показателей. При этом делаются некоторые допущения[2, с. 56]:

- потоки  платежей на конец (начало) периода  известны;

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

 

 

 

 

 

 

 

 

 

 

 

2 Практическа часть

 

2.1 Постановка задачи

2.1.1 Цель решения задачи

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

Цель  решения данной задачи состоит в  правильности расчетов  расхода  и оплаты электроэнергии за месяц.

 

2.1.2 Условия задачи

Входной оперативной информацией служит ведомость учета показадия счетчиков электроэнергии, садержащая слудующие реквизиты (условная форма): код плательщика, ФИО плательщика,адрес, показания счетчика на начало месяца КВт,показания счетчика на конец месяца КВт.(рис.1.1)

Расчет оплаты электроэнергии

месяц:декабрь 2012

Код плательщика	ФИО плательщика			Адрес	Показания счетчика на начало месяца,КВТ	Показания счетчика на конец месяца,КВТ
0001	Коломиец И.И			Проспект Мира,44-1	34578	34678
0002		Петров А.А.		Проспект Мира,44-2	23256	23296
0003		Матвеева К.К.		Проспект Мира,44-3	34589	34620
0004		Сорокина М.М.		Проспект Мира,44-4	98554	98700
0005		Ивлиев С.С		ПроспектМира,44-5	45544	45900

Рис.1.1 Расчет оплаты электроэнергии

 

Условно постоянной информацией(справочной) служат следующие  реквизиты: ФИО плательщика,код плательщика.

Для того чтобы  создать ведомоть расчета оплаты электроэнергии необходимы следующие  реквизиты:ФИО плательщика,код плательщика(условно-постоянные),расход электроэнергии за месяц,КВт, к оплате,итого,отсюда ведомость(рис.1.2):

ФИО плательщка	Код плательщика	Расход электроэнергии за месяц,КВт	К оплате,руб.
-	-	y	b
Итого			s

Рис 1.2

Латинские буквы  в таблице указывают на элементы соответствующих расчетных формул.

2.2 Компьютерная модель  решения  задачи

2.2.1Информационная  модель решения задачи

Информационная  модель,отражает взаимосвязь исходных и результирующих документов(приложение 5)

 

2.2.2 Аналитическая модель решения  задачи

 

Для получения  ведомости «Расчет оплаты электроэнергии» необходимо расчитать следующие показатели:

• Расход электроэнергии за месяц,КВт;
• Сумму к оплате,руб.;
• Итого .

Расчеты выполняются по следующим  формулам:

 

 

Y=y2-y1, D=Y*3,s=∑D

где  у2- показатель счетчика на начало месяца,y1- показатель счетчика на конец месяца,∑- общая сумма к оплате.

 

2.2.3 Технология решения задачи MC Ecxel

Решение задачи средствами MC Ecxel

1. Вызовите Ecxel:

• Нажмите кнопку «Пуск»;

• Выберите в главном миню команду «Программы»;
• В миню Microsoft Office выбирите MC Excel.

2. Переименуйте «Лист1» в «Показания электросчетчиков»:

• Установите курсор мыши на ярлык «Лист1»(нижняя часть экрана) и нажмите правую кнопку мыши ;
• Выберите в контекстном миню команду « Переименовать» нажмите левую кнопку мыши;
• Наберите на клавиатуре «Показания электросчетчиков»;
• Нажмите клавишу «ENTER».

3. Введите заголовок таблицы «Показания электросчетчиков»:

• Сделайте ячейку А1 активной (установите курсор мыши на пересечение столбца А и строки 1 и нажмите левую кнопку мыши);
• Наберите на клавиатуре «Показания электросчетчиков»;
• Нажмите кнопку в строке формул(установите курсор мыши на эту кнопку и нажмите левую кнопку мыши).

4. Отформатируйте заголовок:

• Выделите ячейки А1÷D1(сделайте активной чейку А1,затем нажмите левую кнопку мыши и не отпуская её переместите курсор на ячейку D1)(рис .3)

Рис.3 Форматирование заголовка

 

 

• На панели инструментов в закладке «Главная»,вырерите раздел «Выравнивание» и нажмите кнопку
5. Отформатируйте ячейки  B2÷F2 под ввод длинных заголовков:
• Выделите ячейки B2÷F2;
• Выполните команду «Вырвнивание» в разделе «Формат ячеек» в миню «Главная» на панеле инструментов;
• Выберите вкладку «Выравнивание»;
• В группе опций «Отбражение» установите флажок опции «переносить по словам»(рис.4)

 

Рис.4 Форматирование ячеек

 

• Нажмите кнопку «ОК»
6. Введите в ячейки B2÷F2 информацию представленную на (рис. 5)

 

рис.5 Введение информации

7. Далее вводим в соответствующие ячейки: код плательщика,ФИО плательщика,Адрес,показания счетчика на конец и на начало месяца,КВт.(рис.6)

 

 

 

 

Рис.6 Введение информации

 

8. Теперь непосредственно переходим к созданию «Ведомости расчета»

оплаты электроэнергии»,для этого переходим на Лист2,переименовываем его

аналогично пункту 2.

9. Строим таблицу аналогично пунктам 3,4,5.(рис.6)

Рис.7 Расчет оплаты электроэнергии

 

10. Переходим к расчетам.

Для того чтобы расчитать расход электроэнергии за месяц выспользуемся функцией ПРОСМОТР или ВПР(рис.8)

 

 

 

 

 

Рис.8 Функция  ПРОСМОТР

 

11. Для того чтобы расчитать сумму к оплате по условию задачи нам  дан тариф стоимости электроэнергии (1КВт=3р.),теперь можно расчитать оплату за электроэнергию(рис.9)

Рис.9 Расчет расхода электроэнергии за месяц

 

 

 

12. Для того чтобы расчитать «Итого» ,воспользуемся функцией
13. В итоге получим(рис.10)

Рис.10 Расчет оплаты электроэнергии

14. Сформируем и заполним квитанцию на опалату электроэнергии.

Стотроим таблицу согласно пунктам 3,4,5.(рис.11)

Рис.11 Квитанция  на оплату электроэнергии

15. Для того чтобы автоматические формировать документ «Квитанция на оплату электроэнергии» формируем межтабличные связи при помощи функции ВПР или ПРОСМОТР.(рис.12)

Рис.12 Формирование межтабличных связей

 

 

 

 

В итоге получим: (рис.13)

 

Рис.13 Итог

16. Формирование графического отчета:

• Нажав клавишу СOTR L и удерживая её выделим ячейки таблицы,содерщие данные о расходе электроэнергии,а именно ячейку с данными «ФИО плательщика» и «К оплате».
• На панеле быстрого доступа выберем закладку  «Вставка»             «Гистограммы»           вставить,в итого получим(рис.14):

 

 

 

 

 

 

 

 

Рис.14 Построение гистограммы

 

 

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

 

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

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

Показания электросчетчиков.

 

Расчет оплаты электроэнергии.

 

 

 

 

 

 

 

Квитанция на оплату электроэнергии.

 

3.2.1 Анализ полученных результатов

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

По мимо этого с помощью функции «ПРОСМОТР» уменьшается время затрачиваемое  на заполнение квитанции по электроэнергии и исключается проблема правильности данных переносимых из одной таблицы  в другую.

 

 

 

 

 

 

 

 

 

 

 

 

 

Заключение

Таким образом, общество не стоит на месте. Вчерашний стереотип бухгалтера или экономиста - нарукавники, счёты и толстенные книги учета, канул в лету. На смену счётам пришли сначала “железный Феликс”, затем калькулятор, счетные машины и компьютер.

Трудно себе представить, что “вчерашняя”  амбарная книга, (та, что мы называем бухгалтерской) которую вел для  себя полуграмотный купец, этак лет 70 назад, смогла превратиться в “распределенную  базу данных” или т.н. “транснациональную главную бухгалтерскую книгу” которую  могут одновременно вести тысячи сотрудников компании, филиалы которой  разбросаны по всей планете.

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

Несомненным лидером на рынке программного обеспечения (ПО) на сегодняшний день является корпорация Microsoft.