General information and instructions

•   This business case accounts for 40% of your final grade.

•    You must complete this assignment on an individual basis.

•    Deadline: Friday 5th  May 2023, 11.59pm AEDT (Sydney time)

•    You need to submit via Canvas:

o A written report

▪    .pdf format only

▪    The report should answer each of the questions

o An Excel spreadsheet

▪    .xlsx format only

▪    The spreadsheet must contain all calculations you perform in the ‘Scenario Calculations’ worksheet.

▪    Aim to structure and format your Excel solution in the most efficient way possible and use financial modelling best practices.

•   This task includes a milestone assessment component that evaluates academic English       language proficiency. You may be guided to further language support after the completion of this subject if your results in this milestone task indicate you need more help with your  language skills.

•    All assignment-related queries must be posted on the Canvas discussion board.

E-mail queries related to the assignment will not be entertained.

From prior experience, technical difficulties often can be traced back to students trying to   upload their work a few minutes prior to the deadline. Tip: do not wait until 11.55pm on 5th May to submit your work via Canvas. Late submissions will receive zero credit unless a          special consideration with valid reasons and documentation is filed.

Grading

•    Both components (the written report and the Excel spreadsheet) will be graded.

•   The written report will be marked based on the

•   The Excel spreadsheet will be marked based on the efficiency and clarity with which solutions are produced.

•    Late submissions will receive zero credit unless a special consideration with valid rea- sons and documentation is filed.

Data

•    In the Assignment page on Canvas, you will find the workbook 25624 - Business Case -         Autumn 2023.xlsx. The workbook contains a template to build all the calculations required.

•    Perform your calculations based on the data provided below.

Insurance Co.

Employee benefit programs to improve retention and productivity

The Company

Insurance Co. is a fast-growing company that has been experiencing high employee turnover rates and low  productivity  levels  in their sales team. To  address these  issues, the  company  is  considering implementing new employee benefit programs to improve retention and productivity.

The company’s sales team is composed of 300 employees, all of which are employed on a full-time basis (7.5 hours per day, 5 days a week). An in-depth analysis of the working habits revealed the effective  time  worked  is  only  5  hours  per  day.  When  asked,  employees  referred  to  family commitments and the need to procure their own food and beverages during the day as the main reasons for the  productivity  losses.  Each  hour of effective time worked  produced direct  revenue benefits of $60 (per employee and effective hour worked).

Insurance Co. has annual employee attrition rate of 8%, which is higher than the industry average (6.5%). To offset the employee turnover, Insurance Co. has a policy to recruit new employees in the sales’ team at a 3% annual rate.

Proposed Solutions

Insurance Co. is considering three programs that will provide benefits to employees. There is evidence that these programs have direct and indirect impact in reducing employee turnover and in increasing employee productivity. The company engaged you to evaluate the effectiveness of these programs over a 36-month period, starting on the 1st of May 2023.

OPTION 1 - CATERED LUNCH

This option explores the benefits of providing free lunch to employees. The initiative has several direct and indirect benefits, employees:

•    spend less time away from work buying lunch,

o Based on similar initiatives implemented in other companies, Insurance Co. expects effective working hours per day per employee will increase over time, reaching an average of 6 hours after 12 months and remaining at that level for the remainder of the 36-month period.

•    save money and may become more loyal to the firm,

o Insurance Co. expects a reduction in attrition rate to 7% per annum.

•    get more opportunities to interact with colleagues during lunch.

o there are no studies that quantify these benefits.

The cost of the catered lunch initiative is $12 per employee per day of work.

OPTION 2 - FREE SNACKS

The free snacks option explores the benefits of providing free, healthy snack to employees during the workday. The initiative has several direct and indirect benefits, employees:

•    spend less time away from work buying snacks,

o a modest increase in productivity is expected under this initiative. Effective working hours per day per employee will increase over the first 6 months, reaching an average of 5.5 hours and remaining at that level for the remainder of the 36-month period.

•    save money and may become more loyal to the firm,

o The evidence on loyalty and retention driven by similar initiatives is not conclusive. For this reason, Insurance Co. expect no change in the attrition rate (8% per annum).

The daily cost of the free snacks initiative is $4 per employee.

OPTION 3 - ONE CHILD FREE DAY CARE

This is the most controversial initiative. It only benefits 12% of the current workforce who have at least one child who is under the age of 6. On the flip side, studies show this type of initiatives have a large impact on employee retention, both those who receive the benefit and those who are planning to start a family. Based on the evidence, Insurance Co. expects:

•    a reduction in the attrition rate to 4% over the first 12 months, remaining at that level for the rest of the 36-month period.

•    productivity is expected show a slight improvement in effective working hours per day per employee over the first 12 months, reaching an average of 5.5 hours and remaining at that level for the remainder of the 36-month period.

The daily cost of day care is on average $67 per child.

Please use the assumptions provided to evaluate the effectiveness of these programs and identify the most suitable option for Insurance Co.

QUESTIONS

QUESTION 1. BASE CASE                    [4 marks]

Using the information provided in section “The Company”, please build a BASE CASE scenario in the Scenario Calculations worksheet. Use the timeline provided to show:

•    the evolution of the workforce size, considering new hires and attrition,

•    total revenue, considering effective hour worked, days worked, and direct revenue generated per employee.

•    the gap between the company’s revenue as is and the potential revenue if the company was

in line with the industry average in attrition.

In the written report, use bullet points to:

•    describe how the different variables affect Insurance Co’s revenue, and

•    discuss the economic significance of introducing modifications that could reduce attrition to the industry average.

QUESTION 2. OPTION 1 - CATERED LUNCH             [6 marks]

•    Using the information provided above, please estimate the Net Revenue of implementing the Catered Lunch initiative in the Scenario Calculations worksheet

•    Include any inputs and assumptions in the Inputs worksheet

•   This question does not have a written report component.

QUESTION 3. OPTION 2 - FREE SNACKS           [8 marks]

•    Using the information provided above, please estimate the Net Revenue of implementing the Free Snacks initiative in the Scenario Calculations worksheet

•    Include any inputs and assumptions in the Inputs worksheet

•   This question does not have a written report component.

QUESTION 4. OPTION 3 - ONE CHILD FREE DAY CARE                                                                    [8 marks]

•    Using the information provided above, please estimate the Net Revenue of implementing the One Child Free Day Care initiative in the Scenario Calculations worksheet

•    Include any inputs and assumptions in the Inputs worksheet

•   This question does not have a written report component.

QUESTION 5. OUTPUTS            [6 marks]

•    In the Outputs worksheet, please use tables and charts to report the key results of your analysis.

•    Please include any sensitivity analysis you judge relevant

•   This question does not have a written report component.

QUESTION 6. DISCUSS YOUR FINDINGS

In the written report, use bullet points to:               [6 marks]