o
Instructions Budget 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. Use a cell reference or a single formula where appropriate in order to receive full credit. Do not copy and paste values or type values, as you will not receive full credit for your answers. Steps to Perform: StepInstructionsPoints Possible 1Start Excel. Download and open the workbook named:0 Chapter_4-2_Budget_Start 2In cell D13, calculate the subtotal amount of mandatory outlays.2 3In cell D17, calculate the subtotal amount of discretionary outlays.2 4In cell D19, calculate the subtotal amount of interest outlays.2 5In cell D26, calculate the subtotal amount of receipts.2 6In cell H19, calculate the total amount of outlays.2 7In cell H28, calculate the total amount of receipts.2 8In 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.6 9In 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.6 10In 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.6 11In 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.6 12In 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.6 13In 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.6 14In 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.6 15In 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.6 16In cell H18, calculate the ratio of interest outlays to the total amount of outlays. Format cell H18 as Number with 3 decimal places.4 17In 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.4 18In 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.6 19In 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.6 20In 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.6 21In 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.6 22In 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.10 23In cell G42, enter the amount of individual income taxes. Fill cell G42 down the column to cell G45.4 24In 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.4 25In 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.4 26In cell H47, calculate the annual surplus/deficit.2 27In cell H49, calculate the U.S. debt.2 28Save your file and submit for grading.0 Total Points118 Questions Budget Use a cell reference or a single formula where appropriate in order to receive full credit. Do not copy and paste values or type values, as you will not receive full credit for your answers. Fiscal Year (FY) 2019 Projected Budget (billions $) Outlays (Expenditures):% of Subtotal% of Total Mandatoryb.)b.)b.)b.) Social Security1046.5 Medicare624.7 Medicaid412.0 Other656.0a.) Subtotal: Discretionaryb.)b.)b.)b.) National Defense678.2 Non-Defense626.0a.) Subtotal:b.)b.) Interest363.4a.) Subtotal:TOTAL:a.) Receipts (Income):b.)b.)b.)b.) Individual Income Tax1,687.7 Corporate Income Tax225.3 Payroll Tax1,237.7 Excise Tax + Other271.6a.) Subtotal: TOTAL:a.) Pie Chart Table Outlays CategoryBillions($) c.)Social Security Medicare Medicaid Other National Defensed.)d.) Non-Defense Interest Pie Chart Table Receipts CategoryBillions($) c.)Individual Income Tax Corporate Income Tax Payroll Tax Excise Tax + Other e.)Annual Surplus/Deficit:billions f.)Debt:billions 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.