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$)$ located 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 $1/112/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:

Total Employees $1,420$

Number of Branches $60$

Supervisors per Branch $5$

Total Number of Supervisors $300$

Tellers per Branch $12$

Total Number of Tellers $720$

Total Branch Employees $1,020$

Total Main Office Employees $400$

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

$1)$ You are making all projections in December for subsequent years ending December $31$st$.$

$2)$ With regard to old branches, assume:

The $60$ old branches employ $5$ supervisors and $12$ clerical personnel$/$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 supervisors.

$3)$ 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\%$ x $12)]$; and $30$ in Year $3[(10$ year $1+12$ year $2)$ plus $8(50\%$ x $16)].$

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

$4)$ With regard to the main office, assume that turnover will be $10\%$ per year. Table $3$ talks about the number of employees to be hired in these

Employee Category

Old Branch Supervisors

Old Branch Tellers

Main Office

New Branch Supervisors

New Branch Tellers

Totals

Number of Employees to Be Hired

Year $1$ Year $2$ Year $3$

Table $4$

Year End Employment

Employee Category

Year $1$ Year $2$ Year $3$

Old Branch Supervisors