..pdf 1 / 2 66.7% ar Assignment Brief You have been appointed as a business analyst team at Bixie Manufacturing. Your first project is to provide an analysis of workforce scheduling for the upcoming 6 months and make appropriate recommendations in relation to a hiring plan with cost analysis. A. Background Bixie Manufacturing has two manufacturing plants located in Tucson, Arizona. Product demand varies considerably from month to month, causing Bixie Manufacturing extreme difficulty in workforce scheduling. Recently the company started hiring temporary workers supplied by Staffing Unlimited, a company that specializes in providing temporary employees for firms in the Tucson area. Staffing Unlimited offered to provide temporary employees under three contract options that differ in terms of the length of employment and the cost. The three options are summarized: Option 1 2 3 Length of Employment One-month Two-month Three-month Cost $2000 $4800 $7500 The longer contract periods are more expensive because Staffing Unlimited experiences greater difficulty finding temporary workers who are willing to commit to longer work assignments. Over the next six months, Bixie projects the following needs for additional employees: Month Employees Needed January 12 February 17 March 19 April May 20 June 12 23 Page 1 Each month, Bixie can hire as many temporary employees as needed under each of the three options, one-month, two-months and three-month, For example, if the company hires five (5) employees in January under Option 2, Staffing Unlimited will supply Bixie Manufacturing with five temporary workers who will work for two months: January and February. For these workers, Bixie will have to pay 5 * 54,800 - $24,000 in total employment cost. Because of some merger negotiations under way, Bixie does not want to commit to any contractual obligations for temporary employees that extend beyond June. Bixie's quality control program requires cach temporary employee to receive training at the time of hire. The training program is required even if the person worked for the company in the past. This means that each time a temporary employee is hired, the company has to send the employee to training Bixie estimates that the cost of training is $825 each time a temporary employee is hired. Thus, if a temporary employee is hired for one month, Bixie will incur a training cost of $825, but will incur no additional training cost if the employee is on a two-month or three-month contract. 1. Using the information given in the background section, you are to develop a decision model using Microsoft Excel that can be used to determine the number of temporary employees Bixie should hire cach month under cach contract plan in order to meet the projected needs at an optimal total cost. The model should accurately represent the current situation and allow you to explore and evaluate hiring strategies and provide information to assist you to address the issues required below: a. How many temporary employees should the company hire each month and in each option? b. If the cost to train each temporary employee could be reduced to $750 per month, what effect would this change have on the hiring plan? Explain. c Discuss the implications that reduce the training cost to $750 effect on the hiring plan has for identifying methods for reducing training costs. d. How much of a reduction in training costs would be required to change the hiring plan based on the original training cost of $825 por temporary employee? e Suppose that Bixie hired 8 full-time employees at the beginning of January in order to satisfy part of the labor requirements over the next six months. This means that the number of temporary employee required per month will be reduced by 8 cmployee to be hired. (Hint: you can develop a separate model for this). f. If Bixic can hire full-time employees for $17.50 per hour, what effect would it have on total labor and training costs over the six-month period as compared to hiring only temporary employcos? For full-time employee, the training cost incurs once when they are hired. Assome that full time and temporary employee work approximately 165 hours per month Page 2 ..pdf 1 / 2 66.7% ar Assignment Brief You have been appointed as a business analyst team at Bixie Manufacturing. Your first project is to provide an analysis of workforce scheduling for the upcoming 6 months and make appropriate recommendations in relation to a hiring plan with cost analysis. A. Background Bixie Manufacturing has two manufacturing plants located in Tucson, Arizona. Product demand varies considerably from month to month, causing Bixie Manufacturing extreme difficulty in workforce scheduling. Recently the company started hiring temporary workers supplied by Staffing Unlimited, a company that specializes in providing temporary employees for firms in the Tucson area. Staffing Unlimited offered to provide temporary employees under three contract options that differ in terms of the length of employment and the cost. The three options are summarized: Option 1 2 3 Length of Employment One-month Two-month Three-month Cost $2000 $4800 $7500 The longer contract periods are more expensive because Staffing Unlimited experiences greater difficulty finding temporary workers who are willing to commit to longer work assignments. Over the next six months, Bixie projects the following needs for additional employees: Month Employees Needed January 12 February 17 March 19 April May 20 June 12 23 Page 1 Each month, Bixie can hire as many temporary employees as needed under each of the three options, one-month, two-months and three-month, For example, if the company hires five (5) employees in January under Option 2, Staffing Unlimited will supply Bixie Manufacturing with five temporary workers who will work for two months: January and February. For these workers, Bixie will have to pay 5 * 54,800 - $24,000 in total employment cost. Because of some merger negotiations under way, Bixie does not want to commit to any contractual obligations for temporary employees that extend beyond June. Bixie's quality control program requires cach temporary employee to receive training at the time of hire. The training program is required even if the person worked for the company in the past. This means that each time a temporary employee is hired, the company has to send the employee to training Bixie estimates that the cost of training is $825 each time a temporary employee is hired. Thus, if a temporary employee is hired for one month, Bixie will incur a training cost of $825, but will incur no additional training cost if the employee is on a two-month or three-month contract. 1. Using the information given in the background section, you are to develop a decision model using Microsoft Excel that can be used to determine the number of temporary employees Bixie should hire cach month under cach contract plan in order to meet the projected needs at an optimal total cost. The model should accurately represent the current situation and allow you to explore and evaluate hiring strategies and provide information to assist you to address the issues required below: a. How many temporary employees should the company hire each month and in each option? b. If the cost to train each temporary employee could be reduced to $750 per month, what effect would this change have on the hiring plan? Explain. c Discuss the implications that reduce the training cost to $750 effect on the hiring plan has for identifying methods for reducing training costs. d. How much of a reduction in training costs would be required to change the hiring plan based on the original training cost of $825 por temporary employee? e Suppose that Bixie hired 8 full-time employees at the beginning of January in order to satisfy part of the labor requirements over the next six months. This means that the number of temporary employee required per month will be reduced by 8 cmployee to be hired. (Hint: you can develop a separate model for this). f. If Bixic can hire full-time employees for $17.50 per hour, what effect would it have on total labor and training costs over the six-month period as compared to hiring only temporary employcos? For full-time employee, the training cost incurs once when they are hired. Assome that full time and temporary employee work approximately 165 hours per month Page 2