Question

Department	Budgeted HC	Actual HC
Finance	27	34
Human Resources	28	24
Marketing	20	18
Operations	60	54
Procurement	35	35
TOTAL	170	165

Using Excel:

1) In the column next to Actual HC, calculate the percentage of actual headcount for each department.

3) Insert a COMBO Chart, by selecting just the Department, Budgeted HC and Actual HC cell values (Do not include total).  Do not select % of actual count at this time.

4) Provide key findings/comparative analysis for each department.

Please provide instructions as to how this can be done on Excel. Thank You.

Answer 1

1. The percentage can be calculated by dividing the corresponding Actual Head count with total Actual Head count multiplied by 100 (or click percentage icon in home tab).
2. Combo Chart can be made by selecting the Department, Budgeted HC and Actual HC column data till procurement. Then select Insert Tab>Insert Chart> Combo Chart>click on type of combo you want and your desired combo chart will be present. For Excel 2010 you can make normal Bar Chart and then select one of the Bars and select ‘Change the series type’ and click on line chart.
3. Analysis:
   1. Finance Department resource utilization has not been as planned and which has increased the Actual HC from the budgeted. They need to manage the resources more efficiently.
   2. Human Resources department has utilized its resources most efficiently out all the 5 departments.
   3. Procurement was able to manage the resources according the allocated budget whereas Marketing, operations and Human resources departments have managed resources less than the budget allocated.