OMGT2087 2550 A3C1

Study 2

For this study,

1) An Excel file to demonstrate how you use Solver to identify the solutions. You must ensure that the file is in a workable condition. Each model must be implemented in a separate sheet in the same file.

2) A Word file to explain the mathematical models and to report your answers;

Front page

a. Define decision variables with a measurement unit (e.g. pallets, kg, or km)

b. Provide an objective function and constraints and clearly show how you formulate them with an explanation what each equation means

c. Ensure that decision variables and each equation are linked back to the data

d. Briefly state what answer that you obtain

Problem Statement

Company C is a supermarket in Australia that works 364 days in a year. The manager would like to determine a minimum-cost inventory plan for brown onions. They have collected the following information:

▢ Annual demand: 41,250 kg

▢ Brown onion cost: $1.42/kg

▢ Brown onion RRP: $3.85/kg

▢ Annual inventory holding cost: 23%

▢ Ordering cost: $48.30 (i.e. placing an order and inspecting goods)

▢ Annual in-transit holding cost: 11%

▢ Freight rate: 8¢/kg

▢ Freight-related charges (per shipment): $82 (i.e. handling fee)

▢ Time to process order for freight: 1 day

▢ Freight transit time: 3 days

The manager wants to determine the following information:

a. Economic order quantity.

b. The total purchasing cost.

c. The total ordering cost.

d. The total inventory holding cost.

e. The total transportation cost (by weight).

f. The total freight-related cost (by shipment).

g. The total in-transit holding cost.

h. The total cost for this inventory plan.

i. The number of orders.

j. Ordering point.

k. The profit from this inventory plan.