Вычисление дохода от проведенных курсов в программной среде MS Excel

Федеральное государственное образовательное бюджетное учреждение высшего профессионального образования

" Финансовый университет при Правительстве Российской Федерации"

Кафедра прикладной информатики Контрольная работа Информатика Выполнил студент Катина Яна Михайловна Руководитель Сысоев Анатолий Иванович

• Введение
• 1. Постановка задачи
• 1.1 Условие задачи
• 1.2 Цель решения задачи
• 2. Математическая модель решения задачи
• 3. Технология решения задачи в MS Excel
• Заключение
• Список использованной литературы

В рамках написания контрольной работы предлагается решить задачу о вычислении дохода от проведенных курсов. Негосударственное образовательное учреждение «Креатив» организует творческие курсы для населения города. Стоимость теоретических и практических занятий включает почасовую оплату преподавателя и накладных расходы. Дополнительно слушатели оплачивают стоимость раздаточного материала для проведения практических занятий. Решение данной задачи на сегодняшний день является актуальным, так как различные виды творчества, называемые иностранным словом handmade (ручная работа) в России являются очень популярными и активно продолжают развиваться. В развитии движения «хэндмейд» большую роль играют мастер-классы и различные творческие курсы, соответственно, необходимо уметь рассчитывать доходы от проведенных курсов. Организацией было принято решение ежемесячно отслеживать доходы от проведенных творческих курсов, чтобы не допустить уменьшения прибыли НОУ, не начать терпеть убытки, и оставаться конкурентоспособными. Задача, которая будет решаться в программной среде MS Excel ежедневно, называется «Учет доходов» .

Цель решения данной задачи состоит в отслеживании доходов НОУ «Креатив» от проведенных курсов в текущем месяце. На основании полученной информации, руководство НОУ может принимать решение по изменению, например, стоимости самих занятий или раздаточного материала.

Данная задача будет решаться на ЭВМ в программной среде MS Excel 2010.

учет доход excel

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

1.1 Условие задачи

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

Стоимость курсов на одного человека

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

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

Структура документа «Стоимость раздаточного материала»

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

Структура результирующего документа «Доход НОУ „Креатив“ от проведенных курсов в текущем месяце»

Кроме того, информацию, находящуюся в таблицах для анализа, необходимо представить в виде диаграмм.

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

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

Негосударственное образовательное учреждение «Креатив» организует творческие курсы для населения города. Стоимость теоретических и практических занятий включает почасовую оплату преподавателя и накладных расходы. Дополнительно слушатели оплачивают стоимость раздаточного материала для проведения практических занятий. Организацией было принято решение ежемесячно отслеживать доходы от проведенных творческих курсов, чтобы не допустить уменьшения прибыли НОУ, не начать терпеть убытки, и оставаться конкурентоспособными. Задача, которая будет решаться в программной среде MS Excel ежедневно, называется «Учет доходов» .

Цель решения данной задачи состоит в отслеживании доходов НОУ «Креатив» от проведенных курсов в текущем месяце. На основании полученной информации, руководство НОУ может принимать решение по изменению, например, стоимости самих занятий или раздаточного материала.

2. Математическая модель решения задачи

Для получения итоговой ведомости о доходах НОУ «Креатив» от проведенных курсов в текущем месяце необходимо рассчитать следующие показатели:

Стоимость курса на 1 чел, руб.;

Стоимость раздаточного материала на все практические занятия по курсу на 1 чел., руб;

Доход от проведенных курсов, руб.;

Общий доход от всех курсов, руб.

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

S = T + P;

R_o = C*R;

D =K* (S + R_o);

D_o = ?D.

где S _- стоимость курса, руб., T _- теоретический курс, руб., P _- практические занятия, руб., R_{o -} стоимость раздаточного материала на все практические занятия по курсу на 1 чел., руб., C _- количество практических занятий, R — стоимость раздаточного материала на 1 чел., руб., D _- доход от проведенных курсов, руб., K — количество слушателей, D_o — общий доход от курсов, руб.

3. Технология решения задачи в MS Excel

Решение задачи средствами MS Excel

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

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

выберите в главном меню команду «Программы» ;

в меню Microsoft Office выберите MS Excel.

2. Переименуйте «Лист 1» в «Курсы» :

· установите курсор мыши на ярлык «Лист 1» и нажмите правую кнопку мыши (ПКМ);

· В контекстном меню кликните команду «Переименовать» ;

· Наберите на клавиатуре «Курсы» и подтвердите изменение нажатием клавиши «Enter» .

3. В ячейку A1 введите заголовок таблицы «Стоимость курсов на одного человека без учета раздаточного материала» .

4. Введите в ячейки A2: E2 информацию, представленную на рисунке 1.

Рисунок 1 — Имена полей таблицы «Стоимость курсов на одного человека без учета раздаточного материала»

5. В ячейки A3: E8 введите информацию, приведенную в таблице 1.

Таблица 1 — Стоимость курсов на одного человека. Стоимость курсов на одного человека без учета раздаточного материала

6. Созданная таблица «Стоимость курсов на одного человека без учета раздаточного материала» представлена на рисунке 2.

Рисунок 2 — Вид таблицы «Стоимость курсов на одного человека без учета раздаточного материала»

7. Переименуйте «Лист 2» в «Раздатка» (аналогично действиям пункта 2).

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

Рисунок 3 — Вид таблицы «Стоимость раздаточного материала для проведения практических занятий по каждому курсу на 1 человека»

9. Отсортируем полученные таблицы по названию курса. Для этого выделим диапазон ячеек A2: E8 на листе «Курсы», выберем пункт меню «Данные» — «Сортировка». В результате чего появится диалоговое окно, как показано на рисунке 4. В выпадающем меню «Сортировать по» выбираем «Название курса», порядок — по возрастанию (от, А до Я). Нажимаем кнопку «ОК» .

Рисунок 4 — Вид окна «Сортировка»

В результате таблица «Стоимость курсов на одного человека без учета раздаточного материала» приняла вид, представленный на рисунке 6.

Рисунок 5 — Таблица «Стоимость курсов на одного человека без учета раздаточного материала» в отсортированном виде Аналогичным образом отсортируем таблицу на листе «Раздатка». Результат представлен на рисунке 6.

Рисунок 6 — Таблица «Стоимость раздаточного материала для проведения практических занятий по каждому курсу на одного человека» в отсортированном виде

10. Присвоим имя группе ячеек таблицы «Стоимость курсов на одного человека без учета раздаточного материала». Для этого:

· Выделите ячейки B3: E8 на листе «Курсы», выберите команду «Присвоить имя» в разделе «Определенные имена» меню «Формулы» — «ОК» (рисунок 7).

Рисунок 7 — Вид окна «Создание имени»

11. Аналогичным образом присвоим имя группе ячеек таблицы на листе «Раздатка» (рисунок 8).

Рисунок 8 — Вид окна «Создание имени»

12. Переименуйте «Лист 3» в «Доход» .

13. Создайте таблицу «Доход НОУ „Креатив“ от проведенных курсов в текущем месяце» и введите в нее исходные данные (рисунок 9).

Рисунок 9 — Вид таблицы «Доход НОУ „Креатив“ от проведенных курсов в текущем месяце»

14. Вычислим стоимость курса (столбец E на листе «Курсы»). Для этого в ячейку E3 введем формулу

E3 = C3+D3

Далее, используя маркер автозаполнения, вычислим стоимость курсов остальных направленностей (рисунки 10, 11).

Рисунок 10 — Ввод формулы для вычисления стоимости курса Рисунок 11 — Результат заполнения столбца «Стоимость курса»

15. Заполним столбец «Стоимость раздаточного материала на все практические занятия по курсу на 1 чел.» таблицы «Стоимость раздаточного материала для проведения практических занятий по каждому курсу на одного человека» .

Для этого в ячейку D3 введем формулу:

D3 = B3*C3.

С помощью маркера автозаполнения, вычислим стоимость раздаточного материала для других курсов, для этого «растянем» формулу до ячейки D8 (рис. 12, 13).

Рисунок 12 — Вычисление стоимости раздаточного материала Рисунок 13 — Результат заполнения столбца «Стоимость раздаточного материала»

16. Заполним столбец «Стоимость курса на 1 чел.» в итоговой ведомости о доходах. Для этого:

· Сделайте ячейку D3 активной;

· Введем в ячейку «= «

· Воспользуйтесь командой «Формулы» — «Вставить функцию» ;

· В поле «Категория» выберите «Ссылки и массивы» ;

· В поле «Выберите функцию» нажмите «ВПР» (рисунок 14);

Рисунок 14 — Вид первого окна мастера функций

· Нажмите кнопку «ОК» ;

· Введите артикул в поле «Искомое_значение», щелкнув по ячейке B3;

· Введите информацию в поле «Таблица»: воспользуйтесь командой «Использовать в формуле меню «Формулы», выбрав «Курсы» (рис.15);

· В поле «Номер столбца» введите цифру 4;

· В поле «Интервальный просмотр» введите цифру 0 (рисунок 16);

· Нажмите кнопку «ОК» ;

Рисунок 15 — Ввод имени массива в качестве аргумента функции Рисунок 16 — Вид окна «Аргументы функции»

Рисунок 17 — Вычисление стоимости курса на 1 человека

· Сделайте ячейку D3 активной, установите курсор на маркер автозаполнения в правом нижнем углу ячейки D3, щелкните ЛКМ и протяните его до ячейки D8;

· Результат заполнения столбца «Стоимость курса на 1 чел.» представлен на рисунке 18.

Рисунок 18 — Заполненный столбец «Стоимость курса на 1 чел.»

17. Аналогичным образом перенесем данные о стоимости раздаточного материала на 1 чел. В ячейку E3 введем формулу:

=ВПР (B3; Раздатка; 4; 0)

Далее, используя маркер автозаполнения, вычислим стоимость раздаточного материала для других курсов (размножим формулу в ячейке E3 до ячейки E8, рисунки 19, 20).

Рисунок 19 — Вычисление стоимости раздаточного материала Рисунок 20 — Заполненный столбец «Стоимость раздаточного материала»

18. Вычислим доход от проведенных курсов. Для этого в ячейку F3 введем формулу:

=C3* (D3+E3).

Рисунок 21 — Вычисление дохода от проведенных курсов Результат работы формулы приведен на рисунке 22.

Рисунок 22 — Заполненный столбец «Доход от проведенных курсов»

19. Вычислим итоговый доход от курсов. Для этого в ячейку F9 введем формулу:

=СУММ (F3: F8).

20. В результате таблица «Доход НОУ „Креатив“ от проведенных курсов в текущем месяце» примет вид, представленный на рисунке 23.

Рисунок 23 — Таблица «Доход НОУ „Креатив“ от проведенных курсов в текущем месяце» в заполненном виде

21. Представим наглядно результаты работы, создав диаграмму по данным итоговой ведомости о доходах НОУ «Креатив». Для этого:

· Выделите несмежные диапазоны ячеек, содержащие информацию о названии курсов и информацию о доходах от курсов, руб. При выделении несмежных диапазонов удерживаем клавишу Ctrl (рисунок 24);

· Выберите команду «Гистограмма» в разделе «Диаграммы» меню «Вставка» ;

· Переименуйте получившуюся гистограмму в «Доход от проведенных курсов» (рисунок 24).

Рисунок 24 — Выделение необходимых данных для построения диаграммы Рисунок 25 — Гистограмма «Доход НОУ „Креатив“ от проведенных курсов в текущем месяце»

Заключение

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

1. Информатика: учебное пособие / под ред. Б. Е. Одинцова, А. Н. Романова. — М.: Вузовский учебник: ИНФРА+М, 2012.

2. Информационные ресурсы и технологии в экономике: учебное пособие / под ред. Б. Е. Одинцова, А. Н. Романова. — М.: Вузовский учебник, 2012.

3. Информатика: Практикум для экономистов: учебное пособие /под ред.В. П. Косарева. — М.: Финансы и статистика: ИНФРА+М, 2009.

4. Компьютерная обучающая программа по дисциплине «Информатика» / А. Н. Романов, В. С. Торопцов, Д. Б. Григорович, Л. А. Галкина, А. Ю. Артемьев, Н. И. Лобова, К. Е. Михайлов, Г. А. Жуков, О. Е. Кричевская, С. В. Ясеновский, Л. А. Вдовенко, Б. Е. Одинцов, Г. А. Титоренко, Г. Д. Савичев, В. И. Гусев, С. Е. Смирнов, В. И. Суворова, Г. В. Федорова, Г. Б. Коняшина. — М.: ВЗФЭИ, 2000. Дата обновления: 24.11.2010. — URL: http://repository. vzfei.ru. Доступ по логину и паролю.