U.S. Budget Excel

Budget

Use a cell reference or a single formula where appropriate. Do not copy and paste values or type values.

The projected Fiscal Year (FY) projected 2019 budget is given in the spreadsheet, broken down by Outlays (Mandatory and Discretionary spending) and Receipts (income). https://www.whitehouse.gov/omb/historical-tables/

a.) Compute the subtotals and totals for both Outlays and Receipts.

b.) Compute the percentage for each subcategory as a decimal to 3 places and as a percentage to 1 place, respectively. Compute the percentage of the total for each category as a decimal to 3 places and as a percentage to 1 place, respectively.

c.) In the Pie Chart Tables, enter a simple formula in each cell that links to each of the categories and the associated amount. So, if the first category is in cell A8, then in cell G32, you would enter =A8.

d.) Create two well-labeled pie charts, one for outlays and one for receipts. Include a key and show the percent of the whole on each pie section. Select the following titles for the charts:
U.S. Budget Outlays 2019 and U.S. Budget Receipts 2019.

e.) Compute the annual surplus/deficit.

f.) Assuming the US is getting charged 2.5% APR on our debt, compute the debt using the interest given in the table.

Project Description:
In this problem, you will investigate the U.S. projected 2019 budget broken down by Outlays and Receipts. You will also provide the results as pie charts.

1 Start Excel. Download and open the workbook named: Chapter_4-2_Budget_Start

2 In cell D13, calculate the subtotal amount of mandatory outlays.

3 In cell D17, calculate the subtotal amount of discretionary outlays.

4 In cell D19, calculate the subtotal amount of interest outlays.

5 In cell D26, calculate the subtotal amount of receipts.

6 In cell H19, calculate the total amount of outlays.

7 In cell H28, calculate the total amount of receipts.

8 In cell F9, calculate the ratio of social security outlays to the subtotal amount of mandatory outlays. Fill cell F9 down the column to cell F12. Format cells F9:F12 as Number with 3 decimal places.

9 In cell G9, refer to the ratio of social security outlays to the subtotal amount of mandatory outlays in cell F9. Fill cell G9 down the column to cell G12. Format cells G9:G12 as Percentage with 1 decimal place.

10 In cell H9, calculate the ratio of social security outlays to the total amount of outlays. Fill cell H9 down the column to cell H12. Format cells H9:H12 as Number with 3 decimal places.

11 In cell I9, refer to the ratio of social security outlays to the total amount of outlays in cell H9. Fill cell I9 down the column to cell I12. Format cells I9:I12 as Percentage with 1 decimal place.

12 In cell F15, calculate the ratio of national defense outlays to the subtotal amount of discretionary outlays. Fill cell F15 down the column to cell F16. Format cells F15:F16 as Number with 3 decimal places.

13 In cell G15, refer to the ratio of national defense outlays to the subtotal amount of discretionary outlays in cell F15. Fill cell G15 down the column to cell G16. Format cells G15:G16 as Percentage with 1 decimal place.

14 In cell H15, calculate the ratio of national defense outlays to the total amount of outlays. Fill cell H15 down the column to cell H16. Format cells H15:H16 as Number with 3 decimal places.

15 In cell I15, refer to the ratio of national defense outlays to the total amount of outlays in cell H15. Fill cell I15 down the column to cell I16. Format cells I15:I16 as Percentage with 1 decimal place.

16 In cell H18, calculate the ratio of interest outlays to the total amount of outlays. Format cell H18 as Number with 3 decimal places.

17 In cell I18, refer to the ratio of interest outlays to the total amount of outlays in cell H18. Format cell I18 as Percentage with 1 decimal place.

18 In cell F22, calculate the ratio of individual income taxes to the subtotal amount of receipts. Fill cell F22 down the column to cell F25. Format cells F22:F25 as Number with 3 decimal places.

19 In cell G22, refer to the ratio of individual income taxes to the subtotal amount of receipts in cell F22. Fill cell G22 down the column to cell G25. Format cells G22:G25 as Percentage with 1 decimal place.

20 In cell H22, calculate the ratio of individual income taxes to the total amount of receipts. Fill cell H22 down the column to cell H25. Format cells H22:H25 as Number with 3 decimal places.

21 In cell I22, refer to the ratio of individual income taxes to the total amount of receipts in cell H22. Fill cell I22 down the column to cell I25. Format cells I22:I25 as Percentage with 1 decimal place.

22 In cells G32:G38, enter the values from Outlays (Expenditures) table. In cell G32, enter the amount of social security outlays. Fill cell G32 down the column to cell G35. In cell G36, enter the amount of national defense outlays. Fill cell G36 down the column to cell G37. In cell G38, enter the amount of interest outlays.

23 In cell G42, enter the amount of individual income taxes. Fill cell G42 down the column to cell G45.

24 In cells K36:P50, insert a Pie Chart to show the percentage of all categories of outlays. Select range F32:J38. On the Insert tab, click Recommended Charts, and then click Pie. Apply Style 4 on the Design tab. Add a chart title and choose the Above Chart option. Replace Chart Title with U.S. Budget Outlays 2019. Click Quick Layout and choose Layout 6.

25 In cells R36:Y50, insert a Pie Chart to show the percentage of all categories of outlays. Select range F42:J45. On the Insert tab, click Recommended Charts, and then click Pie. Apply Style 4 on the Design tab. Add a chart title and choose the Above Chart option. Replace Chart Title with U.S. Budget Receipts 2019. Click Quick Layout and choose Layout 6.

26 In cell H47, calculate the annual surplus/deficit.

27 In cell H49, calculate the U.S. debt.