Excel Project: Aggregating Data at Different Levels You are an internal auditor for the city of Burlington, Vermont. Download the data file “P6-3BurlingtonVermontData.xlsx” from the student download page at http://www.pearsonhighered.com/romney, which contains all the annual account balance information for city departments for six fiscal years. In this workbook are two sheets. The “Annual Data” sheet contains data aggregated to the annual level for the different city departments. The “Monthly Data” sheet contains data aggregated to the monthly level for the different city departments. You are planning to perform audit procedures on the “Monthly Data.” But before you do, you need to verify that the data in this sheet matches the data in the “Annual Data” worksheet, which you already verified is correct.

REQUIRED Analyze the two sheets and, based on your analysis, answer the following questions:

a) Under what circumstances can you not use the “Annual Data” sheet for your audit? Said differently, why might you need the data in the “Monthly Data” sheet?

b) On a separate worksheet in Excel, create a summary of the data that shows the total dollar amount of transactions for the two different sheets. Are these the same for both data sets?

c) Does the total amount for transactions differ for the different departments and years? Create two sheets: the first sheet should compare departments and the second sheet should compare years. What do you learn from these analyses?

d) Based on your analysis to the previous questions, suggest the areas that you believe are most important to investigate further. Why do you believe these areas are the most important to investigate further?