Question

Sing Mask is a local enterprise that manufactures surgical face masks that are sold locally as well as exported to the region. Due to a forecasted surge in demand for the next 3 years, Sing Mask decides to make its operations non-stop with a production line that runs 24 hours a day and everyday throughout the whole year, including public holidays.

Now is the last week of December and you are the Operations Manager overseeing manpower planning. Sing Mask’s Sales Director has provided the following monthly demand (each unit is equivalent to a box of 2000 pieces of face mask) for the next 3 years:

January: 800 units of demand

February: 600 units of demand

March: 800 units of demand

April: 1000 units of demand

May: 1200 units of demand

Jun: 1000 units of demand

July: 1100 units of demand

August: 900 units of demand

September: 800 units of demand

October: 1000 units of demand

November: 700 units of demand

December: 800 units of demand

Assume that demand is 800 units in January each year, demand is 600 units in February each year, etc. Further, demand must be met by the end of a month, e.g., the demand of 800 units must be met by the end of January, the demand of 600 units must be met by the end of February, etc.

Face mask production is relatively a low-technology type of operation and the main costs for the business are the storage cost and manpower cost.

Given its non-stop operation and the need to make manpower planning simpler, Sing Maskoffers its workers 1 month of paid leave for working 11 months consecutively (they will still be given rest days but they cannot take any annual leave). For example, a worker working in the month of January to November will enjoy 1 month paid leave in December. Assume that there are no fires or hires during the three years. Based on the historical data, a worker can produce up to 20 units of face mask in a month and is paid a salary of $1,800 per month.

Storage cost is incurred for any excess units produced that are above the monthly demand and estimated at $120 per unit of production. Any excess inventory at end of the year will be donated to local hospitals as part of Sing Mask’s corporate social responsibility thus there will not be any inventory at the end of December each year.

Question:

Determine an LP model to minimise Sing Maskannual cost (storage and manpower cost) while meeting all monthly demands. List any other assumption(s) you have made in your formulation.

Solve the Linear Program problem by using Microsoft Excel. Please provide answers with screenshots in Excel !!

Answer 1

Solution:-

Let : x = total number of workers per month

y = the excess units produced per month

Annual cost = storage cost + manpower cost

Annual cost = 120y + 1,800x = $ 705,000

Explanation:-

Annual demand = Σ ( January to December ) = 10.700 units

Minimum demand = February: 600 units of demand

Maximum demand = May: 1200 units of demand

Since a worker = 20 units of face mask in a month at $1,800 per month,

and must meet all monthly demand, we choose the maximum demand at 1,200 to determine the number of workers.

number of workers = 1,200 units of demand / 20 units in a month = 60 workers

Since a worker has 1 month of paid leave for working 11 months consecutively,

number of additional workers = ( 60 * 12/12 ) - ( 60 * 11/12 ) = 60 - 55 = 5 additional workers

Let : x = total number of workers per month = 60 + 5 = 65 workers

y = the excess units produced per month

z = units of demand per month = 65 workers * 20 units in a month = 1,300 units

y = Σ ( y - ( January to December ))

y = 12z - Annual demand

y = ( 12 * 1,300 ) - 10,700 = 4,900 units

Annual cost = storage cost + manpower cost

Annual cost = 120y + 1,800x

storage cost = $120y = $120 * 4,900 = $588,000

manpower cost = $1,800x = $1,800 * 65 = $117,000

Annual cost = $588,000 + $117,000 = $ 705,000