For each of the following requirements, create a new pivot table in a new worksheet. Name each
Fantastic news! We've Found the answer you've been seeking!
Question:
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.
- From 2013 – 2016, what was the total spending in each of the four calendar years? Is spending trending up, trending down, or remaining stable?
- 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?
- 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?
- 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.
- Analyze the budget variance report you prepared in Step 4. What variances do you think should be investigated? Why?
Related Book For
Database Systems A Practical Approach to Design Implementation and Management
ISBN: 978-0132943260
6th Edition Global
Authors: Thomas Connolly, Carolyn Begg
Posted Date: