Homework 12

Excel Pivot Tables For Global BI

You are a BI consultant at ACME Inc., a multinational company with a global presence. It is year-end, and the CFO wants to audit the differences between budgeted costs and actual costs throughout the year. Your job is to create a BI application that will perform the key analysis in seconds (rather than hours, as it used to take), and then to answer some business questions.

The CFO wants a button that allows the user to download from an enterprise database 15,000+ rows of financial data. The table containing the financial data is called ACME_Budget on the server f-sg6m-s4 and in the DB SmallBankDB. A second button builds a pivot table that must look like this screenshot (numbers might be different).

Business Intelligence Questions

  • Which department had the largest negative variance (budget - actual) across the year and the globe?
  • Which division had the largest negative variance?
  • Which item, in that division?
  • Which month had the largest negative variance in North America across all categories and items?

It is your lucky day! Googling "VBA & pivot table" you found this code that you will need to adapt.  That will save you some typing. However, do not just copy and paste!  Try to understand it.
Use manually the pivot table (no extra programming) to answer the four CFO questions. Put the answers in a .txt file and upload it. Also separately zip and submit the VS folder as you normally do. I am providing a vLab to support you in this homework. You will need to modify the app shown in the video to complete your assignment. Pressing the button produces a pivot just like the example screenshot.