Have To Use Excel For Calculations And Then Prepare Manageri

Have To Use Excel For Calculations And Then Prepare Managerial Report

Specialty Toys, Inc. is evaluating the optimal order quantity for their new product, Weather Teddy, a talking teddy bear with weather-predicting features. The company faces significant demand uncertainty, with an expected demand of 20,000 units and a 95% probability that demand will fall between 10,000 and 30,000 units. Management has proposed several order quantities: 15,000, 18,000, 24,000, or 28,000 units. They seek an analysis of stockout probabilities, profit estimates under various demand scenarios, and a recommended order quantity based on risk appetite. This report provides the necessary calculations, visualizations, and strategic recommendations to guide the decision-making process.

Paper For Above instruction

In the context of inventory management, especially for new and innovative products like Weather Teddy, understanding demand forecast accuracy and risk exposure is paramount. The first step involves modeling the demand distribution using the given forecast information, then analyzing stockout probabilities and potential profits across varying sales scenarios. Finally, strategic recommendations are made based on risk preferences and expected profitability, supported by detailed calculations and visualizations.

Modeling the Demand Distribution

Based on the forecaster's prediction, demand for Weather Teddy can be approximated using a normal distribution, given the continuous nature and the specified probability range. The central estimate (mean) of demand is 20,000 units. The probability that demand falls between 10,000 and 30,000 units with a 95% confidence interval suggests a standard deviation that captures this variability.

The 95% confidence interval for demand (10,000 to 30,000) indicates that the interval corresponds approximately to the mean plus or minus 1.96 standard deviations, according to properties of the normal distribution:

- Lower bound: 20,000 - 1.96×σ = 10,000

- Upper bound: 20,000 + 1.96×σ = 30,000

Solving for σ:

- 1.96 × σ = 10,000

- σ = 10,000 / 1.96 ≈ 5,102

Thus, the demand can be modeled as D ~ N(20,000, (5,102)^2). A sketch of this distribution shows a symmetric bell curve centered at 20,000, with the bulk of probability mass lying within the 10,000 to 30,000 range, aligning with the forecaster's prediction.

Calculating Stockout Probabilities

The stockout probability for each order quantity is the probability that demand exceeds the order quantity, P(D > Q). Using the normal distribution parameters, the Z-score for each Q is calculated as:

Z = (Q - μ) / σ

For each management-suggested order quantity:

- 15,000 units: Z = (15,000 - 20,000) / 5,102 ≈ -0.981

- 18,000 units: Z = (18,000 - 20,000) / 5,102 ≈ -0.392

- 24,000 units: Z = (24,000 - 20,000) / 5,102 ≈ 0.783

- 28,000 units: Z = (28,000 - 20,000) / 5,102 ≈ 1.569

Using the standard normal table, the cumulative probabilities P(D ≤ Q) are:

- For 15,000 units: P ≈ 0.163 (stockout probability ≈ 83.7%)

- For 18,000 units: P ≈ 0.350 (stockout probability ≈ 65.0%)

- For 24,000 units: P ≈ 0.782 (stockout probability ≈ 21.8%)

- For 28,000 units: P ≈ 0.941 (stockout probability ≈ 5.9%)

These calculations indicate significant stockout risk at lower quantities and minimal risk at higher quantities.

Profit Analysis Under Different Demand Scenarios

The profit calculations consider three scenarios: worst (demand = 10,000), most likely (demand = 20,000), and best (demand = 30,000). The cost per unit is $16, and the selling price is $24. Surplus inventory not sold at retail is liquidated at $5 per unit.

The profit formula:

- When demand ≤ Q (order quantity): Profit = (Selling price × demand) - (Cost per unit × Q) + (Surplus units sold at $5)

- When demand > Q: Profit = (Selling price × Q) - (Cost per unit × Q)

Calculations:

For Q = 15,000 units:

- Worst case (10,000 units sold): Profit = (24×10,000) - (16×15,000) + (5×(15,000-10,000)) = 240,000 - 240,000 + 25,000 = $25,000

- Most likely (20,000 units): Demand exceeds Q, so profit = (24×15,000) - (16×15,000) = 360,000 - 240,000 = $120,000

- Best case (30,000 units): Demand exceeds Q, similar calculation as above: $120,000

For Q = 18,000 units:

- Worst case: (10,000 units): Profit = 240,000 - (16×18,000) + 5×(18,000-10,000) = 240,000 - 288,000 + 40,000 = -$8,000 (a loss)

- Most likely: 20,000 units, profit = 24×18,000 - 16×18,000 = 432,000 - 288,000 = $144,000

- Best case: same as above, profit = $144,000

For Q = 24,000 units:

- Worst (10,000): Profit = 240,000 - (16×24,000) + 5×(24,000-10,000) = 240,000 - 384,000 + 70,000 = -$74,000

- Most likely: 20,000 units demand, profit = 24×24,000 - 16×24,000 = 576,000 - 384,000 = $192,000

- Best (30,000): demand exceeds, profit = 24×24,000 - 16×24,000 = $192,000

For Q = 28,000 units:

- Worst (10,000): profit = 240,000 - (16×28,000) + 5×(28,000-10,000) = 240,000 - 448,000 + 90,000 = -$118,000

- Most likely: 20,000 units, profit = 24×28,000 - 16×28,000 = 672,000 - 448,000 = $224,000

- Best (30,000): demand is less than Q, so profit = 24×30,000 - 16×28,000 + 5×(28,000-30,000) = 720,000 - 448,000 - 10,000 = $262,000

These analyses reveal that higher order quantities generally yield higher profits in the most likely and best demand scenarios but carry substantial risk of losses in the worst case.

Order Quantity at a 70% Service Level

The service level requirement implies selecting an order quantity that covers demand with 70% probability. From the Z-table, Z for 70% service level is approximately 0.52.

Order quantity (Q) = μ + Z×σ = 20,000 + 0.52×5,102 ≈ 20,000 + 2,653 ≈ 22,653 units.

Applying this order quantity:

- Demand realizations: in the worst case, demand is 10,000; estimate overall profit similarly.

- Since 22,653 units isn't among the proposed options, the closest options are 24,000 units, which provide slightly higher coverage, and the profits are similar to the calculations above. Using 24,000 units aligns with a 70% service level policy.

Projected profits under this approach are similar to previous calculations for 24,000 units, with an expected moderate risk of stockouts.

Strategic Recommendation and Justification

Considering the demand variability, profit margins, and risk appetite, an order quantity of approximately 24,000 units offers a balanced approach. It aligns with a nearly 70% service level, reduces the risk of significant stockouts, and maximizes expected profit in the most likely demand scenarios ($192,000). Although higher quantities like 28,000 units can increase profits in favorable scenarios, they also substantially increase potential losses in adverse outcomes.

Furthermore, implementing flexible inventory policies, such as discounts on unsold units and responsive marketing, can mitigate risks associated with overordering. Continuous monitoring of demand trends and adjusting orders accordingly in future seasons will also improve inventory management.

In conclusion, the optimal order quantity for Weather Teddy, based on the demand forecast and risk analysis, is approximately 24,000 units, balancing profitability and risk effectively.

References

  • Chopra, S., & Meindl, P. (2016). Supply Chain Management: Strategy, Planning, and Operation (6th ed.). Pearson.
  • Heizer, J., Render, B., & Munson, C. (2017). Operations Management (12th ed.). Pearson.
  • Silver, E. A., Pyke, D. F., & Peterson, R. (2016). Inventory Management and Production Planning and Scheduling. Wiley.
  • Ross, S. M. (2014). Introduction to Probability Models (11th ed.). Academic Press.
  • Tang, C. S., & Musa, S. N. (2011). Innovative supply chain management: the role of technology, customer integration and collaboration. International Journal of Production Economics, 134(1), 312-325.
  • Croom, S., & Brandon-Jones, A. (2007). Impact of e-procurement: experiences from implementation in the UK public sector. International Journal of Operations & Production Management, 27(11), 1180-1202.
  • Feng, H., & Zhang, Y. (2014). Optimal inventory control with demand forecasting. European Journal of Operational Research, 241(2), 558-570.
  • Verma, R., & Kumar, S. (2020). Demand forecasting and inventory management: approach and application. International Journal of Business and Management, 15(2), 45-55.
  • Nahmias, S. (2013). Production and Operations Analysis. Waveland Press.
  • Slack, N., Brandon-Jones, A., & Burgess, N. (2018). Operations Management (8th ed.). Pearson.

Related Assignments