roject Description: this problem, you wil investigate the U.S. projected 2019 budget broken down by Odays and Recepts. You will also provide the results as pie charts. se acel reference of a single formula where appropriate in order to receive credit. Do not copy and paste values of type values, as you will not receive full credit for your answers Points Possible O 2 2 2 2 2 2 6 6 teps to Perform: Step Instructions Start Excel. Download and open the workbook named: Chapter 42 Budget Start 2 In cel D13, calculate the subtotal wount mandatory outlays. 3 In cel D17, calculate the subtotal amount of discretionary outlays. 4 In cel D19, calculate the subtotal amount of interest outlays. 5 In cel D25, calculate the subtotal amount of receipts. 6 In cel H19, calculate the total amount of outlaya 7 In cel M28, calculate the total amount of receipts. 8 In cel F9, calculate the ratio of social security outlays to the subtotal amount of mandatory outlays. Fa cel P9 down the column to cell F12 Format cells P9.F12 as Number with 3 decimal places. . In cel , refer to the ratio of social security outlays to the subtotal amount of mandatory outlaya in cel F. Fill cell down the column to cell G12. Format cells 69.G12 as Percentage wth 1 decimal 10 In cel P. calculate the ratio of social security Alays to the total amount of outlaya. Fa cel He down the column to col 12. Format cels HO H12 as Number with 3 decimal places 11 In col 19. refer to the ratio of social security outlays to the total amount of outlayt incel H. Fill cell I down the column to cell 112. Format cela 19:112 Percentage wen 1 decimal place. In cel F15. calculate the ratio of national defense outlays to the subtotal amount of discretionary outlays. Recall F15 down the column to cel F16. Format cels F15.F16 a Number with 3 decimal 12 places In cel G15. refer to the ratio of national dutense outlay to the subtotal amount of Giscretionary Outlays in cel F15. Filcol G15 down the column to col G16. Format cols G15.016 as Percentage with 1 decimal place 14 Incel H15, calculate the ratio of national defense outlays to the total amount of stays. Filcol 15 down the column to cell 46. Format cels HIS:H16 as Number weh 3 decimal places 15 In cele 15. refer to the ratio of national tense outlays to the total amount of Outlays in cel H15. Face 115 down the column to cef 116. Format cells 115:116 as Percentage with 1 decimal place In cel H18. calculate the ratio of interes! outlays to the total amount of Outlys. Format cel H18 as Number with 3 orcimal places 17 In cele 116. Tefer to the ratio of interest outlays to the total amount of Outlaya in cel H1B. Format cel 118 as Percentage with 1 Cecimal place 18 In cel F22. calculate the ratio of Individual income taxes to the subtotal amount of receipes. Fill cel F22 down the column to cel F25. Format cels F22 F25 as Number with 3 decimal places 19 In cel 622. refer to the ratio of individual income taxes to the subtotal amount of receipts in cel F22 Filcol 22 down the column to col 25. Format cols G22 0:25 as Percentage with 1 decimal place 6 6 6 6 6 4 4 6 6 6 20 21 6 10 23 24 4 In cell 22. calculate the ratio of individual income taxes to the total amount of receipts. Fa cell 22 down the column to cell Hos. Format cels 122. Hesas Number with 3 decimal places In cel 122. refer to the ratio of individual income taxes to the total amount of receipts in cel 02. Recol 122 down the column to col 125. Format cells 122 125 as Percentage wth 1 decimal place In cols 032.038, enter the values from Outlays (Expenditure) table. In cell 032. enter the amount of social security Outinys. Fa cell 32 Gown the column to col G3s. In cel 036, enter the amount of national defense outlays. Fil cel G3 down the column to cell G37. In cel 38. enter the amount of interest outlays. In cell 42. enter the amount of Individual income taxes. Fill cel A2 Gown the column to cell G4S. In celis 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 Ple. Apply Style 4 on the Design tab. Add a charcle and choose the Above Chart option. Replace Chart Title with U.S. Budget Outlays 2018. Gick Quick Layout and choose Layout 6. Inces R$6.950. Insert a Pie Chart to show the percentage of all categories of outlays. Select range F42:45. On the Insert tab, click Recommended Charts, and then click P. Acoly Style 4 on the Design tab. Asd a chart title and choose the Above Chart option. Replace Chan Tele wth U.S. Budit Receipts 2019. Click Quick Layout and choose Layout 6 In col Ht7, calculate the annual surplus de In cel 49, calculate the U.S. debt Save your file and submit for grading Toe Porn 4 26 27 28 2 O 111 F G L M N O P R 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 $) 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.gow/omh/historical tables/ % of Subtotal b.) % of Total b.) b.) b.) a.) Compute the subtotals and totals for both Outlays and Receipts. 656.0 a.) b.) b.) b.) b.) a.) b.) b.) 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. a.) TOTAL: b.) b.) b.) b.) Outlays (Expenditures): 8 Mandatory 9 Social Security 1046.5 10 Medicare 624.7 11 Medicaid 412.0 12 Other 13 Subtotal: 14 Discretionary 15 National Defense 678.2 16 Non-Defense 626.0 17 Subtotal: 18 Interest 363.4 19 Subtotal: 20 21 Receipts (Income): 22 Individual Income Tax 1.687.7 23 Corporate Income Tax 225.3 24 Payroll Tax 1,237.7 25 Excise Tax + Other 271.6 26 Subtotal: 27 28 29 30 31 32 33 34 35 36 37 38 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 AS ov 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. TOTAL: e.) Compute the annual surplus/deficit. Billions($) f.) Assuming the US is getting charged 2.5% APR on our debt, compute the debt using the interest given in the table. Pie Chart Table Outlays Category c.) Social Security Medicare Medicald Other National Defense Non-Defense Interest d.) U.S. Budget Outlays 2019 and U.S. Budget Receipts 2019 TOTAL: e.) Compute the annual surplus/deficit. Billions($) f.) Assuming the US is getting charged 2.5% APR on our debt, compute the debt using the interest given in the table. Pie Chart Table Outlays Category c.) Social Security Medicare Medicaid Other National Defense Non-Defense Interest Billions($) Pie Chart Table Receipts Category Individual Income Tax Corporate Income Tax Payroll Tax Excise Tax + Other e.) Annual Surplus/Deficit billions Debt billions roject Description: this problem, you wil investigate the U.S. projected 2019 budget broken down by Odays and Recepts. You will also provide the results as pie charts. se acel reference of a single formula where appropriate in order to receive credit. Do not copy and paste values of type values, as you will not receive full credit for your answers Points Possible O 2 2 2 2 2 2 6 6 teps to Perform: Step Instructions Start Excel. Download and open the workbook named: Chapter 42 Budget Start 2 In cel D13, calculate the subtotal wount mandatory outlays. 3 In cel D17, calculate the subtotal amount of discretionary outlays. 4 In cel D19, calculate the subtotal amount of interest outlays. 5 In cel D25, calculate the subtotal amount of receipts. 6 In cel H19, calculate the total amount of outlaya 7 In cel M28, calculate the total amount of receipts. 8 In cel F9, calculate the ratio of social security outlays to the subtotal amount of mandatory outlays. Fa cel P9 down the column to cell F12 Format cells P9.F12 as Number with 3 decimal places. . In cel , refer to the ratio of social security outlays to the subtotal amount of mandatory outlaya in cel F. Fill cell down the column to cell G12. Format cells 69.G12 as Percentage wth 1 decimal 10 In cel P. calculate the ratio of social security Alays to the total amount of outlaya. Fa cel He down the column to col 12. Format cels HO H12 as Number with 3 decimal places 11 In col 19. refer to the ratio of social security outlays to the total amount of outlayt incel H. Fill cell I down the column to cell 112. Format cela 19:112 Percentage wen 1 decimal place. In cel F15. calculate the ratio of national defense outlays to the subtotal amount of discretionary outlays. Recall F15 down the column to cel F16. Format cels F15.F16 a Number with 3 decimal 12 places In cel G15. refer to the ratio of national dutense outlay to the subtotal amount of Giscretionary Outlays in cel F15. Filcol G15 down the column to col G16. Format cols G15.016 as Percentage with 1 decimal place 14 Incel H15, calculate the ratio of national defense outlays to the total amount of stays. Filcol 15 down the column to cell 46. Format cels HIS:H16 as Number weh 3 decimal places 15 In cele 15. refer to the ratio of national tense outlays to the total amount of Outlays in cel H15. Face 115 down the column to cef 116. Format cells 115:116 as Percentage with 1 decimal place In cel H18. calculate the ratio of interes! outlays to the total amount of Outlys. Format cel H18 as Number with 3 orcimal places 17 In cele 116. Tefer to the ratio of interest outlays to the total amount of Outlaya in cel H1B. Format cel 118 as Percentage with 1 Cecimal place 18 In cel F22. calculate the ratio of Individual income taxes to the subtotal amount of receipes. Fill cel F22 down the column to cel F25. Format cels F22 F25 as Number with 3 decimal places 19 In cel 622. refer to the ratio of individual income taxes to the subtotal amount of receipts in cel F22 Filcol 22 down the column to col 25. Format cols G22 0:25 as Percentage with 1 decimal place 6 6 6 6 6 4 4 6 6 6 20 21 6 10 23 24 4 In cell 22. calculate the ratio of individual income taxes to the total amount of receipts. Fa cell 22 down the column to cell Hos. Format cels 122. Hesas Number with 3 decimal places In cel 122. refer to the ratio of individual income taxes to the total amount of receipts in cel 02. Recol 122 down the column to col 125. Format cells 122 125 as Percentage wth 1 decimal place In cols 032.038, enter the values from Outlays (Expenditure) table. In cell 032. enter the amount of social security Outinys. Fa cell 32 Gown the column to col G3s. In cel 036, enter the amount of national defense outlays. Fil cel G3 down the column to cell G37. In cel 38. enter the amount of interest outlays. In cell 42. enter the amount of Individual income taxes. Fill cel A2 Gown the column to cell G4S. In celis 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 Ple. Apply Style 4 on the Design tab. Add a charcle and choose the Above Chart option. Replace Chart Title with U.S. Budget Outlays 2018. Gick Quick Layout and choose Layout 6. Inces R$6.950. Insert a Pie Chart to show the percentage of all categories of outlays. Select range F42:45. On the Insert tab, click Recommended Charts, and then click P. Acoly Style 4 on the Design tab. Asd a chart title and choose the Above Chart option. Replace Chan Tele wth U.S. Budit Receipts 2019. Click Quick Layout and choose Layout 6 In col Ht7, calculate the annual surplus de In cel 49, calculate the U.S. debt Save your file and submit for grading Toe Porn 4 26 27 28 2 O 111 F G L M N O P R 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 $) 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.gow/omh/historical tables/ % of Subtotal b.) % of Total b.) b.) b.) a.) Compute the subtotals and totals for both Outlays and Receipts. 656.0 a.) b.) b.) b.) b.) a.) b.) b.) 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. a.) TOTAL: b.) b.) b.) b.) Outlays (Expenditures): 8 Mandatory 9 Social Security 1046.5 10 Medicare 624.7 11 Medicaid 412.0 12 Other 13 Subtotal: 14 Discretionary 15 National Defense 678.2 16 Non-Defense 626.0 17 Subtotal: 18 Interest 363.4 19 Subtotal: 20 21 Receipts (Income): 22 Individual Income Tax 1.687.7 23 Corporate Income Tax 225.3 24 Payroll Tax 1,237.7 25 Excise Tax + Other 271.6 26 Subtotal: 27 28 29 30 31 32 33 34 35 36 37 38 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 AS ov 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. TOTAL: e.) Compute the annual surplus/deficit. Billions($) f.) Assuming the US is getting charged 2.5% APR on our debt, compute the debt using the interest given in the table. Pie Chart Table Outlays Category c.) Social Security Medicare Medicald Other National Defense Non-Defense Interest d.) U.S. Budget Outlays 2019 and U.S. Budget Receipts 2019 TOTAL: e.) Compute the annual surplus/deficit. Billions($) f.) Assuming the US is getting charged 2.5% APR on our debt, compute the debt using the interest given in the table. Pie Chart Table Outlays Category c.) Social Security Medicare Medicaid Other National Defense Non-Defense Interest Billions($) Pie Chart Table Receipts Category Individual Income Tax Corporate Income Tax Payroll Tax Excise Tax + Other e.) Annual Surplus/Deficit billions Debt billions