Question

Care Clinic is preparing a worksheet for their staff. Their average starting salary is $60,000. Benefits are 25% this year. They expect them to go up to 27% next year. They are expecting to give a raise of 5% next year. Care Clinic has 20 employees. Prepare a formatted Excel Spreadsheet for Care Clinic. How much is the Total Staff Expense

Answer 1

Answer:

$1,600,200

Explanation:

The total staff expense can be calculated by using the following formula:

Total Staff Expense = (Salary For coming year with increment (STEP1)       +     Benefits In-Kind)  * Total Employees

Here

Salary for Coming Year with Increment is $63,000 (Step1)

Total employees are 20 in Number

Benefits In-Kind per employee is 27% of salary which is $17,010 ($63,000 * 27%)

By putting values, we have:

Total Staff Expense = ($63,000 + $17,010) * 20 Employees

Total Staff Expense = $1,600,200

STEP 1: Find Salary for Coming Year with Increment

Salary for Coming Year with Increment = Average Salary Now * (1 + Increment)

Here

Average Salary is $60,000

Increment is 5%

Salary for Coming Year with Increment = $60,000 *(1 + 5%) = $63,000

EXCEL Formatted Spreadsheet For Care Clinic

                                  Increment       Avg.        BIK     T. Employees  Total

                                              5%          Salary    at 27%        

Total Salary       60000     105%     63000 -            20               1,260,000

Benefits In-Kind 60000      105%     63000 27%            20        340200

                                                                                                             1,600,200