Analysis of a raw budgetary database (Excel, Pivot Table, CPI, outlay and real change)

Full Answer Section

3. Legislative Branch Outlays:

• Filter the data to find the total outlay for the Legislative Branch in 2020.
• Calculate the real change in 2020 dollars for the Legislative Branch since 2019 and 2010.

4. OMB Priorities:

• Health: Identify the sub-functions within the Department of Health and their respective outlays for 2020.
• Find the sub-function with the largest and smallest outlays and calculate the real change in 2020 dollars since 2019 and 2010.
• Higher Education: Identify the agencies involved in the Higher Education sub-function and their outlays for 2020.
• Find the agency with the largest and smallest outlays and calculate the real change in 2020 dollars since 2019 and 2010.
• Defense: Identify the agencies involved in the Defense function and their outlays for 2020.
• Find the agency with the largest and smallest outlays and calculate the real change in 2020 dollars since 2019 and 2010.

Remember to present your findings in a clear and organized way, including data tables and charts if necessary. Discuss your observations and potential explanations for the trends you identified.

I hope this guidance helps you analyze the data and answer the questions effectively!

Sample Solution

Unfortunately, I cannot access and process raw data like the FY24 OMB Table of Outlays sheet you mentioned. Analyzing financial data requires working with the actual numbers and calculations, which I am not equipped to do. However, I can guide you on how to approach these questions using the data provided:

1. Total Outlays and Real Changes:

• Use a pivot table in your spreadsheet software to filter and sum the outlays for mandatory spending, discretionary spending, and net interest for 2020.
• Use the Historical CPI information to calculate the real change in 2020 dollars for each category since 2019 and 2010. Remember to factor in inflation when comparing across years.

2. Departmental Outlays and Trends:

• Filter the data to analyze the outlays for departments only (excluding other categories) for 2010, 2018, 2019, and 2020.
• Identify the department with the largest and smallest outlays in 2020.
• Calculate the real change in 2020 dollars for the outlays of these two departments since 2019 and 2010.
• Analyze the data to identify any funding trends for these departments, considering factors like economic conditions, policy changes, etc.