Variance Analysis at Acme
(graded individual assignment #8)
You are a consultant at ACME, a large global corporation. It is year-end and the CFO (your boss) wants you to perform a variance analysis of the budgeted versus actual amounts of the expenditures reported worldwide by the many ACME branches. A staff member in the IT department provides you with the address of a table that contains the data that you need. It is in the server f-sg6m-s4.comm.virginia.edu and it is called ACME_Budget. At first you thought that you could do it in Excel by hand, but then realized that the table has more than 15K rows...
GOAL: Learning how to use pivot tables.
TASK: This is a pledged individual assignment. Work on your own.
1. Make sure that the "Power pivot" tab is visible in Excel. File > Options > Customize Ribbon > Power Pivot.
2. In the Power pivot tab, Manage > Home > From database...
use SQL server authentication as you did in ADS and the class DB. Test the connection, and proceed.
3. Add a column for the variance. Make sure that you have negative variances when the budgeted amount is smaller than the actual.
4. Answer the questions below. Write the answers in plain English below the pivot table. No need to track the manipulations you do to come up with the answers.
5. Manipulate the pivot table so that it looks *exactly* like the pic below.
6. Save the Excel workbook and post it to your class box folder. Name it AcmePowerPivot.xlsx
CRITERIA: answers must be correct. Pivot table must be identical to the picture.
Tips for success
The hard part of the exercise is to figure out how MS Power Pivot works and what commands to use to slice and dice the data. Figuring
that out is the exercise.
Business questions
a. Which division had the largest negative variance in August?
b. In terms of negative variance what was the worst department in South America?
c. Which division had the most accurate sales budget?
d. What are the top three sources (categories) of variance in absolute value for R&D worldwide?
e. Was the PR more over/under budget in Europe or Asia?
f. Why is HR in Asia so over budget?
