Лекция "Система связанных электронных таблиц"

Система связанных электронных таблиц Введение. Цель   курсовой   работы:   изучение   системы   связанных   электронных   таблиц,   включая конструирование и трассировку связей между таблицами, внедрение диаграмм в расчетные листы, овладение навыками редактирования и оформления электронных табличных расчетов. Результатом курсовой работы является создание двух файлов: а)   Файла   в   Excel   c   системой   связанных   электронных   таблиц   и   диаграмм   для   решения поставленной задачи; б) Файла в Word c пояснительной запиской, результатами контрольного расчета, выводами по расчету и описанием использованных электронных механизмов (технологий). Для выполнения курсовой работы понадобились сведения: ­ о   составе   семьи   (может   включать   как   очень   простой   родители   и   ребенок,   а   может включать разные поколения: дедушку, бабушку) о доходах каждого члена семьи (всех зарплатах, пенсиях, стипендиях, пособиях и других специальных   выплатах;   доходах   от   предпринимательской   деятельности,   доходах   от операций с имуществом; гонорарах; дивидендах по акциям, облигациям и т.п.) о стоимости имущества семьи (квартиры, гаража, автомобиля и т.д.) о составе расходов семьи (текущих расходов – по отдельным статьям и укрупненным группам: продукты, автомобиль, развлечения и т.п., расходов на коммунальные платежи и налоги; при существовании кредита – расходов на его погашение.). ­ ­ ­ Для   начала   подготавливаются   общие   данные   для   расчета   бюджета   семьи   в   целом:   формируются   статьи необходимых расходов, оцениваются действительные и возможные размеры доходов и их периодичность, намечаются взаимосвязи между отдельными статьями. Условно этот этап называется описанием экономической модели семьи. На втором этапе создаются требуемые для расчета электронные табличные формы, между ними   устанавливаются   необходимые   связи   и   вносятся   цифры.   Результаты   анализируются.   При необходимости   отдельные   цифры   или   статьи   корректируются.   Данные   обеспечиваются диаграммами. Этот этап выполняется в среде электронных таблиц Excel. На третьем этапе электронные табличные формы с цифрами, диаграммами и пояснениями переносятся из Excel в Word, окончательно оформляются, дополняются выводами и описанием использованных электронных механизмов и подготавливаются к печати. Все   расчеты   производятся   за   второе   полугодие   текущего   года.   Таблицы   снабжаются диаграммами. Все электронные таблицы помещаются на листах одной рабочей тетради. Каждый лист   входящий   в   одну   книгу   должен   содержать   одну   таблицу   и   иметь   соответствующее   имя: “Доходы”, “Расходы” и т.д. Разработка таблицы “Доходы” Всю   работу   целесообразно   начинать   с   разработки   таблицы,   включающей   доходы   семьи, которые в общем случае могут складываться из следующих статей: заработной платы членов семьи; пенсии; пособия; стипендии и другие специальные выплаты; доходов от предпринимательской деятельности; доходов от операций с имуществом; гонораров; дивидендов по акциям и облигациям; некоторых других доходов. ­ ­ ­ ­ ­ ­ ­ ­ ­ Стипендии и пенсии подоходным налогом не облагаются, потому их сразу вносим в таблицы, а вот заработная плата, доходы от предпринимательской деятельности облагаются подоходным налогом. Подоходный   налог   уплачивается   в   соответствии   с   Законом   РФ   “О   подоходном   налоге   с физических лиц”. Он рассчитывается исходя из совокупного дохода, полученного в календарную пору. Размер подоходного налога Размер   облагаемого   совокупного дохода,   полученного   в   календарном году до 20 тыс. р. 20 ­ 40 тыс. р 40­60 тыс. р 60 – 80 тыс. р. 80 – 100 тыс. р. от 100 и выше Сумма налога 12% 2400 р. + 15% с суммы выше 20 тыс.р 5400 р. + 20% с суммы выше 40 тыс.р  9400 р. + 25% с суммы выше 60 тыс.р 14400 р. + 30% с суммы выше 80 тыс.р 20400   р.   +   35%   с   суммы   выше   100 тыс.р Подоходный   налог   по   основному   месту   работы   у   работника   вычисляется   ежемесячно. Однако,   если   человек   имеет   дополнительный   заработок,   получает   гонорары,   имеет   доход   от предпринимательской   деятельности,   то   по   истечении   календарного   года   ему   необходимо представление   декларации   в   налоговую   инспекцию   для   окончательного   расчета   и   уплаты подоходного налога. При разработке таблицы для упрощения я буду считать, что подоходный налог в течении года взимается по ставке 12%, а в конце года производится перерасчет и определяются необходимые доплаты. В Законе РФ “О подоходном налоге с физических лиц” предусмотрен ряд льгот. В частности от налогооблагаемой базы ежемесячно вычитается минимальная заработная плата, а если человек имеет детей, то еще количество минимальных заработных плат, равное числу детей. Сумма   совокупного   ежегодного   дохода   рассчитывается   без   учета   выплат   районного регулирования заработной платы, то есть в моем случае, без районного коэффициента и доплат за стаж работы. С доплат по районному коэффициенту и за стаж работы подоходный налог взимается только в размере 12 %. Несмотря на то, что все расчеты производятся за второе полугодие, учет доходов должен быть произведен за весь год. Ежемесячно сумма, полученная на руки, будет рассчитываться как разность между начисленной суммой и удержаниями в Пенсионный фонд и подоходного налога. Окончательный перерасчет подоходного налога с учетом всех дополнительных заработков производится в декабре в следующем порядке: а) рассчитывается суммарный доход без учета районного коэффициента и выплат за стаж работы; б) из суммарного совокупного дохода вычитается сумма льгот и определяется превышение им границ установленных ставок подоходного налога; в) с полученных сумм определяется подоходный налог с добавлением подоходного налога от выплат районного коэффициента и доплат за стаж работы в размере 12 %; г) из рассчитанной по годовым данным суммы подоходного налога выбирается уплаченный за год подоходный налог, и определяется сумма доплат. Сейчас обратимся к доходам конкретной одной семьи (в данном случае моей) и соберем необходимые данные. В семье три человека (родители и один ребенок). Основные и единственные статьи доходов за текущий год включают заработную плату и пенсию. Пенсионером является один из членов семьи. Так как пенсия не облагается никакими налогами, то данные можно сразу записать в таблицу. В связи с тем, что несколько месяцев первого полугодия давалось единовременное пособие   к   пенсии   и   на   несколько   месяцев   была   введена   доплата   к   пенсионным   суммам   для ветеранов   труда,   пенсия   за   первое   полугодие   на   несколько   процентов   превысила   пенсионные выплаты за второе полугодие. Пособий в текущем году не было в связи с экономическим кризисом в стране. Стипендия тоже отсутствует, так как студент обучается на коммерческой основе. Заработную плату получает также один из родителей. За первое полугодие заработная плата составила двадцать тысяч рублей уже с вычетом подоходного налога. Так например годовой суммарный заработок без учета районного коэффициента и доплат за стаж работы составил 52 000 рублей. Районный коэффициент 30%. Доплата за выслугу лет 30%. Минимальная заработная плата 83,49 рублей. Ребенок – один. Начислено за год: оклад 52 000 рублей; доплата по районному коэффициенту – 7 800 рублей; доплата за стаж работы – 7800 рублей (Итого 67 600 рублей). Суммарный годовой доход без учета выплат районного регулирования зарплат составил 52 000 рублей. Льготы по подоходному налогу за год высчитываются как: 283.4912 = 20003,76 рублей. Отчисления в пенсионный Фонд 1% от начисленного заработка 0,0167,600 = 676 рублей. Совокупный облагаемый налогом годовой доход составит 52,000 – 2 003,76 – 676 = 49320,24 тыс. рублей. Совокупный доход попадает в границы от 40 до 60 тыс. рублей.  Подоходный налог составит 5400 + 0,2  (49320,24 – 40,000) = 8237,04 рублей. Подоходный налог с районного коэффициента и доплат за стаж работы составит 0,12  (7800 + 7800) = 1872 рубля. Вычитаем   все   налоги   и   заработная   плата   за   текущий   год   с   учетом   подоходного   налога, включая надбавки и северные составляет 49050 рублей. Занесем   данные   в   таблицу   под   соответствующим   названием   “Доходы   семьи”.   Для   этого создадим   таблицу   в   Excel.   Выделим   необходимое   число   граф   и   столбцов.   Соответствующе заполним “шапку” таблицы. Так за первое полугодие помесячные доходы не расписываются, то первая   колонка   включает   доходы   за   первые   полгода.   В   следующих   колонках   доходы расписываются по месяцам. Предпоследняя колонка итог за второе полугодие и последняя – сумма дохода за год.  Приступим к заполнению таблицы. Так как пособие, стипендия и другие социальные выплаты отсутствуют, то соответственно будет две статьи доходов: заработная плата и пенсия. Так как заработную плата получает один из родителей,   то   сразу   записываем   значение   заработной   платы   в   соответствующие   колонки,   но   с учетом подоходного налога, северных, надбавок, в общем чистом виде. Второй статьей доходов является пенсия. ее тоже получает один из родителей и она также записывается в чистом виде. В колонке “Итого” суммируется заработная плата за вторую половину года. А в последнюю колонку сумма доходов за весь год. Эти расчеты должны производиться компьютером. По   результатам   таблицы   должна   быть   построена   диаграмма,   отражающая   изменение помесячных доходов семьи. Для этого обратимся к мастеру диаграмм. мастер диаграмм представляет собой программу для представления табличных данный в графическом виде. Для   начала   проверим   правильность   выделения   интервалов   данных,   который   компьютер выдает   в   первом   диалоговом   окне.   Затем   выберем   тип   диаграммы,   в   нашем   случае   выберем гистограмму.   Затем   выберем   формат   для   нашего   типа   диаграммы.   Далее   зададим   ориентацию данных по осям  и зарезервируем  место для  меток и подписей.  Введем  название  осей  и самой диаграммы.   в   нашем   случае   параметры   задаются   из   таблицы   и   диаграмма   соответствующе называется “Диаграмма доходов семьи”. Разработка таблицы “Расчет коммунальных платежей” Расчет коммунальных платежей производится по действующим тарифам на коммунальные платежи и принятым измерителям. В основном расходы по каждому виду услуг определяются как произведение величины измерителя на цену услуги. Цены услуг меняются и могут быть приняты действующими на момент написания работы. В   состав   платежей   входят   расходы   по   квартплате,   снабжению   холодной   водой,   теплом, горячей водой, лифтом. мусоропроводом, электроэнергией и так далее. Для того, чтоб рассчитать расходы по коммунальным платежам обратимся к квитанциям по оплате за коммунальные услуги и жилую площадь за второе полугодие. Так например квартплата включает в себя платежи за услуги пользования: мусоропроводом, лифтом.   холодной   водой.   и   все   это   входит   в   стоимость   квартплаты.   Посмотрим   в   расчетную книжку для внесения платежей за квартиру и коммунальные услуги. Посмотрим расчет платежа за: квартиру отопление воду канализацию очистку радио горячее водоснабжение коллективная антенна газ ­ ­ ­ ­ ­ ­ ­ ­ ­ Сложим все расчеты и получим общую квартплату и коммунальные услуги. Проведем такие расчеты для каждого месяца и внесем в таблицу в соответствующие графы. В основном она составляет 250 рублей в месяц, но это уже с учетом, что в семье находится один льготник. Следовательно, полная квартплата включает 500 рублей. Так же из этой суммы уже вычтена первая пеня. В связи с тем, что в ноябре произошла авария и были произведены временные отключения воды и света, а следовательно и лифта, то оплата за коммунальные услуги будет в меньшем размере. Оплата за телефон: Абонентская   плата   за   пользование   телефоном   принимается   в   почтовых   отделениях   и   в сберегательных банках. Плата за телефон вносится до 14­го числа текущего месяца. Для того, чтобы узнать ежемесячную оплату за телефон обратимся к абонементной книжке по расчетам за телефон. Оплата в месяц составляет 38 рублей. За второе полугодие оплата за телефон не поднималась и была стабильной. Так же внесем соответствующие данные в таблицу. И в конечном итоге распишем помесячную оплату за свет. Обратимся к расчетной книжке по оплате за электроэнергию. Единица   измерения   электроэнергии   определяется   в   кВт.   Стоимость   одного   киловатта составляет 0,29 рублей. Для расчета суммы необходимо найти произведение одного киловатта на количество киловатт. Цена за один киловатт за второе полугодие тоже не изменялась, больше всего киловатт было израсходовано в декабре месяца в связи с многочисленными праздниками. Рассчитаем расход электроэнергии за декабрь месяц. 0,29 (цена за 1 кВт) умножим на 448 кВт (количество кВт) получим 130 рублей. Так рассчитаем сумму оплаты за электроэнергию за каждый месяц 2­го полугодия. Заполним таблицу соответствующими данными. Снабдим диаграммой выплат по месяцам, так как   диаграмма   используется   только   как   иллюстрация,   расположим   ее   на   том   же   листе   внизу таблицы.  Разработка таблицы “Текущие расходы”. Расходы   семьи   напрямую   связаны   с   ее   доходами.   Важными   показателями   является   доля расходов на питание. Чем она ниже, тем выше уровень жизни семьи. Доля расходов на питание обычно не ниже 30% от доходов семьи. По   данным   Всероссийского   центра   уровня   жизни   семьи,   структура   расходов   семей   с различным уровнем доходов характеризуется данными , приведенными в таблице: Структура расходов семей с разным уровнем доходов, %. Месячный доход семьи Продукты питания Непродовольственные товары Услуги Отдых на курортах Накопление Уровень доходов средний 35 40 15 ­ 10 высокий 20 46 9 10 15 хлеб овощи В составе продуктов можно выделить отдельные виды: ­ ­ ­ мясо ­ фрукты ­ молочные продукты и т.п. Рассчитаем все виды продуктов в отдельности затем просуммируем их и сумму занесем в графу – питание. Рассчитаем расходы на питание за первое полугодие за 6 месяцев и за второе полугодие, произведем расчет расходов на питание по месяцам. Полученные данные внесем в таблицу в соответствующие колонки. Затем рассчитаем итог за второе полугодие и сумму расходов на питание за год. квартплата; снабжение холодной водой; снабжение горячей водой; теплом; лифтом; В статьи расходов также запишем расходы на квартплату, сюда входят: ­ ­ ­ ­ ­ ­ мусоропроводом; ­ электроэнергией. Все эти данные мы рассчитали в таблице “Расчет коммунальных платежей”. Перенесем эту таблицу в соответствующую графу таблицы “текущие расходы” и занесем в соответствующие колонки необходимые данные. Расчитаем   итоги   расходов   за   коммунальные   платежи   за   первое   полугодие   и   за   второе полугодие по месяцам. расходы на одежду расходы на обувь расходы на мебель расходы на бытовую технику личные расходы расходы на отдых и развлечения В составе непродовольственных товаров могут быть: ­ ­ ­ ­ ­ ­ В нашу таблицу включим только расходы на одежду и личные расходы. Рассчитаем расходы за первое полугодие и за второе полугодие по месяцам, занесем это в соответствующие графы и рассчитаем сумму расходов на одежду и личные расходы за второе полугодие и за год. В   соответствии   с   заданием   может   быть   предусмотрен   запрет   осуществлять   траты   при превышении   доходов   семьи   в   месяц   (с   использованием   функции   ЕСЛИ   и   выдачей соответствующего сообщения) и может быть введено требования учета размеров по каждому члену семьи в отдельной таблице. Следует помнить, что нельзя тратить больше, чем находится денег в таблице “Доходы” но при необходимости можно снять деньги со счета в банке. После   занесения   всех   сведений   о   текущих   расходах   в   таблицу,   построим   диаграмму, отражающую   изменение   помесячных   расходов   семьи   или   диаграмму,   отражающую   структуру расходов (долю) каждого члена семьи. Месячные и годовые итоги автоматически перенесем в таблицу “Расчет месячных остатков и годового итога”. Разработка таблицы “Расчет месячных остатков и годового итога”. Эта таблица представляет собой итоговую таблицу доходов и расходов семьи. Эта таблица создается автоматически на основе данных из предыдущих таблиц – “Текущие расходы” и “Доходы семьи”. По   результатам   всех   расчетов   определяется   остаток   по   каждому   месяцу,   остаток определяется ежемесячно нарастающим итогом как сумма остатков на конец предыдущего месяца плюс расходы за текущий месяц. Остаток за декабрь равен разнице доходов и расходов за весь период.   Этот   остаток   в   определенном   смысле   представляет   собой   показатель   эффективности хозяйственной деятельности семьи за годовой период. Создадим таблицу “Расчет месячных остатков и годового итога” по заданному образцу, затем скопируем в нее таблицы “Текущих расходов” и “Доходы семьи”. По этим двум таблицам, таблица “Расчет месячных остатков и годового итога” должна быть заполнена автоматически. Превышение доходов над расходами ежемесячно помещается на текущий счет в банке под 20% годовых. формулами. Для этого используется формула расчета суммы вклада на конец месяца: S = О  (1 + 0,2/12) + Т, где S – сумма вклада; О – остаток вклада на начало месяца; Т – вклад в текущем месяце. Кроме   этого   необходимо   распечатать   итоговую   таблицу   не   только   с   данными,   но   и   с Для этого выберем флажок формулы на вкладке Вид команды параметры из меню Сервис. Итак,   у   нас   должны   получиться   две   итоговые   таблицы,   одна   из   них   с   данными,   а   другая   с формулами. Снабдим таблицу диаграммой расходов по месяцам. Для этого выделим табличные данные при помощи указателя и левой кнопки мыши. Запустим мастер диаграмм командой диаграмма из меню Вставка. Укажем место расположения диаграммы путем установки указателя мыши в необходимое место рабочего листа. Определим размер диаграммы растягиванием мышью необходимой прямоугольной области на рабочем листе. Выполним действие в пяти диалоговых окнах мастера диаграмм. Основное назначение диаграмм – делать результаты обозримыми и выделять главное.  Копирование данных их Excel в документы Word. Пояснительная записка курсовой работы формируется в текстовом процессоре Word. При окончательном   оформлении   пояснительной   записки   необходимо   включить   в   нее   таблицы   и диаграммы, полученные в электронных таблицах Excel. Для того, чтоб скопировать информацию, находящуюся на рабочих местах Excel и поместить их в Word, выполним действия в следующем порядке. 1. Копирование данные из Excel: откроем рабочий лист Excel; ­ ­ выделим ячейки, которые хотим копировать; выберем копировать в меню Правка ­ ­ переключимся в Word. 2. Вставка данных в документы Word: укажем место документа, в котором хотим разместить данные; ­ ­ щелкнем   кнопку   Вставить   на   панели   инструментов   Стандартная   используем   панель инструментов Таблицы и границы для форматирования таблицы. Заключение. В работе описывались некоторые важные средства, которые могут помочь принять принципы автоматизации экономических расчетов. В ходе курсового проектирования проведен анализ структуры семейного бюджета и влияние различных   статей   доходов   и   расходов   на   уровень   благосостояния   семьи.   Рассмотрено,   как использовать   Excel   для   учета   доходов   и   расходов,   переносить   данные   из   первичных   таблиц   в итоговую.   Многие   экономические   расчеты   для   больших   производственных   систем   строятся   по таким же принципам. Осваивая   семейную   экономику   и   способы   автоматизации   расчетов  приобретаются   навыки настоящей экономической работы. Создавая курсовую работу осуществляется изучение систем связанных электронных таблиц, включая конструирование и трассировку связей между таблицами, внедрение диаграмм в расчетные листы, овладение навыками редактирования и оформления электронных табличных расчетов. Статьи  1 полуго доходов Зарплата Пенсия Итого дие 20000 4000 24000 Доходы семьи Месяцы VII 6000 550 6550 VIII 4500 600 5100 IX 4250 650 4900 X 5000 650 5650 XI 4300 650 4950 XII 5000 630 5630 Итого 49050 3730 52780 За год 69050 7730 76780 Диаграмма доходов за год по  статьям Статьи  доходов 1 полуго дие Месяцы VII Месяцы VIII Месяцы IX Месяцы X Месяцы XI Месяцы XII Месяцы Итого За год 80000 60000 40000 20000 0 1 2 Вид Ед.изм. Расчет комvунальных платежей месяцы платежей Измер. VII VIII IX X XI XII кВт счетчик 0,29 Ц Свет Телефон Кварт плата Ц С 90 0,29 38 250 С Ц Ц С 98 0,29 110 0,29 38 250 38 250 Ц С 80 0,29 38 250 С Ц С 85 0,29 130 38 38 250 230 В среднем по  месяцам 98,83 38,00 205,00 Диаграмма выплат по месяцам Свет Телефон Кварт плата 300 200 100 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 дие Статьи  1 полуго­ расходов 9000 Питание 3000 кв. плата Одежда 5000 личные раходы 6000 Итого 23000 Расходы семьи Месяцы VII 2000 378 1500 300 4178 VIII 2400 386 1000 500 4286 IX 2000 398 1100 600 4098 X 1900 368 2000 800 5068 XI 2000 353 1800 500 4653 XII Итого 3000 13300 415 2298 8400 1000 500 3200 4915 27198 За год 22300 5298 13400 9200 50198 Диаграмма расходов по месяцам Питание кв. плата Одежда личные раходы 25000 20000 15000 10000 5000 0 1 2 3 4 5 6 7 8 9 Статьи доходов и 1 полугодие расходов Доход Расход 24000 23000 VII 6550 4178 VIII 5100 4286 IX 4900 4098 Месяцы X 5650 5068 XI 4950 4653 XII 5630 4915 Итого 32780 27198 За год 56780 50198 Доход Расход 60000 40000 20000 0 Диаграмма расходов Статьи   доходов и расходов 1 полуго­ дие VII VIII IX Месяцы X XI XII Итого За год Доход Расход  Лист1!В8  Лист3!В9  Лист1!С8  Лист1!D8  Лист1!E8  Лист1!F8  Лист1!G8  Лист1!H8 СУММ(C6:H6) СУММ(B6;I6)  Лист3!C9  Лист3!D9  Лист3!E9  Лист3!F9  Лист8!G9  Лист3!H9 СУММ(C7:H7) СУММ(B7;I7)