Analyzing Revenue Budget Data

Access the data file from the Connecticut Office of the State Comptroller available at this link and answer the following questions.

Required

a$.$ Prepare a PivotChart showing the revenue actual totals by revenue category in a bar chart. Hint: With your cursor on a cell in the worksheet, click on Insert, PivotChart. Drag Revenue category into Axis$($Rows$)$ and Actual Amount into Values. Right click on chart to Select a new chart type and choose Bar chart.

b$.$ Add two chart slicers: one for Fiscal Year and one for Fund type in order to capture data only for $2022$ and for General fund. Add data labels to columns. Hint: Under the $$PivotChart Analyze$$ label, select Insert slicer and select Fiscal year. Repeat steps but now select Fund type. To add data labels, with cursor on a bar $($make sure all bars are highlighted$),$ right click and select Add data labels.

c$.$ Sort bar chart from the largest expenditure to the smallest.

d$.$ Using your data visualization, answer the following questions.

$1.$ What are the top three revenue sources and dollar amounts for the General fund in $2022?$

Note: Enter your answers with two decimal places and do not round your answers.

Answer $1$

Answer $2$

Answer $3$

$2.$ What is the dollar amount of revenue for the category $$Cigarettes and Tobacco$?$

Note: Enter your answer with two decimal places and do not round your answer.

$Answer $4$

$3.$ Display the amounts as a percentage of the grand total. Starting from the category with the highest percentage, what categories make up $94\mathrm{.}46\%$ of the total? Hint: Open the dropdown menu next to the amount field in the Values section. Select Value Field Settings and open the Show values as tab. Select $\%$ of Grand Total.

Note: Show your percentages with two decimal places such as $18\mathrm{.}56\%.$

Health Provider Answer $5$

Corporations Answer $6$

Federal Grants Answer $7$

Pass$-$Through Entity Tax Answer $8$

Personal Income $-$ Estimates and Finals Answer $9$

Sales and Use Answer $10$

Personal Income $-$ Withholding Answer $11$

e$.$ Create a revenue budget for $2023$ using the incremental approach: increase each $2022$ category by $2\%$ as the basis for your $2023$ revenue budget. Round the amounts to millions of dollar. Hint: Create an Excel worksheet using the data from your summary table. Arrange your budget from the largest to the smallest dollar amounts.

Note: Round your answers to two decimal places, this time rounded to the nearest million dollar.

$($$ millions$)$

Personal Income $-$ Withholding Answer $12$

Sales and Use Answer $13$

Personal Income $-$ Estimates and Finals Answer $14$

Pass$-$Through Entity Tax Answer $15$

Federal Grants Answer $16$

Corporations Answer $17$

Health Provider Answer $18$

Transfers In Answer $19$

Transfers $-$ Special Revenue Answer $20$

Miscellaneous Answer $21$

Licenses Permits and Fees Answer $22$

Real Estate Conveyance Answer $23$

Cigarettes and Tobacco Answer $24$

Insurance Companies Answer $25$

Public Service Corporations Answer $26$

Indian Gaming Payments Answer $27$

Inheritance and Estate Answer $28$

Rents Fines and Escheats Answer $29$

Alcoholic Beverages Answer $30$

Admissions Dues and Cabaret Answer $31$

Sales of Commodities and Services Answer $32$

Investment Income Answer $33$

Non General Fund Revenue Answer $34$

Motor Fuels Answer $35$

R & D Credit Exchange Answer $36$

Refunds of Payments Answer $37$

Transfers Out Answer $38$

Refunds of Taxes Answer $39$

Total

$0$

f$.$ What are some strengths and weaknesses of using the incremental approach to budgeting?

Advantages:

Simplified process: assumes that the base of the budget is Answer $40$

and only focuses on the whether the Answer $41$

amount is accurate.

Disadvantages:

An incremental budgeting process does not encourage analysis of the Answer $42$

budget.