Preston Township's City Council will be evaluating costs incurred in the various city departments at its next meeting. In total, costs exceeded budgeted amounts in the prior year by $576,277. The Council president has asked for information about actual vs. budgeted costs by department and by expense type to help in the evaluation process. Transaction and budget information is included in the Budget Variance.

I. Create two PivotTables. a. One for actual costs by department and expense type. h. One for budgeted costs by department and expense type.

2. Create two Budget Variance reports (one for variances by department and one for variances by expense type). Both reports should link actual and budget data from the PivotTables. a. The report should include columns for budgeted amounts, actual amounts, variance (in dollars), and variance (in percent). Show the unfavorable dollar variances as negative numbers, favorable variances as positive numbers. Show all percent variances as positive numbers. HINT: Use the ABS function in Excel in the formula to calculate percent variances.

3. Use the PivotTable and the variance reports to answer the following questions: a. Which department experienced the greatest variance between budgeted and actual cost (in dollars)? Which expense type in that department accounted for the largest share of the variance? HINT: Filter your PivotTables to update the variance reports. /J. Which expense type had the highest unfavorable variance (in dollars)? Which department had the highest unfavorable variance in that expense type? Which expense type had the highest favorable variance (in dollars)? Which department had the highest favorable variance in that expense type? c. Which department had the highest percentage variance? Which type of expense was most over or under budget in that department? Which expense type had the highest percentage variance? Which department was most over or under budget in that expense type? d. Schools had the largest budget. Does it appear that the budget dollars were well managed? Explain your answer.

4. In general, should the council members be more concerned about the departments or expense types with the highest unfavorable dollar variances or the highest unfavorable percentage variances? Should the council members be concerned about departments or expense types with favorable variances? Explain your answe