Question

1. Is spending trending up, trending down, or remaining stable?

Column Labels 2013 2014 2015 2016 Grand Total $ 157,158,459 $ 141,432,243 $ 190,121,361 $ 188,302,774 $ 677,014,837 Sum of Amount

2. How much in expenditures did Somerville have in each "Item Class" in the General Government category for each of the years 2013-2016? What insights can you draw from the pivot table ?

Analyze the budget variance report. What variances do you think should be investigated? Why?

Answer 1

Part 1

a. The expenses decreased by approx. 10% in 2014, however, subsequently increased by 35% in 2015 and marginally declined by 1% in 2016. Considering 2014 to be a one-off decline, the expenses have trended upwards indicating a net growth of approx. 20% in 2016 over 2013.

b. The Company incurred $103,035,030 in 2013, $84,464,671 in 2014, $120,518,951 in 2015 and $128,688,856 in 2016 under General Government expenditure in each of the years.

Part 2

We can draw the following insights:

1. Miscellaneous line item accounts for a huge portion of these expenses and have increased by approx $4,513,004 (11%) over the period. We need to investigate into the components of this expenditure and understand reasons for increase.

2. Property, plant and equipment that accounted for approx. 20% ($20,145,609) of the expenditure in 2013, significantly dropped to $6,042,134 in 2016. These might pertain to depreciation on PPE and could reflect the underlying depreciation policy of the company.

3. Professional service expense has increased by double fold since 2013 on a steady basis and has emerged as one of the top 3 expenditures under this class in 2016.

4. Bonds and debt-related expenditure trended from 33,854,232 in 2013 to $21,685,664 in 2014 and jumped significantly to %62,597,497 in 2016. These costs are the second biggest drivers of movement in this expense category. These could be driven by underlying financing activity of the Company such as complete amortisation of debt related costs in 2014 and issue of new bonds/ debt in 2015.

6. Other expenses have moved up/ down based on the nature and are significantly smaller.

Part 3

1. We should in particular investigate into variances that are above 5% threshold over the budget/ significant amount.

2. Miscellaneous line item should be investigated into nature, reasons for variance over budget and ways to curb costs/ budget better in the future

3. Professional services should be investigated into nature and reasons for variance over budget. We should also check if any one time costs were incurred which are expected to be non-recurring for use in budgeting excercise in future.

4. Property, plant and equipment should be investigated into nature and reasons for variance over budget.

5. Repairs and maintenance should be investigated into nature and reasons for variance over budget. We should also check if any one time costs were incurred which are expected to be non-recurring for use in budgeting excercise in future.

For other expenditures, thought the variance % are high, the amounts are relatively smaller and hence can be skipped considering time available.