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 answer a few 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?

It is your lucky day! Googling "VBA  & pivot table" you found this code that you will need to adapt.
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 must produce a pivot just like the example screenshot above.