Question

1. Acme Manufacturing makes a variety of household appliances at a single manufacturing facility. The expected demand for one of these appliances during the next 4 months is shown in the following table along with the expected production costs and the expected capacity for producing these items.

   	Month
   	1	2	3	4
   Demand	420	580	310	540
   Production Cost	$49.00	$45.00	$46.00	$47.00
   Production Capacity	500	520	450	550

   Acme estimates it costs $1.50 per month for each unit of this appliance carried in inventory at the end of each month. Currently, Acme has 120 units in inventory on hand for this product. To maintain a level workforce, the company wants to produce at least 400 units per month. They also want to maintain a safety stock of at least 50 units per month. Acme wants to determine how many of each appliance to manufacture during each of the next 4 months to meet the expected demand at the lowest possible total cost.

   1. Draw a network flow model for this problem.

   2. Create a spreadsheet model for this problem and solve it using Solver.

   3. What is the optimal solution?

   4. How much money could Acme save if the company were willing to drop the restriction about producing at least 400 units per month?

Answer 1

(b)

Firstly take the cell B2 to B10 as decision variable to decide that how much flow will assign from region to the destination as per provided combination. Then screenshot is given below.

Now write down the provided information into excel spreadsheet. The screenshot is given as below.

Now calculate the net flow at each node by using SUMIF( )function of excel, that us from cell H2 to H11. the screenshot is given as below.

Now finally calculate the total cost in cell E12 by using SUMPRODUCT( )function of excel.

All the inputs have been done. Now click on solver tab of excel, then a new dialog box will open. In this, take E12 as objective which need to be minimized and then take the decision variables as changing cells. After this, add the following constraints.

1. Add the constraint that the flow is must greater than or equal to minimum requirement. The screenshot is given as below.

2. Add the constraint that the net flow should be less than or equal to the available supply or demand. The screenshot is given as below.

Now click on solve button of the solver to have the desired result of the whole process.

(c) According to the solver report, the total cost is $83,565 (because all cost in thousands)and the rest information is shown above in the last screenshot of output(here supply/demand and flow also in thousands).

(d) For this just one change is required that is replace zero where 400 been written. The screenshot is given as below.

According to the solver report, now the total cost is $83,520. Hence, Acme can save.

=$83,565-$83,520

=$45

Therefore, Acme can save only $45 in the present scenario.