HR Forecasting Assignment

Assumed Date in Case: $12/1/2024$

You have been given the assignment of forecasting the human resource needs of the Union

Bank and Trust Company which currently employs $1,420$ people. The bank presently has $60$

branch offices $($classified as OLD BRANCHES$)$ tocated throughout the metropolitan area,

each of which employs approximately $17$ people. The bank expects to add $38$ branches

during the next three years $($classified as NEW BRANCHES$).$ Branches within the bank differ

considerably in size, so the figures given represent averages.

During the past month, the bank has placed an order for $30$ ATM machines to be placed in

its old branch offices. These machines are schedule to be in operation December $31$ at the

end of the first year $($FY $\frac{1}{1}-\frac{12}{31}).$ The bank has found that for each new machine

purchased, one less teller is needed, on the average.

A breakdown of the bank's current staffing is shown below:

The bank has asked you to perform three human resource forecasting tasks. First, based on

the assumptions given below, you are required to determine employee turnover for the

main office, the old branches, and the new branches. Your boss would like to know this

information for the next three years and for each of the major personnel categories $($i$.$e$.$

supervisors, tellers$/$clerical$,$ and main office$).$ Your job is to complete Table $2,$ located in the

Excel Spreadsheet.

Second, your boss would like to know the number of new employees the bank will need to

hire for each major personnel category for each of the next three years. Your job is to

complete Table $3,$ located in the Excel Spreadsheet.

Finally, your boss would like to know the total number of employees who will be working for

the bank as of the end of each of the next three years. Your job is to complete Table $4,$

located in the Excel Spreadsheet.

Assignment Assumptions

You are making all projects in December for subsequent years ending December $31\%.$

With regard to old branches, assume:

The $60$ old branches employ $5$ supervisors and $12$ clerical personne$/$tellers each.

$30$ATM machines are placed in operation on December $31($one year hence$)$ and

replace $30$ tellers.

The bank does not terminate employees because of the new ATMs. Rather, as tellers

quit throughout the year, $30$ are not replaced.

Turnover is $30\%$ for tellers$/$clerical personnel, and $20\%$ for supenvisors.

With regard to new branches, assume:

New branches are added as follows: $10$ in year $1,12$ in year $2,$ and $16$ in year $3.$

Each new branches employs $17$ individuals; $5$ supervisors and $12$ tellers$/$clerical$.$

New branches are added evenly throughout the year. Thus, for the purpose of

calculating turnover, on average, there are $5$ new branches in Year $1[50\%$ x $10)$; $16$ in

Year $2[10$ in Year $1$ plus $6(50\%12)]$; and $30$ in Year $3[(10$ year $1+12$ year $2)$ plus $8$

$(50\%16)].$

Turnover is $30\%$ for tellers$/$clerical personnel, and $20$ percent for supervisors.

With regard to the main office, assume that turnover will be $10\%$ per year. Table $3$

Table $3$

Year End Employment

Employee Category Year $1$ Year $2$ Year $3$

Old Branch Supervisors

Old Branch Tellers

Main Office

New Branch Supervisors

New Branch Tellers

Totals

Total new branches$/$year

year $1=10$ total

year $2=10+12=22$ total

year $3=22+16=38$ total