APPROPRIATIONS AND EXPENSES TREND ANALYSIS

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.

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.

· 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