Инструкция к выполнению лабораторной работы

Лабораторная работа №1

Тема: Технологии и системы обработки табличной информации в среде MS EXCEL. Функция ПРОСМОТР().

Цель работы: приобрести практические навыки экономических расчетов в электронных таблицах с использованием встроенных функций категорий «математические» и «статистические».

Содержание работы:

Часть 1:Создать таблицу следующего вида:

Исходные данные представлены в таблице:

Доходы и расходы предприятия «Савушкин продукт» г. Бреста за I – II кварталы 20ХХ года

Доходы и расходы предприятия «Савушкин продукт» г. Бреста за I – II кварталы 20ХХ года.

Показатель	Январь	Февраль	Март	Апрель	Май	Июнь
Валовая выручка	5 670 000	5 780 000	6 780 000	6 590 000	5 390 000	4 850 000
НДС
Выручка от реализации
Затраты на производство
В том числе:
Сырье без отходов	1 067 000	1 073 000	1 123 000	1 097 000	986 000	923 000
Вспомогательные материалы	456 000	379 000	486 000	534 000	645 000	521 000
Заработная плата	678 300	693 900	709 500	725 100	740 700	756 300
Электро - и теплоэнергия	367 000	385 000	403 000	421 000	439 000	457 000
Отчисления в социальные фонды	398 600	401 900	405 200	408 500	411 800	415 100
Другие расходы	534 900	547 300	559 700	572 100	584 500	596 900
Валовая прибыль
Облагаемый доход
Налог на прибыль
Чистый доход

НДС=	18%
Налог на прибыль	24%

Статьи бюджета вычисляются по следующим формулам:

• Налог на добавленную стоимость (НДС) – произведение значения статьи Валовая выручка на коэффициент НДС (18%).

• Выручка от реализации – разность между статьей Валовая выручка и НДС.

• Валовая прибыль – разность между статьями Выручка от реализации и Затраты на производство.

• Облагаемый доход – сумма Валовой прибыли и Заработной платы.

• Налог на прибыль – 24% от значения статьи Облагаемый доход.

• Чистый доход – разность между статьями Валовая прибыль и Налог на прибыль.

• Затраты на производство – сумма статей Сырье без отходов, Вспомогательные материалы, Заработная плата, Электро- и теплоэнергия, Отчисления в социальные фонды и Другие расходы.

Часть 2: Провести простейший статистический анализ полученной таблицы.

Часть 3: Построить график для сравнения валовой выручки и чистого дохода предприятия.

Часть 4: Рассчитать Чистый доход в валюте.

Инструкция к выполнению лабораторной работы.

Часть 1:

1. Откроем MS Excel; Сохраним рабочую книгу на диск R:\ и присвоим ей имя - Лабораторная работа № 1;

2. Рабочий лист назовем «Исходные данные»;

3. Создадим таблицу с исходными данными. Для определенности таблицу можно разместить в ячейках A4:G15, B21, B22. Можно выбирать любые диапазоны ячеек. Полученная таблица будет выглядеть таким образом:

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

Ø активизируем сначала ячейку В21 и в поле имен введем имя НДС;

Ø активизируем ячейку В22 и в поле имен введем имя НалогНП;

4. Рассчитаем статью Затраты на производство. Для этого просуммируем значения в строках 10, 11, 12, 13, 14 и 15, то есть в ячейку B8 введем формулу: =СУММ(В10:В15). Можно воспользоваться кнопкой Автосуммирование, пиктограмма которой находится на панели инструментов, пункта меню Главная.

5. Далее остается скопировать эту формулу в ячейки C8:G8. Поскольку были использованы относительные ссылки на ячейки В10:В15, при копировании эти ссылки настраиваются относительно своего нового положения.

6. Рассчитаем НДС: в ячейку В6 введем формулу: = В5 * НДС и скопируем ее в ячейки C6:G6. Относительная ссылка на ячейку В5 будет меняться в пределах строки, а ссылка на именованную ячейку НДС останется неизменной, то есть будет себя вести как абсолютная ссылка на ячейку $B$21.

7. Ввести в ячейку В7 формулу: = В5 - В6 и скопировать ее в ячейки C7:G7.

8. Ввести в ячейку В16 формулу: = В7 – В8.

9. Ввести в ячейку В17 формулу: = В12 + В16.

10. Ввести в ячейку В18 формулу: = В17 * Налог. (Налог – именованная ячейка В22).

11. Ввести в ячейку В19 формулу: = В16 - В18.

12. Выделить диапазон ячеек В16:В19 и скопировать формулы, расположенные в этих ячейках, в ячейки С16-G19.

13. Отформатируем полученную таблицу по усмотрению пользователя.

Часть 2:

Проведем простейший статистический анализ полученных результатов:

1. В ячейке В24 определим среднее значение чистого дохода за 2 квартала: =СРЗНАЧ(B19:G19);

2. В ячейке В25 определим количество месяцев с доходом ниже среднего: =СЧЁТЕСЛИ(B19:G19;СЦЕПИТЬ("<";B24)). Здесь текстовая функция СЦЕПИТЬ("<";B24) объединяет два элемента текста – знак "<" и содержимое ячейки В24 – в единый текстовый элемент;

3. В ячейке В26 определим максимальное значение чистого дохода за 2 квартала: =МАКС(B19:G19);

4. В ячейке В27 определим минимальное значение чистого дохода(максимальное значение убытков) за 2 квартала: =МИН(B19:G19).

Часть 3:

Представим данные о доходах предприятия по статьям валовая прибыль и

чистый доход в виде графиков.

1. Выделим ячейки B16:G16 и B19:G19 с данными необходимыми для построения графика (для выделения не смежных диапазонов можно использовать клавишу Ctrl);

2. Вызовем Мастер диаграмм, выбрав соответствующую кнопку на пиктографическом меню;

3. В открывшемся окне мастера (шаг 1 из 4) выберем тип диаграммы – график и ее вид – график с маркерами, помечающими точки данных. Нажмем кнопку «Далее»;

4. На вкладке Ряд в поле Ряд напротив имени Ряд1 введем название «Валовая прибыль» (можно выбирать адрес ячейки, где находится в таблице данный текст), напротив имени Ряд2 – «Чистый доход»;

5. В поле «Подписи по оси Х» укажем диапазон ячеек B4:G4 (названия месяцев). Введем название диаграммы «Доходы и расходы предприятия «Савушкин продукт» г. Бреста за I – II кварталы 2012 года.» и перейдем к следующему шагу;

6. Выберем способ размещения диаграммы: на имеющемся листе. Диаграмма будет вставлена в отведенную для нее область на одном листе с таблицей.

Результат вашей работы в Excel будет выглядеть следующим образом:

Часть 4:

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