Новые технологии в экономических информационных системах (стр. 22 )

Рисунок 3.33 - График линии тренда

Как видно из рисунка, квадрат коэффициента корреляции равен 0.9723, следовательно, линейная модель может быть использована для предсказания результатов.

На основе найденных коэффициентов уравнения регрессии можно определить теоретическое значение наблюдаемой величины у. Вычислим теоретическое значение у в ячейке С2, показанной на рисунке 14, при х из А2 по формуле

=$D$2*A2+$E$2

Однако теоретическое значение у в фиксированной точке можно вычислить и без предварительного определения коэффициентов линейной модели с помощью функции ПРЕДСКАЗ.

Синтаксис:

ПРЕДСКАЗ(t; известные_значения_у; известные_значения_х).

Аргументы:

-  t - точка данных, для которой предсказывается значение;

-  известные_значения_у - массив известных значений зависимой наблюдаемой величины;

-  известные_значения_х - массив известных значений независимой наблюдаемой величины. Если аргумент известные_значения_х опущен, то предполагается, что это массив {1; 2; 3; ...} такого же размера, как и массив известные_значения_у.

Например, теоретическое значение в ячейке С2 на рисунке 14 можно также определить по формуле

=ПРЕДСКАЗ(А2;$В$2:$В$7;$А$2:$А$7).

Функция ТЕНДЕНЦИЯ вычисляет значения уравнения линейной регрессии для целого диапазона значений независимой переменной как для одномерного, так и для многомерного уравнения регрессии. Многомерная линейная модель регрессии имеет вид:

Синтаксис:

ТЕНДЕНЦИЯ (известные_значения_у; известные_значения_х; новые_значения_х; конст).

Аргументы:

-  известные_значения_у - массив известных значений зависимой наблюдаемой величины;

-  известные_значения_х - массив известных значений независимой наблюдаемой величины. Если аргумент известные_значения_х опущен, то предполагается, что это массив {1; 2; 3;...} такого же размера, как и массив известные_значения_у;

-  новые_значения_х - новые значения х, для которых функция тенденция возвращает соответствующие значения у;

-  конст - логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если аргумент конст имеет значение истина или опущен, то b вычисляется обычным образом. Если конст имеет значение ЛОЖЬ, то b полагается равным 0.

Если строится многомерная линейная модель, то аргументы известные_значения_х и новые_значения_х должны содержать столбец (или строку) для каждой независимой переменной. Если аргумент новые_значения_х опущен, то предполагается, что он совпадает с аргументом известные_значения_х.

Функция ЛИНЕЙН возвращает массив значений параметров уравнения многомерной линейной регрессии.

Синтаксис:

ЛИНЕЙН (известные_значения_у; известные_значения_х; конст; статистика).

Аргументы:

-  известные_значения_у - массив известных значений зависимой наблюдаемой величины;

-  известные_значения_х - массив известных значений независимой наблюдаемой величины. Если аргумент известные_значения_х опущен, то предполагается, что это массив {1;2;3;...} такого же размера, как и известные_значения_у;

-  конст - логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если аргумент конст имеет значение ИСТИНА или опущен, то b вычисляется обычным образом. Если конст имеет значение ЛОЖЬ, то b полагается равным 0;

-  статистика - логическое значение, которое указывает, требуется ли вывести дополнительную статистику по регрессии, например, коэффициент корреляции. Если статистика имеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику. Если аргумент статистика имеет значение ЛОЖЬ или опущен, то функция ЛИНЕЙН возвращает только значения коэффициентов.

Контрольные вопросы

1.  Какие существуют методы регрессионного анализа?

2.  Какие функции участвуют в регрессионном анализе?

3.  Назначение линии тренда?

4.  Какие линии тренда можно использовать?

Практическая работа № 23

Автоматизация расчета заработной платы при повременной оплате труда

Задание

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

Методика выполнения практической работы

Для выполнения поставленной задачи необходимо использовать пакет прикладной программы Excel. На одном листе файла необходимо расположить базу данных на трех работников. Листу книги Excel присвоить имя «Исходные данные». Форма базы данных представлена в таблице 3.35.

Таблица 3.35 – База данных

Ячейкам, выделенным серым цветом, рекомендуется присвоить имена типа «Месяц», «МРП», «МЗП», «ОбязПенсОтчисления» и т. д. Напоминаем, что присвоенные имена ячеек не должны содержать пробелов.

Остальные поля заполняются на трех работников.

Все поля заполняются вручную с учетом существующего законодательства.

Поля Сумма заработной платы и количество рабочих дней за отработанный период содержат ссылку на ячейки ИТОГО из таблицы расчета заработной платы на рисунке 2.

На втором рабочем листе, названном «Квитанция», необходимо расположить расчетный листок на каждого работника, представленный на рисунке 3.34.

Рисунок 3.34 – Квитанция к заработной плате

Ячейке, выделенной серым цветом, необходимо присвоить имя «Начислено».

Месяц заполняется автоматически, используя ссылку на ячейку «Месяц» в базе данных на рабочем листе «Зарплата»

Начисление по окладу в ячейке B3 вычисляется по формуле: Оклад*РабДнейФакт/РабДнейПлан.

Так как надбавка указывается в процентах от оклада, начисление премии происходит путем умножения размера надбавки на оклад.

Среднедневной заработок равен частному от деления суммы заработной платы за отработанный период на количество рабочих дней за отработанный период.

Начисление по больничному листу производится по формуле:

=ЕСЛИ(СредДневЗар*КоличБольничнДней<=15*МРП;СредДневЗП*КоличБольничнДней; 15*МРП).

Отпускные начисляются как произведение среднего дневного заработка на количество отпускных дней.

Итоговое начисление определяется функцией СУММА всех произведенных начислений.

Удержание пенсионных взносов в ячейке D3 производится по формуле:

=СУММЕСЛИ(А4:А15;Месяц;D4:D15) по данным таблицы рисунка 2.

Индивидуальный подоходный налог вычисляется в ячейке D4по формуле:

=СУММЕСЛИ(А4:А15;Месяц;G4:G15) по данным таблицы рисунка 2.

Удержание по исполнительному листу производится в ячейке D6 по формуле: =ПроцентВыплатПоИсполЛисту*(Доход за текущий месяц– ИПН).

Сумма кредита оформляется ссылкой из базы данных на работника.

Итого к удержанию считается по формуле =СУММ(D3:D6).

Итоговая сумма к выдаче равняется разности между итоговым начислением и удержанием.

На другом рабочем листе необходимо расположить таблицы расчета заработной платы на каждого работника представленные на рисунке 3.35.

Сумма полученного дохода за текущий месяц вычисляется по формуле =ЕСЛИ(А4=Месяц; Начислено; Клетка равна сама себе).

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

СервисàПараметры à закладка Вычисления à Итерации включить и установить минимум 3 обращения.

Затем эта формула копируется вниз в другие ячейки.

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

Сумма налогооблагаемого дохода равна сумме полученного дохода за вычетом минимальной заработной платы и взносов в пенсионный фонд.

Сумма ИПН, равна произведению суммы дохода облагаемого ИПН на 10%.

Расходы работодателя, облагаемые социальным налогом, определяются как разность начисленного дохода за текущий месяц и суммы обязательных пенсионных отчислений за текущий месяц.

На третьем листе сформировать платежную ведомость, которую можно представить таблицей 3.36.

Таблица 3.36 – Макет платежной ведомости.

Заполняя графы ФИО работника использовать функции по работе с тестом: СЦЕПИТЬ, ЛЕВСИМВОЛ.

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

Рисунок 3.35 – Таблица расчета заработной платы

Практическая работа № 24

Автоматизация учета денежных средств

Задание

Задание 1 Оформить исходные данные учета кассовых и расчетных операций.

Задание 2. Оформить документы аналитического учета кассовых и расчетных операций

Задание 3 Оформить документы кассовых и расчетных операций

Методика выполнения практической работы

Для выполнения поставленной задачи необходимо использовать пакет прикладной программы Excel. На одном листе файла необходимо расположить приходный и расходный кассовый ордер и выписку из банка. Листу книги Excel присвоить имя «Исходные данные». Форму ПКО и РКО можно представить в виде рисунка 3.36.

Рисунок 3.36 - Макет документа «Приходный кассовый ордер»

Для номера документа и даты выполнения операции необходимо отвести отдельные ячейки. Ячейкам, выделенным серым цветом рекомендуется присвоить имена типа «номерПКО», «содержаниеПКО», «суммаРКО» и т. д. Напоминаем, что присвоенные имена ячеек не должны содержать пробелов.

Форма выписки из банка представлена на рисунке 3.37, Выделенным ячейкам следует присвоить имена типа «НомерВыписки» и «Дата Выписки». Также необходимо присвоить имена диапазонам ячеек рабочей области формы, которые относятся к «счету», «сумма прихода» и «сумма расхода». Присвоенные имена могут выглядеть как «СчетВыписки», «ПриходВыписки» и «РасходВыписки».

Форма «Выписки из банка» заполняется по дате, которая фиксируется Е ячейке «ДатаВыписки», но отражает информацию за определенный период, который указывается в соответствующих ячейках.

Количество строк данного документа неограниченно и зависит oi количества проведенных операций за данный период.

На следующем листе файла, который следует назвать «Кассовая книга создаем форму документа кассовой книги в виде рисунка 3.38.

Рисунок 3.37 - Форма документа «Выписка из банка»

Рисунок 3.38 - Форма кассовой книги

Из за большого объема этот материал размещен на нескольких страницах: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28