PerfectCustomPapers | Modeling Analytics

Modeling Analytics

In some ordering problems, like the one for Sam’s Bookstore, whenever demand exceeds existing inven- tory, the excess demand is not lost but is filled by expedited orders—at a premium cost to the company. Change Sam’s model to reflect this behavior. Assume that the unit cost of expediting is $40, well above the highest regular unit cost.

In the Sam’s Bookstore problem, the quantity discount structure is such that all the units ordered have the same unit cost. For example, if the order quantity is 2500, then each unit costs $22.25. Sometimes the quantity discount structure is such that the unit cost for the first so many items is one value, the unit cost for the next so many units is a slightly lower value, and so on. Modify the model so that Sam’s pays $24 for units 1 to 1500, $23 for units 1501 to 2500, and $22 for units 2501 and above. For example, the total cost for an order quantity of 2750 is 1500(24) 1 1000(23) 1 250(22). (Hint: Use IF functions, not VLOOKUP.)

Continuing Problem 1, create a two-way data table for expected profit with order quantity along the side and unit expediting cost along the top. Allow the order quantity to vary from 500 to 4500 in incre- ments of 500, and allow the unit expediting cost to vary from $36 to $45 in increments of $1. Each col- umn of this table will allow you to choose an opti- mal order quantity for a given unit expediting cost. How does this best order quantity change as the unit expediting cost increases? Write up your results in

a concise memo to management. (Hint: You will have to modify the existing spreadsheet model so that there is a cell for expected profit that changes automatically when you change either the order quantity or the unit expediting cost.)

Sample Answer

This memo addresses the modifications made to the Sam’s Bookstore model to reflect changing inventory and cost dynamics:

1. Expedited Orders:

Introduced a new variable “ExpeditedUnits” to track the number of units fulfilled through expedited orders.
Modified the cost calculation to include the regular unit cost for non-expedited units and the sum of expedited unit cost ($40) multiplied by the “ExpeditedUnits”.
Adjusted the demand satisfaction logic to ensure that only the unmet demand after regular inventory depletion triggers expedited orders.

Full Answer Section

2. Tiered Quantity Discounts:

Implemented an “IF” function to categorize order quantities into defined ranges (1-1500, 1501-2500, 2501+) and apply corresponding unit costs ($24, $23, $22).
Modified the total cost calculation to reflect the applied unit costs based on the order quantity range.

3. Data Table and Analysis:

Constructed a two-way data table with order quantity (500-4500 in increments of 500) and unit expediting cost ($36-$45 in increments of $1) as variables.
Created a formula for expected profit that considers revenue, regular and expedited costs, and demand satisfaction.
Analyzed the data table to identify the optimal order quantity for each unit expediting cost level.

Observations and Recommendations:

The optimal order quantity generally increases as the unit expediting cost rises. This reflects the trade-off between minimizing regular unit cost and avoiding expensive expedited orders.
Identifying specific trends and optimal quantities requires detailed analysis based on actual demand and cost data.
Visualizing the data table (e.g., using conditional formatting) can help pinpoint regions with optimal profit outcomes.

Next Steps:

Populate the data table with relevant demand and cost data to derive actionable insights.
Conduct sensitivity analysis to understand the impact of fluctuating demand and cost parameters on optimal order quantities.
Integrate these findings into inventory management strategies to optimize costs and ensure demand satisfaction.

Disclaimer: This memo provides a general framework. Tailoring the analysis and recommendations to Sam’s specific context and data is crucial for making informed decisions.

Additional Notes:

The modifications described involve updating your existing spreadsheet model with the mentioned formulas and functionalities.
Consider using spreadsheet software’s built-in data table feature to automate the table creation and analysis process.
Remember to adapt the analysis and recommendations based on the specific details of your model and data.

This question has been answered.

Get Answer

No More Worries!

Paper Formatting

No Lateness!

Our Guarantees

Modeling Analytics

Sample Answer

Full Answer Section

This question has been answered.

Satisfaction Guaranteed

Why Choose Us