Analysis Report

Assignment 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, your will only have to focus creating charts and doing the analysis.

The spreadsheets sometimes contain budgetary figures for illustrative purposes.  You must enter the appropriate budget figures in constant dollars (2020) in each of the cells.

• Open and save the Research Paper: Appropriations and Expenses Trend Analysis Worksheet found on the assignment page.

• Prepare the Appropriations Data for U.S. Department of Homeland Security
  • You will open and save the Assignment Resource: Abbreviated Budget Department of Homeland Security
  • When dealing with dollars over time:
    1. 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.
    2. The easiest way to do that is to use an inflation calculator, such as the U.S. Inflation Calculator.

• Open the calculator and enter the year of the data (e.g., 2015, 2016, 2017), enter the year, the dollar amount, and the current year (e.g., 2020). That will convert all dollar amounts to 2020 dollars, controlling for the impact of inflation.  Enter the 2020 dollars (constant dollars) into the excel spreadsheets.

• Look at the Total Budget Authority and enter the Total Budget (in 2020 dollars) into the excel spreadsheet for each of the three years.*
• Look at the S. Immigration and Customs Enforcement (CAS-ICE) and enter the Total Appropriations (in 2020 dollars) into the excel spreadsheet for each of the three years. *
• Look at the Transportation Security Administration (CAS-TSA) and enter the Total Appropriations (in 2020 dollars) into the excel spreadsheet for each of the three years.*
• Look at the S. Coast Guard and enter the Total Appropriations (in 2020 dollars) into the excel spreadsheet for each of the three years.*

• Analyze the Appropriations Data for Department of Homeland Security
  • 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, title, and legend.
  • Be sure to view the Watch: How to Make a Bar Chart in Excel.

• Prepare the Expense Data for The U.S. Department of Homeland Security
  • Utilizing the Expenses Trend Analysis Worksheet found on the assignment page, enter data for the five (5) line-item expenses in the example below for Transportation Security Administration (CAS-TSA) for 2015, 2016, and 2017.*
• Analyze the Expense Data
  • Analyze, the expenses distribution for the Transportation Security Administration (CAS-TSA). for 2015, 2016, and 2017 utilizing Excel Tool Pie Chart in the research paper. The Pie Chart should be labeled appropriately with a title and legend.*
  • Example: for the U.S. Department of Homeland Security, analyze the three-year budgetary allocations for the Transportation Security Administration (CAS-TSA). So, look at the line-item allocations within that agency’s line item and select four-line items allocation as follows:

• Be sure to view the Watch: How to Make a Pie Chart in Excel.

• Use the Research Paper: Appropriations and Expenses Trend Analysis Report Template found on the assignment page as a template for your report.

*Note- Remember to use the inflation calculator and convert the 2015, 2016, and 2017 budgetary figures into 2020 figures and enter