Министерство образования  Российской Федерации

ТОМСКИЙ  ГОСУДАРСТВЕННЫЙ  УНИВЕРСИТЕТ  СИСТЕМ  УПРАВЛЕНИЯ  И  РАДИОЭЛЕКТРОНИКИ (ТУСУР)

 

 

 

 

 

С.Л. Миньков

 

 

 

 

 

EXCEL

 

 

Лабораторный практикум

 

 

 

 

 

 

 

 

 

 

 

 

2000


 

 

 

 

Миньков С. Л. Excel: Лабораторный практикум. – Томск: ТУСУР, 2000. –109 с.

 

 

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

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

Практикум рассчитан на студентов очных и заочных отделений вузов, изучающих дисциплины блока «Информатика», а также аспирантов и слушателей факультетов повышения квалификации экономических специальностей. Возможно использование в дистанционном обучении по специальности  «Прикладная информатика (в экономике)».

 

 

 

 

 

 

 

 

 

 

 

 

Ó С.Л. Миньков, 2000


Вводя в ячейку формулу недрогнувшей рукой,

Запомни, юзер, истину простую:

Связавшись с Microsoft, ты потерял покой,

Зато с Excel поймаешь рыбку золотую.

User’ ский фольклор

Введение

 

В 1979 году выпускник Гарвардского университета Дэн Бриклин и профессиональный программист Боб Франкстон предложили миру способ автоматизации трудоемких и утомительных операций пересчета, которые всегда требуются при ручном составлении таблиц в бухгалтерском и банковском учете, в проектно-сметных работах, при решении планово-экономических задач. Это была программа VisiCalc[1], которая позволяла представлять данные в виде таблицы на экране дисплея, прокручивать эту электронную таблицу  по строкам и столбцам и, самое главное, обладала удивительным свойством – в таблице проводился автоматический пересчет содержимого ячеек при изменении значений одной из них. Причем все действия были очень наглядны – всё выполнялось в соответствии с принципом WYSIWYG What You See Is What You Get»).

Популярность этой программы была феноменальной. Естественно, в первую очередь ею заинтересовались представители делового мира, что, кстати, заметно стимулировало продажу персональных компьютеров. По некоторым оценкам, более четверти проданных машин Apple II в 1979 году покупались для того, чтобы получить возможность работать на VisiCalc. Компьютер превратился в неотъемлемый инструмент бизнеса.

Успех первой электронной таблицы вызвал множество подражаний и вариантов, сопровождавшихся шумными судебными разбирательствами. В конце концов, на рынке программного обеспечения осталось несколько программных продуктов, в числе которых – Excel[2] корпорации Microsoft, входящий в состав интегрированного пакета прикладных программ Microsoft Office for Windows[3].

Excel предлагает широкий набор функциональных средств по обработке табличных данных:

создание и редактирование электронных таблиц с применением богатого набора встроенных функций;

оформление и печать электронных таблиц;

построение диаграмм и графиков различной степени наглядности и детализации;

работа с электронными таблицами как с базами данных: фильтрация, сортировка, создание итоговых и сводных таблиц, консолидация данных из различных таблиц, в том числе из внешних баз данных;

решение экономических задач типа «что – если» путем подбора параметров;

решение оптимизационных задач;

численное решение разнообразных математических задач;

статистическая обработка данных;

использование интегрированной среды разработки собственных программ – макросов на основе языка программирования Visual Basic for Applications.

Предлагаемый цикл из 12 лабораторных работ предназначен для ознакомления с основами Excel и ориентирован на студентов экономических специальностей вузов. Он может быть использован при изучении дисциплин «Информатика», «Разработка и применение пакетов прикладных программ в экономике», а также «Численные методы» – в качестве иллюстративного материала, демонстрирующего возможности офисных пакетов прикладных программ по решению уравнений.

Изучение возможностей Excel основано на версии Excel 97 (или Excel 8), входящего в состав MS Office 97 для Windows 95. Можно также использовать Excel 7.0, входящий в состав MS Office 95, и Excel 2000 из MS Office 2000 Pro.

Лабораторные работы содержат теоретический материал, необходимый для выполнения заданий, примеры, показывающие применение функций Excel, и варианты для самостоятельной работы различной степени сложности. В заключение приведена литература [1-5], которая была использована для подготовки контрольных вариантов.


Лабораторная работа 1. Изучение основ Excel.

Заполнение таблиц

 

Электронная таблица Excel – одна из составных частей пакета прикладных программ Microsoft Office, работающего в среде Windows. Она предназначена для обработки числовых данных, проведения математического моделирования различных процессов (в первую очередь экономических), изготовления различных документов и форм, а также может быть использована в качестве простой базы данных.

 

Книги и листы. При запуске Excel открывается рабочая книга Excel (так называются файлы Excel), которая по умолчанию имеет имя Книга1.xls. Эта книга состоит из трех листов – Лист1, Лист2 и Лист3. Щелкая левой клавишей по ярлычку листа, можно переходить из одного листа в другой. Можно дать им и более осмысленное название. Для этого необходимо щелкнуть по ярлычку листа правой клавишей мыши, вызвав контекстно-зависимое меню, выбрать опцию Переименовать и набрать с клавиатуры новое имя, например, «План» (рис.1.1). Отметим, что с помощью предлагаемого меню можно также производить удаление листа, добавление нового листа и перемещение его в другой файл. Есть и другой способ переименования – двойной щелчок левой клавишей мыши по ярлычку листа и набор нового имени.

 

Ячейки. Каждый лист Excel представляет собой таблицу. Столбцы обозначены буквами от A до Z и далее сочетаниями букв от AA до IV, а строки – числами от 1 до 65536 ( в Excel 97). Поэтому каждая ячейка таблицы имеет свой номер, например, А1, GA200. С помощью мыши или клавиш передвижения курсора (указателя)  можно перемещаться из ячейки в ячейку.

Текущая ячейка выделяется черным контуром (F20, рис.1.1). Чтобы выделить несколько ячеек (блок), необходимо щелкнуть левой клавишей мыши по начальной (обычно левой верхней) ячейке и, не отпуская ее, протащить указатель до последней (правой нижней) ячейки. Выделенные ячейки (кроме первой) затемняются. Для выделения нескольких несмежных блоков (бывает полезно при построении диаграмм и графиков) необходимо выделить первый блок, а затем, нажав и удерживая клавишу Ctrl, выделить следующий блок и т.д. Чтобы отменить выделение, достаточно щелкнуть мышью по любому невыделенному участку листа.


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

Если текст не входит в ячейку, то можно:

-         раздвинуть границы ячеек по горизонтали, встав курсором на границу между буквами столбцов (широкий крест курсора превращается в черный крестик со стрелками) и, удерживая нажатой левую клавишу мыши, сдвинуть границу на требуемое расстояние;

-         объединить  несколько  ячеек и в них записать текст.  Для этого необходимо выделить несколько соседних ячеек и выбрать через Главное меню Excel путь: Формат | Ячейки (появляется диалоговое окно Формат ячеек, рис. 1.2) | Выравнивание | Объединение ячеек (этот же путь можно выбрать через контекстно-зависимое меню);

-         организовать перенос текста в ячейке по словам: Формат | Ячейки | Выравнивание | Переносить по словам.

Если число не входит в ячейку, то Excel отображает его либо в экспоненциальной форме (1230000000 ® 1,23Е+09), либо вместо числа ставит знаки ####. Тогда необходимо раздвинуть границы ячейки. В Excel можно выбрать различные форматы представления чисел: Формат | Ячейки | Число | Числовые форматы.

 


В виде формулы в ячейке записывается арифметическое или логическое выражение, состоящее из чисел, адресов ячеек и функций, соединенных между собой знаками арифметических операций и операций отношения, и начинающееся со знака =. При его записи следует соблюдать обычные правила алгоритмических языков: арифметические операции выполняются слева направо в порядке старшинства (возведение в степень ^ , умножение *, деление /, сложение +, вычитание -). Для изменения порядка выполнения операций используются круглые скобки, аргумент функции также берется в круглые скобки. Адреса  ячеек набираются только латинским шрифтом!

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

Переход в режим редактирования данных также можно осуществить щелчком по строке формул.

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

Аналогичные действия можно провести с помощью контекстно-зависимого меню или через Главное меню Excel (опция Правка).

 

Пример. Рассмотрим применение некоторых возможностей Excel  на примере создания таблицы выполнения плана (см. рис.1.1).

1.       В ячейку В2 вводим текст «Задание. Определить процент выполнения плана и рост», в ячейку В3 – текст «производства по предприятиям». Изменять ширину ячеек нет смысла, т.к. в соседние ячейки текст не вносится и ничто не помешает его увидеть полностью.

2.       Объединяем ячейки В5:С6 и вводим текст «Предприятия отрасли», центрируя его (Формат | Ячейки | Выравнивание | По вертикали (По горизонтали) | По центру).

3.       Объединяем ячейки D5:F6 и вводим текст «Объем производства, млн. руб.». Аналогично, объединяем G5:G6 – текст «Процент выполнения плана», и Н5:Н6 – текст «Относительный прирост». В последних двух блоках также задаем режим Переносить по словам. Передвигая границы ячеек, добиваемся требуемого расположения текста в ячейках.

4.       Объединяя ячейки В7:C7 и т.д., вводим названия предприятий, а в ячейки D6, E6, F6 – текст «План...», «Отчет...».

5.       Заполняем ячейки D7:F9 входными данными.

6.       Заполняем строку «Итого». В ней должен находиться результат суммирования трех вышестоящих ячеек. Суммирование можно выполнить двумя способами.

Первый способ. Выделяем ячейку D10 и выбираем Вставка | Функция | (появляется окно Мастер функций (рис. 1.3)) | Категория | Математические | Функция | СУММ | ОК. В появившемся окне задаем диапазон суммирования D7:D9. После нажатия кнопки ОК в ячейке D10 появляется результат суммирования содержимого ячеек D7:D9. Сама формула

=СУММ (D7:D9)

высвечивается в строке формул.

Окно Мастер функций также открывается щелчком по кнопке со знаком fx на панели инструментов Стандартная. Excel содержит большое количество встроенных функций: математических, статистических, финансовых и других, сгруппированных по категориям (рис. 1.3).


Знание и умелое применение этих функций облегчает процесс обработки информации. Более подробную информацию о каждой функции можно найти в справке по MS Excel : ? | Вызов справки | Предметный указатель; в Поле 1 ввести слово «функция» и выбрать в Поле 2 необходимую функцию.

Второй способ. Выделяем ячейки D7:D9 и щелкаем по кнопке  со  знаком  S  на  панели  инструментов  Стандартная. В ячейке D10 появляется результат суммирования.

7.       Копируем полученную формулу в ячейки E10 и F10. Для этого указываем на маленький квадратик в правом нижнем углу ячейки D10 (курсор при этом превращается в черный крестик – маркер заполнения), нажимаем левую кнопку мыши и, не отпуская ее, двигаем мышь вправо, пока рамка не охватит ячейки E10 и F10. В ячейке Е10 появится формула

=СУММ (Е7:Е9),

а в ячейке F10 – формула

=СУММ (F7:F9).

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

Если же при копировании требуется оставить неизменным адрес какой-нибудь ячейки (или только столбца, или только строки), то перед именем столбца и/или номером строки ставится символ $. Например, $D$5, H$4, $A2.

8.       Заносим в ячейку G7 формулу

=F7/E7,

а в ячейку H7 – формулу

=(F7-D7)/D7.

9.       Выделяем ячейки G7 и H7 и копируем сразу две формулы на ячейки G8:G10 и F8:F10, соответственно.

10.   Чтобы задать процентный формат чисел в ячейках G7:H10, можно, выделив их, выбрать путь Формат | Ячейки | Число | Числовые форматы | Процентный ; Число десятичных знаков | 2. Это же самое выполняется быстрее при помощи кнопки Процентный формат панели инструментов Форматирование. Если число десятичных знаков меньше (или больше) требуемого, то следует использовать кнопку Увеличить разрядность (Уменьшить разрядность). Таблица заполнена.

11.   Оформим таблицу, нарисовав внутренние и внешние рамки: Формат | Ячейки | Границы, или используя кнопку Границы на панели инструментов Форматирование.

12.   Можно также подобрать для разных частей таблицы различный фон (тип штриховки, цвет штриховки, цвет фона): Формат | Ячейки | Вид, или используя кнопки Цвет заливки и Цвет шрифта на панели инструментов Форматирование.

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

 Теперь таблица окончательно готова – и в вычислительном аспекте, и в эстетическом.

При изменении исходных данных в ячейках D7:F9 результаты, находящиеся в ячейках D10:F10 и G7:H10, будут автоматически пересчитываться.

 

Примечание. В Excel существует интересная функция автозаполнения (не использованная в примере), полезная при заполнении рядов данных.

Если ввести в две соседние ячейки последовательно два числа, составляющие начало арифметической прогрессии, например, 1 и 2, затем их выделить и, как при  копировании, с помощью маркера заполнения протащить их на несколько ячеек, то ряд продолжится: 1, 2, 3, 4 и т.д.

Excel также позволяет вводить и нечисловые последовательности. Например, если ввести в две соседние ячейки Январь и Февраль и осуществить описанную выше операцию, то в следующих ячейках появится Март, Апрель и т.д. Эти последовательности, или списки, можно сформировать самому и дать Excel запомнить их. Для этого необходимо выполнить команду Сервис | Параметры | Списки | Добавить и в окне Элементы списка записать (разделяя Enter) элементы, составляющие список.

 

 

Задание

1.       Открыть файл Excel и разместить на листе таблицу из выбранного варианта.

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

3.       Оформить таблицу.

 

 

 

 

Вариант 1. Рассчитать суммы распределения прибыли в НПО.

 

Показатели

Нормативы распределения, %

Сумма отчислений прибыли, млн. руб.

Прибыль, всего

Отчисления в бюджет

Отчисления на собственные нужды:

в фонд развития производства

в фонд мат. поощрения

в фонд соц. развития

100

29

 

45

15

11

35,4

?

 

?

?

?

 

 

Вариант 2. Выполнить расчет движения материальных ценностей по складу по балансовым счетам 051, 052, 055 (в тыс. руб.).

 

Показатели, тыс. руб.

Номер счета

Всего по складу

051

052

055

Остаток на начало года

Приход за год

Расход за год

Остаток на конец года

в % к началу года

6000

3400

7000

?

?

30

45

55

?

?

1200

960

750

?

?

?

?

?

?

?

 

 

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

 

Виды расходов, руб.

Группа 1

Группа 2

Группа 3

Группа 4

Группа 5

Продукты

370

580

1300

1950

2300

Жилье

88

125

180

200

1400

Комм. услуги

44

60

120

120

120

Одежда

80

220

800

1500

3500

Другие расходы

350

860

1200

2500

4400

Итого

?

?

?

?

?

К-т Энгеля, %

?

?

?

?

?

 

 

 

 

Вариант 4. Определить экономические показатели фирмы «Геркулес» в отчетном периоде.

 

Сегмент рынка

Себестоимость единицы продукции, руб.

Цена единицы продукции, руб.

Объем реализации, тыс. шт.

Затраты на производство, тыс. руб.

Выручка от реализации, тыс. руб.

Прибыль, тыс. руб.

A

B

C

D

14

13

14

11

16

15

16

13

10

15

20

10

?

?

?

?

?

?

?

?

?

?

?

?

Итого

?

?

?

?

 

Вариант 5. Рассчитать объем территориального фонда обязательного медицинского страхования в 1997 году и сравнить показатели с 1996 г.

 

Поступления, млрд. руб.

1996 г.

1997 г.

В % к итогу

В % к 1996 г.

Страховые взносы

Платежи на обязательное мед. страхование нераб. населения

Штрафные санкции

Другие поступления

101,48

 

75,03

12,77

10,8

148,88

 

118,32

18,0

16,92

?

 

?

?

?

?

 

?

?

?

Итого

?

?

?

?

 

Вариант 6. Рассчитать среднюю стоимость 1 кв. м общей площади жилых помещений в отдельных городах Западно-Сибирского региона в 1997 г.

 

Рынок жилья,

тыс. руб.

Томск

Новосибирск

Барнаул

Тюмень

Первичный рынок:

- улучш. планировки

- элитные

Средняя цена

Средняя цена по отношению к томской, %

 

2640,4

2576,0

?

 

?

 

3354,6

3000,0

?

 

?

 

3132,0

4127,3

?

 

?

 

2886,5

3523,2

?

 

?

Вторичный рынок:

- низкого качества

- типовые

- улучш. планировки

- элитные

Средняя цена

 

2206,1

2268,7

2505,8

3805,6

?

 

1394,7

1847,5

2176,4

3891,1

?

 

1900,0

1989,2

2126,4

3605,3

?

 

2486,7

2999,0

3150,3

4500,2

?

Средняя цена по отношению к томской, %

 

?

 

?

 

?

 

?

Вариант 7. Рассчитать ежедневный предполагаемый доход от деятельности киносети.

 

Кинотеатры

Вместимость зрительного зала, мест

Средний % посещаемости

Средняя цена билета, руб.

Количество сеансов в день

Доход, тыс.руб.

Родина

Сибиряк

Аэлита

Авангард

700

150

300

500

45

40

60

30

40

30

25

40

4

5

3

5

?

?

?

?

Ср. знач.

?

?

?

?

?

Итого

 

?

 

Вариант 8.  Рассчитать сумму оплаты за электроэнергию за 1-й квартал 2000 года. Определить средний расход электроэнергии за квартал.

 

Дата

Показания счетчика, кВт×ч

Расход электроэнергии

Тариф, руб./кВт×ч

Сумма

Текущее

Предыдущее

5.01.2000 г.

39530

9.02.2000 г.

39900

?

?

0,17

?

3.03.2000 г.

40210

?

?

0,12

?

2.04.2000 г.

41500

?

?

0,12

?

Итого к оплате

?

 

Вариант 9. Рассчитать сумму оплаты за услуги водоканала и теплосетей за март 2000 года, если в квартире площадью 71,4 кв. м проживает 4 чел. Каков удельный вес каждого платежа в общей сумме?

 

Вид платежа

Стоимость за единицу услуги (вода – на 1 чел., отопление – на 1 кв. м)

% понижения

Стоимость эконом. обоснованная

Начислено фактически

Долг (+); переплата (-)

Итого

Эконом. обоснованная

С учетом пониж. коэффициента

Вода холодная и канализация

Горячая вода

Отопление

 

36,02

39,92

  7,08

 

?

?

?

 

45

72

76

 

?

?

?

 

?

?

?

 

0

102

-23

 

?

?

?

Итого

?

?

?

?

Вариант 10. Рассчитать сумму оплаты за жилищно-коммунальные услуги за март 2000 года, если в квартире площадью 71,4 кв. м проживает 4 человека. Каков удельный вес каждого платежа в общей сумме?

 

Вид платежа

Стоимость за единицу услуги, руб.

% понижения

Стоимость эконом. обоснованная

Начислено фактически

Долг (+); переплата (-)

Итого

Эконом. обоснованная

С учетом пониж. коэффициента

Тех. обслуж.

Кап. ремонт

Очистка стоков

Вывоз мусора

Лифт

Колл. антенна

1,93

1,72

5,43

2,36

9,78

3,80

?

?

?

?

?

?

58

83

50

 1

62

 3

?

?

?

?

?

?

?

?

?

?

?

?

0

0

0

0

-3,25

3,00

?

?

?

?

?

?

Итого

?

?

?

?

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

 

Вариант 11. Расчет затрат на выработку тепла по котельным МП «ТепТоп» (в тыс. руб.).

 

Статья

Тип котельной

Всего

Газовая котельная

Угольная котельная

1. Материалы

2. Амортизация

3. Вода

4. Электроэнергия

5. Заработная плата с начислениями

6. Топливо

7. Ремонтный фонд

8. Цеховые расходы (11,9% от ст. 10)

9. Общеэксплуатационные расходы (9,1% от ст. 10)

10. Итого затрат по котельным, тыс. руб.

84,2

165,6

607,1

339,3

621,2

1234,5

590,0

?

 

?

?

85,5

337,5

80,8

333,9

3081,0

2194,7

320,6

?

 

?

?

?

?

?

?

?

?

?

?

 

?

?

11. Косвенные затраты

12. Всего затрат по котельным с учетом косвенных

13. Рентабельность (15% ст. 12)

391,5

 

?

?

1709,5

 

?

?

?

 

?

?

14. Итого затрат с учетом рентабельности

?

?

?

15. Доля затрат, %

100

?

?

 

 

Вариант 12. Проанализировать динамику поступления товаров от поставщиков.

 

Поставщики

1998 г., млн. руб.

1999 г.,

млн. руб.

Превышение

В % к 1998 г.

Удельный вес в 1998 г.

Удельный вес в 1999 г.

ООО «Прима»

АОЗТ «Томь»

ЧП «Сантик»

ОАО «Гермес»

Всего

15,5

23,4

0,96

7,5

?

16,9

32,1

1,2

6,4

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

 

 

 

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

 

Ф.И.О.

Тарифный разряд

% выполнения плана

Тарифная ставка

Заработная плата с премией

Пряхин Н.П.

Суховейко А.Д.

Лоханкин В.А.

Пферд Л.Ф.

Севрюгов Л.А.

Гигиенишвили Г.С.

Птибурдуков А.И.

3

2

1

1

3

2

3

102

98

114

100

100

94

100

?

?

?

?

?

?

?

?

?

?

?

?

?

?

 

Примечание 1. Тарифная ставка определяется в зависимости от разряда: 1-й разряд – 1200 руб.; 2-й разряд – 1500 руб.; 3-й разряд – 2000 руб.

Примечание 2. Размер премиальных определяется в зависимости от выполнения плана:

- ниже 100% – премия не начисляется;

- 100% – премия 20% от тарифной ставки;

- 101…110% – премия 30%;

- 111…115% – премия 40%.

 

 

 

Вариант 14. Сравнить доходную часть городского бюджета в 1999 и 2000 году.

 

Статья

1999 г. (отчет), тыс. руб.

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

2000 г. (план), тыс. руб.

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

Превышение

В % к 1999 г

Налоговые доходы

1. Налоги на прибыль (доход), прирост капитала

2. Налоги на товары и услуги, лицензионные сборы

3. Налоги на совокупный доход

4. Налоги на имущество

5. Платежи за пользование природными ресурсами

6. Прочие налоги, пошлины и сборы

Неналоговые доходы

1. Доходы от имущества, находящегося в гос. собственности

2. Административные платежи и сборы

3. Штрафные санкции

Итого доходов

?

 

347660

 

 

396110

 

53810

266900

 

 

102600

 

236580

?

 

 

10690

 

9500

3500

?

?

 

?

 

 

?

 

?

?

 

 

?

 

?

?

 

 

?

 

?

?

100

?

 

666562

 

 

142887

 

35696

107253

 

 

382380

 

274296

?

 

 

37366

 

4500

3600

?

?

 

?

 

 

?

 

?

?

 

 

?

 

?

?

 

 

?

 

?

?

100

?

 

?

 

 

?

 

?

?

 

 

?

 

?

?

 

 

?

 

?

?

?

?

 

?

 

 

?

 

?

?

 

 

?

 

?

?

 

 

?

 

?

?

?

 

Вариант 15. Рассчитать начисление стипендии студентам по итогам сессии.

 

Ф.И.О.

Оценки за экзамены

Начисление стипендии

Информатика

Эконом. теория

Теория

вероятности

ТЭИС

1. Иванопуло И.П.

2. Зверев Д.Б.

3. Калачов Н.А.

4. Калачова Е.Б.

5. Синицына З.С.

6. Писаревская Л.Г.

7. Тарасов А.Н.

8. Паровицкий С.Т.

5

4

4

4

4

5

3

4

5

4

5

3

5

5

5

4

5

5

5

2

3

4

2

5

5

5

5

4

5

4

4

4

?

?

?

?

?

?

?

?

 

Примечание. Размер стипендии составляет 2 МРОТ (минимальный размер оплаты труда, равный 108 руб. 54 коп.). Если все экзамены сданы на «пятерку», то надбавка составляет 50%. Если есть одна «четверка» (при остальных «пятерках»), то надбавка составляет 25%. Если есть хотя бы одна «двойка», то стипендия не начисляется.

 

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

 

Показатели

Ф.И.О. зарегистрированного кандидата

Полесов В.М.

Чарушников М.П.

Кислярский М.Б.

Сумма, руб.

% к всего

Сумма, руб.

% к всего

Сумма, руб.

% к всего

Поступило средств всего,

в том числе:

от избирательной комиссии

собственные средства кандидата

пожертвования юридических лиц

пожертвования физических лиц

 

?

 

 

2000

 

 

800

 

125000

 

 

100

 

 

?

 

 

?

 

?

 

?

 

?

 

 

2000

 

 

800

 

1057300

 

 

100

 

 

?

 

 

?

 

?

 

?

 

 

?

 

2000

 

 

2000

 

4193410

 

10590

 

100

 

 

?

 

 

?

 

?

 

?

Израсходовано средств всего,

 в том числе:

радио и телевидение

печатные издания

публичные мероприятия

канцелярские расходы

Аренда помещений и автотранспорта

Прочие расходы

 

?

 

 

 

114418

 

 

 

 

8800

4940

 

100

 

 

?

 

?

 

?

 

?

 

 

?

?

 

?

 

 

334122

 

604582

 

7780

 

4169

 

 

14392

30231

 

100

 

 

?

 

?

 

?

 

?

 

 

?

?

 

?

 

 

752600

 

1332990

 

200330

 

106040

 

 

95170

1172180

 

100

 

 

?

 

?

 

?

 

?

 

 

?

?

Остаток неизрасходованных средств

?

?

?

Вариант 17. Рассчитать доход от реализации колбасных изделий АОЗТ «Рога и копыта».

 

Наименование

изделий

Объем производства, т

Цена за кг, руб

Торгово-сбытовая скидка, %

Цена со скидкой

Сумма

Колбаса пермская п/к, 1с.

Колбаса одесская п/к, 1с.

Колбаски охотничьи, п/к, в/с,

Колбаса польская п/к, 2с.

Колбаса таллиннская п/к, в/с.

 

5

 

12

 

2

 

14

 

3

 

50

 

60

 

79

 

46

 

66

 

8

 

8

 

8,5

 

7,8

 

8,5

 

?

 

?

 

?

 

?

 

?

 

?

 

?

 

?

 

?

 

?

Всего

?

 

 

 

?

 

Вариант 18. Рассчитать заработную плату работникам высшего учебного заведения.

 

Ф.И.О.

Оклад (штат.), руб.

Начислено по окладу

Надбавки

Удержано

Всего начислено

Всего удержано

Сумма к выдаче

За ученую степень (в МРОТ)

Персональная (в% от гр.2)

Остальные (в % от гр.2)

Подоходный налог (12% от гр. 10)

Пенсионный налог (1% от гр. 10)

Профсоюз (1% от гр. 10)

1

2

3

4

5

6

7

8

9

10

11

12

Штатные

Персицкий Р.

Судейкин М.

Наперников Г

 

660585385

 

?

?

?

 

3

3

 

40

40

 

20

30

 

?

?

?

 

?

?

?

 

?

?

?

 

?

?

?

 

?

?

?

 

?

?

?

Совместители

Ляпис Н.

Авдотьев К.

 

 

405

660

 

 

?

?

 

 

1,5

 

 

40

 

 

35

 

 

?

?

 

 

?

?

 

 

?

?

 

 

?

?

 

 

?

?

 

 

?

?

 

Примечание 1. Надбавка за ученую степень рассчитывается, исходя из минимального размера оплаты труда (МРОТ), равного 108 руб. 54 коп.

Примечание 2. Начисление по окладу (графа 3) составляет 100% для штатных работников и 50% для совместителей.

 

Вариант 19. Рассчитать плановую сумму прибыли ЧП «Московские баранки».

 

Показатели

Год

1999 г в % к 1997г.

1997

1998

1999

1. Объем продаж, т

2. Цена единицы продукции, руб./кг

3. Выручка от реализации продукции, руб.

4. Постоянные расходы, руб.

5. Переменные расходы (60% от ст. 3),  руб.

6. Общая себестоимость продукции, руб.

7. Удельная себестоимость продукции, руб.

8. Валовая прибыль, руб.

30

 

10

 

?

90000

 

?

 

?

 

?

?

47

 

13

 

?

90300

 

?

 

?

 

?

?

62

 

16

 

?

90500

 

?

 

?

 

?

?

?

 

?

 

?

 

 

?

 

?

?

 

Вариант 20. Оценить влияние изменения расхода материалов на изменение удельной материалоемкости изделия.

 

Материалы

1998 г.

1999 г.

Количество материала, т

Затраты на материалы в ценах 1999 г.

Количество материала, т

Цена 1 кг

материала, У.Е.

Затраты на материалы, У.Е.

1

2

3

4

5

6

Сталь 3Х13

Сталь 12ХР3А

Дюралюминий

Бронза

Латунь

130

126

56

13

34

?

?

?

?

?

165

123

78

12

31

3,4

4,3

4,2

8,7

5,3

?

?

?

?

?

Итого

?

?

Оценка изменения расхода материалов на изменение удельной материалоемкости изделия, DM

?

 

Примечание. Величина DM вычисляется по формуле:

,

где цена всего изделия равна 25 У.Е.

 

Вариант 21. Сформировать и заполнить накопительную ведомость по переоценке основных средств производства млн. руб.).

 

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

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

Износ

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

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

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

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

12556,4

589,3

?

?

?

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

184,0

51,2

?

?

?

Цех №1

954,4

235,1

?

?

?

Цех №2

821,9

218,9

?

?

?

Цех №3

529,6

124,7

?

?

?

Цех №4

758,4

171,1

?

?

?

Склад №1

580,2

223,3

?

?

?

Склад №2

443,9

98,6

?

?

?

Склад №3

579,0

123,4

?

?

?

Склад №4

322,8

69,8

?

?

?

Итого

?

?

?

?

?

 

Примечание 1. Восстановительная полная стоимость (ВПС) объекта  и восстановительная остаточная стоимость (ВОС) объекта вычисляются по балансовой стоимости (БС) и износу объекта (ИО):

ВПС = БС*К;  ВОС = ОС*К,

где  К = 3,0, если БС > 500 млн. руб.;

       К = 2,0, если БС £ 500 млн. руб.

Примечание 2. Для заполнения столбцов использовать функцию ЕСЛИ.

 

Вариант 22. Рассчитать структуру розничной цены продукции (руб.), исходя из следующих данных:

 

Составляющие

Виды продукции

А

В

С

D

1. Себестоимость

2. Рентабельность (25% от п.1)

3. Оптовая цена предприятия

4. Акциз (70% от оптовой цены)

5. Наценка посредника

6. НДС (20% от отпускной цены фирмы)

7. Торговая наценка

8. Розничная цена

 

50

?

?

?

10

 

?

12

?

71

?

?

?

10

 

?

12

?

36

?

?

?

8

 

?

10

?

12

?

?

?

8

 

?

10

?

 

 

 

 

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

 

Составляющие

Сорт

Пшеничный в/с

Пшеничный 1с.

Ржаной в/с

1. Стоимость пшеницы (1 кг), руб.

2. Стоимость ржи (1 кг), руб.

3. Издержки элеваторов, руб

4. Рентабельность затрат элеваторов, %

5. Издержки мельниц, руб.

6. Рентабельность затрат мельниц, %

7. Издержки хлебозаводов, руб.

8. Рентабельность затрат хлебозаводов, %

9. Оптовая цена 1 кг хлеба (при норме выхода продукта 140%)

10. НДС (10% от ст. 9)

11. Отпускная цена 1 кг хлеба с НДС

12. Торговая надбавка, %

13. Розничная цена 1 кг хлеба

 

1,4

0,6

15

0,6

20

0,9

20

 

?

?

?

15

?

1,1

0,6

15

0,5

20

0,8

20

 

?

?

?

15

?

1,5

0,6

15

0,5

20

0,8

20

 

?

?

?

15

?

 

 

 

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

-      до 30000 руб. : 3% в федеральный бюджет, 9% в бюджет субъектов федерации;

-      от 30001 до 60000 руб. : 3% в федеральный бюджет, 2700 руб. + 12% с суммы, превышающей 30000 руб., в бюджет субъектов федерации;

-      от 60001 до 90000 руб. : 3% в федеральный бюджет, 6300 руб. + 17% с суммы, превышающей 60000 руб., в бюджет субъектов федерации;

-      от 90001 до 150000 руб. : 3% в федеральный бюджет, 11400 руб. + 22% с суммы, превышающей 90000 руб., в бюджет субъектов федерации;

-      от 150001 и выше : 3% в федеральный бюджет, 24600 руб. + 32% с суммы, превышающей 150000 руб., в бюджет субъектов федерации.

Подсчитать итоговые суммы к выплате.

 

Вариант 25. Рассчитать прибыль, полученную от реализации трех видов продукции.

 

Показатель

Продукция

Итого

А

В

С

1. Цена изделия, тыс. руб.

2. Количество изделий, реализуемых в рассматриваемом периоде, шт.

3. Выручка от реализации, тыс. руб.

4. Удельный вес каждого изделия в общем объеме реализации, %

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

6. Переменные расходы по каждому виду продукции, тыс. руб.

7. Постоянные расходы в рассматриваемом периоде, тыс. руб.

5

 

500

?

 

?

 

3

 

?

 

10

 

700

?

 

?

 

6

 

?

 

20

 

300

?

 

?

 

12

 

?

 

 

?

?

 

100

 

 

?

 

6000

8. Итого расходов

9. Прибыль

 

?

?

 

 

 

 

Вариант 26. Рассчитать итоговое количество продукции, произведенное в России различными отраслями за 4 года, и относительные показатели каждого года.

 

Продукция

Года

Итого

1994

В % к 1997 г.

В % к общему итогу

1995

В % к 1997 г.

В % к общему итогу

1996

В % к 1997 г.

В % к общему итогу

1997

Топливно-энергетические отрасли

Электроэнергия, млрд. кВт. ч

Нефть,

млн. т

Газ естественный, млрд. м3

Уголь,

млн. т

 

 

 

 

 

 

876

 

310

 

 

607

 

271

 

 

 

 

 

 

?

 

?

 

 

?

 

?

 

 

 

 

 

 

?

 

?

 

 

?

 

?

 

 

 

 

 

 

862

 

298

 

 

595

 

262

 

 

 

 

 

 

?

 

?

 

 

?

 

?

 

 

 

 

 

 

?

 

?

 

 

?

 

?

 

 

 

 

 

 

848

 

293

 

 

601

 

255

 

 

 

 

 

 

?

 

?

 

 

?

 

?

 

 

 

 

 

 

?

 

?

 

 

?

 

?

 

 

 

 

 

 

834

 

297

 

 

571

 

244

 

 

 

 

 

 

?

 

?

 

 

?

 

?

Черная металлургия, млн. т

Чугун

Сталь

Прокат готовых черных металлов

Трубы стальные

 

 

 

36,1

48,7

 

 

 

36,5

 

3,57

 

 

 

?

?

 

 

 

?

 

?

 

 

 

?

?

 

 

 

?

 

?

 

 

 

39,2

51,3

 

 

 

39,1

 

3,72

 

 

 

?

?

 

 

 

?

 

?

 

 

 

?

?

 

 

 

?

 

?

 

 

 

35,6

49,2

 

 

 

38,8

 

3,50

 

 

 

?

?

 

 

 

?

 

?

 

 

 

?

?

 

 

 

?

 

?

 

 

 

37,3

48,4

 

 

 

37,8

 

3,47

 

 

 

?

?

 

 

 

?

 

?

 

 

Вариант 27. Используя рекламную прессу (газета «Реклама»), проанализировать затраты на рекламу производителей товаров и услуг в Томске по различным направлениям. Стоимость рекламных объявлений в газете «Реклама» (в рублях за 1 кв. см): первая полоса - 29, последняя полоса - 18, полоса с программой ТВ - 15, обычная полоса - 9,8 (эти данные оформить отдельной таблицей и ссылаться на них через адреса ячеек).

 

 

Секторы рынка

Площадь объявлений, кв. см

Затраты на рекламу, тыс. руб.

Место по затратам

Первая полоса

Последняя полоса

Полоса с программой ТВ

Обычная полоса

Информационные технологии и услуги

430,8

135,0

208,8

5363,5

?

?

Продовольственные товары

41,3

0

0

4191,3

?

?

Строительно-хозяйственные товары и услуги

149,3

138,5

488,3

3697,8

?

?

Предметы гигиены и санитарии

0

0

0

949,0

?

?

Одежда и обувь

0

0

108,0

534,5

?

?

Мебель и торговое оборудование

134,0

0

0

2071,3

?

?

Лекарства и медицинские услуги

0

0

42,3

568,0

?

?

Автотовары и автоуслуги

50,0

0

0

2648,1

?

?

Бытовая техника и ее обслуживание

0

420,0

0

1680,3

?

?

Недвижимость

76,3

0

0

2087,3

?

?

Итого

?

?

?

?

?

?

 

 

 

 

 

Вариант 28. Рассчитать сумму, которая будет на счету в конце каждого месяца, если в начале года внесено 10000 руб., для различных годовых норм банковского процента  i.

 

Годовая норма i,%

Период, месяц

1

2

3

4

5

6

7

8

9

10

11

12

?

 
4

5

6

7

8

9

11

15

 

 

 

 

 

 

 

 

 

 

 

 

 

Примечание. Формула для расчета сложных процентов имеет вид:

,

где  P0 первоначально вложенная сумма;

Pm сумма, которая будет получена через N лет;

mколичество начислений процентных платежей в течение годового периода;

iгодовая норма процента.

 

Вариант 29.  Одним из показателей, определяющих эффективность планируемых инвестиций, является показатель чистой текущей стоимости, вычисляемый по формуле:

,

где  Rt доходы  (за вычетом налогов) от реализации проекта в t-ом году , руб.;

Сt инвестиции в проект в t-ом году, руб.;

К – норма дохода по проекту;

nпериод реализации проекта в годах.

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

 

Период

1-й проект

2-й проект

3-й проект

4-й проект

Rt

Ct

Rt

Ct

Rt

Ct

Rt

Ct

 

1-й год

 

2-й год

 

3-й год

 

4-й год

 

5-ый год

 

 

0

 

2500

 

3000

 

4300

 

5800

 

 

10000

 

0

 

0

 

0

 

0

 

0

 

2000

 

3200

 

4400

 

6200

 

4000

 

3000

 

2000

 

1000

 

0