For each of the following requirements, create a new pivot table in a new worksheet. Name each new worksheet as “Req 1,” “Req 2,” etc. Format the dollar amounts in each pivot table or pivot chart using the accounting format with zero decimal places. Format non-currency numbers in each pivot table or pivot chart using the accounting format with zero decimal places.

1. From 2013 – 2016, what was the total spending in each of the four calendar years? Is spending trending up, trending down, or remaining stable?
2. In each of the years 2013 - 2016, how much was spent in each of the three categories of government (Education, General Government, and Public Works)? What does the pivot table show you?
3. How much in expenditures did Somerville have in each “Item Class” in the General Government category for each of the years 2013 – 2016? What insights can you draw from the pivot table?
4. Using the budget worksheet included in the Excel data file, prepare a budget variance report that compares actual spending by “Item Class” in 2016 for the General Government category. Use cell references for the actual spending totals. Use conditional formatting (use the “Shapes, 3 Signs” style) to denote the direction of the percentage variances. You will want to denote any variance that is more than +/−10% with a red diamond, between +/−3 – 9.99% with a yellow diamond, and less than +/−3% with a green circle.

Note: You can refer to cells in a pivot table from another worksheet, but you cannot copy the referenced cell – so you have to point to each cell individually.

5. Analyze the budget variance report you prepared in Step 4. What variances do you think should be investigated? Why? 

Transcribed Image Text:

City of Somerville, MA Budgeted Expenditures (hypothetical) For Year Ended December 31, 2016 2016 Variance Variance Expenditure (Item Class) 2$ Budget $ Actual % 67,369 $ Advertising Athletics expense (1,321) (1,457) (623,342) 66,048 $ -2.00% 2$ 36,400 $ $ 62,334,155 $ 2$ 37,857 $ -4.00% Bonds and debt-related expenditures 62,957,497 $ 22,654 $ -1.00% Вooks 17,698 $ (4,956) (29,272) -28.00% Dues and subscriptions 2$ 154,062 $ 183,334 2$ -19.00% Education-related $ 294,264 $ 329,577 $ (35,313) (1,751,979) (1,834,855) -12.00% Miscellaneous line item $ 43,799,465 $ 45,551,444 $ -4.00% Professional services expense 2$ 7,339,418 $ 9,174,273 $ -25.00% 2$ Property, plant and equipment Rental expense 6,042,134 $ 390,801 $ 5,700,126 $ (342,008) -6.00% 2$ 407,084 $ 16,283 4.00% A Repairs and maintenance expense Supplies expense Technology expense 2,978,076 $ 22,643 $ 2,567,306 $ (410,770) -16.00% 2$ 22,871 $ 228 1.00% 2$ 551,148 $ 606,264 $ (55,116) -10.00% Utilities expense 260,627 $ 304,934 $ (44,307) -17.00% Total expenditures $ 123,550,672 $ 128,668,856 $ (5,118,184) -139.01% City of Somerville, MA Budgeted Expenditures (hypothetical) For Year Ended December 31, 2016 2016 Variance Variance Expenditure (Item Class) 2$ Budget $ Actual % 67,369 $ Advertising Athletics expense (1,321) (1,457) (623,342) 66,048 $ -2.00% 2$ 36,400 $ $ 62,334,155 $ 2$ 37,857 $ -4.00% Bonds and debt-related expenditures 62,957,497 $ 22,654 $ -1.00% Вooks 17,698 $ (4,956) (29,272) -28.00% Dues and subscriptions 2$ 154,062 $ 183,334 2$ -19.00% Education-related $ 294,264 $ 329,577 $ (35,313) (1,751,979) (1,834,855) -12.00% Miscellaneous line item $ 43,799,465 $ 45,551,444 $ -4.00% Professional services expense 2$ 7,339,418 $ 9,174,273 $ -25.00% 2$ Property, plant and equipment Rental expense 6,042,134 $ 390,801 $ 5,700,126 $ (342,008) -6.00% 2$ 407,084 $ 16,283 4.00% A Repairs and maintenance expense Supplies expense Technology expense 2,978,076 $ 22,643 $ 2,567,306 $ (410,770) -16.00% 2$ 22,871 $ 228 1.00% 2$ 551,148 $ 606,264 $ (55,116) -10.00% Utilities expense 260,627 $ 304,934 $ (44,307) -17.00% Total expenditures $ 123,550,672 $ 128,668,856 $ (5,118,184) -139.01%