Question

1 Approved Answer

Posted on Jul 24, 2024

Hello, i have added a sample of the data that im utilizing(there are over 6000 rows so i cant post all of them) please show

Hello, i have added a sample of the data that im utilizing(there are over 6000 rows so i cant post all of them) please show the excel formulas that are needed to answer the questions. below.

image text in transcribed

1 Item # Category of Government Vendor Name 2 11912 General Government US BANK 3 11945 General Government US BANK 4 2750 General Government COMMERCE BANK 5 2756 General Government COMMERCE BANK 6 7200 Public Works MASS WATER RESOURCES AUTHORITY 7 7205 Public Works MASS WATER RESOURCES AUTHORITY 8 2909 General Government COMMONWEALTH OF MASSACHUSETTS 9 2915 General Government COMMONWEALTH OF MASSACHUSETTS 10 2921 General Government COMMONWEALTH OF MASSACHUSETTS 11 2927 General Government COMMONWEALTH OF MASSACHUSETTS 12 2933 General Government COMMONWEALTH OF MASSACHUSETTS 13 2939 General Government COMMONWEALTH OF MASSACHUSETTS 14 2945 General Government COMMONWEALTH OF MASSACHUSETTS 15 2952 General Government COMMONWEALTH OF MASSACHUSETTS 16 2958 General Government COMMONWEALTH OF MASSACHUSETTS 17 2964 General Government COMMONWEALTH OF MASSACHUSETTS 18 2970 General Government COMMONWEALTH OF MASSACHUSETTS 19 2976 General Government COMMONWEALTH OF MASSACHUSETTS 20 2982 General Government COMMONWEALTH OF MASSACHUSETTS 21 2988 General Government COMMONWEALTH OF MASSACHUSETTS 22 2994 General Government COMMONWEALTH OF MASSACHUSETTS 23 3000 General Government COMMONWEALTH OF MASSACHUSETTS 24 3006 General Government COMMONWEALTH OF MASSACHUSETTS 25 3012 General Government COMMONWEALTH OF MASSACHUSETTS 26 3184 General Government CREATIVE SIGNS INC 27 3186 General Government CREATIVE SIGNS INC 28 2600 General Government COMMERCE BANK 29 2612 General Government COMMERCE BANK 30 11913 General Government US BANK 31 2072 General Government CENTERS FOR MEDICARE & MEDICAID SERVICES 32 1837 General Government CAMELOT SPECIAL EVENTS AND TENTS INC 33 2102 Education CENTRAL PAPER PRODUCTS CO 34 12166 General Government WB MASON CO INC 35 12184 General Government WB MASON CO INC 36 12273 General Government WB MASON CO INC 37 12858 General Government WB MASON CO INC 38 2467 General Government COMMERCE BANK 39 2509 General Government COMMERCE BANK 40 2598 General Government COMMERCE BANK 41 2611 General Government COMMERCE BANK 42 2613 General Government COMMERCE BANK 43 7197 General Government MASS WATER RESOURCES AUTHORITY 44 7199 General Government MASS WATER RESOURCES AUTHORITY Amount 36,880 25,607.50 175 2,110.80 765,829 1,591,803.50 154,863.96 148,548.42 113,593.90 67,324.63 3,749.37 2,206.41 613,445.66 2,145.83 588,284.71 2,058.93 388,725.92 1,360.23 228,901.77 801 12,206.45 42.72 7,127.65 25.16 1,095 45 1,095 45 37,521.25 18,733.90 1,225.50 11,082.47 55.36 395.35 403.24 899.64 450.71 11,082.47 899.64 1,225.50 403.24 87,500 160,051.10 E G Invoice Date Check Date Department 6/13/2020 7/14/2020 TREASURY DEPARTMENT 7/2/2020 7/14/2020 TREASURY DEPARTMENT 6/25/2020 7/14/2020 OSPCD ADMINISTRATION 6/16/2020 7/14/2020 OSPCD ADMINISTRATION 6/5/2020 7/20/2020 DEPARTMENT OF PUBLIC WORKS 6/10/2020 7/20/2020 DEPARTMENT OF PUBLIC WORKS 6/5/2020 7/20/2020 PERSONNEL DEPARTMENT 6/19/2020 7/20/2020 PERSONNEL DEPARTMENT 6/19/2020 7/20/2020 PERSONNEL DEPARTMENT 7/4/2020 7/20/2020 PERSONNEL DEPARTMENT 7/8/2020 7/20/2020 PERSONNEL DEPARTMENT 6/19/2020 7/20/2020 PERSONNEL DEPARTMENT 6/12/2020 7/20/2020 PERSONNEL DEPARTMENT 6/26/2020 7/20/2020 PERSONNEL DEPARTMENT 6/5/2020 7/20/2020 PERSONNEL DEPARTMENT 6/21/2020 7/20/2020 PERSONNEL DEPARTMENT 7/9/2020 7/20/2020 PERSONNEL DEPARTMENT 6/14/2020 7/20/2020 PERSONNEL DEPARTMENT 6/21/2020 7/20/2020 PERSONNEL DEPARTMENT 6/27/2020 7/20/2020 PERSONNEL DEPARTMENT 7/7/2020 7/20/2020 PERSONNEL DEPARTMENT 6/15/2020 7/20/2020 PERSONNEL DEPARTMENT 6/19/2020 7/20/2020 PERSONNEL DEPARTMENT 6/28/2020 7/20/2020 PERSONNEL DEPARTMENT 6/21/2020 7/25/2020 COMMUNICATIONS DEPARTMENT 7/10/2020 7/25/2020 COMMUNICATIONS DEPARTMENT 6/28/2020 7/25/2020 AUDITING DEPARTMENT 5/27/2020 7/25/2020 AUDITING DEPARTMENT 7/18/2020 7/29/2020 TREASURY DEPARTMENT 6/5/2020 8/3/2020 PERSONNEL DEPARTMENT 6/27/2020 8/3/2020 ARTS COUNCIL 7/4/2020 8/3/2020 SCHOOL ADMINISTRATION 6/14/2020 8/3/2020 PURCHASING DEPARTMENT 7/20/2020 8/3/2020 TREASURY DEPARTMENT 7/13/2020 8/3/2020 OSPCD ADMINISTRATION 6/30/2020 8/3/2020 RECREATION AND YOUTH 7/24/2020 8/3/2020 AUDITING DEPARTMENT 6/18/2020 8/3/2020 AUDITING DEPARTMENT 7/1/2020 8/3/2020 AUDITING DEPARTMENT 6/25/2020 8/3/2020 AUDITING DEPARTMENT 6/23/2020 8/3/2020 AUDITING DEPARTMENT 7/9/2020 8/10/2020 TREASURY DEPARTMENT 7/15/2020 8/10/2020 TREASURY DEPARTMENT H Check # Organization Description 3912 DEBT SERVICE 3912 WATER DEBT SERVICE / OFU 3913 2013 LEAD HAZARD REDUCTION 3913 2013 LEAD HAZARD REDUCTION 3914 WATER SPECIAL ITEMS 3914 SEWER SPECIAL ITEMS 3915 GENERAL FUND - CITY 3915 GENERAL FUND - CITY 3915 GENERAL FUND - CITY 3915 GENERAL FUND - CITY 3915 GENERAL FUND - CITY 3915 GENERAL FUND - CITY 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3916 CABLE/TELCO GRANT ACCOUNT 3916 COMCAST SOMERMOVIE FEST 3917 CABLE/TELCO GRANT ACCOUNT 3917 COMCAST SOMERMOVIE FEST 3918 DEBT SERVICE 3919 HEALTH & LIFE INS ORDINARY MAI 3920 ARTS COUNCIL 3921 SCHOOL LUNCH EXPENSES 3922 PURCHASING ORDINARY MAINT 3922 TREAS/COLL ORDINARY MAINT 3922 15 CDBG ADMIN PACK 3922 PARKS AND RECREATION 3923 GENERAL FUND - CITY 3923 SCHOOL LUNCH 3923 PARKS AND RECREATION 3923 ARTS COUNCIL 3923 CDBG - GRANT FUND 3924 WATER DEBT SERVICE / OFU 3924 SEWER DEBT SERVICE / OFU Account Description INTEREST ON LTD INTEREST ON LTD STAFF DEVELOPMENT OUT OF STATE TRAVEL MWRA ASSESSMENT MWRA ASSESSMENT GIC ACTIVE-CITY W/H GIC ACTIVE-SCHOOL W/H GIC RETIREE-CITY W/H GIC RETIREE-SCHOOL W/H GIC SURV SPOUSE-CITY W/H GIC SURV SPOUSE-SCHOOL W/H GIC ACTIVE-CITY GIC ADMIN ACTIVE-CITY GIC ACTIVE-SCHOOL GIC ADMIN ACTIVE-SCHOOL GIC RETIREE-CITY GIC ADMIN RETIREE-CITY GIC RETIREE-SCHOOL GIC ADMIN RETIREE-SCHOOL GIC SURV SPOUSE-CITY GIC ADMIN SURV SPOUSE-CITY GIC SURV SPOUSE-SCHOOL GIC ADMIN SURV SPOUSE-SCHOOL PRINTING & STATIONERY PRINTING & STATIONERY ACTIVE CARD INTEGRATION PAYABL ACTIVE CARD INTEGRATION PAYABL INTEREST ON LTD MEDICARE PENALLTIES RENTAL - TABLES & CHAIRS EDUCATIONAL SUPPLIES OFFICE SUPPLIES OFFICE SUPPLIES OFFICE SUPPLIES OFFICE SUPPLIES ACTIVE CARD INTEGRATION PAYABL ACTIVE CARD INTEGRATION PAYABL ACTIVE CARD INTEGRATION PAYABL ACTIVE CARD INTEGRATION PAYABL ACTIVE CARD INTEGRATION PAYABL PRINCIPAL ON LNG TRM DEBT PRINCIPAL ON LNG TRM DEBT What is the total dollar amount paid out for each of the time periods (for all departments)? Payments made in the month of purchase: $ Payments made one month after purchase: $ Payments made two months after purchase: $ Total Payments made within all 3 time periods: $ What is the total dollar amount paid out in each of the time periods for the OSPCD ADMINISTRATION department? Payments made in the month of purchase: $||| Payments made one month after purchase: $ Payments made two months after purchase: $ Total Payments made within all 3 time periods: $ What is the percent of payments in each of the time periods for the EXEC - CAPITAL PROJ MGMT department? What percentage of payments did the EXEC - CAPITAL PROJ MGMT department make in the month of purchase % What percentage of payments did the EXEC - CAPITAL PROJ MGMT department make in the month after purchase % What percentage of payments did the EXEC - CAPITAL PROJ MGMT department make two months after purchase % Jennifer originally budeted for 30% of payments made in the month of purchase, 60% of payments made in the month after purchase, and 10% of payments made two months after purchase across all departments Which department comes closest to this expectation in the month of purchase, the month after purchase, and 2 months after purchase? Which department is closest to the 30% payment in the month of purchase? Which department is closest to the 60% in the month after purchase? Which department is closest to the 10% in the month after purchase? The easiest way to do this is add up the difference between the actual purchasing percentage and the budgeted percentage in each time Which of the departments mathematically is closest to the 30% in the month of purchase, 60% in the month after purchase, and 10% two months after purchase? period and find the department that has the lowest difference across all time periods. Does any department pay for their purchases with the budgeted expectation of 30% in the month of purchase, 60% the month after, and 10% two months after? Type in YES or NO for your answer (in caps). 1 Item # Category of Government Vendor Name 2 11912 General Government US BANK 3 11945 General Government US BANK 4 2750 General Government COMMERCE BANK 5 2756 General Government COMMERCE BANK 6 7200 Public Works MASS WATER RESOURCES AUTHORITY 7 7205 Public Works MASS WATER RESOURCES AUTHORITY 8 2909 General Government COMMONWEALTH OF MASSACHUSETTS 9 2915 General Government COMMONWEALTH OF MASSACHUSETTS 10 2921 General Government COMMONWEALTH OF MASSACHUSETTS 11 2927 General Government COMMONWEALTH OF MASSACHUSETTS 12 2933 General Government COMMONWEALTH OF MASSACHUSETTS 13 2939 General Government COMMONWEALTH OF MASSACHUSETTS 14 2945 General Government COMMONWEALTH OF MASSACHUSETTS 15 2952 General Government COMMONWEALTH OF MASSACHUSETTS 16 2958 General Government COMMONWEALTH OF MASSACHUSETTS 17 2964 General Government COMMONWEALTH OF MASSACHUSETTS 18 2970 General Government COMMONWEALTH OF MASSACHUSETTS 19 2976 General Government COMMONWEALTH OF MASSACHUSETTS 20 2982 General Government COMMONWEALTH OF MASSACHUSETTS 21 2988 General Government COMMONWEALTH OF MASSACHUSETTS 22 2994 General Government COMMONWEALTH OF MASSACHUSETTS 23 3000 General Government COMMONWEALTH OF MASSACHUSETTS 24 3006 General Government COMMONWEALTH OF MASSACHUSETTS 25 3012 General Government COMMONWEALTH OF MASSACHUSETTS 26 3184 General Government CREATIVE SIGNS INC 27 3186 General Government CREATIVE SIGNS INC 28 2600 General Government COMMERCE BANK 29 2612 General Government COMMERCE BANK 30 11913 General Government US BANK 31 2072 General Government CENTERS FOR MEDICARE & MEDICAID SERVICES 32 1837 General Government CAMELOT SPECIAL EVENTS AND TENTS INC 33 2102 Education CENTRAL PAPER PRODUCTS CO 34 12166 General Government WB MASON CO INC 35 12184 General Government WB MASON CO INC 36 12273 General Government WB MASON CO INC 37 12858 General Government WB MASON CO INC 38 2467 General Government COMMERCE BANK 39 2509 General Government COMMERCE BANK 40 2598 General Government COMMERCE BANK 41 2611 General Government COMMERCE BANK 42 2613 General Government COMMERCE BANK 43 7197 General Government MASS WATER RESOURCES AUTHORITY 44 7199 General Government MASS WATER RESOURCES AUTHORITY Amount 36,880 25,607.50 175 2,110.80 765,829 1,591,803.50 154,863.96 148,548.42 113,593.90 67,324.63 3,749.37 2,206.41 613,445.66 2,145.83 588,284.71 2,058.93 388,725.92 1,360.23 228,901.77 801 12,206.45 42.72 7,127.65 25.16 1,095 45 1,095 45 37,521.25 18,733.90 1,225.50 11,082.47 55.36 395.35 403.24 899.64 450.71 11,082.47 899.64 1,225.50 403.24 87,500 160,051.10 E G Invoice Date Check Date Department 6/13/2020 7/14/2020 TREASURY DEPARTMENT 7/2/2020 7/14/2020 TREASURY DEPARTMENT 6/25/2020 7/14/2020 OSPCD ADMINISTRATION 6/16/2020 7/14/2020 OSPCD ADMINISTRATION 6/5/2020 7/20/2020 DEPARTMENT OF PUBLIC WORKS 6/10/2020 7/20/2020 DEPARTMENT OF PUBLIC WORKS 6/5/2020 7/20/2020 PERSONNEL DEPARTMENT 6/19/2020 7/20/2020 PERSONNEL DEPARTMENT 6/19/2020 7/20/2020 PERSONNEL DEPARTMENT 7/4/2020 7/20/2020 PERSONNEL DEPARTMENT 7/8/2020 7/20/2020 PERSONNEL DEPARTMENT 6/19/2020 7/20/2020 PERSONNEL DEPARTMENT 6/12/2020 7/20/2020 PERSONNEL DEPARTMENT 6/26/2020 7/20/2020 PERSONNEL DEPARTMENT 6/5/2020 7/20/2020 PERSONNEL DEPARTMENT 6/21/2020 7/20/2020 PERSONNEL DEPARTMENT 7/9/2020 7/20/2020 PERSONNEL DEPARTMENT 6/14/2020 7/20/2020 PERSONNEL DEPARTMENT 6/21/2020 7/20/2020 PERSONNEL DEPARTMENT 6/27/2020 7/20/2020 PERSONNEL DEPARTMENT 7/7/2020 7/20/2020 PERSONNEL DEPARTMENT 6/15/2020 7/20/2020 PERSONNEL DEPARTMENT 6/19/2020 7/20/2020 PERSONNEL DEPARTMENT 6/28/2020 7/20/2020 PERSONNEL DEPARTMENT 6/21/2020 7/25/2020 COMMUNICATIONS DEPARTMENT 7/10/2020 7/25/2020 COMMUNICATIONS DEPARTMENT 6/28/2020 7/25/2020 AUDITING DEPARTMENT 5/27/2020 7/25/2020 AUDITING DEPARTMENT 7/18/2020 7/29/2020 TREASURY DEPARTMENT 6/5/2020 8/3/2020 PERSONNEL DEPARTMENT 6/27/2020 8/3/2020 ARTS COUNCIL 7/4/2020 8/3/2020 SCHOOL ADMINISTRATION 6/14/2020 8/3/2020 PURCHASING DEPARTMENT 7/20/2020 8/3/2020 TREASURY DEPARTMENT 7/13/2020 8/3/2020 OSPCD ADMINISTRATION 6/30/2020 8/3/2020 RECREATION AND YOUTH 7/24/2020 8/3/2020 AUDITING DEPARTMENT 6/18/2020 8/3/2020 AUDITING DEPARTMENT 7/1/2020 8/3/2020 AUDITING DEPARTMENT 6/25/2020 8/3/2020 AUDITING DEPARTMENT 6/23/2020 8/3/2020 AUDITING DEPARTMENT 7/9/2020 8/10/2020 TREASURY DEPARTMENT 7/15/2020 8/10/2020 TREASURY DEPARTMENT H Check # Organization Description 3912 DEBT SERVICE 3912 WATER DEBT SERVICE / OFU 3913 2013 LEAD HAZARD REDUCTION 3913 2013 LEAD HAZARD REDUCTION 3914 WATER SPECIAL ITEMS 3914 SEWER SPECIAL ITEMS 3915 GENERAL FUND - CITY 3915 GENERAL FUND - CITY 3915 GENERAL FUND - CITY 3915 GENERAL FUND - CITY 3915 GENERAL FUND - CITY 3915 GENERAL FUND - CITY 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3915 HEALTH & LIFE INS PERSONAL SVC 3916 CABLE/TELCO GRANT ACCOUNT 3916 COMCAST SOMERMOVIE FEST 3917 CABLE/TELCO GRANT ACCOUNT 3917 COMCAST SOMERMOVIE FEST 3918 DEBT SERVICE 3919 HEALTH & LIFE INS ORDINARY MAI 3920 ARTS COUNCIL 3921 SCHOOL LUNCH EXPENSES 3922 PURCHASING ORDINARY MAINT 3922 TREAS/COLL ORDINARY MAINT 3922 15 CDBG ADMIN PACK 3922 PARKS AND RECREATION 3923 GENERAL FUND - CITY 3923 SCHOOL LUNCH 3923 PARKS AND RECREATION 3923 ARTS COUNCIL 3923 CDBG - GRANT FUND 3924 WATER DEBT SERVICE / OFU 3924 SEWER DEBT SERVICE / OFU Account Description INTEREST ON LTD INTEREST ON LTD STAFF DEVELOPMENT OUT OF STATE TRAVEL MWRA ASSESSMENT MWRA ASSESSMENT GIC ACTIVE-CITY W/H GIC ACTIVE-SCHOOL W/H GIC RETIREE-CITY W/H GIC RETIREE-SCHOOL W/H GIC SURV SPOUSE-CITY W/H GIC SURV SPOUSE-SCHOOL W/H GIC ACTIVE-CITY GIC ADMIN ACTIVE-CITY GIC ACTIVE-SCHOOL GIC ADMIN ACTIVE-SCHOOL GIC RETIREE-CITY GIC ADMIN RETIREE-CITY GIC RETIREE-SCHOOL GIC ADMIN RETIREE-SCHOOL GIC SURV SPOUSE-CITY GIC ADMIN SURV SPOUSE-CITY GIC SURV SPOUSE-SCHOOL GIC ADMIN SURV SPOUSE-SCHOOL PRINTING & STATIONERY PRINTING & STATIONERY ACTIVE CARD INTEGRATION PAYABL ACTIVE CARD INTEGRATION PAYABL INTEREST ON LTD MEDICARE PENALLTIES RENTAL - TABLES & CHAIRS EDUCATIONAL SUPPLIES OFFICE SUPPLIES OFFICE SUPPLIES OFFICE SUPPLIES OFFICE SUPPLIES ACTIVE CARD INTEGRATION PAYABL ACTIVE CARD INTEGRATION PAYABL ACTIVE CARD INTEGRATION PAYABL ACTIVE CARD INTEGRATION PAYABL ACTIVE CARD INTEGRATION PAYABL PRINCIPAL ON LNG TRM DEBT PRINCIPAL ON LNG TRM DEBT What is the total dollar amount paid out for each of the time periods (for all departments)? Payments made in the month of purchase: $ Payments made one month after purchase: $ Payments made two months after purchase: $ Total Payments made within all 3 time periods: $ What is the total dollar amount paid out in each of the time periods for the OSPCD ADMINISTRATION department? Payments made in the month of purchase: $||| Payments made one month after purchase: $ Payments made two months after purchase: $ Total Payments made within all 3 time periods: $ What is the percent of payments in each of the time periods for the EXEC - CAPITAL PROJ MGMT department? What percentage of payments did the EXEC - CAPITAL PROJ MGMT department make in the month of purchase % What percentage of payments did the EXEC - CAPITAL PROJ MGMT department make in the month after purchase % What percentage of payments did the EXEC - CAPITAL PROJ MGMT department make two months after purchase % Jennifer originally budeted for 30% of payments made in the month of purchase, 60% of payments made in the month after purchase, and 10% of payments made two months after purchase across all departments Which department comes closest to this expectation in the month of purchase, the month after purchase, and 2 months after purchase? Which department is closest to the 30% payment in the month of purchase? Which department is closest to the 60% in the month after purchase? Which department is closest to the 10% in the month after purchase? The easiest way to do this is add up the difference between the actual purchasing percentage and the budgeted percentage in each time Which of the departments mathematically is closest to the 30% in the month of purchase, 60% in the month after purchase, and 10% two months after purchase? period and find the department that has the lowest difference across all time periods. Does any department pay for their purchases with the budgeted expectation of 30% in the month of purchase, 60% the month after, and 10% two months after? Type in YES or NO for your answer (in caps)