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 and it is stored on the DBMS server f-sg6m-s4 in the SmallBankDB. A second button builds a pivot table that must look like this screenshot.

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.
After creating the two buttons, use manually the pivot table (no extra programming) to answer the four CFO questions. Upload the zipped VS project as you normally do, and put the answers in a .txt file and upload it as a separate item. 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.