ISDS 361B- Group Project: Workforce Scheduling PL

Can you help to answer my attach homework project, both excel and report. Due in 3 days!

ISDS361B, Fall 2020

Dr. T. Newby

Page 1

ISDS 361B- Group Project: Workforce Scheduling PL

Due date: 12/6/2020 at 10PM.

Contribution: 25 points (4% of the course grade)

The goal of this group project is for you to apply the concepts of linear programming

optimization that you have learned in class and apply them to a workforce scheduling for Bixie

Manufacturing company. From the results, your team will write a report with recommendations

to the operation manager, Mr. Humphrey Appleby.

This project will be carried out in teams of 2-3 members in a group.

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 Length of Employment Cost

1 One-month $2000

2 Two-month $4800

3 Three-month $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 23

May 20

June 12

ISDS 361B, Fall 2020

Dr. T. Newby

Page 2

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 * $4,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 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.

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.

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 $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 per 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 employee to be

hired. (Hint: you can develop a separate model for this).

f. If Bixie 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 employees? For full-time employee, the training cost incurs once when they

ISDS 361B, Fall 2020
Dr. T. Newby

Page 3

are hired. Assume that full-time and temporary employees work approximately 165 hours

per month.

NOTE:

• Your model must follow the concepts: clear, simple and concise structure with clear
distinction between inputs and outputs.

• Since this is an Excel Project, 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)

• The report must address the questions (a-f) above.

• Include a schedule that shows the number of temporary employees that Bixie should hire
each month for each contract option (in table).

• Include 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.

• What conclusions can you derive from your results regarding the hiring of the temporary
employees?

• Provide a summary of total (in table) showing the total number of full-time employees
hired and the total labor costs associated with these employees.

• What conclusions can you derive from your results regarding the hiring of the full-time
employees?

• Provide recommendations regarding the decision to hire additional full-time employees
versus temporary employees.

• The 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).

NOTE. All recommendations must be evaluated; use tables and figures to support your

claims.

Submission requirements:

Using the project submission section on Titanium, submit 2 files: (a) a copy of your Excel file

which must include the model(s) you developed and (b) your report in Microsoft Word.

C. Assessment Measures:

Points Assessments

15 Correctness:

• Use appropriate Excel functions in your model

• Interpret the results to make suitable business recommendations

10 Format and flow:

• The report is well organized, coherent, and looks professional

ISDS 361B, Fall 2020
Dr. T. Newby

Page 4

Important Notes on Report:

Report correctness, flow, and format:

Your report should be flawless, that is, error-free and professional looking. Each report must have

the following:

• A short introduction paragraph that gives the background to your study and a brief summary
of what you have done in the report

• A discussion section covering the different analyses you have carried out and
comments/evaluations on the results (you can have multiple paragraphs for this section)

• A conclusion section that wraps up and gives recommendations based on your analyses.

The report, including tables and figures/graphs, should not be more than seven (7) pages in

length. Quantity is no indicator of quality, however. A short but well-focused report is

preferable to a long and rambling one.

All pages must be numbered appropriately. Margins need to be 1 inch all around. Use Times

New Roman font, size 12. If possible, use headings for better readability. Do not indent

paragraphs. Keep them short (no more than 5-6 lines). Single space between lines but double

space between paragraphs.

Hint: Plan your report; if necessary outline what you will say. Respond to the task fully. Before

writing, analyze the context of the assignment by considering the requirements of the report and

address the following questions:

• What is the purpose of the document to be written?

• What result do you hope to achieve by writing it?

• Who are your readers and what do they want from your document?

• What method of organization of your information is most useful?

Notes:

Be specific and give justification. Commonplaces and clichés should not be used. Give clear and

well-illustrated reasons with supportive evidence/analysis results (use charts, graphs and tables

where necessary to support your evaluation). Do not reference: see Excel file in your report.

Points will be deducted if you do so. Arrange your argument/narrative/interpretation logically

and coherently. I will evaluate both form and content of your report.

Sample report

Below is a sample report taken from Guffey and Loewy (2018).

• 361B LP Project Fall 2020
• Report sample