On the Budgets worksheet, determine the actual amount spent on each expenditure for 2016.Enter the total amount spent in 2016 in cell C20.
(possibly use nested formulas no match year to 2016 and item class in the data sheet (screenshots 2 and 3) and then sum the amounts and fill in C6:C19 of the budget sheet respectively)
A B H I Department Code 1 Check # Category of Gov 2 555771 Public Works 3 555808 Education 4 555875 General Government 5 2312 General Government 6 555799 Education 7 555803 General Government B 555740 Education 9 555759 Public Works 10 555805 Education 11 555761 General Government 12 555610 Education 13 555809 Education 14 555738 Education 15 555788 Education 16 555767 Education 17 555743 General Government 18 555766 Education 19 555793 Education 20 555753 Education 21 555778 Education 22 555885 Education 23 555891 Education 24 555752 Education 25 555790 Education 26 555755 Public Works 27 555733 Education 28 555772 General Government 29 555741 Education 30 555750 Education 21 CEC7A6 Education Documentation Dala Vendor Name GLOBAL PETROLEUM CORP. MAY INSTITUTE INC SEQUOIA CONSTRUCTION INC CENTERS FOR MEDICARE & MEDICAID SERVICES MANAGED HEALTH RESOURCES INC MARKINGSINC BOSTON HIGASHI SCHOOL INC EAST COAST PETROLEUM STATE MASS.ASSOC.FOR THE BLIND ELIZABETH A FLEMING VALLEY COLLABORATIVE MCLEAN HOSPITAL INC BAY COVE HUMAN SERVICES JUSTICE RESOURCE INSTITUTE INC FUTURES CLINIC INC CAMDELE CONSTRUCTION COMPANY INC. FRANOSCAN CHILDREN'S HOSPITAL LIGHTHOUSE SCHOOL INC COTTING SCHOOL INC HOME FOR LITTLE WANDERERS TRIUMPH CENTER WALKER INC COMMUNICATIVE HEALTH CARE ASSOCIATES, INC. LANDMARK SCHOOL DAVID MACEACHERN ALL AMERICAN SPORTS CORP GOLDEN RIDERS INC CAMBRIDGE PUBLIC HEALTH COMMISSION ATY OF HAVERHILL CUANARIANS. CUAIC INC Budgets + D Amount $ 75,648.03 $ 32,701.33 $ 25,525.00 $ 20,263.00 $ 19,547.00 $ 17,223.93 $ 16,716.30 S $ 14,245.63 $ 12,457.76 $ 11,809.61 $ 9,886.77 $ 8,180.83 $ 8,090.01 S $ 7,504.80 $ 7,348.35 $ 7,251.50 $ 6,894.53 $ 6,380.58 $ 5,890.20 $ 5,832.00 $ 4,970.00 $ 4,762.08 $ 4,537.50 $ 4,505.76 $ 3,489.50 $ 3,176.50 $ 3,150.00 $ 2,700.00 $ 2,665.00 2440.00 E F G Check Date Year Department 1/2/13 2013 DEPARTMENT OF PUBLIC WORKS 1/2/13 2013 SCHOOL ADMINISTRATION 1/2/13 2013 OSPCD ADMINISTRATION 1/2/13 2013 PERSONNEL DEPARTMENT 1/2/13 2013 SCHOOL ADMINISTRATION 1/2/13 2013 TRAFFIC AND PARKING 1/2/13 2013 SCHOOL ADMINISTRATION 1/2/13 2013 DEPARTMENT OF PUBLIC WORKS 1/2/13 2013 SCHOOL ADMINISTRATION 1/2/13 2013 WORKER COMPENSATION 1/2/13 2013 SCHOOL ADMINISTRATION 1/2/18 2013 SCHOOL ADMINISTRATION 1/2/13 2013 SCHOOL ADMINISTRATION 1/2/13 2013 SCHOOL ADMINISTRATION 1/2/13 2013 SCHOOL ADMINISTRATION 1/2/13 2013 OSPCD ADMINISTRATION 1/2/13 2013 SCHOOL ADMINISTRATION 1/2/13 2013 SCHOOL ADMINISTRATION 1/2/13 2013 SCHOOL ADMINISTRATION 1/2/13 2013 SCHOOL ADMINISTRATION 1/2/13 2013 S01OOL ADMINISTRATION 1/2/13 2013 SCHOOL ADMINISTRATION 1/2/13 2013 SCHOOL ADMINISTRATION 1/2/13 2013 SCHOOL ADMINISTRATION 1/2/13 2013 DEPARTMENT OF PUBLIC WORKS 1/2/13 2013 SCHOOL ADMINISTRATION 1/2/13 2013 COUNCIL ON AGING 1/2/13 2013 SCHOOL ADMINISTRATION 1/2/13 2013 SCHOOL ADMINISTRATION 2012 CUOLADMINISTRATION Item Number 2 8730-2013 3 15337-2013 1 22980-2013 1 4179-2013 314696-2013 1 14862-2013 3 2972-2013 2 7266 2013 3 15117-2013 1 7704-2013 3 25422-2013 3 15398-2013 3 2631-2013 3 12556-2013 38301-2013 1 3806-2013 3 8240-2013 3 14331-2013 3 5993-2013 3 11142-2013 3 25096-2013 3 25887-2013 3 5630-2013 3 13162-2013 2 6267-2013 3 1516-2013 1 8801-2013 3 3605-2013 3 4800-2013 22000 2012 A C D E F G H 1 2 City of Somerville, MA Budgeted Expenditures (hypothetical) For Year Ended December 31, 2016 2016 Department Code Actual Variance $ Variance % 1 1 1 1 1 1 Budget 66,048 300,000 62334.155 17,698 170,000 12,500,000 75,000,000 15.000.000 10,000,000 430,000 14,000,000 75,000 650.000 3,300,000 193,842 901 1 1 1 1 1 1 3 4 5 Expenditure (Item Class) 6 Advertising 7 Athletics expense 8 Bonds and debt-related expenditures 9 Books 10 Dues and subscriptions 11 Education-related 12 Miscellaneous line item 13 Professional services expense 14 Property, plant and equipment 15 Rental expense 16 Repairs and maintenance expense 17 Supplies expense 18 Technology expense 19 Utilities expense 20 Total expenditures 21 22 Category of Government Spending 23 Education 24 Total 25 Average 26 General Government 27 Total 28 Average 29 Public Works 30 Total 21. As Documentation Dala 2 2 Department ARTS COUNAL AUDITING DEPARTMENT BOARD OF ALDERMEN BOARD OF ASSESSORS BOARD OF HEALTH CTY CLERK OFFICE CLERK OF COMMITTEES COMMUNICATIONS DEPARTMENT CONSTITUENT SERVICES 311 COUNCIL ON AGING CPA COMMITTEE DEPARTMENT OF PUBLIC WORKS DISABILITY AND COMPLIANCE ELECTION COMMISSIONER EXEC - CAPITAL PROJ MGMT EXECOS ENVIRONMENT FIRE DEPT HEADQUARTERS GRANTS MANAGEMENT INSPECTIONAL SERVICES IT DEPARTMENT CITY HALL LAW DEPARTMENT OFFICE OF MAYOR OSPCD ADMINISTRATION PENSION PERSONNEL DEPARTMENT POLICE DEPARTMENT OLDALASING DERARTMENT Category of Government General Government General Government General Government General Government General Government General Government General Government General Government General Government General Government General Government Public Works General Government General Government General Government General Government General Government General Government General Government General Government General Government General Government General Government General Government General Government General Government COROLLOW ant 1 1 1 190,742,852.00 1 1 1 2013 2014 2015 2016 1 1 1 1 1 1 Budgets + F H 1 Year Department Department Code 2 2013 DEPARTMENT OF PUBLIC WORKS 3 2013 SCHOOL ADMINISTRATION 4 2013 OSPCD ADMINISTRATION 5 2013 PERSONNEL DEPARTMENT 2013 SCHOOL ADMINISTRATION 7 2013 TRAFFIC AND PARKING 8 2013 SCHOOL ADMINISTRATION 9 2013 DEPARTMENT OF PUBLIC WORKS 10 2013 SCHOOL ADMINISTRATION 11 2013 WORKER COMPENSATION 12 2013 SCHOOL ADMINISTRATION 13 2013 SCHOOL ADMINISTRATION 14 2013 SCHOOL ADMINISTRATION 15 2013 SCHOOL ADMINISTRATION 16 2013 SCHOOL ADMINISTRATION 17 2013 OSPCD ADMINISTRATION 18 2013 SCHOOL ADMINISTRATION 19 2013 SCHOOL ADMINISTRATION 20 2013 SCHOOL ADMINISTRATION 21 2013 SCHOOL ADMINISTRATION 22 2013 SCHOOL ADMINISTRATION 23 2013 SCHOOL ADMINISTRATION 24 2013 SCHOOL ADMINISTRATION 25 2013 SCHOOL ADMINISTRATION 26 2013 DEPARTMENT OF PUBLIC WORKS 27 2013 SCHOOL ADMINISTRATION 28 2013 COUNCIL ON AGING 29 2013 SCHOOL ADMINISTRATION 30 2013 SCHOOL ADMINISTRATION 21. 2012 SCUOALARMINI CTDATION Documentation Data Budgets + Item Number 2 8730-2013 3 15337 2013 1 22980-2013 1 4179 2013 3 14596-2013 1 14862-2013 3 2972-2013 2 2 7266-2013 3 15117-2013 1 7704-2013 3 25422-2013 3 15398-2013 3 2631-2013 3 12556-2013 38301-2013 1 3806-2013 3 8240-2013 3 14331-2013 3 5993-2013 3 3 11142-2013 3 25095-2013 3 25887-2013 3 5630-2013 3 13162-2013 2 6267-2013 3 1516-2013 1 8801-2013 3 3 3605-2013 3 4800-2013 21500 2012 Org Description DPW-BLGD&GRNDS ORDINARY MAINT SPED-TUITNPSCH DW SPED QUINCY STREET EXPENSE HEALTH CLAIMS TRUST SPED-MEDICAL-DW-SPED TRAFFIC & PARKING ORD MAINT SPED-TUITNPSCH-DW-SPED DPW-BLGD&GRNDS ORDINARY MAINT SPED-TUITNPSCH-OW-SPED WORKERS COMP ORDINARY MAINT SPED-TUITNCOLAB-DW-SPED SPED-TUITNPSCH-DW-SPED SPED-TUITNPSCH-DW-SPED SPED-TUITNPSCH-DW-SPED SPED-TUITNPSCH-DW-SPED LOWER MYSTIC RR IMPR MET SPED-TUITNPSCH-DW-SPED SPED-TUITNPSCH-DW-SPED SPED-TUITNPSCH-DW-SPED SPED-TUITNPSCH-DW-SPED SPED-PSYCH-DW-SPED SPED-TUITNPSCH-DW-SPED SPED-MEDICAL-DW-SPED SPED-TUITNPSCH-DW-SPED DPW-BLGD&GRNDS ORDINARY MAINT ATHLETICS-DW-UND COUNCIL ON AGING SHS-PSYCH-SHS-REG SPED-TUITION-DW-SPED ATUISTICS ALLINN Account Desc OIL SPED-TUITNPSCH DW-SPED-P&T SITE IMP-PARK RECONSTRUCTION MEDICARE PENALLTIES SPED-MEDICAL-DW-SPED-PROF&TECH REPAIRS HIGHWAYS SPED-TUITNPSCH-DW-SPED-P&T OIL SPED-TUITNPSCH-DW-SPED-P&T LEGAL SERVICES SPED-TUITNCOLAB-DW-SPED-P&T SPED-TUITNPSCH-DW-SPED-P&T SPED-TUITNPSCH-DW-SPED-P&T SPED-TUITNPSCH-DW-SPED-P&T SPED-TUITNPSCH-DW-SPED-P&T SITE IMP - PARK RECONSTRUCTION SPED-TUITNPSCH-DW-SPED-P&T SPED-TUITNPSCH-DW-SPED-P&T SPED-TUITNPSCH-DW-SPED-P&T SPED-TUITNPSCH-DW-SPED-P&T SPED-PSYCH-DW-SPED-PROF&TECH SPED-TUITNPSCH-DW-SPED-P&T SPED-MEDICAL-DW-SPED-PROF&TECH SPED-TUITNPSCH-DW-SPED-P&T INTERIOR BLDG MAINT ATHLETICS-DW-UND-SUPPLIES RECREATION TRANSPORTATN. SHS-PSYCH-SHS-REG-PROF&TECH SPED-TUITION-DW-SPED-PROF&TECH ATUISTICS IN SLIROLICS Item Class Miscellaneous line item Education-related Repairs and maintenance expense Miscellaneous line item Education-related Repairs and maintenance expense Education-related Miscellaneous line item Education-related Miscellaneous line item Education-related Education-related Education-related Education-related Education-related Repairs and maintenance expense Education-related Education-related Education-related Education-related Education-related Education-related Education related Education-related Miscellaneous line item Athletics expense Miscellaneous line item Education-related Education-related Athletic CHDORA
