информатика
.pdf41
•выберите необходимый тип гистограммы;
•переименуйте гистограмму в «Фактическое выполнение поставок по поставщикам и датам» (рис. 34).
Рис. 34. Гистограмма «Фактическое выполнение поставок по поставщикам и датам»
ВозможностиMSExcelпозволяюттакжепроизвестифильтрацию поконкретнойдатепоставкинепосредственновобластипостроения диаграммы. Например, построим гистограмму по дате: «07.09.10», выбрав ее из предложенного перечня нажатием на кнопку поля «Дата поставки» в области построения – рис. 35, 36.
Рис. 35. Изменение исходных данных в области построения гистограммы «Фактическое выполнение поставок по поставщикам и датам»
42
Рис. 36. Гистограмма «Фактическое выполнение поставок по поставщикам 07.09.2010»
Приведенный пример позволяет продемонстрировать не только способ решения экономических задач, таких как получение данных о поставках средствами MS Excel, но и проводить аналитическую обработкуполученныхданных,производяразличныеманипуляции над ними с целью принятия решений.
2.3.Результатыкомпьютерногоэкспериментаииханализа
2.3.1.Результаты компьютерного эксперимента
Для тестирования правильности решения задачи заполним входные документы и справочники, а затем рассчитаем результаты.
Ведомость учета поставки материалов
|
Код |
Наимено |
Код |
Наимено |
|
|
|
Дата |
вание |
вание |
Сумма |
||||
постав- |
мате- |
||||||
поставки |
постав |
мате |
постав |
ки |
|||
щика |
риала |
||||||
|
|
щика |
|
риала |
|
|
|
5.09.10 |
100 |
Заря |
1001 |
Краска |
5,00 |
|
|
|
|
|
|
|
|
|
|
5.09.10 |
100 |
Заря |
1001 |
Краска |
7,00 |
|
|
|
|
|
|
|
|
|
|
6.09.10 |
101 |
Аврора |
1003 |
Цемент |
3,00 |
|
|
|
|
|
|
|
|
|
|
7.09.10 |
101 |
Аврора |
1005 |
Стекло |
4,00 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
43 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Код |
Наимено |
Код |
Наимено |
|
|
|
Дата |
вание |
вание |
Сумма |
||||
постав- |
мате- |
||||||
поставки |
постав |
мате |
постав |
ки |
|||
щика |
риала |
||||||
|
|
щика |
|
риала |
|
|
|
7.09.10 |
102 |
Восход |
1001 |
Краска |
2,00 |
|
|
|
|
|
|
|
|
|
|
7.09.10 |
102 |
Восход |
1002 |
Лак |
3,00 |
|
|
|
|
|
|
|
|
|
|
7.09.10 |
102 |
Восход |
1003 |
Цемент |
5,00 |
|
|
|
|
|
|
|
|
|
|
8.09.10 |
103 |
Космос |
1004 |
Кирпич |
1,00 |
|
|
|
|
|
|
|
|
|
|
8.09.10 |
103 |
Космос |
1005 |
Стекло |
2,00 |
|
|
|
|
|
|
|
|
|
|
8.09.10 |
103 |
Космос |
1005 |
Стекло |
5,00 |
|
|
|
|
|
|
|
|
|
|
8.09.10 |
103 |
Космос |
1005 |
Стекло |
5,00 |
|
|
|
|
|
|
|
|
|
Справочник поставщиков
Код |
Наименование |
Адрес |
Расчетный |
|
постав- |
постав- |
|||
поставщика |
счет |
|||
щика |
щика |
|||
|
|
|||
100 |
Заря |
Москва |
11111111111111111111 |
|
101 |
Аврора |
Казань |
22222222222222222222 |
|
102 |
Восход |
Пермь |
33333333333333333333 |
|
103 |
Космос |
Тверь |
44444444444444444444 |
|
|
|
|
|
|
104 |
Азов |
Тула |
55555555555555555555 |
Справочник материалов
Код материала |
Наименование мате- |
Единица измерения |
|
риала |
|
1001 |
Краска |
кг |
1002 |
Лак |
кг |
|
|
|
1003 |
Цемент |
т |
1004 |
Кирпич |
шт. |
|
|
|
1005 |
Стекло |
м2 |
44
Фактическое выполнение поставок
|
|
|
Наименование постав- |
Сумма поставок |
|
|
|
|
|
|
щика |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Аврора |
7,00 |
|
|
|
|
|
|
|
|
|
|
|
|
Восход |
10,00 |
|
|
|
|
|
|
|
|
|
|
|
|
Заря |
12,00 |
|
|
|
|
|
|
|
|
|
|
|
|
Космос |
13,00 |
|
|
|
|
|
|
|
|
|
|
|
|
Общий итог |
42,00 |
|
|
|
|
|
|
|
|
|
|
Выполнение поставок по поставщикам и датам |
|||||
Дата |
|
Наименование |
Сумма поставки фактическая |
|||
|
поставщика |
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
5.09.10 |
|
|
|
Заря |
12,00 |
|
|
|
|
|
Всего |
12,00 |
|
|
|
|
|
|
|
|
6.09.10 |
|
|
|
Аврора |
3,00 |
|
|
|
|
|
Всего |
3,00 |
|
|
|
|
|
|
|
|
7.09.10 |
|
|
|
Аврора |
4,00 |
|
|
|
|
|
Восход |
10,00 |
|
|
|
|
|
Всего |
14,00 |
|
|
|
|
|
|
|
|
8.09.10 |
|
|
|
Космос |
8,00 |
|
|
|
|
|
Всего |
8,00 |
|
|
|
|
|
|
|
|
9.09.10 |
|
|
|
Космос |
5,00 |
|
|
|
|
|
Всего |
5,00 |
|
|
|
|
|
|||
Общий итог |
42,00 |
|
||||
|
|
|
|
|
|
|
Врезультатерешениязадачиполученныеспомощьюкомпьютера ведомости совпадают с тестовыми.
2.3.2. Анализ полученных результатов
Таким образом, формирование сводных таблиц на основе «Ведомости учета поставки материалов» позволяет решить постав-
45
леннуюзадачу–отслеживатьсоблюдениеграфикаиобъемовпоста- вок и контролировать своевременность погашения и уменьшение объема кредиторской задолженности. Создание различных диаграмм (гистограмм, графиков) на основе данных сводных таблиц средствами MS Excel позволяет не только наглядно представлять результатыобработкиинформациидляпроведенияанализасцелью принятия решений, но и достаточно быстро осуществлять манипуляции в области их построения в пользу наиболее удобного представления результатов визуализации по задаваемым пользователем (аналитиком) параметрам.
5. Варианты заданий для выполнения практической части курсовой работы
Предложенные экономические задачи следует решать с использованием табличного процессора MS Excel. Отсутствующие числовые значения данных и единицы их измерения задаются студентами самостоятельно.
Вариант 1
ПредприятиеООО«Энергос»осуществляетдеятельность,связанную с обеспечением электроэнергией физических и юридических лиц, и производит расчеты по предоставленным услугам. Данные, на основании которых производятся расчеты по оплате, представлены на рис. 1.1.
1.Построить таблицу согласно рис. 1.1.
2.Результаты вычислений представить в виде таблицы, содержащей данные о расходе электроэнергии и сумму к оплате (рис. 1.2),
ив графическом виде.
3.Организовать межтабличные связи для автоматического формирования документа «Квитанция об оплате электроэнергии» при помощи функций ВПР или ПРОСМОТР.
4.Сформировать и заполнить квитанцию на оплату электроэнер-
гии (рис. 1.3).
5.Построить и проанализировать графический отчет по полученным результатам.
46
Показания электросчетчиков
месяц: декабрь 2010
|
Код |
|
|
|
|
|
Показания |
Показания |
|
|
|
|
|
|
|
|
счетчика |
счетчика |
|
||
|
пла- |
ФИО платель- |
|
|
|
|||||
|
|
Адрес |
на начало |
на конец |
|
|||||
|
тель- |
щика |
|
|
|
|||||
|
|
|
|
месяца, |
месяца, |
|
||||
|
щика |
|
|
|
|
|
|
|||
|
|
|
|
|
|
КВт |
КВт |
|
||
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
проспект |
|
|
|
|
|
|
001 |
Коломиец И.И. |
Мира, 44-1 |
34 578 |
|
34 278 |
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
проспект |
|
|
|
|
|
|
002 |
Петров А.А. |
|
Мира, 44-3 |
23 256 |
|
23 296 |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
проспект |
|
|
|
|
|
|
003 |
Матвеева К.К. |
Мира, 44-5 |
34 589 |
|
34 620 |
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
проспект |
|
|
|
|
|
|
004 |
Сорокина М.М. |
Мира, 44-7 |
98 554 |
|
98 700 |
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
проспект |
|
|
|
|
|
|
005 |
Ивлев С.С. |
|
Мира, 44-9 |
45 544 |
|
45 900 |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
Рис. 1.1. Данные о показаниях электросчетчиков |
|
|||||||
|
|
|
Расчет оплаты электроэнергии |
|
|
|||||
|
Тариф за 1 КВт – 3р. |
|
|
|
месяц:декабрь2010 |
|
||||
|
|
|
|
|
|
|
||||
|
ФИО платель- |
Код пла- |
Расход электроэнер- |
|
К оплате, |
|
||||
|
щика |
тельщика |
гии за месяц, КВт |
|
руб. |
|
||||
|
|
|
|
|
|
|
|
|
|
|
|
Коломиец И.И. |
|
001 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Матвеева К.К. |
|
003 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Ивлев С.С. |
|
005 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Петров А.А. |
|
002 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Сорокина М.М. |
|
004 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Итого |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Рис. 1.2. Расчет оплаты электроэнергии
47
ООО «Энергос»
Месяц |
______ |
|
20__г. |
||
|
Кодплательщика 001
КВИТАНЦИЯ НА ОПЛАТУ ЭЛЕКТРОЭНЕРГИИ
ФИО плательщика |
|
|
||
Тариф за 1 КВт |
|
. |
|
|
|
|
|
|
|
Показания |
Показания |
Расход элек- |
К |
|
счетчика на |
счетчика |
троэнергии |
||
оплате, |
||||
начало месяца, |
на конец |
за месяц, |
||
руб. |
||||
КВт |
месяца, КВт |
КВт |
||
|
||||
|
|
|
|
|
|
|
|
|
Рис. 1.3. Квитанция на оплату электроэнергии
Вариант 2
В бухгалтерии предприятия ООО «Бета» производится расчет налоговых вычетов, предоставляемых сотрудникам, и формирование платежных ведомостей. Данные для выполнения расчета налоговых вычетов приведены на рис. 2.1. Стандартный налоговый вычет предоставляется каждому сотруднику в размере 400 руб. в месяц до тех пор, пока совокупный доход с начала года не превысит 40 000 руб., налоговый вычет на ребенка предоставляется в размере 1000 руб. в месяц до тех пор, пока совокупный доход с начала года не превысит 280 000 руб. НДФЛ – налог на доходы физических лиц (13%) – рассчитывается с начисленной суммы за вычетом размера налоговых вычетов.
48
1.Построить таблицы по приведенным ниже данным.
2.Выполнить расчет размера налогового вычета, предоставляе мого сотрудникам в текущем месяце с использованием функций ВПР или ПРОСМОТР, результаты вычислений представить в виде таблицы (рис. 2.2).
3.Сформировать и заполнить форму документа «Расчетная ведомость по заработной плате» за текущий месяц (рис. 2.3).
4.Построить и проанализировать графический отчет по полученным результатам.
ФИО сотрудника |
Начислено за |
Совокупный доход |
|
месяц, руб. |
с начала года, руб. |
||
|
|||
|
|
|
|
Васечкин М.М. |
5 890,00 |
36 000,00 |
|
|
|
|
|
Иванов И.И. |
7 800,00 |
25 000,00 |
|
|
|
|
|
Кузнецова С.С. |
6 350,00 |
32 000,00 |
|
|
|
|
|
Петров А.А. |
9 500,00 |
24 000,00 |
|
|
|
|
|
Сидорова К.К. |
10 200,00 |
39 000,00 |
|
|
|
|
Рис. 2.1. Данные для расчета налоговых вычетов
|
Стандартный |
Количество |
Размер |
|
|
детей, на кото- |
налогового |
||
ФИО сотруд- |
налоговый |
|||
рых предостав- |
вычета за |
|||
ника |
вычет на физ. |
|||
ляется налого- |
текущий |
|||
|
лицо, руб. |
|||
|
вый вычет |
месяц, руб. |
||
|
|
|||
|
|
|
|
|
Васечкин М.М. |
400,00 |
– |
|
|
|
|
|
|
|
Иванов И.И. |
400,00 |
2 |
|
|
|
|
|
|
|
Кузнецова С.С. |
400,00 |
2 |
|
|
|
|
|
|
|
Петров А.А. |
400,00 |
1 |
|
|
|
|
|
|
|
Сидорова К.К. |
400,00 |
3 |
|
|
|
|
|
|
Рис. 2.2. Размер налоговых вычетов, предоставляемых сотрудникам в текущем месяце
49
ООО «Бета» |
|
|
|
|
|
||
|
|
|
|
|
|
||
|
|
|
Расчетный период |
|
|
||
|
|
|
|
|
|
|
|
|
|
|
с |
|
по |
|
|
|
|
|
|
|
|
|
|
|
|
|
__.__.20__ |
|
__.__.20__ |
|
|
|
|
|
|
|
|||
РАСЧЕТНАЯ ВЕДОМОСТЬ |
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
|
Начис |
|
Размер |
|
К |
Табель |
|
|
|
налого |
|
||
ФИО сотруд- |
лено за |
|
НДФЛ, |
вып |
|||
ный |
ника |
месяц, |
|
вого |
руб. |
лате, |
|
номер |
|
вычета, |
|||||
|
|
|
руб. |
|
руб. |
|
руб. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
0001 |
Иванов И.И. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
0002 |
Петров А.А. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
0003 |
ВасечкинМ.М. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
0004 |
Сидорова К.К. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
0005 |
КузнецоваС.С. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Итого по ведомости
Главный бухгалтер
Рис. 2.3. Расчетная ведомость
Вариант 3
Компания «Страхование» осуществляет страховую деятельность натерриторииРоссииповидамполисов,представленныхнарис.3.1. Каждый полис имеет фиксированную цену.
Компания имеет свои филиалы в нескольких городах (рис. 3.2) ипоощряетразвитиекаждогофилиала,предоставляяопределенный дисконт. Дисконт пересматривается ежемесячно по итогам общих сумм договоров по филиалам.
Вконцекаждогомесяцасоставляетсяобщийреестрдоговоровпо всем филиалам (рис. 3.3).
50
1.Построить таблицы (рис. 3.1, 3.2, 3.3).
2.Организовать межтабличные связи для автоматического заполнениядокумента«Реестрдоговоров»припомощифункцийВПРили ПРОСМОТР (рис. 3.3).
3.Произвести расчет суммы полисов по филиалам.
4.Построить и проанализировать графический отчет по полученным результатам.
Код вида стра- |
Наименование |
|
|
Сумма |
|||
хового полиса |
страхового полиса |
|
страхового полиса, руб. |
||||
|
|
|
|
|
|
|
|
101 |
От несчастного случая |
|
20 000 |
|
|||
|
|
|
|
|
|
|
|
102 |
От автокатастрофы |
|
|
60 000 |
|
||
|
|
|
|
|
|
|
|
103 |
От авиакатастрофы |
|
|
50 000 |
|
||
|
|
|
|
|
|
|
|
104 |
Медицинский |
|
|
30 000 |
|
||
|
|
|
|
|
|
|
|
105 |
Автомобильный |
|
|
90 000 |
|
||
|
|
|
|
|
|
|
|
106 |
Жилищный |
|
|
700 000 |
|
||
|
|
|
|
|
|
||
|
|
Рис. 3.1. Виды страховых полисов |
|||||
|
|
|
|
|
|
||
|
Код |
Наименование |
|
Дисконтный процент |
|
||
|
фили- |
|
|
с каждого полиса |
|
||
|
ала |
филиала |
|
|
|
по филиалу |
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
100 |
Московский |
|
|
3% |
|
|
|
|
|
|
|
|
|
|
|
200 |
Тульский |
|
|
2% |
|
|
|
|
|
|
|
|
|
|
|
300 |
Уфимский |
|
|
1% |
|
|
|
|
|
|
|
|
|
|
|
400 |
Липецкий |
|
|
2% |
|
|
|
|
|
|
|
|
|
|
|
500 |
Ростовский |
|
|
3% |
|
|
|
|
|
|
|
|
|
|
|
600 |
Воронежский |
|
|
2% |
|
|
|
|
|
|
|
|
|
|
Рис. 3.2. Список филиалов компании «Страховщик»