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 Cost Training Length of Employment One-month Two-month $1900 $425 2 $4200 $425 Three-month $6400 $425 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 10 February 17 March April May June 23 12 14 15 Page 1 Under the three options available given by Staffing Unlimited, each month, Bixie can hire as many temporary employees as needed under each of the three options, However, they can only hire the exact numbers of temporary employee as specified in the above table for each month. Bixie's quality control program requires each 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. The estimated cost of training is $425 each time a temporary employee is hired. Thus, if a temporary employee is hired whether on a one-month, two-month or three-month contract, Bixie will incur the flat rate training cost of $425 per employee, each time they are hired. The labor cost calculation is as follow: 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* $4,200 = $21,000 in total employment cost plus 5* $425= $2,125 training cost, so the total labor cost is $21,000+$2,125 $23,125. Because of some merger negotiations under way, Bixie does not want to commit to any contractual obligations for temporary employees that extend beyond June, so this workforce scheduling will end at the end of June. B. Project Requirements 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 each month under each contract plan in order to meet the projected needs at an optimal total cost. (15 points). 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 $350 per month, what effect would this change have on the hiring plan? Discuss the implications of reducing the training cost to $350 for the hiring plan. c. How much of a reduction in training costs would be required to change the hiring plan based on the original training cost of $425 per temporary employee? Suppose that Bixie hired 4 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 employees required each month will be reduced by 4. For full-time employees, the training cost incurs once when they are hired in January. Assume that full-time and temporary employees work approximately 152 hours per month. d. If the four full-time employees were hired at a $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 employees? Hint: you can develop a separate model for this, but reduce the number of required employee of each for each month by 4 NOTE: Your Excel model(s) must follow the concepts: clear, simple and concise structure with clear distinction between inputs and outputs. Suitable Excel functions must be used where appropriate. 2. From your developed model(s), you are to produce one report (about 700 words) summarizing your evaluation of the hiring strategies for the company (10 points) Your report must be written in a narrative style. Do not use bullet points throughout to answer the listed questions (points will be deducted if you do so). The report must address the questions (a-f) above. At the minimum, it must include: a schedule that shows the number of temporary employees that Bixie should hire each month for each contract option (in table). a summary table, with totals, that shows the number of temporary employees that Bixie should hire under each contract option, the associated contract cost for each option, and the associated training cost for each option. conclusions you can you derive from your results regarding the hiring of the temporary employees. . a summary (in a table) showing the total number of full-time employees hired and the total labor costs associated with these employees. conclusions you can derive from your results regarding the hiring of the full-time employees. recommendations regarding the decision to hire additional full-time employees versus temporary employees. 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 Cost Training Length of Employment One-month Two-month $1900 $425 2 $4200 $425 Three-month $6400 $425 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 10 February 17 March April May June 23 12 14 15 Page 1 Under the three options available given by Staffing Unlimited, each month, Bixie can hire as many temporary employees as needed under each of the three options, However, they can only hire the exact numbers of temporary employee as specified in the above table for each month. Bixie's quality control program requires each 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. The estimated cost of training is $425 each time a temporary employee is hired. Thus, if a temporary employee is hired whether on a one-month, two-month or three-month contract, Bixie will incur the flat rate training cost of $425 per employee, each time they are hired. The labor cost calculation is as follow: 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* $4,200 = $21,000 in total employment cost plus 5* $425= $2,125 training cost, so the total labor cost is $21,000+$2,125 $23,125. Because of some merger negotiations under way, Bixie does not want to commit to any contractual obligations for temporary employees that extend beyond June, so this workforce scheduling will end at the end of June. B. Project Requirements 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 each month under each contract plan in order to meet the projected needs at an optimal total cost. (15 points). 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 $350 per month, what effect would this change have on the hiring plan? Discuss the implications of reducing the training cost to $350 for the hiring plan. c. How much of a reduction in training costs would be required to change the hiring plan based on the original training cost of $425 per temporary employee? Suppose that Bixie hired 4 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 employees required each month will be reduced by 4. For full-time employees, the training cost incurs once when they are hired in January. Assume that full-time and temporary employees work approximately 152 hours per month. d. If the four full-time employees were hired at a $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 employees? Hint: you can develop a separate model for this, but reduce the number of required employee of each for each month by 4 NOTE: Your Excel model(s) must follow the concepts: clear, simple and concise structure with clear distinction between inputs and outputs. Suitable Excel functions must be used where appropriate. 2. From your developed model(s), you are to produce one report (about 700 words) summarizing your evaluation of the hiring strategies for the company (10 points) Your report must be written in a narrative style. Do not use bullet points throughout to answer the listed questions (points will be deducted if you do so). The report must address the questions (a-f) above. At the minimum, it must include: a schedule that shows the number of temporary employees that Bixie should hire each month for each contract option (in table). a summary table, with totals, that shows the number of temporary employees that Bixie should hire under each contract option, the associated contract cost for each option, and the associated training cost for each option. conclusions you can you derive from your results regarding the hiring of the temporary employees. . a summary (in a table) showing the total number of full-time employees hired and the total labor costs associated with these employees. conclusions you can derive from your results regarding the hiring of the full-time employees. recommendations regarding the decision to hire additional full-time employees versus temporary employees