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 (Attached) 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.

Attached Files:

• P6-3BurlingtonVermontData.xls
• (10.283 MB)

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?

P6-3BurlingtonVermontData.xlsx