Результаты компьютерного эксперимента и анализа полученных результатов
3.3.1. Результаты компьютерного эксперимента
Для тестирования правильности решения задачи заполним входные документы и справочники, а за тем рассчитаем результаты.
Ведомость учета поставки материалов
| Дата поставки | Код поставщика | Наименование поставщика | Код материала | Наименование материала | Сумма поставки |
| 5.09.10 | Заря | Краска | 5.00 | ||
| 5.09.10 | Заря | Краска | 7.00 | ||
| 6.09.10 | Аврора | Цемент | 3.00 | ||
| 7.09.10 | Аврора | Стекло | 4.00 | ||
| 7.09.10 | Восход | Краска | 2.00 | ||
| 7.09.10 | Восход | Лак | 3.00 | ||
| 7.09.10 | Восход | Цемент | 5.00 | ||
| 8.09.10 | Космос | Кирпич | 1.00 | ||
| 8.09.10 | Космос | Стекло | 2.00 | ||
| 8.09.10 | Космос | Стекло | 5.00 | ||
| 8.09.10 | Космос | Стекло | 5.00 |
Справочник поставщиков
| Код поставщика | Наименование поставщика | Адрес поставщика | Расчетный счет |
| Заря | Москва | ||
| Аврора | Казань | ||
| Восход | Пермь | ||
| Космос | Тверь | ||
| Азов | Тула |
| Код материала | Наименование материала | Единица измерения |
| Краска | кг | |
| Лак | кг | |
| Цемент | т | |
| Кирпич | шт | |
| Стекло | м2 |
| Наименование поставщика | Сумма поставок |
| Аврора | 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. Анализ полученных результатов
Таким образом, формирование сводных таблиц на основе «Ведомости учета поставки материалов» позволяет решить поставленную задачу – отслеживать соблюдения графика и объемов поставок и контролировать своевременность погашения и уменьшение объема кредиторской задолженности. Создание различных диаграмм (гистограмм, графиков) на основе данных сводных таблиц средствами MSExcel позволяет не только наглядно представлять результаты обработки информации для проведения анализа с целью принятия решений, но и достаточно быстро осуществлять манипуляции в области их построения в пользу наиболее удобного представления результатов визуализации по задаваемым пользователем (аналитиком) параметрам.
Варианты заданий для выполнения практической части курсовой работы
Предложенные экономические задачи следует решать с использованием табличного процессора MS Excel. Отсутствующие числовые значения данных и единицы их измерения задаются студентами самостоятельно.
ВАРИАНТ 1
Предприятие ООО «Энергос» осуществляет деятельность, связанную с обеспечением электроэнергией физических и юридических лиц, и производит расчеты по предоставленным услугам. Данные, на основании которых производятся расчеты по оплате, представлены на рис. 1.1.
1. Построить таблицу согласно рис. 1.1.
2. Результаты вычислений представить в виде таблицы, содержащей данные о расходе электроэнергии и сумму к оплате (рис. 1.2), и в графическом виде.
3. Организовать межтабличные связи для автоматического формирования документа “Квитанция об оплате электроэнергии” при помощи функций ВПР или ПРОСМОТР.
4. Сформировать и заполнить квитанцию об оплате электроэнергии (рис. 1.3).
5. Построить и проанализировать графический отчет по полученным результатам.
| Показания электросчетчиков | ||||
| месяц: декабрь 2010 | ||||
| Код плательщика | ФИО плательщика | Адрес | Показания счетчика на начало месяца, КВт | Показания счетчика на конец месяца, КВт |
| Коломиец И.И. | проспект Мира, 44-1 | |||
| Петров А.А. | проспект Мира, 44-3 | |||
| Матвеева К.К. | проспект Мира, 44-5 | |||
| Сорокина М.М. | проспект Мира, 44-7 | |||
| Ивлев С.С. | проспект Мира, 44-9 | |||
| Рис. 1.1. Данные о показаниях электросчетчиков |
| Расчет оплаты электроэнергии | ||||
| Тариф за 1 КВт - 3р. | месяц: декабрь 2010 | |||
| ФИО плательщика | Код плательщика | Расход электроэнергии за месяц, КВт | К оплате, руб. | |
| Коломиец И.И. | ||||
| Матвеева К.К. | ||||
| Ивлев С.С. | ||||
| Петров А.А. | ||||
| Сорокина М.М. | ||||
| ИТОГО | ||||
Рис. 1.2. Расчет оплаты электроэнергии
| ООО "Энергос" | |||||
| Месяц | ______ 20__г. | ||||
| Код плательщика | |||||
| КВИТАНЦИЯ НА ОПЛАТУ ЭЛЕКТРОЭНЕРГИИ | |||||
| ФИО плательщика | |||||
| Тариф за 1 КВт | . | ||||
| Показания счетчика на начало месяца, КВт | Показания счетчика на конец месяца, КВт | Расход электроэнергии за месяц, КВт | К оплате, руб. | ||
Рис. 1.3. Квитанция на оплату электроэнергии
ВАРИАНТ 2
В бухгалтерии предприятия ООО «Бета» производится расчет налоговых вычетов, предоставляемых сотрудникам, и формирование платежных ведомостей. Данные для выполнения расчета налоговых вычетов приведены на рис. 2.1. Стандартный налоговый вычет предоставляется каждому сотруднику в размере 400 руб. в месяц до тех пор, пока совокупный доход с начала года не превысит 40 000 руб., налоговый вычет на ребенка предоставляется в размере 1000 руб. в месяц до тех пор, пока совокупный доход с начала года не превысит 280 000 руб. НДФЛ - налог на доходы физических лиц (13%) - рассчитывается с начисленной суммы за минусом размера налоговых вычетов.
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 | ||
| Кузнецова С.С. | 400,00 | ||
| Петров А.А. | 400,00 | ||
| Сидорова К.К. | 400,00 |
Рис. 2.2. Размер налоговых вычетов, предоставляемых сотрудникам в текущем месяце
| ООО "Гамма" | ||||||||||
| Расчетный период | ||||||||||
| С | по | |||||||||
| __.__.20__ | __.__.20__ | |||||||||
| РАСЧЕТНАЯ ВЕДОМОСТЬ | ||||||||||
| Табельный номер | ФИО сотрудника | Начислено за месяц, руб. | Размер налогового вычета, руб. | НДФЛ, руб. | К выплате, руб. | |||||
| Иванов И.И. | ||||||||||
| Петров А.А. | ||||||||||
| Васечкин М.М. | ||||||||||
| Сидорова К.К. | ||||||||||
| Кузнецова С.С. | ||||||||||
| ИТОГО ПО ВЕДОМОСТИ | ||||||||||
| Главный бухгалтер | ||||||||||
Рис. 2.3. Расчетная ведомость
ВАРИАНТ 3
Компания «Страхование» осуществляет страховую деятельность на территории России по видам полисов, представленных на рис. 3.1. Каждый полис имеет фиксированную цену.
Компания имеет свои филиалы в нескольких городах (рис. 3.2) и поощряет развитие каждого филиала, предоставляя определенный дисконт. Дисконт пересматривается ежемесячно по итогам общих сумм договоров по филиалам.
В конце каждого месяца составляется общий реестр договоров по всем филиалам (рис. 3.3).
1. Построить таблицы (рис. 3.1, 3.2, 3.3).
2. Организовать межтабличные связи для автоматического заполнения документа “Реестр договоров” при помощи функций ВПР или ПРОСМОТР (рис. 3.3.).
3. Произвести расчет суммы полисов по филиалам.
4. Построить и проанализировать графический отчет по полученным результатам.
| Код вида страхового полиса | Наименование страхового полиса | Сумма страхового полиса, руб. |
| От несчастного случая | 20 000 | |
| От автокатастрофы | 60 000 | |
| От авиакатастрофы | 50 000 | |
| Медицинский | 30 000 | |
| Автомобильный | 90 000 | |
| Жилищный | 700 000 |
Рис. 3.1. Виды страховых полисов
| Код филиала | Наименование филиала | Дисконтный % с каждого полиса по филиалу |
| Московский | 3% | |
| Тульский | 2% | |
| Уфимский | 1% | |
| Липецкий | 2% | |
| Ростовский | 3% | |
| Воронежский | 2% |
Рис. 3.2. Список филиалов компании «Страховщик»
| Код филиала | Наименование филиала | Код страхового полиса | Наименование полиса | Дата выдачи полиса | Сумма полиса, руб. | Сумма скидки по дисконту, руб. |
| 11.11.10 | ||||||
| 12.11.10 | ||||||
| 13.11.10 | ||||||
| 14.11.10 | ||||||
| 11.11.10 | ||||||
| 16.11.10 | ||||||
| 17.11.10 | ||||||
| 12.11.10 | ||||||
| 19.11.10 | ||||||
| 20.11.10 |
Рис. 3.3. Табличные данные реестра договоров
ВАРИАНТ 4
Произвести расчет платежа по кредиту клиентом банка (рис. 4.1). Ежемесячное погашение кредита осуществляется равными (аннуитетными) платежами.
Погашение основного долга определяется как отношение суммы кредита к количеству месяцев, на которые выдан кредит. Результаты округлить до целого, используя функцию ОКРУГЛ().
Сумма процентов определяется как произведение суммы текущего остатка по кредиту на процентную ставку в месяц. Процентная ставка в месяц равна отношению процентной ставки кредита к количеству месяцев, на который выдан кредит.
Сумма текущего остатка по кредиту определяется как разница между суммой предыдущего остатка по кредиту и текущей суммы погашения основного долга.
Платеж по кредиту определяется как сумма текущей суммы процента по кредиту и текущей суммы погашения основного долга.
Результаты округлить до целого, используя функцию ОКРУГЛ(). Для того, чтобы итоговая сумма погашения основного долга равнялась сумме выданного кредита, использовать функцию ЕСЛИ() для отражения остатков по платежу в последнем платеже. Сумма последнего платежа по погашению основного долга будет больше, чем платежи за предыдущие месяцы.
По данным таблицы (рис. 4.1) построить гистограмму с отражением платежей по кредиту по месяцам.
| Платежи по кредиту клиента _______________ банка"Акцепт+"за 2010г. | |||||
| Годовая процентная ставка | 12% | ||||
| Кредит выдан на | 12 месяцев | ||||
| Сумма кредита, руб. | |||||
| Номер платежа | Дата платежа | Текущий остаток по кредиту, руб. | Сумма процентов, руб. | Погашение основного долга, руб. | Платеж по кредиту, руб. |
| Январь 2010 | |||||
| Февраль 2010 | |||||
| Март 2010 | |||||
| Апрель 2010 | |||||
| Май 2010 | |||||
| Июнь 2010 | |||||
| Июль 2010 | |||||
| Август 2010 | |||||
| Сентябрь 2010 | |||||
| Октябрь 2010 | |||||
| Ноябрь 2010 | |||||
| Декабрь 2010 | |||||
| Итого |
Рис. 4.1. Платежи по кредиту клиента банка «Акцепт+»
ВАРИАНТ 5
Произвести расчет стоимости междугородних телефонных разговоров абонента по заданным значениям. Для определения дня недели, когда производился звонок, следует использовать функцию ДЕНЬНЕД(), а также функции ЕСЛИ() и ИЛИ() (рис. 5.1, рис. 5.2).
Сформировать документ “Квитанция для оплаты телефонных разговоров”. По данным квитанции построить гистограмму с отражением стоимости звонков на определенную дату.
| Тарифы на услуги междугородней телефонной связи ОАО "Россвязь" для абонентов квартирного сектора | |||
| Наименование города | Код города | в рабочие дни, руб. | в выходные дни, руб. |
| Волгоград | 4,50 | 4,20 | |
| Киров | 4,50 | 4,20 | |
| Пенза | 5,50 | 4,50 | |
| Челябинск | 7,50 | 4,80 | |
| Новосибирск | 7,50 | 4,80 |
Рис. 5.1. Тарифы на услуги междугородней телефонной связи
| Квитанция для оплаты телефонных разговоров | ||||
| Номер телефона | 123-45-67 | |||
| Дата | Код города | Минут | Стоимость, руб. | |
| 15.01.2010 | ||||
| 16.01.2010 | ||||
| 17.01.2010 | ||||
| 18.01.2010 | ||||
| 23.01.2010 | ||||
| Итого | ||||
| Срок оплаты счета до: | ||||
Рис. 5.2. Квитанция для оплаты телефонных разговоров
ВАРИАНТ 6
Исходные данные для расчета заработной платы организации представлены на рис. 6.1 и 6.2.
1. Построить таблицы по приведенным ниже данным.
2. В таблице на рис. 6.3 для заполнения столбцов «Фамилия» и «Отдел» использовать функцию ПРОСМОТР().
3. Для получения результата в столбце «Сумма по окладу», используя функцию ПРОСМОТР(), по табельному номеру найти соответствующий оклад, разделить его на количество рабочих дней и умножить на количество отработанных дней. Сумма по надбавке считается аналогично.
4. Сформировать документ “Ведомость зарплаты сотрудников”.
5. Данные результатной таблицы отсортировать по номеру отдела и рассчитать итоговые суммы по отделам.
6. Построить и проанализировать графический отчет по полученным результатам.
| Табельный номер | Фамилия | Отдел | Оклад, руб. | Надбавка, руб. |
| Иванова И.И. | Отдел кадров | 7 000,00 | 4 000,00 | |
| Петрова П.П. | Бухгалтерия | 9 500,00 | 3 000,00 | |
| Сидорова С.С. | Отдел кадров | 6 000,00 | 4 500,00 | |
| Мишин М.М. | Столовая | 6 500,00 | 3 500,00 | |
| Васин В.В. | Бухгалтерия | 7 500,00 | 1 000,00 | |
| Львов Л.Л. | Отдел кадров | 4 000,00 | 3 000,00 | |
| Волков В.В. | Отдел кадров | 3 000,00 | 3 000,00 |
Рис. 6.1. Данные о сотрудниках
| Таб. номер | Количество рабочих дней | Количество отработанных дней |
Рис. 6.2. Данные об учете рабочего времени
| Таб. номер | Фамилия | Отдел | Сумма по окладу, руб. | Сумма по надбавке, руб. | Сумма зарплаты, руб. | НДФЛ% | Сумма НДФЛ, руб. | Сумма к выдаче, руб. |
| Всего |
Рис. 6.3. Графы таблицы для заполнения ведомости зарплаты
ВАРИАНТ 7
Торговое предприятие ООО «Электрон» осуществляет розничную продажу бытового оборудования и средств вычислительной техники (СВТ). Для продвижения товара предприятие организует рекламную кампанию, на осуществление которой формируется бюджет в зависимости от объема продаж и показателя отчислений на рекламу. Данные, на основании которых выполняется расчет рекламного бюджета предприятия, приведены на рис. 7.1 и 7.2.
Для решения задачи необходимо:
1. Построить таблицы по приведенным данным рис. 7.1 и 7.2.
2. Рассчитать объем продаж предприятия по каждому месяцу за текущий год (рис. 7.1).
3. Организовать межтабличные связи для автоматического формирования рекламного бюджета с использованием функций ВПР или ПРОСМОТР.
4. Сформировать и заполнить итоговую таблицу, содержащую расчет рекламного бюджета предприятия ООО «Квадра» по месяцам и за текущий год (рис. 7.3).
5. Результаты расчета рекламного бюджета по каждому месяцу представить в графическом виде.
Объем продаж
| Месяц | Объем продаж бытовой техники, руб. | Объем продаж СВТ, руб. | Объем продаж, всего за месяц, руб. |
| Январь | |||
| Февраль | |||
| Март | |||
| Апрель | |||
| Май | |||
| Июнь | |||
| Июль | |||
| Август | |||
| Сентябрь | |||
| Октябрь | |||
| Ноябрь | |||
| Декабрь |
Рис.7.1. Данные об объемах продаж
Показатель отчислений на рекламу, %
| Январь | Февраль | Март | Апрель | Май | Июнь | Июль | Август | Сентябрь | Октябрь | Ноябрь | Декабрь |
| 3,2 | 3,5 | 3,4 | 4,1 | 4,8 | 4,9 | 3,9 | 4,5 | 3,2 | 3,3 | 4,2 | 4,9 |
Рис. 7.2. Данные показателя отчислений на рекламу
Рекламный бюджет
| Месяц | Объем продаж, всего за месяц, руб. | Показатель отчислений на рекламу, % | Рекламный бюджет, руб. |
| Январь | |||
| Февраль | |||
| Март | |||
| Апрель | |||
| Май | |||
| Июнь | |||
| Июль | |||
| Август | |||
| Сентябрь | |||
| Октябрь | |||
| Ноябрь | |||
| Декабрь | |||
| Итого общий рекламный бюджет за год, руб. |
Рис. 7.3. Расчет рекламного бюджета
ВАРИАНТ 8
Туристическая фирма ООО «Турист» осуществляет реализацию путевок на заграничные туры. В стоимость путевок включаются дополнительные обязательные экскурсионные программы. Первичный расчет стоимости путевок выполняется в долларах, которые затем переводятся в рубли по текущему курсу. Данные для выполнения расчетов представлены на рис. 8.1 и 8.2.
Для решения задачи необходимо:
1. Построить таблицы по приведенным данным на рис. 8.1. и 8.2.
2. Рассчитать общую стоимость туров по каждому месту пребывания (рис. 8.1).
3. Организовать межтабличные связи для автоматического формирования стоимости туров в рублях по каждому месту пребывания и по всем странам в целом, используя функции ВПР или ПРОСМОТР.
4. Сформировать и заполнить таблицу с данными по расчету дохода по предоставляемым турам для каждого места пребывания и по всем странам в целом в рублях с количества проданных путевок и обязательной экскурсионной программы (рис. 8.3).
5. Результаты расчетов доходов от реализации путевок по каждому месту пребывания туристов представить в графическом виде и проанализировать результаты.