Средства и инструменты Excel при работе с электронными таблицами помогают получать максимальную отдачу работы с ними: учат проектировать эффективные электронные таблицы; помогают быстро суммировать данные. Электронные таблицы помогают определить размер месячных выплат по кредиту, позволяет создавать формулы, позволяющие производить математические действия, используя данные из нескольких листов или даже нескольких рабочих книг. С помощью программы Exсel можно работать с диаграммами и это очень важно, так как эта программа позволяет графически представлять данные. Так, например: можно создать диаграмму, нажав комбинацию клавиш; можно улучшать внешний вид диаграммы, используя встроенные средства форматирования; в одной диаграмме можно использовать разные типы данных; существуют диаграммы, в которых часть значений представлена дополнительной вторичной диаграммой, например, в виде двух круговых диаграмм или круговых диаграмм и гистограммы. С помощью диаграмм можно визуально проанализировать, сопоставить данные, определить какие факторы анализа являются доминирующими и как сильно они влияют на исследуемый показатель.
Exсel предлагает целый ряд программ, функций и команд, которые можно использовать для проверки правильности вводимых данных и анализа условий. Функция “Если” используется, чтобы не допускать ошибок в рабочих книгах, Средство “Проверка вводимых значений” осуществляет выбор из списка значений, отражаются сообщения с подсказками, относительно вводимых данных и выделяются ошибочные данные в таблицах. С помощью средства “Подбор параметра” задается желаемая величина и Exсel рассчитывает, что необходимо сделать для достижения этой цели. С помощью Exсel «Поиск решений» можно после проведенных анализов получить необходимое, эффективное, правильное решение. Для этого создаются списки или база данных. «Автозавершение» и «Выбрать из списка», которые облегчают работу пользователя и ускоряют получение необходимого, эффективного, правильного решения, в этом также заключается достоинство программы Excel. В состав Miсrosoft Excel входит набор средств анализа данных (так называемый пакет анализа), предназначенный для решения сложных статистических и инженерных задач. Использование пакета анализа не требует высокого уровня подготовки. Для успешного применения процедур анализа необходимы начальные знания в области статистических и инженерных расчетов, для которых эти инструменты были разработаны. В состав пакета анализа входят: корреляционный анализ (используется для количественной оценки взаимосвязи двух наборов данных, представленных в безразмерном виде; он дает возможность установить, ассоциированы ли наборы данных по величине, то есть, большие значения из одного набора данных связаны с большими значениями другого набора (положительная корреляция), или, наоборот, меньшие значения одного набора связаны с большими значениями другого (отрицательная корреляция), или данные двух диапазонов никак не связаны (корреляция близка к нулю); для вычисления коэффициента корреляции между двумя наборами данных на листе используется статистическая функция КОРРЕЛ); ковариационный анализ ; описательная статистика; экспоненциальное сглаживание ; анализ Фурье ; двухвыборочный F – тест для дисперсий ; гистограмма ; скользящее среднее ; выборка ; генерация случайных чисел ; дисперсионный анализ; регрессия.
Кроме пакета анализа, имеющего колоссальное значение в повышении эффективности и снижении трудоемкости аналитической работы, в том числе и при анализе себестоимости, Microsoft Exсel обладает также десятками статистических функций, позволяющих выполнять статистический анализ диапазонов данных. Прогнозные значения мы находим с помощью функции ПРОГНОЗ.
В данной работе при помощи программы Excel посчитаны все таблицы данных: баланс, отчет о финансовых результатах, сводная таблица показателей финансового состояния предприятия, построен график изменения выручки, величины кредиторской задолженности за период 8 кварталов. Все перечисленные таблицы представлены в приложениях к курсовой работе. На примере составления сводной таблицы показателей финансового состояния предприятия продемонстрированы формулы, по которым производился расчет показателей (приложение ) .
- В пункте 3.4 данной дипломной работы рассматривалась возможность прогнозирования уровня кредиторской задолженности при помощи метода корреляционно-регрессионного анализа. В Excel существует две функции, при помощи которых можно построить уравнение линейной регрессии: ЛИНЕЙН; инструмент «Регрессия». Построение уравнения регрессии с помощью функции ЛИНЕЙН: задать входные данные в виде таблицы; выделить пустой диапазон ячеек размером 5 строк * 2 столбца; с помощью мастера функций, выбрав категорию статистические, воспользуемся функцией ЛИНЕЙН; завершим ввод функции =ЛИНЕЙН(В2:В9;А2:А9;1;1) комбинацией клавиш Ctrl-Shift-Enter, этим действием мы введем формулу массива в выделенный диапазон ячеек
| Объем реализации | Кредиторская задолженность по полученным авансам |
| 62,2 | 0 |
| 365,3 | 365,8 |
| 689,7 | 221,9 |
| 885,1 | 458,2 |
| 645 | 363,4 |
| 1126,1 | 595,1 |
| 1459,4 | 626,7 |
| 2212,5 | 1077 |
| 0,454353 | 40,66345 |
| 0,054875 | 61,67552 |
| 0,919521 | 97,80264 |
| 68,55354 | 6 |
| 655739,1 | 57392,14 |
В результате расчета при помощи функции ЛИНЕЙН получили массив данных, где: - 0,454353 – коэффициент уравнения регрессии; 40,66345– свободный член уравнения регрессии; 0,919521– коэффициент детерминации (если коэффициент близок к единице, то связь между исследуемыми явлениями очень тесная и наоборот); 68,55354– расчетное значение коэффициента Фишера; 6 – количество степеней свободы для поиска критического значения распределения Фишера. По таблицам математической статистики критическое значение распределения Фишера для уровня надежности 5% и степеней свободы k1=1 (количество независимых переменных) и k2(6) будет 5,99, что гораздо меньше расчетного значения, значит, выбранная совокупность соответствует общей совокупности данных. Таким образом, рассчитанное уравнение регрессии y = 0,523066x + 267,427 годится для прогнозирования уровня кредиторской задолженности.
Чтобы для того же диапазона входных данных построить уравнение регрессии при помощи инструмента надо из меню «Сервис» –«Анализ данных» выбрать инструмент анализа «Регрессия». В появившемся диалоговом окне зададим, указав мышью соответствующий диапазон ячеек: выходной интервал Y(кредиторская задолженность); входной интервал Х (объем реализации); параметры вывода выходных данных.
Проанализируем выходные данные регрессионного анализа. В таблице результатов получены: значение коэффициента (0,454352728); свободного члена 40,6634543; значение R2 коэффициента детерминации (0,919520926); значение F распределения Фишера ( 68,55354137); регрессионная (655739,106) и остаточная (57392,1428) суммы квадратов; стандартная ошибка для оценки уровня кредиторской задолженности (97,8026438); стандартные ошибки определения коэффициента (0,068) и свободного члена уравнения регрессии (77,504); расчетные показатели распределения Стьюдента для коэффициента (8,279706599) и свободного члена уравнения регрессии (0,659312761).
| ВЫВОД ИТОГОВ | |||||||||||||||
| Регрессионная статистика | |||||||||||||||
| Множественный R | 0,958916537 | ||||||||||||||
| R-квадрат | 0,919520926 | ||||||||||||||
| Нормированный R-квадрат | 0,906107747 | ||||||||||||||
| Стандартная ошибка | 97,8026438 | ||||||||||||||
| Наблюдения | 8 | ||||||||||||||
| Дисперсионный анализ | |||||||||||||||
| df | SS | MS | F | Значимость F | |||||||||||
| Регрессия | 1 | 655739,106 | 655739,106 | 68,55354137 | 0,000168059 | ||||||||||
| Остаток | 6 | 57392,1428 | 9565,357133 | ||||||||||||
| Итого | 7 | 713131,2488 | |||||||||||||
| Коэффициенты | Стандартная ошибка | t-статистика | P-Значение | ||||||||||||
| Y-пересечение | 40,6634543 | 61,6755153 | 0,659312761 | 0,534166707 | |||||||||||
| Объем реализации | 0,454352728 | 0,054875462 | 8,279706599 | 0,000168059 | |||||||||||
| Нижние 95% | Верхние 95% | Нижние 95,0% | Верхние 95,0% | ||||||||||||
| -110,2512054 | 191,578114 | -110,2512054 | 191,578114 | ||||||||||||
| 0,320077211 | 0,588628245 | 0,320077211 | 0,588628245 | ||||||||||||
О полезности каждого коэффициента в уравнении регрессии можно сделать вывод, сравнив расчетное значение показателей Стьюдента и критическое табличное значение. При уровне надежности 1-q = 0,95 со степенью свободы k = 6 табличное значение коэффициента Стьюдента t =1,94. Расчетное значение показателя Стьюдента для свободного члена меньше табличного, следовательно, этот член не важен для уравнения, для коэффициента он превышает табличное значение, значит, этот член можно оставить в уравнении.