[Solved] APPROPRIATIONS AND EXPENSES TREND ANALYSIS

Assignment Prompt & Instructions

Directions are in attachments. – ## AbreviatedBudgetMaumelle20221.docx – ## AMayorsPersonnelBudgetWorksheet.xlsx – ## HWAppropriationsandExpensesTrendAnalysisGuide.docx – ## AGeneralRevenueWorksheet.xlsx **## AbreviatedBudgetMaumelle20221.docx**   ## image1.emf ## image2.emf **## AMayorsPersonnelBudgetWorksheet.xlsx** ## TSA Budget | | | | | | | | | | — | — | — | — | — | — | — | — | | | Mayor's Personnel Budget (Expenses) | % of Total Expenses | | | Total Expenses in 2020 dollars | | | | 2020 | 2021 | 2022 | 2020 | 2021 | 2022 | | | Salaries | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | | Health Insurance | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | | Retirement Expenses | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | | Social Security Taxes | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | | Total of Four Major Personnel Expenses | | | | $ – 0 | $ – 0 | $ – 0 | Comparison of the Four Largest Expense Items in the Mayor's Personnel Budget, 2020 Salaries Health Insurance Retirement Expenses Social Security Taxes 0 0 0 0 Comparison of the Four Largest Expense Items in the Mayor's Personnel Budget, 2021 Salaries Health Insurance Retirement Expenses Social Security Taxes 0 0 0 0 Comparison of the Four Largest Expense Items in the Mayor's Personnel Budget, 2022 Salaries Health Insurance Retirement Expenses Social Security Taxes 0 0 0 0 **## HWAppropriationsandExpensesTrendAnalysisGuide.docx** PADM 702 **HOMEWORK: Appropriations and Expenses Trend Analysis Guide** _Note- All spreadsheets in this assignment are interactive. That is after you enter the appropriate figures, the percentages and tables and charts will be computed for you. You will not have to do that. Thus, you will only have to focus on creating charts and doing the analysis. You must enter the appropriate budget figures in constant dollars (2022) in each of the cells._ Use the **Appropriations and Expenses Trend Analysis Report Template** found on the assignment page as a template for your report. **NOTE**: The _Trend Analysis Report Template_ will serve as the template for your report. You must use it as is. You are not writing the _Trend Analysis Report_. It is already written for you. You just need to compute the data, present the charts in a professionally looking format, and analyze the data in the Concluding Analysis section. 1) **For all budget computations, use the line items under the following columns, 2020 Budget, 2021 Amended Budget, and 2022 Approved Budget.** 2) Open and save the **A** **General Revenue Worksheet** found on the assignment page. 3) **Prepare the Appropriations Data for General Revenues** · You will open and save the **Abbreviated Budget Maumelle 2022** assignment resource. · **When dealing with dollars over time:** i. Before entering the budgeted amounts in the excel spreadsheets, you need to convert each dollar amount to constant dollars. Due to inflation, the purchasing power of the dollar changes over time, so to compare dollar values from one year to another, they need to be converted from nominal (current) dollar values to constant dollar values. ii. The easiest way to do that is to use an inflation calculator, such as the **U.S. Inflation Calculator** found in the assignment resources. iii. Open the calculator and enter the year of the data (e.g., 2020, 2021 and 2022), enter the year, the dollar amount, and the current year (e.g., 2022). That will convert all dollar amounts to 2022 dollars, controlling for the impact of inflation. Enter the 2022 dollars (constant dollars) into the excel spreadsheets. · Look at the **Total Revenues** row and enter the revenues (in 2022 dollars) into the excel worksheet for each of the three years. (Remember to use the inflation calculator and convert the 2020, 2021 and 2022 budgetary figures into 2022 figures and enter the calculated figures into the spreadsheet.) · Look at the **Public Safety** row and enter the revenues (in 2022 dollars) into the excel worksheet for each of the three years. (Remember to use the inflation calculator and convert the 2020, 2021 and 2022 budgetary figures into 2022 figures and enter the calculated figures into the spreadsheet.) · Look at the **Parks and Recreation** row and enter the revenues (in 2022 dollars) into the excel worksheet for each of the three years. (Remember to use the inflation calculator and convert the 2020, 2021 and 2022 budgetary figures into 2022 figures and enter the calculated figures into the spreadsheet.) · Look at the **Senior Services** row and enter the revenues (in 2022 dollars) into the excel worksheet for each of the three years. (Remember to use the inflation calculator and convert the 2020, 2021 and 2022 budgetary figures into 2022 figures and enter the calculated figures into the spreadsheet.) · Look at the **Planning and Permits** row and enter the revenues (in 2022 dollars) into the excel worksheet for each of the three years. (Remember to use the inflation calculator and convert the 2020, 2021 and 2022 budgetary figures into 2022 figures and enter the calculated figures into the spreadsheet.) 4) **Analyze the Appropriations Data** · Analyze and illustrate the appropriations for the three-year budget period utilizing Excel Tool Bar Graph in the research paper. Bar graph(s) should be labeled appropriately with a number, descriptive title, and vertical and horizontal labels. · Be sure to view **Watch: How to Make a Bar Graph in Excel – Tutorial** in Module 4: Week 4. 5) **Prepare the Mayor's Personnel Budget** · Utilizing the **A Mayor's Personnel Budget Worksheet,** enter figures for 2020, 2021, and 2022 in the appropriate cells. (Remember to use the inflation calculator and convert the 2020, 2021, and 2022 budgetary figures into 2022 figures and enter the calculated figures into the worksheet.) For **A Mayor's Personnel Budget Worksheet**, enter the line-item allocations within that agency’s line item and select following item allocations: · Salaries · Health Insurance · Retirement Expenses · Social Security Taxes The expense spreadsheet in this assignment is interactive. Thus, after you enter the appropriate figures in the appropriate cells, the percentages and pie charts will be computed for you with the correct format layout. Consequently, if completed properly, all you must do is to paste the charts in the appropriate place in the **Appropriations and Expenses Trend Analysis Template**. 6) **Analyze the Mayor's Personnel Budget** · Analyze the expenses distribution for **A Mayor's Personnel Budget** for 2020, 2021, and 2022 utilizing Excel Tool Pie Chart in the research paper. The Pie Chart should be labeled appropriately with a chart number, descriptive title, and legend, with each pie slice labeled. Page 2 of 2 **## AGeneralRevenueWorksheet.xlsx** ## Worksheet | | | | | | | | | | | | | | | | | | | | | | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | | | Maumelle | | | General Revenues Budget | Line Item | 2020 | 2021 | 2022 | | | Chart 1 | | | | | Constant Dollars (2022) | Constant Dollars (2022) | Constant Dollars (2022) | | | | | Total | Total | Total | | | | Chart 1 | | | | | | | | Chart 2 | | Appropriation Category | | 1 | Public Safety | Public Safety | | | | Total Revenues | | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Appropriation Category | | 2 | Parks and Recreation | Parks and Recreation | | | | Total Revenues | | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Appropriation Category | | 3 | Senior Services | Senior Services | | | | Total Revenues | | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Appropriation Category | | 4 | Planning and Permits | Planning and Pemits | | | | Total Revenues | | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | | | Chart 3 | | | | | | | | | | Chart 4 | Percent of Revenues Allocated to Public Safety 2020 2021 2022 0 0 0 Years Percent of Revenues allocated to Public Safety Percent of Revenues Allocated to Parks and Recreation 2020 2021 2022 0 0 0 Years Percent of Revenues allocated to Parks and Recreation Percent of Revenues Allocated to Planning and Pemits 2020 2021 2022 0 0 0 Years Percent of Revenues allocated to Planning and Pemits Percent of Revenues Allocated to Senior Services 2020 2021 2022 0 0 0 Years Percent of Revenues Allocated to Senior Services ## Chart 3 | | | | | | | | | | — | — | — | — | — | — | — | — | | Maumelle | | General Revenues Budget | Line Item | 2020 | 2021 | 2022 | | | | Constant Dollars (2022) | Constant Dollars (2022) | Constant Dollars (2022) | | | | Total | Total | Total | | | Chart 3 | | Public Safety | Public Safety | 2,070,861 | 2,333,553 | 2,407,325 | | | Total Revenues | 16,799,784 | 16,112,085 | 15,762,875 | | Percentage | | 12.3% | 14.5% | 15.3% | | Parks and Recreation | Parks and Recreation | 498,191 | 442,409 | 401,550 | | | Total Revenues | 16,799,784 | 16,112,085 | 15,762,875 | | Percentage | | 3.0% | 2.7% | 2.5% | | Senior Services | Senior Services | 291,495 | 269,602 | 216,000 | | | Total Revenues | 16,799,784 | 16,112,085 | 15,762,875 | | Percentage | | 1.7% | 1.7% | 1.4% | | Planning and Permits | Planning and Pemits | 315,662 | 290,019 | 267,000 | | | Total Revenues | 16,799,784 | 16,112,085 | 15,762,875 | | Percentage | | 1.9% | 1.8% | 1.7% | Percent of Revenues Allocated to Senior Services 2020 2021 2022 1.7351115942919267E-2 1.6732905766075588E-2 1.3703083986899598E-2 Years Percent of Revenues Allocated to Senior Services ## Sheet1 | | | | | | | — | — | — | — | — | | Maumelle | | General Revenues Budget | Line Item | 2020 | 2021 | 2022 | | | | Constant Dollars (2022) | Constant Dollars (2022) | Constant Dollars (2022) | | | | Total | Total | Total | | Public Safety | Public Safety | | | Total Revenues | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Parks and Recreation | Parks and Recreation | | | Total Revenues | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Senior Services | Senior Services | | | Total Revenues | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Planning and Permits | Planning and Pemits | | | Total Revenues | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | **## AGeneralRevenueWorksheet.xlsx** ## Worksheet | | | | | | | | | | | | | | | | | | | | | | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | | | Maumelle | | | General Revenues Budget | Line Item | 2020 | 2021 | 2022 | | | Chart 1 | | | | | Constant Dollars (2022) | Constant Dollars (2022) | Constant Dollars (2022) | | | | | Total | Total | Total | | | | Chart 1 | | | | | | | | Chart 2 | | Appropriation Category | | 1 | Public Safety | Public Safety | | | | Total Revenues | | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Appropriation Category | | 2 | Parks and Recreation | Parks and Recreation | | | | Total Revenues | | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Appropriation Category | | 3 | Senior Services | Senior Services | | | | Total Revenues | | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Appropriation Category | | 4 | Planning and Permits | Planning and Pemits | | | | Total Revenues | | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | | | Chart 3 | | | | | | | | | | Chart 4 | Percent of Revenues Allocated to Public Safety 2020 2021 2022 0 0 0 Years Percent of Revenues allocated to Public Safety Percent of Revenues Allocated to Parks and Recreation 2020 2021 2022 0 0 0 Years Percent of Revenues allocated to Parks and Recreation Percent of Revenues Allocated to Planning and Pemits 2020 2021 2022 0 0 0 Years Percent of Revenues allocated to Planning and Pemits Percent of Revenues Allocated to Senior Services 2020 2021 2022 0 0 0 Years Percent of Revenues Allocated to Senior Services ## Chart 3 | | | | | | | | | | — | — | — | — | — | — | — | — | | Maumelle | | General Revenues Budget | Line Item | 2020 | 2021 | 2022 | | | | Constant Dollars (2022) | Constant Dollars (2022) | Constant Dollars (2022) | | | | Total | Total | Total | | | Chart 3 | | Public Safety | Public Safety | 2,070,861 | 2,333,553 | 2,407,325 | | | Total Revenues | 16,799,784 | 16,112,085 | 15,762,875 | | Percentage | | 12.3% | 14.5% | 15.3% | | Parks and Recreation | Parks and Recreation | 498,191 | 442,409 | 401,550 | | | Total Revenues | 16,799,784 | 16,112,085 | 15,762,875 | | Percentage | | 3.0% | 2.7% | 2.5% | | Senior Services | Senior Services | 291,495 | 269,602 | 216,000 | | | Total Revenues | 16,799,784 | 16,112,085 | 15,762,875 | | Percentage | | 1.7% | 1.7% | 1.4% | | Planning and Permits | Planning and Pemits | 315,662 | 290,019 | 267,000 | | | Total Revenues | 16,799,784 | 16,112,085 | 15,762,875 | | Percentage | | 1.9% | 1.8% | 1.7% | Percent of Revenues Allocated to Senior Services 2020 2021 2022 1.7351115942919267E-2 1.6732905766075588E-2 1.3703083986899598E-2 Years Percent of Revenues Allocated to Senior Services ## Sheet1 | | | | | | | — | — | — | — | — | | Maumelle | | General Revenues Budget | Line Item | 2020 | 2021 | 2022 | | | | Constant Dollars (2022) | Constant Dollars (2022) | Constant Dollars (2022) | | | | Total | Total | Total | | Public Safety | Public Safety | | | Total Revenues | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Parks and Recreation | Parks and Recreation | | | Total Revenues | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Senior Services | Senior Services | | | Total Revenues | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Planning and Permits | Planning and Pemits | | | Total Revenues | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | **## AbreviatedBudgetMaumelle20221.docx**   ## image1.emf ## image2.emf **## AMayorsPersonnelBudgetWorksheet.xlsx** ## TSA Budget | | | | | | | | | | — | — | — | — | — | — | — | — | | | Mayor's Personnel Budget (Expenses) | % of Total Expenses | | | Total Expenses in 2020 dollars | | | | 2020 | 2021 | 2022 | 2020 | 2021 | 2022 | | | Salaries | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | | Health Insurance | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | | Retirement Expenses | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | | Social Security Taxes | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | | Total of Four Major Personnel Expenses | | | | $ – 0 | $ – 0 | $ – 0 | Comparison of the Four Largest Expense Items in the Mayor's Personnel Budget, 2020 Salaries Health Insurance Retirement Expenses Social Security Taxes 0 0 0 0 Comparison of the Four Largest Expense Items in the Mayor's Personnel Budget, 2021 Salaries Health Insurance Retirement Expenses Social Security Taxes 0 0 0 0 Comparison of the Four Largest Expense Items in the Mayor's Personnel Budget, 2022 Salaries Health Insurance Retirement Expenses Social Security Taxes 0 0 0 0 **## HWAppropriationsandExpensesTrendAnalysisGuide.docx** PADM 702 **HOMEWORK: Appropriations and Expenses Trend Analysis Guide** _Note- All spreadsheets in this assignment are interactive. That is after you enter the appropriate figures, the percentages and tables and charts will be computed for you. You will not have to do that. Thus, you will only have to focus on creating charts and doing the analysis. You must enter the appropriate budget figures in constant dollars (2022) in each of the cells._ Use the **Appropriations and Expenses Trend Analysis Report Template** found on the assignment page as a template for your report. **NOTE**: The _Trend Analysis Report Template_ will serve as the template for your report. You must use it as is. You are not writing the _Trend Analysis Report_. It is already written for you. You just need to compute the data, present the charts in a professionally looking format, and analyze the data in the Concluding Analysis section. 1) **For all budget computations, use the line items under the following columns, 2020 Budget, 2021 Amended Budget, and 2022 Approved Budget.** 2) Open and save the **A** **General Revenue Worksheet** found on the assignment page. 3) **Prepare the Appropriations Data for General Revenues** · You will open and save the **Abbreviated Budget Maumelle 2022** assignment resource. · **When dealing with dollars over time:** i. Before entering the budgeted amounts in the excel spreadsheets, you need to convert each dollar amount to constant dollars. Due to inflation, the purchasing power of the dollar changes over time, so to compare dollar values from one year to another, they need to be converted from nominal (current) dollar values to constant dollar values. ii. The easiest way to do that is to use an inflation calculator, such as the **U.S. Inflation Calculator** found in the assignment resources. iii. Open the calculator and enter the year of the data (e.g., 2020, 2021 and 2022), enter the year, the dollar amount, and the current year (e.g., 2022). That will convert all dollar amounts to 2022 dollars, controlling for the impact of inflation. Enter the 2022 dollars (constant dollars) into the excel spreadsheets. · Look at the **Total Revenues** row and enter the revenues (in 2022 dollars) into the excel worksheet for each of the three years. (Remember to use the inflation calculator and convert the 2020, 2021 and 2022 budgetary figures into 2022 figures and enter the calculated figures into the spreadsheet.) · Look at the **Public Safety** row and enter the revenues (in 2022 dollars) into the excel worksheet for each of the three years. (Remember to use the inflation calculator and convert the 2020, 2021 and 2022 budgetary figures into 2022 figures and enter the calculated figures into the spreadsheet.) · Look at the **Parks and Recreation** row and enter the revenues (in 2022 dollars) into the excel worksheet for each of the three years. (Remember to use the inflation calculator and convert the 2020, 2021 and 2022 budgetary figures into 2022 figures and enter the calculated figures into the spreadsheet.) · Look at the **Senior Services** row and enter the revenues (in 2022 dollars) into the excel worksheet for each of the three years. (Remember to use the inflation calculator and convert the 2020, 2021 and 2022 budgetary figures into 2022 figures and enter the calculated figures into the spreadsheet.) · Look at the **Planning and Permits** row and enter the revenues (in 2022 dollars) into the excel worksheet for each of the three years. (Remember to use the inflation calculator and convert the 2020, 2021 and 2022 budgetary figures into 2022 figures and enter the calculated figures into the spreadsheet.) 4) **Analyze the Appropriations Data** · Analyze and illustrate the appropriations for the three-year budget period utilizing Excel Tool Bar Graph in the research paper. Bar graph(s) should be labeled appropriately with a number, descriptive title, and vertical and horizontal labels. · Be sure to view **Watch: How to Make a Bar Graph in Excel – Tutorial** in Module 4: Week 4. 5) **Prepare the Mayor's Personnel Budget** · Utilizing the **A Mayor's Personnel Budget Worksheet,** enter figures for 2020, 2021, and 2022 in the appropriate cells. (Remember to use the inflation calculator and convert the 2020, 2021, and 2022 budgetary figures into 2022 figures and enter the calculated figures into the worksheet.) For **A Mayor's Personnel Budget Worksheet**, enter the line-item allocations within that agency’s line item and select following item allocations: · Salaries · Health Insurance · Retirement Expenses · Social Security Taxes The expense spreadsheet in this assignment is interactive. Thus, after you enter the appropriate figures in the appropriate cells, the percentages and pie charts will be computed for you with the correct format layout. Consequently, if completed properly, all you must do is to paste the charts in the appropriate place in the **Appropriations and Expenses Trend Analysis Template**. 6) **Analyze the Mayor's Personnel Budget** · Analyze the expenses distribution for **A Mayor's Personnel Budget** for 2020, 2021, and 2022 utilizing Excel Tool Pie Chart in the research paper. The Pie Chart should be labeled appropriately with a chart number, descriptive title, and legend, with each pie slice labeled. Page 2 of 2 **## AGeneralRevenueWorksheet.xlsx** ## Worksheet | | | | | | | | | | | | | | | | | | | | | | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | | | Maumelle | | | General Revenues Budget | Line Item | 2020 | 2021 | 2022 | | | Chart 1 | | | | | Constant Dollars (2022) | Constant Dollars (2022) | Constant Dollars (2022) | | | | | Total | Total | Total | | | | Chart 1 | | | | | | | | Chart 2 | | Appropriation Category | | 1 | Public Safety | Public Safety | | | | Total Revenues | | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Appropriation Category | | 2 | Parks and Recreation | Parks and Recreation | | | | Total Revenues | | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Appropriation Category | | 3 | Senior Services | Senior Services | | | | Total Revenues | | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Appropriation Category | | 4 | Planning and Permits | Planning and Pemits | | | | Total Revenues | | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | | | Chart 3 | | | | | | | | | | Chart 4 | Percent of Revenues Allocated to Public Safety 2020 2021 2022 0 0 0 Years Percent of Revenues allocated to Public Safety Percent of Revenues Allocated to Parks and Recreation 2020 2021 2022 0 0 0 Years Percent of Revenues allocated to Parks and Recreation Percent of Revenues Allocated to Planning and Pemits 2020 2021 2022 0 0 0 Years Percent of Revenues allocated to Planning and Pemits Percent of Revenues Allocated to Senior Services 2020 2021 2022 0 0 0 Years Percent of Revenues Allocated to Senior Services ## Chart 3 | | | | | | | | | | — | — | — | — | — | — | — | — | | Maumelle | | General Revenues Budget | Line Item | 2020 | 2021 | 2022 | | | | Constant Dollars (2022) | Constant Dollars (2022) | Constant Dollars (2022) | | | | Total | Total | Total | | | Chart 3 | | Public Safety | Public Safety | 2,070,861 | 2,333,553 | 2,407,325 | | | Total Revenues | 16,799,784 | 16,112,085 | 15,762,875 | | Percentage | | 12.3% | 14.5% | 15.3% | | Parks and Recreation | Parks and Recreation | 498,191 | 442,409 | 401,550 | | | Total Revenues | 16,799,784 | 16,112,085 | 15,762,875 | | Percentage | | 3.0% | 2.7% | 2.5% | | Senior Services | Senior Services | 291,495 | 269,602 | 216,000 | | | Total Revenues | 16,799,784 | 16,112,085 | 15,762,875 | | Percentage | | 1.7% | 1.7% | 1.4% | | Planning and Permits | Planning and Pemits | 315,662 | 290,019 | 267,000 | | | Total Revenues | 16,799,784 | 16,112,085 | 15,762,875 | | Percentage | | 1.9% | 1.8% | 1.7% | Percent of Revenues Allocated to Senior Services 2020 2021 2022 1.7351115942919267E-2 1.6732905766075588E-2 1.3703083986899598E-2 Years Percent of Revenues Allocated to Senior Services ## Sheet1 | | | | | | | — | — | — | — | — | | Maumelle | | General Revenues Budget | Line Item | 2020 | 2021 | 2022 | | | | Constant Dollars (2022) | Constant Dollars (2022) | Constant Dollars (2022) | | | | Total | Total | Total | | Public Safety | Public Safety | | | Total Revenues | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Parks and Recreation | Parks and Recreation | | | Total Revenues | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Senior Services | Senior Services | | | Total Revenues | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Planning and Permits | Planning and Pemits | | | Total Revenues | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | **## AbreviatedBudgetMaumelle20221.docx**   ## image1.emf ## image2.emf **## AMayorsPersonnelBudgetWorksheet.xlsx** ## TSA Budget | | | | | | | | | | — | — | — | — | — | — | — | — | | | Mayor's Personnel Budget (Expenses) | % of Total Expenses | | | Total Expenses in 2020 dollars | | | | 2020 | 2021 | 2022 | 2020 | 2021 | 2022 | | | Salaries | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | | Health Insurance | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | | Retirement Expenses | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | | Social Security Taxes | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | | Total of Four Major Personnel Expenses | | | | $ – 0 | $ – 0 | $ – 0 | Comparison of the Four Largest Expense Items in the Mayor's Personnel Budget, 2020 Salaries Health Insurance Retirement Expenses Social Security Taxes 0 0 0 0 Comparison of the Four Largest Expense Items in the Mayor's Personnel Budget, 2021 Salaries Health Insurance Retirement Expenses Social Security Taxes 0 0 0 0 Comparison of the Four Largest Expense Items in the Mayor's Personnel Budget, 2022 Salaries Health Insurance Retirement Expenses Social Security Taxes 0 0 0 0 **## HWAppropriationsandExpensesTrendAnalysisGuide.docx** PADM 702 **HOMEWORK: Appropriations and Expenses Trend Analysis Guide** _Note- All spreadsheets in this assignment are interactive. That is after you enter the appropriate figures, the percentages and tables and charts will be computed for you. You will not have to do that. Thus, you will only have to focus on creating charts and doing the analysis. You must enter the appropriate budget figures in constant dollars (2022) in each of the cells._ Use the **Appropriations and Expenses Trend Analysis Report Template** found on the assignment page as a template for your report. **NOTE**: The _Trend Analysis Report Template_ will serve as the template for your report. You must use it as is. You are not writing the _Trend Analysis Report_. It is already written for you. You just need to compute the data, present the charts in a professionally looking format, and analyze the data in the Concluding Analysis section. 1) **For all budget computations, use the line items under the following columns, 2020 Budget, 2021 Amended Budget, and 2022 Approved Budget.** 2) Open and save the **A** **General Revenue Worksheet** found on the assignment page. 3) **Prepare the Appropriations Data for General Revenues** · You will open and save the **Abbreviated Budget Maumelle 2022** assignment resource. · **When dealing with dollars over time:** i. Before entering the budgeted amounts in the excel spreadsheets, you need to convert each dollar amount to constant dollars. Due to inflation, the purchasing power of the dollar changes over time, so to compare dollar values from one year to another, they need to be converted from nominal (current) dollar values to constant dollar values. ii. The easiest way to do that is to use an inflation calculator, such as the **U.S. Inflation Calculator** found in the assignment resources. iii. Open the calculator and enter the year of the data (e.g., 2020, 2021 and 2022), enter the year, the dollar amount, and the current year (e.g., 2022). That will convert all dollar amounts to 2022 dollars, controlling for the impact of inflation. Enter the 2022 dollars (constant dollars) into the excel spreadsheets. · Look at the **Total Revenues** row and enter the revenues (in 2022 dollars) into the excel worksheet for each of the three years. (Remember to use the inflation calculator and convert the 2020, 2021 and 2022 budgetary figures into 2022 figures and enter the calculated figures into the spreadsheet.) · Look at the **Public Safety** row and enter the revenues (in 2022 dollars) into the excel worksheet for each of the three years. (Remember to use the inflation calculator and convert the 2020, 2021 and 2022 budgetary figures into 2022 figures and enter the calculated figures into the spreadsheet.) · Look at the **Parks and Recreation** row and enter the revenues (in 2022 dollars) into the excel worksheet for each of the three years. (Remember to use the inflation calculator and convert the 2020, 2021 and 2022 budgetary figures into 2022 figures and enter the calculated figures into the spreadsheet.) · Look at the **Senior Services** row and enter the revenues (in 2022 dollars) into the excel worksheet for each of the three years. (Remember to use the inflation calculator and convert the 2020, 2021 and 2022 budgetary figures into 2022 figures and enter the calculated figures into the spreadsheet.) · Look at the **Planning and Permits** row and enter the revenues (in 2022 dollars) into the excel worksheet for each of the three years. (Remember to use the inflation calculator and convert the 2020, 2021 and 2022 budgetary figures into 2022 figures and enter the calculated figures into the spreadsheet.) 4) **Analyze the Appropriations Data** · Analyze and illustrate the appropriations for the three-year budget period utilizing Excel Tool Bar Graph in the research paper. Bar graph(s) should be labeled appropriately with a number, descriptive title, and vertical and horizontal labels. · Be sure to view **Watch: How to Make a Bar Graph in Excel – Tutorial** in Module 4: Week 4. 5) **Prepare the Mayor's Personnel Budget** · Utilizing the **A Mayor's Personnel Budget Worksheet,** enter figures for 2020, 2021, and 2022 in the appropriate cells. (Remember to use the inflation calculator and convert the 2020, 2021, and 2022 budgetary figures into 2022 figures and enter the calculated figures into the worksheet.) For **A Mayor's Personnel Budget Worksheet**, enter the line-item allocations within that agency’s line item and select following item allocations: · Salaries · Health Insurance · Retirement Expenses · Social Security Taxes The expense spreadsheet in this assignment is interactive. Thus, after you enter the appropriate figures in the appropriate cells, the percentages and pie charts will be computed for you with the correct format layout. Consequently, if completed properly, all you must do is to paste the charts in the appropriate place in the **Appropriations and Expenses Trend Analysis Template**. 6) **Analyze the Mayor's Personnel Budget** · Analyze the expenses distribution for **A Mayor's Personnel Budget** for 2020, 2021, and 2022 utilizing Excel Tool Pie Chart in the research paper. The Pie Chart should be labeled appropriately with a chart number, descriptive title, and legend, with each pie slice labeled. Page 2 of 2 **## AGeneralRevenueWorksheet.xlsx** ## Worksheet | | | | | | | | | | | | | | | | | | | | | | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | — | | | Maumelle | | | General Revenues Budget | Line Item | 2020 | 2021 | 2022 | | | Chart 1 | | | | | Constant Dollars (2022) | Constant Dollars (2022) | Constant Dollars (2022) | | | | | Total | Total | Total | | | | Chart 1 | | | | | | | | Chart 2 | | Appropriation Category | | 1 | Public Safety | Public Safety | | | | Total Revenues | | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Appropriation Category | | 2 | Parks and Recreation | Parks and Recreation | | | | Total Revenues | | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Appropriation Category | | 3 | Senior Services | Senior Services | | | | Total Revenues | | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Appropriation Category | | 4 | Planning and Permits | Planning and Pemits | | | | Total Revenues | | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | | | Chart 3 | | | | | | | | | | Chart 4 | Percent of Revenues Allocated to Public Safety 2020 2021 2022 0 0 0 Years Percent of Revenues allocated to Public Safety Percent of Revenues Allocated to Parks and Recreation 2020 2021 2022 0 0 0 Years Percent of Revenues allocated to Parks and Recreation Percent of Revenues Allocated to Planning and Pemits 2020 2021 2022 0 0 0 Years Percent of Revenues allocated to Planning and Pemits Percent of Revenues Allocated to Senior Services 2020 2021 2022 0 0 0 Years Percent of Revenues Allocated to Senior Services ## Chart 3 | | | | | | | | | | — | — | — | — | — | — | — | — | | Maumelle | | General Revenues Budget | Line Item | 2020 | 2021 | 2022 | | | | Constant Dollars (2022) | Constant Dollars (2022) | Constant Dollars (2022) | | | | Total | Total | Total | | | Chart 3 | | Public Safety | Public Safety | 2,070,861 | 2,333,553 | 2,407,325 | | | Total Revenues | 16,799,784 | 16,112,085 | 15,762,875 | | Percentage | | 12.3% | 14.5% | 15.3% | | Parks and Recreation | Parks and Recreation | 498,191 | 442,409 | 401,550 | | | Total Revenues | 16,799,784 | 16,112,085 | 15,762,875 | | Percentage | | 3.0% | 2.7% | 2.5% | | Senior Services | Senior Services | 291,495 | 269,602 | 216,000 | | | Total Revenues | 16,799,784 | 16,112,085 | 15,762,875 | | Percentage | | 1.7% | 1.7% | 1.4% | | Planning and Permits | Planning and Pemits | 315,662 | 290,019 | 267,000 | | | Total Revenues | 16,799,784 | 16,112,085 | 15,762,875 | | Percentage | | 1.9% | 1.8% | 1.7% | Percent of Revenues Allocated to Senior Services 2020 2021 2022 1.7351115942919267E-2 1.6732905766075588E-2 1.3703083986899598E-2 Years Percent of Revenues Allocated to Senior Services ## Sheet1 | | | | | | | — | — | — | — | — | | Maumelle | | General Revenues Budget | Line Item | 2020 | 2021 | 2022 | | | | Constant Dollars (2022) | Constant Dollars (2022) | Constant Dollars (2022) | | | | Total | Total | Total | | Public Safety | Public Safety | | | Total Revenues | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Parks and Recreation | Parks and Recreation | | | Total Revenues | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Senior Services | Senior Services | | | Total Revenues | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | | Planning and Permits | Planning and Pemits | | | Total Revenues | | Percentage | | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | – 1 – 2 – 3 – 4