Inventory Simulation Assignment

The decision as to HOW MUCH inventory to order and WHEN to order that inventory can occupy a substantial portion of a manager$$s day. Additionally, even with great deliberation and thought, the decisions will be affected by demand patterns that are somewhat unpredictable. Thus, the manager must attempt to have adequate inventory on hand to meet customer needs $($adequate service levels$)$ while at the same time keeping ordering costs and inventory holding costs to a minimum.

Problem Description:

You are in charge of purchasing for an e$-$commerce company. Your job is to minimize the inventory$-$related costs of purchasing products. This exercise will track your performance for one of the products. The inventory$-$related costs include inventory ordering costs $($per order$),$ inventory holding costs $($per unit of inventory$/$day$),$ and backlog costs $($extra processing, lost customers, lost goodwill$).$ Below are the parameters for the exercise.

Each day you have orders $($demand$)$ from your customers that historically vary according to an approximately normal distribution with a mean of $35$ and a variance of $25($standard deviation $=5).$

Each day you may choose to order any quantity of the product from a supplier for arrival the next day. Each time you order a processing cost of $$20$ must be paid.

Each day you try to fulfill the customer orders $($and any backlogs$).$ The following costs are assessed at the end of each day.

Each unit left in inventory costs $$3$ to handle $($storing$,$ moving, interest, etc.$)$

Each customer left unsatisfied costs you $$8($extra processing, lost customers, lost goodwill$).$

Your goal is to maximize the amount of money left in your monthly $$6,500$ budget for inventory$-$related expenses.

Assignment

Use the template provided to simulate one month $(30$ days$)$ of demand from the given distribution.

Build a spreadsheet that will automatically calculate all of the other columns in the spreadsheet template provided. Hints are in Row $1.$

Use the concepts of the inventory modeling to find an optimal decision rule $($order quantity and reorder point$)$ that will result in the maximum remaining budget at the end of the month. You will need to set up a Solver model.

Use the SolverTable to simulate twelve months of results from your single month. You will need to use a single dummy data cell for the SolverTable input; it will need to replicate $12$ times to represent $12$ months. The outputs should include the order quantities, reorder points, and monthly budget surplus.

Based on the results what order quantity and reorder point would you suggest adopting? You are recommending a single order quantity and a single reorder point. Justify your answer.

Based on the results, what part of the purchasing budget for inventory$-$related costs is excessive? You should make a recommendation about the budget for inventory related costs. Justify your answer.

Explain your results and answer the above questions as if you are explaining this to your boss.

Tips

You will need to create data reference cells for the information in the problem description. DO NOT TYPE NUMBERS INTO FORMULAS!

You will need to create decision variables for the reorder point $($at what point you make the decision to place an order$)$ and order quantity $($how many you will order each time you place an order$).$ This means there are $2$ decision variables, only $2.$

For Day $0,$ assume beginning inventory, yesterday$$s order, random number, today$$s demand, and yesterday$$s backlog are all $0.$

Demand starting on Day $1$ will be generated using the normal distribution.

You will need to run solver to maximize ending cash before running SolverTable.

Solver and SolverTable will take a while to run. For those of you with Macs, I recommend using the lab computers or remote login when using SolverTable.

Think about which information in this problem cannot be decimals.