ACC 430 Module 8 Prescriptive Analytics Assignment Example

From Forecast to Order: Order Quantities, Safety Stock and a Space-Constrained Optimization for Three Products

[Student Name]

Southern New Hampshire University

ACC 430: Data Analytics for Financial Professionals

Module Eight Assignment

[Instructor Name]

[Date]

The organization, setting and figures below are a composite written as a model document. No real employer, client, colleague or patient is described.

From Forecast to Order: Order Quantities, Safety Stock and a Space-Constrained Optimization for Three Products

Introduction

The distributor reorders its three fastest-moving products, nitrile gloves, can liners and paper towels, on the first of each month in quantities set years ago. The forecast in Module Five shows demand rising, and the warehouse manager says the bay that holds these products is overflowing. The question is prescriptive: how much of each product should be ordered, and when, to minimize ordering and holding cost while keeping customers supplied and fitting the space available? Bertsimas and Kallus (2020) describe prescriptive analytics as the step from predicting what will happen to deciding what to do, and this assignment takes that step for one inventory decision.

Economic Order Quantity for Gloves

Each order costs about $85 to place and receive, from purchasing time, receiving labor and the supplier's freight minimum. Holding a case for a year costs 22 percent of its $28 cost, $6.16, covering capital, space, insurance and shrinkage. Annual demand is 48,000 cases. The economic order quantity, which balances the two costs, is the square root of twice demand times ordering cost divided by holding cost: the square root of 2 times 48,000 times $85, divided by $6.16, or about 1,151 cases (Datar & Rajan, 2021).

Table 1. Current Policy Compared With EOQ, Gloves

Ordering smaller amounts more often roughly halves the annual cost for this product, mainly by reducing the average inventory carried.

Safety Stock and Reorder Point

Demand varies from week to week, so the company needs a buffer. Weekly glove demand averages 923 cases with a standard deviation of 180, and the supplier's lead time is three weeks. Demand over the lead time therefore has a standard deviation of 180 times the square root of three, about 312 cases. For a 97.5 percent service level, meaning the company runs out during a lead time in only about one cycle in forty, the z value is 1.96, giving a safety stock of about 611 cases. The reorder point is expected lead-time demand, 2,769 cases, plus safety stock, or 3,380 cases: when stock falls to that level, the next order is placed.

Adding the Space Constraint

Economic order quantities for the other two products, computed the same way, are 1,605 cases of can liners and 1,778 cases of paper towels. When each order arrives, the bay must hold the full order. Gloves take 2 cubic feet a case, liners 1.5 and towels 3, so the three unconstrained order quantities need 10,043 cubic feet. The bay holds 8,000 for cycle stock, after setting aside space for safety stock.

The Solver model's decision variables are the three order quantities. The objective is to minimize total annual ordering and holding cost for the three products. The constraint is that the space needed for the three orders cannot exceed 8,000 cubic feet.

Table 2. Unconstrained and Constrained Order Quantities

Fitting the space costs only $535 a year compared with the unconstrained ideal, because order quantities near the EOQ produce a flat cost curve: moving away from the optimum raises cost slowly. Solver cuts the bulky paper towels most, because each case uses the most space, and trims gloves least, because each glove case takes relatively little space for its holding cost. A simpler rule of shrinking all three quantities by the same proportion would cost $22,623, slightly more, so the optimization adds a small but real improvement.

What More Space Is Worth

Solver reports a shadow price for the space constraint of about $0.60: each additional cubic foot of bay space would reduce annual cost by about 60 cents. Another 1,000 cubic feet would save about $600 a year, which tells the warehouse manager that a racking project costing $15,000 would not pay for itself on these products alone. Lepenioti et al. (2020) note that one of the main values of prescriptive models is exposing such tradeoffs so that managers can weigh them explicitly.

From Model to Practice

A model that is run once and filed changes nothing. To make the recommendation operational, the reorder points and order quantities will be loaded into the ERP system as replenishment parameters for the three products, so that purchase suggestions appear automatically when stock reaches the reorder point. The purchasing manager will review the suggestions daily rather than placing a large order each month. Demand and its variability will be recalculated quarterly from the forecast in Module Five, and the model rerun if either changes by more than 10 percent. The same approach can then be extended to the next twenty products by volume, which together account for about 40 percent of warehouse space.

Sensitivity

If demand rises 10 percent, as the forecast suggests for next year, the economic order quantities rise about 5 percent, because they grow with the square root of demand, and the plan remains nearly optimal. If the holding cost rate is 18 percent rather than 22, quantities rise about 10 percent, but the constrained solution changes little because space binds. The recommendation is therefore robust to the most uncertain inputs. The service level is the one input that changes the plan materially: raising it from 97.5 to 99 percent would increase glove safety stock by about 115 cases and use more space, a tradeoff the sales team should weigh with purchasing.

Conclusion

The company should replace monthly orders with reorder points and order quantities set by the model: for gloves, order about 977 cases whenever stock falls to 3,380, with similar rules for liners and towels. Total ordering and holding cost for the three products comes to about $22,600 a year at the space-constrained optimum, well below what fixed monthly orders cost for gloves alone relative to their volume, and the shadow price shows that buying more space is not justified by these products alone.

References

Bertsimas, D., & Kallus, N. (2020). From predictive to prescriptive analytics. Management Science, 66(3), 1025-1044. https://doi.org/10.1287/mnsc.2018.3253

Datar, S. M., & Rajan, M. V. (2021). Horngren's cost accounting: A managerial emphasis (17th ed.). Pearson.

Lepenioti, K., Bousdekis, A., Apostolou, D., & Mentzas, G. (2020). Prescriptive analytics: Literature review and research challenges. International Journal of Information Management, 50, 57-70. https://doi.org/10.1016/j.ijinfomgt.2019.04.003