You are required to develop a business report for F&G that covers the following requirements:

Developing Mathematical Model

Conduct a literature review; then develop a LP model that relates workforce and production

decisions to monthly costs, and to find the minimum$-$cost solution that meets forecasted demands

on time and stays within limits on overtime hours and production capacity. $(6$ marks $-$ Word

Document$)$

Reproducing the model in Excel and solving the problem

Formulating the decision variables, objective function, and constraints in Excel; Setting the

Solver parameters; Solving the problem; Interpret the result, provide the schedule, and

summarize the key steps and findings in the report. $(10$ marks $-$ Excel Document and Word Document$)$

The company's CEO is asking your recommendation regarding the initial number of workers

$($currently $20).$ The CEO said, "there is an opportunity to begin the process by selecting any initial

number of workers between $0$ and $200".$ Investigate the relationship between the initial number

of workers and the total cost

Definition

In these models, the workforce level is allowed to change each period through the hiring and firing of

workers. Such models, where we determine workforce levels and production schedules for a multiperiod

time horizon, are called aggregate planning models.

Case

F&G company is a manufacturer of fancy bags in United States. The demand for the next four

months is forecasted as below:

$1000$ bags are on hand at the beginning of month $1,$ and the company has $20$ workers. The wage of a

worker is $$1500$ per month. The standard working time for a worker is up to $160$ hours per month before

he or she receives overtime. The maximum overtime for a worker is $20$ hours per month and is paid

$$13$ per hour for overtime labour. It takes four hours of labour and $$15$ of raw material to make a bag.

At the beginning of each month, workers can be hired or fired. Each hired worker costs $$1500,$ and

each fired worker costs $$1900.$ At the end of each month, a holding cost of $$2$ per bag left in inventory

is incurred. Production in a given month can be used to meet that month's demand. F&G wants to use

LP to determine its optimal production schedule and labour policy.

Additional Info:

The data on the current number of workers, the regular hours per worker per month, the

regular hourly wage rates, and the overtime hourly rate, should be well known. The maximum

number of overtime hours per worker per month is probably the result of a policy decision by

management or a clause in the workers' contracts.

The costs for hiring and firing a worker are not trivial. The hiring cost includes training costs

and the cost of decreased productivity due to the fact that a new worker must learn the job.

The firing cost includes severance costs and costs due to loss of morale. Neither the hiring nor

the firing cost would be simple to estimate accurately, but the human resources department

should be able to estimate their values.

The unit production cost is a combination of two inputs: the raw material cost per bag and the

labour hours per bag. The raw material cost is the going rate from the supplier$($s$).$ The labour

per bag represents the "production function"$-$the average labour required to produce a unit of

the product. The operations managers should be able to supply this number.

The big picture for aggregate demand planning model is presented as below:

You are required to develop a business report for F&G that covers the following requirements:

$1.$ Developing Mathematical Model

Conduct a literature review; then develop a LP model that relates workforce and production

decisions to monthly costs, and to find the minimum$-$cost solution that meets forecasted demands

on time and stays within limits on overtime hours and production capacity. $(6$ marks $$ Word

Document$)$

$2.$ Reproducing the model in Excel and solving the problem

Formulating the decision variables, objective function, and constraints in Excel; Setting the

Solver parameters; Solving the problem; Interpret the result, provide the schedule, and

summarize the key steps and findings in the report. $(10$ marks $$ Excel Document and Word Document$)$

$3.$ The company$$s CEO is asking your recommendation regarding the initial number of workers

$($currently $20).$ The CEO said, $$there is an opportunity to begin the process by selecting any initial

number of workers between $0$ and $200.$ Investigate the relationship between the initial number

of workers and the total cost $($preferably by using a graph$)$ and provide your recommendation

regarding the best value or the best range of initial number of workers. Explain how changes in

initial number of workers impacts the total cost value. Discuss other possible ways to improve

the aggregate planning for F&G